Structured Query Language

1
Structured Query Language
2
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
• 2003SQL 2003
• SQL/XML
• Products DB2, Oracle, MS SQL, MySQL

3
Oracle Account

• Server libra.sfsu.edu
• Telnet libra.sfsu.edu
• How to use Oracle
• http//www.sfsu.edu/helpdesk/docs/using/usingorac
  le.htm
• Basic Unix commands
• http//www.sfsu.edu/helpdesk/docs/using/unixcmd.h
  tm
• You may also use MySQL for assignments and
  project.

4
Other DBMS

• MySQL 5.0
• http//dev.mysql.com/downloads/
• MS SQL Server 2005 Express
• http//msdn.microsoft.com/vstudio/express/sql/down
  load/

5
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

6
Data Definition Language

• SQL Identifiers
• Character set A-Z, a-z, 0-9, _
• lt 128 characters
• Start with a letter
• Cannot contain spaces

7
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

8
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)

9
Integrity Enhancement Feature

• Required data NOT NULL
• eid CHAR(5) NOT NULL,
• Default valueDEFAULT
• sex CHAR DEFAULT M,
• Field domain CHECK(condition)
• salary NUMERIC(9,2) CHECK (salary gt 100 AND
  salary lt10000),
• 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

10

• 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)

11

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

12
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

13
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)

14
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.
15
Dropping Constraints

• ALTER TABLE tablename
• DROP CONSTRAINT constraintname

16
Disable/Enable Constraints

• ALTER TABLE tablename
• DISABLE CONSTRAINT constraintname
• ALTER TABLE tablename
• ENABLE CONSTRAINT constraintname

17
Creating Table Through SubQuery

• CREATE TABLE tableName
• AS (Select query)
• Ex.
• CREATE TABLE newEmp
• AS (SELECT empid, ename,salary FROM emp)

18
Renaming a Table

• Rename oldName to newName

19
Removing a Table

• DROP TABLE tableName

20
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)
21
Record with Date Field

• Oracle date format
• dd-mmm-yyyy
• Example
• insert into orders values('O7','c2','s1','10-oct-2
  007')

22
Inserting records from an existing table

• INSERT INTO stu2
• (select from student)

23
SQL Delete Command
DELETE FROM tableName WHERE criteria Ex 1.
Delete a record from the Customer table. DELETE
FROM CUSTOMER WHERE CID C1
24
SQL Update Command
UPDATE tableName SET field new value WHERE
criteria Ex 1. UPDATE CUSTOMER SET RATING
A WHERE CIDC1 Ex 2. UPDATE CUSTOMER SET
CITY SF, RATING A WHERE CIDC1
25
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 salarynull where empide99
• SELECT FROM emp WHERE salary IS NULL
• SELECT FROM emp WHERE salary NULL (not
  working)