04-11-2022

Chapter 7
SQL, the Stpattured
[Link]
Overview
Introduction
.DDL Commands
. DML Commands
. SQL Statements, Operators Cduses
Dr.
[Link]
[Link]
Mat Aggregate Functions

DROP
[Link]-Madan
SQL: DDL Commands
CREATE TABLE: used to create

ALTER TABLE: modifies

TABLE: removes a
a table
a table
af
table from a
t was

database.
created.
SQL: CREATE TABLE Statement
Things to

the table name

what column(s) will make
Madar,
consider before you
The type of data yoable
create

up ihe primary key

are:

the names of the

colug
CREATE TABLE
CREATE TABLE stgment
name> syntax:
(field1 dataty NOT NULL).
field2 dape (NOT NULL)
 04-11-2022

SQL: Attributes Types

Summ ura ety
Aoat n
ntegar. Int. anal 1int.
o n g * * * l n t .

fleat. rel, dauble . To

[Link] Madàn
SQL: ALTER TABLE Statement
add or
drop columns on existing tahlo
temataddec mal LLdeo(L .ALTER TADLE
statement
Chancter-rmae char (n).ehatactet {ni
OTA ALTER TADLE «table name> syntax
ength
Naying varchar (n), char ADD attr datatype;
lmgh AEyngn chatactet
or
DROP COLUMN attr:
lmpth
aryingbtt Varying (n)
Date and tine
lengt
dale, tire datutlne,
timestanps t ine vith
EOne, Interval
Larye es
caaicton varciar (n), olo
binnry blob
Table 7.6 pg.164

SQL: DROP TABLE Statement Example:

Has two options: aan CREATE TABLE FoodCart (
.CASCADE: Specifies that Da_or varchar(10),
violations that are caused by foreigny
any
constraint
d a n
food varchar(20),
drog the table will profit float
cause the
be deleted.
corresponding rows o the related table to
mta
Da ROod profit
ALTER TABLE FoodCart (odCart
RESTRICT: blockhe deletion of the table of any ADD sold
amydate food profit sold
foreign key cadant violations would be created.
ALTER TABLE PodCart(
DROP statement syntax: DROP C0LUN profit FoodCart
DROP TALE «table name [ RESTRICT|CASCADE ]: date food sold
DROP TABLE FoodCart;
 04-11-2022

[Link]-Madan
SQL: DML Commands
INSERT: adds new rows to a table

UPDATE: modifies
DELETE: deletes one
one or mare

or
atfes.
more rows from a
table
SQL: INSERT Statement
To insert a rom into a table, it is reces
harve a value for each attnbute, crd
matters.

INSERT statement syntac

Madan
IHSERT nto ctcble
VALUES (vchuel', ra .
'value2UL;
Excnple: IHSERT intodcart
VALUES (02/RO3', pizza', 70)x
date food sold
date sold 02/25/03 pizza 350
02/25/03Ypizza 350 02/26/03 hotdog 500
02/26/03 hotdog 500 02/26/03 pizza 70

SQL: UPDATE Statement

rame SET
WHERE selection condition
Example: UPDATE FoodCaa
Madan
To update the content of the table:
UPDATE Statement syntax:
UPDATE ctable
«attr»NCie
sold 349
SQL: DELETE Statement
amtaMadan
To delete rows from the table:
DELETE Statement syrtax
DELETE FROn <table name
HERE condition:
Example: DELETE FROM Food
HHERE fod = hotdog
UHERE date '02/208' AND food 'pizza':
Fodcert FoodCert
date fooosold date food sold date food 6ld date food sold
02/25/02wa 350 02/25/08 pizza 349 02/25/08 pizza 3499 02/25/08 pizza |349
02/26/0hotdog 500 02/26/08 |hotdog |500 02/26/ndtdog 500 02/26/08 pizza |70
02/26/08 Ipizza 70 02/26/08|pizza |700 02/26/08 pizza |70
Note If the VHERE case is oitted cl ros of deta dekted frs the
ce
t
 04-11-2022

SQL Statements:
Mamta Madan
SQL Statements, Operations, Clauses SQL: SELECT Statement
A basic SELECT statement includes 3 c
- Select
SELECT attr ibuta name> FROM <toboUHERE <condition>
SQL Operations:
Join SELECI ERON WHERE
ta
.Left Join
Specifies the Speclfles Specifies the
.Right Join attributes that are tables serve
selection condition,
part of the p u t to the including the Join
-Like resulting relatlon Ement condition
Dr. M

sQL Clauses:
orde
Grou By
Having

SQL: SELECT Statement (cont.) Example: 1) SELECT*

U s i n g a * " in a select statement i n d i c g t m a t Person FROM person
Name Age Weight WHERE age
every attribute of the input table
Mac isaGe Hamy 34 80 Name Weigh
selected. Sally

Mamnt
28 64 Har O34 80
Example: SELECT * FROM ... ERE. George|29 70

Helena | 54 54
Mena 54
Peter 80
Peter |34 80
To get unique rows,te the keyword 2) SELECT weight 3) SELECT distinct weight
DISTINCT after ACT. FROM person
FROM person
Example: SELEDISTINCT FROM WHERE age > 3 WHERE age> 30;

OE-
Weight
| 80
 04-11-2022

SQL: Join operation

A join can be specified in the
which list the two input
5Rclause
SQL: Join
.left outer join left join
aMadan
operation (cont.)
relatios and the SELECT

Example: Mamta
UHERE clause which lists
te join condition. FROM emp left join
on [Link] [Link]:
dept

Emp Dept Emp.D [Link] [[Link] [Link]

D D Division 1000 CANO null null
100CA 1001
IT 1001 MA 1001 IT
100MA |1002 Sales 10TN 1002 Sales
1002 TN 1003 Biotech

SQL: Join operation (cont.)

inner join = join
SELECT
SQL: Join
right outer join = right join
SELECT
Madan
operation (cont.)
FROn emp join dept (or FRonc dept) FROM emp right join dept
on
[Link] [Link];
=
on [Link] [Link];

Emp.D [Link] Dept.1D [Link] [Link] [Link] [Link] [Link]

DR
1001 MAN a 1001 IT 1001 MANO 1001 IT
1002
1002 Sales 1002 TN |1002 Sales
nu nll 1003 Biotech
 04-11-2022

SQL: Like operation

Pattern matching selection
%(arbitrary string)
taMadan SQL: The 0RDER BY Clause
Ordered result selection
Madan
.dasc (descending order)
SELECT
SELECT
FRON cmp
FROM emp
WHERE ID like %01:
order by state
finds ID that endsth 01, c.g. 1001, 2001, cte desc
-(a single charg puts stateindegending order, eg. TN, MA, CA
ase(ascending e r )
SELECT
FROM SELECT

wRERE1ke
finds ID
_o FROnEm
ordery id asc
that has the second and third character
as 01, eg. 1010, 1011, 1012, 1013, etc puts ID in ascending order, e.g. 1001, 1002, 1003

SQL: The GROUP BY Clause SQL: The HAVING Clause

The function to divide the .The substitute of UHERE
tuples into and
for aggregatections
returns an aggregate for each .Usually, it is an aggregate
gron function's anion
SELECT Mamtä,
Usually, it is an aggregate fungts companion
food, sum(sold)
FROM FoodCart
aztofalSold
SELECT food, a
FROM FoodCart
group by food
sum(sold)
rnt
totd
aM s
as

group by food; having sum(sold)>

FOOdCart FoodCart
date fodd sold food totalSold date food sold
02/20pizza 349 food totalSold
hotdog 500 02/20 pizza_ 349 hotdog 500
02/26/08 hotdog 500 pizza 419 02/26/08 hotdog 500
02/26/08 pizza 70
02/26/08| pizza 70
 04-11-2022

MamtaMacan
SQL: Aggregate Functions
Are used to provide summarization infog for
SQL: Aggregate Functions (cont.)
FoodCart adan
SQL statements, which rcturn a singlsue. date food sold
02/26/08 plzza
coUNT(attr) 02/26/08 hotdog0o
SUM(attr)
MAX(attr)
MIN(atfr)
02/26/08 pizz 70
AVG(attr) coUNT(attr)- retu of rows that are not null
Ex: CoUNT (distije food) from FoodCart;-2
Note: when Msing aggregate functions, NULL values
SUn(attr)return the sum of values in the attr
are
notsdered, except in CoUNT("). Ex: SUn0 from FoodCart; -> 919

MAX(attr)-return the highest value from the attr

Ex: MAX(sold) from FoodCart: -> 500

FoodCart
date |food
rMadan
SQL: Aggregate Functions (cont.)

Sold
Dr.MamtaM4adan
02/25/08 pizza Some more add opn
02/26/08|hotdog 500
02/26/08 pizz 70
.MIN(attr) -> return lowest value from the attr
Ex: MIN(Ssold) rFoodCart: - 70

AVG(attrreturn the average value from the attr

Ex: AVGO from FoodCart; -> 306.33
Note: valuis rounded to the precision of the
datatype
 04-11-2022

Madan [Link]
Adding a constraint...
Sql> alter table employee addnary
Dropping a constráint key(name);
mta'
Sql> alterable employee drop
Dr. M primary key;

Adding referential integrity

constraints
Sql>Alter table employee adds
d tra
a in
n t abc [Link] Madan
Entering Date values

[Link]
foreign key(deptno) refermdes
department;
Date-03-jan-2013'
 04-11-2022

[Link] Using Joins Madan

This command helps you tg M changes Employee, Department
permanent. Select ename, dname
Sqcommit; From employee, degtment
[Link]
Where employeccSno=[Link]
And dnameNP

Views Mädan
.In SQL a VIEW io a virtual relaton base he result-set of
Views 4àmtaMädan
SQL Relations, Tables &
a SELECT otolement When we say Relation, it coutd be a Tay aView. There
are three kind of relatons:
Avlow conlains rowD and columno, Just llke a real table. The
floldoIn o viow ar0 liolds froUY or moro real tables In Uhe 1. Stored relations
dalaboso. In 0omo cas0s on modity a view and present es
tho dolo os f ho datla yecoming froma oingle table. We sometimes use the t b a s e relation or "base table"
2. Vinual relations
Syntax Viers

CREATEW vlow name AS 3. Temporary r e u

SELERT, Column_namo(s)
ablo_name D r .

WERE Conditlon
 04-11-2022

Creale a view wilth title and year and made by Paramount

studio. SQL-Querying View
MdalOddioName,
Movie (title, year, length, inColor
producerC#) A vlew could be used from inside a quervred
or from inslde another vlew, By adding
procedure,
ons, Joins, etc., to
a vlew, it allows us to present exacthne data we want to the
User.
CREATE VIEW ParahtMovie AS mta
SELECT title,year SELECT ille
FROM Movien FROM ParamMovieview
WHERE stdkioName = 'Paramount' WHERE ve1979
Havesame resun as
SCT tlle
FOM Movie--table
WHERE studioName = 'Paramount' AND year =

1979

SQL MamtaMadarfs
Querying View exagple
Movie (Ute, year, length, inColor, studioName, producerC#)
Movie Exec (name, address, certt, netWorth)
SQL
(INSERT)
Modifying View
Madan
INSERT INTO ParamountMovie
CREATE VIEW MovieProd AS VALUES (Star Trek', 1979):
SELECT le, name To
make the vlew PaamountMovie updar ule, we naed to add attnbute
FROM Movie, Movie Exec studioName P a L n c a u s e bacuse it makes more sense if the
StudioName is Paramount
WHERE producerC# cert insteacNULL.
SELECT name CREATE VIEW Paramount AS
FROM MovieProd SELECT studloName year
WHERE Lile =" With the Wind; FROM M
Movie
WHERE studmo "Paramount;
Same resusquery from tables Then
SELTahe O ParamountMovie
FROMOvie, MovleExec s(Paramount, 'Star Trek', 1979):
WHERE producerCt# = certt AND Ileo = The War Of the World
Tille year length InColor shudioName
Star Trek' 1079 0 NULL Parmount
producercs
NULL
 04-11-2022

SQL -Modifying View

(DELETE) Madan SQL -Modifying View
(UPDATE)
Madan
Suppose we wish to delete all movieith "Trek" in UPDATE from an updateable view
their titte from the updateable vieramountMovie. UPDATE ParamountMovie
SET year = 1979

DELETE FROM ParamountM WHERE title = 'Slar Trek the l e

WHERE title LIKE %Trek
It is turned into the bas table update
It is turned into thhaoe table delete
M

UPDATE Mov
DELETE ROM Movie SET yeac 1979
WHRe LIKE %Trek6' AND studioName = 'Paramount; the Movie' AND studioName=
ount'='Star Trek
WHEe
P

SQL -View (DROP) mtaMadan Note on views 1ädan

this view is theoretically updatable,
DROP view: All views can be dropped, w or not the view b
is updateable. cannot be updated in SQL

DROP VIEN ParamountMovie;

CREATE VIEW FLT-SCHEDAND-DAY
AS SELECT S.", [Link]
FROM FLT-SCHED S, FLT-WEEKDAYD
DROP VIEW does noleti any tuples of the underlying
relaton (table) May
Dr. Ma
WHERE [Link]¥

However, DROABLE will delete the table and also make

the view ParamountMovie unusable.

DROPLE Movie
 04-11-2022

[Link] Madan
Subqueries
SELFJOIN Operations
A SELF JOIN operation is usedtopodce
a result table when the relationsi
interest exists among rows hfe stored
within a single table.

D r . M
P_
-

SEJOIN Operations
SELECT e1.emp_last_name ||. || e1.emp_first nam
Supervisor
Triggers
MaO
e2.emp_last_name ||'. '|| e2.emp_firstnam
FROM employee e1, employee e2 PLUSQL can be used to wpitoaata basse
WHERE e1.emp_ssn e2.emp_supe
=

triggers. Triggers are useo define code

Supervisor Employ that is executed/firedhen certain actions
or event occur.
Bordoloi, Bijoy n e r , Suzanne
Atae data base level,
Bordolol, Bijoy hu, Waiman
triggers can beefined for events such as
Joyner, Suzann Markis, Marcia inserting adQrd into a table, deleting a
Joyner, Sane Amin, Hyder record, and updating a record.
Zhua Bock, Douglas
Zhu, Vman Joshi, Dinesh
Zhu, Waiman Prescott, Sheri
Dr. MaMas mi

da gadan
Dr. Manmta M
 04-11-2022

Audit Table lan

amtaMadan
Code for trigger
D Enploycouunibar changei Dono Create TRIGGER employee_add

[Link]
on

before update on employee

for each row
begin
if updating then
insert into audl EMPLOYNO,changedone,action)
values (:9LD.EMP_NO,sysdate, update');
end
end;

Mafyta Madan
Conclusions Types of triggers
Madan
Whenever we update the evooyee table, a Row Triggers
trigger will be fired e.g A row trigger is fired eacKtime a row in the
Update employee1 table is affected [Link] g n update statement
Set lastname='aofa updates multipleoWS , a row trigger is
fired once for paeh row.
Where empr
We
reco
can now check the audit data,
úst have been inserted. I.e aa
new Statementriggers
triggrwill be fired whenever an update in
A staehent trigger is fired once on behalf
of tridering statement.
the employee table occurs.
uepew eauigiN a
uep
uepew
 04-11-2022

DECLARE
Bank Transaction dan
acct_balance NUMBER(11,2);
acct CONSTANT NUMBER(AO3;
Consider the program belowaich
processes a bank transaqion. Before
deblt amt CONSTANT NUGTEER(5,2)
500.00;
allowing you to withdtav $500 from BEGIN
nta

account 3, it makgaSure the account has

sufficient fundsb cover the withdrawal. If SELECT bal INTOacct_balance FROM
acct
accounts ERE account_id
=

the funds available, the program debits

the account. Otherwise, the program IF acctbalance >= debit_amt THEN
UPR accounts
insaa record into an audit table.
Contd......

SET bal

ELSE
bal -debit_amt WHERE
account i d = acct;

INSERT INTO audit VALUES

an

d a n
Dr. Mamta Madan
Trapping predefined
exceptions
Dr. Mamta
acct_balance, 'Insufficientfds');
COMMIT;
End if
End;
 04-11-2022

Trapping Exceptions
Madan
Madan brgamta
mtaMadan
Madann
Another example

DECLARE V_Iname VARCHAR2 ( DECLARE v_invalid INTES

BEGIN BEGIN
SELECT last_name INTOIname FROM V_invalid= 100/0;
employees WHERE firstame = 'John';
[Link] (Last name is || EXCEPTION

V_Iname): WHEN ZERAOIVIDE THEN

Aa DBMSOUTPUT.PUT_LINE ('Attempt to
EXCEPTION
WHENTOO_MANY_ROWS THEN divieyo");
DBM OTPUT.PUT_LINE ("Your SELECT
statenent retrieved multiple rows): END
END;

[Link]
Trapping user-defined

exceptions
Using the RAISE statement
execution of a PL/SQL bRERdan
or
The RAISE statement stopsadmal
subprogram and transers control to an
exception handlerAISE statements can
raise predefineexceptions, or user-
defined exeáptfons whose names you
decide
pr
 04-11-2022

Raising a user defined

exception dan [Link]
adan
DECLARE V_deptno NUMBER v_name WHEN e_invalid_dept THE
VARCHAR2 (20):= Testing': alid_dept
EXCEPTION;
BEGIN nta Dr. Mamta
DBMS_OUTPUT.PUT_NË ('No such
department);
End;
UPDATE departmep
SET departmeame v_name WHERE
=

department deptno;
IF SOLNOTFOUND THEN
RAI_invalid_dept END IF;
EXCEPTION