Page 95 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

If you want to insert multiple records of different tables using select union all, then the INSERT
INTO SELECT statement would be as shown below:
INSERT INTO cus_bckup (cus_id, cus_fname, cus_lname) SELECT cus_id, cus_fname,
cus_lname FROM cus_tbl WHERE cus_id=101 UNION ALL SELECT cus_id, cus_fname,
cus_lname FROM cust_tbl WHERE cus_id=102;

Above example will copy all the records from cus_tbl as well as cust_tbl table to cus_bckup
table whose cus_id is 101 in cus_tbl table and cus_id is 102 in cust_tbl table.

SQL WHERE Clause
The SQL WHERE clause is used to specify a condition while fetching the data from a single
table or by joining with multiple tables. If the given condition is satisfied, then only it returns a
specific value from the table. You should use the WHERE clause to filter the records and
fetching only the necessary records.
The WHERE clause is not only used in the SELECT statement, but it is also used in the
UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
Syntax: SELECT column1, column2 FROM table_name WHERE condition;

Text Fields vs Numeric Fields
- SQL requires single quotes around text values
- Numeric fields should not be enclosed in quotes

List of Operators than can be used with WHERE clause.
Operator Description
= Equal
<> Not equal. NOTE: in some versions of SQL this
operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column.

SQL SELECT DISTINCT Statement
The SQL SELECT DISTINCT statement is used to return only distinct (unique) records.
There may be a situation when you have multiple duplicate records in a table. While fetching
such records, it makes more sense to fetch only those unique records instead of fetching
duplicate records.
Syntax: SELECT DISTINCT column1, column2 FROM table_name.

Page | 11