Transcript select

SQL – part 1
SQL:
DDL and DML
31-08-2007
NOEA/IT - FEN: Databases/SQL
1
Realisation of the Relational Model:
SQL-based DBMSs
(SQL = Structured Query Language)
• SQL is much more than merely queries – it includes
– DDL
• Data Definition Language
– DML
• Data Manipulation Language
– DCL
• Data Control Language
31-08-2007
NOEA/IT - FEN: Databases/SQL
2
SQL-Versions
• SQL has been implemented by many different DBMSmanufactures
• SQL is to a large extend the same for most DBMSs – close to a
de facto standard
• Standards:
SQL86 (SQL1), SQL89 (SQL1½), SQL92 (SQL2), SQL3
(SQL9x/SQL2000? - eventually SQL-99)
• SQL2 is still the most common standard.
• SQL-99 is the newest standard (released in 2002)
• Most manufactures have their own extensions (and omissions)
to the standard
31-08-2007
NOEA/IT - FEN: Databases/SQL
3
SQL2 - DDL
Supports domains:
– Basic types, INT, CHAR, strings etc.
– Date-Time
– Programmer defined types:
Not in MS SQL
Server 2000
CREATE DOMAIN cprType AS CHAR(10);
CREATE DOMAIN postalType AS CHAR(4);
Types allows the compiler/interpreter to check
for some logical errors
31-08-2007
NOEA/IT - FEN: Databases/SQL
4
SQL2 - DDL
CREATE TABLE Client (
cprno
cprType
NOT NULL,
….
Should make NOT
postCode postalType,
NULL unnecessary
…,
CONSTRAINT ClientPK
PRIMARY KEY(cprno),
CONSTRAINT PCodeCityFK
FOREIGN KEY(postCode) REFERENCES PCodeCity(pCode)
ON UPDATE CASCADE ON DELETE SET NULL);
31-08-2007
NOEA/IT - FEN: Databases/SQL
5
SQL2 - DDL
CREATE TABLE PCodeCity (
pCode
postalType
city
VARCHAR(30)
CONSTRAINT PCodeCityPK
PRIMARY KEY(pCode));
NOT NULL,
NOT NULL,
Also see Elmasri figure 8.1 and 8.2
31-08-2007
NOEA/IT - FEN: Databases/SQL
6
SQL2 - DDL
•
•
•
•
DROP SCHEMA
DROP TABLE
ALTER TABLE
ALTER TABLE
– DROP CONSTRAINT
– ADD CONSTRAINT
31-08-2007
NOEA/IT - FEN: Databases/SQL
7
SQL2 - DML
SELECT
UPDATE
INSERT
DELETE
31-08-2007
NOEA/IT - FEN: Databases/SQL
8
SQL2 – DML: SELECT
Queries:
SELECT
<attribute-list>
FROM
<tables>
[WHERE <condition>]
[GROUP BY
<attribute-list>]
[HAVING <condition>]
[ORDER BY
<attribute-list>]
[...]: WHERE, GROUP BY, HAVING and ORDER BY may be
omitted.
31-08-2007
NOEA/IT - FEN: Databases/SQL
9
The Company Database
31-08-2007
NOEA/IT - FEN: Databases/SQL
10
Company on SQL Server
• Script:
31-08-2007
NOEA/IT - FEN: Databases/SQL
11
Relational Algebra - Overview
31-08-2007
NOEA/IT - FEN: Databases/SQL
12
SQL2 - DML
(Q0): Row and coulomb selection:
SELECT
FROM
WHERE
BDATE, ADDRESS
EMPLOYEE
FNAME = ’John’ AND MINIT = ’B’
AND LNAME = ’Smith’
All attributes:
SELECT *
---31-08-2007
NOEA/IT - FEN: Databases/SQL
13
SQL2 - DML
(Q1): JOIN:
SELECT
FROM
WHERE
FNAME, LNAME, ADDRESS
EMPLOYEE, DEPARTMENT
DNAME = ’Research’
AND DNO = DNUMBER
Last term in the WHERE-clause is the join-condition.
If omitted the result will be the Cartesian product.
31-08-2007
NOEA/IT - FEN: Databases/SQL
14
SQL2 - DML
(Q2): JOIN several tables:
SELECT
FROM
WHERE
PNUMBER, DNUM, LNAME, ADDRESS
PROJECT, EMPLOYEE, DEPARTMENT
PLOCATION = ’Stafford’
AND DNUM = DNUMBER
AND SSN = MGRSSN
Note: Two join-conditions in the WHERE-clause.
31-08-2007
NOEA/IT - FEN: Databases/SQL
15
SQL2 - DML
(Q8): Ambiguous attribute names and aliases:
SELECT
FROM
WHERE
E.FNAME, E.LNAME,
S.FNAME, S.LNAME
EMPLOYEE E, EMPLOYEE S
E.SUPERSNN = S.SSN
EMPLOYEE is joined with itself using the aliases E and S.
’.’ (”dot”)-notation may also be used to resolve ambiguous attribute
names.
31-08-2007
NOEA/IT - FEN: Databases/SQL
16
SQL2 - DML
SQL-tables are NOT sets (in the math sense of the word set):
(Q11):
SELECT
FROM
(Q11A):
SELECT DISTINCT SALARY
FROM
EMPLOYEE
31-08-2007
SALARY
EMPLOYEE
NOEA/IT - FEN: Databases/SQL
17
SQL2 - DML
(Q4): Set operations:
(SELECT
FROM
WHERE
UNION
(SELECT
FROM
WHERE
PNUMBER
PROJECT, DEPARTMENT, EMPLOYEE
LNAME = ’Smith’ AND
DNUM = DNUMBER AND MGRSSN = SSN)
PNUMBER
PROJECT, WORKS_ON, EMPLOYEE
LNAME = ’Smith’ AND
PNO = PNUMBER AND ESSN = SSN)
Returns a set!!! (also INTERSECT and EXCEPT)
31-08-2007
NOEA/IT - FEN: Databases/SQL
18
SQL2 - DML
Updates:
– Inserting rows:
– Deleting rows:
– Updating row values:
INSERT
DELETE
UPDATE
As SELECT they work on tables.
31-08-2007
NOEA/IT - FEN: Databases/SQL
19
SQL2 - DML
Inserting a single row:
INSERT INTO
VALUES
EMPLOYEE
(’Richard’,’K’,’Marini’,’653298653’,
’30-DEC-52’,’98 Oak Forest, Katy,
’TX’,’M’,37000,’987654321’,4)
Inserting a single row, only selected attributes:
INSERT INTO
VALUES
EMPLOYEE(FNAME,LNAME,SSN)
(’Richard’,’Marini’,’653298653’)
Is rejected if any of the other attributes is defined NOT NULL
and doesn’t have defined a default value.
31-08-2007
NOEA/IT - FEN: Databases/SQL
20
SQL2 - DML
Inserting using sub SELECTs:
CREATE TABLE DEPTS_INFO
INSERT INTO
DEPTS_INFO
SELECT
FROM
WHERE
GROUP BY
(DEPT_NAME
NO_OF_EMPS
TOTAL_SAL
VARCHAR(15),
INTEGER,
INTEGER);
DNAME, COUNT(*), SUM(SALARY)
DEPARTMENT, EMPLOYEE
DNUMBER = DNO
DNAME;
Note DEPTS_INFO is not automatically updated if changes are made
in the othe tables. It is a base table.
31-08-2007
NOEA/IT - FEN: Databases/SQL
21
SQL2 - DML
Deleting rows:
DELETE FROM
WHERE
EMPLOYEE
LNAME =’Brown’
DELETE FROM
EMPLOYEE
WHERE
SSN = ’123456789’
DELETE FROM
EMPLOYEE
WHERE
DNO IN (SELECT
FROM
WHERE
DELETE FROM
EMPLOYEE
DNUMBER
DEPARTMENT
DNAME = ’Research’)
(Not equivalent to: ´DROP TABLE EMPLOYEE’. Why not?)
31-08-2007
NOEA/IT - FEN: Databases/SQL
22
SQL2 - DML
Updating rows:
UPDATE
SET
WHERE
PROJECT
PLOCATION = ’Bellaire’, DNUM = 5
PNUMBER = 10
UPDATE
SET
WHERE
EMPLOYEE
SALARY = SALARY*1.1
DNO IN(SELECT
DNUMBER
FROM
DEPARTMENT
WHERE
DNAME = ’Research’)
Note, that it is only possible to affect one table in one
UPDATE statement.
31-08-2007
NOEA/IT - FEN: Databases/SQL
23
Exercises
1. The Company Database:
1. Run the CREATE-scripts on MS SQL Server
2. Do some of examples in Elmasri chapter 8
2. To which extend does SQL Server obey to the
standard? Can you find any divergences?
3. The VW Database:
1. Run the CREATE-scripts
2. Implement the queries from vwDatabase.pdf in SQL
31-08-2007
NOEA/IT - FEN: Databases/SQL
24