Scribd
Upload
15 views

Basic SQL Exercise Correction

Uploaded by

hdthai2005
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views

Basic SQL Exercise Correction

Uploaded by

hdthai2005
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

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

You might also like