Mar 13

DELETE

The DELETE operator is used to erase records (not table structure).  The number of records deleted may be 0, 1, or many, depending on how many satisfy the predicate.

Format:
DELETE FROM table/view WHERE delete_criteria;

Example 1

Erase the record of student ‘S1020′  (Delete a single tuple)
DELETE FROM STUDENT WHERE STUID = ‘S1020′;

Example 2

Erase all enrollment records for student ‘S1020′.  (Delete several tuples).
DELETE FROM ENROLL WHERE STUID = ‘S1020′; Continue reading

Mar 13

INSERT

The INSERT operator is used to put new records into a table.  Normally it is not used to load an entire database (since other utilities can do that more efficiently).  Aside from this, older implementations of SQL use it to remove columns from existing tables (before the ALTER TABLE had this capability).

Format1:
INSERT INTO table (fieldlist) SELECT fieldlist FROM table WHERE append_criteria;

OR

Format2
INSERT INTO table (col1, col2…) VALUES (val1, val2…);

On the general format2 above, you can specify the columns in any order you wish and the system will match them to the appropriate table attributes.

Example 1

Insert a new faculty record with ID of ‘F330′, name of Jones, department of CIS, and rank of Instructor.  (Inserting a single record).
INSERT INTO FACULTY (FACID, FACNAME, DEPT, RANK) VALUES (‘F330′,’Jones’,'CIS’,Instructor’);

Since you are inserting for all fields, you can leave off the column names after FACULTY and get the same effect.  For instance, the following two examples are equivalent:
INSERT INTO FACULTY VALUES (‘F330′,’Jones’,'CIS’,Instructor’);
INSERT INTO FACULTY SELECT * FROM DATATABLE;

‘Datatable’ is a table that holds the data to be inserted.  Since the data is already in a table, this format of the INSERT is not as useful as the first version. Continue reading

Mar 13

UPDATE

Update gives you a way to modify individual attributes of a single tuple, a group of tuples, or a whole table (or view).

Format:
UPDATE table/view SET col-name = {value | expression} [col-name = value | subquery,...] [WHERE update_criteria];

You can only update tuples already present in the table (i.e., you cannot use UPDATE to add new tuples).  You can either UPDATE one table at a time.  You don’t have to know the present value of a field to set it (although you can refer to it in the “expression” clause).  The expression cannot be a sub-query or involve aggregate operations.

Example 1

Change the major of student ‘S1020′ to music. (Update a single field of one tuple)
UPDATE STUDENT SET MAJOR = ‘Music’ WHERE STUID = ‘S1020′;

Example 2

Change Tanaka’s department to MIS and rank to Assistant.  (Update several fields in one tuple)
UPDATE FACULTY SET DEPT = ‘MIS’ RANK = ‘Assistant’ WHERE FACNAME = ‘Tanaka’;

Example 3

Change the major of student ‘S1013′ from math to NULL.  (Updating using NULL)
UPDATE STUDENT SET MAJOR = NULL WHERE STUID = ‘S1013′; Continue reading

Mar 13

UNION QUERIES

A union query performs the ‘union‘ set operation on two or more tables.  The union operation returns all tuples from all tables (like appending a second table to the bottom of the first). The union operation also allows you to sort the resulting data, perform where restriction, etc.  The syntax for the UNION operator is shown below.

Format

SELECT fields FROM tables WHERE criteria GROUP BY field HAVING criteria
UNION
SELECT fields FROM tables WHERE criteria
GROUP BY field HAVING criteria ORDER BY sortcriteria;

Each select is a standard select with two exceptions.  First, the fields shown in the SELECT clause must be ‘union compatible’ (i.e., equivalent number, type, and order).  Second, there can only be one order by for the entire query. Continue reading

Mar 13

NESTED QUERIES

SQL allows the nesting of one query inside another, but only in the WHERE and the HAVING clauses.  In addition, SQL permits a subquery only on the right hand side of an operator.

Example 1

Find the names and IDs of all faculty members who teach a class in room ‘H221′. You could do this with 2 queries as follows:
SELECT FACID FROM CLASS WHERE ROOM = ‘H221′;
—> RESULT:  F101, F102

SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (F101, F102);

or you could combine the 2 into a nested query:
SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (SELECT FACID FROM CLASS WHERE ROOM = ‘H221′);

Note that the nested SELECT is executed first and its results are used as the argument to the outer SELECTs IN clause.

FACNAME FACID
Adams F101
Smith F202

Continue reading

Mar 13

MULTIPLE TABLE QUERIES

A JOIN operation is performed when more than one table is specified in the FROM clause.  You would join two tables if you need information from both. You must specify the JOIN condition explicitly in SQL.  This includes naming the columns in common and the comparison operator.

Example 1

Find the name and courses that each faculty member teaches.
SELECT FACULTY.FACNAME, COURSENUM FROM FACULTY, CLASS WHERE FACULTY.FACID = CLASS.FACID;

FACULTY.FACNAME COURSENUM
Adams ART103A
Tanaka CIS201A
Byrne MTH101B
Smith HST205A
Byrne MTH103C
Tanaka CIS203A

When both tables have an attribute name in common, you must specify which version of the attribute that you are referring to by preceding the attribute name with the table name and a period.  (e.g., table‑name.col‑name).  This is called “qualification”.

It is sometimes more convenient to use an “alias” (an alternative name) for each table.  SQL specifies alias names in the FROM clause immediately following the actual table.  Once defined, you can use the alias anywhere in the SELECT where you would normally use the table name. Continue reading