October 13, 2024 IT3292E - DATABASE
BASIC SQL EXERCISE
CORRECTION
Minh N. Ta – Research Assistant @ BK.AI Center
minh@tnminh.com • tnminh.com
October 13, 2024 IT3292E - DATABASE
INTERACTIVE SESSION IS COMING!
Join at
slido.com
#2745 106
October 13, 2024 IT3292E - DATABASE
REMINDS OF SOME BASIC QUESTIONS
October 13, 2024 IT3292E - DATABASE 4
CREATE RELATION
CREATE TABLE book (
book_id CHAR(8) PRIMARY KEY,
author_id CHAR(8),
publication_date DATE NOT NULL,
ISBN VARCHAR(20)
CONSTRAINT fk_author_id FOREIGN KEY (author_id)
REFERENCES author(author_id);
);
October 13, 2024 IT3292E - DATABASE 5
RETRIEVE THE LIST OF COMMENTS FOR POST_ID
"20240319VTT1" IN ASCENDING ORDER OF COMMENT
DATE.
SELECT comment_id, comment, comment_date
FROM Comment c
WHERE c.post_id = '20240319vtt1'
ORDER BY c.comment_date ASC;
October 13, 2024 IT3292E - DATABASE 6
RETRIEVE THE LIST OF PATIENTS AND THE NUMBER
OF HIS/HER APPOINTMENTS
SELECT pp.patient_id,
pp.name,
COUNT(a.appointment_id) AS appointment_count
FROM Patient pp
JOIN Appointment a ON pp.patient_id = a.patient_id
GROUP BY pp.patient_id, pp.name;
October 13, 2024 IT3292E - DATABASE 7
RETRIEVE THE LIST OF PROJECTS AND THE NUMBER OF
EMPLOYEES WORKING ON EACH PROJECT.
Employee(employee_id, name, email, phone_number, hire_date,
salary, department_id)
Department(department_id, name, manager_id)
Project(project_id, name, start_date, end_date, location)
Workin(project_id, employee_id, pct_time)
Salary(salary_id, employee_id, salary_amount, effective_date)
Underline attributes are primary keys; italic attributes are foreign keys
October 13, 2024 IT3292E - DATABASE 8
RETRIEVE THE LIST OF PROJECTS AND THE NUMBER OF
EMPLOYEES WORKING ON EACH PROJECT.
SELECT w.project_id,
COUNT(w.employee_id) AS numberOfEmp, p.name
FROM workin w
JOIN project AS p
ON w.project_id = p.project_id
GROUP BY w.project_id;
October 13, 2024 IT3292E - DATABASE 9
RETRIEVE THE LIST OF BOOKS AND THEIR NUMBER OF
LOANS.
A SELECT B.title, COUNT(L.loan_id) AS loan_count
FROM Book B
LEFT JOIN Loan L ON B.book_id=L.book_id
GROUP BY B.title;
B SELECT B.title, COUNT(L.loan_id) AS loan_count
FROM Book B
JOIN Loan L ON B.book_id=L.book_id
GROUP BY B.title;
October 13, 2024 IT3292E - DATABASE
DEEPER DISCUSSION ABOUT SQL QUERY
October 13, 2024 IT3292E - DATABASE 11
RETRIEVE THE LIST OF MEMBERS WHO BORROW MORE THAN 50
BOOKS PER YEAR.
A SELECT M.member_id, M.name, COUNT(L.loan_id) AS loan_count
FROM Member M
JOIN Loan L ON M.member_id=L.member_id
WHERE YEAR(L.loan_date)=YEAR(CURDATE())
GROUP BY M.member_id, M.name
HAVING loan_count >50;
B SELECT M.member_id, M.name, COUNT(L.loan_id) AS loan_count
FROM Member M
JOIN Loan L ON M.member_id=L.member_id
GROUP BY M.member_id, M.name
HAVING loan_count >50
AND YEAR(L.loan_date)=YEAR(CURDATE());
October 13, 2024 IT3292E - DATABASE 12
RETRIEVE THE LIST OF STUDENTS WHO ARE MORE THAN 25 YEARS
OLD.
A SELECT *
FROM student
WHERE YEAR(date_of_birth) < CURDATE() - 25;
B SELECT *
FROM student
WHERE date_part('year',age(date_of_birth)) < 25;
October 13, 2024 IT3292E - DATABASE 13
RETRIEVE THE LIST OF EMPLOYEES WHO DO NOT WORK ON ANY
PROJECT.
A SELECT e.employee_id, e.name FROM employees AS e
WHERE e.employee_id NOT IN (
SELECT e.employee_id FROM employees AS e
JOIN workin AS w USING(employee_id)
);
B SELECT e.employee_id, e.name
FROM employees AS e
LEFT JOIN workin AS w USING (employee_id)
WHERE w.employee_id IS NULL;
October 13, 2024 IT3292E - DATABASE 14
RETRIEVE THE LIST OF PRODUCTS THAT HAVE NOT BEEN
ORDERED.
Product(product_id, name, description, price, category)
Customer(customer_id, name, email, address, phone_number)
Order(order_id, customer_id, order_date, total_amount)
Order_Detail(order_id, product_id, quantity, unit_price)
Payment(order_id, payment_date, amount, payment_method)
Underline attributes are primary keys; italic attributes are foreign keys
October 13, 2024 IT3292E - DATABASE 15
RETRIEVE THE LIST OF PRODUCTS THAT HAVE NOT BEEN
ORDERED.
SELECT p.product_id,
p.name
FROM product AS p
LEFT JOIN order_detail AS o
USING (product_id)
WHERE o.product_id IS NULL;
October 13, 2024 IT3292E - DATABASE 16
RETRIEVE THE LIST OF USER REGISTERED IN 2024
A SELECT *
FROM User
WHERE YEAR(registration_date) = 2024;
B SELECT *
FROM User
WHERE registration_date >= ‘2024-01-01'::date
AND registration_date <= ‘2024-12-31'::date;
October 13, 2024 IT3292E - DATABASE
THANKS FOR JOINING
WITH ME TODAY!
ANY QUESTIONS? DON’T HESITATE TO ASK
