SQL Server Developer Interview Test: T-SQL Assessment Guide Môn Công nghệ thông tin | Trường Đại học Huế

SQL Server Developer Interview Test 1 INSTRUCTIONS
This test is intended to give you the opportunity to demonstrate
your knowledge and understanding of SQL server and T-SQL.
Abroad knowledge of questions is included that test at different
levels. There are no trick questions.
• Please complete as many questions as you have time for.
• If you do not understand a question then please write why you do not understand it.
• If you understand a question but do not know the answer
then please write anything you do know about the subject of the question Test Duration: 60 Minutes
Number of Questions: Total Marks: Score Section 1: Section 2: Section 3: Total : 2
Section 1 – General Questions
1. What are the differences between DELETE & TRUNCATE commands? 2
2. What are cursors? Give an example of using cursor 3.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? 3
4. Give an example of many-to-many relationships and how to implement it
while designing tables 4
Section 2 –T-SQL Programming
1. Write an SQL command to create a table with the following information:
• Table name - Employees
• Contains the following columns:
1. EmployeeId (EmpId) – integer 2. FirstName - 255 characters 3. LastName - 255 characters
4. Employee Number (EmpNo) - integer 5. StartDate - Date
6. Salary - Amount in Dollars and cents. 7. City - 255 characters
• The primary key will be the employee Id . This must be unique.
• The Employee Number must also be unique.
• All 昀椀elds must contain data except First Name.
2. The table contains the following values: EmpId FirstName SecondName EmpNo StartDate Salary City 1 John Smith 123 2nd Feb2005 $30,000 New York 2 Mary Green 456 12th Dec 2008 $28,000 Chicago 3 David Jones 789 14th May 2009 $25,500 Chicago 4 Janet Ryan 15 17th Jun 2003 $42,350 Boston
Write an SQL command to add the 昀椀rst line of data to the table. 5
3. Please write a merge statement to insert/update below row to the Employees table
Please use EmpNo as merge search codition. EmpId FirstName SecondName EmpNo StartDate Salary City 1 John Dave 222 2nd Feb2005 $30,000 New York
4. Create a second table Cites with 2 columns : CityId City 1 Boston 2 Chicago 3 New York Where CityId is primary Key
How could we normalize the data?
5. A second table exists of name Orders.
This logs all the orders the user has put into a system. OrderId OrderNo EmpId 1 77895 3 2 44678 3 6 3 22456 2 4 24562 1 5 33657 2 6 37986 6 All 昀椀elds are integers.
The Order Id and Order Number is Unique.
Write an SQL command to create this table and link it to the Employee table
(Assume the table will be populated after)
6. Write an SQL query to show the number employees that earn over $27,000
7. Write an SQL query to show all the cities which have more than 10 employees 7
8. Write an SQL to query all the employees having salary in the top 5th
highest salaries
9. Write an SQL query to show the FirstName, LastName and OrderNo using
an INNER JOIN and order by LastName.
10. What would be the difference in the output if we ran the same statement
with a LEFT JOIN, FULL JOIN ? 8
11. Write an DDL command to create a constraint or trigger that means that if
we delete an employee, all their orders will be deleted.
12. Write an SQL command to set the minimum Salary of employees to $30,000
13. Create a stored procedure to increase the salary of employee in a city
• The salary increase is 20% for the employees making less than
$30000 and 10% for the employees making salary higher than or
equal to $30000
• Log the start time, 昀椀nished time, total number of old and new salary
of all employees in that city to output.
• Log the old and new salary of each employee 9 10
Section 3 - SQL database
1. What does TOP Operator Do?
2. What types of Joins are possible with Sql Server?
3. We need to capture all the update operation to a speci昀椀c table, how could
you do that?
4. What is your understanding of locking in SQL server? 11
5. What is dirty read and how to perform the dirty read? 12