SQL Queries: Examples on Database Operations

Posted on Feb 7, 2025 in Graphic Arts

SQL Queries for Member, Book, and Borrowed Tables

The following SQL queries operate on these tables:

• member(memb_no, name, age)
• book(isbn, title, authors, publisher)
• borrowed(memb_no, isbn, date)

1. Members Who Borrowed McGraw-Hill Books

Print the names of members who have borrowed any book published by “McGraw-Hill”.

SELECT name
FROM member m, book b, borrowed l
WHERE m.memb_no = l.memb_no
AND l.isbn = b.isbn
AND b.publisher = 'McGraw-Hill';

Relational Algebra:

t1 <-- πisbn(σpublisher = "McGraw-Hill"(books))
πname((member ⋈ borrowed) ⋈ t1)

2. Members Who Borrowed All McGraw-Hill Books

Print the names of members who have borrowed all books published by McGraw-Hill.

SELECT DISTINCT m.name
FROM member m
WHERE NOT EXISTS (
    (SELECT isbn
     FROM book
     WHERE publisher = 'McGraw-Hill')
    EXCEPT
    (SELECT isbn
     FROM borrowed l
     WHERE l.memb_no = m.memb_no)
);

Relational Algebra:

t1 <-- πisbn(σpublisher = "McGraw-Hill"(books))
πname, isbn((member ⋈ borrowed) ÷ t1)

3. Members Borrowing 5+ Books from a Publisher

For each publisher, print the names of members who have borrowed more than five books of that publisher.

SELECT publisher, name
FROM (
    SELECT publisher, name, COUNT(isbn) AS count_books
    FROM member m, book b, borrowed l
    WHERE m.memb_no = l.memb_no
    AND l.isbn = b.isbn
    GROUP BY publisher, name
) AS membpub
WHERE count_books > 5;

Relational Algebra:

t1 <-- member ⋈ borrowed ⋈ (σpublisher = "McGraw-Hill"(books))
πname(σcountisbn > 5(memb_noGCOUNT-DISTINCT(isbn) AS countisbn(t1)))

4. Average Number of Books Borrowed

Print the average number of books borrowed per member.

WITH memcount AS (
    SELECT COUNT(*) AS total_members
    FROM member
)
SELECT CAST(COUNT(*) AS REAL) / (SELECT total_members FROM memcount) AS average_books
FROM borrowed;

Relational Algebra:

t1 <-- member ⋈ borrowed ⋈ books
πpublisher, name(σcountisbn > 5(memb_noGCOUNT-DISTINCT(isbn) AS countisbn(t1)))

SQL Queries for Employee and Company Tables

The following SQL queries operate on these tables:

• employee(employee_name, street, city)
• works(employee_name, company_name, salary)
• company(company_name, city)
• manages(employee_name, manager_name)

1. Employees of First Bank Corporation

Find the names of all employees who work for First Bank Corporation.

SELECT employee_name
FROM works
WHERE company_name = 'First Bank Corporation';

2. Employees Living in Company Cities

Find all employees who live in the same cities as the companies for which they work.

SELECT e.employee_name
FROM employee e, works w, company c
WHERE e.employee_name = w.employee_name
AND e.city = c.city
AND w.company_name = c.company_name;

3. Employees on Same Street as Managers

Find all employees who live in the same cities and on the same streets as their managers.

SELECT P.employee_name
FROM employee P, employee R, manages M
WHERE P.employee_name = M.employee_name
AND M.manager_name = R.employee_name
AND P.street = R.street
AND P.city = R.city;

4. Employees Earning Above Company Average

Find all employees who earn more than the average salary of all employees of their company.

SELECT employee_name
FROM works T
WHERE salary > (
    SELECT AVG(salary)
    FROM works S
    WHERE T.company_name = S.company_name
);

5. Company with the Smallest Payroll

Find the company that has the smallest payroll.

SELECT company_name
FROM works
GROUP BY company_name
HAVING SUM(salary) <= ALL (
    SELECT SUM(salary)
    FROM works
    GROUP BY company_name
);

SQL Queries: Accidents Involving Cars

The following SQL queries operate on these tables:

• person(driver_id, name, address)
• car(license, model, year)
• accident(report_number, date, location)
• owns(driver_id, license)
• participated(report_number, license, driver_id, damage_amount)

1. Accidents Involving John Smith’s Cars

Find the number of accidents in which the cars belonging to “John Smith” were involved.

SELECT COUNT(*)
FROM accident
WHERE EXISTS (
    SELECT *
    FROM participated, owns, person
    WHERE owns.driver_id = person.driver_id
    AND person.name = 'John Smith'
    AND owns.license = participated.license
    AND accident.report_number = participated.report_number
);

2. Update Damage Amount

Update the damage amount for the car with license number “AABB2000” in the accident with report number “AR2197” to $3000.

UPDATE participated
SET damage_amount = 3000
WHERE report_number = 'AR2197'
AND license = 'AABB2000';