SQL

1
SQL

• Intro DDL

2

• Originally called SEQUEL (for Structured English
  Query Language)
• Designed at IBM Research as an interface for an
  experimental DBMS
• ANSI Standard published in 1986
• Current standard is SQL3 or SQL-99

3

• Implements most of the elements operations that
  are part of the formal definition of the
  relational database.
• Relaxes the definition whenever implementing the
  theoretical definitions are
• Too costly
• Make little practical sense

4
Oracle

• Oracles version is called SQLPlus
• Plus is a tip-off that it diverts from the
  standard
• In fact, might be called SQLMinus since domains
  are not implemented.
• Oracle also has a programming language that
  accompanies its database called PLSQL

5
SQL Terms
Relational Term
SQL term

• Relation
• Tuple
• Attribute

• Table
• Row
• Column

6
Both DDL and DML are part of SQL
7
Important DDL Statementscreate

• database
• domain (though not in Oracle)
• table
• view
• index

8
Important DDL Statementsalter

• database
• domain (though not in Oracle)
• table
• index

9
Important DDL Statements drop

• domain (though not in oracle)
• table
• view
• index

10
Important DML Statements

• insert
• Inserts a row into a table
• update
• Modifies an existing row
• delete
• Deletes a row
• delect
• Combines select, project, join
• rollback
• Rollsback database to a previous state
• commit
• Saves changes to a databae

11
Some Oracle Specific Features

• describe
• Describes the structure of a table
• Transaction buffer
• All SQLPlus transactions for a given session are
  stored in a transaction buffer
• You have access through edit
• Edit invokes vi (or whatever editor is set up
  through your profile)

12
Example

• SQL says that there is a syntax error
• SQLgtedit
• Oracle responds by creating a file called
• afiedt.buf
• Fix and save with vi
• To Run SQLgt_at_afiedt.buf

13
Important Data Types

• char(size)
• Stores fixed length character data with max size
  255
• varchar2(size)
• Stores variable length character data with max
  size 2000
• number(L,D)
• Stores numeric data
• L is number of places to the left of the
  decimal
• D is the number of places to the right of the
  decimal
• int
• Stores integer data
• date
• Stores data in YYYY-MM-DD format

14
Creating the Company Database

• Notice the relationship between Employee and
  Department

15
Symbiotic References

• Employee refers to Department through foreign key
  dno
• Department refers to Employee through foreign key
  mgrssn
• Attempting to create either table will cause a
  referential integrity violation

16
Solution

• Create Employee without dno as FK
• Create Department with all constraints
• Alter Employee constraints
• Script to Create Company Database

17
Loading Tables

• Issue of symbiosis between Employee and
  Department arises here as well
• Department has a foreign key into Employee
• If we try to load a mgrssn before the
  corresponding SSN is loaded, we will violate
  referential integrity
• Issue arises within emploee. Superssn is a
  recursive FK. The supervisor must be loaded
  before his/her employees
• Character data is enclosed within single quotes
• Script to Load Company Database

18
Adding Columns

• SQLgtalter table lttablegt
• add column ltcolumngt
• All columns will have a null value in that column
  after the command is executed
• SQLgtalter table department
• add column members_num int

19
Dropping Columns

• SQLgtalter table lttablegt
• drop column ltcolumngt
• SQLgtalter table department
• drop column members_num

20
Updating Rows

• SQLgtupdate lttablegt
• set ltattr1 v1, , attrnvngt
• where ltconditiongt
• SQLgtupdate employee
• set superssn 123456789
• where dno 4

21
Deleting Rows

• SQLgtdelete from lttablegt
• where ltconditiongt
• SQLgtdelete from department
• where dname research

22
Retrieving Data

• SQLgtselect ltattribute listgt
• from lttable listgt
• where ltconditiongt-
• SQLgtselect lname
• from employee, department
• where dname Research and dnumber dno

23
Script to Drop Company DatabaseNotice the order
of the drops
24
Indexes

• Primary key is always and index
• Can create other indexes as well
• SQLgtcreate index dno_index on
• employee(dno)
• The index created is a B-tree

25
Indexes Continued

• To drop an index
• SQLgtdrop index dno_index
• Can use an index to enforce uniqueness
• SQLgtcreate unique index
• lname_index on employee(lname)
• Since indexes can be secondary, they are not
  necessarily unique

26
Data Dictionary

• All information about an SQL database is stored
  in tables in an SQL database
• Oracle offers pre-defined views of various db
  objects
• user_constraints
• user_tables
• user_catalog
• user_indexes
• user_views