1 dept. of cis, temple univ. cis616/661 – principles of data management v. megalooikonomou sql...

1

Dept. of CIS, Temple Univ.CIS616/661 – Principles of Data Management

V. MegalooikonomouSQL (part 2)

(based on slides by C. Faloutsos at CMU)

Overview - detailed - SQL DML

select, from, where, renaming, ordering,

aggregate functions, nested subqueries

insertion, deletion, update other parts: DDL, embedded SQL,

auth etc

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

Data Definition Languagecreate table r( A1 D1, …, An Dn, integrity-constraint1, … integrity-constraint-n)

Data Definition LanguageDomains: char(n), varchar(n) int, numeric(p,d), real, double

precision float, smallint date, time

Data Definition Languageintegrity constraints: primary key foreign key check(P)

Data Definition Languagecreate 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”))

Data Definition Languagedelete a table: difference between drop table student

delete from student

Data Definition Languagemodify a table: alter table student drop address

alter table student add major char(10)

Overview - detailed - SQL DML

select, from, where, renaming, ordering,

aggregate functions, nested subqueries

insertion, deletion, update other parts: DDL, embedded SQL,

auth etc

Embedded SQLfrom within a ‘host’ language (eg.,

‘C’, ‘VB’) EXEC SQL <emb. SQL stmnt> END-

EXEC

Q: why do we need embedded SQL??

Embedded SQLSQL returns sets; host language

expects a tuple - impedance mismatch!

solution: ‘cursor’, ie., a ‘pointer’ over the set of tuples.

example:

Embedded SQLmain(){…EXEC SQL declare c cursor for select * from studentEND-EXEC…

Embedded SQL - ctn’d…EXEC SQL open c END-EXEC… while( !sqlerror ){ EXEC SQL fetch c into :cssn, :cname, :cad END-EXEC fprintf( … , cssn, cname, cad);}

Embedded SQL - ctn’d…EXEC SQL close c END-EXEC…} /* end main() */

dynamic SQLmain(){ /* set all grades to user’s input */…char *sqlcmd=“ update takes set grade = ?”;EXEC SQL prepare dynsql from :sqlcmd ;char inputgrade[5]=“a”;EXEC SQL execute dynsql using :inputgrade;…} /* end main() */

Overview - detailed - SQL DML

select, from, where, renaming, ordering,

aggregate functions, nested subqueries

insertion, deletion, update other parts: DDL, embedded SQL,

auth etc

SQL - miscLater, we’ll see authorization: grant select on student to

<user-id> transactions other features (triggers, assertions

etc)

General Overview - rel. model Formal query languages

rel algebra and calculi Commercial query languages

SQL QBE, (QUEL)

Rel. model - QBE Inspired by the domain relational

calculus “P.” -> print (ie., ‘select’ of SQL) _x, _y: domain variables (ie.,

attribute names) Example: find names of students

taking 15-415

Rel. model - QBECLASSc-id c-name units15-413 s.e. 215-412 o.s. 2

TAKESSSN c-id grade

123 15-413 A234 15-413 B

STUDENTSsn Name Address

123 smith main str234 jones forbes ave

Rel. model - QBECLASSc-id c-name units

TAKESSSN c-id grade

STUDENTSsn Name Address

Rel. model - QBE

CLASSc-id c-name units

SSN c-id grade_x 15-415

STUDENTSsn Name Address_x P.

names of students taking 15-415

Rel. model - QBE condition box self-joins (Tom’s grandparents) ordering (AO., DO.) aggregation (SUM.ALL.,

COUNT.UNIQUE. , …) group-by (G.)

Rel. model - QBE

CLASSc-id c-name units

SSN c-id gradeP.AVG.ALL.

STUDENTSsn Name Address

aggregate: avg grade overall:

Rel. model - QBE

CLASSc-id c-name units

SSN c-id gradeP.G. P.AVG.ALL.

STUDENTSsn Name Address

aggregate: avg grade per student:

General Overview - rel. model Formal query languages

rel algebra and calculi Commercial query languages

SQL QBE, (QUEL)

Rel. model - QUELUsed in INGRES only - of historical

interest.Eg.: find all ssn’s in mini-U: range of s is student; retrieve (s.ssn);

]}[][|{ ssntssnsstudentst

Rel. model - QUELgeneral syntax:range of …. is t-

name

retrieve (attribute list)

where condition

SQL

select attr. list

from t-name

where condition

Rel. model - QUEL very similar to SQL also supports aggregates,

ordering etc

General Overview Formal query languages

rel algebra and calculi Commercial query languages

SQL QBE, (QUEL)

Integrity constraints Functional Dependencies Normalization - ‘good’ DB design