Database Systems

DATABASE QUERY WITH SQL

SQL Database Overview

 SQL (Structured Query Language) is the standard language for Relational Database
System.
• It is designed over relational algebra and tuple relational calculus
• All the Relational Database Management Systems (RDMS) like MySQL, MS Access,
Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database
language.
 SQL is Structured Query Language, which is a computer language for storing,
manipulating, and retrieving data stored in a relational database.
 Data Definition Language (DDL)
• CREATE: It creates a new table, a view of a table.
• ALTER: It modifies the existing table.
• DROP: It deletes the entire table or other objects in the database.
 Data Manipulation Language (DML)
• SELECT: It extracts certain records from one or more tables.
• INSERT: It creates a record (row) in the existing table.
• UPDATE: It modifies the existing record (row) of the table.
• DELETE: It deletes the records (rows) in the table.
 Brief History of SQL
• The SQL programming language was first developed in the 1970s by IBM researchers
Raymond Boyce and Donald Chamberlin. The programming language, known then as
SEQUEL, was created following the publishing of Edgar Frank Todd's paper, "A
Relational Model of Data for Large Shared Data Banks," in 1970.
Year Event
1972 System R project at IBM Research Labs
1974 SQUARE
1975 Language revision and name change to SEQUEL
1976 Language revision and name change to SEQUEL2
1977 Name change to SQL
1978 First commercial implementation by Oracle Corporation
1981 IBM product SQL/DS featuring SQL
1986 SQL-86 (SQL1) standard approved
1989 SQL-89 standard approved (revision to SQL-86)
1992 SQL-92 (SQL2) standard approved
1999 SQL-1999 (SQL3) standard approved
CREATE TABLE statements in standard SQL

 CREATE TABLE
CREATE TABLE <table_name>
(<column1> <datatype> [NOT NULL],
<column2> <datatype> [NOT NULL],
<column3> <datatype> [NOT NULL],.....
<columnN> <datatype> [NOT NULL],
[CONSTRAINT <constraintname>] PRIMARY KEY( <one or more columns>),
[CONSTRAINT <constraintname>] UNIQUE (<one column>),
[CONSTRAINT <constraintname>] FOREIGN KEY (<fk column>)
REFERENCES <tablename>(<column>), ….
);
CREATE TABLE Student
( StdID CHAR(8),
StdName VARCHAR(50),
StdBirthday DATE,
StdMajor CHAR(6),
StdClass CHAR(30),
StdGPA DECIMAL(3,2),
CONSTRAINT PKStudent PRIMARY KEY (StdID)
);
CREATE TABLE Course
( CourseNo CHAR(6),
CrsDesc VARCHAR(250),
CrsCredit SMALLINT,
PRIMARY KEY (CourseNo),
UNIQUE (CrsDesc)
);
CREATE TABLE Offering
( OfferNo CHAR(6),
CourseNo CHAR(6),
OffTerm SMALLINT NOT NULL,
OffYear INT NOT NULL,
OffLocation CHAR(6),
OffTime CHAR(8),
OffDays CHAR(10),
FacID CHAR(8)
PRIMARY KEY (OfferNo),
FOREIGN KEY (CouseNo) REFERENCES Course(CourseNo)
 Table Enrollment (OfferNo, StdID, EnrGrade)
 Table Faculty (FacID, FacName, FacDept, FacSalary, FacSupervisor)
 Create Table - By Copying all columns from another table
CREATE TABLE <new_table> AS (<SELECT statement>);
Modifying Relation Schema

 Delete a table:
DROP TABLE <tablename>;

 Add Column:
ALTER TABLE <tablename>
ADD <columnname> <datatype>;

 Drop Column:
ALTER TABLE <tablename>
DROP <columnname>;
SELECT Statement

 The SELECT statement is used to select data from a database.

SELECT <list of columns and expressions usually involving columns>

FROM <list of tables and join operations>
WHERE <condition>
GROUP BY <list of grouping columns>
HAVING <condition>
ORDER BY <list of sorting specifications>
Simple SQL statement with a single table

1. SELECT * FROM Student;

(* Show all columns)
2. SELECT StdID, StdName FROM Student;

3. SELECT StdID FROM Enrollment;

4. SELECT DISTINCT StdID FROM Enrollment;

• Using DISTINCT keyword to move duplicates in the result.
5. SELECT StdID, StdName FROM Student WHERE StdGPA >=3.0;
Simple SQL statement with a single table

6. SELECT * FROM Course WHERE CourseNo LIKE ‘MI%’;

7. SELECT * FROM Course WHERE CourseNo LIKE ‘MI200_’;
( %, _ is the wildcard character. In Acess: *, ?)
8. SELECT StdID, StdName, StdClass FROM Student
WHERE StdBirthday BETWEEN ‘1/1/2000’ AND ‘31/12/2000’;
(In Access: # 1/1/2000 # AND #31/12/2000#)

9. SELECT StdID, StdName, StdClass FROM Student

WHERE StdBirthday IS NULL;
10. SELECT StdID, StdName, StdClass FROM Student
WHERE StdBirthday IS NOT NULL;
Simple SQL statement with a single table

11. List the offer number, course number for course offerings scheduled in term 2 in
year 2020 or term 1 in year 2021.
SELECT OfferNo, CourseNo
FROM Offering
WHERE (OffTerm = 2 AND OffYear = 2020) OR (OffTerm = 1 AND OffYear = 2021);

12. List the offer number, course number of term 1 year 2021 offerings without
information about location.
SELECT OfferNo, CourseNo
FROM Offering
WHERE OffLocation IS NULL AND (OffTerm = 1 AND OffYear = 2021);
Joining Tables

13. List the offering number, course number, days, and time of offerings containing
the words ‘Database’ in the course description and taught in term 1 year 2021.
SELECT OfferNo, Offering.CourseNo, OffDays, OffTime
FROM Offering, Course
WHERE Course.CourseNo = Offering.CourseNo
AND OffTerm = 1 AND OffYear = 2021
AND CrsDesc LIKE ‘%Database%’;
(In Access: CrsDesc LIKE ‘*Database*’)

SELECT OfferNo, Offering.CourseNo, OffDays, OffTime

FROM Offering INNER JOIN Course ON Course.CourseNo = Offering.CourseNo
WHERE OffTerm = 1 AND OffYear = 2021 AND CrsDesc LIKE ‘%Database%’;
Joining Tables

14. List the student name, offering number and grade of students who have a grade
>= 3.5 in a course offering.

15. List the names of students who have grades >= 3.5 in a course offering.

16. List the names of students (without duplicates) who have grades >= 3.5 in a
course offering.
Joining Tables

 List the student ID, names and the offering number in which the grade is greater
than 3.0 and the offering is given in term 2 year 2020.
SELECT Student. StdID, StdName, Enrollment.OfferNo
FROM Student, Enrollment, Offering
WHERE Student.StdID = Enrollment.StdID
AND Offering.OfferNo = Enrollment.OfferNo
AND OffYear = 2020 AND OffTerm = 2
AND EnrGrade >= 3.0;
Joining Tables

Combine the Cross Product and Join Operator Styles

SELECT Student. StdID, StdName, Enrollment.OfferNo

FROM Student INNER JOIN Enrollment ON Student.StdID = Enrollment.StdID, Offering
WHERE Offering.OfferNo = Enrollment.OfferNo
AND OffYear = 2020 AND OffTerm = 2
AND EnrGrade >= 3.0;
Joining Tables

 Retrieve the name, class and grade of students who have greater than or equal 3.5
in a course offered in term 2 year 2020.


SELECT StdName, StdClass, EnrGrade

FROM (Student INNER JOIN Enrollment
ON Student.StdID = Enrollment.StdID)
INNER JOIN Offering ON Offering.OfferNo = Enrollment.OfferNo
WHERE EnrGrade >= 3.5 AND OffTerm = 2 AND OffYear = 2020;
Joining Tables
Self Join
 List faculty member who have a higher salary than their supervisor. List the Social
Security number, name and salary of the faculty and supervisor.

SELECT Subr.FacID, Subr.FacName, Subr.FacSalary, Supr.FacID,

Supr.FacName, Supr.FacSalary
FROM Faculty AS Subr, Faculty AS Supr
WHERE Subr.FacSupervisor = Supr.FacID
AND Subr.FacSalary > Supr.FacSalary;

 (Subr, Supr are Alias Table Names)

.
Joining Tables

 SQL Aliases
• SQL aliases are used to give a table, or a column in a table, a temporary name.
• Aliases are often used to make column names more readable.
• An alias only exists for the duration of that query.
• An alias is created with the AS keyword

 Alias Column Syntax

SELECT column_name [AS] alias_name
FROM table_name;

 Alias Table Syntax

SELECT column_name(s)
FROM table_name [AS] alias_name;
Joining Tables

SELECT S.StdID, StdName, Enrollment.OfferNo

FROM Student AS S, Enrollment AS E, Offering AS O
WHERE S.StdID = E.StdID
AND O.OfferNo = E.OfferNo
AND OffYear = 2020 AND OffTerm = 2
AND EnrGrade >= 3.0;
Joining Tables

 Retrieve the name, class and grade of students who have a greater than or equal
to 3.5 in a course offered in term 2 year 2020 taught by ‘Nguyen Nam’.
 List the offer number, course number, and name of the instructor of MI course
offering scheduled in term 1 year 2021.

 (CourseNo LIKE ‘MI*’)

 OUTER JOIN
Students
StdID StdName StdBirthday StdMajor StdClass StdGPA
20191000 Hoa 12/4/2001 EM Kinh tế 2.50
20191001 Cúc 1/1/2000 MI Toán Tin 3.00

Enrollment
OfferNo StdID EnrGrade
11111 20191000 5
11112 20191000 6

 SELECT Students.StdID, Students.StdName, Enrollment.OfferNo,

Enrollment.EnrGrade StdID StdName OfferNo EnrGrade
20191000 Hoa 11111 5
FROM Students INNER JOIN Enrollment ON Students.StdID = Enrollment.StdID;
20191000 Hoa 11112 6

 SELECT Students.StdID, Students.StdName, Enrollment.OfferNo,

Enrollment.EnrGrade StdID StdName OfferNo EnrGrade
20191000 Hoa 11111 5
FROM Students LEFT OUTER JOIN Enrollment ON Students.StdID = 20191000 Hoa 11112 6

Enrollment.StdID; 20191001 Cúc

OUTER JOIN

 INNER join is used to retrieve rows from two or more tables by matching a field
value that is common between the tables. The fields you join on must have similar
data type (you cannot join on MEMO or OLEOBJECT data types).

 An OUTER JOIN is used to retrieve rows from multiple tables while preserving rows
from one of the tables, even if there is no matching row in the other table. There
are two types of OUTER JOINs: LEFT OUTER JOIN and RIGHT OUTER JOIN.
SQL Aggregate Functions

Aggregate function Description

The AVG() aggregate function calculates the average of non-NULL values

AVG
in a set.

The COUNT() aggregate function returns the number of rows in a group,

COUNT
including rows with NULL values.

The MAX() aggregate function returns the highest value (maximum) in a

MAX
set of non-NULL values.

The MIN() aggregate function returns the lowest value (minimum) in a set
MIN
of non-NULL values.

The SUM() aggregate function returns the summation of all non-NULL

SUM
values a set.
SQL Aggregate Functions

SELECT count(*)
FROM Student;

SELECT count(*)
FROM Student
WHERE StdGPA >= 3.0;

SELECT count(StdID)
FROM Student
WHERE StdGPA >= 3.0;
SQL Aggregate Functions

SELECT MAX(StdGPA)
FROM Students;

SELECT COUNT(*), AVG(StdGPA)

FROM Students
WHERE StdMajor = ‘EM’;

SELECT COUNT(DISTINCT StdID) SELECT COUNT(StdID)

FROM Enrollment FROM Enrollment
WHERE EnrGrade < 4; WHERE EnrGrade < 4;
SQL Aggregate Functions

SELECT count(StdID)
FROM (Course INNER JOIN Offering ON Course.CrsNO = Offering.CrsNo) INNER JOIN
Enrollment ON Offering.OfferNo = Enrollment.OfferNo
WHERE CrsDesc LIKE ‘MI*’ AND OffTerm = 1 AND OffYear = 2021;

SELECT count(StdID)
FROM Course, Offering, Enrollment
WHERE Course.CrsNO = Offering.CrsNo AND Offering.OfferNo = Enrollment.OfferNo
AND CrsDesc LIKE ‘MI*’ AND OffTerm = 1 AND OffYear = 2021;
SQL Aggregate Functions

SELECT sum(CrsCredit * EnrGrade)

FROM Course, Offering, Enrollment
WHERE Course.CrsNO = Offering.CrsNo AND Offering.OfferNo = Enrollment.OfferNo
AND StdID = "20191000" AND OffTerm = 1 AND OffYear = 2021;
 Calculate the GPA of ‘20191000’ student.
SELECT sum(CrsCredit * EnrGrade)/sum(CrsCredit)
FROM Course, Offering, Enrollment
WHERE Course.CrsNO = Offering.CrsNo AND Offering.OfferNo = Enrollment.OfferNo
AND StdID = ‘20191000’ AND OffTerm = 1 AND OffYear = 2021;
Summarizing Tables with GROUP BY and HAVING

 Summarize the average GPA of students by major

SELECT StdMajor, AVG(StdGPA) AS AvgGPA
FROM Student
GROUP BY StdMajor;

COUNT function usage:

• COUNT(*) and COUNT(Column) produce identical results except when “column”
contains NULL values.
• COUNT, AVG, SUM functions support the DISTINCT keyword to restrict the
computation to unique column values
• (Some DBMS do not support the DISTINCT keyword inside of aggregate functions)
 Summarize the number of offerings and unique courses by year.
SELECT OffYear, COUNT(*) AS NumOfferings, COUNT (DISTINCT CourseNo) AS
NumCourses FROM Offering GROUP BY OffYear;

 Summarize the average GPA of students in class ‘PTKD 1’ or ‘PTKD 2’ by major

 SELECT StdMajor, AVG(StdGPA) AS AvgGPA
FROM Student
WHERE StdClass = ‘PTKD 1’ OR StdClass = ‘PTKD 2’
GROUP By StdMajor;
 Summarize the average GPA of students in class ‘PTKD 1’ or ‘PTKD 2’ by major.
Only list the major with average GPA greater than 3.0
SELECT StdMajor, AVG(StdGPA) AS AvgGPA
FROM Student
WHERE StdClass IN (‘PTKD 1’, ‘PTKD 2’)
GROUP By StdMajor
HAVING AVG(StdGPA) > 3.0;
 Summarize the minimum and maximum GPA of students by major and class
SELECT StdMajor, StdClass, MIN(StdGPA), MAX(StdGPA)
FROM Student
GROUP BY StdMajor, StdClass;

 Summarize the number of MI1111 course offerings by year.

 Summarize the number of MI1111 course offerings by term of each year. Only list
the term has less than 5 of MI1111 course offerings.
 Summarize the number of MI course offerings by course description
 Use the ORDER BY clause to sort the result table on one or more columns.
 Summarize the number of MI course offerings by course description
SELECT CrsDesc, COUNT(*) AS OfferCount
FROM Course, Offering
WHERE Course.CrsNo = Offering.CrsNo AND Course.CrsNo LIKE ‘MI%’
GROUP BY CrsDesc;
Sorting using ORDER BY clause

 List the GPA, , ID, name of “EM” major students. Order the result by GPA in
ascending order
SELECT StdGPA, StdID, StdName
FROM Student
WHERE StdMajor = ‘EM’
ORDER BY StdGPA;
 Order by GPA in descending, student name in ascending order
SELECT StdGPA, StdID, StdName
FROM Student
WHERE StdMajor = ‘EM’
ORDER BY StdGPA DESC, StdName;

 List the salary, name, and department of faculty. Order the result by ascending
name and descending salary.
 Combining Joins and Grouping:
• List the course number, the offering number and the number of students enrolled.
Only include course offered in term 2 year 2020.
 SELECT CourseNo, Enrollment.OfferNo, COUNT (*) AS NumStudent
FROM Offering, Enrollment
WHERE Offering.OfferNo = Enrollment.OfferNo AND OffTerm = 2 AND OffYear = 2020
GROUP BY Enrollment.OfferNo , CourseNo;
 List the course number, offer number, and average grade of students enrolled in
the term 2 year 2020 MI course offerings in which more than ten students is
enrolled. Sort the result by course number in ascending order and average grade in
descending order.
 SELECT CourseNo, Offering.OfferNo, AVG(EnrGrade) AS AvgGrade
FROM Enrollment, Offering
WHERE Enrollment.OfferNo = Offering.OfferNo AND CourseNo LIKE ‘MI%’ AND OffTerm =
2 AND OffYear = 2020
GROUP BY CourseNo, Offering.OfferNo
HAVING COUNT(*) > 1
ORDER BY CourseNO, AVG(EnrGrade) DESC;

• In the ORDER BY clause, note the number 3 as the second column to sort:
ORDER BY CourseNO, 3 DESC;
Traditional Set Operators in SQL

 UNION, INTERSECT, MINUS (or EXCEPT)

 Show all MI or EM major students.
 (SELECT * FROM Student WHERE StdMajor = ‘MI’)
UNION
(SELECT * FROM Student WHERE StdMajor = ‘EM’);
 (SELECT CourseNo FROM Offering WHERE OffTerm = 1 AND OffYear = 2020)
UNION
(SELECT CourseNo FROM Offering WHERE OffTerm = 2 AND OffYear = 2020);
 (SELECT CourseNo FROM Offering WHERE OffTerm = 1 AND OffYear = 2020)
UNION ALL
(SELECT CourseNo FROM Offering WHERE OffTerm = 2 AND OffYear = 2020);
 (SELECT CourseNo FROM Offering WHERE OffTerm = 1 AND OffYear = 2020)
INTERSECT
(SELECT CourseNo FROM Offering WHERE OffTerm = 2 AND OffYear = 2020);
 (SELECT CourseNo FROM Offering WHERE OffTerm = 1 AND OffYear = 2020)
MINUS
(SELECT CourseNo FROM Offering WHERE OffTerm = 2 AND OffYear = 2020);
Correlated Subquery

 A subquery is best defined as a query within a query. Subqueries enable you to

write queries that select data rows for criteria that are actually developed while
the query is executing at run time. More formally, it is the use of a SELECT
statement inside one of the clauses of another SELECT statement. In fact, a
subquery can be contained inside another subquery, which is inside another
subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and
DELETE statements. Subqueries must be enclosed within parentheses.
 A subquery can be used any place where an expression is allowed providing it
returns a single value. This means that a subquery that returns a single value can
also be listed as an object in a FROM clause listing. This is termed an inline view
because when a subquery is used as part of a FROM clause, it is treated like a
virtual table or view. Subquery can be placed either in FROM clause, WHERE clause
or HAVING clause of the main query.
SELECT OfferNo, CourseNo, OffDays, OffTime
FROM Offering
WHERE OffTerm = 1 AND OffYear = 2021
AND CourseNo IN (SELECT CourseNo
FROM Course WHERE CrsDesc LIKE ‘%Database%’);

SELECT OfferNo, CourseNo, OffDays, OffTime

FROM Offering
WHERE OffTerm = 1 AND OffYear = 2021
AND CourseNo = ANY (SELECT CourseNo
FROM Course WHERE CrsDesc LIKE ‘%Database%’);
 IN (Subquery)
 NOT IN (Subquery)
 EXISTS (Subquery)
 NOT EXISTS (Subquery)
  ALL (Subquery)
  ANY (Subquery)
  (Single Row Subquery)

  is a relational operator: =, ! = , >, >=, <, <=

 Types of Subqueries
• Single Row Sub Query: Sub query which returns single row output. They mark the
usage of single row comparison operators, when used in WHERE conditions.
• Multiple row sub query: Sub query returning multiple row output. They make use of
multiple row comparison operators like IN, ANY, ALL. There can be sub queries
returning multiple columns also.
• Correlated Sub Query: Correlated subqueries depend on data provided by the outer
query. This type of subquery also includes subqueries that use the EXISTS operator to
test the existence of data rows satisfying specified criteria.
SELECT FacID, FacName, FacSalary, FacDept
FROM Faculty
WHERE FacSalary = (SELECT MIN (FacSalary) FROM Faculty);

SELECT FacDept, MIN (FacSalary)

FROM Faculty
GROUP BY FacDept
HAVING MIN (FacSalary) < (SELECT AVG (FacSalary) FROM Faculty) ;
Correlated Sub Query

SELECT FacID, FacName, FacSalary, FacDept

FROM Faculty F
WHERE FacSalary > (SELECT AVG(FacSalary)
FROM Faculty T
WHERE F. FacDept = T. FacDept);
Database Modifications

 INSERT: insert rows into a table.

 DELETE: delete certain rows from a table
 UPDATE: update values of certain components of certain existing rows.
 INSERT INTO <table name> [(list of column names)] VALUES (v1, v2, …., vn);

 INSERT INTO Course VALUES (‘MI3030’, ‘Toan Kinh te’, 3)

 INSERT INTO Course(CourseNo, CrsDesc, CrsCredit) VALUES (‘MI3030’, ‘Toan Kinh te’, 3)
 INSERT INTO Course(CrsDesc, CourseNo, CrsCredit) VALUES (‘Toan Kinh te’, ‘MI3030’, 3)
 Insert data into a table from another table:
 INSERT INTO <table name> [(list of column names)] SELECT …… ;

 CREATE TABLE EMStudent

(StdID CHAR(8),
StdName VARCHAR(50),
StdClass CHAR(30));

 INSERT INTO EMStudent

SELECT StdID, StdName, StdClass FROM Student WHERE StdMajor = ‘EM’;
 DELETE FROM <table name> [WHERE <condition>];

 DELETE FROM EMClass WHERE SdtID = ‘20201000’;

 DELETE FROM EMClass;

 UPDATE <table name>
SET <new-value assignments> WHERE <condition>;

UPDATE Course SET CrsCredit = 2 WHERE CourseNo = ‘MI2000’;

Indexes

 An index on a column A of a table is a data structure that makes it efficient to find

those rows that have a fixed value for column A. (When tables are very large, it
becomes expensive to scan all the rows of a table to find those rows that match a
given condition.
 CREATE INDEX <index name> ON <table name> (<list of column names>);
 DROP INDEX <index name>;

 CREATE INDEX StdIdx ON Student(SdtName);

VIEW Definitions

 CREATE VIEW <view name> AS <view definition >;

 <view definition> is a SELECT statement.

 Table that are defined with a CREATE TABLE statement actually exist in the
database.
 “Tables” are defined with a CREATE VIEW (called VIEWs) do not exist physically.
 Views can be queried as if they existed physically, and in some cases, we can even
modify views.
REFERENCES

1. Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, DATABASE SYSTEMS:

The Complete Book
2. Ramez Elmasri, Shamkant B. N avathe, FUNDAMENTALS OF
FourthEdition DATABASE SYSTEMS
3. Michael V. Manning, Database Design, Application Development and
Administration.
4. https://www.tutorialspoint.com
5. https://beginnersbook.com
6. https://www.datacamp.com
7. https://www.w3schools.com
8. https://www.sqlservertutorial.net