SQL

1
SQL

• B term 2004 lecture 14

2
Inserting tuples

• INSERT INTO Student VALUES
• (6, Emily, 324 FL, NULL)
• INSERT INTO Student (sNumber, sName)
• VALUES (6, Emily)
• INSERT INTO Student (sNumber, sName)
• SELECT pNumber, pName
• FROM Professor

3
Delete and Update

• Deleting tuples
• DELETE FROM Student
• WHERE sNumber6
• Updating tuples
• UPDATE Student SET professorER
• WHERE sNumer6

4
SQL DDL (Data Definition Language)

• Creating table
• CREATE TABLE Student (
• sNumber integer,
• sName varchar (20),
• address varchar (30),
• professor char (2),
• gender char (1) DEFAULT ?)

5
SQL DDL

• Dropping a table
• DROP TABLE Student
• Altering a table
• ALTER TABLE Student DROP gender
• ALTER TABLE Student ADD gender char (1)
• ALTER TABLE Student ADD gender char (1) DEFAULT
  ?

6
Indexes

• Creation of indexes is NOT part of SQL
• However common syntax is used.
• CREATE INDEX ltnameofIndexgt ON lttableNamegt (a1,
  a2, , an)
• For eg CREATE INDEX NameDeptIndex ON Student
  (sname, dept)
• In oracle, you can see the indexes are stored in
  a table user_ind_columns
• SELECT FROM user_ind_columns
• DROP INDEX ltnameofIndexgt

7
Indexes Illustration

• Indexes make queries faster, they might make
  modifications longer.
• As an example, let us consider Student (sNumber,
  sName, dept)
• Let us assume a disk block can hold 2 data
  records.
• Let us assume there are 3 data records for each
  sName, and there are 9 data records in total.

8
Indexes Illustration
Dave
Greg
Amanda
Empty
9
Indexes Illustration

• SELECT FROM Student where studentAmanda 4
  disk accesses
• Inserting a new student with nameAmanda 4
  disk accesses.
• Note assumption given Amanda, we know the index
  block with 0 disk access.
• We know the unfull data block with 0 disk
  access.

10
Views

• View is a virtual relation
• Convenience Queries on base relations might be
  complex
• Independence base tables may change, but
  still queries using views need not change.
• CREATE VIEW ltviewNamegt as ltquerygt
• CREATE VIEW studentProfessor (student,
  professor) AS
• SELECT sName, pName
• FROM Student, Professor
• WHERE Student.professor Professor.pName
• DROP VIEW ltviewNamegt

11
Querying Views

• Views can be queried on just like a relation
• Some views can be updated, not all.
• Idea View must be columns of one table such
  that updates to the view can be mapped to updates
  on the base table
• Enforced in SQL as
• Updatable views are specified using SELECT (not
  SELECT DISTINCT)
• FROM should specify one relation R
• WHERE clause cannot involve R in subquery.
• SELECT clause must include enough attributes so
  that filling out the rest of the attributes as
  NULL in R does not violate any constraint.