Structured Query Language

Structured Query Language

Brief History • Developed in early 1970 for relational data model: • Structured English Query Language (SEQUEL) • Implemented with IBM System R • 1987 first ISO standard version • 1992 SQL 2 • 1999 SQL 3 • Object-relational model • 2003:SQL 2003 • SQL/XML • Products: DB2, Oracle, MS SQL, MySQL

Oracle Account • Server: libra.sfsu.edu • Telnet: libra.sfsu.edu • How to use Oracle: • http://www.sfsu.edu/~helpdesk/docs/using/usingoracle.htm • Basic Unix commands: • http://www.sfsu.edu/~helpdesk/docs/using/unixcmd.htm • You may also use MySQL for assignments and project.

Other DBMS • MySQL 5.0: • http://dev.mysql.com/downloads/ • MS SQL Server 2005 Express: • http://msdn.microsoft.com/vstudio/express/sql/download/

Language Overview • Three major components: • Data definition language • Create Table • Data manipulation language • Updating database: • Insert, Delete, Update • Query database: • Select • Data control language (DCL) • Help DBA control the database: • Grant/revoke privileges to access the database, creating procedures, etc. • Interface with database: • Entering command interactively at the DBMS command prompt. • Embedded in a procedural language

Data Definition Language • SQL Identifiers • Character set: A-Z, a-z, 0-9, _ • <= 128 characters • Start with a letter • Cannot contain spaces

SQL Data Types • Boolean • True, False, Unknown (for Null) • Character • Fixed length: CHARACTER(n), CHAR(n), CHAR – default to 1 character • Varying length: • CHARACTER VARYING(n): Maximum length is n • VARCHAR(n) • Numeric • NUMERIC(i,j), DECIMAL(i,j), DEC(i,j) • INTEGER, INT, SMALLINT – up to 32767 • FLOAT, REAL, DOUBLE PRECISION • Date: • DATE • TIME • TIMESTAMP • Large objects: • CHARACTER LARGE OBJECT • BINARY LARGE OBJECT

CREATE TABLE • CREATE TABLE tableName(fields and data type separated by commas); • Ex. • CREATE TABLE employee( eid CHAR(5), ename VARCHAR(40), sex CHAR, salary NUMERIC(9,2), hire_Date DATE);

Integrity Enhancement Feature • Required data: NOT NULL • eid CHAR(5) NOT NULL, • Default value:DEFAULT sex CHAR DEFAULT ‘M’, • Field domain: CHECK(condition) • salary NUMERIC(9,2) CHECK (salary >= 100 AND salary <=10000), • sex CHAR DEFAULT ‘M’ CHECK (sex in (‘M’,’F”)), • PRIMARY KEY • PRIMARY KEY(sid) • PRIMARY KEY(sid, cid) • Unique – allow null value, the PRIMARY KEY constraint does not allow null. • ESSN CHAR(9) UNIQUE

CREATE TABLE employee( eid CHAR(5) PRMARY KEY, ename VARCHAR(40), sex CHAR DEFAULT ‘M’ CHECK (sex in (‘M’,’F”)), salary NUMERIC(9,2), hire_Date DATE);

create table orderdetail • (oid char(3), cid char(5), qty numeric(5,2), • primary key (oid,cid));

ALTER TABLE • ADD/Modify/DROP COLUMN a new field from a table. • Ex. ALTER TABLE employee ADD phone CHAR(8); ALTER TABLE employee Modify phone CHAR(9); ALTER TABLE employee DROP COLUMN Phone;

Adding Constraints with the ALTER TABLE command • Constraints: • PRIMARY KEY, CHECK, UNIQUE • PRIMARY KEY: • ALTER TABLE tablename • ADD CONSTRAINT constraintname • PRIMARY KEY (columnname); • CHECK • ALTER TABLE tablename • ADD CONSTRAINT constraintname • CHECK (criteria);

Examples ALTER TABLE emp ADD CONSTRAINT empkey PRIMARY KEY (empid); ALTER TABLE emp ADD CONSTRAINT validSalary CHECK (salary between 100 AND 20000); Note: Constraints information are stored in table: USER_CONSTRAINTS. You can use the DESCRIBE command to show fields in this table.

Dropping Constraints ALTER TABLE tablename DROP CONSTRAINT constraintname;

Disable/Enable Constraints • ALTER TABLE tablename • DISABLE CONSTRAINT constraintname; • ALTER TABLE tablename • ENABLE CONSTRAINT constraintname;

Creating Table Through SubQuery • CREATE TABLE tableName • AS (Select query) • Ex. • CREATE TABLE newEmp • AS (SELECT empid, ename,salary FROM emp);

Renaming a Table • Rename oldName to newName

Removing a Table • DROP TABLE tableName

SQL Insert Command INSERT INTO tableName VALUES (field values separated by commas); INSERT INTO tableName (Column names separated by commas)VALUES (field values separated by commas); Ex 1. Customer table with CID, CNAME, CITY, RATING. a. INSERT INTO CUSTOMER VALUES (‘C1’, ‘SMITH’, ‘SF’, ‘A’); b. INSERT INTO CUSTOMER (CID, CNAME,RATING) VALUES (‘C1’, ‘SMITH’, ‘A’);

Record with Date Field • Oracle date format: • ‘dd-mmm-yyyy’ • Example: • insert into orders values('O7','c2','s1','10-oct-2007');

Inserting records from an existing table • INSERT INTO stu2 • (select * from student);

SQL Delete Command DELETE FROM tableName [WHERE criteria]; Ex 1. Delete a record from the Customer table. DELETE FROM CUSTOMER WHERE CID = ‘C1’;

SQL Update Command UPDATE tableName SET field = new value [WHERE criteria]; Ex 1. UPDATE CUSTOMER SET RATING = ‘A’ WHERE CID=‘C1’; Ex 2. UPDATE CUSTOMER SET CITY = ‘SF’, RATING = ‘A’ WHERE CID=‘C1’;

Dealing with Null • Null is a key word. We can use Null in the INSERT, UPDATE, and DELETE command. • Use IS NULL (or IS NOT NULL) in a criteria. • Examples: • INSERT INTO emp VALUES (‘e95’,’June’,’f’,NULL,5000); • UPDATE emp SET salary=null where empid=‘e99’; • SELECT * FROM emp WHERE salary IS NULL; • SELECT * FROM emp WHERE salary= NULL (not working);

Structured Query Language