Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 6 December 2012

Test Your Skills For SQL Server

Test 1:

 1 ) Which wildcard usage will help you find all last names starting with 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?
3 ) Look at the following SQL statement:
SELECT * 

  FROM Employee LEFT OUTER JOIN Location

  ON Location.LocationID = Employee.LocationID

 4 ) What is the correct way to alias the sales table? Choose the correct answer
5 ) Can you create a table with zero fields? Choose the correct answer
 6 ) Which of the following is not a way to run a SQL script? Choose the correct answer
7 ) Which statement describes the difference between the DROP and DELETE clauses for tables? Choose the correct answer
 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
 9 ) What is a dirty record? Choose the correct answer
 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

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?

* 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.

* 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?



* 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

* 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

* 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?

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

* 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?


* 9 ) Which code uses a CTE (Common Table Expression)?
* 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

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?


* 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

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

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

* 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

* 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

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

* 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?


* 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

* 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?


Ans????????

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