Database Management Fall 2003 SQL Chapter 10

1. Database Management Fall 2003 SQL Chapter 10

2. Structured Query Language Declaritive language vs. procedural Three types of SQL statements Data Manipulation Language (DML) SELECT, INSERT, UPDATE, DELETE Data Definition Language (DDL) CREATE/ALTER/DROP TABLE, VIEW, INDEX Data Control Language (DCL) GRANT, REVOKE SQL

3. SELECT Retrieve data from the database INSERT Add new rows to the database UPDATE Modify data in the database DELETE Remove rows from the database Data Manipulation Commands (DML)

4. We are already familiar with this command: SELECT col1, col2, …FROM table1, table2, … [ WHERE search_condition AND search_condition OR search_condition] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ]] SELECT

5. INSERT INSERT INTO table (col1, col2,…) VALUES (val1, val2, …) UPDATE UPDATE table SET col1 = val1, col2 = val2, … WHERE [condition] DELETE DELETE FROM table WHERE [condition]

6. Enforces integrity constraints Two styles: with or without column names: Style 1 – insert values in order of columns as defined by CREATE TABLE – see object browser. All columns must be included or NULL INSERT INTO item VALUES (21, 'Camoflage Pants', 'C', 'Khaki'); Style 2 – indicate columns explicitly. (Not all columns need appear, but must match up with values) INSERT INTO item (itemno, itemname, itemtype) VALUES (22, 'Hunting Vest', 'C'); INSERT

7. Special case – inserts rows from another table: CREATE TABLE new_item (itemno INT not null, itemname CHAR(30) null, itemtype CHAR(1) null, itemcolor CHAR(10) null); INSERT INTO new_item VALUES (23, 'Climbing Rope', NULL, 'White'); INSERT INTO item SELECT * FROM new_item; INSERT INTO item (itemno, itemname, itemcolor) SELECT itemno, itemname, itemcolor FROM new_item WHERE itemcolor = ‘White’; INSERT-SELECT

8. Modify data in a table Enforces integrity, primary key constraints UPDATE locks rows during update Without a WHERE clause, updates ALL rows! Give all employees a 10% raise: UPDATE emp SET empsalary = 1.10*(empsalary); Move all employees in Personnel to Marketing: UPDATE emp SET deptname = 'Marketing' WHERE deptname = 'Personnel'; UPDATE

9. Remove entire rows from table Again, without WHERE clause, deletes ALL rows! Enforces integrity constraints DELETE FROM new_item; DELETE FROM item WHERE itemno = 23; DELETE

10. DELETE FROM emp; Safest: Logs the delete transactions in a log file for undo Does not delete the table Does not reclaim space TRUNCATE TABLE emp; Faster than delete, does not log transactions Does not delete the table Reclaims space DROP TABLE emp; Fast, does not log deletions Deletes the table as well as the rows Reclaims all space Three options for deleting a table

11. CREATE TABLE VIEW INDEX TRIGGER, FUNCTION, PROCEDURE ALTER TABLE VIEW TRIGGER, FUNCTION, PROCEDURE DROP TABLE VIEW INDEX TRIGGER, FUNCTION, PROCEDURE Data Definition Language (DDL)

12. Creates a base table CREATE TABLE table_name (col1_name col1_type DEFAULT constraints, col2_name col2_type DEFAULT constraints, …, PRIMARY KEY (pk_col1, pk_col2, …), FOREIGN KEY (fk_col1, fk_col2, …) REFERENCES ref_table (ref_col1, ref_col2,…), CHECK (check conditions)); CREATE TABLE

13. CREATE TABLE nation ( natcode CHAR(3), natname VARCHAR(20), exchrate DECIMAL(9,5), PRIMARY KEY (natcode)); CREATE TABLE stock ( stkcode CHAR(3), stkfirm VARCHAR(20), stkprice DECIMAL(6,2), stkqty DECIMAL(8), stkdiv DECIMAL(5,2), stkpe DECIMAL(5), natcode CHAR(3), PRIMARY KEY(stkcode), CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation); Creating tables example

14. CREATE TABLE stock (stkcode char(3) NOT NULL, stkfirm char(20), stkprice decimal(6,2) DEFAULT 0, stkqty decimal(8) CHECK (stkqty > 0), stkdiv decimal(5,2), stkpe decimal(5), natcode char(3), PRIMARY KEY (stkcode), FOREIGN KEY (natcode) REFERENCES nation(natcode)); CREATE TABLE Example 2

15. CREATE TABLE stock (stkcode char(3) PRIMARY KEY, stkfirm char(20), stkprice decimal(6,2), stkqty decimal(8) CHECK (stkqty > 0), stkdiv decimal(5,2), stkpe decimal(5), natcode char(3) FOREIGN KEY REFERENCES nation(natcode)); Constraints can appear in the column definitions CREATE TABLE Example 3

16. Primary key CONSTRAINT pk_stock PRIMARY KEY(stkcode) Foreign key CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation Unique CONSTRAINT unq_stock_stkname UNIQUE(stkname) Check CONSTRAINT item_color_cons CHECK (itemcolor IN ('White', 'Brown', 'Khaki')) Constraints

17. The set of all possible (and meaningful!) values that an attribute can take on. Age >= State code one of AK, AL, … WY ShipDate >= OrderDate CHECK Constraints

18. MS SQLServer: A column can have any number of CHECK constraints The condition can include multiple logical expressions combined with AND and OR. Multiple CHECK constraints for a column are validated in the order created. The search condition must evaluate to a Boolean expression and cannot reference another table. CHECK Constraints

19. CREATE TABLE item (itemno INT NOT NULL, itemname CHAR(30) NULL, itemtype CHAR(1) NULL, itemcolor CHAR(10) NULL CONSTRAINT chk_item_color CHECK (itemcolor IN ('White', 'Brown', 'Khaki'))); INSERT INTO item VALUES (24, 'Climbing Rope', 'N', 'Yellow'); Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with COLUMN CHECK constraint 'chk_item_color'. The conflict occurred in database 'markf', table 'item', column 'itemcolor'. The statement has been terminated. Creating a CHECK constraint

20. DROP – Deletes the table and all it’s rows Cannot be undone! DROP TABLE table_name; DROP TABLE

21. Add or drop a column or constraint Change a column type ALTER TABLE table_name ALTER COLUMN column_name DROP/ADD column or constraint; ALTER TABLE stock_tmp ALTER COLUMN stkfirm char(30); ALTER TABLE stock_tmp ADD stkyield decimal(4,2); ALTER TABLE

22. Can’t alter a primary key or a column referenced by a foreign key or index. Data type changes have to be compatible can’t be smaller can’t change to NOT NULL if there are already values Can’t drop a column if it is part of a constraint Altering Columns – some rules

23. An imaginary table constructed by the DBMS when required – not a base table. Only the definition of the view is stored, not the result Usually can’t be updated except in special circumstances CREATE VIEW view_name (col1, col2, …) AS select statement; Views - virtual tables

24. CREATE VIEW stkvalue (nation, firm, price, qty, exchrate, value) AS SELECT natname, stkfirm, stkprice, stkqty, exchrate, stkprice*stkqty*exchrate FROM stock, nation WHERE stock.natcode = nation.natcode; Create View example

25. DROP VIEW deletes a view ALTER VIEW Only replaces the SELECT statement Usually DROP VIEW, then CREATE VIEW are used instead DROP/ALTER VIEW

26. What is an index? A database object associated with a table A sorted list for fast lookup of rows (remember, RDBMS rows are not ordered) Similar to the index in a textbook CREATE INDEX index_name (col1, col2,…) ON table or view; CREATE INDEX color_idx (itemcolor) ON item; CREATE INDEX

27. An index has data for one or more columns in a table and pointers to the locations of the rows with those column values. Indexes reduce disk accesses and improve SELECT performance Indexes slow down INSERT and UPDATE due to maintenance overhead Indexing

28. Special index that enforces uniqueness on the column(s). The database automatically creates an unique index for every primary key. Unique constraint creates a unique index. CREATE UNIQUE INDEX index_name (col1, col2,…) ON table or view; UNIQUE INDEX

29. Sequence set is a single level index with pointers to records Index set is a tree-structured index to the sequence set B-tree index

30. DROP INDEX Can you guess? There is no ALTER INDEX command – simply drop and recreate. DROP/ALTER INDEX

31. Data Control Language (DCL) Data is a valuable resource Access should be controlled SQL security procedures CREATE VIEW Authorization commands Security

32. Based on privilege concept You cannot execute an operation without the appropriate privilege DBA has all privileges Authorization

33. Defines a user’s privileges GRANT privileges ON object TO users [WITH GRANT OPTION]; An object is a base table or view The keyword privilege can be ALL PRIVILEGES or chosen from SELECT UPDATE DELETE INSERT Privileges can be granted to everybody using the keyword PUBLIC or to selected users by specifying their user identifier GRANT

34. The UPDATE privilege can specify particular columns in a base table or view WITH GRANT OPTION Permits a user to pass privileges to another user GRANT

35. Give Alice all rights to the STOCK table. GRANT ALL PRIVILEGES ON stock TO alice; Permit the accounting staff, Todd and Nancy, to update the price of a stock. GRANT UPDATE (stkprice) ON stock TO todd, nancy; Give all staff the privilege to select rows from ITEM. GRANT SELECT ON item TO PUBLIC; Give Alice all rights to view STK. GRANT SELECT, UPDATE, DELETE, INSERT ON stk TO alice; Using GRANT

36. Removes privileges Format REVOKE privileges ON object FROM users; Cascading REVOKE Reverses use of the WITH GRANT OPTION When a user’s privileges are revoked, all users whose privileges were established using WITH GRANT OPTION are also revoked REVOKE

37. Remove Sophie's ability to select from ITEM. REVOKE SELECT ON item FROM sophie; Nancy is no longer permitted to update stock prices. REVOKE UPDATE ON stock FROM nancy; Using REVOKE

38. A relational database containing definitions of base tables, view, etc. Also known as data dictionary or metadata Can be interrogated using SQL Called systems tables rather than base tables Key tables are sysobjects syscolumns systypes sysindexes sysusers The catalog

39. Find how many columns belong to each table: SELECT sysobjects.name 'Table', COUNT(syscolumns.name) 'Number of Columns' FROM sysobjects, syscolumns WHERE sysobjects.id = syscolumns.id GROUP BY sysobjects.name; What columns in what tables store dates? SELECT sysobjects.name 'Table', syscolumns.name 'Column' FROM sysobjects, syscolumns, systypes WHERE sysobjects.id = syscolumns.id AND syscolumns.xtype = systypes.xtype AND systypes.name = 'datetime' Interrogating the catalog

40. GRANT and REVOKE DML privileges Control access to data GRANT privileges ON object TO user; GRANT SELECT ON emp (deptname) TO markf; GRANT ALL ON sale TO markf; WITH GRANT OPTION grants superuser privileges for the object to the grantee GRANT SELECT ON emp (deptname) TO markf WITH GRANT OPTION; Data Control Language (DCL)

41. REVOKE removes privileges from a user REVOKE privilege ON object FROM user; Oracle Roles System privileges GRANT CREATE TABLE TO user; Data Control Language (DCL)