System-independent mapping

(LogDataDesign)

1. Locial schema: "create table"
2. Physical schema: "create database","create tablespace","create index"
3. External schema:"create view" 

1. Select...from...where
2. Insert...into...
3. Update...where...
4. Delete where...

• adds of drops columns
• changes colomn defenitions 
• add or drop table constraints
• ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12);

SELECT <attribute list>

FROM <table list>

WHERE <condition>;

INSERT INTO EMPLOYEE(FNAME, LNAME, DNO, SSN)

VALUES  ('Richard', 'Marini', :DEPNUMBER, '653298653');

Retrieve the name and address of all employees who work for the 'Research' department.

SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER = DNO)

WHERE DNAME = 'Research';

Select all EMPLOYEE SSNs

SELECT SSN

FROM EMPLOYEE;

Make a list of 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');

Retrieve all employees whose address is in Houston, Texas

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE ADDRESS LIKE '%HOUSTON,TX%'

Retrieve the first name and last name 

of all employees working in the 

department ‘Research’

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE DNO =  

(SELECT DNUMBER

 FROM DEPARTMENT

              WHERE DNAME = 'Research'

Retrieve the name of each employee who has a dependent with the same first name and 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);

Retrieve the names of employees who 

have no dependents

SELECT FNAME, LNAME

FROM EMPLOYEE

WHERE NOT EXISTS  (SELECT *

              FROM DEPENDENT

              WHERE SSN = ESSN);

WHERE....IS NULL

WHERE...IS NOT NULL

• inner join
• left outer join
• right outer join
• full outer join

INNER JOIN

(default)

Used after SELECT-clause or HAVING-clause

COUNT,SUM,MAX,MIN,AVG

allows to apply aggregate function to subgroups of tubples in a relation based on an attribute value

SELECT DNO, COUNT(*), AVG(SALARY)

FROM EMPLOYEE

GROUP BY DNO;

• virtual tables
• consists of formula that determines which attributes from base table are shown

• No DISTINCT
• no aggregate functions
• only one table in FROM
• no correlated subquery
• no GROUP BY
• no UNION, INTERSECT or EXCEPT

View

(WITH CHECK OPTION)

• atomicity 
• consistency preservation
• isolation
• durability

seperate the SQL instructions to be processed by the precompiled from the native host language

"#SQL"