850 likes | 875 Views
Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries). Outline Introduction Data Definition Language CREATE TABLE
E N D
Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries)
Outline • Introduction • Data Definition Language • CREATE TABLE • ALTER TABLE • DROP TABLE • Data Manipulation Language • SELECT Statement • INSERT Statement • DELETE Statement • UPDATE Statement Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 1
Introduction • SQL: Structured Query Language. • Standard for commercial relational DBMSs, a joint effort by ANSI • & ISO. • Benefits of a standardized relational language: • Reduced training costs. • Productivity. • Application portability. • Application longevity. • Reduced dependence on a single vendor. • Cross-system communication. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 2
“Declarative” Versus “Procedural” • Most DBMSs provide the user with a high-level declarative • language interface. • The user has just to specify “what” results he/she would like, • leaving the “how” to the DBMS. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 3
Schema and Catalog Concepts in SQL • The concept of an SQL Schema was incorporated to group together • tables & other constructs that belong to the same database • application. • Schema: the structure that contains descriptions of objects created • by the user (tables, constraints, views, domains, character sets, • triggers, rules). • Catalog: a set of schemas that constitute the description of a • database. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 4
Types of SQL Statements • Data Definition Language (DDL): • Commands that define a database. • E.g. CREATE, ALTER, DROP, ...etc. • Data manipulation language (DML): • Commands that maintain and query a database. • E.g. SELECT, INSERT, UPDATE, DELETE. • Data Control Language (DCL): • Commands that control a database, including administering • privileges and committing data. • E.g. CONNECT, GRANT, REVOKE, ...etc. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 5
CREATE Statements • Major CREATE statements: • CREATE SCHEMA: • Defines a portion of the database owned by a particular user. • CREATE SCHEMA COMPANY AUTHORIZATION JSMITH. • CREATE TABLE: • Defines a table and its columns. • CREATE VIEW: • Defines a logical table from one or more views. • Other CREATE statements: DOMAIN , CHARACTER SET, • COLLATION, TRANSLATION, ASSERTION. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 6
CREATE TABLE Construct • CREATE TABLE name ( • column-1 TYPE [DEFAULT value] [constraints], • column-2 TYPE [DEFAULT value] [constraints], • : • column-n TYPE [DEFAULT value] [constraints], • [table-constraints]); • Where: • name: table name. • column-1 to column-n: column names. • TYPE: data type. • [DEFAULT value]: optional default value. • [constraints]: optional constraints, can be specified at column • level or at table level. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 7
SQL Data Types (From Oracle) • CHAR(n): a fixed-length string n characters long • (default 1, max 255 character). • VARCHAR2(n): a variable string up to n character long (max 2000). • LONG: a variable length character string up to 2 GB long. • NUMBER: a signed floating point number up to 38 digits in length. • NUMBER(n,d): a signed number n digits long including d decimal • places. • NUMBER(n): a signed integer n digits long (max 38 digits). • DATE: a valid date and time. • RAW: binary data up to 64K in size (image data for example). • LONG RAW: binary data up to 2 GB in size. • CLOB: character large object up to 4GB in size. • BLOB: binary large object up to 4GB in size. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 8
CREATE DOMAIN Construct • A domain can be declared, and the domain name is used with the • attribute specification. • This makes it easier to change the data type for a domain that is • used by numerous attributes in a schema, and improves schema • readability. • Examples: • CREATE DOMAIN SSN_TYPE AS CHAR(9) • CREATE DOMAIN DNUM_TYPE AS NUMBER(2) CHECK • (DNUM>0 AND DNUM<21) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 9
Constraints • There are essentially five types of constraints: • NOT NULL constraints. • UNIQUE value constraints. • PRIMARY KEY constraints. • FOREIGN KEY constraints. • CHECK constraints. • The syntax of these constraints varies according to whether they are • specified at column level or at table level. • Any constraints which apply to multiple columns must be defined as • table level constraints. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 10
Constraints • The syntax for each of the column level constraints is: • NOT NULL • UNIQUE • PRIMARY KEY • REFERENCES table (key) • CHECK (condition) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 11
Constraints • The syntax for each of the table level constraints is: • UNIQUE (column-list) • [CONSTRAINT name] PRIMARY KEY (column-list) • [CONSTRAINT name] FOREIGN KEY (column-list) • REFERENCES table (column-list) • CHECK (condition) • An additional ON DELETE clause may be applied to a foreign key • constraint: • ON DELETE CASCADE causes all referencing child records to • be deleted whenever a parent record is deleted. • ON DELETE SET NULL causes all referencing child records to • be set to NULL whenever a parent record is deleted. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 12
To improve access speed • Steps in Table Creation • Identify data types for attributes. • Determine default values. • Identify columns that can and cannot be null. • Identify columns that must be unique (candidate keys). • Identify primary key - foreign key mates. • Identify constraints on columns (domain specifications). • Create the table and associated indexes. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 13
CREATE TABLE Example • CREATE TABLE EMPLOYEE ( • FNAME VARCHAR2(15) NOT NULL, • MINIT CHAR, • LNAME VARCHAR2(15) NOT NULL, • SSN CHAR(9), • BDATE DATE, • ADDRESS VARCHAR2(30), • SEX CHAR CHECK (LOWER(SEX) IN ('m', 'f')), • SALARY NUMBER(10,2), • SUPERSSN CHAR(9), • DNO NUMBER(2) NOT NULL, • CONSTRAINT EMP_PKEY PRIMARY KEY (SSN), • CONSTRAINT EMP_FKEY1 FOREIGN KEY (SUPERSSN) REFERENCES • EMPLOYEE (SSN), • CONSTRAINT EMP_FKEY2 FOREIGN KEY (DNO) REFERENCES • DEPARTMENT (DNUMBER)) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 14
ALTER TABLE Construct • Tables can be changed in a number of ways using the SQL • statement ALTER TABLE. • Possible actions include: • Adding or dropping a column. • Adding or dropping a constraint. • Changing a column definition. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 15
ALTER TABLE Construct • To add two columns to the EMPLOYEE table: • ALTER TABLE EMPLOYEE ADD ( • TITLE CHAR(3) CHECK(LOWER(TITLE) IN ('mr', 'mrs', 'ms')), • TEL_NO CHAR(7)) • To add a constraint to the DEPARTMENT table: • ALTER TABLE DEPARTMENT ADD ( • CONSTRAINT D_FKEY FOREIGN KEY (MGRSSN) • REFERENCES EMPLOYEE (SSN) ON DELETE CASCADE) • To increase the width of LNAME column: • ALTER TABLE EMPLOYEE MODIFY ( • LNAME VARCHAR2(20)) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 16
ALTER TABLE Construct • To add a NOT NULL constraint to the ADDRESS column: • ALTER TABLE EMPLOYEE MODIFY ( • ADDRESS NOT NULL) • To drop the attribute ADDRESS from the EMPLOYEE table: • ALTER TABLE EMPLOYEE DROP (ADDRESS) CASCADE • CONSTRAINTS • To drop a constraint named D_FKEY from DEPARTMENT table: • ALTER TABLE DEPARTMENT DROP CONSTRAINT D_FKEY Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 17
ALTER TABLE Construct • To drop an existing default clause: • ALTER TABLE DEPARTMENT ALTER MGRSSN DROP • DEFAULT • To define a new default clause: • ALTER TABLE DEPARTMENT ALTER MGRSSN SET • DEFAULT '333445555' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 18
DROP TABLE Construct • The DROP TABLE command deletes all information about the • dropped relation from the database. • Syntax: • DROP TABLE name [CASCADE CONSTRAINTS] • Where: • name: is the name of the table to be dropped. • CASCADE CONSTRAINTS: drops all referential integrity • constraints which refer to keys in the dropped table. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 19
Basic Queries in SQL • SQL has one basic statement for retrieving data from a database, • the SELECT statement. • This is not the same as the SELECT operation of the relational • algebra. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 20
Mandatory • The SQL SELECT Statement • It consists of the following basic clauses: • SELECTspecifies the columns which are to be retrieved • FROMspecifies the tables from which data is to be retrieved • WHEREdefines any selection criteria required • GROUP BYspecifies how output is to be grouped & summarized • HAVINGdetermines which group summaries to include in the output • ORDER BYdefines how the output will be stored Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 21
The SELECT-FROM-WHERE Structure • The basic form of the SELECT statement is: • SELECT [DISTINCT] item-list • FROM table-list • WHERE condition • Where: • item-list: a comma-separated list of items to be retrieved. • (database attributes, functions, fixed text, ...etc.) • table-list: a comma-separated list of tables from which the • attributes are to be retrieved. • condition: a conditional (Boolean) expression that identifies the • tuples to be retrieved by the query. • [DISTINCT]: ensures only unique tuples will be returned. • The result of an SQL query is a relation (can be a multiset) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 22
The SELECT-FROM-WHERE Structure • Query: retrieve the birthdate 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' • Is it similar to a tuple relational expression? Any differences? • SELECT Statement Relational Algebra • SELECT clause project operation • FROM clause cartesian product operation • WHERE clause select condition Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 23
The SELECT-FROM-WHERE Structure • Query: retrieve the name and address of all employees who • work for the ‘Research’ department. • Query: for every project located in ‘Stafford’, list the project • number, the controlling department number, and the department • manager’s last name, address, and birthdate. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 24
Ambiguous Attribute Names, Aliasing, and Tuple Variables • In SQL if the same attribute name is used in more than one • relation, the attribute names must be qualified. • Assume that DNO attribute of the EMPLOYEE relation was • called DNUMBER. • Query: retrieve the name and address of all employees who • work for the ‘Research’ department. • SELECT FNAME, LNAME, ADDRESS • FROM EMPLOYEE, DEPARTMENT • WHERE DNAME='Research' AND • EMPLOYEE.DNUMBER=DEPARTMENT.DNUMBER Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 25
Ambiguous Attribute Names, Aliasing, and Tuple Variables • Ambiguity also arise in the case of queries that refer to the same • relation twice, as in the following example. • Query: for each employee, retrieve the employee’s first and last • name and the first and last name of his or her immediate supervisor. • SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME • FROM EMPLOYEE E, EMPLOYEE S • WHERE E.SUPERSSN=S.SSN Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 26
Unspecified WHERE Clause • Query: select all EMPLOYEE SSNs. • SELECT SSN • FROM EMPLOYEE • Query: select all combinations of EMPLOYEE SSN and • DEPARTMENT DNAME. SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 27
The Use of Asterisk • Query: retrieve all the attribute values of any EMPLOYEE who • work in DEPARTMENT 5. • SELECT * • FROM EMPLOYEE • WHERE DNO =5 • Query: retrieve all the attributes of an EMPLOYEE and all the • attributes of the DEPARTMENT in which he or she works for • every employee of the ‘Research’ department. SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 28
A set • Tables as Sets in SQL • Query: retrieve the salary of every employee. • SELECT SALARY • FROM EMPLOYEE • Query: retrieve all distinct salary values. • SELECT DISTINCT SALARY • FROM EMPLOYEE Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 29
Union compatible • The UNION, INTERSECT, & MINUS SET OPERATORS • The relations resulting from these set operations are sets of tuples; • that is, duplicate tuples are eliminated from the result. • Query: make a list of all project numbers for projects that involve • an employee whose last name is ‘Smith’, either as a worker or as • a manager of the department that controls the project. • SELECT DISTINCT PNUMBER • FROM PROJECT, DEPARTMENT, EMPLOYEE • WHERE DNUM=DNUMBER AND MGRSSN=SSN AND • LNAME='Smith' • UNION • SELECT DISTINCT PNUMBER • FROM PROJECT, WORKS_ON, EMPLOYEE • WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 30
Conditional Operators • Main conditional operators include: =, <>, >, <, >=, <=. • Other conditional operators: • IN: tests if an item value appears in a specified list of values. • BETWEEN: tests if an item value lies (inclusively) between • two specified values. • IS NULL: tests if an item has a Null value. Null is neither zero • (in a numeric item), nor spaces (in a character item). • LIKE: tests if an item value matches a string containing • wildcard characters. The wildcard characters are: • %: meaning zero or more occurrences of any character. • _: meaning a single occurrence of any characters. • The keyword NOT can be used in conjunction with all the above • operators to revise the test. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 31
Conditional Operators • Query: retrieve the names of all employees whose salary is not • (20000, 30000, or 40000). • Query: retrieve all employees in department 5 whose salary is • between $30,000 and $40,000. SELECT FNAME, LNAME FROM EMPLOYEE WHERE SALARY NOT IN (20000, 30000, 40000) SELECT * FROM EMPLOYEE WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5 Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 32
Conditional Operators • Query: retrieve the names of all employees who do not have • supervisors. • Query: retrieve all employees whose address is in Houston, Texas. SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston, TX%' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 33
Arithmetic Operations • The standard arithmetic operations (+, -, *, /) can be applied to • numeric values or attributes with numeric domains. • Query: show the resulting salaries if every employee working on the • ‘ProductX’ project is given 10 percent raise. • SELECT FNAME, LNAME, 1.1*SALARY INCREASED_SAL • FROM EMPLOYEE, WORKS_ON, PROJECT • WHERE SSN=ESSN AND PNO=PNUMBER AND • PNAME='ProductX' Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 34
Ordering of Query Results • SQL allows the user to order the tuples in the result of a query by • the values of one or more attributes, using the ORDER BY clause. • Query: retrieve a list of employees and the projects they are working • on, ordered by department and, within each department, ordered • alphabetically by last name, first name. • SELECT DNAME, LNAME, FNAME, PNAME • FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT • WHERE DNUMBER=DNO AND SSN=ESSN AND • PNO=PNUMBER • ORDER BY DNAME, LNAME, FNAME • The default order is in ascending order. • ORDER BY DNAME DESC, LNAME ASC, FNAME ASC Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 35
The Three Valued Logic • Any comparison with Null returns unknown, for example, 5 < Null. • Three- valued logic using the truth value unknown: • OR: • (unknown OR true) = true. • (unknown OR false) = unknown. • (unknown OR unknown) = unknown. • AND: • (unknown AND true) = unknown. • (unknown AND false) = false. • (unknown AND unknown) = unknown. • NOT: • (NOT unknown) = unknown. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 36
Nested Queries • Some queries require that existing values in the database be fetched • and then used in a comparison condition. • Such queries can be conveniently formulated by using nested • queries. • Nested queries: complete select-from-where blocks usually within • the WHERE clause of another query. That other query is called the • outer query. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 37
Nested Queries • Query: make a list of all project numbers for projects that involve • an employee whose last name is ‘Smith’, either as a worker or as • a manager of the department that controls the project. • SELECT DISTINCT PNUMBER • FROM PROJECT • WHERE PNUMBER IN (SELECT PNUMBER • FROM PROJECT, EMPLOYEE, • DEPARTMENT • WHERE DNUM=DNUMBER • AND MGRSSN=SSN • AND LNAME='Smith') • OR • PNUMBER IN (SELECT PNO • FROM WORKS_ON, EMPLOYEE • WHERE ESSN=SSN • AND LNAME='Smith') Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 38
Nested Queries • SQL allows the use of tuples of values in comparisons by placing • them within parentheses. • Example: • SELECT DISTINCT ESSN • FROM WORKS_ON • WHERE (PNO, HOURS) IN (SELECT PNO, HOURS • FROM WORKS_ON • WHERE ESSN='123456789') Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 39
Nested Queries • In addition to the IN operator, a number of other comparison • operators can be used to compare a single value (attribute) to a set • or multiset. • The keywords ANY or ALL can be combined with the following • operators: >, >=, <, <=, <>. (= ANY is equivalent to IN) • Query: list the names of employees whose salary is greater than the • the salary of all the employees in department 5. SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 40
Correlated Nested Queries • Whenever a condition in the WHERE clause of a nested query • references some attribute of a relation declared in the outer query, • the two queries are said to be correlated. • The result of a correlated nested query is different for each tuple (or • combination of tuples) of the relation(s) the outer query. • Query: retrieve the name of each employee who has a dependent • with the same name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE E.FNAME=DEPENDENT_NAME AND E.SEX=SEX) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 41
Correlated Nested Queries • In general, a query with nested select-from-where blocks and using • the = or IN comparison operators can always be expressed as a • single block query. • Query: retrieve the name of each employee who has a dependent • with the same name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D WHERE E.SSN=D.ESSN AND E.SEX=D.SEX AND E.FNAME=D.DEPENDENT_NAME Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 42
The EXISTS Function in SQL • This function checks whether the result of a correlated nested query • is empty (contains no tuples) or not. • Query: retrieve the name of each employee who has a dependent • with the same first name and same sex as the employee. SELECT E.FNAME, E.LNAME FROM EMPLOYEE E WHERE EXISTS (SELECT * FROM DEPENDENT WHERE E.SSN=ESSN AND E.SEX=SEX AND E.FNAME=DEPENDENT_NAME) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 43
The EXISTS Function in SQL • Query: retrieve the names of employees who have no dependents. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 44
The EXISTS Function in SQL • Query: list the names of managers who have at least one dependent. SELECT FNAME, LNAME FROM EMPLOYEE WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN) AND EXISTS (SELECT * FROM DEPARTMENT WHERE SSN=MGRSSN) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 45
The EXISTS Function in SQL • Query: retrieve the name of each employee who works on all the • projects controlled by department number 5. SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS ( (SELECT PNUMBER FROM PROJECT WHERE DNUM=5) MINUS (SELECT PNO FROM WORKS_ON WHERE SSN=ESSN) ) Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 46
Renaming of Attributes in SQL • It is possible to rename any attribute that appears in the result of a • query. • Alias names are specified following the selected attributes and must • be bounded by double quotes if they contain spaces. • Attribute aliases can be referenced only in the ORDER BY clause. • Example: • SELECT E.LNAME Name, S.LNAME "Supervisor name" • FROM EMPLOYEE E, EMPLOYEE S • WHERE E.SUPERSSN=S.SSN Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 47
Outer Join • An outer join is specified by appending a parenthesized plus sign (+) • to the name of the foreign key in the join condition. • Example: • SELECT FNAME, LNAME, DEPENDENT_NAME • FROM EMPLOYEE, DEPENDENT • WHERE SSN=ESSN(+) • ORDER BY FNAME • Even when no dependents exist for a particular employee, that • employee’s name is retrieved. Null value will be replaced in the • dependent’s name. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 48
Aggregate Function in SQL • A number of built-in functions exist: COUNT, SUM, MAX, MIN, • and AVG. • These functions can be used in the SELECT clause or in the • HAVING clause. • The functions SUM, MAX, MIN, and AVG are applied to a set or • multiset of numeric values. • The functions MAX and MIN can also be used with attributes that • have nonnumeric domains if the domain values have a total • ordering among one another. • Null values are discarded when aggregate functions are applied to a • particular attribute. Chapter 8: SQL-99: Schema Definition, Basic Constraints and Queries 49