Database

Management
Systems
Course No. 306008001 & 306008011
Instructor: Chih-Yuan Chou
Mar 12, 19, 26 & Mar 14, 21, 28, 2024
Lecture 4, 5, & 6- SQL
 Your 1st booked TA consulting session by group
We may change your responsible TAs temporarily if time conflicts occur. Note that all our TAs will be able to help you during the semester.

Sandy + Julian + Pei-Han John + Richard + Bo-Yi Wen-Jing + Meng-Mei Andy + Mavis

2024/3/14 11:00-11:30 Group 06 Group 01 Group 11 Group 08

(Thursday) 11:30-12:00 Group 05 Group 23 Group 02 Group 24

16:00-16:30 Group 16 Group 18 Group 07 Group 20

2024/3/14 16:30-17:00 Group 14 Group 17 Group 21 Group 12
(Thursday) 17:00-17:30 Group 09 Group 04 Group 22 Group 15
17:30-18:00 Group 19 Group 10 N/A Group 13

2024/3/15
16:30-17:00 Group 03
(Friday)

Location: College of Commerce Building 260526 Please get ready with your needs analysis findings (HW1), initial ERD,
(5th floor, IT Innovation Lab ) and proposed functional decomposition diagram. Also, kindly wait
Turn LEFT after leaving the elevators. patiently outside if the preceding group requires more time.
© 2024 Spring (112-2) NCCU Department of MIS, DBMS Course
 SQL

Introduction to SQL

SQL Overview
Structured Query Language

The standard for Relational Database Management Systems

(RDBMS)

RDBMS: A database management system that manages data as a

collection of tables in which all relationships are represented by
common values in related tables
 SQL Introduction
Nonprocedural language
American National Standards Institute (ANSI) prescribes a standard SQL.
Standardized by ANSI in 1986 and by ISO in 1987: SQL-86 or SQL/1
SQL continuously evolves.
SQL-86 SQL-89 SQL-92 SQL-1999 SQL-2003 SQL-2006 SQL-2008
SQL-2011 SQL-2016 SQL-2019
Several SQL dialects exist.
Transact-SQL (T-SQL): Microsoft SQL Server
PL/SQL: Oracle
PL/pgSQL: PostgreSQL

History of SQL
1970 E. F. Codd develops relational database concept Donald Chamberlin & Raymond Boyce
1974-79 System R with Sequel (later SQL) created at IBM Research Lab
1979 Oracle markets first relational DB with SQL
1981 SQL/DS first available RDBMS system on DOS/VSE
Others followed: INGRES (1981), IDM (1982), DG/SGL (1984), Sybase (1986)
1986 ANSI SQL standard released
Major ANSI standard updates in 1989, 1992, 1999, 2003, 2006, 2008, 2011, 2016,
2019
Today SQL is supported by most major database vendors
Oracle, Microsoft SQL Server, IBM DB2, MySQL, Postgre SQL, etc.
Original Purpose of SQL Standard
Specify syntax/semantics for data definition and manipulation
Define data structures and basic operations
Enable portability of database definition and application
modules
Specify minimal (level 1) and complete (level 2) standards
Allow for later growth/enhancement to standard (referential
integrity, transaction management, user-defined functions,
extended join operations, national character sets)

Benefits of a Standardized Relational Language

Reduced training costs
Productivity
Application portability
Application longevity
Reduced dependence on a single vendor
Cross-system communication
 Catalog
A set of schemas that constitute
the description of a database
SQL
Schema
Environment
The structure that contains
descriptions of objects created by
a user (base tables, views,
constraints)

Common SQL Categories

Data Definition Language (DDL)
Commands that define a database, including creating,
altering, and dropping tables and establishing
constraints
E.g., Create, drop, and alter tables, views, or
database, etc.
Data Manipulation Language (DML)
Commands that maintain and query a database
E.g., Insert, update, delete, and query data, etc.
Data Control Language (DCL)
Commands that control a database, including
administering privileges and committing data
E.g., Grant, revoke, alter password, etc.
 Other SQL Categories
Data Administration Commands
Commands that audit and analyze
database
E.g., START AUDIT, STOP AUDIT, etc.
Transaction Control Commands
Commands that maintain the transaction
actions in database
E.g., COMMIT, ROLLBACK, SAVEPOINT, etc.

SQL Environment, as Described by the SQL:2016 Standard

 CHARACTER(n) or CHAR(n) : Character
string has n characters long. (fixed length)

SQL Data VARYING CHARACTER(n) or VARCHAR(n):

varying length character string
Types
Character BIT(n) : Bit string has n bits long.

Strings
BIT VARING(n): varying length bit string

SQL Data Types Numeric Strings

INTEGER or INT
Integers or numbers without a decimal part. Range is from 2147483648 to
2147483647.
SMALLINT
Like INTEGER but does not occupy as much space. Range is -32768 to 32767.
This is a better choice than INTEGER if you are certain that numbers will be
in the indicated range.
DECIMAL(p, q) or DEC(p, q) or NUMERIC(p, q) ( p, q)
Decimal number has p digits long with q digits of these being decimal
places. For example, DECIMAL (5, 2) represents a number with three places
to the left of the decimal and two to the right.
FLOAT : Float number
 DATE: YYYY-MM-DD (the most
common format), DD-MON-YY, etc.
SQL Data TIME: HH:MM:SS (the most common
Types format)
TIMESTAMP :
Temporal YYYYMMDDHHMMSS, for the record
of time order of transactions
(Date/Time) INTERVAL : the duration between
different TIME

SQL Data Types - Boolean

BOOLEAN : True or False values -
TRUE, FALSE, or UNKNOWN
 DDL, DML, DCL, and the Database Development Process
DDL :
CREATE TABLE
ALTER TABLE
DROP TABLE

DML :
INSERT
UPDATE
DELETE
SELECT

DCL :
GRANT
ADD
REVOKE

Common commands of SQL

CREATE
INSERT INTO
DELETE (FROM)
UPDATE

ORDER BY (ASC/DESC)
BUILT-IN FUNCTIONS

UNION
Common build-in functions or operators
COUNT() NOT
SUM() AND
AVG() OR
MAX() DATEDIFF (get the time difference, dialect exists)
MIN() LIKE
ABS() (get absolute value) AS
ROUND() (get round value) LIMIT/TOP (limit the query, dialect exists: LIMIT
for MySQL, TOP for SQL server)
BETWEEN
(NOT) EXISTS
IF ()
ALL
LEAD (), LAG()
ANY
ROWNUM(), RANK(), DENSE_RANK()
(NOT) IN
DISTINCT

Free Online SQL Environment

When you are in a hurry without your own SQL environment, use it.
SQL
Free SQL environment for command testing
SQL Fiddle: https://sqlfiddle.com/
MySQL Online: https://paiza.io/en/projects/new?language=mysql
DB Fiddle: https://www.db-fiddle.com/
SQL Online: https://sqliteonline.com/
Oracle Live SQL: https://livesql.oracle.com/apex/f?p=590:1000

Note: these free platforms may have security/privacy problems. DO NOT upload your real data.
We recommend creating a real database on your localhost computer. Instructions for MySQL
installations have been provided during our TA sessions. MS SQL Server, PostgreSQL, SQLite, and
other NoSQL platforms such as MongoDB and Redis are also possible options.

Data Definition Language

Introduction to DDL
Data Definition Language (DDL)
Major CREATE statements:
CREATE SCHEMA defines a portion of the database owned by a
particular user
CREATE TABLE defines a new table and its columns
CREATE VIEW defines a logical table from one or more tables or
views
Other CREATE statements: CHARACTER SET, COLLATION,
TRANSLATION, ASSERTION, DOMAIN

Create/Drop Database /

CREATE DATABASE <name> or CREATE SCHEMA <name>

E.g., CREATE SCHEMA REGISTRATATION_DB

DROP DATABASE <name> or DROP SCHEMA <name>

E.g., DROP SCHEMA REGISTRATATION_DB
 Steps in Table Creation
1. Identify data types for attributes
2. Identify columns that can and cannot be null
3. Identify columns that must be unique (candidate keys)
4. Identify primary key foreign key mates
5. Determine default values
6. Identify constraints on columns (domain specifications)
7. Create the table and associated indexes

General Syntax for CREATE TABLE

[] (optional)
{} (multiple choices)
 The Following Slides Create Tables for This Enterprise Data Model
Customer_ID Customer_Name
(from Chapter 1, Figure 1-3)

CUSTOMER Product_ID PRODUCT

Standard_Price

Places

Has
Order_ID

Product_ID
Order_ID

ORDER Contains ORDER_LINE

Customer_ID

Order_Date Quantity

SQL Database Definition Commands for PVF Company (Oracle 12c)

Defining Attributes and Their Data Types

Non-Nullable Specifications
Some primary keys are composite composed of multiple attributes
 Controlling the Values in Attributes

Domain constraint

Identifying Foreign Keys and Establishing Relationships

Foreign key of dependent table

 Data Integrity Controls
Referential integrity constraint that ensures that foreign
key values of a table must match primary key values of a
related table in 1:N relationships
Restricting:
Deletes of primary records
Updates of primary records
Inserts of dependent records

Figure 5-7
Ensuring Data 1

Integrity
Through
Updates 2

Relational integrity is enforced

via the primary-key to foreign- 3
key match
4
4 ways to check integrity
automatically
 Changing Tables
ALTER TABLE statement allows you to change column specifications:
,

Table Actions:
,

Example (adding a new column with a default value):

,

Removing Tables
DROP TABLE statement allows you to remove tables from
your schema:
,
 Create Indexes
Speed up random/sequential access to base table data
Example
CREATE INDEX NAME_IDX ON CUSTOMER_T(CUSTOMERNAME)
This makes an index for the CUSTOMERNAME field of the
CUSTOMER_T table

Data Manipulation Language

Introduction to DML
 INSERT Statement
Adds one or more rows to a table
Inserting into a table:
,

Inserting a record that has some null attributes requires identifying

the fields that actually get data:
,

Inserting from another table:

,

Creating Tables with Identity Columns

Inserting into a table does not require explicit customer ID entry or field list.
 DELETE Statement
Removes rows from a table
Delete certain rows
,

Delete all rows

,

UPDATE Statement
Modifies data in existing rows
Example
UPDATE Product_T
SET ProductStandardPrice = 775
WHERE ProductID = 7;
 Example
Practices

Add data to a table: INSERT INTO

and the phone number is 2056.

INSERT INTO EMPLOYEE
VALUES
Update data: UPDATE

UPDATE PACKAGE
SET PACKNAME = 'MANTA II'
WHERE
Delete data: DELETE
Example: Delete from the database the employee whose
phone number is 8716.
DELETE FROM EMPLOYEE
WHERE EMPHONE = 8716;
 SELECT Statement (SQL Query)
Used for queries on single or multiple tables
Clauses of the SELECT statement:
SELECT: List the columns (and expressions) to be returned from the query
FROM: Indicate the table(s) or view(s) from which data will be obtained
WHERE: Indicate the conditions under which a row will be included in the
result
GROUP BY: Indicate categorization of results
HAVING: Indicate the conditions under which a category (group) will be
included
ORDER BY: Sorts the result according to specified criteria
 Example: List the complete PC table.
Query: SELECT SELECT *
FROM WHERE FROM PC;

Query: SELECT
FROM WHERE
Example: What is the name
of employee 124?
SELECT EMPNAME
FROM EMPLOYEE
WHERE EMPNUM = 124;
The computer responds with:
EMPNAME
Alvarez, Ramon
 Substring Comparisons, Arithmetifc Operations
SQL allows comparison conditions on only parts of a character string,
using the LIKE comparison operator
Partial strings are specified using '%' replaces an arbitrary number of
characters
Ex: Retrieve all employees whose address is in Houston, Texas
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston,TX%

55

SELECT Example Using Alias

Alias is an alternative column or table name
 Comparison operators in the where clause
Comparison operator Meaning
= Equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to

<> Not equal to (used by most implementations of SQL)

!= Not equal to (used by some implementations of SQL)

Example: Find the package ID and name for any package whose type
is Database.
SELECT PACKID, PACKNAME
FROM PACKAGE
WHERE
 Query with compound conditions
Compound conditions are formed by connecting two or more simple
conditions using AND, OR, and NOT. (Boolean operators)
Example: List the names of all packages whose type is Database and
whose cost is over $400.
By default, processing order of Boolean
SELECT PACKNAME operators is NOT, then AND, then OR
FROM PACKAGE
WHERE PACKTYPE = 'Database' AND PACKCOST > 400;
The computer will respond with:
PACKNAME
Manta

Query with compound conditions

Compound conditions are formed by connecting two or more simple conditions
using AND, OR, and NOT.
Example: List the names of all packages that are not of type Database.
SELECT PACKNAME
FROM PACKAGE
WHERE NOT
The computer will respond with:
PACKNAME
Boise Accounting
Limitless View
Words & More
Freeware Processing
 Query with Computed Columns
A computed column means that one which does not exist in the
database can be computed from columns having to do with.
Arithmetic operators for column computation:

Query with Computed Columns

Example : Suppose the packages in the database are all to be
discounted 10%. List the ID, name, and discounted cost for all
packages.
SELECT PACKID, PACKNEME, (.90 * PACKCOST)
FROM PACKAGE;
The computer will respond with:
AC01 Boise Accounting 653.25
DB32 Manta 342.00
DB33 Manta 387.16
SS11 Limitless View 196.16
WP08 Words & More 166.50
WP09 Freeware Processing 27.00
 Query with Sorting
Example: List the number, name, and phone of all employees. Order
the output by name.
SELECT EMPNUM, EMPNAME, EMPPHONE
FROM EMPLOYEE ASC By default
ORDER BY EMPNAME; Use DESC to sort descending.

The computer will respond with:

EMPNUM EMPNAME EMPPHONE
124 Alvarez, Ramon 1212
611 Dinh, Melissa 2963
567 Feinstein, Betty 8716

Query with Built-In Functions

Functions Meaning
COUNT Count of the number of rows satisfying the WHERE clause
SUM Sum of the values in a column for all rows satisfying the WHERE clause
(column must be numeric)
AVG Average of the values in a column for all rows satisfying the WHERE clause
(column must be numeric)
MAX Largest value in a column for all rows satisfying the WHERE clause. (If column
is numeric, will be largest number. If not, will be highest entry based on
collating sequence. If column contains names, for example, will be last name
alphabetically.)
MIN Smallest value in a column for all rows satisfying WHERE clause. (If column is
numeric, will be smallest number. If not, will be lowest entry based on
collating sequence. If column contains names, for example, will be first name
alphabetically.)
 Query with Built-In Functions
Example : How many packages are of type Database?
SELECT COUNT(*)
(or SELECT COUNT(PACKID) )
FROM PACKAGE
WHERE PACKTYPE = 'Database';
The computer responds with:
COUNT1
2

Query with Built-In Functions

Example : Find the number of packages and the total of their costs.
SELECT COUNT(PACKID), SUM(PACKCOST)
FROM PACKAGE;
The computer responds with: 1. With SUM, we must specify the column for
COUNT1 SUM2 which we want the sum calculated.
6 1968.96 2. The column must be numeric.
 Query with Grouping
Example : For each PC, list the tag number along with the total value
of the software installed on the PC.
For TAGNUM 32808 group :
SELECT SUM(SOFTCOST)
FROM SOFTWARE
WHERE TAGNUM = 32808;
For every TAGNUM group:
SELECT TAGNUM, SUM(SOFTCOST)
FROM SOFTWARE
GROUP BY TAGNUM
ORDER BY TAGNUM;

Query with Grouping

Example : For each PC, list the tag number along with the total value
of the software installed on the PC.
The computer responds with:
G_TAGNUM SUM1
32808 1319.95
37691 607.50
57772 583.01
59836 35.00
77740 35.00
 Query with Grouping
Example : List the tag number and the total value of software for each
PC where the value of the software is more than $600.
SELECT TAGNUM, SUM(SOFTCOST)
FROM SOFTWARE
GROUP BY TAGNUM
ORDER BY TAGNUM
HAVING SUM(SOFTCOST) > 600;

Query with Grouping

Example : List the tag number and the total value of software for each
PC where the value of the software is more than $600.
The computer responds with:
G_TAGNUM SUM1
32808 1319.95
37691 607.50
 Nesting Queries
Example : List the package ID and name for all packages whose cost is
greater than the average cost of the database packages.
Without nesting queries in two steps.
1. Get the condition first.
SELECT AVG (PACKCOST)
FROM PACKAGE
WHERE PACKTYPE = 'Database';
The computer responds with:
AVG1
405.09

Nesting Queries
Example : List the package ID and name for all packages whose cost is
greater than the average cost of the database packages.
Without nesting queries in two steps.
2. After viewing the answer (405.09), we could use the following
SELECT statement to do the query.
SELECT PACKID, PACKNAME
FROM PACKAGE
WHERE PACKCOST > 405.09;
The computer will respond with:
PACKID PACKNAME
AC01 Boise Accounting
DB33 Manta
 Nesting Queries
Example : List the package ID and name for all packages whose cost is
greater than the average cost of the database packages.
With nesting queries in one step.
SELECT PACKID, PACKNAME The portion in parentheses is called a
FROM PACKAGE subquery.
WHERE PACKCOST > This subquery is evaluated first.
(SELECT AVG (PACKCOST)
FROM PACKAGE
WHERE PACKTYPE = 'Database');
The computer will respond with:
PACKID PACKNAME
AC01 Boise Accounting
DB33 Manta

Query by Joining Tables

Example : For each PC, list the tag number and computer ID together
with the number and name of the employee to whom the PC has
been assigned.
Query by Joining Tables
Example : For each PC, list the tag number and computer ID together
with the number and name of the employee to whom the PC has
been assigned.
SELECT TAGNUM, COMPID, EMPLOYEE.EMPNUM, EMPNAME
FROM PC, EMPLOYEE
WHERE PC.EMPNUM = EMPLOYEE.EMPNUM;

Query by Joining Tables

Example : For each PC, list the tag number and computer ID together
with the number and name of the employee to whom the PC has
been assigned.
The computer responds with:
TAGNUM COMPID EMPNUM EMPNAME
32808 M759 611 Dinh, Melissa
37691 B121 124 Alvarez, Ramon
57772 C007 567 Feinstein, Betty
59836 B221 124 Alvarez, Ramon
77740 M759 567 Feinstein, Betty
Query by Joining Tables
Example : For each PC, list the tag number, computer ID, and the
name of the computer manufacturer along with the number and
name of the employee to whom the PC has been assigned.

Query by Joining Tables

Example : For each PC, list the tag number, computer ID, and the
name of the computer manufacturer along with the number and
name of the employee to whom the PC has been assigned.
SELECT TAGNUM, COMPUTER.COMPID, MFGNAME,
EMPLOYEE.EMPNUM, EMPNAME
FROM PC, EMPLOYEE, COMPUTER
WHERE PC.EMPNUM = EMPLOYEE.EMPNUM
AND PC.COMPID = COMPUTER.COMPID;
Query by Joining Tables
Example : For each PC, list the tag number, computer ID, and the
name of the computer manufacturer along with the number and
name of the employee to whom the PC has been assigned.
The computer responds with:
TAGNUM COMPID MFGNAME EMPNUM EMPNAME
32808 M759 Lemmin 611 Dinh, Melissa
37691 B121 Bantam 124 Alvarez, Ramon
57772 C007 Cody 567 Feinstein, Betty
59836 B221 Bantam 124 Alvarez, Ramon
77740 M759 Lemmin 567 Feinstein, Betty

Query by Joining Tables

Example : For each PC with a 486DX processor, list the tag number,
computer ID, and the name of the computer manufacturer along with
the number and name of the employee to whom the PC has been
assigned.
Query by Joining Tables
Example : For each PC with a 486DX processor, list the tag number,
computer ID, and the name of the computer manufacturer along with
the number and name of the employee to whom the PC has been
assigned.
SELECT TAGNUM, COMPUTER.COMPID, MFGNAME,
EMPLOYEE.EMPNUM, EMPNAME
FROM PC, EMPLOYEE, COMPUTER
WHERE PC.EMPNUM = EMPLOYEE.EMPNUM
AND PC.COMPID = COMPUTER.COMPID
AND PROCTYPE = '486DX';

Query by Joining Tables

Example : For each PC with a 486DX processor, list the tag number,
computer ID, and the name of the computer manufacturer along with
the number and name of the employee to whom the PC has been
assigned.
The computer responds with:
TAGNUM COMPID MFGNAME EMPNUM EMPNAME
37691 B121 Bantam 124 Alvarez, Ramon
57772 C007 Cody 567 Feinstein, Betty
Query by Union
The two tables must have the same structure.
The formal term is union-compatible.
Note that the definition does not state that the column headings of
the two tables must be identical, but rather that the columns must be
of the same type. Thus, if one is CHAR(20), the other must also be
CHAR(20).

Query by Union
Example : List the computer ID and manufacturer name of all
computers which either have a 486DX processor or have been
assigned for home use, or both.
Query by Union
Example : List the computer ID and manufacturer name of all
computers which either have a 486DX processor or have been
assigned for home use, or both.
SELECT COMPID, MFGNAME
FROM COMPUTER
WHERE PROCTYPE = '486DX'
UNION
SELECT COMPUTER.COMPID, MFGNAME
FROM COMPUTER, PC
WHERE COMPUTER.COMPID = PC.COMPID
AND LOCATION = 'HOME';

Query by Union
Example : List the computer ID and manufacturer name of all
computers which either have a 486DX processor or have been
assigned for home use, or both.
The computer responds with:
COMPID MFGNAME
B121 Bantam
B221 Bantam
C007 Cody
M759 Lemmin
Creating a New Table from an Existing Table
Example : Create a new table called DBPACK containing the package
ID, name, version, and cost for all rows in the PACKAGE table of type
Database.
The first thing to do is to describe this new table using the data
definition facilities of SQL, as follows:
CREATE TABLE DBPACK
(PACKID CHAR(4),
PACKNAME CHAR(20),
PACKVER DECIMAL(3, 2),
PACKCOST DECIMAL (5, 2));

Creating a New Table from an Existing Table

Example : Create a new table called DBPACK containing the package
ID, name, version, and cost for all rows in the PACKAGE table of type
Database.
The second thing to do is to insert into this new table the data
retrieved from an existing table, as follows:
INSERT INTO DBPACK
SELECT PACKID, PACKNAME, PACKVER, PACKCOST
FROM PACKAGE
WHERE PACKTYPE = 'Database';
SQL Statement Processing Order
(based on van der Lans, 2006, p. 100)

Join

Processing Multiple Tables

Equi-Join, Inner-Join, Outer Join, Union Join
 Join
A relational operation that causes two or more
tables with a common domain to be combined
into a single table or view
Equi-join: A join in which the joining condition is
based on equality between values in the common
columns; common columns appear redundantly in the
result table

Processing Natural join: An equi-join in which one of the

duplicate columns is eliminated in the result table

Multiple Tables
Outer join: A join in which rows that do not have
matching values in common columns are nonetheless
included in the result table (as opposed to inner join,
in which rows must have matching values in order to
appear in the result table)

Union
Includes all data from each table that was joined
Unions are not literally joins

Visualization of
Different Join
Types with
Results
Returned in
Shaded Area
 The Following Slides Create Tables for This Enterprise Data Model
Customer_ID Customer_Name
(from Chapter 1, Figure 1-3)

CUSTOMER Product_ID PRODUCT

Standard_Price

Places

Has
Order_ID

Product_ID
Order_ID

ORDER Contains ORDER_LINE

Customer_ID

Order_Date Quantity

PVF Company Customer_T and Order_T Tables

With pointers from orders to the customers who placed them
 Emp_ID Name Dept_ID Dept_ID Dept_N Mgmt_ID

Example
1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4
3 Vicky 1 4 FI 5
4 Jane 2
5 Samuel 3

SELECT Emp_ID, Name, EMP.Dept_ID, DEPT.Dept_ID, Dept_N, Mgmt_ID

FROM EMP, DEPT
WHERE EMP.Dept_ID=DEPT.Dept_ID;

Emp_ID Name Dept_ID Dept_ID Name Mgmt_ID

1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4 Equi-join
3 Vicky 1 1 MIS 3
4 Jane 2 2 BA 4

Emp_ID Name Dept_ID Dept_ID Dept_N Mgmt_ID

Example
1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4
3 Vicky 1 4 FI 5
4 Jane 2
5 Samuel 3

SELECT Emp_ID, Name, EMP.Dept_ID, DEPT.Dept_ID, Dept_N, Mgmt_ID

FROM EMP (LEFT) INNER JOIN DEPT
ON EMP.Dept_ID=DEPT.Dept_ID;

Emp_ID Name Dept_ID Dept_ID Name Mgmt_ID

1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4 Inner-join
3 Vicky 1 1 MIS 3
4 Jane 2 2 BA 4
Difference

Source: https://www.w3resource.com/sql/joins/perform-an-equi-join.php

Emp_ID Name Dept_ID Dept_ID Dept_N Mgmt_ID

Example
1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4
3 Vicky 1 4 FI 5
4 Jane 2
5 Samuel 3

SELECT Emp_ID, Name, EMP.Dept_ID, Dept_N, Mgmt_ID

FROM EMP, DEPT
WHERE EMP.Dept_ID=DEPT.Dept_ID;

Emp_ID Name Dept_ID Name Mgmt_ID

1 Jack 1 MIS 3
2 Rose 2 BA 4 Join
3 Vicky 1 MIS 3
(from last class)
4 Jane 2 BA 4
 Emp_ID Name Dept_ID Dept_ID Dept_N Mgmt_ID

Example
1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4
3 Vicky 1 4 FI 5
4 Jane 2
5 Samuel 3

SELECT Emp_ID, Name, EMP.Dept_ID, DEPT.Dept_ID, Dept_N, Mgmt_ID

FROM EMP LEFT OUTER JOIN DEPT
ON EMP.Dept_ID=DEPT.Dept_ID;

Emp_ID Name Dept_ID Dept_ID Name Mgmt_ID

1 Jack 1 1 MIS 3
2 Rose 2 2 BA 4 Left Outer
3 Vicky 1 1 MIS 3
4 Jane 2 2 BA 4
Join
5 Samuel 3 NULL NULL NULL

Equi-Join
Example
What are the customer
IDs and names of all
customers, along with
the order IDs for all the
orders they have placed?
 Equi-Join Example Alternative Syntax An INNER Join

INNER JOIN clause is an alternative to WHERE clause, and is used to match primary
and foreign keys.
An INNER join will only return rows from each table that have matching rows in the
other.
This query produces the same results as the previous equi-join example.

Outer-Join Example
List the customer name, ID number, and order number for all customers. Include
customer information even for customers that do not have an order.

LEFT OUTER JOIN clause causes rows from the first mentioned table (customer) to
appear even if there is no corresponding order data.
Unlike an INNER join, this will include customer rows with no matching order rows.

customers, and one of these customers has 2 orders.

Result
Note two rows for customer
#1 Contemporary Casuals.
Also note that several

This is because of the left

outer join.

Multiple Table Join Example

Assemble all information necessary to create an invoice for order number 1006.
Each pair of tables requires an equality-check condition in the WHERE clause,
matching primary keys against foreign keys.
 Results from a Four-Table Join (Edited for Readability)

All rows returned from this query will pertain to OrderID 1006.
Note that the full query results include columns from four different tables.

Self Join Example

What are the employee ID and name of each employee and the name of his or her

The same table is used on both sides of the join; distinguished using table aliases. See
the next slide for details.
 Self join involve tables that
Example of a Self Join implement 1-to-many unary
relationships.
 Subqueries

Partially lectured in previous slides. (i.e., we may test it in midterm exam.)

Let us use the example in our textbook to review.

Subqueries
Subquery placing an inner query (SELECT statement) inside an outer query
Options:
In a condition of the WHERE clause

Returning a field for the SELECT clause

Within the HAVING clause
Subqueries can be:
Noncorrelated executed once for the entire outer query
Correlated executed once for each row returned by the outer query
 Subquery Example
What are the name and address of the customer who placed order number 1008?

Alternative Approach, Using a Join

What are the name and address of the customer who placed order number 1008?
Join Query
Approach

Subquery Approach
 Alternative Approach, Using a Join
What are the name and address of the customer who placed order number 1008?

Join Query
Approach
 Subquery Approach

Noncorrelated subqueries:
Do not depend on data from the outer
query
Correlated Execute once for the entire outer query
versus.
Noncorrelated Correlated subqueries:
Subqueries Make use of data from the outer query
Execute once for each row of the outer
query
Can use the EXISTS, ALL, and ANY operators
 Example of a Correlated Subquery
List the details about the product with the highest standard price.

Another Correlated Subquery

What are the order IDs for all orders that have included furniture finished
in natural ash?
List the duplicate attribute value only once.

Check whether the attribute or condition has value or not.

A correlated subquery always refers to an attribute from a table

referenced in the outer query.
Subquery Processing (1 of 2) a) Processing a
noncorrelated subquery

Subquery Processing (2 of 2) b) Processing a

correlated subquery
 Derived Table (Subquery in the FROM Clause of the Outer Query)
What are the order IDs for all orders that have included furniture finished in natural ash?

Here, the subquery forms the derived table used in the FROM clause of the outer
query. The AvgPrice column from the subquery is used in the SELECT clause of the
outer query.

UNION

Fully lectured in previous slides. (i.e., we may test it in midterm exam.)

Let us use the example in our textbook to review.
 UNION Combining Queries
Combine the output (union of
multiple queries) together into a
single result table.
With UNION queries, the
quantity and data types of the
attributes in the SELECT clauses
of both queries must be identical.

CASE

Introduction
Conditional Expressions Using Case Keyword

A CASE expression acts like an if-

then statement. It allows you to
choose what will appear in a column
of the result set, depending on a
condition.

Views

View: A Logical (& Virtual) Table

Introduction
 More Complicated SQL Queries
Production databases contain hundreds or even
thousands of tables, and tables could include
hundreds of columns.
So, sometimes query requirements can be very
complex.

through the use of Views.

If you use a view (which is a query), you could
have another query that uses the view as if it
were a table.

Using a View in Your Query

For each salesperson, list
his or her biggest-selling
product.
The view:

The query using the view:

 Tips for Developing Queries
Be familiar with the data model (entities and relationships)
Understand the desired results
Know the attributes desired in results
Identify the entities that contain desired attributes
Review ERD
Construct a WHERE equality for each link
Fine tune with GROUP BY and HAVING clauses if needed
Consider the effect on unusual data

Query Efficiency Considerations

Instead of SELECT *, identify the specific
attributes in the SELECT clause; this helps
reduce network traffic of result set
Limit the number of subqueries; try to
make everything done in a single query if
possible
If data is to be used many times, make a
separate query and store it as a view
Guidelines for Better Query Design (1 of 2)
Understand how indexes are used in query processing
Keep optimizer statistics up to date
Use compatible data types for fields and literals
Write simple queries
Break complex queries into multiple simple parts

-joins)

Guidelines for Better Query Design (2 of 2)

Create temporary tables for groups of queries
Combine update operations
Retrieve only the data you need

Learn!
Consider the total query processing time for ad hoc queries
 Dynamic View
Using and request by a user

Defining Views No data actually stored; instead, data from

base table made available to user
Based on SQL SELECT statement on base tables
or other views

Materialized View
Copy or replication of data, data actually
stored
Must be refreshed periodically to match
corresponding base tables

View has a name

A Sample Create View is based on a SELECT statement
View Command CHECK_OPTION works only for updateable
views and prevents updates that would
create rows not included in the view
Advantages of Dynamic Views
Simplify query commands Establish physical data
Assist with data security independence
Enhance programming Use processing time each time
productivity view is referenced
Contain most current base table May or may not be directly
data updateable
Use little storage space As with all SQL constructs, you
should use views with discretion
Provide customized view for user

Routines & Triggers

Procedural SQL; PL/SQL SQL

Introduction
 Routines
Program modules that execute on demand

Functions
routines that return values and take input
parameters
Routines and Procedures
Triggers routines that do not return values and can
take input or output parameters
Triggers
routines that execute in response to a
database event (INSERT, UPDATE, or
DELETE)

Triggers Contrasted with Stored Procedures (Based on Mullins 1995)

Procedures and functions are called explicitly. Triggers are event-driven.
 Simplified Trigger Syntax, SQL:2008

Example DML trigger

Example DDL trigger

Syntax for Creating a Routine, SQL:2011

Example DDL trigger

Calling the procedure

 SQL Quick Review

SQL

Common commands of SQL

CREATE
INSERT INTO
DELETE (FROM)
UPDATE

ORDER BY
BUILT-IN FUNCTIONS
GROUP BY
UNION
JOIN
Common build-in functions or operators
COUNT() (NOT) IN
SUM() DISTINCT
AVG() NOT
MAX() AND
MIN() OR
ABS() (get absolute value) DATEDIFF (get the time difference, dialect exists)
ROUND() (get round value) LIKE
BETWEEN AS
EXISTS LIMIT/TOP/ROWNUM (limit the query, dialect
exists: LIMIT for MySQL, TOP for SQL server)
ALL
CASE
ANY
 SQL Statement Processing Order
(based on van der Lans, 2006, p. 100)

More SQL Practices?

Leetcode: https://leetcode.com/problemset/database/
Hackerrank: https://www.hackerrank.com/domains/sql
StrataScratch: https://www.stratascratch.com/
Advanced SQL Puzzles: https://advancedsqlpuzzles.com/
: https://www.nowcoder.com/exam/oj?tab=SQL%E7%AF%87&topicId=82
CodeSignal
DataCamp
SQL Assessments/Interviews (majorly from large companies)
Other Common Issues

Data Dictionary, SQL Enhancement, and more

Data Dictionary Facilities

System tables that store metadata
Users usually can view some of these tables
Users are restricted from updating them
Examples in Oracle 12c
DBA_TABLES descriptions of tables
DBA_USERS information about the users of the
system
Examples in Microsoft SQL Server 2016
sys.columns table and column definitions
sys.indexes table index information
SQL Enhancements/Extensions (1 of 2)
User-defined data types (UDT)
Subclasses of standard types or an object type
Analytical functions (for OLAP)
CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP, CUBE, SAMPLE,
WINDOW improved numerical analysis capabilities
New Data Types
BIG INT, MULTISET (collection), XML
CREATE TABLE LIKE
create a new table similar to an existing one
MERGE

SQL Enhancements/Extensions (2 of 2)
Programming extensions
Persistent Stored Modules (SQL/PSM)
Capability to create and drop code modules
New statements: CASE, IF, LOOP, FOR, WHILE, etc.
Makes SQL into a procedural language
Oracle has propriety version called PL/SQL, and Microsoft SQL Server
has Transact/SQL (T-SQL)
 CASE or IF?
SELECT FirstName, LastName, Salary, DOB, SELECT FirstName, LastName, Salary, DOB,
CASE Gender IF (Gender='M', 'Male', 'Female')
WHEN 'M' THEN 'Male' FROM Employees
WHEN 'F' THEN 'Female'
ELSE 'Others'
END
FROM Employees;

CTE: Common Table Expression

Available after SQL 2005
A common table expression (CTE) is a named temporary result set that exists
within the scope of a single statement and that can be referred to later
within that statement, possibly multiple times.
The CTE query definition here cannot
include the following items:
WITH ORDER BY
cte1 AS (SELECT a, b FROM table1), INTO
OPTION
cte2 AS (SELECT c, d FROM table2) FOR BROWSE
FOR XML
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
 Source: https://stackoverflow.com/questions/2404565/sql-difference-between-partition-by-and-group-by

GROUP BY vs. PARTITION BY

StackOverflow:
They're used in different places. GROUP BY modifies the entire query, like:
SELECT customerId, COUNT(*) AS orderCount
FROM Orders Here, we wish to know how many orders have been placed for each customer.
GROUP BY customerId;
But PARTITION BY just works on a window function, like ROW_NUMBER():
SELECT ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY orderId)
AS OrderNumberForThisCustomer
FROM Orders; Here, we wish to know the rank numbers for each customer placing orders.

GROUP BY normally reduces the number of rows returned by rolling them up and calculating
averages or sums for each row. GROUP BY query row
PARTITION BY does not affect the number of rows returned, but it changes how a window
function's result is calculated. PARTITION BY window function row

Source: https://stackoverflow.com/questions/2404565/sql-difference-between-partition-by-and-group-by

GROUP BY vs. PARTITION BY

Another answer from StackOverflow- GROUP BY:
SELECT SUM(Mark) AS marksum, firstname
FROM TableA
GROUP BY id, firstName;
Result:
 Source: https://stackoverflow.com/questions/2404565/sql-difference-between-partition-by-and-group-by

GROUP BY vs. PARTITION BY

Another answer from StackOverflow- PARTITION BY:
SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname
FROM TableA;
Result:

TA Discussion Sessions

Mavis Tsai Meng Mei Chen Andy Lee Sandy Lee Richard Lin
MS Student MS Student MS Student MS Student MS Student
[email protected] [email protected] [email protected] [email protected] [email protected]

Pei Han Hao Wen Jing Chen John Jiang Julian Lee Bo-Yi Li
MS Student MS Student MS Student MS Student PhD Candidate
[email protected] [email protected] [email protected] [email protected] [email protected]

Time: Group Discussion Location: Participation may be counted.

For Tuesday Class: ITI Lab at College of Assignments, practices, and tips
Thursdays 10 am - 12 pm Commerce Bldg. 260526 (Time to be provided in the TA classes may be
announced during semester) highly beneficial to the final project
For Thursday Class: (which counts for 45 points).
Thursdays 4 pm - 6 pm ALL our students are required to
attend the TA classes & group Bonus points may be offered.
TA Class Location: Yi Xian Bldg. discussions.
050502 (5th floor) Starting from Feb 22, 2024
 Your homework
Attend the TA discussion sessions. This week we have the
1st TA meeting on March 14. Please attend at College of
Commerce Building 5th floor 260526 on time when you
scheduled the meeting. Be sure to bring your need
analysis (HW1), potential functional decomposition
diagram (FDD), & initial ERD (if possible).
Start your brainstorming and make plans.
HW3: Logical Database Design Practices will be due on
next class (Mar 19 or 21).
See you next week!

And see you next week!