Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

1
Carnegie Mellon Univ.Dept. of Computer
Science15-415 - Database Applications

• C. Faloutsos
• Rel. model - SQL part3

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
  15-415
• update takes
• set gradeA
• where ssn123 and c-id15-415
• (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-id15-415)
• view updates are tricky - typically, we can only
  update views that have no joins, nor aggregates
• even so, consider changing a c-id to 15-222...

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
o.s. gone!
15
outer join
16
outer join

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

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, ie., 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

• 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, auth etc

34
SQL - misc

• Later, well see
• authorization
• grant select on student to ltuser-idgt
• transactions
• other features (triggers, assertions etc)
• see, e.g.
• http//www.contrib.andrew.cmu.edu/shadow/sql.html

35
General Overview - rel. model

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

36
Rel. model - QBE

• Inspired by the R.D.C.
• P. -gt print (ie., select of SQL)
• _x, _y domain variables (ie., attribute names)
• Example find names of students taking 15-415

37
Rel. model - QBE
38
Rel. model - QBE
39
Rel. model - QBE
names of students taking 15-415
40
Rel. model - QBE

• condition box
• self-joins (Toms grandparents)
• ordering (AO., DO.)
• aggregation (SUM.ALL., COUNT.UNIQUE. , )
• group-by (G.)

41
Rel. model - QBE
aggregate avg grade overall
42
Rel. model - QBE
aggregate avg grade per student
43
General Overview - rel. model

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

44
Rel. model - QUEL

• Used in INGRES only - of historical interest.
• Eg. find all ssns in mini-U
• range of s is student
• retrieve (s.ssn)

45
Rel. model - QUEL

• general syntax
• range of . is t-name
• retrieve (attribute list)
• where condition

SQL select attr. list from t-name where condition
46
Rel. model - QUEL

• very similar to SQL
• also supports aggregates, ordering etc

47
General Overview

• Formal query languages
• rel algebra and calculi
• Commercial query languages
• SQL
• QBE, (QUEL)
• Integrity constraints
• Functional Dependencies
• Normalization - good DB design