Quartz 5

06 SQL statements (part 2)

8 min read

SQL statements (part 2)

Structured Query Language (SQL) statements (part 2)

TO DO:

SQL join: allows the combination of data from 2 or more sets of data (tables)

  • Cross join: returns the Cartesian product of rows from the tables in the join
  • Combines each row in the 1st table with each row in the 2nd table
E.g. SELECT * FROM Student, Class
E.g. SELECT * FROM Student
INNER JOIN Class ON
Student.ClassID = Class.ID		E.g. SELECT * FROM Student, Class
WHERE
Student.ClassID = Class.ID
  • E.g.
SELECT [order].id, [customer].companyname FROM [order]
INNER JOIN customer ON [order].customerid = customer.id
  • List order ID and customer’s company name of the records in the Order table which contains valid customer information (Customers and Orders)
  • Left outer join / left join
  • Returns:
  • All rows from both tables that meet the WHERE clause criteria, same as an INNER join result set
  • All rows from the left table (A) that do not have a matching row that exists in the right table (B) will also be included in the result set. The columns being selected from the right side table will return NULL values
  • E.g.
SELECT Student.Name, Class.Name FROM Student
LEFT OUTER JOIN
Class ON Student.ClassID = Class.ID
  • E.g.
SELECT [Order].Id, Customer.CompanyName FROM [Order]
LEFT OUTER JOIN customer ON [Order].CustomerId = Customer.Id
  • List all orders, display order ID and customer’s company name (if any), of the records in the Order table12
  • To check no. of all records in Order table: select COUNT(*) from [order] ⇒ 830 ⇒ verify that all 830 records in ORDER table are retrieved
  • Out of 830 records, some has companyname with NULL
SELECT [order].id, [customer].companyname
FROM [order] LEFT OUTER JOIN Customer
ON [order].customerid = customer.id
where [customer].companyname is NULL
OUTPUT: 29 rows returned (i.e. 29 records)
  • E.g.
SELECT [Order].Id, [orderdetail].ProductId from [order]
INNER JOIN [orderdetail] on [order].id = [orderdetail].orderid WHERE [order].CustomerId=‘QUICK’
  • List order ID, product ID of orders which are ordered by customers with ID = ‘QUICK’ (order and orderdetail)
  • E.g.
SELECT [order].id, [product].productname FROM [order]
INNER JOIN [orderdetail] ON [order].id = [orderdetail].orderId
INNER JOIN [product] on [OrderDetail].productId = [product].id
WHERE [order].customerid = 'QUICK'
Order By [order].ID DESC
  • List order ID, product name of each orders which is ordered by Customers with ID = ‘QUICK’
  • Note: sequence of both INNER JOIN lines doesn’t matter, can be swapped (?)
INSERT statement
  • Adds row(s) to a table
INSERT INTO table [(columns list)]
VALUES (values list)
  • E.g.
INSERT INTO Student (ID, Name, NRIC)
VALUES (29, ‘TAN TEST TEST’, ‘T1234567A’)
UPDATE statement
  • Changes data in existing rows
UPDATE table
SET column_name1 = column_value1, column_name2 = column_value2…
[WHERE conditions]
  • E.g.
UPDATE Student SET Name = ‘Tan’, NRIC = ‘T1234567Z’
WHERE id=29
DELETE statement
DELETE FROM table
[WHERE conditions]
  • E.g.
DELETE FROM Student WHERE id=29

Methods to copy all records into a different table:

  • Right click on the original table then “Copy Create statement”, paste into Execute SQL and change the details
INSERT INTO Category2 (ID, categoryname, description)
SELECT ID, categoryname, description FROM Category ⇒ subquery
INSERT INTO Category2
SELECT ID categoryname, description FROM Category

E.g.

INSERT INTO Category2 (ID, categoryname, description)
VALUES (9, ‘Frozen’, ‘Frozen Food’)
  • Insert a new category into Category2 table, ID = 9, Name = ‘Frozen’, Description = ‘Frozen Food’
SELECT statement
  1. Identify table(s) that contains the SELECT field
  2. Identify table(s) that contains the WHERE field
  3. Identify additional tables between the tables above (if any)
  4. Join all the tables – INNER JOIN or LEFT OUTER JOIN
  5. Any conditions? WHERE
  6. Any sorting orders? ORDER BY ASC (A-Z), DESC (Z-A)
Select with group by clause
  • Use COUNT with GROUP BY
  • Use Count() to count different items in separate groups
  • E.g. select no. of students from each class
SELECT COUNT(*), ClassID FROM Student GROUP BY ClassID
  • E.g. List order ID and no. of items in each order
SELECT orderid,
COUNT(*) AS [No of Order]
FROM OrderDetail
GROUP BY [orderID]
To check number of records from an order:
SELECT * FROM orderdetail WHERE orderid = 10248

Using AVG with GROUP BY

  • Use AVG() to get average of a column in separate groups
  • E.g. get average age of student in each class
SELECT AVG(Age), ClassID from Student GROUP BY ClassID
  • E.g. list supplier company name, average price of products per supplier (product, supplier)
SELECT s.companyname AS [Supplier],
AVG(p.UnitPrice) AS [Average Price]
from
Product p
INNER JOIN Supplier s
ON p.supplierid = s.id
GROUP BY s.id
My ans: (correct?)
SELECT [supplier].companyname, AVG([product].unitprice)
FROM Supplier
INNER JOIN product ON [supplier].id = [product].supplierid
GROUP BY [supplier].companyname

Using SUM with GROUP BY

  • Use SUM() to get the sum of a column in separate groups
  • E.g. get total sum of the age of student in each class
SELECT SUM(Age), ClassID from Student GROUP BY ClassID
  • E.g. list CustomerName, and total no. of item quantity in each customer (Order, Orderdetail, customer). Pick 1 customer to check total no. of item quantity is correct
SELECT c.companyname AS [CustomerName],
SUM (d.quantity) as [Quantity], c.ID
FROM Customer c
INNER JOIN [Order] o
ON c.ID = o.customerID
INNER JOIN [orderDetail] d
ON o.d = d.orderID
GROUP BY c.ID
My ans: (correct? except ans key included customer Id)
SELECT [customer].contactname, [orderdetail].quantity
FROM [customer]
INNER JOIN [orderdetail] ON [order].id = [orderdetail].orderid
INNER JOIN [order] ON [order].customerid = [customer].id
GROUP BY [customer].contactname
SELECT with GROUP BY clause
  • Restrict rows with Having works like the WHERE clause, but is applicable to groups
  • vs WHERE clause applied to rows
  • E.g. Retrieve rows with filter = classID<>2 then group by classID
SELECT SUM(Age), ClassID FROM Student
WHERE ClassID <> 2 GROUP BY ClassID
  • E.g. Retrieve rows without filter then (group by ClassID) filter = classID <> 2
SELECT SUM (AGE), ClassID FROM Student GROUP BY ClassID HAVING ClassID<>2
  • E.g. List customerID and no. of orders which a customer placed. Display list in descending order based on no. of orders (Order Table)
SELECT customerID,
COUNT(*) as [number of orders]3
FROM [order]
GROUP BY customerID
ORDER BY [number of orders] desc
  • E.g. List customerID and no. of orders which a customer placed and no. of orders > 15. Display list in descending order based on no. of orders
SELECT customerID,
COUNT(*) as [number of orders]
FROM [order]
GROUP BY customerID
Having [number of orders] > 15
ORDER BY [number of orders] DESC
NOTE: don’t use WHERE here ⇒ error4
Use Having because it’s by groups (?)
  • E.g. List customerID and no. of orders which a customer placed and no. of orders > 5. The order ShipCountry is from ‘Germany’. Display list in descending order based on no. of orders
SELECT customerID,
COUNT(*) as [number of orders]
FROM [order]
WHERE ShipCountry = 'Germany'
GROUP BY customerID
HAVING [number of orders] > 5
ORDER BY [number of orders] DESC
SELECT customerID,
COUNT(*) as [number of orders]
FROM [order]
GROUP BY customerID
HAVING [number of orders] > 5
AND ShipCountry = ‘Germany’
ORDER BY [number of orders] DESC

Practice: Library database

Table descriptions
Book(BookID, Name, AuthorID)
Member(MemberID, Name, Gender)
Author(AuthorID, Name)
Loans(LoanID, MemberID, BookID)		ERD
SELECT statement to list the Book Name of all the books in the library sorted in descending order of the book name.
SELECT Name FROM Book ORDER BY Name DESC
SELECT statement to list the Book Name and Author Name for all the books in the library sorted in ascending order of the book name
SELECT Book.Name, Author.Name FROM Book
INNER JOIN Author ON Book.AuthorID = Author.AuthorID
ORDER BY Book.Name
SELECT statement that lists the Member Name, Book Name of the loans that the members with Gender = ‘F’ took, sorted in ascending order of the member’s name.
My ans:
SELECT Member.Name, Book.Name
FROM Member
INNER JOIN Loans ON Member.MemberID = Loans.MemberID
INNER JOIN Book on Loans.BookID = Book.BookID
WHERE Member.Gender = ‘F’
ORDER BY Member.Name ASC
SELECT Member.Name, Book.Name FROM Loans
INNER JOIN Book ON Loans.BookID = Book.BookID
INNER JOIN Member on Loans.MemberID = Member.MemberID WHERE Member.Gender = ‘F’
ORDER BY Member.Name
INSERT statement that is used for adding a new member with MemberID=927, Name = 'Mark Tan', Gender='M'
INSERT INTO Member (MemberID, Name, Gender)
VALUES (927, ‘Mark Tan’, ‘M’)
UPDATE statement that is used for updating an existing book with BookID=92 with the updated Name = cCode Complete II' and AuthorID=827
UPDATE BOOK
SET Name = ‘Code Complete II’, AuthorID = 827
WHERE BookID = 92

2025 C1 WA2:

  1. (a)


Table description (for (b)):

NOTE: must list in the order given in qn
For SELECT statements, all tables needs to be JOIN together before other operations (such as WHERE and ORDER BY) can be applied
  1. (c)
SELECT Movie.title, Actor.name, Actor.date_of_birth, Actor.nationality
FROM Movie
INNER JOIN Movie_Actor ON Movie.id = Movie_Actor.movie_id
INNER JOIN Actor ON Actor.id = Movie_Actor.actor_id
WHERE Actor.nationality = 'American'
ORDER BY Actor.date_of_birth DESC

2025 C1 Promos:

  • Note: primary keys must be unique and cannot be changed
    ⇒ primary key should be e.g. CustomerID, not CustomerName (since names can be exactly the same)
  • For the alternative answer without OrderID, each order is identified by the customer, the bento, AND the timestamp
Q:
A:

Use COUNT not SUM!!
Use GROUP BY to collate the number by each bento

Comments from the Word document

Footnotes

  1. Comment by ANDREA TAN KAI XUAN HCI: from chatgpt: 
    ✅ Show every row from [Order] (even if it has no matching customer)
    ✅ If there is a matching Customer, show their CompanyName
    ✅ If not, CompanyName will be NULL ↩

  2. Comment by ANDREA TAN KAI XUAN HCI: but how does this list all orders ↩

  3. Comment by ANDREA TAN KAI XUAN HCI: from chatgpt: counts all rows in [Order] for each unique CustomerID ↩

  4. Comment by ANDREA TAN KAI XUAN HCI: from chatgpt: 
    SELECT CustomerID,
    COUNT(*) AS [number of orders]
    FROM [Order]
    WHERE [number of orders] > 15 — ❌ INVALID here
    GROUP BY CustomerID
    ORDER BY [number of orders] DESC;
    The problem is:
    WHERE is evaluated before the SELECT clause (where [number of orders] is defined).
    So at the time SQL processes WHERE, [number of orders] does not yet exist. ↩

Graph View

Backlinks