2.
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.
3.
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
4.
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.
5.
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’
6.
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)
7.
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.
8.
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.
9.
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.
Get Free Quote!
372 Experts Online