Assignment 2 solution #

Assignment 3 solutions #

SQL cont. #

INSERT #

The attributes (VALUES) are listed one by one in the same order as when the table was declared. If they are put in the wrong order the attributes will go into the wrong places (or violate domain constraints). You can specify the attributes and their order is

INSERT INTO EMPLOYEE(FNAME, LNAME, SSN, DNO)
    VALUES ('Richard', 'Marini', '1932914921', 4)

The attributes that are not null must be specified in the command. Attributes not supplied will go to their default value (or null if no default specified).

Note: We will skip this slide because we haven’t covered queries yet.

DELETE #

Examples

DELETE FROM EMPLOYEE
    WHERE LNAME='Brown'

Deletes all employees who have the last name “Brown”.

DELETE FROM EMPLOYEE
    WHERE SSN='123456789'

Deletes all employees who have the ssn 123456789.

DELETE FROM EMPLOYEE

Since this doesn’t have a WHERE clause, it is the same as saying WHERE TRUE, so all employees will be deleted from the table. The table will still exist (as opposed to DROP, where all the tuples would be deleted and the table itself would be deleted).

UPDATE #

Used to change values in existing tuples.

UPDATE PROJECT
    SET PLOCATION='Bellaire', DNUM=5
    WHERE PNUMBER=10

This will update each location and department number of all projects who have project number 10.

Queries #

There are 6 clauses used in SQL queries, we will first talk about these 3.

• SELECT takes a list of attributes
• FROM takes a list of tables
• WHERE takes a condition, a filter

Consider the usual company schema,

1. Retrieve the birthdate and address of the employee whose name is ‘John B. Smith’.

   SELECT BDATE, ADDRESS
   FROM EMPLOYEE
   WHERE FNAME='John', AND MINIT='B', AND LNAME='Smith'
   

Note: While relational algebra and relational model is set based, in SQL it is bag based. So by default a query result is a bag of tuples (duplicates are allowed).

2. Retrieve the name and address of all employees who work for the ‘Research’ department.

   SELECT FNAME, LNAME, ADDRESS
   FROM EMPLOYEE, DEPARTMENT
   WHERE DNUMBER=DNO AND DNAME='Research'
   

3. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, and birthdate.

   Include this in the weekly exercise.

   SELECT PNUMBER, DNUM, LNAME, BDATE
   FROM PROJECT, DEPARTMENT, EMPLOYEE
   WHERE DNUM=DNUMBER AND
         MGRSSN=SSN AND
         PLOCATION='Stafford'
   

Qualify attribute name #

If we have ambiguous attribute names we can use the entity name and a dot operator to specify which attributes we need.

For example, since DNUMBER is ambiguous when joining department and dept_locations,

SELECT DNAME, DLOCATION
FROM DEPARTMENT, DEPT_LOCATIONS
WHERE DEPARTMENT.DNUMBER=DEPT_LOCATIONS.DNUMBER