Description
- List the Store ID and quantity from the sales table and the title id, from the titles table, where the advance is less than 5000. Calculate the average price by the title id.
- List the title ID and the first 30 characters of the title from the titles table and the publisher id and name from the publishers table where the publisher name ends with the letter s or y and the publisher name does not begin with the letters g and the price is greater than or equal to $15.00. Order the result set by the publisher name.
- Create a view called vw_emp_info displaying the employee id, first name and last name from the employee table, the job id and the job description from the jobs table, and the publisher name from the publishers table. Use the following code to test your view to produce the result selected below
- Using subqueries only (no JOINS) list the author id, first name, last name and phone number of the authors who have a book price greater than 20.00. The name of the author should be followed by the last name. Order the result set by the author id.
- Create a stored procedure called sp_emp_hire_date which displays the employee id, the first name, the last name and the hire date for employees and the job followed by a comma and a space followed by the first name. The hire date should be formatted as mmm dd yyy. The stored procedure will have two imput parameters to be used for the date range to query when employees were hired. Order the result set by the employee id. Use the following code to test:
EXEC sp_emp_hire_date ‘Jan 1 1989’, ‘Jun 30 1989’
- Create an INSERT and UPDATE trigger called tr_ins_upd_jobs that checks to ensure the difference between the minimum job level (min_lv) and the maximum level (max_lv) is not greater than 100 when rows are inserted or updated in the job table. Run the INSERT and UPDATE queries below to test your trigger.
INSERT jobs
VALUES ( ‘New job’, 50, 200)
- List the title id, quantity and the sum of the quantity for the title ids BU1032, MC3021 and PS2091 from the sales table. Show the subtotal for each title id and the final total.
- List the store id, quantity and order date from the sales table and the store name from the stores table where the order date is greater than or equal to january 1, 1994. In addition to displaying quantity use a CASE statement to display the level of quantity is greater than or equal to 20 and less than or equal to 40, display Stock Low and if the quantity is greater than 40 display Quantity Okay. The order date should be formatted as MMM DD YYYY. Order the result set by quantity.
- List the type from the titles table, order date from the sales table, publisher name from the publishers table, length in years from the order date to December 1, 2008 and the count by type for each book type. Format the order date as YYYY.MM.DD Do not include the rows in the result set unless the count by type is more than 1.