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
 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 2
 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 3
 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 4
 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 5
 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 6
 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 7
 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 expression use the
following operators (= , <, >, <=, >=, <>)
Introduction to Databases 8
 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, Address

FROM EMPLOYEE
WHERE Fname='John' AND Minit='B’ AND Lname='Smith’;

Note: The result of the query may contain duplicate tuples

Introduction to Databases 9
 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 10
 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, Address
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE 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 11
 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 12
 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 13
 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.Lname
FROM EMPLOYEE AS E,
EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

Introduction to Databases 14
 ‫طيب ازاااااااااااااااااى؟‬
‫للحقل وللجدول ‪ alias‬مبدايا ده بيبقى شكل ال‬
‫‪-------------------‬‬
‫‪SELECT column_name AS alias_name‬‬
‫)‪ALIASES (contd.‬‬ ‫;‪FROM table_name‬‬
‫‪----------------------------‬‬
‫)‪SELECT column_name(s‬‬
‫;‪FROM table_name AS alias_name‬‬
‫نبص بقى كويس فى الجدولين اللى معانا فى صور البوست‬
‫‪ Customers‬صورة لجدول العمالء‬
‫‪ Orders‬و التانية لجدول للطلبات‬
‫لو عاوزين نعمل تغيير السماء حقول معينة فى جدول العمالء مثال – بنعمل االتى‬
‫‪‬‬ ‫‪Characteristics of Relations‬‬ ‫‪SELECT CustomerID AS ID, CustomerName AS Customer‬‬
‫;‪FROM Customers‬‬
‫ولو عاوزين نعمل تغيير السم الجدول فممكن نعمل كده لتغيير اسم جدول ال‬
‫‪Customers‬‬
‫وجدول ال‬
‫‪Orders‬‬
‫‪SELECT o.OrderID, o.OrderDate, c.CustomerName‬‬
‫‪FROM Customers AS c, Orders AS o‬‬
‫;‪WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID‬‬
‫‪------------------------------‬‬
‫‪ c‬ل ‪ customers‬فى المثال اللى فات غيرنا اسم جدول ال‬
‫‪ o‬ل ‪ orders‬واسم جدول ال‬
‫‪-------------------------------‬‬
‫باختصار بقى‬
‫‪ Alias‬امتى نستخدم ال‬
‫او بالعربى امتى بنغير االسماء سواء التغيير ده السم الحقل او اسم الجدول‬
‫) ‪ ( Join‬لو عندنا اكتر من جدول واحنا عاملينلهم ترابط مع بعض ‪1 -‬‬
‫فى بوست تانى ‪ Query‬وهنعرف ازاى نعمل فانكشن فى جملة ال ‪ Query‬لو استخدمنا فانكشن فى ال ‪2 -‬‬
‫لو اسماؤ الحقول كبيرة او ملهاش معنى ‪3 -‬‬
‫لو عاوز تعمل دمج الكتر من حقل مع بعض ‪4 -‬‬
‫‪-----------------------‬‬
‫لو شايف انك استفدت من البوست متوقفوش عندك‬
‫‪ #eraasoft_sql‬ولو عاوز تعرف السلسلة كلها تابع الهاشتاج ده‬
‫‪----------------------------------‬‬
‫‪#eraasoft‬‬
‫‪Introduction to‬‬ ‫‪Databases‬‬ ‫‪#sql‬‬ ‫‪15‬‬