The following E/R diagram and tables are used in the examples throughout this post.
Category: Basis Data
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…
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…
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…
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…
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…
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…