-
Notifications
You must be signed in to change notification settings - Fork 0
Part F
Daniel Sawyer edited this page Nov 30, 2023
·
2 revisions
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
age INT,
gender VARCHAR(10),
f_name VARCHAR(50),
l_name VARCHAR(50),
address VARCHAR(100),
`rank` INT,
title VARCHAR(50),
dept_id INT,
super_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id),
FOREIGN KEY (super_id) REFERENCES Employee(emp_id)
);
CREATE TABLE Applicant (
app_id INT PRIMARY KEY,
age INT,
gender VARCHAR(10),
f_name VARCHAR(50),
l_name VARCHAR(50),
address VARCHAR(100)
);
CREATE TABLE Marketing_Site (
site_id INT PRIMARY KEY,
site_name VARCHAR(50),
location VARCHAR(100)
);
CREATE TABLE Site_Employment (
site_id INT,
emp_id INT,
PRIMARY KEY (site_id, emp_id),
FOREIGN KEY (site_id) REFERENCES Marketing_Site(site_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);
CREATE TABLE Customer (
cust_id INT PRIMARY KEY,
age INT,
gender VARCHAR(10),
f_name VARCHAR(50),
l_name VARCHAR(50),
address VARCHAR(100),
emp_id INT,
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id)
);
CREATE TABLE Vendor (
vendor_id INT PRIMARY KEY,
vendor_name VARCHAR(50),
address VARCHAR(100),
account_number VARCHAR(20),
credit DECIMAL(10, 2),
url VARCHAR(100)
);
CREATE TABLE Part_supplies (
type VARCHAR(50),
v_id INT,
price DECIMAL(10, 2),
FOREIGN KEY (v_id) REFERENCES Vendor(vendor_id),
PRIMARY KEY (type, v_id)
);
CREATE TABLE Product (
product_id INT PRIMARY KEY,
type VARCHAR(50),
size VARCHAR(20),
price DECIMAL(10, 2),
weight DECIMAL(10, 2),
style VARCHAR(50)
);
CREATE TABLE Product_Materials (
product_id INT,
type VARCHAR(50),
quantity INT,
PRIMARY KEY (product_id, type),
FOREIGN KEY (product_id) REFERENCES Product(product_id)
);
CREATE TABLE Job_Position (
job_id INT PRIMARY KEY,
post_date DATE,
description TEXT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
CREATE TABLE Phone (
person_id INT,
phone_number VARCHAR(15),
PRIMARY KEY (person_id, phone_number)
-- person_id can refer to any person (employee, applicant, customer)
);
CREATE TABLE Part (
type VARCHAR(50) PRIMARY KEY
);
CREATE TABLE Sale (
order_id INT PRIMARY KEY,
sale_time TIMESTAMP,
product_id INT,
site_id INT,
emp_id INT,
cust_id INT,
FOREIGN KEY (product_id) REFERENCES Product(product_id),
FOREIGN KEY (site_id) REFERENCES Marketing_Site(site_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY (cust_id) REFERENCES Customer(cust_id)
);
CREATE TABLE Interview (
job_id INT,
emp_id INT,
applicant_id INT,
time TIMESTAMP,
grade INT,
PRIMARY KEY (job_id, emp_id, applicant_id),
FOREIGN KEY (job_id) REFERENCES Job_Position(job_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
FOREIGN KEY (applicant_id) REFERENCES Applicant(app_id)
);
CREATE TABLE Salary (
trans_id INT,
emp_id INT,
pay_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id),
PRIMARY KEY (trans_id, emp_id)
);---View 1
CREATE VIEW View1 AS
SELECT
e.emp_id,
AVG(s.amount) AS average_monthly_salary
FROM
Employee e
JOIN
Salary s ON e.emp_id = s.emp_id
GROUP BY
e.emp_id;
SELECT * FROM View1;
---View 2
CREATE VIEW View2 AS
SELECT
i.applicant_id,
i.job_id,
COUNT(*) AS passed_interviews
FROM
Interview i
WHERE
i.grade > 60
GROUP BY
i.applicant_id, i.job_id;
SELECT * FROM View2;
---View 3
CREATE VIEW View3 AS
SELECT
p.type,
COUNT(s.product_id) AS total_items_sold
FROM
Sale s
JOIN
Product p ON s.product_id = p.product_id
GROUP BY
p.type;
SELECT * FROM View3;
--- View 4
CREATE VIEW View4 AS
SELECT
materials.product_id,
SUM(supplies.price * materials.quantity) AS total_part_cost
FROM
Product_Materials materials
JOIN
Part_supplies supplies ON materials.type = supplies.type
GROUP BY
materials.product_id;
SELECT * FROM View4;- Return the ID and Name of interviewers who participate in interviews where the interviewee's name is "Hellen Cole" arranged for job "11111"
SELECT
e.emp_id,
CONCAT(e.f_name, ' ', e.l_name) AS interviewer_name
FROM
Interview i
JOIN
Employee e ON i.emp_id = e.emp_id
JOIN
Applicant a ON i.applicant_id = a.app_id
WHERE
a.f_name = 'Hellen' AND a.l_name = 'Cole' AND i.job_id = 11111;- Return the ID of all jobs which are posted by department "Marketing" in January 2011.
SELECT
j.job_id
FROM
Job_Position j
JOIN
Department d ON j.dept_id = d.dept_id
WHERE
d.dept_name = 'Marketing' AND MONTH(j.post_date) = 1 AND YEAR(j.post_date) = 2011;- Return the ID and Name of the employees having no supervisees.
SELECT
e.emp_id
FROM
Employee e
LEFT JOIN
Employee e2 ON e.emp_id = e2.super_id
WHERE
e2.emp_id IS NULL;- Return the Id and Location of the marketing sites with no sale records during March 2011.
SELECT
ms.site_id,
ms.location
FROM
Marketing_Site ms
LEFT JOIN
Sale s ON ms.site_id = s.site_id AND MONTH(s.sale_time) = 3 AND YEAR(s.sale_time) = 2011
WHERE
s.order_id IS NULL;- Return the job's id and description, which does not hire a suitable person one month after it is posted.
SELECT
j.job_id,
j.description
FROM
Job_Position j
LEFT JOIN
Interview i ON j.job_id = i.job_id
WHERE
i.applicant_id IS NULL AND j.post_date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);- Return the ID and Name of the salespeople who have sold all product types whose price is above $200.
SELECT
e.emp_id
FROM
Employee e
JOIN
Sale s ON e.emp_id = s.emp_id
JOIN
Product p ON s.product_id = p.product_id
WHERE
p.price > 200
GROUP BY
e.emp_id
HAVING
COUNT(DISTINCT p.type) = (SELECT COUNT(DISTINCT type) FROM Product WHERE price > 200);- Return the department's id and name, which has no job post during 1/1/2011 and 2/1/2011.
SELECT
d.dept_id,
d.dept_name
FROM
Department d
LEFT JOIN
Job_Position j ON d.dept_id = j.dept_id AND j.post_date BETWEEN '2011-01-01' AND '2011-02-01'
WHERE
j.job_id IS NULL;- Return the ID, Name, and Department ID of the existing employees who apply for job "12345".
SELECT
e.emp_id,
CONCAT(e.f_name, ' ', e.l_name) AS employee_name,
e.dept_id
FROM
Employee e
JOIN
Interview i ON e.emp_id = i.emp_id
WHERE
i.job_id = 12345;- Return the best seller's type in the company (sold the most items).
Select p.type
From Product p
Where p.product_id =
(Select s.product_id
From Sale s
Group by s.product_id
Order by Count(s.product_id) DESC
Limit 1
);- Return the product type whose net profit is highest in the company (money earned minus the part cost).
SELECT
p.type,
(SUM(s.quantity * p.price) - SUM(pm.quantity * ps.price)) AS net_profit
FROM
Sale s
JOIN
Product p ON s.product_id = p.product_id
JOIN
Product_Materials pm ON p.product_id = pm.product_id
JOIN
Part_supplies ps ON pm.type = ps.type
GROUP BY
p.type
ORDER BY
net_profit DESC LIMIT 1;- Return the name and id of the employees who have worked in all departments after being hired by the company.
SELECT
e.emp_id,
e.emp_name
FROM employee e
JOIN dept_id d ON e.dept_id = d.dept_id
JOIN dept_records r ON e.emp_id = r.emp_id AND d.dept_id = r.dept_id
GROUP BY e.emp_id, e.emp_name
HAVING
COUNT(DISTINCT d.dept_id) = COUNT(DISTINCT r.dept_id);- Return the name and email address of the interviewee who is selected.
Select
CONCAT(a.f_name, ' ',a.l_name) as name,
CONCAT(a.f_name,'@company.com') as email
FROM Applicant a
where Interview.app_id = a.app_id;- Retrieve the names, phone numbers, and email addresses of the interviewees selected for all the jobs they apply for.
Select
CONCAT(a.f_name, ' ',a.l_name) as name,
CONCAT(a.f_name,'@company.com') as email,
pn.Number
From Applicant a
JOIN Phone pn ON a.app_id = pn.personal_id;
Where a.app_id =
(Select a.app_id
From applicant a
Where View2.passed_interviews = Count(Interview.app_id));- Return the employee's name and id whose average monthly salary is the highest in the company.
Select
CONCAT(e.f_name, ' ',e.l_name) as name,
e.emp_id
From Employee e
where e.emp_id =
(Select v.emp_id
From View1 v
Order by v.average_monthly_salary DESC
Limit 1);- Return the ID and Name of the vendor who supplies part whose name is "Cup" and weight is smaller than 4 pounds, and the price is lowest among all vendors.
SELECT
v.vendor_id,
v.v_name
FROM vendor v
JOIN part_supplies ps ON v.vendor_id = ps.vendor_id
JOIN product_material pm ON ps.type = pm.type
JOIN product p ON pm.product_id = p.product_id
WHERE
p.name = 'Cup' AND
p.weight < 4 AND
ps.price = (
SELECT DISTINCT ON (pm.type)
price
FROM
part_supplies
WHERE
pm.type = part_supplies.type
ORDER BY pm.type, price);