Transcript Lecture 6

Data Definition Languages
Atif Farid Mohammad
UNCC
SQL
• Most popular query language for DBMSs
– current standard SQL99
• most DBMSs conform .. but some features are still
not implemented by some vendors.
• Based on the relational algebra
DDL – Data Definition Language
• Creating database objects
• Altering table definitions
• Dropping database objects
DDL – Data Definition Language
CREATE TABLE Company.Employee (
FName
VARCHAR(15) NOT NULL,
MInit
CHAR,
LName
VARCHAR(15) NOT NULL,
SSN
CHAR(9) NOT NULL PRIMARY KEY,
BDate
DATE,
Address
VARCHAR(40),
Sex
CHAR,
Salary
DECIMAL(10,2),
SuperSSN CHAR(9),
DNo
INTEGER NOT NULL REFERENCES
Company.Department,
FOREIGN KEY (SuperSSN)
REFERENCES Company)
Schema
• Schema is a named collection of objects
(tables) in a database.
• Names need only be unique within their
schema!
– Create table Company.Employee(…)
• Company is the schema name
• Employee is the table name
– Create table G1.Employee (…)
Schema (con’t)
• If no schema is created (or implied by the table
creation), DB2 uses your user ID for the schema
name. Any tables created by user 3gh3 will be
referred to as:
3gh3.TableName
You may have noticed in the tutorial that DB2
doesn’t like the numeric at the beginning of a
schema name…..so……
• We’ll use our groups as our schema…
C2.TableName
Alter
ALTER TABLE Company.Employee
ADD COLUMN status CHAR(1)
ALTER TABLE Company.Department
ALTER COLUMN DName SET DATA TYPE VARCHAR(50)
• ALTER used to add columns, to increase the length of
an existing VARCHAR attribute or to add or delete
constraints
• In other cases, you need to drop the table & recreate it.
Using Alter to Add Foreign Key
Constraints
• To specify a FK constraint in a create table
statement, the referenced table must
already be created.
• Sometimes this is impossible! Circular
reference?
ALTER TABLE Employee FOREIGN
KEY(DNo) REFERENCES Department;
DML - Data Manipulation
Language
4 main SQL statements:
– INSERT - put new tuples into the database
– UPDATE - change values that already exist
– DELETE - remove tuples from the database
– SELECT - “query”; retrieve data that satisfies
some condition or set of conditions.
Insert
INSERT INTO Company.Employee VALUES
(‘Paul’, ‘E’, ‘Fish’, 111223333, ‘1962-12-12’,
‘200 First Ave’, ‘M’, 36000, 333445555, 5)
INSERT INTO Company.Employee
(FName, Lname, SSN, DNo)
VALUES (‘Paul’, ‘Fish’, 111223333, 5)
INSERT INTO Company.Employee
(FName, Lname, SSN, DNo)
VALUES (‘Paul’, ‘Fish’, 111223333, 5),
(‘Sally’, ‘Lambert’, 222334444, 4)
Batch Loads
• Import or load command is used to do bulk
inserts into the database.
import from <filename> of DEL insert into
Company.Employee
“Delimited Text”
Update
UPDATE TABLE Company.Employee
SET Salary = 30000
WHERE SSN = 453453453
Delete
DELETE FROM Company.Employee
* Deletes all records in Company.Employee table
DELETE FROM Company.Employee where Salary < 25000
* Deletes all Employees who earn less than 25K
Select
SELECT [DISTINCT] <columns>
FROM <tables>
[ WHERE <condition> ]
Where:
columns = list of attributes to be retrieved
tables = list of relations needed to process the query
condition = a boolean expression that identifies which
tuples are to be retrieved
Assumption
• For the following examples, we assume
the default schema thus eliminating the
need to qualify the table names with a
schema name.
– The default is either the user name or a
schema specified as:
db2 set schema=H4
Simple Select
SELECT plocation, dnum
FROM project
PLOCATION
DNUM
Bellaire
5
Sugarland
5
Houston
5
Stafford
4
Houston
1
Stafford
4
SELECT DISTINCT plocation, dnum
FROM project
PLOCATION
DNUM
Bellaire
5
Sugarland
5
Houston
5
Stafford
4
Houston
1
SQL does not
automatically eliminate
duplicates .. it returns “multisets”
Simple Select
SELECT fname, lname
FROM employee
WHERE salary > 40000
FNAME

(
Fname, Lname
LNAME
Jennifer
Wallace
James
Borg

salary > 40000
Employee)
Simple Select
SELECT dname, dnumber,
mgrssn, mgrstartdate
FROM department
SELECT *
FROM department
Retrieves everything from Department table (in
no particular order)
DNAME
DNUMBER
MGRSSN
MGRSTARTDATE
Research
5
333445555
1988-05-22
Administration
4
987654321
1995-01-01
Headquarters
1
888665555
1981-06-19
You should know…
Relational
Algebra


(project)
(select)
SQL
select
where clause
Example…
• Select topic, Subject from Paper
topic, subject (Paper)
Example…
• Select topic, Subject from Paper, Journal
Where Author= First_Author
topic, subject
Author=First_Author
(Paper X Journal)
Example…
• Select topic from Paper, Journal Where
Author= First_Author and Publish_Date =
2014
•
topic
Author=First_Author (Paper X Journal)
^ Publish_Date=2014
Example…
• Select topic, Subject from Paper, Journal
Where Author= First_Author and
Publish_Date = 2014
topic, subject
Author=First_Author
^ Publish_Date=2014
(Paper X Journal)
Table Qualifiers (“Range
Variables”)
SELECT E.fname, E.ssn
FROM employee E
WHERE E.salary > 40000
SELECT Employee.fname, Employee.ssn
FROM employee
WHERE Employee.salary > 40000
– qualifiers are only necessary when it is
unclear which table an attribute belongs to
but..they can be used anytime you wish (as
in the examples above).
– SQL is case insensitive.
Simple Select
SELECT fname, lname, address
FROM employee, department
WHERE dname = ‘Research’ AND
This is a join in SQL!
dnumber = dno
Lists the names & addresses of all employees in the research
department.
** Note that the string “Research” is case sensitive!
FNAME
LNAME
ADDRESS
John
Smith
731 Fondren, Houston, TX
Franklin
Wong
638 Voss, Houston, TX
Ramesh
Narayan
975 Fire Oak, Humble, TX
Joyce
English
5631 Rice, Houston, TX
How is a query evaluated?
• Basically (“conceptual evaluation strategy”):
–
–
–
–
compute the cross product of all tables in the from-list
delete rows in the cross product that fail the “where” condition
delete all columns that do not appear in the select list
if DISTINCT is specified, eliminate duplicate rows
• This strategy is probably the least efficient way
to compute a query! An optimizer will find more
efficient strategies to compute the same
answers.
Expressions & Strings in Select
List each employee’s ssn, their current salary and a listing of
their new salary, given that they just received a 10% raise.
SELECT ssn, salary, salary * 1.10 AS NewWage
FROM Employee
WHERE salary > 40000
SSN
SALARY NEWWAGE
987654321
43000
47300
888665555
55000
60500
Expressions & Strings in Select
Find employees that earn 10,000 more than another
employee.
SELECT E1.ssn as Emp1, E1.salary,
E2.ssn as Emp2, E2.salary
FROM Employee E1, Employee E2
WHERE 10000 + E1.salary = E2.salary
EMP1
SALARY
123456789 30000
EMP2
SALARY
333445555 40000
Pattern Matching
Find the names of employees whose
dependents name(s) begin with “A”.
SELECT lname, dependent_name
FROM employee, dependent
WHERE dependent_name like ‘A%’
and ssn=essn
LNAME
DEPENDENT_NAME
Smith
Alice
Wong
Alice
Wallace
Abner
Union
Make a list of employees (first
names), dependents and their
respective birthdates.
SELECT fname, bdate
FROM employee
UNION
SELECT dependent_name, bdate
FROM dependent
1
BDATE
James
11/10/1937
Jennifer
06/20/1941
Abner
02/28/1942
Franklin
12/08/1955
Joy
05/03/1958
Ramesh
09/15/1962
Theodore
10/25/1963
John
01/09/1965
Alice
04/05/1966
Elizabeth
05/05/1967
Alicia
07/19/1968
Ahmad
03/29/1969
Joyce
07/31/1972
Michael
01/04/1988
Alice
12/30/1988
Union (column renamed)
Make a list of employees (first
names), dependents and their
respective birthdates.
SELECT fname as name, bdate
FROM employee
UNION
SELECT dependent_name as name, bdate
FROM dependent
NAME
BDATE
James
11/10/1937
Jennifer
06/20/1941
Abner
02/28/1942
Franklin
12/08/1955
Joy
05/03/1958
Ramesh
09/15/1962
Theodore
10/25/1963
John
01/09/1965
Alice
04/05/1966
Elizabeth
05/05/1967
Alicia
07/19/1968
Ahmad
03/29/1969
Joyce
07/31/1972
Michael
01/04/1988
Alice
12/30/1988
Union All
• Duplicates will be removed during a union…note
the contrast to general select where we need to
specify the DISTINCT keyword.
• To retain duplicates we use UNION ALL.
SELECT fname as name, bdate
FROM employee
UNION ALL
SELECT dependent_name as name, bdate
FROM dependent
INTERSECTION
• Find all employees who work on both
project 1 and project 3.
SELECT essn
FROM works_on
WHERE pno = 1
INTERSECT
SELECT essn
FROM works_on
WHERE pno=2
ESSN
123456789
453453453
NOTE: INTERSECT ALL retains duplicates.
EXCEPT (Difference)
• Find all employees who work on project 2
but not on project 1.
SELECT essn
FROM works_on
WHERE pno = 2
EXCEPT
SELECT essn
FROM works_on
WHERE pno=1
ESSN
333445555
NOTE: EXCEPT ALL retains duplicates.
Nested Queries
Find last name and salary of all managers
SELECT lname, salary
FROM employee
WHERE ssn IN
(SELECT mgrssn
FROM department)
Can you express this query in another way?
“nested subquery”
produces a
(multi)set used by
the outer query
Nested Queries
Find last name and salary of all those who
are not managers.
SELECT lname, salary
FROM employee
WHERE ssn NOT IN
(SELECT mgrssn
FROM department)