SQL B term 2004 lecture 14 Dec 4

SQL B term 2004: lecture 14 Dec 4, 2003 Murali Mani

Inserting tuples INSERT INTO Student VALUES (6, ‘Emily’, ‘ 324 FL’, NULL); INSERT INTO Student (s. Number, s. Name) VALUES (6, ‘Emily’); INSERT INTO Student (s. Number, s. Name) SELECT p. Number, p. Name FROM Professor; Dec 4, 2003 Murali Mani

Delete and Update l Deleting tuples DELETE FROM Student WHERE s. Number=‘ 6’; l Updating tuples UPDATE Student SET professor=‘ER’ WHERE s. Numer=‘ 6’ Dec 4, 2003 Murali Mani

SQL DDL (Data Definition Language) l Creating table CREATE TABLE Student ( s. Number integer, s. Name varchar (20), address varchar (30), professor char (2), gender char (1) DEFAULT ‘? ’); Dec 4, 2003 Murali Mani

SQL DDL l Dropping a table DROP TABLE Student; l Altering a table ALTER TABLE Student DROP gender; ALTER TABLE Student ADD gender char (1) DEFAULT ‘? ’; Dec 4, 2003 Murali Mani

Indexes l l Creation of indexes is NOT part of SQL However “common syntax” is used. CREATE INDEX <nameof. Index> ON <table. Name> (a 1, a 2, …, an) For eg: CREATE INDEX Name. Dept. Index 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 <nameof. Index> Dec 4, 2003 Murali Mani

Indexes: Illustration l l Indexes make queries faster, they might make modifications longer. As an example, let us consider Student (s. Number, s. Name, dept) Let us assume a disk block can hold 2 data records. Let us assume there are 3 data records for each s. Name, and there are 9 data records in total. Dec 4, 2003 Murali Mani

Indexes: Illustration Dave Greg Amanda Dec 4, 2003 Empty Murali Mani

Indexes: Illustration l l SELECT * FROM Student where student=“Amanda” – 4 disk accesses Inserting a new student with name=“Amanda” – 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. Dec 4, 2003 Murali Mani

Views l View is a virtual relation l l Convenience: Queries on base relations might be “complex” Independence: “base tables” may change, but still queries using views need not change. CREATE VIEW <view. Name> as <query> CREATE VIEW student. Professor (student, professor) AS SELECT s. Name, p. Name FROM Student, Professor WHERE Student. professor = Professor. p. Name; DROP VIEW <view. Name> Dec 4, 2003 Murali Mani

Querying Views l l Views can be queried on just like a relation Some views can be updated, not all. l l Dec 4, 2003 “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: l Updatable views are specified using SELECT (not SELECT DISTINCT) l FROM should specify one relation R l WHERE clause cannot involve R in subquery. l SELECT clause must include enough attributes so that filling out the rest of the attributes as NULL in R does not violate any constraint. Murali Mani