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> or 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 ); department instructor dept name building budget id name dept name salary 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 obligex_ident_fkey FOREIGN KEY (ident) REFERENCES Student(ident) ON UPDATE CASCADE ); ObligEx Student Ident PersonNr Name Ident Number Grade HandedIn Graded Approved Status 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 of 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 ); department instructor dept name building budget dept head id name dept name salary 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 order SELECT name , salary FROM instructor ORDER BY salary ASC; -- ASCending order SELECT name , salary , ID FROM instructor ORDER BY name , salary , ID; -- Major -to - minor order ; 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
Recommend
More recommend
