Use UNION, INTERSECT, or MINUS operator to write SQL queries answer following queries:

Queries on Scheduling Database in Figure 6-15 page 291 of the textbook.  (Include queries only in your answer)

1. Use UNION, INTERSECT, or MINUS operator to write SQL queries answer following queries:

1. Find ID and NAME of all faculty qualified to teach both ISM 3112 and ISM 3113 courses
2. Find ID and NAME of all faculty qualified to teach ISM 3112 course but are not qualified to teach ISM 3113 course

2. Use INNER JOIN or LEFT OUTER JOIN or RIGHT OUTER JOIN to answer the following questions

1. For each course whose name starts with Syst, find the number of students registered in each section. If a section has no student registered, the enrollment, the column should contain zero
2. Find Course ID, CourseName, Section ID, Semester for all the courses in which student Marra is registered

3. Use EXISTS or NOT EXISTS or IN or NOT IN to write SQL queries for the following questions

1. List names of the students who have not registered for any section of any course in semester I-2018
2. List names of the students who have registered for at least one section taught by Collins

Queries on Pine Valley Furniture Corporation Database.  (Include ddl to create views, queries, and the result table for each question)

4. Write SQL queries to answer the following questions (These questions refer to PVFC database that you used in Assignment 3):

1. (Question 6-80). Write an SQL query to list all product line names and, for each product line, the number of products and the average product price. Make sure to include all product lines separately.

Answer the query by first creating a view that creates average product price for each product line and then comparing product price of a product with the average price within its product line.

1. Write an SQL query to list ID and name of all customers served by the salesperson who has generated most revenue

Answer this query using following steps:

1. Create a view that calculate the total dollar amount for each order
2. Use view in step (i) to create a view that calculates the total dollar amount for each sales person

• Use the view in step (ii) to create a view that lists the salesperson who has generated most revenue

1. Use the view step (iii) to find all customers who have placed at least one order with this salesperson