Database Fundamentals (Exam 98-364) PDF

FMTOC.indd Page x 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop FMTOC.indd Page i 5/3/11 12:23:49 PM user-F409 /Users/user-F409/Desktop Microsoft® Official Academic Course Database Fundamentals,...

FMTOC.indd Page x 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop FMTOC.indd Page i 5/3/11 12:23:49 PM user-F409 /Users/user-F409/Desktop Microsoft® Official Academic Course Database Fundamentals, Exam 98-364 FMTOC.indd Page ii 5/3/11 12:23:51 PM user-F409 /Users/user-F409/Desktop Credits EDITOR Bryan Gambrel DIRECTOR OF SALES Mitchell Beaton EXECUTIVE MARKETING MANAGER Chris Ruel MICROSOFT SENIOR PRODUCT MANAGER Merrick Van Dongen of Microsoft Learning EDITORIAL PROGRAM ASSISTANT Jennifer Lartz CONTENT MANAGER Micheline Frederick PRODUCTION EDITOR Amy Weintraub CREATIVE DIRECTOR Harry Nolan COVER DESIGNER Jim O’Shea TECHNOLOGY AND MEDIA Tom Kulesa/Wendy Ashenberg Cover photo: Credit: Matthias Hombauer photography/Getty Images, Inc. This book was set in Garamond by Aptara, Inc. and printed and bound by Bind Rite Robbinsville. The cover was printed by Bind Rite Roobinsville. Copyright © 2012 by John Wiley & Sons, Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc. 222 Rosewood Drive, Danvers, MA 01923, website www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, (201)748-6011, fax (201)748-6008, website http://www.wiley.com/go/permissions. Microsoft, ActiveX, Excel, InfoPath, Microsoft Press, MSDN, OneNote, Outlook, PivotChart, PivotTable, PowerPoint, SharePoint, SQL Server, Visio, Visual Basic, Visual C#, Visual Studio, Windows, Windows 7, Windows Mobile, Windows Server, and Windows Vista are either registered trademarks or trademarks of Microsoft Corporation in the United States and/ or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. The book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Neither the authors, John Wiley & Sons, Inc., Microsoft Corporation, nor their resellers or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book. Founded in 1807, John Wiley & Sons, Inc. has been a valued source of knowledge and understanding for more than 200 years, helping people around the world meet their needs and fulfill their aspirations. Our company is built on a foundation of principles that include responsibility to the communities we serve and where we live and work. In 2008, we launched a Corporate Citizenship Initiative, a global effort to address the environmental, social, economic, and ethical challenges we face in our business. Among the issues we are addressing are carbon impact, paper specifications and procurement, ethical conduct within our business and among our vendors, and community and charitable support. For more information, please visit our website: www.wiley.com/go/citizenship. ISBN 978-0-470-88916-9 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1 www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page iii 5/10/11 2:44:02 AM user-F391 /Users/user-F391/Desktop Foreword from the Publisher Wiley’s publishing vision for the Microsoft Official Academic Course series is to provide students and instructors with the skills and knowledge they need to use Microsoft technol- ogy effectively in all aspects of their personal and professional lives. Quality instruction is required to help both educators and students get the most from Microsoft’s software tools and to become more productive. Thus our mission is to make our instructional programs trusted educational companions for life. To accomplish this mission, Wiley and Microsoft have partnered to develop the highest quality educational programs for Information Workers, IT Professionals, and Developers. Materials cre- ated by this partnership carry the brand name “Microsoft Official Academic Course,” assuring instructors and students alike that the content of these textbooks is fully endorsed by Microsoft, and that they provide the highest quality information and instruction on Microsoft products. The Microsoft Official Academic Course textbooks are “Official” in still one more way—they are the officially sanctioned courseware for Microsoft IT Academy members. The Microsoft Official Academic Course series focuses on workforce development. These programs are aimed at those students seeking to enter the workforce, change jobs, or embark on new careers as information workers, IT professionals, and developers. Microsoft Official Academic Course programs address their needs by emphasizing authentic workplace scenarios with an abundance of projects, exercises, cases, and assessments. The Microsoft Official Academic Courses are mapped to Microsoft’s extensive research and job-task analysis, the same research and analysis used to create the Microsoft Technology Associate (MTA) and Microsoft Certified Technology Specialist (MCTS) exams. The textbooks focus on real skills for real jobs. As students work through the projects and exercises in the textbooks, they enhance their level of knowledge and their ability to apply the latest Microsoft technology to everyday tasks. These students also gain resume-building credentials that can assist them in finding a job, keeping their current job, or furthering their education. The concept of life-long learning is today an utmost necessity. Job roles, and even whole job categories, are changing so quickly that none of us can stay competitive and productive without continuously updating our skills and capabilities. The Microsoft Official Academic Course offerings, and their focus on Microsoft certification exam preparation, provide a means for people to acquire and effectively update their skills and knowledge. Wiley sup- ports students in this endeavor through the development and distribution of these courses as Microsoft’s official academic publisher. Today educational publishing requires attention to providing quality print and robust elec- tronic content. By integrating Microsoft Official Academic Course products, WileyPLUS, and Microsoft certifications, we are better able to deliver efficient learning solutions for students and teachers alike. Joseph Heider General Manager and Senior Vice President www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | iii FMTOC.indd Page iv 5/3/11 12:23:51 PM user-F409 /Users/user-F409/Desktop Preface Welcome to the Microsoft Official Academic Course (MOAC) program for Database Fundamentals. MOAC represents the collaboration between Microsoft Learning and John Wiley & Sons, Inc. publishing company. Microsoft and Wiley teamed up to produce a series of textbooks that deliver compelling and innovative teaching solutions to instructors and superior learning experiences for students. Infused and informed by in-depth knowledge from the creators of Microsoft products, and crafted by a publisher known worldwide for the pedagogical quality of its products, these textbooks maximize skills transfer in minimum time. Students are challenged to reach their potential by using their new technical skills as highly productive members of the workforce. Because this knowledge base comes directly from Microsoft, creator of the Microsoft Certified Technology Specialist (MCTS), and Microsoft Technology Associate (MTA) exams (www.microsoft. com/learning/mcp/mcts), you are sure to receive the topical coverage that is most relevant to your personal and professional success. Microsoft’s direct participation not only assures you that MOAC textbook content is accurate and current; it also means that you will receive the best instruction possible to enable your success on certification exams and in the workplace. The Microsoft Official Academic Course Program The Microsoft Official Academic Course series is a complete program for instructors and institutions to prepare and deliver great courses on Microsoft software technologies. With MOAC, we recognize that, because of the rapid pace of change in the technology and curriculum developed by Microsoft, there is an ongoing set of needs beyond classroom instruction tools for an instructor to be ready to teach the course. The MOAC program endeavors to provide solutions for all these needs in a sys- tematic manner in order to ensure a successful and rewarding course experience for both instructor and student—technical and curriculum training for instructor readiness with new software releases; the software itself for student use at home for building hands-on skills, assessment, and validation of skill development; and a great set of tools for delivering instruction in the classroom and lab. All are important to the smooth delivery of an interesting course on Microsoft software, and all are pro- vided with the MOAC program. We think about the model below as a gauge for ensuring that we completely support you in your goal of teaching a great course. As you evaluate your instructional materials options, you may wish to use this model for comparison purposes with available products: www.wiley.com/college/microsoft or iv | call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page v 5/3/11 12:23:51 PM user-F409 /Users/user-F409/Desktop Illustrated Book Tour Pedagogical Features The MOAC textbook for Database Fundamentals is designed to cover all the learning objectives for that MTA exam 98-364, which is referred to as its “lesson skill matrix.” The Microsoft Technology Associate (MTA) exam objectives are highlighted throughout the text- book. Many pedagogical features have been developed specifically for the Microsoft Official Academic Course program. Presenting the extensive procedural information and technical concepts woven throughout the textbook raises challenges for the student and instructor alike. The illustrated book tour that follows provides a guide to the rich features contributing to the Microsoft Official Academic Course program’s pedagogical plan. The following is a list of key features in each lesson designed to prepare students for success as they continue in their IT education, on the certification exams, and in the workplace: Each lesson begins with an Objective Domain Matrix. More than a standard list of learning objectives, the Lesson Skill Matrix correlates each software skill covered in the lesson to the specific exam objective. Concise and frequent Step-by-Step instructions teach students new features and provide an opportunity for hands-on practice. Numbered steps give detailed, step-by-step instructions to help students learn software skills. Illustrations—in particular, screen images—provide visual feedback as students work through the exercises. These images reinforce key concepts, provide visual clues about the steps, and allow students to check their progress. Lists of Key Terms at the beginning of each lesson introduce Students to important technical vocabulary. When these terms are used later in the lesson, they appear in bold, italic type where they are defined. Engaging point-of-use Reader Aids, located throughout the lessons, tell students why a topic is relevant (The Bottom Line) or provide students with helpful hints (Take Note). Reader Aids also provide additional relevant or background information that adds value to the lesson. Certification Ready features throughout the text signal students where a specific certification objective is covered. They provide students with a chance to check their understanding of that particular MTA objective and, if necessary, review the section of the lesson where it is covered. MOAC offers complete preparation for MTA certification. End-of-Lesson Questions: The Knowledge Assessment section provides a variety of multiple-choice, true-false, matching, and fill-in-the-blank questions. End-of-Lesson Scenarios: Competency Assessment case scenarios and Proficiency Assessment case scenarios are projects that test students’ ability to apply what they've learned in the lesson. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | v FMTOC.indd Page vi 5/10/11 2:44:20 AM user-F391 /Users/user-F391/Desktop vi | Illustrated Book Tour Lesson Features c02CreatingDatabaseObjects.indd Page 20 4/30/11 4:11:00 AM user-F392 /Users/user-F392/Desktop 2 L ESSO N Creating Database Objects O B J E C T I V E D O M A I N M AT R I X SKILLS/CONCEPTS Defining Data Types MTA EXAM OBJECTIVE Choose data types. MTA EXAM OBJECTIVE NUMBER 2.1 Objective Domain Matrix Creating and Using Tables Understand tables and how to 2.2 create them. Creating Views Create views. 2.3 Creating Stored Procedures Create stored procedures and 2.4 functions. KEY TERMS c02CreatingDatabaseObjects.indd Page 27 4/30/11 4:11:01 AM user-F392 /Users/user-F392/Desktop data type tables SQL injection views Key Terms stored procedures Creating Database Objects | 27 You are a database designer for a large importing/exporting business. Your boss has come is meant to be a date and/or time. If you have a reason to force a conversion, you can use the to you with a request to help update some of the company’s old ways of doing business. Cast and Convert functions. She explains that the company will no longer take orders via fax—instead, order requests Cast and Convert offer similar functionality. However, Cast is compliance with ANSI will be received through a Web server or by email. She expects you to design a database standards, which allow you to import or export to other database management systems. to store and process these electronic orders. Convert is specific to T-SQL, but is a little bit more powerful. The syntax of the cast function is: cast(source-value AS destination-type) Defining Data Types Therefore, to convert the count variable to a float, you would use the following command: cast(count AS float) In this section, you will learn what data types are, why they are important, and how they The syntax of the convert function is: affect storage requirements. When looking at data types, you need to understand what each type is designed to do within a table, as well as how certain types work best for CONVERT ( data_type [ ( length ) ], expression [,style ] ) each column, local variable, expression, or parameter. Also, when choosing a data type where you can specify how many digits or characters the value will be. For example: THE BOTTOM LINE to fit your requirements, you need to ensure that whatever type you choose provides the CONVERT(nvarchar(10), OrderDate, 101) most efficient storage and querying schema. In fact, one of the key roles of a database administrator is to ensure that the data within each database is kept uniform by deciding This will convert the OrderDate, which is a DateTime data type to nvarchar value. which data type is best suited to the application module currently being worked on. The 101 style represents USA date with century. mm/dd/yyyy. Figure 2-1 20 Implicit and explicit conversion types c03ManipulatingData.indd Page 54 5/10/11 12:09:00 AM user-F391 /Users/user-F391/Desktop 54 | Lesson 3 This will output the following result: (2 row(s) affected) It really is as simple as that to harness the power of database modification and administration. Now, let’s explore some other types of data modification. Updating Data and Databases As a database administrator, you must understand how data is updated in a database, THE BOTTOM LINE how to write update data to a database using appropriate UPDATE statements, and how to update a database using a table. The Bottom CERTIFICATION READY The function of the UPDATE statement is to change data in a table or view. Much like any of Line Reader the data manipulation or modification clauses and statements within SQL, you can use this What command is used to change existing data statement in either SSMS or a text editor window. Aid in a table? 3.3 Using the UPDATE Statement The UPDATE clause allows you to modify data stored in tables using data attributes such as the following: Certification UPDATE SET = Ready Alert WHERE As you’ve seen from the beginning of this lesson, you can read this type of SQL statement much as you would any sentence. Say you want to update a table in which you want a certain column identifier to reflect a certain value. Perhaps you want to have an attribute of a new Informative supervisor (think of our employee example), Doug Able, being assigned to new employees for training purposes. That supervisor could have the attribute set for him or her as (looking back at our department table) an ID of 4, and the WHERE clause would be satisfied by having Diagrams it match the NULL condition for our employees without a supervisor. Let’s write that scenario UPDATE statement to update the previous example. The first step would be to add a record in the department table with our new supervisor’s name and department ID information using the INSERT statement: INSERT INTO department (first_name, last_name, department_id) VALUES (‘Doug’, ‘Able’, 4) The output response would be as follows: (1 row(s) affected) TAKE NOTE * Now, we need to update our employee table to reflect any employees who do not have an If you don’t specify what assigned department supervisor. Here, our UPDATE statement would look as follows: records to update, all UPDATE employee records or rows will be SET department = 4 updated with the new value. This is potentially WHERE department IS NULL harmful. The result is shown in Table 3-4. Take Note Reader Aid www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page vii 5/10/11 2:44:33 AM user-F391 /Users/user-F391/Desktop Illustrated Book Tour | vii c03ManipulatingData.indd Page 49 5/3/11 12:20:00 PM user-F409 /Users/user-F409/Desktop Manipulating Data | 49 identify the matching column in each of the tables you wish to write the query against and obtain the desired output from. In this example, the foreign key in Table 3-1 is identified in the column “department_id,” and in Table 3-2, the foreign key is identified as the “depart- ment” column match: In other words, the Department table’s Department ID is linked to the department column in the Employee table Table 3-1 Employee table first_name last_name employee_id department James Alexander 610001 1 Easy-to-read David Thompson 620002 1 Frances Drake 610003 1 Tables Alexandria Link 610004 2 Peter Link 620005 2 David Cruze 610007 NULL Table 3-2 Department table department_id first_name last_name 1 Jane Horton 2 Mitch Simmons 3 Paul Franklin Trying to combine data between tables can be very cumbersome, especially if you are creating specific lists from thousands of rows of data. Using a SELECT statement query lets you produce individual lists, but the result may be that you get all the information you need but in an individual list format. The INNER JOIN keyword simplifies this data retrieval by not only using the information from the two tables from which you require output, but using the INNER JOIN keyword to specify the required conditions for which records will appear For example, from the two example tables, you may wish to create a list showing which employees work for each of the different department supervisors. You would write the SQL query statement as follows: SELECT employee.first_name, employee.last_name, department.first_name, department.last_name FROM employee INNER JOIN department ON employee.department = department.department_id The resulting output is shown below: first_name last_name first_name last_name c02CreatingDatabaseObjects.indd Page 32 4/30/11 4:11:05 AM user-F392 /Users/user-F392/Desktop ——————————— —————————— ——————————— —————————— James Alexander Jane Horton David Thompson Jane Horton Frances Drake Jane Horton Alexandria Link Mitch Simmons 32 | Lesson 2 Peter Link Mitch Simmons Antoin Drake Paul Franklin 5. Type the new name of the table you are saving, as shown in Figure 2-6. (6 row(s) affected) Figure 2-6 Naming the table Your new table will appear under the Tables section, as depicted in Figure 2-7. c03ManipulatingData.indd Page 52 5/3/11 12:20:00 PM user-F409 /Users/user-F409/Desktop Figure 2-7 The newly created table 52 | Lesson 3 interface too (SSMS), and the second uses the INSERT statement. Either way accomplishes CERTIFICATION READY What command is used to add data to a table? the same goal. Screen 3.2 Inserting Data Images Let’s first learn how to insert data into a table using SSMS before we move on to the syntax method. INSERT DATA USING SQL SERVER MANAGEMENT STUDIO GET READY. Before you begin, be sure to launch the SSMS application and connect to the database you wish to work with. Then, follow these steps: 1. Check that you have connected to the database you want to work with (see Figure 3-1). Figure 3-1 Connecting to the desired database PAUSE. Leave the SSMS interface open for the next exercise. CREATE A TABLE USING TRANSACT-SQL COMMANDS Creating tables within SSMS is simple because SSMS is an easy-to-use graphical interface. But how can you create tables using ANSI SQL syntax? Quite simply, you will use the create table statement to accomplish this task. An example of proper Transact-SQL syntax for creating a table is as follows: 2. Expand the Databases folder by clicking the plus (⫹) icon beside the word “Databases.” 3. Expand the folder of the database you want to modify. Step-by-Step CREATE TABLE planets (name varchar(50), diameter varchar(50)) INSERT INTO planets (name, diameter) VALUES (‘earth’, 10000) CERTIFICATION READY 4. Expand the Tables folder by clicking on the plus sign next to the word “Tables.” 5. Right-click the table name and chose Edit Top 200 Rows (see Figure 3-2). Exercises How would you create a table using SSMS, and Note that if SQL Server didn’t support implicit conversion, the following syntax would be needed: how would you create a Figure 3-2 table using Transact-SQL CREATE TABLE planets (name varchar(50), diameter varchar(50)) commands? Edit top 200 rows INSERT INTO planets (name, diameter) VALUES (‘earth’, CAST (10000 as 2.2 varchar(50))) www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page viii 5/10/11 2:44:43 AM user-F391 /Users/user-F391/Desktop viii | Illustrated Book Tour c02CreatingDatabaseObjects.indd Page 38 4/30/11 4:11:06 AM user-F392 /Users/user-F392/Desktop 38 | Lesson 2 Microsoft SQL Server already has hundreds of system-stored procedures so that you can perform basic functions. For example, you can use the Select Stored Procedure to retrieve or select rows from a database. Some of the more popular stored procedures will be covered in the next lesson including SELECT, INSERT, UPDATE, and DELETE. Understanding SQL Injections TAKE NOTE * A SQL injection is an Before you learn the syntax statements for selecting, inserting, updating, and deleting attack in which mali- data, you need to understand what a SQL injection is. In short, a SQL injection is an cious code is inserted attack in which malicious code is inserted into strings that are later passed on to instanc- into strings to be passed es of SQL Server waiting for parsing and execution. Any procedure that constructs SQL on later when parsing or statements should be reviewed continually for injection vulnerabilities because SQL executing statements. Server will execute all syntactically valid queries from any source. The primary form of SQL injection is a direct insertion of code into user-input variables that are concatenated with SQL commands and then executed. A less direct method of attack injects malicious code into strings that are destined for storage in a table or are considered metadata. When these stored strings are subsequently concatenated into the dynamic SQL command, the malicious code will be executed. The injection process’s function is to terminate a text string prematurely and append a new command directed from it; because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark “—”, making subsequent text ignored at execution time. S K I L L S U M M A RY Skill Summary IN THIS LESSON , YOU LEARNED THE FOLLOWING : A data type is an attribute that specifies the type of data an object can hold, as well as how many bytes each data type takes up. As a general rule, if you have two data types that differ only in how many bytes each uses, the one with more bytes has a larger range of values and/or increased precision. Microsoft SQL Server includes a wide range of predefined data types called built-in data types. Most of the databases you will create or use will employ only these data types. Exact numeric data types are the most common SQL Server data types used to store numeric information. int is the primary integer (whole number) data type. Precision (p) is the maximum total number of decimal digits that can be stored in a numeric c02CreatingDatabaseObjects.indd Page 39 4/30/11 4:11:06 AM user-F392 /Users/user-F392/Desktop data type, both to the left and to the right of the decimal point; this value must be at least 1 and at most 38. The default precision number is 18. money and smallmoney are Transact-SQL data types you would use to represent monetary or currency values. Both data types are accurate to 1/10,000th of the monetary units they represent. Creating Database Objects | 39 Approximate numeric data types are not as commonly used as other SQL Server data types. If you need more precision (more decimal places) than is available with the exact numeric data types, you should use the float or real data types, both of which typically take SQL Server supports implicit conversions, which can occur without specifying the actual additional bytes of storage. callout function (cast or convert). Explicit conversions require you to use the func- The date and time data types, of course, deal with dates and times. These data types include tions cast or convert specifically. date, datetime2, datetime, datetimeoffset, smalldatetime, and time. A regular character uses one byte of storage for each character, which allows you to define one of 256 possible characters; this accommodates English and some European languages. A Unicode character uses two bytes of storage per character so that you can represent one of 65,536 characters. This added capacity means that Unicode can store characters from almost any language. When you use a VAR element, SQL Server will preserve space in the row in which this element resides on the basis of on the column’s defined size and not the actual number of characters in the character string itself. The Unicode character strings nchar and nvarchar can either be fixed or variable, like regular character strings; however, they use the UNICODE UCS-2 character set. The purpose of a table is to provide a structure for storing data within a relational database. A view is simply a virtual table that consists of different columns from one or more tables. Unlike a table, a view is stored in a database as a query object; therefore, a view is an object that obtains its data from one or more tables. Case Scenarios c03ManipulatingData.indd Page 59 5/3/11 12:20:02 PM user-F409 /Users/user-F409/Desktop A stored procedure is a previously written SQL statement that has been stored or saved into a database. A SQL injection is an attack in which malicious code is inserted into strings that are later passed on to instances of SQL Server for parsing and execution. Manipulating Data | 59 Knowledge Assessment Competency Assessment Fill in the Blank Scenario 3-1: Using the SELECT Command Complete the following sentences by writing the correct word or words in the blanks provided. You have just have been hired as a database administrator for the AdventureWorks 1. Each ____________, ____________, expression, and ____________ always has a Corporation. A network administrator wants to know how to extract information from the related data type. AdventureWorks database. Therefore, you need to answer the following questions: 2. A bit is a Transact-SQL integer data type that can take a ____________ of 1, 0, or 1. What command would you use to display records from a table? NULL. 2. What command would you use to display a FirstName and LastName from the Users table? 3. When you are defining the cost of a product, it is best to use the ___________ data 3. What command would you use to display all records from the Member database and type. have it sorted by the Name column? 4. It is important to consider your use of ____________ data sets when building tables 4. What command would you use to display all records from the Suppliers table that have dependent on daylight saving time. the City of Sacramento? 5. SQL Server supports ____________ conversions without using actual callout functions 5. What command would you use to display the CompanyName, ContactName, and (cast or convert). PhoneNumber from the Suppliers table with Supplier ID greater than 1000? 6. A regular character uses ____________ byte(s) of storage for each character, whereas a 6. What command would you use to display CompanyName, ContactName, and Phone Unicode character requires ____________ byte(s) of storage. Number from the Customers table for companies that have more than 100 employees 7. The data set char is of ____________ length and has a length of ____________ bytes. and reside in the state of California? 8. The purpose of a table is to provide a(n) ____________ for storing data within a rela- Scenario 3-2: Deleting Data from Tables tional database. After you and the network administrator review some records in the AdventureWorks data- 9. When creating a view, be sure to consider ____________ in your design. base, the two of you decide to delete some old records. This scenario brings up the following 10. When querying a database, you can obtain faster results from properly ____________ questions: tables and views. 1. What command would you use to remove all records from the Customer table where the age is less than 18? 2. What command would you use to remove all records from the Schools table that have enrollment less than 500? 3. What command would you use to remove all records from the Contact table that do not have a country of USA and at the same time free the space used by those records? 4. What command would you use to delete the Temp table? Knowledge Assessment Proficiency Assessment Scenario 3-3: Manipulating Data Using SELECT and JOIN Statements You are a database administrator for the AdventureWorks Corporation. Some confu- sion has arisen because the company’s purchase orders are stored in two tables. Therefore, you need to write a query to join the PurchaseOrderHeader table in the sample database, AdventureWorks, to itself in order to provide a list of purchase orders paired together. Each row includes two purchase orders that have identical vendors and shipping methods. 1. After opening SSMS and accessing the AdventureWorks database, what query would you use against the AdventureWorks database to display the ProductSubcategoryID and ProductCategoryID from the Production.ProductSub table that contain the word “Bike”? You also want to sort by Subcategory Name. 2. What query would you use to join the ProductCategory table to the ProductSubcategory table in order to retrieve the Name column from within the ProductCategory table? www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page ix 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop Conventions and Features Used in This Book This book uses particular fonts, symbols, and heading conventions to highlight important information and call attention to special steps. For more information about the features in each lesson, refer to the Illustrated Book Tour section. C ONVENTION M EANING This feature provides a brief summary of the material THE BOTTOM LINE to be covered in the section that follows. CLOSE Words in all capital letters indicate instructions for opening, saving, or closing files or programs. They also point out items you should check or actions you should take. CERTIFICATION READY This feature signals a point in the text where a specific certification objective is covered. It provides you with a chance to check your understanding of that particular MTA objective and, if necessary, review the section of the lesson where the objective is covered. TAKE NOTE* Reader Aids appear in shaded boxes found in your text. Take Note provides helpful hints related to particular tasks or topics. These notes provide pointers to information discussed X REF elsewhere in the textbook or describe interesting features that are not directly addressed in the current topic or exercise. Alt + Tab A plus sign (+) between two key names means that you must press both keys at the same time. Keys that you are instructed to press in an exercise will appear in the font shown here. Example Key terms appear in bold, italic font when they are defined. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | ix FMTOC.indd Page x 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop FMTOC.indd Page xi 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop Instructor Support Program The Microsoft Official Academic Course programs are accompanied by a rich array of resources that incorporate the extensive textbook visuals to form a pedagogically cohesive package. These resources provide all the materials instructors need to deploy and deliver their courses. Resources available online for download include: The Instructor’s Guide contains solutions to all the textbook exercises and Syllabi for vari- ous term lengths. The Instructor’s Guide also includes chapter summaries and lecture notes. The Instructor’s Guide is available from the Book Companion site (http://www.wiley.com/ college/microsoft). The Test Bank contains hundreds of questions in multiple-choice, true-false, short answer, and essay formats, and is available to download from the Instructor’s Book Companion site (www.wiley.com/college/microsoft). A complete answer key is also provided. A complete set of PowerPoint presentations and images is available on the Instructor’s Book Companion site (http://www.wiley.com/college/microsoft) to enhance classroom presentations. Approximately 50 PowerPoint slides are provided for each lesson. Tailored to the text’s topical coverage and Skills Matrix, these presentations are designed to con- vey key concepts addressed in the text. All images from the text are on the Instructor’s Book Companion site (http://www.wiley.com/college/microsoft). You can incorporate them into your PowerPoint presentations or use them to create your own overhead trans- parencies and handouts. By using these visuals in class discussions, you can help focus students’ attention on key elements of technologies covered and help them understand how to use these technologies effectively in the workplace. When it comes to improving the classroom experience, there is no better source of ideas and inspiration than your colleagues. The Wiley Faculty Network connects teachers with technology, facilitates the exchange of best practices, and helps enhance instructional efficiency and effectiveness. Faculty Network activities include technology training and tutorials, virtual seminars, peer-to-peer exchanges of experiences and ideas, personal consulting, and sharing of resources. For details, visit www.WhereFacultyConnect.com. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | xi FMTOC.indd Page xii 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop xii | Instructor Support Program Important Web Addresses and Phone Numbers To locate the Wiley Higher Education Representative in your area, go to http://www. wiley.com/college and click on the “Who’s My Rep?” link at the top of the page, or call the MOAC Toll-Free Number: 1 + (888) 764-7001 (U.S. & Canada only). To learn more about becoming a Microsoft Certified Technology Specialist and about exam availability, visit www.microsoft.com/learning/mcp/mcp. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page xiii 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop Student Support Program Additional Resources Book Companion Web Site (www.wiley.com/college/microsoft) The students’ book companion site for the MOAC series includes any resources, exercise files, and Web links that will be used in conjunction with this course. Wiley Desktop Editions Wiley MOAC Desktop Editions are innovative, electronic versions of printed textbooks. Students buy the desktop version for up to 50% off the U.S. price of the printed text, and they get the added value of permanence and portability. Wiley Desktop Editions also provide students with numerous additional benefits that are not available with other e-text solutions. Wiley Desktop Editions are NOT subscriptions; students download the Wiley Desktop Edition to their computer desktops. Students own the content they buy to keep for as long as they want. Once a Wiley Desktop Edition is downloaded to the computer desktop, students have instant access to all of the content without being online. Students can print the sections they prefer to read in hard copy. Students also have access to fully integrated resources within their Wiley Desktop Edition. From highlighting their e-text to taking and sharing notes, students can easily personalize their Wiley Desktop Edition as they are reading or following along in class. About the Microsoft Technology Associate (MTA) Certification Preparing Tomorrow's Technology Workforce Technology plays a role in virtually every business around the world. Possessing a fundamen- tal knowledge of how technology works and understanding its impact on today’s academic and workplace environment is increasingly important—particularly for students interested in exploring professions involving technology. That’s why Microsoft created the Microsoft Technology Associate (MTA) certification—a new entry-level credential that validates funda- mental technology knowledge among students seeking to build a career in technology. The Microsoft Technology Associate (MTA) certification is the ideal and preferred path to Microsoft’s world-renowned technology certification programs, such as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP). MTA is positioned to become the premier credential for individuals seeking to explore and pursue a career in technology, or enhance related pursuits such as business or any other field where technology is pervasive. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | xiii FMTOC.indd Page xiv 5/3/11 12:24:37 PM user-F409 /Users/user-F409/Desktop xiv | Student Support Program MTA Candidate Profile The MTA certification program is designed specifically for secondary and post-secondary students interested in exploring academic and career options in a technology field. It offers students a certification in basic IT and development. As the new recommended entry point for Microsoft technology certifications, MTA is designed especially for students new to IT and software development. It is available exclusively in educational settings and easily integrates into the curricula of existing computer classes. MTA Empowers Educators and Motivates Students MTA provides a new standard for measuring and validating fundamental technology knowledge right in the classroom with minimal impact on your budget and teaching resources. MTA helps institutions stand out as innovative providers of high-demand industry credentials and is easily deployed with a simple, convenient, and affordable suite of entry-level technology certification exams. MTA enables students to explore career paths in technology without requiring a big investment of time and resources, while providing a career foundation and the confidence to succeed in advanced studies and future vocational endeavors. In addition to giving students an entry-level Microsoft certification, MTA is designed to be a stepping stone to other, more advanced Microsoft technology certifications, like the Microsoft Certified Technology Specialist (MCTS) certification. Delivering MTA Exams: The MTA Campus License Implementing a new certification program in your classroom has never been so easy with the MTA Campus License. Through the one-time purchase of the 12-month, 1,000-exam MTA Campus License, there’s no more need for ad hoc budget requests and recurrent purchases of exam vouchers. Now you can budget for one low cost for the entire year, and then administer MTA exams to your students and other faculty across your entire campus where and when you want. The MTA Campus License provides a convenient and affordable suite of entry-level technology certifications designed to empower educators and motivate students as they build a foundation for their careers. The MTA Campus License is administered by Certiport, Microsoft’s exclusive MTA exam provider. To learn more about becoming a Microsoft Technology Associate and exam availability, visit www.microsoft.com/learning/mta. www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page xv 5/3/11 12:24:38 PM user-F409 /Users/user-F409/Desktop Student Support Program | xv This page intentionally left blank www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page xvi 5/3/11 12:24:38 PM user-F409 /Users/user-F409/Desktop Acknowledgments MOAC MTA Technology Fundamentals Reviewers We'd like to thank the many reviewers who pored over the manuscript and provided invaluable feedback in the service of quality instructional materials: Yuke Wang, University of Texas at Dallas David Koppy, Baker College Palaniappan Vairavan, Bellevue College Sharon Moran, Hillsborough Community College Harold “Buz” Lamson, ITT Technical Institute Keith Hoell, Briarcliffe College and Queens College— Colin Archibald, Valencia Community College CUNY Catherine Bradfield, DeVry University Online Mark Hufnagel, Lee County School District Robert Nelson, Blinn College Rachelle Hall, Glendale Community College Kalpana Viswanathan, Bellevue College Scott Elliott, Christie Digital Systems, Inc. Bob Becker, Vatterott College Gralan Gilliam, Kaplan Carol Torkko, Bellevue College Steve Strom, Butler Community College Bharat Kandel, Missouri Tech John Crowley, Bucks County Community College Linda Cohen, Forsyth Technical Community College Margaret Leary, Northern Virginia Community College Candice Lambert, Metro Technology Centers Sue Miner, Lehigh Carbon Community College Susan Mahon, Collin College Gary Rollinson, Cabrillo College Mark Aruda, Hillsborough Community College Al Kelly, University of Advancing Technology Claude Russo, Brevard Community College Katherine James, Seneca College www.wiley.com/college/microsoft or xvi | call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) FMTOC.indd Page xvii 5/3/11 12:24:38 PM user-F409 /Users/user-F409/Desktop Brief Contents 1 Understanding Core Database Concepts 1 2 Creating Database Objects 20 3 Manipulating Data 43 4 Understanding Data Storage 61 5 Administering a Database 84 Appendix A 110 Index 111 www.wiley.com/college/microsoft or call the MOAC Toll-Free Number: 1+(888) 764-7001 (U.S. & Canada only) | xvii FMTOC.indd Page xviii 5/3/11 12:24:38 PM user-F409 /Users/user-F409/Desktop FMTOC.indd Page xix 5/3/11 12:24:38 PM user-F409 /Users/user-F409/Desktop Contents Lesson 1: Understanding Core Lesson 3: Manipulating Data 43 Database Concepts 1 Objective Domain Matrix 43 Objective Domain Matrix 1 Key Terms 43 Key Terms 1 Using Queries to Select Data 44 Understanding Database Concepts 2 Combining Conditions 45 Understanding Flat-Type Databases 3 Using the BETWEEN Clause 46 Understanding Hierarchical Databases 3 Using the NOT Clause 47 Understanding Relational Databases 4 Using the UNION Clause 47 Understanding Database Fundamentals 5 Using the EXCEPT and INTERSECT Clauses 48 Understanding Relational Database Concepts 7 Using the JOIN Clause 48 Using the SQL Server Management Studio Interface 9 Using Queries to Insert Data 51 Understanding Data Manipulation Inserting Data 52 Language (DML) 12 Updating Data and Databases 54 Understanding Data Definition Language (DDL) 12 Using the UPDATE Statement 54 Using DDL Statements 13 Deleting Data 55 Skill Summary 16 Using the DELETE Statement 55 Truncating a Table with TRUNCATE TABLE 56 Knowledge Assessment 17 Deleting a Table with DROP TABLE 56 Competency Assessment 19 Using Referential Integrity 56 Proficiency Assessment 19 Skill Summary 57 Knowledge Assessment 57 Lesson 2: Creating Database Competency Assessment 59 Objects 20