Introduction To SQL1 (CSE Department) 12.09.22

Chapter 3: Introduction to SQL1
Abu Raihan Mostofa Kamal
Professor, CSE Department

Islamic University of Technology (IUT)
August 28, 2022
1
This is based on Textbook, its companion slide and other sources
Overview of The SQL Query Language Basic Structure of SQL Queries The Rename Operation String Operations Set Operations Null values Aggregate Functions Nested Sub-query an
Chapter Outline
Overview of The SQL Query Language

Basic Structure of SQL Queries
The Rename Operation
String Operations
Set Operations
Null values
Aggregate Functions
Nested Sub-query and Case statement
DML Statements : Insert, Update, Delete
2 / 44
History of SQL
• IBM Sequel language developed as part of System R project at the IBM San Jose
Research Laboratory
• Renamed Structured Query Language (SQL)
• A number of standards defined (for industry compatibility)
✓ SQL-86,SQL-89, SQL-92
✓ SQL:1999 (language name became Y2K compliant!)
✓ SQL:2003
• Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from
later standards and special proprietary features.
• Hence, not all examples here may work on your particular system.
3 / 44
SQL: Broad Classification
SQL can be categorized into 2 major classes:

1. Data-definition language (DDL): The SQL DDL provides commands for defining relation
schemas, deleting relations, and modifying relation schemas.
Example: Create table, drop table, modify table
2. Data-manipulation language (DML): The SQL DML provides the ability to query
information from the database and to insert tuples into, delete tuples from, and modify
tuples in the database.
Example: update, insert, delete
4 / 44
Data Definition Language
The SQL data-definition language (DDL) allows the specification of information about relations,
including:
• The schema for each relation.
• The type of values associated with each attribute.
• The Integrity constraints (such as primary constraint and others)
• The set of indices to be maintained for each relation.
• Security and authorization information for each relation.
• The physical storage structure of each relation on disk.
5 / 44
Domain Types in SQL
• char(n). Fixed length character string, with user-specified length n.

• varchar(n). Variable length character strings, with user-specified maximum length n.
• int. Integer (a finite subset of the integers that is machine-dependent).
• smallint. Small integer (a machine-dependent subset of the integer domain type).
• numeric(p,d). Fixed point number, with user-specified precision of p digits, with d digits
to the right of decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not
444.5 or 0.32)
• real, double precision. Floating point and double-precision floating point numbers, with
machine-dependent precision.
• float(n). Floating point number, with user-specified precision of at least n digits.
6 / 44
Domain Types in SQL: Oracle Implementation
In Oracle Database (basic) data-types are classified as follows:

• varchar2(n): n bytes/(characters!!) are allocated
• number(p,s): where p is the precision: total number of digits while s is the digits after
decimal point. Any number can by generated varying these 2 parameters.
Example: only fraction: number(3,3)
integer value: number(3,0)
• Date: it is not string anymore, all date related built-ins can be applied here. (Chapter 4 for
more details)
7 / 44
Create Table Construct
• r is the name of the relation, Ai is the

attribute
create table r
(A1 D1, A2 D2, ..., A n Dn, • Di is the data type of values in the domain
(integrity-constraint1), of attribute Ai
...,
• Types of (basic) integrity constraints:
(integrity-constraintk))
1. primary key (A1, ..., An )
2. foreign key (Am , ..., An ) references r
3. not null
8 / 44
Create Table Construct: Example
create table instructor (

ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreigny (dept_name) references department)
9 / 44
Create Table Construct: Better Example
create table instructors

(ID varchar2(5),
name varchar2(20) not null,
dept_name varchar(20),
salary number(8,2),
constraint pk_instructor primary key (ID),

constraint fk_instructor_dept foreign key (dept_name)
references department)
The benefit of naming constraint is mostly felt when errors occurs.
10 / 44
Alter / Drop Table Construct
• drop table r : table r is dropped both its structure and data are lost
• alter table r add A D
✓ where A is the name of the attribute to be added to relation r and D is the domain of A.
✓ All exiting tuples in the relation are assigned null as the value for the new attribute.
11 / 44
Alter / Drop Table Example
alter table instructor

drop table instructor;
add address varchar2(40);
Listing: drop table
Listing: alter table
12 / 44
Basic Structure of SQL Queries: Single Relation
The basic structure of an SQL query consists of three clauses: select, from, and where clause.
• Queries on a Single Relation.
select id,name, salary
from instructors
where dept_name='CSE' or dept_name='EEE';
• where clause dept_name=’CSE’ or dept_name=’EEE’ ... predicate part is

the select operator σp (r) (sigma)
• select part is the project ΠA1 , A2 , AN (r)
13 / 44
The select Clause
• Select all can be expressed by * sign as follows

select *
from stutents;
• Keyword distinct is used for eliminating duplicate records ( not duplicate column)
select distinct dept,name
from stutents;
14 / 44
Basic Structure of SQL Queries: Multiple Relations
• Queries on a Cartesian Product. Remember it is all possible combinations between 2

relations.
create t a b l e dept
( dept_name v a r c h a r 2 ( 1 0 ) ,
location varchar2 (10) ,
y e a r _ e s t number ( 4 , 0 ) ,
y e a r l y _ b u d g e t number ( 1 0 , 2 ) ,
c o n s t r a i n t pk_dept primary key ( dept )
); select *
from dept , s t u d e n t s ;
create table students
( name v a r c h a r 2 ( 2 0 ) ,
prog varchar2 ( 2 0 ) , Listing: Cartesian Product, no where clause
dob d a t e ,
c g p a number ( 5 , 4 ) ,
dept varchar2 ( 1 0 ) ,
c o n s t r a i n t f k _ s t u d e n t s f o r e i g n key ( dept )
references dept ( dept )
);
15 / 44
The Rename Operation
Renaming in SQL is very useful in a number of cases:

1. Rename attribute name
2. Rename expression
3. Rename relation name
4. Rename entire query
5. Rename to compare tuples in the same relation (Self-reference)
16 / 44
Rename: Examples
select name as instructorname,

select ID,name,
courseid
(salary/12) MSalary
from instructor, teaches
from emp;
where instructor.ID= teaches.ID;
Listing: Rename expression
Listing: Rename Attribute
1
as keyword is optional in oracle database
17 / 44
Rename: Examples (Cont.1)
select name as instructorname,

courseid
• Renaming Relation is called Correlation
from instructor I, teaches T
where I.ID= T.ID; Name in the SQL standard, but it is also
commonly referred to as a Table Alias
Listing: Rename Relation
18 / 44
select A.N as Name,A.CID as Course

from • Such renaming is very useful
(select name as N, CourseID as CID particularly when we need to sort
from instructor I, teaches T records not based on the stored
where I.ID= T.ID) A
;
values but on the computed values
(will be covered in soon in details).
Listing: Rename a Query
19 / 44
select E1.ID,
E1.Name, E1.BID
from Emp E1, Emp E2 • Such self-reference works like a for
where E1.BID=E2.ID;
loop in C.
Listing: Rename for self-reference
20 / 44
String Operations
• SQL includes a string-matching operator for comparisons on character strings. The

operator like uses patterns that are described using two special characters:
1. percent ( % ). The % character matches any substring.
2. underscore ( _ ). The _ character matches any character.
• An Exact Match is done using = operator. Single quote is used for string matching and
backslash \ as the escape character
Example:
select *
from depts
where name='CSE';
Listing: String Operation
21 / 44
String Operations (Cont.)
• Patterns are case sensitive.

• Pattern matching examples:
✓ ’Intro%’ matches any string beginning with "Intro".
✓ ’%Comp%’ matches any string containing “Comp” as a substring.
✓ ’_ _ _’ matches any string of exactly three characters.
✓ ’%m%’ matches any string containing letter m anywhere.
• SQL supports a variety of string operations such as:
✓ concatenation (using || sign)
✓ Many built-ins are normally used (i.e. UPPER, LOWER, SUBSTR..)
22 / 44
Set Operations: When to use
• In practice, set operations are used to extract data from two relations having no
references (it may happen if data is collected from a legacy system and merged into new
system)
• It is also useful to merge data from different sources where conditions vary in each case.
• In many cases, (specially the SQL shown here) same SQL can be written alternatively
(using AND , OR, NOT operators) in much simpler way.
23 / 44
Set Operations: Examples (Cont.)
• Find courses that ran in Fall 2017 or in Spring 2018

( s e l e c t c o u r s e _ i d f r o m s e c t i o n w h e r e sem = ' F a l l ' and y e a r = 2 0 1 7 )
union
( s e l e c t c o u r s e _ i d f r o m s e c t i o n w h e r e sem = ' S p r i n g ' and y e a r = 2 0 1 8 )
• Find courses that ran in Fall 2017 and in Spring 2018

( s e l e c t c o u r s e _ i d f r o m s e c t i o n w h e r e sem = ' F a l l ' and y e a r = 2 0 1 7 )
intersect
( s e l e c t c o u r s e _ i d f r o m s e c t i o n w h e r e sem = ' S p r i n g ' and y e a r = 2 0 1 8 )
24 / 44
Null values
• Null Values present special problems in relational operations, including arithmetic

operations, comparison operations, and set operations.
• For example:
select Name, (salary+bonus) Total
from emp;
For employees who have null values for their bonus will return Null as total salary!!
• Solution: use nvl() built-in.
Example:
select Name, (salary+ nvl(bonus,0)) Total
from emp;
Now the null values found will be treated as 0, so total salary is now meaningful.
25 / 44
Aggregate Functions
• Aggregate functions are functions that take a collection of values as input and return a
single value. SQL offers five (5) standard built-in aggregate functions (Note: more are
available in many commercial databases):
✓ Average: avg
✓ Minimum: min
✓ Maximum: max
✓ Total: sum
✓ Count: count
Note: The input to sum and avg must be a collection of numbers, but the other operators
can operate on collections of nonnumeric data types, such as strings, as well
26 / 44
Aggregate Functions: Examples

• Find the average salary of instructors in the Computer Science department
select avg (salary)
from instructor
where dept_name= 'Comp. Sci.';
• Find the total number of instructors who teach a course in the Spring 2018 semester
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;
• Find the number of tuples/records in the course relation

select count (*)
from course;
27 / 44
Aggregation with Grouping
• Need to apply the aggregate function not only to a single set of tuples, but also to a group
of sets of tuples; we specify this in SQL using the group by clause.
• Tuples with the same value on all attributes in the group by clause are placed in one
group.
• Applying avg() of salary in instructor relation will return one single value. But we are more
interested for finding average salary for each department.
28 / 44
Aggregation with Grouping
• Find the average salary in each department

select deptname, avg (salary) as avg salary
from instructor
group by deptname;
29 / 44
Aggregation: Basic Principle
All selected attributes must be present either in (i) aggregate functions or in (ii) group by
clause.
• Following Query has some errors!!!

s e l e c t i d , deptname , a v g ( s a l a r y ) as a v g s a l a r y
from i n s t r u c t o r
g r o u p by d e p t n a m e ;
• It is needed specially when we need joining over an aggregated function. For example:
Find the department name, its budget and total number of students. Trick: use either
max or min functions for those extra attributes.
test the Query
s e l e c t dname , max ( b u d g e t ) , c o u n t ( * ) t o t a l S t u d e n t
from depts , s t u d e n t s
w h e r e d e p t s . dname = s t u d e n t s . d e p t
g r o u p by dname ;
30 / 44
Aggregation: Having Clause
• It is useful to state a condition that applies to groups (which will be computed at run-time)
• where clause can only be applied on the values already stored, but here the values are not
stored rather will be computed on-the-fly. Hence we have to use Having clause
• Find out the department name, its budget and total number of students for those who
have at-least 200 students;
select dname,max(budget), count (*) totalStudent
from depts,students
where depts.dname=students.dept
group by dept
having count(*)>=200;
• Note: predicates in the having clause are applied after the formation of groups whereas
predicates in the where clause are applied before forming groups
31 / 44
Sub-query
• SQL provides a mechanism for nesting subqueries. A subquery is a select-from-where

expression that is nested within another query.
• It can be placed at 2 places:
1. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.
2. A subquery in the FROM clause of a SELECT statement is also called an inline view.
32 / 44
Subquery in WHERE clause: Example
• Find the employees name,address and salary for those who are now getting more than
the average salary of the company.
select id,name,salary
from emp
where salary>(select avg(salary) from emp);
• Here, subquery in the where part is first executed and the value is locked. This is
particularly important for update statement. Suppose, consider the Query:
Increase salary of employees by 20% for those who are now getting lower than the
average salary of the company.
update emp
set salary=salary*1.2
where salary<(select avg(salary) from emp);
33 / 44
Subquery in WHERE clause: Set-membership
• IN and NOT IN are normally used in the where clause as a mechanism of

set-membership. It works in iterative for (i.e. loop as long as data exists). In other words,
it works as a subset membership.
• Example: Find courses offered in Fall 2017 and in Spring 2018
select distinct course_id
from section
where semester = 'Fall' and year= 2017 and
course_id in (select course_id
from section
where semester = 'Spring' and year= 2018);
34 / 44
Subquery in FROM clause: Example
• A subquery can also be found in the FROM clause. These are called inline views.
• Example: Display the top five earner names and salaries from the EMPLOYEES table:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;
35 / 44
DML Statements
There are 3 DML statements:

1. INSERT : new record entry
2. UPDATE : existing record modification
3. DELETE : existing record remove
36 / 44
General INSERT Statement: Example
• Add a new tuple to course:

insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
%Equivalently:
insert into course (course_id, title, dept_name, credits)

values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
• Add a new tuple to student with tot_creds set to null

insert into student
values ('3003', 'Green', 'Finance', null);
37 / 44
INSERT Statement: INSERT INTO SELECT...FROM
• More dynamically, we might want to insert tuples on the basis of the result of a query.
• We can follow INSERT INTO T1 (SELECT ...FROM T2);
• Example: Make each student in the Music department who has earned more than 144
credit hours an instructor in the Music department with a salary of $18, 000 (fixed values
can be passed as well).
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and total_cred > 144;
38 / 44
UPDATE statement: Example
• Give a 5% salary raise to those instructors who earn less than 70000
update instructor
set salary = salary * 1.05
where salary < 70000;
• Give a 5% salary raise to instructors whose salary is less than average (already shown!!)
update instructor
where salary < (select avg (salary)
from instructor);
39 / 44
Compound UPDATE statement

• Sometimes, we need to specify a number of different conditions for updating. Simply we
can achieve it by writing different UPDATE statements for each condition.
• Example: Increase salaries of instructors whose salary is over $100, 000 by 3%, and all
others by a 5%
Two update statements:
update instructor
where salary > 100000;
update instructor
where salary <= 100000;
• The order is important. Together they will form a transaction.

• Can be done better using the case statement (newly introduced)
40 / 44
Compound UPDATE statement (Cont.)
• Same Example: (using case) Increase salaries of instructors whose salary is over
$100, 000 by 3%, and all others by a 5%
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end;
41 / 44
DELETE statement: Example
• Delete all instructors:

delete [from] instructor;
• Delete all tuples in the instructor relation for those instructors associated with a
department located in the Watson building.
delete from instructor
where dept name in (select dept name
from department
where building = 'Watson');
42 / 44
DELETE statement: Example (Cont.)
• Delete all instructors whose salary is less than the average salary of instructors
delete from instructor
where salary < (select avg (salary)
from instructor);
• Problem: as we delete tuples from instructor, the average salary changes

• Solution: In RDBMS, locking is used to resolve the issue. First the subquery is computed
and locked. Then delete statement is executed one by one without recomputing subquery
(ie. avg)
43 / 44
End of Chapter 3
Thank You
44 / 44

Introduction To SQL1 (CSE Department) 12.09.22

Uploaded by

Copyright:

Available Formats

Introduction To SQL1 (CSE Department) 12.09.22

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Introduction To SQL1 (CSE Department) 12.09.22

Uploaded by

Copyright:

Available Formats

Chapter 3: Introduction to SQL1

Abu Raihan Mostofa Kamal

Professor, CSE Department

August 28, 2022

Overview of The SQL Query Language

SQL: Broad Classiﬁcation

SQL can be categorized into 2 major classes:

Data Deﬁnition Language

Domain Types in SQL

• char(n). Fixed length character string, with user-speciﬁed length n.

Domain Types in SQL: Oracle Implementation

In Oracle Database (basic) data-types are classiﬁed as follows:

Create Table Construct

• r is the name of the relation, Ai is the

Create Table Construct: Example

create table instructor (

Create Table Construct: Better Example

create table instructors

constraint pk_instructor primary key (ID),

The beneﬁt of naming constraint is mostly felt when errors occurs.

Alter / Drop Table Construct

Alter / Drop Table Example

alter table instructor

Basic Structure of SQL Queries: Single Relation

• where clause dept_name=’CSE’ or dept_name=’EEE’ ... predicate part is

The select Clause

• Select all can be expressed by * sign as follows

Basic Structure of SQL Queries: Multiple Relations

• Queries on a Cartesian Product. Remember it is all possible combinations between 2

The Rename Operation

Renaming in SQL is very useful in a number of cases:

select name as instructorname,

Rename: Examples (Cont.1)

select name as instructorname,

Rename: Examples (Cont.2)

select A.N as Name,A.CID as Course

Rename: Examples (Cont.3)

• SQL includes a string-matching operator for comparisons on character strings. The

Listing: String Operation

String Operations (Cont.)

• Patterns are case sensitive.

Set Operations: When to use

Set Operations: Examples (Cont.)

• Find courses that ran in Fall 2017 or in Spring 2018

• Find courses that ran in Fall 2017 and in Spring 2018

• Null Values present special problems in relational operations, including arithmetic

Aggregate Functions: Examples

• Find the number of tuples/records in the course relation

Aggregation with Grouping

Aggregation with Grouping

• Find the average salary in each department

Aggregation: Basic Principle

• Following Query has some errors!!!

Aggregation: Having Clause

• SQL provides a mechanism for nesting subqueries. A subquery is a select-from-where

Subquery in WHERE clause: Example

Subquery in WHERE clause: Set-membership

• IN and NOT IN are normally used in the where clause as a mechanism of

Subquery in FROM clause: Example