Microsoft Windows XP

Transcription

1 SQL Server 2000 MCP

2 1 SQL Server 2000 RAID I/O RAID? A. B. C. D. C Web A. FOR XML SELECT B. SELECT sp_makewebtask HTML C. TAB D. d. SEL_DMO EDI electronic data interchange A 3 SQL Server2000 CORP1 CORP2 CORP1 CORP2 CORP1 ID 21 SalesHistory CORP2 CORP1 A. EXEC sp_addlinkedserver CORP2, SQL Server

3 SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory WHERE CategoryID = 21 B. SELECT CustomerID FROM OPENROWSET SQLOLEDB, CORP2 ; p*word, SELECT CustomerID FROM Archive.dbo.SalesHistory WHERE CategoryID = 21 C. SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory WHERE CategoryID = 21 D. EXEC sp_addserver CORP2 SELECT CustomerID FROM CORP2.Archive.dbo.SalesHistory WHERE CategoryID = 21 C 4 Owners A. BirthDate CHECK B. BirthDate C. Owners BirthDate D. Owners BirthDate D 5 SQL Server2000 A. DDL Wizard B. SQL Profiler Wizard C. D. SQL Show Execution Plan E. SQL Show Server Trace E

4 6 A. REPEATABLE READ. B. C. UPDATE WHERE UPDATE D. SCROLL_LOCKS UPDATE B 7 Size Container Shape Shape size SELECT A. ContainerID B. ContainerID C. Container volume D. Container A 8 Department

5 Employees SQLLogin department Department A. Department B. Department Employees SQLLogin C. WITH CHECK OPTION D. Department INSERT UPDATE E. Employees SQLLogin INSERT UPDATE B 9 CREATE PROCEDURE get_sales_for_title%title int OUTPUT AS = ytd_sales FROM titles WHERE title = 0 RETURN-1 ELSE RETURN0 No Sales Found A. int int EXEC get_sales_for_title Net < 0 PRINT No sales found ELSE PRINT Year to date sales: + B. int int

6 EXEC get_sales_for_title Net OUTPUT < 0 PRINT No sales found ELSE PRINT Year to date sales: + C. int int EXEC get_sales_for_title Net Etiquette,@retval OUTPUT < 0 PRINT No sales found ELSE PRINT Year to date sales: + D. int int = get_sales_for_title Net OUTPUT < 0 PRINT No sales found ELSE PRINT Year to date sales: + D 10 SQL Server 2000 Policy CREATE TABLE Policy PolicyNumber int NOT NULL DEFAULT 0, InsuredLastName char 30 NOT NULL, InsuredFirstName char 20 NOT NULL, InsuredBirthDate datetime NOT NULL, PolicyDate datetime NOT NULL, FaceAmount money NOT NULL, CONSTRAINT PK_Policy PRIMARY KEY PolicyNumber Policy A. INSTEAD OF INSERT

7 B. INSTEAD OF UPDATE C. AFTER UPDATE D. AFTER UPDATE DEFAULT A A. sp_lock sp_who B. sysprocesses sysobjects C. sysprocesses D. sp_monitor A 12 Trey SQL Server 2000 A. syscomments B. C. syscomments public SELECT D. sysobjects public SELECT B 13 Woodgrove SQL

8 A. ORDER BY B. HAVING C. NUMERIC_ROUNDABORT ON D. E. WITH SCHEMABINDING E 14 SQL Server Sales WITH SCHEMABINDING schema-bound INSTEAD OF A. B. C. WITH SCHEMABINDING D. WITH SCHEMABINDING E. INSTEAD OF D 15 Woodgrove Accounts

9 Accounts A. B. API C. SELECT D. Transact-SQL B 16 BookOrders Reports Reports Order LineItem Order LineItem Order LineItem Reports A.. B. C. D. Order LineItem SELECT B 17 SQL Server 2000 A.

10 B. WHERE C. D. SELECT WHERE C 18 SQL Server 2000 Andrea Andrea A. Andrea db_owner B. Andrea db_ddladmin C. Andrea CREATE VIEW D. Andrea ALTER VIEW E. Andrea REFERENCES B 19 IT CandidateSkills A. CandidateID PRIMARY KEY B. CandidateID DateLastUsed PRIMARY KEY C. CandidateID SkillID PRIMARY KEY D. CandidateID SkillID DateLastUsed PRIMARY KEY C 20 SQL Server 2000 Physicians Patients Physicians I/O

11 Create Table A. Create Table Physicians Physicians ID int NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY CLUSTERED, LastName varchar25 NOT NULL, Create Table Patients PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, PrimaryPhysician int NOT NULL, SecondaryPhysician int NOT NULL, CONSTRAINT PK_Patients_Physicians1 FOREIGN KEY PrimaryPhysician REFERENCES Physicians PhysicianID, CONSTRAINT PK_Patients_Physicians2 FOREIGN KEY SecondaryPhysician REFERENCES Physicians PhysicianID B. Create Table Patients PatientID smallint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar25 NOT NULL, FirstName varchar 25 NOT NULL, PrimaryPhysician int NOT NULL, SecondaryPhysician int NOT NULL, Create Table Physicians PhysicianID smallint NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY CLUSTERED, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, CONSTRAINT PK_Physicians_Patients FOREIGN KEY PhysicianID REFERENCES Patients PatientID C. Create Table Patients

12 PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, Create Table Physicians PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, Create Table PatientPhysician PatientPhysicianID bigint NOT NULL CONSTRAINT PK_PatientsPhysicians PRIMARY KEY CLUSTERED, PhysicianID int NOT NULL, PatientID bigint NOT NULL, PrimaryPhysician bit NOT NULL, FOREIGN KEY PhysicianID REFERENCES Physicians PhysicianID, FOREIGN KEY PatientID REFERENCES Patients PatientID D. Create Table Patients PatientID int NOT NULL PRIMARY KEY, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, Create Table Physicians PhysicianID int NOT NULL PRIMARY KEY, LastName varchar 25 NOT NULL, FirstName varchar 25 NOT NULL, Create Table PatientPhysician PhysicianID int NOT NULL REFERENCES Physicians PhysicianID, PatientID int NOT NULL REFERENCES Patients PatientID, PrimaryPhysician bit NOT NULL,

13 CONSTRAINT PK_PatientsPhysicians PRIMARY KEY PhysicianID, PatientID D 21 Adventure Works Format File Format File 1 SQLINT 0 4, 1 EmployeeID 2 SQLCHAR 0 50, 2 Firstname SQL_Latin1_General_CP1_AS 3 SQLCHAR 0 50, 3 Lastname SQL_Latin1_General_CP1_AS 4 SQLCHAR 0 10, 4 SSN SQL_Latin1_General_CP1_AS 5 SQLDATETIME Hiredate Employee Employee A. SQL-DMO Microsoft Visual Basic Table Table ImportData B. SQL-DMO Microsoft Visual Basic Database Database CopyData C. Microsoft Visual Basic Package Connection Package BulkInsertTask Package Execute D. Microsoft Visual Basic Package Connection Package ExecuteSQLTask2 ExecuteSQLTask2 Execute C 22 SQL Server 2000 Sales Sales 150,000 Sales

14 SQL A. EXEC sp_updatestats 'resample' B. CREATE STATISTICS Sales WITH FULLSCAN C. Sp_autostats Sales D. UPDATE STATISTICS Sales WITH FULLSCAN ALL A 23 PhoneNumbers 100,000 ALTER TABLE [dbo]. [PhoneNumbers] WITH NOCHECK ADD CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED [FirstName], [LastName], ON [PRIMARY] CREATE UNIQUE INDEX [IX_PhoneNumbers] ON [dbo].[phonenumbers] [PhoneNumberID] ON [PRIMARY] W

15 A. PRIMARY KEY LastName FirstName B. City C. AreaCode, Exchange, Number D. PhoneNumberID E. PRIMARY KEY F. SQL UPDATE STATISTICS FULLSCAN ALL A B 24 Employee Employee Employee A. GRANT, REVOKE, DENY B. db_datareader C. Employee D. SQL Employee C 25 Contoso. HumanResources...,..,...

16 ,.,....? A. SELECT l.locationname, e.firstname, e.lastname FROM Employee AS e LEFT OUTER JOIN Location AS 1 ON e.locationid= l.locationid ORDER BY l.locationname, e.lastname, e.firstname B. SELECT l.locationname, e.firstname, e.lastname FROM Location AS 1 LEFT OUTER JOIN EMPLOYEE AS 1 ON e.locationid= l.locationid ORDER BY l.locationname, e.lastname, e.firstname C. c. SELECT l.locationname, e.firstname, e.lastname FROM Employee AS e FULL OUTER JOIN Location AS 1 ON e.locationid= l.locationid ORDER BY l.locationname, e.lastname, e.firstname D. d. SELECT l.locationname, e.firstname, e.lastname FROM Employee AS e CROSS JOIN Location AS 1 ORDER BY l.locationname, e.lastname, e.firstname E. e. SELECT l.locationname, e.firstname, e.lastname FROM Employee AS e, Location AS 1 ORDER BY l.locationname, e.lastname, e.firstname C 26 SQL Server2000. Microsoft Windows 2000 Server... SQL Server.,,.,.? A. SQL Server Access Methods..

17 B. sp_configure SQL Server lcok C. SQL Profiler, SQL:BatchCompleted RPC:Completed, EventClass TextData. SQL Profiler Index Tuning Wizard.. D. E., 100 FILLFACTOR C 27. executive, administrative labor..,. News,. News. CREATE TABLE News NewsID int NOT NULL, NewsText varchar 8000 NOT NULL, EmployeePositionType char 15 NOT NULL, DisplayUntil datetime NOT NULL, DateAdded datetime NOT NULL DEFAULT getdate, CONSTRAINT PK_News PRIMARY KEY NewsID News,,..? A., B. C. EmpoyeePositionType SELECT. D. News. B 28., SurveyDate.,..? A., FETCH RELATIVE 5 B. SELECT.

18 C. rowcount 5. SELECT D. SELECT TOP 5 A 29 SQL Server EndOfMonthSales. EndOfMonthSales : GRANT EXECUTE ON EndOfMonthSales TO SalesDept Andrea, SalesDept.Andrea Marketing,Marketing EndOfMonthSales.Andrea..? A. Andrea db_datareader, REFERENCES. B. Andrea Marketing. C., Andrea SELECT D., Andrea REFERENCES B 30, Employee. Employee : CREATE TABLE Employee EmployeeID int NOT NULL; EmpType char 1 NOT NULL, EmployeeName char 50 NOT NULL, Address char 50 NULL, Phone char 20 NULL, CONSTRAINT PK_Employee PRMARY KEY Employee ID EmpType,.,,.? A., WITH ENCRYPTION B., WITH CHECK OPTION C., SCHEMABINDING D., convering

19 E.. B 31. SQL., : SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P JOIN AgentPolicy AS AP ON P.PolicyNumber = AP.PolicyNumber WHERE IssueState = IL AND PolicyDate BETWEEN 1/1/2000 AND 3/1/2000 AND FaceAmount > :? A. BETWEEN B. join HASH C. Policy WITH INDEX0 D. Policy E. Policy DBCCDBREINDEX D 32,.. Dogs : CREATE TABLE [dbo].[dogs] [DogID] [int] NOT NULL, [BreedID] [int] NOT NULL, [DateofBirth] [datetime] NOT NULL,

20 [WeightAtBirth] [decimal] 5, 2 NOT NULL, [NumberOfSiblings] [int] NULL, [MotherID] [int] NOT NULL, [FatherID] [int] NOT NULL ON [PRIMARY] ALTER TABLE [dbo].[dogs] WITH NOCHECK ADD CONSTRAINT [PK_Dogs]PRIMARY KEY CLUSTERED [DogID] ON [PRIMARY] MotherID FatherID..? A. Dogs, AFTER INSERT MotherID FatherID. B. MotherID FatherID CHECK. C. FOREIGNKEY : MotherID, FatherID. DogID. D. MotherID, FatherID. C 33 Proseware.. company ,,...? A.,. WITHRECOMPILE B. C. D. Company., A

21 34., 1%... :,.,.? A. Return B. int Replace lines with the following: = 0 Else Return c. int Replace lines with the following: = = 0

22 Else C. Return 5 D. int Replace line 9 with the following: SET Balance = Balance * = Count * Replace line 15 with the following: 5 C 35 Tailspin Toys, :.? A. CustomerContact, CustomerID,ContactName Phone B. OrderDetails PRIMARYKEY, OrderID,ProductID CustomerID C. OrderDetails PRIMARYKEY, IDENTITY OrderDetails D. OrderDetails CustomerID. E. OrderDetails Quantity. Quantity Orders D 36, XML.XML :

23 XML, Products. Products :? A. CREATE PROCEDURE spaddcatalogitems AS varchar 8000 int EXEC INSERT INTO Products EXEC INSERT INTO Products SELECT * FROM /ROOT/Category/Product, 1 WITH Products EXEC END B. CREATE PROCEDURE varchar 8000 AS BEGIN int EXEC INSERT INTO Products SELECT * FROM /ROOT/Category/Product, 1 WITH ProductID int./@productid, CategoryID int../@categoryid, [Description] varchar 100./@Description EXEC END

24 C. CREATE PROCEDURE varchar 8000 AS BEGIN INSERT INTO Products SELECT * FROM /ROOT/Category/Product, 1 WITH ProductID int, Description varchar 50 END D. CREATE PROCEDURE varchar 8000 AS BEGIN INSERT INTO Products SELECT* FROM /ROOT/Category/Product,1 WITH Products END B 37 Sales CREATE TABLE Customers CustomerID int NOT NULL, CustomerName varchar30 NOT NULL, ContactName varchar30 NULL, Phone varchar20 NULL, Country varchar30 NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY CustomerID A. SELECT Country FROM Customers GROUP BY Country HAVING COUNT Country>10 B. SELECT TOP 10 Country FROM Customers

25 C. SELECT TOP 10 Country FROM Customers FROM SELECT DISTINCT Country FROM Customers AS X GROUP BY Country HAVING COUNT*> 10 D. SET ROWCOUNT 10 SELECT Country, COUNT * as NumCountries FROM Customers GROUP BY Country ORDER BY NumCountries, Desc A 38 Inventory Inventory Inventory CREATE PROCEDURE int AS BEGIN int BEGIN TRAN = Available FROM Inventory WITH HOLDLOCK WHERE InventoryID > 0 UPDATE Inventory SET Available 1 WHERE InventoryID COMMIT TRAN END Process ID 53 A. B. UPDLOCK C. REPEATABLEREAD D. SERIALIZABLE E. REPEATBLEE READ B 39 SQL Server 2000

26 Order OrderDetails 9 GB Order A. B. Order C. OrderDetail D. Order Ord E. 2 D E 40 Sales VacuumSales Employee VacuumSales Employee Employee IsActive Employee EmployeeID VacuumS ID A. ID B. VacuumSales EmployeeID CHECK C. VacuumSales EmployeeID Emoployee EmployeeID D. VacuumSales FOR INSERT EmployeeID Inserted Employee IsActive D 41 CREATE TABLE Trades TradeID int IDENTITY1,1PRIMARY KEY NONCLUSTERED NOT NULL, TradeDate datetime NULL, SettlementDate datetime NULL, Units decimal18, 4 NULL, Symbol char5 NULL,

27 ClientID int NULL, BrokerID int NULL CREATE CLUSTERED INDEX c_symbol ON Trades Symbol Trades SQL DBCC SHOWCONTIG Trades DBCC SHOWCONTIG scanning Trades table..... Table: Trades ; index ID:1, database ID:12 Table level scan performed. -Pages Scanned :104 -Extents Scanned :16 -Extent Switches :24 -Avg. Pages per Extent :6.5 -Scan Density[Best Count:Actual Count] :52.00%[13:25] -Logical Scan Fragmentation :7.69% -Extent Scan Fragmentation :43.75% -Avg. Bytes Free per page : Avg. Page Density full :94.32% DBCC execution completed. If DBCC printed error messages, contact your system Trades A. DBCC DBREINDEX B. UPDATE STATISTICS C. DROP STATISTICS D. DBCC INDEXDEFRAG E. DROP INDEX CREATE INDEX A 42 SQL Server 2000 Employee CREATE VIEW Employee AS SELECT P.SSN, P.LastName, P.FirstName, P.Address, P.City, P.State, P.Birthdate, E.EmployeeID, E.Department, E.Salary FROM Person AS P JOIN Employees AS E ON P.SSN = E.SSN Employees Person

28 CREATE TABLE Person SSN char11 NOT NULL PRIMARY KEY LastName varchar 50 NOT NULL, FirstName varchar 50 NOT NULL, Address varchar 100 NOT NULL, City varchar 50 NOT NULL, State char 2 NOT NULL, Birthdate datetime NOT NULL CREATE TABLE Employees EmployeeID int NOT NULL PRIMARY KEY, SSN char 11 UNIQUE NOT NULL, Department varchar 10 NOT NULL, Salary money NOT NULL, CONSTRAINT FKEmpPER FOREIGN KEY SSNREFERENCES Person SSN INSERT A. AFTER B. INSTEAD OF C. Person Employees INSTEAD OF D. WITH CHECK E. WITH SCHEMABINDING B 43 SQL Server 2000 A. CREATE PROCEDURE decimal AS BEGIN varchar50 UPDATE Stocks SET CurrentPrice WHERE StockID StockID AND CurrentPrice Price

29 IF <> 0 RAISERROR Error %d occurred updating Stock %d., WITH LOG > 0 BEGIN = Stock + + updated to + +. EXEC master.. xp_logevent END END B. CREATE PROCEDURE decimal AS BEGIN UPDATE Stocks SET CurrentPrice WHERE StockID StockID AND CurrentPrice Price <> 0 PRINT ERROR + STR@@ERROR + occurred updating Stock > 0 PRINT Stock + + updated to + +. END C. CREATE PROCEDURE decimal AS BEGIN varchar50 UPDATE Stocks SET CurrentPrice WHERE StockID StockID AND CurrentPrice Price = <> 0 BEGIN = Error + STR@Err + occurred updating Stock + +. EXEC master..xp_logevent END > 0 BEGIN = Stock + + updated to + +. EXEC master.. xp_logevent END END D. CREATE PROCEDURE decimal AS BEGIN varchar 50 UPDATE Stocks SET CurrentPrice

30 WHERE StockID AND CurrentPrice @RCount <> 0 RAISEERROR Error %d occurred updating Stock %d., WITH LOG > 0 BEGIN = Stock + + update to + +. EXEC master.. xp_logevent END END D 44 SQ A. WITH RECOMPILE B. UPDATE STATISTIC C. sp_recompile D. DBCC REINDEX C