1 / 14

Chapter 8 Part 2

Chapter 8 Part 2. SQL-99 Schema Definition, Constraints, Queries, and Views. Chapter Outline. SQL Data Definition and Data Type The CREATE TABLE command in SQL Attribute data types and domains in SQL Specifying Constraints in SQL Specifying attribute constraints and attribute defaults

yaphet
Download Presentation

Chapter 8 Part 2

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 Part 2 SQL-99 SchemaDefinition, Constraints, Queries, and Views

  2. Chapter Outline • SQL Data Definition and Data Type • The CREATE TABLE command in SQL • Attribute data types and domains in SQL • Specifying Constraints in SQL • Specifying attribute constraints and attribute defaults • Specifying key and referential integrity constraints • Specifying constraints on tuples using CHECK • Schema Change Statements in SQL • The DROP command • The ALTER command • Basic Queries in SQL Introduction to Databases

  3. Specifying Constraints in SQL: Specifying constraints on tuples using CHECK: • Other table constraints can be specified through additional CHECK clauses at the end of a CREATE TABLE statement. • Called tuple-based constraints because they apply for each tuple individually and are checked whenever a tuple is inserted or modified. CHECK (Dept_create_date <= Mgr_start_date) Introduction to Databases

  4. Schema Change Statements in SQL • The DROP command • The DROP command can be used to drop table and its definition. • If drop table; The relation can no longer be used in queries, updates, or any other commands since its description no longer exists DROP TABLE DEPENDENT; Introduction to Databases

  5. Schema Change Statements in SQL • The ALTER command • The definition of a table can be changed by using the ALTER command. • For table, the possible alter table action include adding or dropping a column, and changing column definition. Introduction to Databases

  6. The ALTER command (contd.) • Used to add an attribute to one of the base relations • The new attribute will have NULLs in all the tuples of the relation right after the command is executed; hence, the NOT NULL constraint is not allowed for such an attribute Example:ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); • The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. • This can be done using the UPDATE command. • To drop the Address column from EMPLOYEE ALTER TABLE EMPLOYEE DROP COLUMN Address; Introduction to Databases

  7. Basic Queries in SQL Retrieval Queries in SQL • SQL has one basic statement for retrieving information from a database; the SELECT statement • SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a query Introduction to Databases

  8. Retrieval Queries in SQL (contd.) Basic form of the SQL SELECT statement is called a mapping or a SELECT-FROM-WHERE block SELECT<attribute list> FROM<table list> WHERE<condition>; • <attribute list> is a list of attribute names whose values are to be retrieved by the query • <table list> is a list of the relation names required to process the query • <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query. Comparison expressionuse the following operators (= , <, >, <=, >=, <>) Introduction to Databases

  9. Simple SQL Queries (contd.) Example of a simple query on one relation: Query 0: Retrieve the birthdate and address of the employee whose name is 'John B. Smith'. Q0: SELECT Bdate, AddressFROM EMPLOYEEWHERE Fname='John' AND Minit='B’ AND Lname='Smith’; Note: The result of the query may contain duplicate tuples Introduction to Databases

  10. Simple SQL Queries (contd.) Query 1: Retrieve the name and address of all employees who work for the 'Research' department. Q1:SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dnumber=Dno; • (Dname='Research') is a selection condition • (Dnumber=Dno) is a join condition Introduction to Databases

  11. Simple SQL Queries (contd.) 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 birthdate. Q2: SELECT Pnumber, Dnum, Lname, Bdate, AddressFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation='Stafford‘ ; • In Q2, there are two join conditions • The join condition Dnum=Dnumber relates a project to its controlling department • The join condition Mgr_ssn=Ssn relates the controlling department to the employee who manages that department Introduction to Databases

  12. Ambiguous Attribute Names, Aliasing, and Tuple Variables Aliasing: • In SQL, we can use the same name for two (or more) attributes as long as the attributes are in different tables • A query that refers to two or more attributes with the same name must qualify the attribute name with the table name by prefixing the relation name to the attribute name Example: EMPLOYEE.Lname, DEPARTMENT.Dname Introduction to Databases

  13. ALIASES (contd.) • Some queries need to refer to the same relation twice • In this case, aliases are given to the relation name Query 8: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.Q8: SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM EMPLOYEE E S WHERE E.Super_ssn=S.Ssn ; • In Q8, the alternate relation names E and S are called aliases or tuple variables for the EMPLOYEE relation • We can think of E and S as two different copies of EMPLOYEE; E represents employees in role of supervisees and S represents employees in role of supervisors Introduction to Databases

  14. ALIASES (contd.) • Aliasing can also be used in any SQL query for convenience • Can also use the AS keyword to specify aliases Q8: SELECT E.Fname, E.Lname, S.Fname, S.LnameFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.Super_ssn=S.Ssn; Introduction to Databases

More Related