Sample SQL Test

Sample SQL Server Test

Q = Question
A = Answer
C = Comments

Q1. What does SQL Stand for?
A1. Structured Query Language
C1. This question was probably asked to weed out candidates who answer “Sequel” or guess.

Q2. Write a SQL query to select everything in a table.
A2. SELECT * FROM <table>
C2. Another question to weed out candidates who might have lied in their resume.

Q3. Write a SQL query that shows a name only once in a column.
A3. SELECT DISTINCT <columns> FROM <table>
C3. Simple, yet effective for weeding out candidates.

Q4. How to insert a row of data into a table.
A4. INSERT INTO <table> (col1, col2, col3, …) VALUES (‘x’, ‘y’, ‘z’, …)
C4. Another simple question for understanding syntax

Q5. How to insert column into a table.
A5. ALTER TABLE <table> ADD <column> <type> NOT NULL DEFAULT(1)
C5. Vague question, but specify type, such as INT, CHAR, VARCHAR, etc., and default values.

Q6. How to delete data?
A6. DELETE FROM <table> WHERE <expression>
C6. Vague question, but give explicit example if possible for the expression (e.g., WHERE ID = NULL)

Q7. What is a primary key?
A7. Unique row identifier in each table, used to establish 1:1, 1:Many, Many:Many relationships.
C7. For the database designers.

Q8. What does COMMIT mean?
A8. Finalizes the transaction (T-SQL Statement) as opposed to ROLL BACK.
C8. Usually used with BEGIN TRAN.

Q9. What is a JOIN and what are the different types?
A9. INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN
C9. Draw venn diagrams to help explain this.

Q10. Write a query to return the following results using the given tables.

RESULTS:
FirstName | LastName | OrderID
Cathy | Ngo | 143571
Cathy | Ngo | 143578
Leon | Ngo |
Peter | Nguyen | 143569
Peter | Nguyen | 143570

EMPLOYEES:
Employee ID | First Name | Last Name
1 | Peter | Nguyen
2 | Cathy | Ngo
3 | Leon | Yuri

ORDERS:
OrderID | ProductID | EmployeeID
143569 | 17 | 1
143570 | 18 | 1
143571 | 35 | 2
143578 | 17 | 2
143589 | 12 | 5

A10.

SELECT
E.FirstName,
E.LastName,
O.OrderID
FROM
Orders [O]
RIGHT JOIN Employees [E] ON E.EmployeeID = O.EmployeeID

C10. This is probably the main skill needed to land the job.