Designing And Implementing Databases With SQL Server 2000 Quiz

Set the data type of the column to uniqueidentifier.

Set the data type of the column to int. Create a user-defined function that selects the maximum order number in the table.

Set the data type of the column to int. Create a NextKey table, and add a NextOrder column to the table. Set the data type of the NextOrder column to int. Create a stored procedure to retrieve and update the value held in the NextKey.

Set the data type of the column to int, and set the IDENTITY property for the column.

CREATE STATISTICS Sales WITH FULLSCAN

Sp_autostats �Sales�

UPDATE STATISTICS Sales WITH FULLSCAN ALL

Move all the columns from the Classroom table to the Courses table, and drop the Classroom table.

Create a new table named Instructors. Include an InstructorID column, and InstructorName column, and an OfficePhone column. Add an InstructorID column to the Courses table.

Remove the PRIMARY KEY constraint from the Courses table, and replace the PRIMARY KEY constraint with a composite PRIMARY KEY constraint based on the CourseID and CourseTitle.

Remove the ClassroomID column, and base the PRIMARY KEY constraint on the ClassroomNumber and ClassTime columns.

Execute the DBCC UPDATEUSAGE statement against the database to update the sysindexes system table.

Execute the DBCC SHOW_STATISTICS statement to find high-density indexes. Drop the high-density indexes.

Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.

Use SQL profiler to find table scans. Add indexes to tables that were found to have table scans.

Add your login name to the HR database role.

Qualify the trigger name with the trigger owner in the DROP TRIGGER statement.

Disable the trigger before executing the DROP TRIGGER statement.

Define the trigger number in the DROP TRIGGER statement.

Use the ALTER TABLE statement and the ON UPDATE clause to modify the Products table.

Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Products table.

Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to modify the Companies table.

Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Companies table.

Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to modify the Products table.

Rewrite the query to eliminate BETWEEN keyword.

Add a join hint that includes the HASH option to the query.

Add the WITH (INDEX(0)) table hint to the Policy table.

Update statistics on the Policy table.

SELECT Country FROM Customers GROUP BY Country HAVING COUNT (Country)>10

SELECT TOP 10 Country FROM Customers

SELECT TOP 10 Country FROM Customers FROM (SELECT DISTINCT Country FROM Customers) AS X GROUP BY Country HAVING COUNT(*)> 10

SET ROWCOUNT 10 SELECT Country, COUNT (*) as �NumCountries� FROM Customers GROUP BY Country ORDER BY NumCountries, Desc

BEGIN TRANSACTION Order INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) SAVE TRANSACTION Order

INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

BEGIN TRANSACTION INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) IF (@@Error = 0) BEGIN INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END ELSE ROLLBACK TRANSACTION END

BEGIN TRANSACTION INSERT INTO Order VALUES (@ID, @CustomerID, @OrderDate) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION BEGIN TRANSACTION INSERT INTO LineItem VALUES (@ItemID, @ID, @ProductID, @Price) IF (@@Error = 0) COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

Create a stored procedure that requires ContainerID as an argument and returns the volume of the container

Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.

Add a column named volume to the Container table. Create a trigger that calculates and store the volume in this column when a new container is inserted into the table.

Add a computed column to the Container table that calculates the volume of the container.

Use a scrollable static cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases.

Use a scrollable dynamic cursor to retrieve the list of tickets. Use positioned updates within the cursor to make purchases

Use a stored procedure to retrieve the list of tickets. Use a second stored procedure to make purchases.

Use a user-defined function to retrieve the list of tickets. Use a second stored procedure to make purchases

Create a view that displays the order information for a customer.

Create a scalar user-defined function that retrieves the order information for a customer.

Grant SELECT permissions on the Order and LineItem tables to the customers.

Create a stored procedure that retrieves the order information for a customer.

Create a trigger on the Department table that checks whether the database login of the user performing the insert or update operation belongs to a member of that department.

Include the WITH CHECK OPTION clause in the view definition.

Create a view that includes all columns in the Department table and the SQLLogin column from the Employees table.

Grant INSERT and UPDATE permissions on the Department table.

Execute each query in SQL Query Analyzer, and use the SHOWPLAN_TEXT option. Use the output for the report.

Execute each query in SQL Query Analyzer, and use the Show Execution Plan option. Use output for the report

Run the Index Tuning Wizard against a Workload file. Use the output for the report.

Execute the DBCC SHOW_STATISTICS statement. Use the output for the report.

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

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

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

SELECT l.LocationName, e.FirstName, e.LastName FROM Employee AS e CROSS JOIN Location AS 1 ORDER BY l.LocationName, e.LastName, e.FirstName

Create a cursor within an explicit transaction, and set the transaction isolation level to REPEATABLE READ.

Create a cursor that uses optimistic concurrency and positioned updates. In the cursor, place the positioned UPDATE statements within an explicit transaction.

Create a cursor that uses optimistic concurrency. In the cursor, use UPDATE statements that specify the key value of the row to be updated in the WHERE clause, and place the UPDATE statements within an implicit transaction.

Create a cursor that uses positioned updates. Include the SCROLL_LOCKS argument in the cursor definition to enforce pessimistic concurrency. In the cursor, place the positioned UPDATE statements within an implicit transaction.

Use a cursor to retrieve all of the data for the survey. Use FETCH RELATIVE 5 statement to select the summary data from the cursor.

Use a SELECT INTO statement to retrieve the data for the survey into a temporary table. Use a SELECT TOP 1 statement to retrieve the first row from the temporary table.

Set the query rowcount to five. Use a SELECT statement to retrieve and summarize the survey data.

Use a SELECT TOP 5 statement to retrieve and summarize the survey data.

Create an AFTER INSERT trigger on the Dogs table that rolls back the transaction of the MotherID or FatherID column is not valid.

Create a table-level CHECK constraint on the MotherID and FatherID columns.

Create two FOREIGN KEY constraints: one constraint on the MotherID column and one constraint on the FatherID column. Specify that each constraint reference the DogID column.

Create a rule and bind it to the MotherID. Bind the same rule to the FatherID column.

INSERT INTO Country (CountryName) SELECT DISTINCT Country FROM Customers

SELECT (*) AS ColID, c1.Country INTO Country FROM (SELECT DISTINCT Country FROM Customers)AS c1, (SELECT DISTINCT Country FROM Customers) AS c2, WHERE c1.Country >=c2.Country GROUP BY c1.Country ORDER BY 1

DECLARE @Country char (20) DECLARE cursor_country CURSOR FOR SELECT Country FROM Customers OPEN cursor_country FETCH NEXT FROM cursor_country INTO @Country WHILE (@@FETCH_STATUS -1) BEGIN If NOT EXISTS (SELECT CountryID FROM Country WHERE CountryName = @Country) INSERT INTO Country (CountryName) VALUES (@Country) FETCH NEXT FROM cursor_country INTO @Country END CLOSE cursor_country DEALLOCATE cursor_country

Create a trigger on the Reservations table to create the History table entries.

Create a cascading referential integrity constraint on the Reservations table to create the History table entries.

Create a view on the Reservations table. Include the WITH SCHEMABINDING option in the view definition.

Create a view on the Reservations table. Include the WITH CHECK OPTION clause in the view definition.

Create a stored procedure that requires a postal code as a parameter. Include the WITH RECOMPILE option when the procedure is created.

Create one stored procedure for each postal code

Create one view for each postal code.

Split the Company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.

CREATE TABLE Physicians ( Physicians ID int NOT NULL CONSTRAINT PK_Physicians PRIMARY KEY CLUSTERED, LastName varchar(25) NOT NULL, ) GO 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) )

. CREATE TABLE Patients ( PatientID smallint 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, ) GO 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) )

CREATE TABLE Patients ( PatientID bigint NOT NULL CONSTRAINT PK_Patients PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID int NOT NULL CONSTRAINT PK_Physician PRIMARY KEY CLUSTERED, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO 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) )

CREATE TABLE Patients ( PatientID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE Physicians ( PhysicianID int NOT NULL PRIMARY KEY, LastName varchar (25) NOT NULL, FirstName varchar (25) NOT NULL, ) GO CREATE TABLE PatientPhysician ( PhysicianID int NOT NULL REFERENCES Physicians (PhysicianID), PatientID int NOT NULL REFERENCES Patients (PatientID), PrimaryPhysician bit NOT NULL, CONSTRAINT PK_PatientsPhysicians PRIMARY KEY (PhysicianID, PatientID) )

Use the bcp utility, and specify the �t option.

Use the BULK INSERT statement, and specify the FIRE_TRIGGERS argument.

Use the SQL-DMO BulkCopy2 object and set the TableLock property to TRUE.

Use Data Transformation Services to create two Transform Data tasks.

Create a stored procedure to retrieve all of the data that is loaded into the list box.

Use an API server-side cursor to retrieve the data that is loaded into list box.

Retrieve all of the data at once by using a SELECT statement, and then load the data into the list box.

Use a Transact-SQL server-side cursor to retrieve the data that is loaded into the list box.

Create a view, and include the WITH ENCRYPTION clause.

Create a view, and include the WITH CHECK OPTION clause.

Create a view, and include the SCHEMABINDING clause.

Create a view, and build a covering index on the view.

Replace the lines 12-17 of the stored procedure with the following: Return @@ROWCOUNT

Replace line 5 of the stored procedure with the following: DECLARE @Err int, @Count int Replace lines 12-17 with the following: SELECT @Err = @@ERROR, @Count = @@ROWCOUNT IF @Err = 0 Return @Count Else Return @Err

Replace line 5-6 of the stored procedure with the following: DECLARE @count int Replace lines 12-17 with the following: SET @Count = @@ROWCOUNT If @@ERROR = 0 Return @Count Else Return �1

Replace line 5 of the stored procedure with the following: Return @@Error

Use a DELETE statement to remove the data from the table.

Use a TRUNCATE TABLE statement to remove the data from the table.

Use a DROP TABLE statement to remove the data from the table.

Use an updateable cursor to access and remove each row of data from the table.

Create a stored procedure that accepts a parameter with a default value of 101.

Create two stored procedures, and specify that one of the stored procedures should accept a parameter and that the other should not.

Create a user-defined function that accepts a parameter with a default value of 101.

Create a default of 101 on the FlightTimes table.

UPDATE Products SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

. UPDATE Products p1 SET [Description] = p2.[Description], UnitPrice = p2.UnitPrice FROM Products, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate GO

UPDATE Products p1 SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM (SELECT [Description], UnitPrice FROM PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID) GO TRUNCATE TABLE PendingProductUpdate GO

UPDATE p1 SET p1.[Description] = p2.[Description], p1.UnitPrice = p2.UnitPrice FROM Products p1, PendingProductUpdate p2 WHERE p1.ProductID= p2.ProductID GO TRUNCATE TABLE PendingProductUpdate

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Msg varchar(50) UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @ StockID AND CurrentPrice @ Price IF @@ERROR 0 RAISERROR (�Error %d occurred updating Stock %d.�, 10, 1, @@ERROR, @StockID) WITH LOG IF @@ROWCOUNT > 0 BEGIN SELECT @Msg = �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� EXEC master. . xp_LOGEVENT 50001, @Msg END END

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @ StockID AND CurrentPrice @ Price IF @@ERROR 0 PRINT �ERROR� + STR(@@ERROR) + �occurred updating Stock� +STR (@StockID)+ �.� IF @@ROWCOUNT > 0 PRINT �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� END

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Err int, @RCount int, @Msg varchar(50) UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @ StockID AND CurrentPrice @ Price SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT IF @Err 0 BEGIN SELECT @Msg = �Error� + STR(@Err) + �occurred updating Stock� + STR (@StockID) + �.� EXEC master..xp_logevent 50001, @Msg END IF @RCOUNT > 0 BEGIN SELECT @Msg = �Stock� + STR (@StockID) + �updated to� + STR (@Price) + �.� EXEC master. . xp_LOGEVENT 50001, @Msg END END

CREATE PROCEDURE UpdateStockPrice @StockID int, @Price decimal AS BEGIN DECLARE @Err int, @RCount int, @Msg varchar (50) UPDATE Stocks SET CurrentPrice = @Price WHERE StockID = @StockID AND CurrentPrice @Price SELECT @Err = @@ERROR, @RCount = @@ROWCOUNT If @Err 0 RAISEERROR (�Error %d occurred updating Stock %d.�, 10, 1, @Err, @StockID) WITH LOG If @RCount > 0 BEGIN SELECT @Msg = �Stock� + STR (@StockID) + �update to� + STR (@Price) + �.� EXEC master. . xp_logevent 50001, @Msg END END

SELECT m.ModelName, c.ColorName FROM Colors AS c FULL OUTER JOIN Models AS m ON c.ColorID = m.ColorID ORDER BY m.ModelName, c.ColorName

SELECT m.ModelName, c.ColorName FROM Colors AS c CROSS JOIN Models AS m ORDER BY m.ModelName, c.ColorName

. SELECT m.ModelName, c.ColorName FROM Colors AS m INNER JOIN Colors AS c ON m.ColorID = c.ColorID ORDER BY m.ModelName, c.ColorName

SELECT m.ModelName, c.ColorName FROM Colors AS c LEFT OUTER JOIN Models AS m ON c.ColorID = m.ColorID UNION SELECT m.ModelName, c.ColorName FROM Colors AS c RIGHT OUTER JOIN Models AS m ON c.ColorID = m.ColorID ORDER BY m.ModelName, c.ColorName

DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title �Net Etiquette�, @ytd IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO

DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title �Net Etiquette�, @ytd OUTPUT IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO

DECLARE @retval int DECLARE @ytd int EXEC get_sales_for_title �Net Etiquette�,@retval OUTPUT IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO

DECLARE @retval int DECLARE @ytd int EXEC @retval = get_sales_for_title �Net Etiquette�, @ytd OUTPUT IF @retval < 0 PRINT �No sales found� ELSE PRINT �Year to date sales: � + STR (@ytd) GO

SELECT TOP 20 PERCENT LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

SELECT TOP 20 WITH TIES LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

SELECT TOP 20 LastName, FirstName, SUM (OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

SELECT TOP 20 LastName, FirstName, MAX(OrderAmount) AS ytd FROM sales GROUP BY LastName, FirstName ORDER BY 3 DESC

Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use the xp_sendmail system stored procedure to page the supply manager.

Create a stored procedure that will be called to update the inventory table. If the resultant quantity is less than the restocking quantity, use the xp_logevent system stored procedure to page the supply manager.

Create an INSTEAD OF UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use SQLAgentMail to send an e-mail message to the supply manager�s pager.

Schedule the SQL server job to run at four-hour intervals. Configure the job to use the @notify_level_page = 2 argument. Configure the job so that it tests each item�s quantity against the restocking quantity. Configure the job so that it returns a false value if the item requires restocking. This will trigger the paging of the supply manager.

Create a stored procedure that returns the rows that apply to a specified position type.

Create a view that is defined to return the rows that apply to a specified position type.

Grant SELECT permissions on the EmployeePositionType column for each position type

Grant permission on the News table for each position type.

SIZE = 1GB

FILEGROWTH = 20

FILEGROWTH = 5%

SIZE= 2048KB

Create a covering index for each query contained in the stored procedures.

For each query in the stored procedures, create an index that includes each column contained in the WHERE clause.

Use output from the Index Tuning Wizard to identify whether indexes should be added.

CREATE STATISTICS on all columns in the SELECT and WHERE clauses of each query.