Structured Query Language - SQL

September 18, 2023 DB: SQL 1

 Outline

 Example Tables
 SELECT Definition
 Selecting Columns
 Selecting Rows
 Sorting
 Aggregation
 Grouping
 Restricting Groups
 Aliasing Table Names
 Nested Queries
 Join
 Set Operations
September 18, 2023 DB: SQL 2
September 18, 2023 ICS324 3
 FIGURE 7.2
Result of mapping the COMPANY ER schema into a relational schema.

September 18, 2023 ICS324 4

 SELECT Definition

 SQL has only one statement for retrieving information from a database called the
SELECT statement, and it consists of six main clauses or components.

 The six clauses of SQL are

 FROM Specifies table(s) to be used.

 WHERE Filters rows.

 GROUP BY Forms groups of rows with same column value.

 HAVING Filters groups subject to some condition.

 SELECT Specifies which columns are to appear in output.

 ORDER BY Specifies the order of the output.

September 18, 2023 DB: SQL 5

 Selecting Columns

 Selecting all columns

Select *
from employee

 Selecting Specific columns

Select ssn, salary from employee

 Selecting Computed columns

Select ssn, salary*1.1
from employee

 Renaming Columns
Select ssn, salary*1.1 as new_salary
from employee
September 18, 2023 DB: SQL 6
 Selecting Rows

 Selecting All Rows

Select * from employee;
 Partial match Search
Select * from employee where salary > 25000;
 Range Search
Select * from employee
where salary between 25000 and 35000;

 Set Membership Search

Select * from employee where dno in (1, 5);

Select * from employee where dno not in (1, 5);

September 18, 2023 DB: SQL 7

 Pattern Matching and Null Search

 Pattern matching Search

Select * from employee where fname like "A%";

Select * from employee where fname like "%n";

Select * from employee where fname like "%n%";

Select * from employee where fname like "Ahm_d";

 Null Search
Select * from employee where super_ssn is NULL;

Select * from employee where super_ssn is not NULL;

September 18, 2023 DB: SQL 8

 Duplicate Removal and Sorting

 Removing Duplicate Rows

Select distinct *
from employee;

Select distinct salary, dno

from employee

 Sorting Rows
Select * from employee
order by fname;

Select * from employee

order by fname desc;

September 18, 2023 DB: SQL 9

 Aggregation …

 ISO standard defines five aggregate functions:

 COUNT returns number of values in a specified column.

 SUM returns sum of values in a specified column.

 AVG returns average of values in a specified column.

 MIN returns smallest value in a specified column.

 MAX returns largest value in a specified column.

September 18, 2023 DB: SQL 10

 Aggregate Functions

Select count(*) from employee;

Select sum(salary) from employee;

Select avg(salary) from employee;

Select min(salary) from employee;

Select max(salary) from employee;

Select max(salary), min(salary), avg(salary)

from employee;

September 18, 2023 DB: SQL 11

 … Aggregation …

 Each operates on a single column of a table and return single value.

 COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM
and AVG may be used on numeric fields only.

 Apart from COUNT(*), each function eliminates nulls first and operates
only on remaining non-null values.

 COUNT(*) counts all rows of a table, regardless of whether nulls or

duplicate values occur.

 Can use DISTINCT before column name to eliminate duplicates.

September 18, 2023 DB: SQL 12

 Group By Clause

Select sex, count(*)

from employee
Group by sex;

Select sex, sum(salary)

from employee
Group by sex;

Select sex, dno, avg(salary)

from employee
Group by sex, dno;

Select sex, dno, min(salary)

from employee
Group by sex, dno;

September 18, 2023 DB: SQL 13

 Having Clause

Select dno, count(*)

from employee
Group by dno
Having count(*) > 2;

Select dno, sum(salary)

from employee
Group by dno
Having sum(salary) > 100000;

September 18, 2023 DB: SQL 14

 Join

 Inner join

Select fname, dependent_name

From employee join dependent
on ssn = essn

 Left outer join

Select fname, dependent_name

From employee left join dependent
on ssn = essn

September 18, 2023 DB: SQL 15

 Join

 Right outer join

Select fname, dependent_name

From employee right join dependent
on ssn = essn

 Full join

Select fname, dependent_name

From employee full join dependent
on ssn = essn

September 18, 2023 DB: SQL 16

 Aliasing Table Names

Select d.dependent_name, d.sex, e.dno

from employee e join dependent d
where d.essn = e.snn;

September 18, 2023 DB: SQL 17

 Join

September 18, 2023 DB: SQL 18

 Nested queries

 select every employees whose salary is above average.

Inner select
SELECT *
FROM employee
WHERE salary > (
select avg(salary)
from employee
);
Outer select

 The Inner select is done before the outer select.

September 18, 2023 DB: SQL 19

 Nested query

 List the names of all employees who are in the Research department

SELECT fname, lname

FROM employee
WHERE dno = (
SELECT dnumber
FROM department
WHERE dname = "Research"
);

September 18, 2023 DB: SQL 20

 Input Tables

Nested Query

 Find employee whose salary higher than the salary of at least one employee in
Research department.

SELECT *
FROM employee
WHERE salary > (
SELECT min(salary)
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
WHERE dname = "Research"

)
);

September 18, 2023 DB: SQL 21

 Input Tables

Nested Query: Example

 Find employee whose salary higher than the salary of every employee in Research
department.

SELECT *
FROM employee
WHERE salary > (
SELECT max(salary)
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
WHERE dname = "Research"

)
);

September 18, 2023 DB: SQL 22

 Union, Intersect, and Difference

 Can use normal set operations of union, intersection, and difference to combine
results of two or more queries into a single result table.

 Union of two tables, A and B, is table containing all rows in either A or B or both.

 Intersection is table containing all rows common to both A and B.

 Difference is table containing all rows in A but not in B.

 Two tables must be union compatible.

 If ALL specified, result can include duplicate rows

September 18, 2023 DB: SQL 23

 UNION

 List every Research and Administration employee salaries. Remove duplicates

SELECT salary
FROM employee
WHERE dno =
( SELECT dnumber
FROM department
WHERE dname = "Research"
)
UNION
SELECT salary
FROM employee
WHERE dno =
( SELECT dnumber
FROM department
WHERE dname = "Administration"
)

September 18, 2023 DB: SQL 24

 UNION ALL

 List aevery Research and Administration employee salaries. keep duplicates

SELECT salary
FROM employee
WHERE dno =
( SELECT dnumber
FROM department
WHERE dname = "Research"
)
UNION ALL
SELECT salary
FROM employee
WHERE dno =
( SELECT dnumber
FROM department
WHERE dname = "Administration"
)

September 18, 2023 DB: SQL 25

 Input Tables

DIFFERENCE

 List salaries that are taken by Research and not Administration employees.

SELECT salary
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
where dname = "Research"
)
MINUS
SELECT salary
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
WHERE dname= "Administration“
)

September 18, 2023 DB: SQL 26

 Input Tables

INTESECTION

 List salaries that are taken by Research and not Administration employees.

SELECT salary
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
where dname = "Research"
)
INTERSECT
SELECT salary
FROM employee
WHERE dno = (
SELECT dnumber
FROM department
WHERE dname= "Administration"
)

September 18, 2023 DB: SQL 27

 References

 https://www.w3schools.com/
 https://www.db-fiddle.com/
 http://sqlfiddle.com/ sql/

September 18, 2023 DB: SQL 28

September 18, 2023 DB: SQL 29