Temple University - PowerPoint PPT Presentation

1 / 47

About This Presentation

Title:

Temple University

Description:

Relational Model SQL Part II ... create table r( A1 D1, ..., An Dn, integrity-constraint1, integrity-constraint-n) ... Construct and submit SQL queries at run time ' ... – PowerPoint PPT presentation

Number of Views:30

Avg rating:3.0/5.0

Slides: 48

Provided by: Vas111

Learn more at: http://www.cis.temple.edu

Category:

more less

Transcript and Presenter's Notes

Title: Temple University

1
Temple University CIS Dept.CIS331 Principles
of Database Systems

V. Megalooikonomou
Relational Model SQL Part II
(based on notes by Silberchatz,Korth, and
Sudarshan and notes by C. Faloutsos at CMU)

2
General Overview - rel. model

Formal query languages
rel algebra and calculi
Commercial query languages
SQL
QBE, (QUEL)

3
Overview - detailed - SQL

DML
select, from, where, renaming, ordering,
aggregate functions, nested subqueries
insertion, deletion, update
other parts DDL, embedded SQL, auth etc

4
Reminder our Mini-U db
5
DML - insertions etc

insert into student
values (123, smith, main)
insert into student(ssn, name, address)
values (123, smith, main)

6
DML - insertions etc

bulk insertion how to insert, say, a table of
foreign-students, in bulk?

7
DML - insertions etc

bulk insertion
insert into student
select ssn, name, address
from foreign-student

8
DML - deletion etc

delete the record of smith

9
DML - deletion etc

delete the record of smith
delete from student
where namesmith
(careful - it deletes ALL the smiths!)

10
DML - update etc

record the grade A for ssn123 and course
cis351
update takes
set gradeA
where ssn123 and c-idcis351
(will set to A ALL such records)

11
DML - view update

consider the db-takes view
create view db-takes as
(select from takes where c-idcis351)
view updates are tricky - typically, we can only
update views that have no joins, nor aggregates
even so, consider changing a c-id to cis333....

12
DML - joins

so far INNER joins, eg
select ssn, c-name
from takes, class
where takes.c-id class.c-id

13
Reminder our Mini-U db
14
inner join
SSN
c-name
123
d.b.
o.s. gone!
234
d.b.
15
outer join
SSN
c-name
123
d.b.
234
d.b.
null
o.s.
16
outer join

select ssn, c-name
from takes outer join class on takes.c-idclass.c-
id

SSN
c-name
123
d.b.
234
d.b.
null
o.s.
17
outer join

left outer join
right outer join
full outer join
natural join

18
Overview - detailed - SQL

DML
select, from, where, renaming, ordering,
aggregate functions, nested subqueries
insertion, deletion, update
other parts DDL, embedded SQL, auth etc

19
Data Definition Language

create table student
(ssn char(9) not null,
name char(30),
address char(50),
primary key (ssn) )

20
Data Definition Language

create table r( A1 D1, , An Dn,
integrity-constraint1,
integrity-constraint-n)

21
Data Definition Language

Domains
char(n), varchar(n)
int, numeric(p,d), real, double precision
float, smallint
date, time

22
Data Definition Language

integrity constraints
primary key
foreign key
check(P)

23
Data Definition Language

create table takes
(ssn char(9) not null,
c-id char(5) not null,
grade char(1),
primary key (ssn, c-id),
check grade in (A, B, C, D, F))

24
Data Definition Language

delete a table difference between
drop table student
delete from student

25
Data Definition Language

modify a table
alter table student drop address
alter table student add major char(10)

26
Overview - detailed - SQL

DML
select, from, where, renaming, ordering,
aggregate functions, nested subqueries
insertion, deletion, update
other parts DDL, embedded SQL, auth etc

27
Embedded SQL

from within a host language (eg., C, VB)
EXEC SQL ltemb. SQL stmntgt END-EXEC
Q why do we need embedded SQL??

28
Embedded SQL

SQL returns sets host language expects a tuple -
impedance mismatch!
solution cursor, i.e., a pointer over the
set of tuples
example

29
Embedded SQL

main()
EXEC SQL
declare c cursor for
select from student
END-EXEC

30
Embedded SQL - ctnd

EXEC SQL open c END-EXEC
while( !sqlerror )
EXEC SQL fetch c into cssn, cname, cad
END-EXEC
fprintf( , cssn, cname, cad)

31
Embedded SQL - ctnd

EXEC SQL close c END-EXEC
/ end main() /

32
dynamic SQL

Construct and submit SQL queries at run time
? a place holder for a value provided when it
is executed
main() / set all grades to users input /
char sqlcmd update takes set grade ?
EXEC SQL prepare dynsql from sqlcmd
char inputgrade5a
EXEC SQL execute dynsql using inputgrade
/ end main() /

33
Overview - detailed - SQL

DML
select, from, where, renaming, ordering,
aggregate functions, nested subqueries
insertion, deletion, update
other parts DDL, embedded SQL, authorization, etc

34
SQL - misc

Later, well see
authorization
grant select on student to ltuser-idgt
transactions
other features (triggers, assertions etc)

35
General Overview - rel. model

Formal query languages
rel algebra and calculi
Commercial query languages
SQL
QBE, (QUEL)

36
Rel. model Introduction to QBE

Inspired by the domain relational calculus
P. -gt print (ie., select of SQL)
_x, _y domain variables (ie., attribute names)
Example find names of students taking cis351

37
Rel. model - QBE
CLASS
c-id
c-name
units
cis331
d.b.
2
cis321
o.s.
2
TAKES
SSN
c-id
grade
123
cis331
A
234
cis331
B
38
Rel. model - QBE
39
Rel. model - QBE
names of students taking cis351
SSN
c-id
grade
_x
cis351
40
Rel. model - QBE