Determine the number of days between the latest employee hire date and today. Show results only for titles with 50 or more copies ordered.ġ4. List the total quantity of books ordered for each title_id. The month may be expressed as an integer from 1 to 12. List the total quantity of books ordered for each month of 1993. The result set should have column titles as in the first sentence.ġ2. The hire date should appear in the format: Jun 1, 1990.

List the Employee ID, First name, Last name, and Hire date of all employees. You may want to execute the following query, to see the possible values of payterms: SELECT DISTINCT payterms from dbo.sales.ġ1.

You will need to see two Books Online topics for this one: 'YEAR (Transact-SQL),' for determining the year part of the order date, and 'Operator Precedence (Transact-SQL).' In the precedence topic, note AND, OR, and use of parentheses to override defined precedence. Retrieve the store ID, title ID, order date, and quantity sold for all sales ( dbo.sales) that occurred in the year 1994 from stores that either sold more than 20 copies of a single title or had a payment term of 'Net 30' days. See the Books Online topic 'IS NULL (Transact-SQL).'ġ0. Retrieve all titles for which the royalty is unknown. List all stores ( dbo.stores) that have a store name with first letter D or E.ĩ. List the title ID and title for all titles that contain the character string 'computer' anywhere in the title. Retrieve the employee ID, last name, and job level for all employees that are at job level 35, 100, or 200. (You may use the numbers 10 and 15 without dollar signs.) Use the BETWEEN keyword.Ħ. Write a select statement that retrieves the title and price of all books that cost between $10 and $15. Use the proper data type for the zip code. Retrieve a list of author last names, cities, states, and zip codes whose zip code is greater than 90000. Retrieve the title of the book that has title ID 'PC9999'.Ĥ.

See the Books Online topic 'SELECT Clause (Transact-SQL),' subtopic column_alias.ģ. Write a SELECT statement that retrieves from the dbo.employees table the 'first name,' 'last name,' 'employee ID,' and 'job level.' These are the column aliases that should appear in the result set. Write a SELECT statement that retrieves the title_id and title from the dbo. Also check practice queries on northwind databaseġ.

Pubs database download