CSU/ITU07314

DATABASE MANAGEMENY
SYSTEMS
[DBMS]
2 Logistics

Instructor: Siphy, A. S.
email: [email protected]/
[email protected]

Office: Block D, 020

Consultation Time
BY
Appointment

Intro to SQL, 04/11/2022

3 OVERVIEW
 SQL DML
 INSERT(
 DELETE
 UPDATE
 SQL SELECT(BASIC)
 DISTINCT CLAUSE
 WHERE CLAUSE
 ORDER BY CLAUSE
 DATE FUNCTION, & AGGREGATE FIELD
Intro to SQL,
4 DML [INSERT, UPDATE,
DELETE]  UPDATE and DELETE use ‘WHERE
clauses’ to specify which rows to
 INSERT - add a row to a change or remove
table  BE CAREFUL with these - an
incorrect WHERE clause can destroy
lots of data
 UPDATE - change row(s) in
a table

 DELETE - remove row(s)

from a table

Intro to SQL,
Populate relational tables
5 To create a tuple in SQL the following ‘Insert’ command is required:

INSERT INTO COURSE (Course_id, Title)

VALUES (‘BCS’, ‘Bachelor in Computer The insert order matters in
Science’) ; terms of referential
INSERT INTO COURSE integrity constraints! 1st
VALUES (‘BIT’, ‘BSc in Information
technology’) ;
Insert Into Student (sid, sname, birthdate, course )
values (‘IMC/BCS/207839’, ‘King President’, ‘17-Nov-81’, ‘BS’);
2nd

Insert into student

values (‘IMC/BCS/207698’, ‘Blake Coi’, ’01-May-91’, ‘BCS’);

Intro to SQL, 11/16/2015

6 INSERT -Example
INSERT INTO Student
Student
(ID, Name, Year)
VALUES (2, ‘Mary’, 3) ID Name Year
You can partially add a row 1 John 1
without violating the integrity 2 Mary 3
constraints! Student
Student
INSERT INTO Student ID Name Year
ID Name Year
(ID, Name) 1 John 1
1 John 1 VALUES (‘Mary’, 2) 2 Mary
Student
INSERT INTO Student ID Name Year
VALUES (2, ‘Mary’, 1 John 1
You can add a row3)
without listing the columns 2 Mary 3
with full insert!
Intro to SQL,
SQL - Update statement
7 a set of tuples in SQL the following ‘Update’ command is used:
To alter
UPDATE R
SET attribute1 = datavalue1,
attribute2 = datavalue2,
……...
attributen = datavaluen
[where condition-]expression]
Example: increase the salary of all employees that work in
department 10 by 15%.

UPDATE Emp
SET sal = sal *0.15 If the where clause is omitted then all
WHERE deptno = 10; tuples in the relation will be altered!

Intro to SQL,
8 UPDATE -Examples
Student
ID Name Year
UPDATE Student 1 John 1
SET Year = 1, 2 Mark 3
Student Name = ‘Jane’ 3 Anne 2
4 Jane 1
ID Name Year WHERE ID = 4
1 John 1
2 Mark 3
3 Anne 2 Student
4 Mary 2 ID Name Year
UPDATE Student 1 John 2
SET Year = Year + 1 2 Mark 4
3 Anne 3
4 Mary 3

Intro to SQL,
SQL - Delete statement
9
To delete a set of tuples in SQL the following ‘Delete’ command is used:

DELETE FROM R
[where condition-expression]

Example: remove details of all employees that work in

department 10 from the Emp relation.

DELETE FROM Emp If the where clause is omitted then all

WHERE deptno = 10; tuples in the relation will be removed!

Intro to SQL,
10 DELETE -Example
Student

DELETE FROM ID Name Year

Student 1 John 1
Student WHERE Year = 2 2 Mark 3
Student
ID Name Year
ID Name Year
1 John 1
1 John 1
2 Mark 3
2 Mark 3
3 Anne 2
3 Anne 2
4 Mary 2
4 Mary 2
Student
DELETE FROM Student ID Name Year
or
TRUNCATE TABLE Student

Intro to SQL,
SQL - Group Insert statement-copy
11
To create a set of tuples in SQL the following ‘Insert’
command can be used:
INSERT INTO R (attribute1, attribute2, … attributen )
SELECT (attribute1, attribute2, … attributen)
FROM relation1, relation2, … relationn
[WHERE condition-expression]
[GROUP BY attribute1, attribute2, … attributen ]
[HAVING condition-expression]
[ORDER BY attribute1, attribute2, … attributen ]

Example: copy details of all employees that work in department

10 from the Emp relation into the DepA relation.
corresponding
INSERT INTO DepA (staffno, name, job,hiredate) attributes have to
SELECT empno, name, job, hiredate be of the same
FROM Emp type
WHERE deptno = 10;
Intro to SQL,
Each tuple to be inserted has to be unique!
Saving Table Changes
 Changes made to table contents are not physically
saved on disk until
 Database is closed
 Program is closed
 COMMIT command is used
 Syntax
 COMMIT [WORK]
 Will permanently save any changes made to any table
in the database
Restoring Table Contents
 ROLLBACK
 Used restore the database to its previous condition
 Only applicable if COMMIT command has not been used to
permanently store the changes in the database
 Syntax
 ROLLBACK;
 COMMIT and ROLLBACK only work with data
manipulation commands that are used to add, modify,
or delete table rows
14 Advanced Data Definition
Commands
 All changes in table structure are made by
using ALTER command
 Followed by keyword that produces specific
change
 Following three options are available:
 ADD
 MODIFY
 DROP
15 Changing a Column’s Data Type

 ALTER can be used to change data type

 Some RDBMSs (such as Oracle) do not permit
changes to data types unless column to be changed is
empty Create table student(
 Example, SID INT PRIMARY KEY,
…………);
 ALTER TABLE student
MODIFY SID CHAR(16); //Data type INT(previous
defined) can be changed to string (char(16) if there is
no data exists in the table STUDENT//
16 Changing a Column’s Data
Characteristics
 Use ALTER to change data characteristics
 If column to be changed already contains data,
changes in column’s characteristics are permitted if
those changes do not alter the data type
 Example;

SQL> ALTER TABLE Student

MODIFY Sname VARCHAR(30);
//Let’s assume previous permitted characters were 20.
17 Adding a Column

 Use ALTER to add column

 Do not include the NOT NULL
clause for new column
18 Dropping a Column

 Use ALTER to drop column

 Some RDBMSs impose restrictions
on the deletion of an attribute
Retrieval Queries in SQL (SELECT
Statement)

SELECT <List of Columns and expressions (usually

involving columns)>
FROM <List of Tables & Join Operators>
WHERE <List of Row conditions joined together by
And, Or, Not>
GROUP BY <list of grouping columns>
HAVING <list of group conditions connected by And,
Or, Not >
ORDER BY <list of sorting specifications>
Conceptual Evaluation -SELECT
From Tables:
Cross product and 1
join operations

Restriction on
where conditions 2
Compute
Sort on aggregates Restriction
Group
By?
Group BY and reduce on HAVING
Yes columns each group conditions
to 1 row
No 3 5
4
Order By?
Sort
Yes
No columns in 6
ORDER BY
Project columns 7
in SELECT

Finish
Retrieval Queries in SQL..
Basic form of the SQL SELECT statement is called a mapping or a
SELECT-FROM-WHERE block ( 3 clauses)

SELECT <attribute list>

FROM <table list>
WHERE <condition>

– <attribute list> is a list of attribute names whose values are to

be retrieved by the query
– <table list> is a list of the relation names required to process
the query
– <condition> is a conditional (Boolean) expression that
identifies the tuples to be retrieved by the query
Retrieval Queries in SQL…
22

Using a “*” in a select statement indicates that every

attribute of the input table is to be selected.
Example: SELECT * FROM … WHERE …;

To get unique rows, type the keyword DISTINCT after

SELECT.
Example: SELECT DISTINCT * FROM …
WHERE …;

Intro to SQL,
23 Retrieval Queries in SQL…
 Selecting Specific Columns
 Specify the column names to be displayed in the
result set by typing the exact, complete column
names.
 Separate each column name with a comma (,).
 Specify the name of the table after the FROM
clause.
 Terminate the query with a semi-colon (;).

Eg, SELECT emp_ssn, emp_name FROM

Employee;
Intro to SQL,
SQL – DISTINCT
 Eliminates all the duplicate entries in the table resulting from
the query.
Syntax:
SELECT [DISTINCT] coln_list
FROM table[, table, …]
[WHERE expression]
[ORDER BY expression]
Example:
SELECT DISTINCT studio_id, director_id
FROM Movies
SQL – Order By
 Used to sort the results based on contents of a column
 Multiple levels of sort can be done by specifying
multiple columns
 An expression can be used in Order By clause
Syntax:
Select function(column)
From table1 [, table2 …]
[Where <condition>]
[Order By {Column | alias | position} [ASC | DESC]]
Selecting Rows with
Conditional Restrictions

26
27 Queries-Special Operators
 Often a subquery will  Options
return a set of values
rather than a single value  IN - checks to see if a
 You can’t directly value is in the set
compare a single value to  EXISTS - checks to see
a set
if the set is empty or not
 ALL/ANY - checks to
see if a relationship
holds for every/one
member of the set
08-Nov-
2016
SQL, More Select statements
28 (NOT) IN
 Using IN we can see if a SELECT
given value is in a set of <columns>
values FROM <tables>
 NOT IN checks to see if a WHERE <value>
given value is not in the set IN <set>
 The set can be given SELECT <columns>
explicitly or from a subquery FROM <tables>
WHERE <value>
NOT IN <set>

08-Nov-
2016
SQL, More Select statements
29 (NOT) IN
SELECT *
FROM Employee
WHERE Department
Employee IN(‘Marketing’,‘Sales’)
Name Department Manager
John Marketing Chris
Mary Marketing Chris Name Department Manager
Chris Marketing Jane John Marketing Chris
Peter Sales Jane Mary Marketing Chris
Jane Management Chris Marketing Jane
Peter Sales Jane

08-Nov-
2016
SQL, More Select statements
30 (NOT) IN

Employee SELECT *
Name Department Manager FROM Employee
John Marketing Chris WHERE Name NOT IN
Mary Marketing Chris (SELECT Manager
Chris Marketing Jane FROM
Peter Sales Jane Employee);
Jane Management

08-Nov-
2016
SQL, More Select statements
31 (NOT) IN
 This gives
 First the subquery
SELECT *
SELECT Manager
FROM Employee
FROM Employee
Manager WHERE Name NOT IN

Chrisis evaluated giving (‘Chris’,‘Jane’)
Chris
Jane Name Department Manager
Jane
John Marketing Chris
Mary Marketing Chris
Peter Sales Jane

08-Nov-
2016
SQL, More Select statements
32 (NOT) EXISTS

 Using EXISTS we see SELECT <columns>

if there is at least one FROM <tables>
WHERE EXISTS <set>
element in a set
 NOT EXISTS is true if
the set is empty SELECT <columns>
 The set is always given FROM <tables>
by a subquery WHERE NOT EXISTS <set>

08-Nov-
2016
SQL, More Select statements
33 (NOT) EXISTS
SELECT *
FROM Employee E1
Employee WHERE EXISTS (
Name Department Manager SELECT * FROM
John Marketing Chris Employee E2
Mary Marketing Chris WHERE E2.Name =
Chris Marketing Jane E1.Manager)
Peter Sales Jane
Jane Management Name Department Manager
Chris Marketing Jane
Jane Management

08-Nov-
2016
SQL, More Select statements
34 ANY and ALL
 ANY and ALL compare  val = ANY (set) is
a single value to a set of true if there is at least
values one member of the set
 They are used with equal to the value
comparison operators like  val = ALL (set) is
=, >, <, <>, >=, <= true if all members of
the set are equal to the
value

08-Nov-
2016
SQL, More Select statements
35 ANY
Find the names of the employee(s) who earn
the highest salary

Employee
Name Salary SELECT Name
Mary 20,000 FROM Employee
John 15,000 WHERE Salary >=
Jane 25,000 ALL (
Paul 30,000
SELECT Salary
FROM Employee)

08-Nov-
2016
SQL, More Select statements
36 ANY
Find the names of employee(s)
who earn more than someone else

Employee
Name Salary
SELECT Name
Mary 20,000
John 15,000 FROM Employee
Jane 25,000 WHERE Salary >
Paul 30,000
ANY (
Haule 25,000
SELECT Salary
FROM Employee)

08-Nov-
2016
SQL, More Select statements
37 Special operators…
 BETWEEN
 Used to check whether attribute value is
within a range
 IS NULL
 Used to check whether attribute value is null
 Usual mathematical operators
 +-*/^

Intro to SQL,
38 SQL CONCATENATE

 Sometimes it is necessary to combine

together (concatenate) the results from
several different fields.
 Each database provides a way to do……
 MSQL : CONCAT()
 Oracle: CONCAT(), ||
 SQL Sever: +

08-Nov-
SQL, More Select statements
2016
Aliases
39

 Two forms:
 Aliases rename
 Column alias
columns or tables to
SELECT column
 Make names more
AS newName...
meaningful
 Table alias
 Make names shorter
SELECT ...
and easier to type
FROM table
 Resolve ambiguous
names AS newName

This ‘AS’ is optional, but Oracle

doesn’t accept it at all 08-Nov-
2016
SQL, More Select statements
Queries- Example:
1) SELECT *
Person FROM person
Name Age Weight WHERE age > 30;
Harry 34 80 Name Age Weight
Sally 28 64 Harry 34 80
George 29 70 Helena 54 54
Helena 54 54 Peter 34 80
Peter 34 80

3) SELECT DISTINCT weight

2) SELECT weight
FROM person FROM person
WHERE age > 30; WHERE age > 30;
Weight Weight
80 80
54 54
80 Intro to SQL, 40
41 ORDER BY With ASC and DESC
 To sort columns from high to low, or descending, an
optional keyword DESC must be specified.

 ASC - Ascending, low to high.

 DESC - Descending, high to low.

 When ASC or DESC is used, it sorts results from

specified column name.

Intro to SQL,
42 Date functions
 Commonly used date functions are:
 Last_day
 Gives the last day of the month. This is useful for
knowing when payment might be expected.
 MONTHS_BETWEEN
 Least
 Which of the string of dates is the earliest.
 Greatest
 Which of the string of dates is the latest.
Intro to SQL, 01/11/16
43
Meaning…
TO_CHAR(date,format)
 Converts the date to the specified format.

NEXT_DAY(date,dayofweek)
 Gives the date of the next ‘dayofweek’ after the date given.

LAST_DAY(date)
 Gives the last day of the month in the date specified.

ADD_MONTHS (date,int)
 Adds int months to the given date.

Intro to SQL, 01/11/16

44 Date functions…
• SYSDATE/ CURRENT_DATE gives current date, eg
SELECT SYSDATE FROM Dual;
• NEXT_DAY(d,day) where d is a date and day is a
string representing a day of the week.
– E.g. next_day(’14-Nov-2014’,’Monday’) will return ’19-Nov-2014’

• ADD_MONTHS(d,count) adds n months to d.

• MONTHS_BETWEEN(d1,d2)
• LEAST(d1,d2,…,dn)
• GREATEST(d1,…,dn)
Intro to SQL, 01/11/16
Aggregate Fields in SQL
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)

SQL, More Select statements

single column 08-Nov-
2016
45
Aggregate Functions

 These functions operate on the multi-set of values

of a column of a relation, and return a value
AVG(A): Average value
MIN(A): Minimum value
MAX(A): Maximum value
SUM(A): Sum of values
COUNT(A): Number of values
47 To aggregate over > 1 row

 COUNT() AND COUNT(*)

 COUNT(A) is used to count the number of values in a
column
 COUNT(*) is used to count number of rows of the
query results ( it counts the number of rows in the
resulting table)
 SUM(A)
 SUM(A) is used to find the sum of the values in a
column

08-Nov-
SQL, More Select statements
2016
48 Grouping clause
 The GROUP BY clause allows you to form groups
based on the specified condition.
 The syntax for using this command is

SELECT < column>, function <column>,

FROM table/S
GROUP BY <column>;

Note: GROUP BY is not the same as ORDER BY

08-Nov-
SQL, More Select statements
2016
49 Grouping
 Sometimes we need to have sub-
groups.
 This allows us to get subtotals.
 This is done by using the ‘GROUP
BY’ clause.

08-Nov-
SQL, More Select statements
2016
50 Having clause

 The HAVING clause defines criteria used to

eliminate certain groups from the output, just as
the WHERE clause does for individual row.
 HAVING and WHERE clauses work in a similar
manner. The difference is that WHERE works on
rows, while HAVING works on groups. Expressions
in HAVING clause must be single_value per group

08-Nov-
SQL, More Select statements
2016
51 Max() , Min() and Avg()
 MAX(A) is used to find the maximum value in a
column
 MIN(A) is used for finding the minimum value in a
column.
 AVG(A) is used to find the average of the values in a
column. It calculates the average (mean) of all
selected values of a given field.

08-Nov-
SQL, More Select statements
2016
EX-1:Find age of the youngest sailor with age
18, foreach rating with at least 2 such sailors
Sailors instance:
SELECT S.rating, MIN (S.age)
AS minage sid sname rating age
FROM Sailors S 22 dustin 7 45.0
WHERE S.age >= 18
29 brutus 1 33.0
GROUP BY S.rating
HAVING COUNT (*) > 1 31 lubber 8 55.5
32 andy 8 25.5
58 rusty 10 35.0
rating minage
64 horatio 7 35.0
3 25.5
71 zorba 10 16.0
7 35.0
Answer relation: 74 horatio 9 35.0
8 25.5
85 art 3 25.5
95 bob 3 63.5
96 frodo 3 25.5
Find age of the youngest sailor with age 18, for
each rating with at least 2 such sailors.
rating age rating age
7 45.0 1 33.0
1 33.0 3 25.5
8 55.5 3 63.5 rating minage
8 25.5 3 25.5 3 25.5
10 35.0 7 45.0 7 35.0
7 35.0 7 35.0 8 25.5
10 16.0 8 55.5
9 35.0 8 25.5
3 25.5
9 35.0
3 63.5
10 35.0
3 25.5
EX-2: Find age of the youngest sailor with age 18, foreach
rating with at least 2 such sailors and with every sailor under
60.
HAVING COUNT (*) > 1 AND EVERY (S. age <=60)

rating age
rating age
7 45.0 1 33.0
1 33.0 3 25.5
rating minage
8 55.5 3 63.5
8 25.5 7 35.0
3 25.5
10 35.0 8 25.5
7 45.0
7 35.0
10 16.0
7 35.0
9 35.0 8 55.5 What is the result of
3 25.5 8 25.5 changing EVERY to
3 63.5 9 35.0 ANY?
3 25.5 10 35.0
EX-3: Find age of the youngest sailor with age
18, foreach rating with at least 2 sailors between
18 and 60.
Sailors instance:
SELECT S.rating, MIN (S.age) AS minage
FROM Sailors S sid sname rating age
WHERE S.age >= 18 AND S.age <= 60 22 dustin 7 45.0
GROUP BY S.rating
29 brutus 1 33.0
HAVING COUNT (*) > 1
31 lubber 8 55.5
32 andy 8 25.5
58 rusty 10 35.0
64 horatio 7 35.0
rating minage 71 zorba 10 16.0
Answer relation: 3 25.5 74 horatio 9 35.0
7 35.0 85 art 3 25.5
8 25.5 95 bob 3 63.5
96 frodo 3 25.5
56 Examples
 select count(*) from Grade; refer Grade table
 select max(mark) from Grade;
 select min(mark) from Grade;
 select avg(mark) from Grade;
 select count (*), code from Grade
group by code;
 For more examples visit www.w3schools.com/sql

08-Nov-
SQL, More Select statements
2016