The following E/R diagram and tables are used in the examples throughout this post.

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…

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…

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…

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…

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…

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…

The ORDER BY clause is used to force the query result to be sorted based on one or more column values.  You can select either ascending or descending sort for each named column. Example 1 List the names and IDs of all faculty members arranged in alphabetical order. SELECT FACID, FACNAME FROM FACULTY ORDER BY…

Aggregate functions allow you to calculate values based upon all data in an attribute of a table. The SQL aggregate functions are:  Max, Min, Avg, Sum, Count, StdDev, Variance.  Note that AVG and SUM work only with numeric values and both exclude NULL values from the calculations. Example 1 How many students are there? SELECT…

The WHERE clause can be enhanced to be more selective.  Operators that can appear in WHERE conditions include: =, <> ,< ,> ,>= ,<= IN BETWEEN…AND… LIKE IS NULL AND, OR, NOT Example 1 Find the student ID of all math majors with more than 30 credit hours. SELECT STUID FROM STUDENT WHERE MAJOR =…