[ DB ] HW Queries

HW1

(a) List all instructor names in the Accounting department

SELECT name
FROM instructor
WHERE dept_name = 'Accounting';

(b) How many students are in the Statistics department?

SELECT COUNT(ID)
FROM student
WHERE dept_name = 'Statistics';

(c) How many unique student names are in the Astronomy department?

SELECT COUNT(DISTINCT name)
FROM student
WHERE dept_name = 'Astronomy';

(d) Find all students who have “db” as a substring in their name

SELECT *
FROM student
WHERE name LIKE '%db%';

List the names of all tables that the “university” database has

SHOW TABLES;

Execute and explain the differences among the results of the following queries
(i) SELECT * FROM instructor; ➡️ List all instructor attributes
(ii) SELECT 'Teacher' FROM instructor; ➡️ The result is a table with 50 rows(row size of instructor table) with a value of "Teacher"
(iii) SELECT 'Teacher'; ➡️ The result is a table with one row with a value of "Teacher"
(iv) SELECT *, 'Teacher' FROM instructor; ➡️ A "Teacher" literal column is added to the list of all instructor attributes.**

---

HW2

Employee database

(a) Find the ID of each employee who does not work for “First Bank Corporation”.

SELECT ID
FROM works
WHERE company_name <> 'First Bank Corporation';

(b) Find the ID, name, and city of residence of each employee who works for “First Bank Corporation” and earns more than $10,000

SELECT employee.ID, person_name, city
FROM employee JOIN works USING (ID)
WHERE company_name = 'First Bank Corporation'
	AND salary > 10000;

(c) Find the ID of each employee who earns more than every employee of “Small Bank Corporation”

SELECT ID
FROM works
WHERE salary > ALL (
	SELECT salary
    FROM works
    WHERE company_name = 'Small Bank Corporation'
);

(d) Assume that companies may be located in several cities. Find the name of each company that is located in every city in which “Small Bank Corporation” is located

SELECT C1.company_name
FROM company AS C1
WHERE NOT EXISTS (
	( SELECT city
    FROM company
    WHERE company_name = 'Small Bank Corporation' ) 
    NOT IN
    ( SELECT city
    FROM company AS C2
    WHERE C1.company_name = C2.company_name )
);

(e) Find the name of the company that has the most employees (or companies, if there is a tie)

SELECT company_name
FROM works
GROUP BY company_name
HAVING COUNT(DISTINCT ID) >= ALL(
	SELECT COUNT(DISTINCT ID)
  	FROM works
  	GROUP BY company_name
);

SELECT company_name
FROM works
GROUP BY company_name
HAVING COUNT(DISTINCT ID) = (
	SELECT COUNT(DISTINCT ID)
    FROM works
    GROUP BY company_name
    ORDER BY COUNT(DISTINCT ID) DESC
    LIMIT 1
);

(f) Find the name of each company whose employees earn a higher salary on average, than the average salary at “First Bank Corporation”

SELECT company_name
FROM works
GROUP BY company_name
HAVING AVG(salary) > (
	SELECT AVG(salary)
    FROM works
    WHERE company_name = 'First Bank Corporation'
);

(g) Modify the database so that the employee whose ID is ‘12345’ now lives in a city called “Newtown”

UPDATE employee
SET city = 'Newtown'
WHERE ID = '12345';

(h) Find ID and name of employee who lives in the same city as the location of the company for which the employee works

SELECT employee.ID, person_name
FROM employee JOIN works USING(ID) JOIN company USING (company_name)
WHERE employee.city = company.city

(i) Find ID and name of each employee who earns more than the average salary of all employees of her or his company

SELECT E.ID, person_name
FROM employee AS E JOIN works AS W1 USING(ID)
WHERE salary > (
	SELECT AVG(salary)
    FROM works AS W2
    WHERE W1.company_name = W2.company_name
);

(j) Find the company that has the smallest payroll (sum of all salary in a company)

SELECT company_name
FROM works
GROUP BY company_name
HAVING SUM(salary) = (
	SELECT SUM(salary)
    FROM works
    GROUP BY company_name
    ORDER BY SUM(salary)
    LIMIT 1
);

(k) Given all employees of “First Bank Corporation” a 10 percent raise

UPDATE works
SET salary = salary * 1.1
WHERE company_name = 'First Bank Corporation';

(l) Delete all tuples in the works relation for employees of “Small Bank Corporation”

DELETE FROM works
WEHRE company_name = 'Small Bank Corporation';

University database

(a) Find the number of all courses offered in Fall and that of Spring, respectively

SELECT semester, COUNT(course_id)
FROM section
WHERE semester = 'Fall' OR semester 'Spring'
GROUP BY semester;

(b) How many unique course names (titles) are among the courses offered by the university?

SELECT COUNT(DISTINCT title)
FROM course

(c) What is the average monthly salary of the instructors in the Cybernetics department? Round the answers at the second decimal place, if necessary

SELECT ROUND(AVG(salary/12), 2)
FROM instructor
WHERE dept_name = 'Cybernetics';

(d) Find the names of departments whose budget is higher than that of Psychology. List them in alphabetic order

SELECT dept_name
FROM department
WHERE budget > (
	SELECT budget
    FROM department
    WEHRE dept_name = 'Psychology'
)
ORDER BY dept_name;

(e) List the names of the students in the Geology department whose name starting with ‘C’

SELECT name
FROM student
WHERE dept_name = 'Geology' 
	AND name LIKE 'C%';

(f) Find the ID and name of each History student whose name begins with the letter ‘D’ and who has not taken at least five Music courses

SELECT ID, name
FROM student
WHERE dept_name = 'History'
	AND name LIKE 'D%'
    AND 5 > (
    	SELECT COUNT(DISTINCT course_id)
        FROM takes JOIN course USING (course_id)
        WHERE student.ID = takes.ID
       		AND course.dept_name = 'Music'
);
   
SELECT ID, name
FROM student
WHERE dept_name = 'History'
    AND name LIKE 'D%'
    AND 5 > (
        SELECT COUNT(DISTINCT course_id)
        FROM takes
        WHERE student.ID = takes.ID
             AND course_id IN ( 
              	SELECT course_id 
                FROM course 
                WHERE dept_name = 'Music' 
        	)
);

(g) Find all Physics and Comp. Sci. students whose name is longer than 11 characters

SELECT name
FROM student
WHERE (dept_name = 'Physics' OR dept_name = 'Comp. Sci.')
	AND name LIKE '___________%';

(h) Find the number of Comp. Sci. students total credits greater than that of AT LEAST ONE student in the English department

SELECT COUNT(*)
FROM student
WHERE dept_name = 'Comp. Sci.'
	AND tot_cred > SOME (
    	SELECT tot_cred
        FROM student
        WHERE dept_name = 'English'
    );

(i) Which of the university buildings can accommodate more than 100 people?
Hint: see the sum of classroom capacities

SELECT building
FROM classroom
GROUP BY building
HAVING SUM(capacity) > 100;

(j) Find all instructor IDs who had taught until 2003 but had not taught after 2003
Hint: Attribute teaches.ID is the instructor ID

SELECT DISTINCT ID
FROM teaches
WHERE ID IN (
	SELECT DISTINCT ID
	FROM teaches
    WHERE year <= 2003 
    	AND NOT IN (
    	SELECT DISTINCT ID
    	FROM teaches
   		WHERE year > 2003
	);

(k) k. Write a query that counts the number of students for each department and sort the results in descending order of the student counts. Hint: the head of the query result looks like the following:

SELECT dept_name, COUNT(ID) AS num_students
FROM student
GROUP BY dept_name
ORDER BY num_students DESC;

(l) Rewrite the WHERE clause WHERE UNIQUE (SELECT title FROM course) without using the UNIQUE construct

SELECT *
FROM course AS C1
WHERE 1 = (
	SELECT COUNT(title)
    FROM course AS C2
    WHERE C1.title = C2.title
);

SELECT *
FROM course
WHERE title IN (
	SELECT title
	FROM course
	GROUP BY title
	HAVING COUNT(*) = 1
)

(m) Write a query that lists up all classes that have been open in the university, together with the number of students who were in each class. More specifically, enumerate all the course IDs, section IDs, years, and semesters, along with the number of students who took each of the classes.
Hint: you may want to come up with a result that starts as below

SELECT course_id, sec_id, semester, year, COUNT(*) AS num_students
FROM takes
GROUP BY course_id, sec_id, semester, year

(n) For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student’s ID. Please display your results in order of course ID and do not display duplicate rows

SELECT course_ID, ID
FROM takes
GROUP BY course_id, ID
HAVING COUNT(*) >= 3;
ORDER BY course_id

➡️ GROUP BY course_id만 하면 Error!

(o) Find the ID and name of each instructor who has never given an A grade in any course s/he has taught. Order result by name

SELECT ID, name
FROM instructor
WHERE ID NOT IN (
	SELECT teaches.ID
	FROM teaches JOIN takes USING(course_id, sec_id, semester, year)
	WHERE takes.grade = 'A '
)
ORDER BY name;

(p) Find the names of the instructors who teach every course taught in his/her department. Order result in reverse alphabetical order

SELECT name
FROM instructor
WHERE NOT EXISTS (
	( 
    	SELECT course_id
    	FROM course
    	WHERE course.dept_name = instructor.dept_name
    ) 
    NOT IN 
    (
    	SELECT course_id
    	FROM teaches
    	WHERE teaches.ID = instructor.ID
    )
)
ORDER BY name DESC;

(q)Consider the following SQL query on the university schema:
SELECT AVG(salary) - (SUM(salary)/COUNT(*))
FROM instructor;
We might expect that the result of this query is zero since the average of a set of numbers is defined to be the sum of the numbers divided by the number of numbers. Indeed, this is true for the example instructor relation in Figure 2.1. However, there are other possible instances of that relation for which the result would NOT be zero. Give one such instance, and explain why the results would not be zero

➡️ Answer: Include NULL value
For example, suppose there is a relationship consisting of five rows (where one pay value is null)
Where AVG(salary) ignores the null value and calculates SUM(salary) / 4 to give the correct average value,
but SUM(salary) / COUNT() = SUM(salary) / 5, SUM(salary) / 4 ! = SUM(salary) / 5
Therefore, AVG(salary) - SUM(salary) / COUNT() is non-zero

---

HW3

E-R to DDL

CREATE TABLE customer (
	ID CHAR(5) PRIMARY KEY,
	name VARCHAR(20),
	street VARCHAR(20),
	city VARCHAR(20),
	province VARCHAR(20)
);

CREATE TABLE customer_phone (
	ID CHAR(5),
	phone VARCHAR(20),
	PRIMARY KEY (ID, phone),
	FOREIGN KEY (ID) REFERENCES customer
);

CREATE TABLE account (
	account_number VARCHAR(20) PRIMARY KEY,
	balance DECIMAL(20,2),
	ID CHAR(5),
	date DATE,
	FOREIGN KEY (ID) REFERENCES customer
);

CREATE TABLE transaction (
	account_number VARCHAR(20),
	datetime DATETIME,
	amount DECIMAL(20, 2),
	FOREIGN KEY (account_number) REFERENCES account
	PRIMARY KEY (account_number, datetime, amount)
);

More SQL

(a) (2 pt.) How many stores are found in the database?

SELECT COUNT(DISTINCT store_id) AS COUNT_STORE
FROM store;

(b) (2 pt.) How many unique last names are found in the actor relation?

SELECT COUNT(DISTINCT last_name) FROM actor;

(c) (2 pt.) According to the database, how many inventories (DVDs) have not been returned (inventories that have not been returned do not have return_date)?

SELECT COUNT(*) AS COUNT_NOT_RETURNED
FROM rental
WHERE return_date IS NULL;

(d) (2 pt.) How many distinct customers have rented a movie title(s) from staff_id=1?

SELECT COUNT(DISTINCT customer_id) AS COUNT_CUSTOMER
FROM rental
WHERE staff_id = 1;