CS605 Data Management and Systems Modeling
This asignment wil be made available on Wednesday, March 28, 2018, and it will
be due on Wednesday, April 11, 2018 at 7:30pm.
This is an individual asignment, and the final deliverable has to be entirely based
on your own work. For al questions, please write a SQL query that produces a
corect answer to the coresponding information request.
Please submit your answers in one SQL file using the atached template. Please end
every query with a semicolon and use a comment heading for each query as shown
in the template. Please name the file with your Bentley user name folowed by
_a5.sql (e.g., smith_mary_a5.sql).
The point value of each question is 4 points.
1. List the last name, first name, department name, department building, and
phone number for the employes who were hired after 3/5/2012 and have
more than two dependents.
2. List the name, color code, and discount percentage for those product
lines that have annual sales year to date of at least $2,000,000 (determined
based on the SALES table) and a discount percentage less than 15 %.
3. a) List department name and the total number of employes for those
departments that are located in building 'H'. b) Modify the previous query so
that the list includes only the departments that employ more than 50 people.
4. List employe names (in the format Smith, M.), department names, and the
names of the customers they are responsible for. Include only those
employes whose last name starts with "K". Make sure to include also those
employes who are not responsible for any customers.
5. List the names, product line names, gros profit margins, and quantities on
hand for those products that belong to product lines with sales increase from
last year to year to date (based on the PRODUCT LINE table) below 7% and
quantity on hand more than 1000.
6. For a subset of customers, list customer name, customer's sales rep's name
(in the format John Smith), customer's sales rep's department's name, and
the names of al the products that were sold to the customer last year.
Include in the subset only those customers whose annual goal is at least 50%
higher than the overal average customer anual goal per sales rep.
7. For each customer group, list the number and the name of the group with the
average, minimum, maximum, and total sales amounts last year per customer
group for those products the price of which is not more than $200. Sort the
list in ascending order by the group name.
8. List the names and the names of the managers of those employes who are
responsible for more than thre customers.
9. Using a (NON) EXISTS subquery, list the names and the sales prices of the
products that have not been sold at all this year.
10. List the numbers, names, and phone numbers of those customers that
exceeded the average sales goal in their customer group by 30% and
exceeded their own sales goal by at least 50%.