RELATIONAL DATABASE &

QUERY REVIEW
Lê Hồng Hải
UET-VNUH
Relational DB

2
Relational DB

 The data in
Relational DB is
stored in objects
called tables

3
Keys

 Primary key: uniquely identifies each

record in a table
◼ A table can have only ONE primary key. This
primary key can consist of single or multiple
columns
 Foreign key: Value in one relation must
appear in another
◼ Example: customer_number in the orders table
is a foreign key from orders referencing the
customers

4
Sample Database

https://www.mysqltutorial.org/mysql-sample-
database.aspx
5
Sample Database

 Customers: stores customer’s data

 Products: stores a list of scale model cars
 ProductLines: stores a list of product line
categories
 Orders: stores sales orders placed by customers
 OrderDetails: stores sales order line items for
each sales order
 Payments: stores payments made by customers
based on their accounts
 Employees: stores all employee information as
well as the organization structure such as who
reports to whom.
 Offices: stores sales office data
6 6
STRUCTURED
QUERY
LANGUAGE
SQL

 SQL stands for Structured Query

Language (/ˌɛsˌkjuːˈɛl/ sometimes
/ˈsiːkwəl/ "sequel" )
 SQL lets you access and manipulate
data in relational databases

8
SQL (Structured Query Language)

 SQL ~ Relational DB

9
Some SQL Operations

 Query
◼ SELECT
 Sorting data
◼ ORDER BY
 Filter data
◼ WHERE, AND, OR, IN, BETWEEN, LIKE, LIMIT,
IS NULL
 Join Tables
◼ Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN,
Self-join

10 10
Sort values in multiple columns

SELECT contactLastname, contactFirstname

FROM customers
ORDER BY contactLastname,
contactFirstname

11
Sort a result set by an expression

SELECT orderNumber, orderLineNumber,

quantityOrdered * priceEach AS subtotal
FROM orderdetails
ORDER BY subtotal DESC;

12
WHERE clause with AND operator

 SELECT lastname, firstname,

jobtitle, officeCode
FROM employees
WHERE jobtitle = 'Sales Rep' AND
officeCode = 1

13
MySQL WHERE clause with the IN operator example

 SELECT firstName, lastName, officeCode

FROM employees
WHERE officeCode IN (1 , 2, 3)
ORDER BY officeCode;

14
WHERE with the LIKEoperator example

 SELECT firstName, lastName

FROM employees
WHERE lastName LIKE '%son’
ORDER BY firstName

15
WHERE clause with the IS NULL operator

SELECT lastName, firstName, reportsTo

FROM employees
WHERE reportsTo IS NULL;

16
MySQL supports the following types of joins:

1. Inner join
2. Left join
3. Right join
4. Cross join

17
INNER JOIN

 The INNER JOIN clause compares each

row in the t1 table with every row in the
t2 table based on the join condition
 If rows from both tables cause the join
condition to evaluate to TRUE the INNER
JOIN creates a new row whose columns
contain all columns of rows from the
tables

18
INNER JOIN:

Product Purchase
name category prodName store

Gizmo gadget Gizmo Wiz

Camera Photo Camera Ritz

OneClick Photo Camera Wiz

SELECT Product.name, Product.category, Purchase.store name Category store

FROM Product Gizmo gadget Wiz
INNER JOIN Purchase
Camera Photo Ritz
ON Product.name = Purchase.prodName
Camera Photo Wiz

19 19
LEFT JOIN

 Returns all rows from the left table

regardless of whether a row from the left
table has a matching row from the right
table or not
 If there is no match, the columns of the
row from the right table will contain NULL

20
 LEFT OUTER JOIN:

Product Purchase
name category prodName store

Gizmo gadget Gizmo Wiz

Camera Photo Camera Ritz

OneClick Photo Camera Wiz

name category store

SELECT Product.name, Product.category, Purchase.store
Gizmo gadget Wiz
FROM Product
LEFT OUTER JOIN Purchase Camera Photo Ritz

ON Product.name = Purchase.prodName Camera Photo Wiz

OneClick Photo NULL

21 21
Hands-on

 Find the customers who have not placed

any orders

22
SELFT JOIN

 Selfjoin that joins a table to itself using the

inner join or left join
 The self join is often used to query hierarchical
data or to compare a row with other rows
within the same table

23
SELFT JOIN

 To perform a self join, you must use table

aliases to not repeat the same table name
twice in a single query

24
SELF JOIN example

 The reportsTo column is used to

determine the manager id of an employee

25
GROUP BY

 The GROUP BY clause groups a set of rows into a

set of summary rows by values of columns or
expressions
 The GROUP BY clause returns one row for each
group
 You often use the GROUP BY clause with
aggregate functions such as SUM, AVG, MAX,
MIN, and COUNT

26
GROUP BY syntax

SELECT
c1, c2,..., cn, aggregate_function(ci)
FROM
table
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;

27
Aggregate function

AVG() Return the average of non-NULL values

COUNT() Return the number of rows in a group,

including rows with NULL values

GROUP_CONCAT() Return a concatenated string

MAX() Return the highest value (maximum) in a set
of non-NULL values

MIN() Return the lowest value (minimum) in a set

of non-NULL values

STDEV() Return the population standard deviation

SUM() Return the summation of all non-NULL values

a set

28
SUM function

29 29
GROUP BY evaluation

 MySQL evaluates the GROUP BY clause after the

FROM, WHERE and SELECT clauses and before
the HAVING , ORDER BY and LIMIT clauses

30
Hands-on

 Give a list of 10 customers who buy the

most
 Find orders whose total values are greater
than 60K

31
Hands-on

1. Platinum customers who have orders with the

volume greater than 100K
2. Gold customers who have orders with the volume
between 10K and 100K
3. Silver customers who have orders with the volume
less than 10K

32
ROLLUP

 The ROLLUP clause is an extension of the

GROUP BY clause
SELECT
select_list
FROM
table_name
GROUP BY
c1, c2, c3 WITH ROLLUP;

33
ROLLUP

SELECT
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;

34
Date functions

 Allow you to
manipulate date
and time data
effectively

35
Hands-on

 Find the company's monthly sales

36
MySQL Window Functions

 Like the aggregate functions with the

GROUP BY clause, window functions also
operate on a subset of rows but they do
not reduce the number of rows returned
by the query
 MySQL has supported window functions since version
8.0.

37
Window Function

SELECT
fiscal_year,
sales_employee,
sale,
SUM(sale) OVER (PARTITION BY fiscal_year)
total_sales
FROM
sales;

38
Subquery

 Subquery is a query nested within another

query such as SELECT, INSERT, UPDATE
or DELETE
 A subquery is called an inner query while
the query that contains the subquery is
called an outer query

39
Subquery in WHERE clause

 When the query is executed, the subquery runs

first and returns a result set. This result set is
used as an input for the outer query

40
Subquery example

 For example, the following query returns

the customer who has the maximum
payment

SELECT
customerNumber,
checkNumber,
amount
FROM
payments
WHERE
amount = (SELECT MAX(amount) FROM payments);

41
Subquery example

 You can use a subquery with NOT IN operator to

find the customers who have not placed any
orders as follows

SELECT
customerName
FROM
customers
WHERE
customerNumber NOT IN (SELECT DISTINCT
customerNumber
FROM orders)

42
Subquery in FROM clause

 A derived table is a virtual table returned

from a SELECT statement

43
Correlated subquery

 In the previous examples, you notice that a subquery

is independent. It means that you can execute the
subquery as a standalone query
 Unlike a standalone subquery, a correlated subquery
is a subquery that uses the data from the outer
query

44
Correlated subquery example

 Select products whose buy prices are greater than the average
buy price of all products in each product line

SELECT
productname,
buyprice
FROM
products p1
WHERE
buyprice > (SELECT
AVG(buyprice)
FROM
products
WHERE
productline = p1.productline)

45
Question

 Find the amount customers owe and their

remaining credit

46
Full-Text Search

 Partial text searching by using the LIKE

operator or regular expressions has some
limitations:
◼ Has to scan the whole table to find the exact
text based on a pattern in the LIKE statement
or pattern in the regular expressions
◼ Difficult to have a flexible search query e.g., to
find products whose descriptions contain car
but not classic
◼ There is no way to specify which row in the
result set is more relevant to the search terms

47
Full-Text Search

 Before performing a full-text search in a

column of a table, you must index its data
 In MySQL, the full-text index is a kind of
index that has the name FULLTEXT

48
Full-Text Search

You use the MATCH() and AGAINST()

functions as the following query:

SELECT
productName,
productLine
FROM products
WHERE
MATCH(productName)
AGAINST('1932,Ford’)

49
THANKS YOU