Part 1-

Questions- Explain why the following query will give an error:

SELECT empid, firstname, lastname, DATEPART(year, hiredate) AS YearHired
FROM HR.Employees
WHERE YearHired = 2004;

You have a table named File with column FileName. You want to write a query that returns all rows for file names that begin with an underscore. Why is WHERE clause i) better than WHERE clause ii)?

i) WHERE FileName LIKE '!_%' ESCAPE '!'
ii) WHERE LEFT(FileName, 1)

Part 3

All queries use the TSQL2012 database unless otherwise indicated.

Label each query with its letter. Copy the query from the SQL Server Management Studio query window into your Assignment Word document. Then copy the results into the Word document. If the result set has more than 10 rows, you need only show the first five rows of the result set, plus an indication of how many rows were returned.

a. Query the HR.Employees table. The result set column headers should be Employee ID, Last Name, and First Name.

b. Return the empid, firstname, lastname, and city for the employee with empid 9.

c. Return a list of employee id's, last names, and first names for employees hired on or after January 1, 2004.

d. Retrieve the product name and unit price of all products that have unit price between $10 and $15. Use the BETWEEN keyword.

e. Retrieve the product id, product name, supplier id, and unit price of all products that are supplied by suppliers 2, 3, 16, or 19. Use the IN keyword. Return the results in order of largest to smallest unit price.

f. List the category name and description for all product categories that contain the character string 'ee' anywhere in the description. Use the LIKE keyword.

g. Retrieve the order id, order date, employee id, and ship (to) city of all orders for which the shipped date is unknown.

h. Write a query that returns the number of days old you are. Use the DATEDIFF function.