Basic SQL

CLO1: Create a relational database schema in SQL that incorporates key, entity integrity, and referential integrity constraints and uses a declarative query language (SQL) to elicit information from a database. (Skills) Chapter objectives:

• Write basic SQL queries. Includes: selection, projection, join, ordering, and set operations.
• Define relational algebra and be able to state why it is important

Make sure you have access to a machine with a functional DBMS, I recommend MySQL. You can use the lab machine, or bring your own laptop to class.

Alternatively, you can use Oracle Apex. We will try to make it work.

We will do many practical exercises throughout the lectures.

We will use Java and JDBC to execute SQL queries, later on.

From now on:

• Tutorials
• Labs

When last we left databases...

We had decided they were great things We knew how to conceptually model them in ER diagrams We knew how to logically model them in the relational model We could formally specify queries Now: how do most people write queries? SQL!

A SQL relation is defined using the

create table command:

create table r (A D, A D, ..., A D, (integrity-constraint ),...,(integrity-constraint ))

1 1 2 2

n n

k

Integrity constraints can be: primary keys, candidate keys, foreign keys Example:

CREATE TABLE Student (sid CHAR(20),

name CHAR(20), address CHAR(20), phone CHAR(8), major CHAR(4), primary key (sid))

Download the DDL code from LMS.

3 tables:

1. Sailors
2. Boats
3. Reserves

We will use this example throughout the lecture.

SELECTION is in the WHERE clause You can use:

1. Attribute names of the relation(s) used in the FROM. comparison operators: =, <>, <, >, <=, >=
2. Apply arithmetic operations: rating*2
3. Operations on strings (e.g., “||” for concatenation).
4. Lexicographic order on strings.
5. Pattern matching: s LIKE p
6. Special format for comparing dates and times.
7. AND, OR to combine operations.

Show the resulting salaries if every employee is given a 10% raise.

SELECT Fname, Lname, 1.1 * E.Salary FROM EMPLOYEE ; Exercise: Write a query to retrieve all sailor names and ratings, and show their rating out of 5 instead of 10.

Between

when

rating

ORDER BY CLAUSE SQL allows the user to order the tuples in the result of a query by the values of one or more of the attributes that appear in the query result, by using the ORDER BY clause.

Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name.

SELECT D.Dname, E.Lname, E.Fname, P.Pname FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P WHERE D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber ORDER BY D.Dname, E.Lname, E.Fname;

The

EXCEPT/Minus operator in SQL is used to retrieve all the unique records from

the left operand (query), except the records that are present in the result set of the right operand (query).

The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two or more queries.

Basic query form:

SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <attribute list> ]; A simple retrieval query in SQL can consist of up to four clauses (that we have seen so far), but only the first two SELECT and

—

FROM are mandatory.

—

In SQL, three commands can be used to modify the database: INSERT, DELETE, and UPDATE.

We discuss each of these in turn.

In its simplest form, INSERT is used to add a single tuple (row) to a relation (table).

The values should be listed in the same order in which the attributes were specified in the CREATE TABLE command.

Example:

INSERT INTO sailors VALUES(22, 'dustin',7,45.0); Alternatively, we can specify the attributes to insert into.

Example:

INSERT INTO sailors(sid, sname, rating) VALUES(22, 'dustin',7); What happens to attribute age here?

INSERT commands must meet constraints specified in the CREATE TABLE DDL code.

Try it:

INSERT INTO sailors VALUES(22, 'dustin',7,45.0); INSERT INTO sailors(sname, rating) VALUES('dustin',7); What happens in each case?

Can you think of an INSERT statement that violates referential integrity?

A variation of INSERT enables inserting the results of a query into a table.

CREATE TABLE WORKS_ON_INFO (Emp_name VARCHAR(15), Proj_name VARCHAR(15), Hours_per_week DECIMAL(3,1) ); INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week) SELECT E.Lname, P.Pname, W.Hours FROM PROJECT P, WORKS_ON W, EMPLOYEE E WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

Another variation is to do this with CREATE TABLE:

CREATE TABLE D5EMPS LIKE EMPLOYEE (SELECT E.* FROM EMPLOYEE AS E WHERE E.Dno = 5) WITH DATA; All DBMSs have a data loading command from files, so you do not need to manually write all your INSERT statements for every tuple.

Example: LOAD DATA INFILE

in MySQL

The DELETE command removes tuples from a relation.

It includes a WHERE clause to select the tuples to be deleted.

Tuples are explicitly deleted from only one table at a time. However, the deletion may propagate to tuples in other relations if referential triggered actions are specified in the referential integrity constraints of the DDL. Example: ON DELETE CASCADE Example:

DELETE FROM EMPLOYEE WHERE Lname

= ‘Brown’;

Exercise: delete all sailors that are older than 50.

The UPDATE command is used to modify attribute values of one or more selected tuples. It includes a WHERE clause to select the tuples to be deleted.

Tuples are explicitly updated from only one table at a time. However, However, updating a primary key value may propagate to the foreign key values of tuples in other relations.

Example: ON UPDATE CASCADE The SET clause in the UPDATE command specifies the attributes and their new values.

Example:

UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno = 5; Exercise: update the age of sailors to increase by 1.

We have also looked at basic SQL commands.

In the next chapter, we will present the following features of SQL:

complex retrieval queries; views; triggers and assertions; and schema modification commands.