DSCI551 HW 3 SQL Solution

Install employee database as described in:
https://dev.mysql.com/doc/employee/en/employees-installation.html
You can either download the database zip file from https://github.com/datacharmer/test_db or use the zip file we downloaded and include in this homework folder. The zip file is: test_db-master.zip.
To install the database on EC2:
1. Download the test_db-master.zip file
2. sftp it to your EC2 instance.
3. On your EC2, execute:
unzip test_db-master.zip cd test_db-master
mysql -u root -p < employees.sql
4. Now log in to your mysql database. You should see a new database called employee now.
In this homework, ignore the views current_dept_emp and dept_emp_latest_date in the database. A. [50 points] Write an SQL query for each of the following questions on the above employees database. Note your query should run without error in MySQL.
1. Find employee records for employees whose first name contains ‘mary’ and the second to the last letter in the last name is ‘o’. Note like operator is NOT case sensitive.
format of output: +------------+
| emp_no |
+------------+


SELECT emp_no
FROM employees.employees
WHERE first_name LIKE '%mary%'
AND last_name LIKE '%o_';

2. Find first name and last name of employees who ever made at least $150000. Show the same employee (with the same name) only once.
format of output: +----------------+---------------+
| first_name | last_name |
+----------------+---------------+

SELECT e.first_name, e.last_name
FROM employees.employees AS e
JOIN (
SELECT emp_no, MAX(salary) AS peak
FROM employees.salaries
GROUP BY emp_no
HAVING peak > 150000
) inn
ON e.emp_no = inn.emp_no;

format of output: +-------------+--------------------+
| dept_no | employeeNum |
+-------------+--------------------+

SELECT dept_no, COUNT(emp_no) AS employeeNum
FROM employees.dept_emp
WHERE from_date = '1988-10-20'
GROUP BY dept_no
ORDER BY employeeNum DESC;

4. Find the department number (dept_no) of departments which have or had at least three different managers.
format of output: +-------------+
| dept_no |
+-------------+

SELECT DISTINCT d.dept_no as dept_no
FROM employees.dept_manager d JOIN (
SELECT dept_no, COUNT(DISTINCT emp_no) AS mngrs
FROM employees.dept_manager
GROUP BY dept_no
) cnt
ON d.dept_no = cnt.dept_no
WHERE cnt.mngrs >= 3;

5. Find first name, last name, and title of employees where the title contains ‘engineer’, e.g., ‘senior engineer’ or ‘engineer’ for the position starting from ‘2000-3-23’.(Do not care about the employee still works in the department or not)
format of output: +----------------+-------------------+--------------+
| first_name | last_name | title |
+----------------+-------------------+--------------+

SELECT DISTINCT d.dept_no as dept_no
FROM employees.dept_manager d JOIN (
SELECT dept_no, COUNT(DISTINCT emp_no) AS mngrs
FROM employees.dept_manager
GROUP BY dept_no
) cnt
ON d.dept_no = cnt.dept_no
WHERE cnt.mngrs >= 3;

For Question B and C, you will use the beers-tables database in the lectures.
B. [30 points] Write an SQL query for each of the following questions on the beers-tables database. Note your query should run without error in MySQL.
1. Find manufacturers who made at least three different beers. Your column names should look EXACTLY like:
+--------------------+
| Manufacturer |
+--------------------+

SELECT manf AS Manufacturer
FROM beerstables.Beers
GROUP BY manf
HAVING COUNT(DISTINCT NAME) >= 3;

2. Find drinkers who never frequent bars. Your column names should look EXACTLY like:
+-----------+
| Drinker |
+-----------+

SELECT name as Drinker
FROM beerstables.Drinkers
WHERE name NOT IN (
SELECT drinker
FROM beerstables.Frequents
);

3. Find out for each bar, how many beers are sold at the bar with a price of at least 2 dollars. Your column names should look EXACTLY like:
+------+---------+
| Bar | Total |
+------+---------+

SELECT ba.name AS Bar, COALESCE(inn.total, 0) AS Total
FROM beerstables.Bars AS ba
LEFT JOIN (
SELECT bar, COUNT(price) AS total
FROM beerstables.Sells
WHERE price >= 2 GROUP BY bar
) inn
ON ba.name = inn.bar;

4. Find the bars who sell the most expensive beers. Your column names should look EXACTLY like:
+------+
| Bar |
+------+


SELECT DISTINCT s.bar AS Bar
FROM beerstables.Sells AS s
JOIN (
SELECT MAX(price) as maxPrice
FROM beerstables.Sells
) inn
ON s.price = inn.maxPrice;

5. Find drinkers who like Bud but do not like Summerbrew. Your column names should look EXACTLY like:
+-----------+
| Drinker |
+-----------+

SELECT drinker AS Drinker
FROM beerstables.Likes
WHERE beer LIKE 'Bud' AND drinker NOT IN (
SELECT drinker
FROM beerstables.Likes
WHERE beer LIKE 'Summerbrew'
);

C. [20 points]
1. Create a view called Beers2Bars that lists the manufacturer, beer, bar, and price for each beer sold at the bar.

SELECT drinker AS Drinker
FROM beerstables.Likes
WHERE beer LIKE 'Bud' AND drinker NOT IN (
SELECT drinker
FROM beerstables.Likes
WHERE beer LIKE 'Summerbrew'
);

2. Write a query that uses only the above view to find the average price of beers for each manufacturer. Your column names should look EXACTLY like:
+--------------------+--------------------+
| Manufacturer | Average |
+--------------------+--------------------+

SELECT drinker AS Drinker
FROM beerstables.Likes
WHERE beer LIKE 'Bud' AND drinker NOT IN (
SELECT drinker
FROM beerstables.Likes
WHERE beer LIKE 'Summerbrew'
);


** Submission requirements **
PLEASE READ CAREFULLY BEFORE SUBMISSION.
1. For each question, submit a .sql file with SQL query (or create view statement) and the result of the query. You can use any text editor to save the file.
Note that you should design a general-purpose query to answer each question. For example, if the data in the beers-tables database are slightly changed, your command must still be able to get the correct results.
2. File format must be: *.sql
e.g. A1.sql, B1.sql, C1.sql, … (12 files in total) DO NOT submit any other files that are not required.
DO NOT submit a zip file.
3. Make sure you follow the correct “multi-line comment” format of in sql file with /* */.
DO NOT write anything except SQL commands or statements outside of comments. For output results, you can paste the content inside /* */, e.g.:
select * from Beers;
/* Output
+------------+----------------+
| name | manf |
+------------+----------------+
| Bud | Anheuser-Busch |
| Bud Lite | Anheuser-Busch |
| Budweiser | Heineken |
| Michelob | Anheuser-Busch |
| Summerbrew | Pete's |
+------------+----------------+
5 rows in set (0.00 sec)
*/