CHAPTER 7

Introduction to
Structured Query Language (SQL)
Contents

 The basic commands and functions of SQL

 How to use SQL for data administration (to create

tables, indexes, and views)

 How to use SQL for data manipulation (to add,

modify, delete, and retrieve data)

 How to use SQL to query a database to extract

useful information
Introduction to SQL
 SQL functions fit into two broad categories:
1. Data definition language (DDL)
◼SQL includes commands to:
◼ Create database objects, such as tables, indexes, and views
◼ Define access rights to those database objects

2. Data manipulation language (DML)

◼Includes commands to:
◼ insert,
◼ update,
◼ delete, and
◼ retrieve data within database tables
Introduction to SQL
 SQL is relatively easy to learn

 Basic command set has vocabulary of less than 100

words

 Nonprocedural language

 American National Standards Institute (ANSI) prescribes

a standard SQL

 Several SQL dialects exist

Data Definition Commands
Data Manipulation Commands
Data Manipulation Commands
The Database Model
The VENDOR and PRODUCT tables
Creating the Database

 The following two tasks must be completed:

1. Create database structure
Syntax: CREATE DATABASE databasename;
◼ RDBMS creates physical files that will hold database
◼ Tends to differ substantially from one RDBMS to another

2. Create tables that will hold end-user data

CREATE AND DROP DATABASE

❖ Creating the Database Structure

CREATE DATABASE database name;
Example :-
CREATE DATABASE dbstudent;

❖ Deleting the database created and its content

DROP DATABASE databasename;
Example :-
DROP DATABASE dbstudent;
Data Types

 Data type selection is usually dictated by nature of

data and by intended use

 Pay close attention to expected use of attributes for

sorting and data retrieval purposes
Some Common SQL Data Types
Creating Table Structures

 Use one line per column (attribute) definition

 Use spaces to line up attribute characteristics and constraints
 Table and attribute names are capitalized
 NOT NULL specification
 UNIQUE specification
 Primary key attributes contain both a NOT NULL and a
UNIQUE specification
 RDBMS will automatically enforce referential integrity for
foreign keys
 Command sequence ends with semicolon
Creating Table Structures
Syntax:
CREATE TABLE tablename (
column1 datatype [constraint],
column2 datatype [constraint],
PRIMARY KEY (columname),
FOREIGN KEY(columname) REFERENCES tablename );
Example CREATE TABLE

CREATE TABLE student CREATE TABLE teach

(studID CHAR(10) NOT NULL UNIQUE, ( id INTEGER NOT NULL UNIQUE,
studname VARCHAR(50) NOT NULL, studID CHAR(10),
studprog CHAR(5),
lecID CHAR(6),
studcgpa FLOAT,
semester VARCHAR(10),
PRIMARY KEY(studID)
); PRIMARY KEY(id),
FOREIGN KEY(studID) REFERENCES
CREATE TABLE lecturer student,
(lecID CHAR(6) NOT NULL UNIQUE,
FOREIGN KEY(lecID) REFERENCES
lecname VARCHAR(50) NOT NULL,
lecturer
lecfaculty CHAR(5),
);
salary INTEGER,
PRIMARY KEY(lecID)
);
 Creating Table Structures
Example:
CREATE TABLE VENDOR (
V_CODE INTEGER NOT NULL UNIQUE,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) ,
V_AREACODE CHAR(3) ,
V_PHONE CHAR(8) ,
V_STATE CHAR(2) ,
V_ORDER CHAR(1) ,
PRIMARY KEY (V_CODE));
Creating Table Structures
Example:
CREATE TABLE PRODUCT (
P_CODE VARCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT VARCHAR(35) NOT NULL,
P_INDATE DATE NOT NULL,
P_QOH SMALLINT NOT NULL,
P_MIN SMALLINT NOT NULL,
P_PRICE NUMBER(8,2) NOT NULL,
P_DISCOUNT NUMBER(5,2) NOT NULL,
V_CODE INTEGER,
PRIMARY KEY (P_CODE),
FOREIGN KEY(V_CODE) REFERENCES VENDOR(V_CODE) );

Access- use float or currency

SQL Constraints
 SQL Integrity Constraints
 Entity Integrity
◼ PRIMARY KEY
◼ NOT NULL and UNIQUE
 NOT NULL constraint
 Ensures that column does not accept nulls

 UNIQUE constraint
 Ensures that all values in column are unique

 Referential Integrity
◼ FOREIGN KEY
SQL Indexes

 When primary key is declared, DBMS automatically

creates unique index
 Indexes are used to improve the efficiency of data search.

 Using CREATE INDEX command, SQL indexes can be

created on basis of any selected attribute
CREATE INDEX indexname
ON tablename (columnname);
 Composite index
 Index based on two or more attributes
 Often used to prevent data duplication
Advanced Data Definition Commands

 All changes in table structure are made by using

ALTER command
 Followed by keyword that produces specific change
 Following three options are available:
◼ ADD
◼ MODIFY
◼ DROP
Adding a Column
 Use ALTER to add column or new attribute
 Do not include the NOT NULL clause for new column
 Syntax:
ALTER TABLE tablename
ADD columnname datatype;

 Example:

ALTER TABLE PRODUCT

ADD (P_SALECODE CHAR(1));
Dropping a Column
 Use ALTER to drop column
 Some RDBMSs impose restrictions on the deletion of an
attribute
 Syntax:
ALTER TABLE tablename
DROP COLUMN columnname;

 Example:
ALTER TABLE VENDOR
DROP COLUMN V_ORDER;
Changing a Column’s Data Type
 ALTER can be used to change data type
 Some RDBMSs (such as Oracle) do not permit changes to
data types unless column to be changed is empty
 Syntax:
ALTER TABLE tablename
MODIFY columnname datatype;

 Example:
ALTER TABLE PRODUCT
MODIFY (V_CODE CHAR(5));
Changing a Column’s Data Characteristics

 Use ALTER to change data characteristics

 If column to be changed already contains data, changes
in column’s characteristics are permitted if those changes
do not alter the data type
 Example:
ALTER TABLE PRODUCT
MODIFY (P_PRICE DECIMAL(9,2));
In Access, use
ALTER TABLE PRODUCT
ALTER COLUMN P_PRICE FLOAT
Example ALTER TABLE

ALTER TABLE lecturer ALTER TABLE lecturer

ADD roomno varchar(20); ADD PRIMARY KEY(lecID);

ALTER TABLE lecturer

ALTER TABLE teach
MODIFY roomno INTEGER;
ADD FOREIGN KEY(lecID) REFERENCES
lecturer;
ALTER TABLE lecturer
DROP roomno;
Data Manipulation Commands

 Adding table rows

 Listing table rows

 Updating table rows

 Deleting table rows
 Inserting table rows with a select subquery
Adding Table Rows

 INSERT
 Used to enter data into table

 Syntax:
INSERT INTO tablename
VALUES (value1, value2, … , value n);

 Example:
INSERT INTO VENDOR
VALUES (21503,‘Bryson, Inc.’,‘Smithson’,‘615’,‘223-3234’,
‘TN’ , ‘Y’);
INSERT DATA INTO A TABLE
INSERT INTO tablename
VALUES (dataColumn1, dataColumn2,…………. .);

INSERT INTO tablename (ColumnA, ColumnB,……)

VALUES (dataColumnA, dataColumnB,………);
❖ Example :

INSERT INTO student

VALUES (‘1234567890’, ‘Ahmad’, ‘CS241’, 3.28);

INSERT INTO student (studname, stuID)

VALUES (‘Sarah’,‘1234567891’);

INSERT INTO student

VALUES (‘1234567892’, ‘Zul’, null, null);
Adding Table Rows (continued)

 When entering values, notice that:

 Row contents are entered between parentheses

 Character and date values are entered between apostrophes

 Numerical entries are not enclosed in apostrophes

 Attribute entries are separated by commas

 A value is required for each column

 Use NULL for unknown values

 INSERT INTO PRODUCT

VALUES (‘BRT-345’,‘Titanium drill’ ,‘18-Oct-07’, 75, 10, 4.50,

0.06, NULL);
 Listing Table Rows
 SELECT
 Used to list contents of table
 Syntax:
SELECT columnlist
FROM tablename;

 Columnlist represents one or more attributes, separated by commas

◼ SELECT P_CODE, P_DESCRIPT, P_INDATE FROM PRODUCT

 Asterisk can be used as wildcard character to list all attributes

◼ SELECT * FROM PRODUCT
Listing Table Rows (continued)
Updating Table Rows
 UPDATE
 Modify data in a table
 Syntax:
UPDATE tablename
SET columnname = columnvalue
WHERE columname = columvalue;

 Example
UPDATE PRODUCT
SET P_INDATE = ‘2008/1/18’
WHERE P_CODE = ‘13-Q2/P2’;
 Updating Table Rows (continued)
 If more than one attribute is to be updated in the row,
separate corrections with commas

 Example
UPDATE PRODUCT
SET P_INDATE = ‘2008/1/18’, P_PRICE = 17.99, P_MIN =10
WHERE P_CODE = ‘13-Q2/P2’;
Deleting Table Rows
 DELETE
 Deletes a table row

 Syntax:
DELETE FROM tablename
WHERE columname = columnvalue;

 WHERE condition is optional

 If WHERE condition is not specified, all rows from

specified table will be deleted
DELETE DATA IN A TABLE

 Delete a row in a table

DELETE FROM tablename;

DELETE FROM tablename

WHERE columname = columndata
 Example :
DELETE FROM student
WHERE studID = ‘1234567890’; //to delete one row of data

DELETE FROM student; //to delete all rows in a table

Deleting Table Rows (Continued)
 DELETE
 Example:
DELETE FROM PRODUCT
WHERE P_CODE = ‘BRT-345’;

or

DELETE FROM PRODUCT

WHERE P_MIN = 5;
 Inserting Table Rows with a
Select Subquery
 INSERT
 Inserts multiple rows from another table (source)

 Uses SELECT subquery

◼ Query that is embedded (or nested) inside another query
◼ Executed first

 Syntax:
◼ INSERT INTO tablename SELECT columnlist FROM
tablename;
SELECT QUERIES
 Selecting Rows with Conditional Restrictions
 Comparison operators
 Logical operators

 Special operators
 Selecting Rows with
Conditional Restrictions

 Add conditional restrictions to SELECT statement,

using WHERE clause to select partial table contents

 Syntax:
SELECT columnname(s)
FROM tablename
WHERE condition(s);
 Selecting Rows with
Conditional Restrictions
 Conditional Restrictions can be done using:
Comparison Operators
= Equal to
< Less than
Special Operators
<= Less than or equal
BETWEEN Check whether an attribute value is
to
within the range
> Greater than
IS NULL Check whether an attribute value is null
>= Greater than or
LIKE Check whether an attribute value
equal to
matches a given string pattern
<> or != Not equal to
IN Check whether an attribute value
Logical Operators matches any value within a value list
AND Both conditions must be EXISTS Check whether a subquery returns any
true rows
OR Both or either one
condition must be true
NOT Not equal to
Restriction Using Comparison Operators

 Example:
Equal to
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344;

Not equal to
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344;
Restriction Using Comparison Operators

 Example:
Using Comparison Operator on Character Attributes
SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < '1558-QW1';

Using Comparison Operator on Date

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= '2008/01/01';
Restriction Using Comparison Operators

 Example:

Computed Columns
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE
FROM PRODUCT;

Computed Columns and Column Aliases

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH *
P_PRICE AS TOTVALUE
FROM PRODUCT;
Restriction Using Logical Operators

 Example:
OR Operator
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288;

AND Operator
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE<50 AND P_INDATE > '2008/01/01';
Restriction Using Logical Operators

 Example:
Combine AND & OR Operator

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE

FROM PRODUCT
WHERE (P_PRICE<50 AND P_INDATE > '2008/01/01')
OR V_CODE = 24288;
Restriction Using Logical Operators

 Example:
NOT Operator
SELECT *
FROM PRODUCT
WHERE NOT(V_CODE = 21344);
Restriction Using Special Operators

 BETWEEN
 Used to check whether attribute value is within a range

 Example:

SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 20.00 AND 150.00;
Restriction Using Special Operators

 IS NULL
 Used to check whether attribute value is null

 Example:

SELECT P_CODE, P_DESCRIPT, V_CODE

FROM PRODUCT
WHERE V_CODE IS NULL;
Restriction Using Special Operators

 LIKE
 Used to check whether attribute value matches given
string pattern
 Used with Wildcards (%) and (_)

SELECT V_NAME, V_CONTACT, V_AREACODE,

V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE 'SMITH%';
Restriction Using Special Operators

 LIKE (cont.)

SELECT V_NAME, V_CONTACT, V_AREACODE,

V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE '_INGH';
Restriction Using Special Operators

 IN
 Used to check whether attribute value matches any
value within a value list

SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);
Restriction Using Special Operators

 EXISTS
 Used to check if subquery returns any rows

SELECT *
FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE
P_QOH <= P_MIN);
Copying Parts of Tables

 SQL permits copying contents of selected table columns so

that the data need not be reentered manually into newly
created table(s)

 New table column names need not be identical with the

original table AND

 Need not have the same number of columns as the

original table

 First you need to create the new table then copy the data
from original table to the newly created one
 Copying Parts of Tables

 Example
Create a new table PART and Copy rows from PRODUCT

1. Create the PART table structure

CREATE TABLE PART(

PART_CODE CHAR(8) NOT NULL UNIQUE,
PART_DESCRIPT CHAR(35),
PART_PRICE DECIMAL(8,2),
V_CODE INTEGER,
PRIMARY KEY(PART_CODE));
 Copying Parts of Tables
 Example (cont.)
Create a new table PART and copy rows from PRODUCT
2. Add rows to new PART table using PRODUCT table rows

Syntax:
INSERT INTO target_tablename (target_columns)
SELECT source_columns
FROM source_tablename;

Example:
INSERT INTO PART(PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE)
SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE
FROM PRODUCT;
Copying Parts of Tables
 Adding Primary and
Foreign Key Designations
 When table is copied, integrity rules do not copy, so primary and
foreign keys need to be manually defined on new table

 User ALTER TABLE command

 Syntax:
◼ Primary key
ALTER TABLE tablename
ADD PRIMARY KEY(columnname);

◼ Foreign key
ALTER TABLE tablename
ADD FOREIGN KEY(columnname) REFERENCES (tablename);
 Adding Primary and
Foreign Key Designations
 Example:
ALTER TABLE PART
ADD PRIMARY KEY(PART_CODE);

ALTER TABLE PART

ADD FOREIGN KEY(V_CODE) REFERENCES VENDOR;

OR combine if both has not been designated

ALTER TABLE PART

ADD PRIMARY KEY(PART_CODE)
ADD FOREIGN KEY(V_CODE) REFERENCES VENDOR;
Deleting a Table from the Database

 DROP
 Deletes table from database

 Syntax:
DROP TABLE tablename;

 Example:
DROP TABLE PART;

Note: a table can be dropped if it is not the ‘ONE’ side of the relationship
Advanced Select Queries

 Ordering a listing ❑ Aggregate functions

ORDER BY ❑ COUNT

❑ MIN
 Listing unique values ❑ MAX
DISTINCT ❑ SUM

❑ AVG
 Grouping data
GROUP BY
Ordering a listing (ORDER BY)
List table contents in order (used when the listing order is important)
 Syntax:
SELECT columnname(s)
FROM tablename
WHERE condition(s)
ORDER BY columnname(s);

Note: WHERE clause is optional

 Example:
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;
Ordering a Listing
Ordering a listing
 To sort descending

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE

FROM PRODUCT
ORDER BY P_PRICE DESC;
Ordering a listing
 Cascading Order Sequence
SELECT EMP_LNAME, EMP_FNAME,EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
ORDER BY EMP_LNAME, EMP_FNAME,EMP_INITIAL;
Listing Unique Values (DISTINCT)

Produces a list of only those values that are different from

one another

 Syntax:
SELECT DISTINCT columnname
FROM tablename;

 Example:
SELECT DISTINCT V_CODE
FROM PRODUCT;
Listing Unique Values
Aggregate Functions
COUNT (Output Examples)
MAX and MIN (Output Examples)
SUM (Output Examples)
AVG (Output Examples)
Grouping Data (GROUP BY)

Used to organize selected data into groups.

 Syntax:
SELECT columnname(s)
FROM tablename
GROUP BY columnname(s);

 Example:
SELECT P_SALECODE, MIN( P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE;
Grouping Data (GROUP BY)
 Note:
 GROUP BY valid only when used in conjunction with one of the
SQL aggregate function (MIN, MAX, SUM, AVG and COUNT)

 Example (Valid) :
SELECT P_SALECODE, AVG( P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE;

 Example (Not Valid) :

SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
GROUP BY V_CODE;
Grouping Data (GROUP BY)

 Note:
 The SELECT columname(s) must include a combination of
columnname(s) and aggregate function
◼ SELECT P_SALECODE, AVG( P_PRICE)

 The GROUP BY columname(s) must include all nonaggregate

function columns specified in the SELECT columname(s)
◼ GROUP BY P_SALECODE;
GROUP BY (Output Example)
GROUP BY with HAVING clause
Works like a WHERE clause, but applied to groups rather
than to individual records
 Syntax:
SELECT columnname(s)
FROM tablename
GROUP BY columnname(s)
HAVING condition(s);
 Example:
SELECT V_CODE, COUNT(DISTINCT(P_CODE)), AVG( P_PRICE)
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG( P_PRICE) < 10;
GROUP BY with HAVING clause
SQL Syntax

SELECT columnname(s)
FROM tablename(s)
WHERE condition(s)
GROUP BY columnname(s)
HAVING condition(s)
ORDER BY columnname(s) ASC|DESC;
SQL Syntax (Example)

SELECT V_CODE, SUM(P_QOH*P_PRICE) AS TOTCOST

FROM PRODUCT
GROUP BY V_CODE
HAVING SUM(P_QOH*P_PRICE) > 500
ORDER BY SUM(P_QOH*P_PRICE) DESC;
View

 View is virtual table based on SELECT query

 Can contain columns, computed columns, aliases, and
aggregate functions from one or more tables

 Base tables : are tables on which view is based

 Create view by using CREATE VIEW command

View

 Syntax:
CREATE VIEW viewname AS SELECT query

 Example:
List product description, product price and product quantity on
hand for product with prices more than $50.00

CREATE VIEW PRICEGT50 AS

SELECT P_DESCRIPT, P_QOH, P_PRICE
FROM PRODUCT
WHERE P_PRICE > 50.00;
Virtual Tables: Creating a View
Joining Database Tables
 Join is performed when data are retrieved from more
than one table at a time

 It is done by matching foreign key and primary key of

related tables using equality operator

 e.g. to list out P_DESCRIPT, P_PRICE from PRODUCT +

V_COMPANY, V_PHONE from VENDOR → V_CODE from
both tables is used as the linking attribute.
 Joining Database Tables (continued)

 Example: to join the PRODUCT and VENDOR tables

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,

V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

Syntax:
SELECT <table name1>.<column name(s)>,
<table name2>.<column name(s)>
FROM <table name1>, <table name 2>
WHERE <table name1>.<primary key> = <table name 2>.<foreign key>;
The Results of a Join

Columns from PRODUCT Columns from VENDOR

Joining Database Tables (continued)

 Using ORDER BY to generate ordered list and Logical

Operator for condition restriction

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,

V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
AND P_INDATE > ‘2008/1/15’
ORDER BY P_PRICE;
An Ordered and Limited Listing
After a Join
Joining Database Tables (continued)

 When joining 3 or more tables:

 Specify a join condition for each pair of tables
 Number of join conditions will always be N-1
 N (number of tables represent in FROM clause)
 e.g. if there are 3 tables there will be 2 join conditions
Joining Database Tables (continued)
Example:
to list the customer last name, invoice number, invoice date and product
descriptions for all invoices for customer 10014

SELECT CUS_LNAME, INV_NUMBER, INV_DATE, P_DESCRIPT

FROM CUSTOMER, INVOICE, LINE, PRODUCT
WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
3 join
AND INVOICE.INV_NUMBER = LINE.INV_NUMBER
conditions
AND LINE.P_CODE = PRODUCT.P_CODE
AND CUSTOMER.CUS_CODE = 10014
ORDER BY INV_NUMBER;
Joining Tables with an Alias
 Alias can be used to identify source table
 Any legal table name can be used as alias
 Add alias after table name in FROM clause
 Syntax:
FROM tablename alias
 Example:
FROM PRODUCT P

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE

FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE;
Recursive Joins
 An alias is especially useful when a table must be joined to itself
in a recursive query.

 To generate a list of employees with their managers’ name →

join EMP table to itself
Recursive Joins

 SQL Command

SELECT E.EMP_NUM, E.EMP_LNAME, E.EMP_MGR,

M.EMP_LNAME

FROM EMP E, EMP M

WHERE E.EMP_MGR = M.EMP_NUM

ORDER BY E.EMP_MGR;
Using an Alias to Join Table to Itself
 Subqueries and Correlated Queries
 A subquery is a query (SELECT statement) inside a query
 A subquery is normally expressed inside parentheses
 The first query in the SQL statement is known as the outer query
 The query inside the SQL statement is known as the inner query
 The inner query is executed first
 The output of an inner query is used as the input for the outer query
 The entire SQL statement is sometimes referred to as a nested query

SELECT V_ CODE, V_NAME

FROM VENDOR
WHERE V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);
WHERE Subqueries
 Most common type uses inner SELECT subquery on
right side of WHERE comparison
 Requires a subquery that returns only one single value
 Value generated by subquery must be of
comparable data type
 Can be used in combination with joins
Subqueries Example

SELECT P_CODE, P_PRICE FROM PRODUCT

WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);

INSERT INTO PRODUCT

SELECT * FROM P;

UPDATE PRODUCT
SET P_PRICE = (SELECT AVG(P_PRICE) FROM PRODUCT)
WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_AREACODE = ‘615’)

DELETE FROM PRODUCT

WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_AREACODE = ‘615’)
IN Subqueries

 Used when comparing a single attribute to a list of

values
HAVING Subqueries

 HAVING clause restricts the output of a GROUP BY

query
 Applies conditional criterion to the grouped rows
Relational Set Operators
 UNION
 INTERSECT
 MINUS
 Work properly if relations are union-compatible
 Names of relation attributes must be the same and their
data types must be identical
UNION
 Combines rows from two or more queries without
including duplicate rows
 Example:
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
FROM CUSTOMER
UNION
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
FROM CUSTOMER_2
 Can be used to unite more than two queries
UNION ALL
 Produces a relation that retains duplicate rows
 Examplequery:
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE
FROM CUSTOMER
UNION ALL
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE
FROM CUSTOMER_2;
 Can be used to unite more than two queries
INTERSECT
 Combines rows from two queries, returning only the
rows that appear in both sets
 Syntax: query INTERSECT query
 Example query:
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
FROM CUSTOMER
INTERSECT
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE,
FROM CUSTOMER_2
MINUS
 Combines rows from two queries
 Returns only the rows that appear in the first set but not
in the second
 Syntax: query MINUS query
 Example:

SELECT CUS_LNAME, CUS_FNAME,

CUS_INITIAL, CUS_AREACODE, FROM
CUSTOMER
MINUS
SELECT CUS_LNAME, CUS_FNAME,
CUS_INITIAL, CUS_AREACODE, FROM
CUSTOMER_2