目錄 C ontents Chapter 01 1-1 MTA...1-2 1-2...1-3 1-3...1-5 1-4...1-10 Chapter 02 2-1...2-2 2-2...2-3 2-3...2-7 2-4...2-11...2-16 Chapter 03 3-1...3-2 3-2...3-8 3-3 views...3-16 3-4...3-24...3-33 Chapter 04 4-1 SELECT...4-2 4-2 INSERT...4-11 4-3 UPDATE...4-12 4-4 DELETE...4-14...4-15 iii
MTA Exam 98-364 資料庫管理認證實戰寶典 Chapter 05 5-1...5-2 5-2...5-6 5-3...5-8...5-14...5-17 Chapter 06 6-1...6-2 6-2 SQL Server...6-6 6-3...6-10 6-4...6-22...6-29 Chapter 07 7-1... 7-2 7-2...7-6 7-3...7-9...7-12 Chapter 08...8-2...8-15...8-30...8-43...8-56...8-68...8-84...8-98...8-113...8-127 Appendix A SQL Server 2008 iv
01 Database Administration Fundamentals 1-1 MTA 1-2 1-3 1-4
MTA Exam 98-364 資料庫管理認證實戰寶典 1-1 MTA 1. Microsoft Technology AssociateMTA Microsoft Certiport 19 128 2. MTA IT Microsoft 3. MCP 1. MTA IT 40% 2. Microsoft 3. IT 1-2
資料庫管理師核心能力認證 CHAPTER 01 1. 2. 80% 20% 3. 45 Min / 35 4. 100 70 5. MTA 1-2 MTA jjdevelopers Software Developer Fundamentals, 98-361 Visual Studio 2008VB/C# Windows Developer Fundamentals, 98-362 WPF Visual Studio 2008 Web Developer Fundamentals, 98-363 Visual Studio 2008VB/C# 1-3
MTA Exam 98-364 資料庫管理認證實戰寶典 jjdatabase Database Administration Fundamentals, 98-364 Manipulating data SQL Server 2008 jjit Professionals Windows Server Administration Fundamentals, 98-365 Active Directory Windows Serve 2008 Networking Fundamentals, 98-366 Windows Server 2008 Security Fundamentals, 98-367 Windows Server 2008 1-4
資料庫管理師核心能力認證 CHAPTER 01 1-3 Q MTA A MTA MCTS MTA MCP Q MTA A MTA Q MTA A MTA MCTS Q MTA A MTA / MTA Q MTA A 1 MTA http:// www. microsoft. com/ learning/ en/ us/ certification/ mta. aspx#certification 2 MTA MTA ERKs 3 http://www.microsoft.com/education/facultyconnection/default. aspx?c1=en-us&c2=0 1-5
MTA Exam 98-364 資料庫管理認證實戰寶典 Q MTA A MTA Certiport MTA MTA proctor MTA MTA MTA MTA Q A MTA 35 Q A 45 Q A www. certiport.com Q MTA A MTA E-certificate http://mcp.microsoft.com/ Windows Live 1-6
資料庫管理師核心能力認證 CHAPTER 01 Q A Windows Live Certiport Certiport Q Windows Live A email Windows Live http://mcp.microsoft.com/ 0800-008833 Q MTA A MTA Q MTA A MTA 70 70% 70% 1-7
MTA Exam 98-364 資料庫管理認證實戰寶典 Q MTA A 1 2 3 Q A MTA 100 70 70 70 www.certiport.com Q A Q A Q A 1-8
資料庫管理師核心能力認證 CHAPTER 01 Q MTA Cisco A Cisco CCNA Cisco Cisco Cisco Cisco PearsonVUE Cisco 250 MTA MTA Cisco Q CompTIA A CompTIA MCSEMicrosoft Certified Systems Engineer MCSAMicrosoft Certified Systems Administrator MCTS MTA IT 1-9
MTA Exam 98-364 資料庫管理認證實戰寶典 1-4 jjdatabase Administration Fundamentals Database Administration Fundamentals98-364 SQL Server 2008 MTA MTA Microsoft IDC MTA 40%IT MTA IT 19 128 MTA http://www.microsoft.com/taiwanhttp://www.gotop.com.tw 1-10
04 Manipulating Data 4-1 SELECT 4-2 INSERT 4-3 UPDATE 4-4 DELETE
MTA Exam 98-364 資料庫管理認證實戰寶典 4-1 SELECT Select SELECT SELECT [ ALL DISTINCT ] column1[,column2,.] [ INTO new_table ] FROM table [,table2,] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC DESC ] ] [ TOP expression [ PERCENT ] [ WITH TIES ] ] <select_list> <select_list> = { * { table_name view_name table_alias }.* { [ { table_name view_name table_alias }. ] { column_name $IDENTITY $ROWGUID } udt_column_name [ {. } { { property_name field_name } method_nameargument [,...n] } ] expression [ [ AS ] column_alias ] } column_alias = expression } [,...n ] 4-2
處理資料 CHAPTER 04 ALL DISTINCT INTO FROM WHERE ALL GROUP BY HAVING ORDER BY TOPexpression[ PERCENT ] [ WITH TIES ] < select_list> * expression 4096 FROM * table_name view_name table_alias.* column_name table_name view_name table_alias 4-3
MTA Exam 98-364 資料庫管理認證實戰寶典 expression expression $IDENTITY FROM IDENTITY $IDENTITY stu.$identity GUID $ROWGUID udt_column_name {. } property_name field_name method_name column_ alias FROM ROWGUIDCOL $ROWGUID stu.$rowguid Common Language RuntimeCLR CLR. CLR EXECUTE udt_column_name udt_column_name udt_column_name method_name mutator student stu column_alias ORDER BY WHEREGROUP BY HAVING 4-4
處理資料 CHAPTER 04 * UNIONEXCEPT INTERSECT 4-5
MTA Exam 98-364 資料庫管理認證實戰寶典 SELECT SELECT 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE WITH ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. ORDER BY 11. TOP SELECT SELECT * FROM student; student 4-6
處理資料 CHAPTER 04 SELECT name, number, grade FROM student WHERE avg < 60; student namenumbergrade avg 60 SELECT LIKE LIKE WHERE % % % SELECT name, number, address FROM student WHERE address like ' %'; student namenumberaddress address 4-7
MTA Exam 98-364 資料庫管理認證實戰寶典 SELECT * FROM student WHERE address like ' %'; student address SELECT DISTINCT grade FROM student; student grade SELECT ALL grade FROM student; student grade grade ALL DISTINCT ALL 4-8
處理資料 CHAPTER 04 WHERE HAVING SQL subquery SELECT 1" FROM " " WHERE " 2" [ ] SELECT " 1" FROM " " WHERE [ ] SELECT SUMFROM WHERE IN SELECT FROM WHERE area_name = ' ' 4-9
處理資料 CHAPTER 04 4-2 INSERT INSERT INSERT INTO student VALUES' ', '01', ' 1 '; student namenumberaddress INSERT INTO student VALUES' ', '01', ' 1 ',' ', '02', ' 2,' ', '03', ' 35 '; student namenumberaddress 4-11
MTA Exam 98-364 資料庫管理認證實戰寶典 INSERT INTO student_new SELECT * FROM student WHERE name like ' %'; student student_new 4-3 UPDATE UPDATE WHERE UPDATE student SET grade=85 WHERE number'03', '05' student 03 05 grade 85 4-12
處理資料 CHAPTER 04 WHERE UPDATE student SET grade=85 WHERE student grade 85 UPDATE UPDATE student SET grade=grade+5 student grade 4-13
本章習題 1. Product ID Name Quantity 1234 Spoon 33 2615 Fork 17 3781 Plate 20 4589 Cup 51 SELECT COUNT* FROM Product WHERE Quantity >18 (A) 1 (B) 2 (C) 3 (D) 4 2. SELECT (A) FROM (B) ORDER BY (C) UNION (D) WHERE 4-15
本章習題 3. SELECT WHERE (A) LIKE (B) FIND (C) BETWEEN (D) INCLUDES 4. Customer CustomerID FirstName DateJoined CustomerID SELECT CustomerIDFirstNameDateJoined FROM Customer (A) (B) FirstNmae (C) DateJoined (D) 4-16
本章習題 5. Employee EmployeeID EmployeeName (A) SELECT COUNTrowsFROM Employee (B) SELECT COUNT* FROM Employee (C) SELECT * FROM Employee (D) SELECT SUM*FROM Employee 6. Employee SELECT * FROM Employee (A) (B) (C) (D) 4-17
本章習題 7. CREATE TABLE Product ID INTEGER PRIMARY KEY, Name VARCHAR20, Quantity INTEGER Product ID Name Quantity 1234 Ap les 33 2615 Oranges 0 3781 Pears 29 4589 Plums SELECT Name FROM Product WHERE Quantity IS NOT NULL (A) 0 (B) 1 (C) 2 (D) 3 (E) 4 4-18
本章習題 8. SELECT DepartmentName FROM Department WHERE DepartmentID = SELECT DepartmentID FROM Employee WHERE EmployeeID = 1234 (A) (B) (C) (D) 9. (A) SELECT Name, TOP Price FROM Product (B) SELECT Name, BOTTOM Price FROM Product (C) SELECT Name, Price FROM Product ORDER BY Price ASC (D) SELECT Name, Price FROM Product ORDER BY Price DESC 4-19
本章習題 10. student id 10 31 name (A) SELECT name FROM students WHERE id>10 AND id<31 (B) SELECT name FROM students WHERE id IN10,31 (C) SELECT name FROM students WHERE id=10 OR id=31 (D) SELECT name FROM students WHERE id=10 OR 31 11. LIKE student L name (A) SELECT * FROM students WHERE name LIKE 'L' (B) SELECT * FROM students WHERE name LIKE 'L%' (C) SELECT * FROM students WHERE name LIKE '&L' (D) SELECT students WHERE name LIKE 'L%' 12. ORDER BY (A) (B) 4 (C) 16 (D) 256 4-20
本章習題 13. SELECT ORDER BY FROM WHERE ORDER BY (A) ORDER BY SELECT (B) ORDER BY WHERE (C) ORDER BY FROM (D) SQL Server SELECT 14. student (A) SELECT ROWCOUNT FROM student (B) SELECT CountRows FROM student (C) SELECT TOTALROWS FROM student (D) SELECT COUNT*FROM student 15. SELECT GROUP BY (A) HAVING (B) COUNTED (C) (D) COMPUTING 4-21
本章習題 16. Customer Order DELETE FROM Order WHERE CustomerID = 209 (A) Customer CustomerID209 (B) Order CustomerID209 (C) Order CustomerID209 (D) Order CustomerID209 Customer CustomerID209 17. (A) DELETE FROM Employee WHERE Phone IS NULL (B) DELETE FROM Employee WHERE Phone = NULLABLE (C) DELETE FROM Employee WHERE Phone = '&' (D) DELETE FROM Employee WHERE Phone IS NOT NULL 4-22
本章習題 18. Product Furniture (A) (B) (C) (D) 19. Student 100 FirstName NULL DELETE FROM Student (A) (B) (C) (D) FirstName NULL 4-23