Review of SQL 5DV120 Database System Principles Ume a University - - PowerPoint PPT Presentation

Review of SQL

5DV120 — Database System Principles Ume˚ a University Department of Computing Science Stephen J. Hegner hegner@cs.umu.se http://www.cs.umu.se/~hegner

Review of SQL 20140327 Slide 1 of 45

About these Slides

• These slides are mostly adapted from those for 5DV119 by the same

instructor.

• The adaptations use the schema of the Silberschatz-Korth-Sudarsham

textbook, rather than that of the Elmasri-Navathe textbook.

• These slides will be used as a refresher/review, and will be covered much

more rapidly than in the introductory course.

• They are intended for those who already know/knew SQL, but need a

quick refresher.

Review of SQL 20140327 Slide 2 of 45

The SQL “Standard”

• SQL is the “standard” language for access to relational databases.
• There have been many standard versions, beginning with SQL92, and

currently ending with SQL:2011.

• But many of its features have evolved from earlier vendor-specific ones.
• As a result, almost no relational DBMS follows the standard very closely.
• Even the most basic things, such as the data types representing dates

and times, differ from system to system and greatly limit code portability.

• Most systems implement a “superset of a subset” of the standard

specification.

• Nevertheless, the basic features of most systems are very similar, even if

not completely compatible.

• To the extent that special features are needed, the open-source system

PostgreSQL will be used.

Review of SQL 20140327 Slide 3 of 45

The Parts of SQL

SQL consists of several parts: DDL: The data-definition language provides commands for defining and altering database schemata, including integrity constraints and virtual relations called views. DML: The data-manipulation language provides commands for both querying and updating databases. Transactions: There are basic SQL commands for specifying transactions.

• These are limited in scope and most systems have their own ways of

managing transactions. Authorization: SQL contains directives for granting and revoking privileges. Access from a host languages: SQL contains some basic commands for use when the language is embedded in a host programming language.

• These are limited in scope and many approaches to hosting SQL,

including ODBC, have their own ways of of doing similar things.

• The last two topics are not covered in this course.

Review of SQL 20140327 Slide 4 of 45

Clients for Direct Access to SQL via PostgreSQL

• The best way to access PostgreSQL is via the command-line interface:

psql --username <username> --hostname <servername> <dbname>

• r

psql -U <username> -h <servername> <dbname>

• <username> and <dbname> are usually the same on the systems of the

department.

• <hostname> is postgres on the systems of the department.
• With ident authentication, no special password is used.
• \? shows a list of system commands.
• Use ctrl-Z and then kill the process if parsing becomes too confused.
• For information on how to access MySQL on the departmental systems,

consult the slides for 5DV119.

Review of SQL 20140327 Slide 5 of 45

Defining Tables

CREATE TABLE department (dept_name VARCHAR (20) , building VARCHAR (15) , budget NUMERIC (12 ,2) CHECK (budget > 0), PRIMARY KEY (dept_name) ); CREATE TABLE instructor (ID VARCHAR (5), name VARCHAR (20) NOT NULL , dept_name VARCHAR (20) , salary NUMERIC (8 ,2) CHECK (salary > 29000) , PRIMARY KEY (ID), FOREIGN KEY (dept_name) REFERENCES department ON DELETE SET NULL );

dept name building budget

department

id name dept name salary

instructor

Review of SQL 20140327 Slide 6 of 45

Defining Tables 2

CREATE TABLE Student ( Name VARCHAR (40) NOT NULL , Personnr CHAR (11) NOT NULL , Ident VARCHAR (8) NOT NULL , Status VARCHAR (3), PRIMARY KEY(ident), UNIQUE (Personnr) ); CREATE TABLE ObligEx ( Ident VARCHAR (10) NOT NULL , Number INTEGER NOT NULL , Grade INTEGER NOT NULL , Handedin DATE , Graded DATE , Approved DATE , Status CHAR (1) , PRIMARY KEY (ident , number), CONSTRAINT

• bligex_ident_fkey

FOREIGN KEY (ident) REFERENCES Student(ident) ON UPDATE CASCADE );

Ident PersonNr Name Student Ident Number Grade HandedIn Graded Approved Status ObligEx

Review of SQL 20140327 Slide 7 of 45

Adding Constraints to Tables

CREATE TABLE instructor (ID VARCHAR (5) NOT NULL , dept_name CHAR (9), ...

• - Foreign

key to department cannot be declared here.

• - forward

reference : FOREIGN KEY ( dept_name ) REFERENCES department CREATE TABLE department (dept_name VARCHAR (20) , ... PRIMARY KEY (dept_name), );

• - Add

the foreign -key constraint here: ALTER TABLE instructor ADD CONSTRAINT Instr_FK1 FOREIGN KEY (dept_name) REFERENCES Department (dept_name );

• Added constraints must be named.
• Instr FK1 is the name of the above constraint.

Review of SQL 20140327 Slide 8 of 45

Adding Constraints to Tables – 2

CREATE TABLE instructor (ID VARCHAR (5) NOT NULL , dept_name CHAR (9), ...

• - Foreign

key to department cannot be declared here.

• - forward

reference : FOREIGN KEY ( dept_name ) REFERENCES department CREATE TABLE department (dept_name VARCHAR (20) ,

• - Add a new

attribute identifying the head

• f the

department dept_head VARCHAR (5) ... PRIMARY KEY (dept_name), FOREIGN KEY (dept_head) REFERENCES instructor (ID); );

• - The

foreign -key constraint must be added here: ALTER TABLE instructor ADD CONSTRAINT Instr_FK1 FOREIGN KEY (dept_name) REFERENCES Department (dept_name );

dept name building budget dept head

department

id name dept name salary

instructor

Review of SQL 20140327 Slide 9 of 45

The Basic From of a Query

• The basic form of an SQL query is as follows.

SELECT <attributes > FROM <tables > WHERE <conditions >

• The WHERE part is optional but most interesting queries require it.
• A very simple query:

SELECT name , salary FROM instructor ;

• Star captures all attributes:

SELECT * FROM instructor ;

• A simple condition:

SELECT name , salary FROM instructor WHERE dept_name = ’Comp.Sci.’;

Review of SQL 20140327 Slide 10 of 45

Renaming the Columns of a Query

• Columns may be given explicit names using the AS directive.

SELECT name AS Nachname , salary AS Gehalt FROM instructor WHERE (salary >= 65000) OR (dept_name = ’History ’);

• These names will appear as the column headers.
• However, such name changes cannot be used as aliases in the WHERE

clause.

Does not work:

SELECT name AS Nachname , salary AS Gehalt FROM instructor WHERE (Gehalt >= 65000) OR (dept_name = ’History ’);

• The use of AS in the FROM clause has different scoping, as will be

illustrated in examples to follow.

Review of SQL 20140327 Slide 11 of 45

Duplicates and Order

SELECT salary FROM instructor ; SELECT DISTINCT salary

• - Duplicates

removed FROM instructor ; SELECT name , salary FROM instructor ORDER BY salary DESC;

• - DESCending
• rder

SELECT name , salary FROM instructor ORDER BY salary ASC;

• - ASCending
• rder

SELECT name , salary , ID FROM instructor ORDER BY name , salary , ID;

• - Major -to - minor
• rder ;

Review of SQL 20140327 Slide 12 of 45

Queries on Two Relations

Problem: For each building, find the names of the instructors whose department is in that building.

• This query requires information from both the instructor and the

department relations.

• A first try:

SELECT building , name FROM department , instructor ;

• Does this work?
• No, it generates the Cartesian product of the two relations.
• A join condition is required:

SELECT building , name FROM department , instructor WHERE (department .dept_name= instructor .dept_name );

• Note also the name resolution.

Review of SQL 20140327 Slide 13 of 45

The JOIN Operation of SQL

• The join operation is used so often that SQL has a special notation for it.

SELECT building , name FROM department INNER JOIN instructor ON ( department .dept_name=instructor .dept_name );

is equivalent to

SELECT building , name FROM department , instructor WHERE (department .dept_name= instructor .dept_name );

• The keyword INNER may be omitted.
• Such queries may include a WHERE clause as well.

SELECT building , name FROM department INNER JOIN instructor ON ( department .dept_name=instructor .dept_name) WHERE (salary > 50000);

Review of SQL 20140327 Slide 14 of 45

The Natural Join of SQL

• When the names of the columns to be joined are the same in both

relations, instead of using inner join, the natural join operation may be used.

SELECT * FROM department NATURAL JOIN instructor ;

is almost equivalent to

SELECT * FROM department INNER JOIN instructor ON ( department .dept_name=instructor .dept_name );

• The difference is that matching columns are not repeated in the natural

join, while they are in the inner join.

• The match is on all columns with matching names in the relations.
• If there are no matching columns, the Cartesian product is the result

(which is almost never what is intended). Try the following query to see this effect:

SELECT * FROM classroom NATURAL JOIN instructor ;

Review of SQL 20140327 Slide 15 of 45

Aliases and Self Joins

Query: Find all pairs of departments which are housed in the same building.

• It is necessary to use aliases:

SELECT D1.dept_name , D2.dept_name FROM department AS D1 JOIN department AS D2 ON (D1.building=D2.building );

• r

SELECT D1.dept_name , D2.dept_name FROM department AS D1 , department AS D2 WHERE (D1.building=D2.building );

Review of SQL 20140327 Slide 16 of 45

Theta Joins

• Join conditions need not be based only upon equality.
• Joins based upon other comparison operators are often called theta joins.

Query: Find those instructors whose salary is greater than the department budget.

SELECT DISTINCT ID , name FROM instructor JOIN department ON (( instructor .dept_name= department.dept_name) AND (salary > budget ));

Review of SQL 20140327 Slide 17 of 45

Set and Multiset Operations in SQL

• Queries return multisets of tuples.

Multiset: A “set” in which elements may occur more than once.

• The usual set operations apply to multisets in SQL.

Name Symbol SQL union ∪

UNION

intersection ∩

INTERSECT

difference \ or −

EXCEPT

Some implementations of SQL do not support INTERSECT and EXCEPT.

Example: Mostly, these are small single-user systems such as MS Access. Example: MySQL is alone amongst “major” systems which do not support these set operations.

• Later, it will be shown how to achieve the same results using

embedded subqueries.

Review of SQL 20140327 Slide 18 of 45

Embedded Subqueries

• It is often useful, if not essential, to be able to use subqueries in the

WHERE clause of a query.

Query: Find all instructors who work in the same department as Srinivasan.

• When a subquery returns a set consisting of just one tuple, the result

may be regarded as a tuple.

SELECT * FROM instructor WHERE dept_name = (SELECT dept_name FROM instructor WHERE name=’Srinivasan ’);

• Note the lexical scoping of variables!
• An alternative using IN (set membership ∈):

SELECT * FROM instructor WHERE dept_name IN (SELECT dept_name FROM instructor WHERE name=’Srinivasan ’);

Review of SQL 20140327 Slide 19 of 45

Queries with Existential Quantification

Query: Find all instructors who teach or have taught a section of a course which Srinivasan has also taught.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE course_id IN (SELECT course_id FROM teaches WHERE id IN (SELECT id FROM instructor WHERE (name=’Srinivasan ’)));

• Exclude Srinivasan from the list.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE course_id IN (SELECT course_id FROM teaches WHERE id IN (SELECT id FROM instructor WHERE (name=’Srinivasan ’))) AND (name <>’Srinivasan ’);

Review of SQL 20140327 Slide 20 of 45

Realizing INTERSECT Using Subqueries

• As already noted, some relational systems do not support the INTERSECT
• peration.

Query: Find those instructors who teach or have taught both CS-101 and CS-319.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -101 ’) INTERSECT SELECT instructor .ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -319 ’);

• Fortunately, this operation can be realized using subqueries.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -101 ’) AND ( instructor.ID IN (SELECT instructor .ID FROM instructor JOIN teaches ON (instructor .ID=teaches.ID) WHERE (course_id=’CS -319 ’)));

• Only the key ID need be used in the subquery.

Review of SQL 20140327 Slide 21 of 45

Realizing EXCEPT Using Subqueries

• Some systems do not support the EXCEPT operation.

Query: Find those instructors who teach or have taught CS-101 but not CS-319.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -101 ’) EXCEPT SELECT instructor .ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -319 ’);

• Fortunately, this operation can be realized using subqueries.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches ON ( instructor .ID=teaches.ID) WHERE (course_id=’CS -101 ’) AND (NOT ( instructor .ID IN (SELECT instructor .ID FROM instructor JOIN teaches ON (instructor .ID=teaches.ID) WHERE (course_id=’CS -319 ’))));

• Only the key ID need be used in the subquery.

Review of SQL 20140327 Slide 22 of 45

Queries with Universal Quantification

Query: Find all instructors who teach or have taught every course which Srinivasan also teaches or has taught. Exclude Srinivasan himself.

• At first sight, this appears to be impossible with SQL.
• However, it may be rephrased as a double negation:
• Find all instructors I for which there is no course C which Srinivasan

teaches but I does not.

• This operation is formally known as division and is studied more carefully

in connection with the relational algebra.

SELECT DISTINCT Ins.ID , name FROM instructor AS Ins JOIN teaches ON (Ins.ID=teaches.ID) WHERE NOT EXISTS (SELECT course_id FROM teaches WHERE (ID IN (SELECT ID FROM instructor WHERE (name=’Srinivasan ’))) EXCEPT (SELECT course_id FROM teaches WHERE (Ins.ID=teaches.ID ))) AND NOT (Ins.ID IN (SELECT ID FROM instructor WHERE (name=’Srinivasan ’)));

Review of SQL 20140327 Slide 23 of 45

Queries which Count (without Aggregation)

Query: Find all instructors who teach at least two distinct courses.

SELECT DISTINCT instructor.ID , name FROM instructor JOIN teaches AS T1 ON ( instructor .ID=T1.ID) JOIN teaches AS T2 ON ( instructor .ID=T2.ID) WHERE (T1.course_id <>T2.course_id );

Query: Find all instructors who teach exactly one course.

SELECT DISTINCT instructor.ID , name FROM instructor WHERE EXISTS (SELECT * FROM teaches WHERE (instructor .ID=teaches.ID)) AND NOT EXISTS (SELECT * FROM teaches AS T1 JOIN Teaches AS T2 ON (T1.ID=T2.ID) WHERE (T1.course_id <>T2.course_id) AND (T1.ID=instructor .ID ));

Exercise: Find all instructors who teach exactly two courses.

Review of SQL 20140327 Slide 24 of 45

ALL and ANY

Query: Find the instructors(s) with the highest salary.

SELECT DISTINCT ID , name FROM instructor WHERE salary >= ALL (SELECT salary FROM instructor );

Query: Find the instructors(s) with salaries which are not the lowest.

SELECT DISTINCT ID , name , salary FROM instructor WHERE salary > ANY (SELECT salary FROM instructor );

Review of SQL 20140327 Slide 25 of 45

Pattern Matching in SQL

• SQL has two features for pattern matching:

LIKE: uses a special syntax. SIMILAR TO: uses regular expressions.

Query: Find all instructors whose names begin with the letter C.

• % is the wildcard symbol for LIKE.
• The following works in both PostgreSQL and MySQL:

SELECT ID , name FROM instructor WHERE name LIKE ’C%’;

• MySQL uses case-insensitive matching, and so the following there,

but in PostgreSQL it only finds names beginning with a lower-case c.

SELECT ID , name FROM instructor WHERE name LIKE ’c%’;

• LIKE in PostgreSQL uses case-sensitive matching,
• Use ILIKE for case-insensitive matching.

SELECT ID , name FROM instructor WHERE name ILIKE ’c%’;

Review of SQL 20140327 Slide 26 of 45

Pattern Matching in SQL Using SIMILAR TO

MySQL does not support SIMILAR TO, although it is part of the

SQL:1999 standard.

• Here are some examples which run under PostgreSQL:

Query: Find all instructors whose names begin with the letter C.

SELECT ID , name FROM instructor WHERE name SIMILAR TO ’C%’; SELECT ID , name FROM instructor WHERE name SIMILAR TO ’C[a-z]*’;

• Underscore matches a single character with both SIMILAR TO and LIKE.

SELECT ID , name FROM instructor WHERE name SIMILAR TO ’C____ ’;

Review of SQL 20140327 Slide 27 of 45

Additional Examples of Pattern Matching

Query: Find the ID and name of all instructors whose ID has 3 or 8 as the third digit from the left.

SELECT ID , name FROM instructor WHERE (ID LIKE ’__3%’) OR (ID LIKE ’__8%’);

• SIMILAR TO has a built-in disjunction operator:

SELECT ID , name FROM instructor WHERE ID SIMILAR TO ’__ (3|8)% ’;

• Tilde is the escape character for both operators.

Query: Find the IDs and names of instructors containing the character %:

SELECT name FROM instructor WHERE name LIKE ’%~%% ’;

Review of SQL 20140327 Slide 28 of 45

Basic Update Operations in SQL

• There are three basic forms of update in SQL:

Insertion: Using the INSERT directive, new tuples may be added to a relation. Deletion: Using the DELETE directive, existing tuples may be removed from a relation. Modification: Using the UPDATE directive, the values in fields of existing tuples may be changed. A note on terminology:

• The word update has two distinct meanings, both of which are

entrenched in the database literature and practice.

• 1. In the general database literature, an update may refer to any
• peration which changes of the state of a database.
• 2. In SQL, an update refers to a modification of an existing tuple or

tuples.

• It is necessary to resolve which of these two meanings is intended by

using context.

Review of SQL 20140327 Slide 29 of 45

The SQL INSERT Directive

• There are two basic forms of insertion:
• In the first, all fields for a single tuple are specified from left to right:

INSERT INTO instructor VALUES (’00000 ’, ’Ortega ’, NULL , ’100000 ’);

• In the second, fields may be listed in any order and missing fields are

taken to be NULL:

INSERT INTO instructor (ID , name , salary) VALUES (’00000 ’, ’Ortega ’, ’100000 ’);

• In both cases, the insertion will fail if any integrity constraint would be

violated by the insertion.

• There is also a third form of insertion which involves views (not

considered here).

Review of SQL 20140327 Slide 30 of 45

The SQL DELETE Directive

• Deletion involves the use of a WHERE clause to identify the tuples to be

deleted.:

DELETE FROM instructor WHERE ID=’00000 ’;

• In contrast to the INSERT directive, DELETE may remove several tuples at
• nce.

DELETE FROM instructor WHERE dept_name=’Comp.Sci.’;

• Deletion can never cause a violation of a PRIMARY KEY or UNIQUE

constraint, but it is possible for a FOREIGN KEY to be violated.

• The deletion is not executed if it would cause such a violation.

Review of SQL 20140327 Slide 31 of 45

The SQL UPDATE Directive

• Modification via UPDATE involves the use of a SET clause to identify the

changes and a WHERE clause to identify the tuples to be modified:

UPDATE instructor SET dept_name = ’Comp.Sci.’ WHERE ID=’00000 ’;

• UPDATE may modify several tuples at once.

UPDATE instructor SET SALARY = SALARY

• 10000

WHERE dept_name=’Comp.Sci.’;

• Modification can cause a violation of a PRIMARY KEY or UNIQUE constraint
• nly if it alters the value of a primary or candidate key.
• Other forms of constraints may be violated, however.
• The deletion is not executed if it would cause such a violation.

Review of SQL 20140327 Slide 32 of 45

Multiple Updates and Integrity Constraints

• By default, integrity constraints are checked after each INSERT, DELETE,

and UPDATE operation, which can lead to intermediate inconsistency.

• Consider again the augmented university schema in which each

department has a head.

dept name building budget dept head

department

id name dept name salary

instructor

Example: Add the new instructor Ortega and make him head of the newly added Management department.

INSERT INTO instructor VALUES (’00000 ’, ’Ortega ’, ’Management ’, ’150000 ’); INSERT INTO department VALUES (’Management ’,’Watson ’,’1000000 ’,’00000 ’);

• Regardless of which order these operations are executed, a violation of a

foreign-key constraint will result.

• In PostgreSQL and most other major systems, the solution involves

DEFERRABLE constraints and explicit transactions.

Review of SQL 20140327 Slide 33 of 45

Explicit Transactions 1

• The foreign key which references the Department relation is made

deferrable and then deferred.

CREATE TABLE instructor (<column declarations > <constraints

• ther

than fkey_instr >); CREATE TABLE department (<column declarations > <constraints >); ALTER TABLE instructor ADD CONSTRAINT fkey_instr FOREIGN KEY (dept_name) REFERENCES department (dept_name) DEFERRABLE INITIALLY DEFERRED;

• This means that it is not checked until the end of a transaction.

Notes:

• The constraint fkey instr is added using ALTER TABLE instructor

after the CREATE TABLE Department directive to avoid forward-reference errors during table definition.

• The constraint fkey instr is made DEFERRABLE and set to be

INITIALLY DEFERRED to allow transactions to defer checking it until

commit time during update execution.

Review of SQL 20140327 Slide 34 of 45

Explicit Transactions 2

• In both PostgreSQL and MySQL BEGIN and COMMIT markers may be used

to identify the bounds of the transaction.

BEGIN; INSERT INTO instructor VALUES (’00000 ’, ’Ortega ’, ’Management ’, ’150000 ’); INSERT INTO department VALUES (’Management ’,’Watson ’,’1000000 ’,’00000 ’); COMMIT;

Note: If the constraint fkey instr already exists but is not DEFERRABLE or is

DEFERRABLE but not INITIALLY DEFERRED, this may be changed by

dropping the constraint and then adding it again with the desired properties.

ALTER TABLE instructor DROP CONSTRAINT fkey_instr ; ALTER TABLE instructor ADD CONSTRAINT fkey_instr FOREIGN KEY (dept_name) REFERENCES department (dept_name) DEFERRABLE INITIALLY DEFERRED;

Review of SQL 20140327 Slide 35 of 45

Cascading Updates

• By default, if an update would violate an integrity constraint, that update

fails and the database is not changed.

• However, it is possible to cascade DELETE and UPDATE directives with

respect to foreign-key constraints. Example: Suppose that a course is dropped (is deleted from the course relation.)

• It then makes sense to delete all information about the sections of that

course as well.

• This is accomplished via the ON DELETE CASCADE clause:

CREATE TABLE section (course_id VARCHAR (8), ... FOREIGN KEY (course_id) REFERENCES course ON DELETE CASCADE );

Review of SQL 20140327 Slide 36 of 45

Cascading Updates — 2

• Cascading may also be applied to UPDATE (modification) directives.

Example: Suppose that the bureaucrats decide to change the course IDs.

• It then makes sense to reflect those changes in the other relations which

have that attribute as a foreign key from the course relation.

• This may be accomplished via the ON UPDATE CASCADE clause:

CREATE TABLE section (course_id VARCHAR (8), ... FOREIGN KEY (course_id) REFERENCES course ON DELETE CASCADE ON UPDATE CASCADE );

Review of SQL 20140327 Slide 37 of 45

Cascading Updates — 3

• Upon deletion of a foreign key, conversion to null is sometimes the

appropriate step. Example: Suppose that the advisor of a student leaves the university.

• It then makes sense to keep the student in the advisor relation, but to

give the student a null advisor.

• This may be accomplished via the ON UPDATE SET NULL clause:

CREATE TABLE advisor (s_ID VARCHAR (5), i_ID VARCHAR (5), PRIMARY KEY (s_ID), FOREIGN KEY (i_ID) REFERENCES INSTRUCTOR (ID) ON DELETE SET NULL , FOREIGN KEY (s_ID) REFERENCES STUDENT (ID) ON DELETE CASCADE );

Review of SQL 20140327 Slide 38 of 45

A Schema for a Grading Database

• Shown below are SQL definitions for two of the relations for a grading

database similar to that used for this course.

CREATE TABLE Student ( Name VARCHAR (40) Not Null , Personnr CHAR (11) Not Null , -- YYMMDD -XXXX Ident VARCHAR (10) Not Null , -- @cs.umu.se user ID PRIMARY KEY (Ident), UNIQUE (Personnr) ); CREATE TABLE ObligEx ( Ident VARCHAR (10) Not Null , Number INTEGER Not Null , -- exercise number (1 or 2) Grade INTEGER , -- numerical point score HandedIn DATE , -- date first submitted Graded DATE , -- date first graded Approved DATE , -- date approved satisfactory Status CHAR (1) , -- S or U PRIMARY KEY (Ident , Number), CONSTRAINT

• bligex_ident_fkey

FOREIGN KEY (Ident) REFERENCES Student(Ident) ON UPDATE CASCADE );

Ident PersonNr Name Student Ident Number Grade HandedIn Graded Approved Status ObligEx

Review of SQL 20140327 Slide 39 of 45

Outer Joins

• First want a scheme with the form of ObligEx, for Exercise 1 only, with

an entry for every student and nulls for missing values.

• The (left) outer join operation delivers the desired structure.
• It is similar to an (inner) join, but it fills in missing matches with nulls.
• It is called left because the left table in the construction is the base; the

right table is padded with nulls.

SELECT S.Ident , E1.Grade , E1.HandedIn , E1.Graded , E1.Approved , E1.Status FROM (SELECT Ident FROM Student) AS S LEFT OUTER JOIN (SELECT * FROM ObligEx WHERE Number =1) AS E1 ON (S.Ident=E1.Ident );

• A right outer join is defined analogously.

Review of SQL 20140327 Slide 40 of 45

Outer Joins —2

• Now add on the second exercise as well by using a second outer join.

Ident Gr1 DateH1 DateG1 DateA1 St1 Gr2 DateH2 DateG2 DateA2 St2 ObligExAll

SELECT S.Ident , E1.Grade AS Gr1 , E1.HandedIn AS DateH1 , E1.Graded AS DateG1 , E1.Approved AS DateA1 , E1.Status St1 , E2.Grade AS Gr2 , E2.HandedIn AS DateH2 , E2.Graded AS DateG2 , E2.Approved AS DateA2 , E2.Status AS St2 FROM (SELECT Ident FROM Student) AS S LEFT OUTER JOIN (SELECT * FROM ObligEx WHERE Number =1) AS E1 ON (S.Ident=E1.Ident) LEFT OUTER JOIN (SELECT * FROM ObligEx WHERE Number =2) AS E2 ON (S.Ident=E2.Ident );

Review of SQL 20140327 Slide 41 of 45

Views

• A view is a virtual table which is constructed using a query.
• It differs from a query in that:
• It persists in time, just as a true table.
• Its state reflects updates to the true tables.
• It has a name and may be used in large part as would any table of

the database. Basic syntax: CREATE VIEW <name> AS <query>; Example:

CREATE VIEW Instructor_Student (Instr_ID , Instr_name , Student_ID , Student_name ) AS SELECT DISTINCT I.ID , I.name , S.ID , S.name FROM instructor AS I INNER JOIN teaches AS Te ON (I.ID=Te.ID) INNER JOIN takes as Ta ON ((Te.course_id=Ta.course_id) AND (Te.sec_id=Ta.sec_id) AND (Te.semester=Ta.semester) AND (Te.year=Ta.year )) INNER JOIN student as S ON (S.ID=Ta.ID);

Review of SQL 20140327 Slide 42 of 45

Updates to Views

• Under limited conditions, updates to views are possible in standard SQL.
• There must be an “obvious” way to reflect the update to the true tables.
• Unfortunately, PostgreSQL does not support updates to views natively.
• It is, however, possible to use triggers to accomplish view updates in

PostgreSQL 9.x.

Review of SQL 20140327 Slide 43 of 45

The Logic of Null Values

• The value of NULL is treated as unknown in truth-valued expressions.

A B (A OR B) (A AND B) (NOT A) false false false false false false true true false true true false true false false true true true true false true unknown true unknown false false unknown unknown false true unknown true true unknown unknown unknown false unknown false unknown unknown unknown unknown unknown unknown

• Conditions of the form (A=B) also evaluate to unknown when at least
• ne of the arguments evaluates to NULL.
• Expressions which evaluate to unknown are not considered to be true for

the purpose of a query in SQL.

Review of SQL 20140327 Slide 44 of 45

Aggregation

Question: What about basic aggregation in SQL? Answer: It will be reviewed with the material on OLAP and more advanced aggregation.

Review of SQL 20140327 Slide 45 of 45