1 / 99

Chapter 8: SQL-99

Chapter 8: SQL-99. reza@aero.und.edu. Objectives. SQL standard Data Definitions, Constraints, and Schema Changes in SQL2 Queries in SQL (basic and complex SQL Queries) Update operations (delete, insert, and update statements) Views (or Virtual Tables ) in SQL

keefer
Download Presentation

Chapter 8: SQL-99

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8: SQL-99 reza@aero.und.edu www.bookspar.com | Website for students | VTU NOTES

  2. Objectives • SQL standard • Data Definitions, Constraints, and Schema Changes in SQL2 • Queries in SQL (basic and complex SQL Queries) • Update operations (delete, insert, and update statements) • Views (or Virtual Tables ) in SQL • Specifying General Constraints as Assertions www.bookspar.com | Website for students | VTU NOTES

  3. History of SQL • SQL stands for Structured Query Language • Developed by IBM • adopted as standard language for commercial RDBMS: • SQL-86 (or SQL-1) joint effort by ANSI and OSI • SQL-92 (or SQL2) • SQL-99( or SQL3) • CORE • OPTIONAL www.bookspar.com | Website for students | VTU NOTES

  4. SQL (cont.) A comprehensive non-procedural database language package that supports standard • Supports both DDL and DML • Provides facilities to specify security, authorization, and constraints www.bookspar.com | Website for students | VTU NOTES

  5. SQL Data Definition and Data types • SQL uses • Table (or relation) • Row (or tuple) • Column (or attribute) • Data Definition Commands • Create Schema • Create tables • Create Domain • Create view • Alter Table/Schema • Drop Table/Schema www.bookspar.com | Website for students | VTU NOTES

  6. Schema • SQL schema • Used to group tables and related constructs • identified by Schema • Name • Elements • tables • constraints • view, domains • authorization constructs www.bookspar.com | Website for students | VTU NOTES

  7. Create Schema • Schema is created using • CREATE SCHEMA • E.g., • CREATE SCHEMA Company AUTHORIZATION JSMITH • JSMITH is the Schema Owner • Catalog • Named collection of schemas • Information_schema www.bookspar.com | Website for students | VTU NOTES

  8. Create Table Command in SQL • CREATE TABLE • used to specify a new relation (or base table) • CREATE TABLE EMPLOYEE • CREATE TABLE COMPANY.EMPLOYEE • CREATE VIEW • Used to create virtual tables • Attributes are ordered www.bookspar.com | Website for students | VTU NOTES

  9. Create table: example • CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); www.bookspar.com | Website for students | VTU NOTES

  10. More example • Key attributes can be specified via the PRIMARY KEY and UNIQUE phrases CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) ); www.bookspar.com | Website for students | VTU NOTES

  11. SQL: Basic Data Types • Basic Data types • Numeric • Integer, Real • Char string • Fixed length (CHAR(n)) • Varying length (VARCHAR(n) • bit-string, • Fixed (BIT(n)) or varying VARYING(n) • date/time • Boolean (T,F, Unknown) • Timestamps (includes both date and time) www.bookspar.com | Website for students | VTU NOTES

  12. SQL: User Defined Data Type • User defined data type • Domain in SQL • CREATE DOMAIN SSN_TYPE AS CHAR (9); www.bookspar.com | Website for students | VTU NOTES

  13. Specifying Constraints using SQL • Constraints and default values can be specified on each • attributes • tuple • table www.bookspar.com | Website for students | VTU NOTES

  14. Specifying NULL and Default Values • NULLS can be used as attribute values • A NOT NULL constraint can be used to specify that NULL is not permitted • DEFAULT Value www.bookspar.com | Website for students | VTU NOTES

  15. www.bookspar.com | Website for students | VTU NOTES 15

  16. CHECK Clause • CHECK Clause • Used to restrict attribute or domain values • E.g., • To restrict department numbers between integer 1-20 integer • DNUMBER INT NOT NULLCHECK (DNUMBER>0 AND DNUMBER <21) • Check clause can also be used to create the domain • CREATE DOMAIN D_NUM AS INTEGER CHECK (D_NUM>0 AND D_NUM <21) • D_NUM can be used as attribute domain for • DNO • Dnumber • Dum www.bookspar.com | Website for students | VTU NOTES

  17. Specifying Key and referential Integrity Constraints • PRIMARY KEY CLUASE used to specify PK • E.g., PRIMARY KEY (DNUMBER); • E.g., Dnumber INT PRIMARY KEY; • FOREIGN KEY CLUASE used to specify FK • FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT (DUNMBER) • UNIQUE CLAUSE • Used for secondary keys • Figure 8.1 www.bookspar.com | Website for students | VTU NOTES

  18. www.bookspar.com | Website for students | VTU NOTES

  19. Violation of Integrity Constraints (IC) • Update/delete/insertion of tuples may violate referential integrity constraints • The default action is Reject the operation • Schema Designer can specify an alternative action using referential triggered action clause to any FK constraint • Option include • SET DEFAULT • SET NULL • CASACADE • Option must be qualified • ON DELETE or ON UPDATE www.bookspar.com | Website for students | VTU NOTES

  20. Referential Integrity DNO should be Null or should match a PK of DEPT EMPLOEE FNAME MINT LNAME SSN BDATE ADDRESS SEX SUPERSSN DNO DEPT SNAME DNUM MGRSSN MSDATE DEPT_LOC DNUM DLOC www.bookspar.com | Website for students | VTU NOTES 20

  21. www.bookspar.com | Website for students | VTU NOTES

  22. Named constraints • Names to constraints • Used to identify/modify a particular constraint • Works like CONSTANT declaration in Program Languages • Figure 8.2 www.bookspar.com | Website for students | VTU NOTES

  23. www.bookspar.com | Website for students | VTU NOTES 23

  24. Specifying Constraints on Tuples Using CHECK • Used at the end of schema • Applies to individual tuples • Use CHECK for more general constraints • E.g., • CHECK (DEPT_CREATE_DATE < MGRSTARTDATE); www.bookspar.com | Website for students | VTU NOTES

  25. SCHEMA CHANGE COMMANDS IN SQL • THE DROP COMMAND • CASCADE • RESTRICT • Used to drop the named schema elements from database schema • E.g., • DROP SCHEMA COMPANY CASCADE • DROP TABLE DEPENDENT CASCADE • RESTRCIT • Used to drop the schema if it has no elements in it • Delete vs. Drop • Use delete if you want to delete the records but not the table definition www.bookspar.com | Website for students | VTU NOTES

  26. The ALTER TABLE Command • Used to change the definition of base table • E.g., • ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12) • ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT; • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT “344556677” • ALTERTABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE www.bookspar.com | Website for students | VTU NOTES

  27. Basic Queries in SQL • SQL has only one basic statement to retrieve information • SELECT • FROM • WHERE • no relationship to the operation of relational algebra • Important features • supports the notion of multi-set (or Bag) www.bookspar.com | Website for students | VTU NOTES

  28. The Select-From-Where Structure of SQL Queries • General Form: • SELECT <attributes list> • FROM <tables list> • WHERE <condition>; www.bookspar.com | Website for students | VTU NOTES

  29. Some example: Query 0 • Get the birthday and address of the employee(s) whose name is ‘John B. Smith’ • SELECT BDATE, ADDRESS • FROM EMPLOYEE • WHERE FNAME=‘John’ AND MINIT =‘B’ AND LNAME = ‘SMITH’ www.bookspar.com | Website for students | VTU NOTES

  30. www.bookspar.com | Website for students | VTU NOTES

  31. Query 1 • Get the name and address of all employee who work for the ‘Research’ Dept. • SELECT FNAME, LNAME, ADDRESS • FROM EMPLOYEE, DEPARTMENT • WHERE DNAME=‘Research’ ANDDNUMBER =DNO www.bookspar.com | Website for students | VTU NOTES

  32. Query 2 • For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birthrate • SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION = ‘Stafford’ www.bookspar.com | Website for students | VTU NOTES

  33. Ambiguous Attribute Names and Renaming • In SQL, same name can be used • For more than one attribute in different tables • used in recursive queries www.bookspar.com | Website for students | VTU NOTES

  34. Example 1: Same name different tables • To remove ambiguity, we need to qualify the attributes : use ‘.’ separator to qualify the attribute • e.g., suppose LNAME=NAME, and DNO=DNUMBER , DNAME=NAME • SELECT FNAME, EMPLOYEE.NAME, ADDRESS • FROM EMPLOYEE, DEPARTMENT • WHERE DEPARTMENT.DNUMBER =EMPLOYEE.DNUMBER AND DEPARTMENT.NAME = ‘Research www.bookspar.com | Website for students | VTU NOTES

  35. Example 2: Recursive relationships • For each employee, find the employee’s first and last name and the first and last name of her/his immediate supervisor • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME • FROM EMPLOYEE AS E, EMPLOYEE AS S • WHEREE.SUPERSSN=S.SSN www.bookspar.com | Website for students | VTU NOTES

  36. Result of Query www.bookspar.com | Website for students | VTU NOTES

  37. SQL: Unspecified WHERE-Clause • No WHERE-clause means no conditions • No condition means Cross product operations () www.bookspar.com | Website for students | VTU NOTES

  38. Example Q10: No WHERE Clause • Get all combinations of EMPLOYEE.SSN and DEPARTMENT.DNAME • SELECT SSN, DNAME • FROM EMPLOYEE, DEPARTMENT • Get all Employee SSN • SELECT SSN • FROM EMPLOYEE www.bookspar.com | Website for students | VTU NOTES

  39. Example: Use of ‘*’ in select • Use * to get all attributes • Get all employees working for Dept. 5 • SELECT * • FROM EMPLOYEE • WHERE DNO=5 • E.g., 2 • SELECT * • FROM EMPLOYEE, DEPARTMENT; • WHERE DNO=5 and Dname=‘Research’ www.bookspar.com | Website for students | VTU NOTES

  40. Tables as Set in SQL • SQL treats tables as a multi-set (i.e., a set having duplicates) • Why? • Duplicate elimination is an expensive operation (sort and delete) • user may be interested in the result of a query • in case of aggregate function, we do not want to eliminate duplicates • SQL Table with a key is a SET by definition • Why? Because key must be unique • To treat tables as sets use DISTINCT in SELECT statement www.bookspar.com | Website for students | VTU NOTES

  41. Query 11 (ALL) • Retrieve the salary of every employee, • SELECT ALL SALARY • FROM EMPLOYEE www.bookspar.com | Website for students | VTU NOTES

  42. Query 12 (DISTINCT) • Get the salary of every employee USING distinct (set) • SELECT DISTINCT SALARY • FROM EMPLOYEE www.bookspar.com | Website for students | VTU NOTES

  43. SQL and NULL • NULL represents • Value is not known • e.g., an unknown address • Value is not available • E.g., unlisted phone number • Value does not apply • E.g., Unmarried employee has no name for his/her spouse www.bookspar.com | Website for students | VTU NOTES

  44. NULL and Comparison Operators • Comparisons involving NULL • When NULL is involved in comparison, then the result considered to be unknown • Unknown (maybe true or maybe false) • SQL uses 3-valued logic • TRUE, • FALSE, • UNKNOWN • How define the SQL evaluate the 3-valued logical expressions involving • AND • OR • NOT www.bookspar.com | Website for students | VTU NOTES

  45. Truth table for 3-valued logic www.bookspar.com | Website for students | VTU NOTES

  46. In SELECT-PROJECT-JOIN queries, only those combinations of tuples that are evaluated to TRUE are selected More on 3-value www.bookspar.com | Website for students | VTU NOTES

  47. Query involved NULL • Q18: Get the names of all employees who do not have supervisors • SELECT Fname, Lname • FROM EMPLOYEE • WHER Super_ssn ISNULL www.bookspar.com | Website for students | VTU NOTES

  48. Query using Union, Intersection, EXECEPT • Complex SQL queries can be formulated using • UNION • INTERSECTION • EXECEPT www.bookspar.com | Website for students | VTU NOTES

  49. Example of UNION: Q4A • Make a list of Project numbers for projects that involve an employee whose last name is ‘Smith’,either as a worker or as a manger of the department that controls the project www.bookspar.com | Website for students | VTU NOTES

  50. Query 4: Using Union Query • (SELECTDISTINCTPNUMBER • FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME=‘Smith’) • UNION • (SELECT DISTINCT PNUMBER • FROM WORKS_ON, EMPLOYEE , PROJECT • WHERE PNUMBR = PNO AND ESSN=SSN AND LNAME=‘Smith’) www.bookspar.com | Website for students | VTU NOTES

More Related