SQL Commands University of California Berkeley School of

SQL Commands University of California, Berkeley School of Information Management and Systems SIMS 257: Database Management 9/30/1999 SIMS 257: Database Management -- Ray Larson

Review • Relational Algebra • Relational Calculus 9/30/1999 SIMS 257: Database Management -- Ray Larson

Relational Algebra Operations • • Select Project Product Union Intersect Difference Join Divide 9/30/1999 SIMS 257: Database Management -- Ray Larson

Relational Calculus • Relational Algebra provides a set of explicit operations (select, project, join, etc) that can be used to build some desired relation from the database. • Relational Calculus provides a notation formulating the definition of that desired relation in terms of the relations in the database without explicitly stating the operations to be performed • SQL is based on the relational calculus. 9/30/1999 SIMS 257: Database Management -- Ray Larson

SQL • Structured Query Language • SEQUEL from IBM San Jose • ANSI 1992 Standard is current version (SQL 92) • Basic language is standardized across relational DBMSs. Each system may have proprietary extensions to standard. 9/30/1999 SIMS 257: Database Management -- Ray Larson

SQL Uses • Database Definition and Querying – Can be used as an interactive query language – Can be imbedded in programs • Relational Calculus combines Select, Project and Join operations in a single command. SELECT. 9/30/1999 SIMS 257: Database Management -- Ray Larson

SELECT • Syntax: – SELECT [DISTINCT] attr 1, attr 2, …, attr 3 FROM rel 1 r 1, rel 2 r 2, … rel 3 r 3 WHERE condition 1 {AND | OR} condition 2 ORDER BY attr 1 [DESC], attr 3 [DESC] 9/30/1999 SIMS 257: Database Management -- Ray Larson

SELECT Conditions • • • = equal to a particular value >= greater than or equal to a particular value > greater than a particular value <= less than or equal to a particular value <> not equal to a particular value LIKE “*term*” (may be other wild cards in other systems) • IN (“opt 1”, “opt 2”, …, ”optn”) • BETWEEN val 1 AND val 2 • IS NULL 9/30/1999 SIMS 257: Database Management -- Ray Larson

Relational Algebra Selection using SELECT • Syntax: – SELECT * WHERE condition 1 {AND | OR} condition 2 9/30/1999 SIMS 257: Database Management -- Ray Larson

Relational Algebra Projection using SELECT • Syntax: – SELECT [DISTINCT] attr 1, attr 2, …, attr 3 FROM rel 1 r 1, rel 2 r 2, … rel 3 r 3 9/30/1999 SIMS 257: Database Management -- Ray Larson

Relational Algebra Join using SELECT • Syntax: – SELECT * FROM rel 1 r 1, rel 2 r 2 WHERE r 1. linkattr = r 2. linkattr 9/30/1999 SIMS 257: Database Management -- Ray Larson

Aggregate Functions • • • Count Avg SUM MAX MIN 9/30/1999 SIMS 257: Database Management -- Ray Larson

Using Aggregate functions • SELECT attr 1, Sum(attr 2) AS name FROM tab 1, tab 2. . . • GROUP BY attr 1, attr 3 HAVING condition; 9/30/1999 SIMS 257: Database Management -- Ray Larson

Using an Aggregate Function • SELECT DIVECUST. Name, Sum([Price]*[qty]) AS Total • FROM (DIVECUST INNER JOIN DIVEORDS ON DIVECUST. [Customer No] = DIVEORDS. [Customer No]) INNER JOIN DIVEITEM ON DIVEORDS. [Order No] = DIVEITEM. [Order No] • GROUP BY DIVECUST. Name • HAVING (((DIVECUST. Name) Like "*Jazdzewski")); 9/30/1999 SIMS 257: Database Management -- Ray Larson

Create Table • CREATE TABLE table-name (attr 1 attrtype PRIMARYKEY, attr 2 attrtype, …, attr. N attr-type); • Adds a new table with the specified attributes (and types) to the database. 9/30/1999 SIMS 257: Database Management -- Ray Larson

Access Data Types • • • Numeric (1, 2, 4, 8 bytes, fixed or float) Text (255 max) Memo (64000 max) Date/Time (8 bytes) Currency (8 bytes, 15 digits + 4 digits decimal) Autonumber (4 bytes) Yes/No (1 bit) OLE (limited only by disk space) Hyperlinks (up to 64000 chars) 9/30/1999 SIMS 257: Database Management -- Ray Larson

Access Numeric types • Byte – Stores numbers from 0 to 255 (no fractions). 1 byte • Integer – Stores numbers from – 32, 768 to 32, 767 (no fractions) 2 bytes • Long Integer • Single (Default) – Stores numbers from – 2, 147, 483, 648 to 2, 147, 483, 647 (no fractions). 4 bytes – Stores numbers from -3. 402823 E 38 to – 1. 401298 E– 45 for negative values and from 1. 401298 E– 45 to 3. 402823 E 38 for positive values. 4 bytes • Double – Stores numbers from – 1. 79769313486231 E 308 to – 4. 94065645841247 E– 324 for negative values and from 1. 79769313486231 E 308 to 4. 94065645841247 E– 324 for positive values. 15 8 bytes • Replication ID 9/30/1999 – Globally unique identifier (GUID) SIMS 257: Database Management -- Ray Larson N/A 16 bytes

Oracle Data Types • • CHAR (size) -- max 2000 VARCHAR 2(size) -- up to 4000 DATE DECIMAL, FLOAT, INTEGER(s), SMALLINT, NUMBER(size, d) – All numbers internally in same format… • LONG, LONG RAW, LONG VARCHAR – up to 2 Gb -- only one per table • BLOB, CLOB, NCLOB -- up to 4 Gb • BFILE -- file pointer to binary OS file 9/30/1999 SIMS 257: Database Management -- Ray Larson

Creating a new table from existing tables • Syntax: – SELECT [DISTINCT] attr 1, attr 2, …, attr 3 INTO newtablename FROM rel 1 r 1, rel 2 r 2, … rel 3 r 3 WHERE condition 1 {AND | OR} condition 2 ORDER BY attr 1 [DESC], attr 3 [DESC] 9/30/1999 SIMS 257: Database Management -- Ray Larson

Alter Table • ALTER TABLE table-name ADD COLUMN attr 1 attr-type; • … DROP COLUMN attr 1; • Adds a new column to an existing database table. 9/30/1999 SIMS 257: Database Management -- Ray Larson

INSERT • INSERT INTO table-name (attr 1, attr 4, attr 5, …, attr. K) VALUES (“val 1”, val 4, val 5, …, “val. K”); • Adds a new row(s) to a table. • INSERT INTO table-name (attr 1, attr 4, attr 5, …, attr. K) VALUES SELECT. . . 9/30/1999 SIMS 257: Database Management -- Ray Larson

DELETE • DELETE FROM table-name WHERE <where clause>; • Removes rows from a table. 9/30/1999 SIMS 257: Database Management -- Ray Larson

UPDATE • UPDATE tablename SET attr 1=newval, attr 2 = newval 2 WHERE <where clause>; • changes values in existing rows in a table (those that match the WHERE clause). 9/30/1999 SIMS 257: Database Management -- Ray Larson

DROP Table • DROP TABLE tablename; • Removes a table from the database. 9/30/1999 SIMS 257: Database Management -- Ray Larson

CREATE INDEX • CREATE [ UNIQUE ] INDEX indexname ON tablename (attr 1 [ASC|DESC][, attr 2 [ASC|DESC], . . . ]) [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }] 9/30/1999 SIMS 257: Database Management -- Ray Larson

Assignment 3 • Read Chapter 9 in Kroenke • Complete the GROUP 1 questions • Turn in the SQL answers for questions 9. 19. 32 9/30/1999 SIMS 257: Database Management -- Ray Larson