Test Your Skills For SQL Server

Test 1:

 1 ) Which wildcard usage will help you find all last names starting with R?

• WHERE LastName = 'R%'
• WHERE LastName = '%R'
• WHERE LastName LIKE 'R%'
• WHERE LastName LIKE '%R'

 2 ) You want to find all first names that have the letter A as the second letter and do not end with the letter Y. Which SQL code would you use?

• SELECT * FROM Employee WHERE Firstname like '_A%[^Y]' AND Firstname NOT LIKE 'Y%'
• SELECT * FROM Employee WHERE Firstname like '_A%[^Y]' AND Firstname NOT LIKE '%Y'
• SELECT * FROM Employee WHERE Firstname like '[^Y]A_% ' AND Firstname NOT LIKE 'Y%'

3 ) Look at the following SQL statement:

SELECT * 

  FROM Employee LEFT OUTER JOIN Location

  ON Location.LocationID = Employee.LocationID

• All records where both tables match.
• All records in Employee including matches from Location.
• All records from Location including matches from Employee.
• The superset of both tables.

 4 ) What is the correct way to alias the sales table? Choose the correct answer

• FROM Sales AS sl
• OFROM sl AS Sales sl
• SELECT Sales AS sl

5 ) Can you create a table with zero fields? Choose the correct answer

• Yes – you can create a table with any numnber of fields icluding 0
• No – You need at least one field to create a table

 6 ) Which of the following is not a way to run a SQL script? Choose the correct answer

• Run it in a query window.
• Use the script file in a command-line utility
• Open Windows Explorer and double-click the .sql file
• Double click .mdb (Microsoft Database File) file in Windows Explorer.

7 ) Which statement describes the difference between the DROP and DELETE clauses for tables? Choose the correct answer

• They do exactly the same thing.
• DROP leaves you with an empty table and DELETE removes the table
• DELETE empties the table and DROP eliminates the table

 8 ) You want to create a stored procedure called GetWagesByManager that takes a parameter called EmpID. What would be the first line of your statement? Choose the correct answer

• CREATE PROC GetWagesByManager @EmpID INT
• CREATE PROC @EmpID INT AS GetWagesByManager
• CREATE PROC GetWagesByManager WHERE @EmpID = EmpID INT

 9 ) What is a dirty record? Choose the correct answer

• A record that can’t be accepted because it violates the database constraints
• A record that replaced the original record in permanent storage
• A record that has been changed, but not yet saved to permanent storage

 10 ) If you granted Phil control to the server, but denied his ability to create databases, what would his effective permissions be? Choose the correct answer

• Phil can do everything.
• Phil can do nothing.
• Phil can do everything except create databases.

Test 2:

* 1 ) You have a table named Performance.

The [Duration] field shows the total time, in milliseconds, it took for a query to run. You have a field, called CPU, which shows the processing time in millisecondYou have a field, called EventClassID.The company worries there may be queries running slowly in the CPU, or quickly in the CPU but spending too much time for the total duration. You want to find all records from EventClassID 7 that are taking more than 1 second of CPU time or more than 2 seconds of total duration. Which predicate do you use?

• WHERE EventClassID = 7 AND ( CPU > 1 OR Duration > 2 )
• WHERE EventClassID = 7 AND ( CPU > 1000 OR Duration > 2000 )
• WHERE EventClassID = 7 AND ( CPU > 1000 AND Duration > 2000)

* 2 ) Your manager wants to see all employees in alphabetical order for each state. She has asked you to sort by State, LastName, and FirstName columns. Without creating any additional tables how can you view this report? Choose the correct answer.

• Specify State, LastName, and FirstName in the ORDER command.
• Create a file format that has an export operation.
• Specify State, LastName, and FirstName in the ORDER BY clause.
• Copy the data into a new table that has a clustered index set on State, LastName, and FirstName.

* 3 ) You have a table named Products defined with the following code:

CREATE TABLE Products

(ProductID int IDENTITY (1,1) ,

ProductRating int NOT NULL,

ProductName varchar(50),

CreationDate datetime NOT NULL)

You must insert a new Product record with a rating of 75 called “Go Duck” with today’s Creation datetime. Which statement should you use?

• INSERT INTO Products VALUES(75, 'Go Duck' ,DEFAULT)
• INSERT INTO Products VALUES(NULL,75, 'Go Duck' ,DEFAULT)
• INSERT INTO Products VALUES(75, 'Go Duck' ,CURRENT_TIMESTAMP)
• INSERT INTO Products VALUES(NULL ,75, 'Go Duck' , CURRENT_TIMESTAMP)

* 4 ) Your JProCo database has two tables named Customers and Orders. Not all customers have placed an order yet. You would like to see a list of customers and the total number of orders listed next to the CustomerID. Which query should you use? Choose the correct answer

• SELECT Count(ord.OrderID) as OrderCount FROM Customers cust INNER JOIN Orders ord ON cust.CustomerID = ord.CustomerID
• SELECT cust.CustomerID, Sum(ord.OrderID) as OrderCount FROM Customers cust INNER JOIN Orders ord ON cust.CustomerID = ord.CustomerID GROUP BY cust.CustomerID
• GROUP BY cust.CustomerID
• SELECT cust.CustomerID, Count(Ord.OrderID) as OrderCount FROM Customers cust INNER JOIN Orders ord ON cust.CustomerID = ord.CustomerID GROUP BY cust.CustomerID

* 5 ) You want to display each grant in your [Grant] table and have an expression field that shows the total amount of all Grants. Which query should you use? Choose the correct answer

• SELECT *, COUNT(Amount) OVER ( ) as CompanyTotal FROM [Grant]
• SELECT *, Sum(Amount) OVER ( ) as CompanyTotal FROM [Grant]
• SELECT *, COUNT(Amount) as CompanyTotal FROM [Grant] GROUP BY *
• SELECT *, Sum(Amount) as CompanyTotal FROM [Grant] GROUP BY *

* 6 ) You have 1 million rows to delete from the TempProducts table. If the field called “PlsDelete” is marked with a 1, then that row should be deleted. You have been asked to delete 5000 rows at a time. You need to write a TransactSQL batch that will delete exactly 5000 rows. Which TransactSQL batch should you use?

• DELETE TOP (5000) dbo.TempProducts WHERE PlsDelete = 1
• DELETE TOP (5000) dbo.TempProducts WHERE PlsDelete != 1
• DELETE BOTTOM (5000) dbo.TempProducts WHERE PlsDelete = 1
• DELETE BOTTOM (5000) dbo.TempProducts WHERE PlsDelete != 1

* 7 ) You want to find the top 2% of all students’ Grade Point Averages (GPA). Which NTILE would you use? Choose the correct answer

• NTILE(1) OVER(ORDER BY GPA DESC)
• NTILE(2) OVER(ORDER BY GPA DESC)
• NTILE(25) OVER(ORDER BY GPA DESC)
• NTILE(50) OVER(ORDER BY GPA DESC)

* 8 ) You have two tables named CurrentProducts and RetiredProducts that have the same field names and data types. In fact the structure of the two tables is identical. You write the following two queries.

SELECT * FROM CurrentProducts

SELECT * FROM RetiredProducts

Between these two queries you want to use the correct T-SQL Operator to display the records that are common between both tables. Which operator would you use?

• UNION
• EXCEPT
• UNION ALL
• INTERSECT

* 9 ) Which code uses a CTE (Common Table Expression)?

• WITH YearlySales (Year,Region,Total) AS (SELECT Year, Region, SUM(OrderTotal) FROM Orders GROUP BY Year,Region) SELECT Year, Region, Total FROM YearlySales ;
• WITH YearlySales(Year,Region,Total) AS (SELECT Year,Region,SUM(OrderTotal) FROM Orders GROUP BY Year,Region) SELECT Year,Region,Total FROM YearlySales;

* 10 ) You have two tables, named Customer and SalesOrder. You need to identify all customers that have not made any purchases or those who have never ordered more than 40 at any given time. Which query should you use? Choose the correct answer

• SELECT * FROM Customer WHERE 40 > ALL (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
• SELECT * FROM Customer WHERE 40 > ANY (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
• SELECT * FROM Customer WHERE 40 > (SELECT MAX(OrderTotal) FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID) 

Test 3:

* 1 ) You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint.

You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100.
Which check constraint would you use?

• CHECK (Rating BETWEEN 1 and 100)
• CHECK (Rating <=90 AND Complaint = 1)
• CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) ) OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
• CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

* 2 ) You want to perform a test insert against your dbo.Employee table which has an insert trigger on it. You are not allowed to delete the trigger but want to perform this insert without the trigger firing. How can you do this? Choose the correct answer

• You can’t.
• Downgrade the trigger before your test and then upgrade afterwards.
• Move the trigger to a new table then move back after your test.
• Disable the trigger before the test and then enable it afterwards.
• Enable the trigger before the test and then disable it afterwards

* 3 ) Which statement will never fire a DDL trigger? Choose the correct answer

• CREATE TABLE
• ALTER TRIGGER
• DROP PROCEDURE
• DELETE TABLE
• GRANT CONTROL

* 4 ) What happens to a view if the underlying base table is dropped? Choose the correct answer

• The view no longer works.
• The view uses the latest cached data until the table is restored.
• The view uses the default or fallback table

* 5 ) You are creating a view that queries your Employee table. You need to prevent the Employee table from being dropped as long as your view exists. Which option should you use when you create the view? Choose the correct answer

• WITH ENCRYPTION
• WITH NOCHECK
• WITH SCHEMABINDING
• WITH OPENXML

* 6 ) Your stored procedure named GetEmployeeByCity has a default parameter of ‘Seattle’. When you execute the stored procedure, you supply “Boston” as the parameter. What is the result of your execute statement. Choose the correct answer

• Seattle will be used
• Boston will be used
• You will get an error message.

* 7 ) What is the difference between SCOPE_IDENTITY( ) and @@IDENTITY? Choose the correct answer

• SCOPE_IDENTITY( ) only works inside the stored procedure.
• SCOPE_IDENTITY( ) only works with code which calls on the stored procedure.
• SCOPE_IDENTITY( ) shows the most recent number, whereas @@Identity shows the next number

* 8 ) The following query finds all reports for leap year months since 1950:

SELECT *

FROM Reports

WHERE  ???????? (rMonth, rYear)  = 29

AND rYear > 1950

If, for a specified year, the month of February will contain 29 days, you want the query to include the record in your query. Which object should you use in your query predicate?

• DML trigger
• Stored procedure
• Table-valued function
• Scalar function

* 9 ) Which SQL Server error action happens for errors with a severity of 11-16 when you set the XACT_ABORT setting to OFF? Choose the correct answer

• You will get Statement Termination
• You will get Scope Abortion
• You will get Batch Abortion
• You will get Connection Termination
• SQL Server will pick the error action

* 10 ) You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. . You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.

BEGIN TRY

  BEGIN TRANSACTION

      DELETE FROM CurrentProducts  WHERE ProductID = 77;

      BEGIN TRANSACTION

        INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 );

      COMMIT TRANSACTION

  COMMIT TRANSACTION

END TRY

BEGIN CATCH

  ROLLBACK TRANSACTION

  PRINT ERROR_MESSAGE( );

END CATCH

What will be the outcome when you run this query?

• 1) The product will be deleted from the CurrentProducts table. 2) The order details will be inserted into the SalesInvoiceHeader table
• 1) The product will be deleted from the CurrentProducts table. 2) The order details will not be inserted into the SalesInvoiceHeader table
• 1) The product will not be deleted from the CurrentProducts table. 2) The order details will be inserted into the SalesInvoiceHeader table.
• 1) The product will not be deleted from the CurrentProducts table. 2) The order details will not be inserted into the SalesInvoiceHeader table. 

Ans????????

I will submit to you soon till you just guess ur answer and patient!!!!!!!!!!!!!!!!!!!!!!!!!!! thankxxxxxxx for patient.