1.1k likes | 1.23k Views
Chapter 4. An Introduction to SQL. History of SQL. Developed by IBM in 1970s Early prototype of DB2 called System R Current version is SQL:1999 All major database vendors support SQL All support a superset of a subset of SQL Both relvar and relation = table in SQL Tuple = row in SQL
E N D
Chapter 4 An Introduction to SQL
History of SQL • Developed by IBM in 1970s • Early prototype of DB2 called System R • Current version is SQL:1999 • All major database vendors support SQL • All support a superset of a subset of SQL • Both relvar and relation = table in SQL • Tuple = row in SQL • Attribute = column in SQL
Overview of SQL • It is the standard language for relational systems, although imperfect • Supports data definition (DDL) and data manipulation (DML) • DDL: CREATE TABLE, CREATE TYPE, ALTER TABLE • DML: SELECT, INSERT, DELETE, UPDATE
DDL CREATE TABLE S ( S# CHAR(5) NOT NULL, SNAME CHAR(20) NOT NULL, STATUS SMALLINT NOT NULL, CITY CHAR(15) NOT NULL, PRIMARY KEY (S#) ) ; Creates (an empty) table with column headings Creates an entry in the data dictionary (catalog) Data can be placed in the table by: 1) Interactive SQL INSERT statements 2) A “host” language program with embedded SQL INSERT statements 3) A utility “load” program that loads tables from files
DDL CREATE TABLE Is an executable statement Can be executed interactively through an SQL interface Can be executed from within a host language program
create table S (snum char(5) not null, sname char(20) not null, status numeric(5) not null, city char(15) not null, primary key (snum)); create table P (pnum char(6) not null, pname char(20) not null, color char(6) not null, weight numeric (5,1) not null, city char(15) not null, primary key (pnum)); The suppliers and parts database (SQL definition)
create table SP (snum char(5) not null, pnum char(6) not null, qty numeric(9), primary key (snum, pnum), foreign key (snum) references S, foreign key (pnum) references P); The suppliers and parts database (SQL definition, cont.)
S +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ SP +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P1 | 300 | | S1 | P2 | 200 | | S1 | P3 | 400 | | S1 | P4 | 200 | | S1 | P5 | 100 | | S1 | P6 | 100 | | S2 | P1 | 300 | | S2 | P2 | 400 | | S3 | P2 | 200 | | S4 | P2 | 200 | | S4 | P4 | 300 | | S4 | P5 | 400 | +------+------+------+ P +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | Red | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ The suppliers and parts database (sample values)
Data Types Integer Smallint Decimal (p, q) p decimal digits with q digits to the right of the decimal point Numeric (p, q) Number (p, q) Float [(p)] p binary digits of precision Real [(p)] (default precision if not specified) Char[acter] (n) character string of n characters (fixed length) (8 bit) Varchar (n) character string of up to n characters (varying length) (8 bit)
DDL ALTER TABLE < base table name> ADD < column name > <type> ; (the column values will be NULL or a default value can be specified) (change to definition only until values supplied) DROP TABLE < base table name > ; (deletes the table and any “associated” views or indexes) CREATE INDEX < index name > ON < base table name > ( <column name> ) ; CREATE UNIQUE INDEX < index name > ON < base table name > ( <column name> ) ; no two entries in the index have the same value i.e., no two records in the table can have the same value in the indexed field “required” for the primary key
SQL DDL: CREATE TABLE CREATE VIEW CREATE INDEX DROP TABLE DROP VIEW DROP INDEX ALTER TABLE DML: SELECT INSERT DELETE UPDATE
Relational operators in SQL (DML) • Restrict is implemented by SELECT • Subset of rows • Uses WHERE clause to narrow result • Uses SELECT * to copy entire table • Project is also implemented by SELECT • Subset of columns
Copy entire table: mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec)
Restrict: mysql> select snum, pnum, qty -> from SP -> where qty < 150; +------+------+------+ | snum | pnum | qty | +------+------+------+ | S1 | P5 | 100 | | S1 | P6 | 100 | +------+------+------+ 2 rows in set (0.02 sec)
Restrict with compound conditions: mysql> select * -> from S -> where status > 10 -> and city = 'Paris'; +------+-------+--------+-------+ | snum | sname | status | city | +------+-------+--------+-------+ | S3 | Blake | 30 | Paris | +------+-------+--------+-------+ 1 row in set (0.01 sec)
Project: mysql> select snum, city -> from S; +------+--------+ | snum | city | +------+--------+ | S1 | London | | S2 | Paris | | S3 | Paris | | S4 | London | | S5 | Athens | +------+--------+ 5 rows in set (0.00 sec)
Relational operators in SQL (DML) • Join is also implemented by SELECT • Result is a single table matched by values in the join columns of each source table • Uses WHERE clause to specify source tables • Uses dot operator to qualify like column names • S.snum: a qualified column name
Join: mysql> select S.snum, sname, status, city, pnum, qty -> from S, SP -> where S.snum = SP.snum; +------+-------+--------+--------+------+------+ | snum | sname | status | city | pnum | qty | +------+-------+--------+--------+------+------+ | S1 | Smith | 20 | London | P1 | 300 | | S1 | Smith | 20 | London | P2 | 200 | | S1 | Smith | 20 | London | P3 | 400 | | S1 | Smith | 20 | London | P4 | 200 | | S1 | Smith | 20 | London | P5 | 100 | | S1 | Smith | 20 | London | P6 | 100 | | S2 | Jones | 10 | Paris | P1 | 300 | | S2 | Jones | 10 | Paris | P2 | 400 | | S3 | Blake | 30 | Paris | P2 | 200 | | S4 | Clark | 20 | London | P2 | 200 | | S4 | Clark | 20 | London | P4 | 300 | | S4 | Clark | 20 | London | P5 | 400 | +------+-------+--------+--------+------+------+ 12 rows in set (0.00 sec)
Update operators in SQL - Insert Insert is implemented by INSERT INTO insert into S (snum,sname,status,city) values ('S1','Smith',20,'London'), ('S2','Jones',10,'Paris'), ('S3','Blake',30,'Paris'), ('S4','Clark',20,'London'), ('S5','Adams',30,'Athens');
Update operators in SQL - Insert Insert is implemented by INSERT INTO INSERT INTO TEMP (P#, Weight) SELECT P#, Weight FROM P WHERE COLOR = COLOR (‘Red’); Inserts part number and weight from P into part number and weight in TEMP for all red parts
Update operators in SQL - Delete Delete is implemented by DELETE DELETE FROM SP WHERE P# = P# (‘P2’); Deletes all shipments for part P2
Update operators in SQL - Update Update is implemented by UPDATE UPDATE S SET STATUS = 2 * STATUS, CITY = ‘Rome’ WHERE CITY = ‘Paris’; Doubles the status of the Parisian suppliers and moves them to Rome.
MySQL sqlsh: /usr/local/opt/mysql-3.23.37/bin/mysql -h jacaranda.intranet.csupomona.edu -u hnriley -pmypassword hnriley_db dbkeys: hnriley mypassword hnriley_db jacaranda.intranet.csupomona.edu one line four lines
MySQL intranet (104) % sqlsh Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> the prompt from the client program--the “monitor,” the “query processor”
MySQL mysql command word mysql> show tables; +----------------------+ | Tables_in_hnriley_db | +----------------------+ | Cellar | | EMP | | P | | Producer | | S | | SP | | Wines | | tempP | +----------------------+ 8 rows in set (0.00 sec)
MySQL mysql> select * from P where color = 'red'; +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P4 | Screw | Red | 14.0 | London | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ 3 rows in set (0.00 sec) SQL statement (a RESTRICT) entered on one line
MySQL mysql> select snum, sname -> from S; +------+-------+ | snum | sname | +------+-------+ | S1 | Smith | | S2 | Jones | | S3 | Blake | | S4 | Clark | | S5 | Adams | +------+-------+ 5 rows in set (0.00 sec) SQL statement (a PROJECT) entered on two lines
MySQL mysql> select pnum, pname -> from P -> where color = 'red' -> and city = 'London' -> ; +------+-------+ | pnum | pname | +------+-------+ | P1 | Nut | | P4 | Screw | | P6 | Cog | +------+-------+ 3 rows in set (0.00 sec) must always end with a semicolon monitor will continue prompting until semicolon entered SQL statement (a RESTRICT and a PROJECT) entered on multiple lines
MySQL project join restrict mysql> select snum, sname, pnum, pname -> from S, P -> where S.city = P.city; +------+-------+------+-------+ | snum | sname | pnum | pname | +------+-------+------+-------+ | S1 | Smith | P1 | Nut | | S4 | Clark | P1 | Nut | | S2 | Jones | P2 | Bolt | | S3 | Blake | P2 | Bolt | | S1 | Smith | P4 | Screw | | S4 | Clark | P4 | Screw | | S2 | Jones | P5 | Cam | | S3 | Blake | P5 | Cam | | S1 | Smith | P6 | Cog | | S4 | Clark | P6 | Cog | +------+-------+------+-------+ 10 rows in set (0.00 sec) SQL statement with a JOIN
SQL “JOIN” mysql> select * from S, P; +------+-------+--------+--------+------+-------+-------+--------+--------+ | snum | sname | status | city | pnum | pname | color | weight | city | +------+-------+--------+--------+------+-------+-------+--------+--------+ | S1 | Smith | 20 | London | P1 | Nut | Red | 12.0 | London | | S2 | Jones | 10 | Paris | P1 | Nut | Red | 12.0 | London | | S3 | Blake | 30 | Paris | P1 | Nut | Red | 12.0 | London | | S4 | Clark | 20 | London | P1 | Nut | Red | 12.0 | London | | S5 | Adams | 30 | Athens | P1 | Nut | Red | 12.0 | London | | S1 | Smith | 20 | London | P2 | Bolt | Green | 17.0 | Paris | | S2 | Jones | 10 | Paris | P2 | Bolt | Green | 17.0 | Paris | | S3 | Blake | 30 | Paris | P2 | Bolt | Green | 17.0 | Paris | | S4 | Clark | 20 | London | P2 | Bolt | Green | 17.0 | Paris | | S5 | Adams | 30 | Athens | P2 | Bolt | Green | 17.0 | Paris | | S1 | Smith | 20 | London | P3 | Screw | Blue | 17.0 | Rome | | S2 | Jones | 10 | Paris | P3 | Screw | Blue | 17.0 | Rome | | S3 | Blake | 30 | Paris | P3 | Screw | Blue | 17.0 | Rome | | S4 | Clark | 20 | London | P3 | Screw | Blue | 17.0 | Rome | | S5 | Adams | 30 | Athens | P3 | Screw | Blue | 17.0 | Rome | | S1 | Smith | 20 | London | P4 | Screw | Red | 14.0 | London | | S2 | Jones | 10 | Paris | P4 | Screw | Red | 14.0 | London | | S3 | Blake | 30 | Paris | P4 | Screw | Red | 14.0 | London | | S4 | Clark | 20 | London | P4 | Screw | Red | 14.0 | London | | S5 | Adams | 30 | Athens | P4 | Screw | Red | 14.0 | London | | S1 | Smith | 20 | London | P5 | Cam | Blue | 12.0 | Paris | | S2 | Jones | 10 | Paris | P5 | Cam | Blue | 12.0 | Paris | | S3 | Blake | 30 | Paris | P5 | Cam | Blue | 12.0 | Paris | | S4 | Clark | 20 | London | P5 | Cam | Blue | 12.0 | Paris | | S5 | Adams | 30 | Athens | P5 | Cam | Blue | 12.0 | Paris | | S1 | Smith | 20 | London | P6 | Cog | Red | 19.0 | London | | S2 | Jones | 10 | Paris | P6 | Cog | Red | 19.0 | London | | S3 | Blake | 30 | Paris | P6 | Cog | Red | 19.0 | London | | S4 | Clark | 20 | London | P6 | Cog | Red | 19.0 | London | | S5 | Adams | 30 | Athens | P6 | Cog | Red | 19.0 | London | +------+-------+--------+--------+------+-------+-------+--------+--------+ 30 rows in set (0.01 sec) the “join” part of the statement produces all possible combinations of rows--the “cartesian product of the two tables
SQL “JOIN” project join restrict mysql> select snum, sname, pnum, pname -> from S, P -> where S.city = P.city; +------+-------+------+-------+ | snum | sname | pnum | pname | +------+-------+------+-------+ | S1 | Smith | P1 | Nut | | S4 | Clark | P1 | Nut | | S2 | Jones | P2 | Bolt | | S3 | Blake | P2 | Bolt | | S1 | Smith | P4 | Screw | | S4 | Clark | P4 | Screw | | S2 | Jones | P5 | Cam | | S3 | Blake | P5 | Cam | | S1 | Smith | P6 | Cog | | S4 | Clark | P6 | Cog | +------+-------+------+-------+ 10 rows in set (0.00 sec) the project and restrict parts of the statement produce the wanted columns from the meaningful rows SQL statement with a JOIN
MySQL Scripts # # file tableBuilder.sql # script file to build table S # drop table S; create table S (snum char(5) not null, sname char(20) not null, status numeric(5) not null, city char(15) not null, primary key (snum)); insert into S (snum,sname,status,city) values ('S1','Smith',20,'London'), ('S2','Jones',10,'Paris'), ('S3','Blake',30,'Paris'), ('S4','Clark',20,'London'), ('S5','Adams',30,'Athens'); SQL statements
MySQL Scripts file containing SQL statements mysql command word note the .sql extension mysql> source tableBuilder.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 deleted old table created new table inserted six rows
MySQL Output to a File mysql command word file to receive copy of terminal output mysql> tee SuppliersList Logging to file 'SuppliersList' mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec) mysql> notee Outfile disabled. mysql> turn off the file output
MySQL Output to a File intranet (103) % cat SuppliersList Logging to file 'SuppliersList' mysql> select * from S; +------+-------+--------+--------+ | snum | sname | status | city | +------+-------+--------+--------+ | S1 | Smith | 20 | London | | S2 | Jones | 10 | Paris | | S3 | Blake | 30 | Paris | | S4 | Clark | 20 | London | | S5 | Adams | 30 | Athens | +------+-------+--------+--------+ 5 rows in set (0.00 sec) mysql> notee intranet (104) % copy of everything between “tee” and “notee”
Nulls The absence of any value. Not a value. P +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | P1 | Nut | Red | 12.0 | London | | P2 | Bolt | Green | 17.0 | Paris | | P3 | Screw | Blue | 17.0 | Rome | | P4 | Screw | | 14.0 | London | | P5 | Cam | Blue | 12.0 | Paris | | P6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ P4 color? (don’t know) not red not not red
Nulls If acceptable, then physically: P +------+-------+-------+ --------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+ --------+--------+ | P1 | Nut | Red | 0 12.0 | London | | P2 | Bolt | Green | 0 17.0 | Paris | | P3 | Screw | Blue | 0 17.0 | Rome | | P4 | Screw | | 1 14.0 | London | | P5 | Cam | Blue | 0 12.0 | Paris | | P6 | Cog | Red | 0 19.0 | London | +------+-------+-------+ --------+--------+ P4 color marked as “null” (no value entered) extra hidden column with “null marker”
Nulls create table Nullbiz (pnum char(6) not null, pname char(20), color char(6), weight numeric (5,1), city char(15), primary key (pnum)); insert into Nullbiz values ('NP1','Nut',null, 12.0, 'London'), ('NP2','Bolt','Green',null,'Paris'), ('NP3','Screw','Blue',17.0,null), ('NP4','Screw','Red',14.0,'London'), ('NP5','Cam','Blue',12.0,'Paris'), ('NP6','Cog','Red',19.0,'London'); primary key must be not null nulls are allowed
insert into Nullbiz values ('NP1','Nut',null, 12.0, 'London'), ('NP2','Bolt','Green',null,'Paris'), ('NP3','Screw','Blue',17.0,null), ('NP4','Screw','Red',14.0,'London'), ('NP5','Cam','Blue',12.0,'Paris'), ('NP6','Cog','Red',19.0,'London'); not a value, not a string mysql> select * from Nullbiz; +------+-------+-------+--------+--------+ | pnum | pname | color | weight | city | +------+-------+-------+--------+--------+ | NP1 | Nut | NULL | 12.0 | London | | NP2 | Bolt | Green | NULL | Paris | | NP3 | Screw | Blue | 17.0 | NULL | | NP4 | Screw | Red | 14.0 | London | | NP5 | Cam | Blue | 12.0 | Paris | | NP6 | Cog | Red | 19.0 | London | +------+-------+-------+--------+--------+ 6 rows in set (0.00 sec)
mysql command word Nulls allowed? mysql> describe Nullbiz; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | pnum | char(6) | | PRI | | | | pname | char(20) | YES | | NULL | | | color | char(6) | YES | | NULL | | | weight | decimal(5,1) | YES | | NULL | | | city | char(15) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) Bad choice. “Column Names”
mysql> alter table tempP add size char(5); Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from tempP; +------+-------+-------+--------+--------+------+ | pnum | pname | color | weight | city | size | +------+-------+-------+--------+--------+------+ | P1 | Nut | Red | 12.0 | London | NULL | | P2 | Bolt | Green | 17.0 | Paris | NULL | | P3 | Screw | Blue | 17.0 | Rome | NULL | | P4 | Screw | Red | 14.0 | London | NULL | | P5 | Cam | Blue | 12.0 | Paris | NULL | | P6 | Cog | Red | 19.0 | London | NULL | +------+-------+-------+--------+--------+------+ 6 rows in set (0.01 sec)
The Catalog in SQL • Every database has a catalog • A catalog has many schemas, one per user • Each catalog includes one Information Schema • The Information Schema is a collection of all other schemas as views • Represents a hypothetical Definition Schema for all users
The Information Schema - Examples • Tables (includes views) • Views (does not include tables) • Table constraints • Referential constraints • Column constraints • Privileges • (Because these objects are themselves tables, they can be queried)
Views CREATE VIEW GOOD_SUPPLIER AS SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15; You can now operate on the view, for example: SELECT S#, STATUS FROM GOOD_SUPPLIER WHERE CITY = ‘London’;
Views - Example Creating a view, and then selecting from the view is equivalent to a select sub-query SELECT GOOD_SUPPLIER .S#, GOOD_SUPPLIER .STATUS FROM (SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15 ) AS GOOD_SUPPLIER; WHERE GOOD_SUPPLIER.CITY = ‘London’;
SQL DDL DML CREATE ALTER DROP CREATE DROP CREATE DROP INSERT DELETE UPDATE SELECT TABLE “update” VIEW “query” “inquiry” “retrieval” INDEX
Select SQL Select provides for all of the “query” functionality of the DML: restrict, project, join. SELECT <column(s)> [ or * ] FROM <table(s)> WHERE <condition> ORDER BY <column> ; always sometimes always
Select mysql> select sname -> from S; +-------+ | sname | +-------+ | Smith | | Jones | | Blake | | Clark | | Adams | +-------+ 5 rows in set (0.00 sec) Result is a table with one column
Select mysql> select city -> from S; +--------+ | city | +--------+ | London | | Paris | | Paris | | London | | Athens | +--------+ 5 rows in set (0.00 sec) Result is a table. What’s wrong?
Select mysql> select distinct city -> from S; +--------+ | city | +--------+ | London | | Paris | | Athens | +--------+ 3 rows in set (0.02 sec) SQL keyword “distinct” to remove duplicates and make the result a true projection.