Unit-4

Introduction to Structured Query

Language (SQL)

Database Systems:
Design, Implementation, and
Management, Sixth Edition, Rob and
Coronel
Quantitative Analysis for Management, 9e / by Render/Stair/Hanna
Content to be covered
❑ Data Definition Commands,
❑ Data Manipulation Commands,
❑ Select Queries,
❑ Additional Data Definition Commands,
❑ Additional Select Query Keywords

6-2
Database Systems, 6e / Rob & Coronel
Introduction to SQL
❑ SQL functions fit into two broad
categories:
❑ Data definition language
◼ SQL includes commands to create
 Database objects such as tables, indexes, and
views
 Commands to define access rights to those
database objects
❑ Data manipulation language
◼ Includes commands to insert, update, delete,
and retrieve data within the database tables
6-3
Database Systems, 6e / Rob & Coronel
Introduction to SQ L
❑ SQL is relatively easy to learn
❑ Basic command set has a vocabulary of less
than 100 words
❑ Nonprocedural language
❑ American National Standards Institute
(ANSI) prescribes a standard SQL
❑ Several SQL dialects exist

6-4
Database Systems, 6e / Rob & Coronel
SQL Data Definition Commands

6-5
Database Systems, 6e / Rob & Coronel
Data Manipulation Commands

6-6
Database Systems, 6e / Rob & Coronel
Data Definition Commands
❑ Examine the simple database model and
the database tables that will form the basis
for the many SQL examples

❑ Understand the data environment

6-7
Database Systems, 6e / Rob & Coronel
The Database Model

6-8
Database Systems, 6e / Rob & Coronel
The Database Model
❑ Reflects the following business rules
◼ A customer may generate one or more invoices; each
invoice is generated by one customer
◼ An invoice contains one or more invoice lines; each
invoice line is associated with one invoice
◼ Each invoice line references one product; a product may
be found in many invoice lines
◼ A vendor may supply many products; some vendors do
not yet supply products
◼ If a product is vendor-supplied, that product is supplied
by only a single vendor
◼ Some products are not supplied by a vendor (in-house or
bought on the open market)
6-9
Database Systems, 6e / Rob & Coronel
 P_INDAT P_ONHAN P_MI P_PRIC P_DISCOUN V_COD
P_CODE P_DESCRIPT
E D N E T E

The Database Model

11QER/31 Power painter, 15 psi., 3-nozzle 03-Nov-03 8 5 $109.99 0.00 25595

13-Q2/P2 7.25-in. pwr. saw blade 13-Dec-03 32 15 $14.99 0.05 21344

14-Q1/L3 9.00-in. pwr. saw blade 13-Nov-03 18 12 $17.49 0.00 21344

1546-QQ2 Hrd. cloth, 1/4-in., 2x50 15-Jan-04 15 8 $39.95 0.00 23119

PRODUCT
1558-QW1 Hrd. cloth, 1/2-in., 3x50 15-Jan-04 23 5 $43.99 0.00 23119

2232/QTY B&D jigsaw, 12-in. blade 30-Dec-03 8 5 $109.92 0.05 24288

2232/QWE B&D jigsaw, 8-in. blade 24-Dec-03 6 5 $99.87 0.05 24288

2238/QPD B&D cordless drill, 1/2-in. 20-Jan-04 12 5 $38.95 0.05 25595

23109-HB Claw hammer 20-Jan-04 23 10 $9.95 0.10 21225

23114-AA Sledge hammer, 12 lb. 02-Jan-04 8 5 $14.40 0.05

54778-2T Rat-tail file, 1/8-in. fine 15-Dec-03 43 20 $4.99 0.00 21344

89-WRE-Q Hicut chain saw, 16 in. 07-Feb-04 11 5 $256.99 0.05 24288

PVC23DR PVC pipe, 3.5-in., 8-ft 20-Feb-04 188 75 $5.87 0.00

T

SM-18277 1.25-in. metal screw, 25 01-Mar-04 172 75 $6.99 0.00 21225

SW-23116 2.5-in. wd. screw, 50 24-Feb-04 237 100 $8.45 0.00 21231

WR3/TT3 Steel matting, 4'x8'x1/6", .5" 17-Jan-04 18

V_COD 5 $119.95 0.10
V_CONTAC 25595
V_AREACOD V_PHON V_STAT V_ORDE
mesh V_NAME
E T E E E R

21225 Bryson, Inc. Smithson 615 223-3234 TN Y

21226 SuperLoo, Inc. Flushing 904 215-8995 FL N

21231 D&E Supply Singh 615 228-3245 TN Y

21344 Gomez Bros. Ortega 615 889-2546 KY N

22567 Dome Supply Smith 901 678-1419 GA N

23119 Randsets Ltd. Anderson 901 678-3998 GA Y

24004 Brackman Bros. Browning 615 228-1410 TN N

24288 ORDVA, Inc. Hakford 615 898-1234 TN Y

25443 B&K, Inc. Smith 904 227-0093 FL N

25501 Damal Supplies Smythe 615 890-3529 TN N

25595 Rubicon Orton 904 456-0092 FL Y

Systems
6-10
Database Systems, 6e / Rob & Coronel
The Database Model
❑ Note the following regarding VENDOR and
PRODUCTS
◼ The VENDOR table contains vendors who are not
referenced in the PRODUCTS table
PRODUCT is optional to VENDOR
◼ Exisiting V_CODE values in the PRODUCT table must have
a match in the VENDOR table to ensure referential
integrity
◼ A few products are supplied factory-direct, a few are
made in-house and a few have been bought in a special
warehouse sale. Thus, a product is not necessarily
supplied by a vendor – VENDOR is optional to PRODUCT

6-11
Database Systems, 6e / Rob & Coronel
Creating the Database
❑ Two tasks must be completed
◼ create the database structure
◼ create the tables that will hold the end-
user data
❑ First task
◼ RDBMS creates the physical files that will
hold the database
◼ Tends to differ substantially from one
RDBMS to another

6-12
Database Systems, 6e / Rob & Coronel
The Database Schema
❑ Authentication
◼ Process through which the DBMS verifies
that only registered users are able to
access the database
◼ Log on to the RDBMS using a user ID and
a password created by the database
administrator
❑ Schema
◼ Logical grouping of database objects—
such as tables and indexes—that are
related to each other

6-13
Database Systems, 6e / Rob & Coronel
Data Types
❑ Data type selection is usually dictated by the
nature of the data and by the intended use

❑ Pay close attention to the expected use of

attributes for sorting and data retrieval purposes

◼ A field that is all numeric, but upon which no

mathematical computation will be performed, should be
stored as character for faster query processing.

◼ However, if you sort on a number stored as character, 10

will come before 2 because of the ANSI character
comparison (e.g., number of bedrooms)

6-14
Database Systems, 6e / Rob & Coronel
Some Common SQL Data Types

6-15
Database Systems, 6e / Rob & Coronel
Creating Table Structures
CREATE TABLE VENDOR (
V_CODE INTEGER NOT NULL UNIQUE,
V_NAME VARCHAR(35) NOT NULL,
V_CONTACT VARCHAR(15) NOT NULL,
V_AREACODE CHAR(3) NOT NULL,
V_PHONE CHAR(8) NOT NULL,
V_STATE CHAR(2) NOT NULL,
V_ORDER CHAR(1) NOT NULL,
PRIMARY KEY (V_CODE));

CREATE TABLE PRODUCT (

P_CODE VARCHAR(10) NOT NULL UNIQUE,
P_DESCRIPT VARCHAR2(35) NOT NULL,
P_INDATE DATE NOT NULL,
P_ONHAND SMALLINT NOT NULL,
P_MIN SMALLINT NOT NULL,
P_PRICE NUMBER(8,2) NOT NULL,
P_DISCOUNT NUMBER(4,2) NOT NULL,
V_CODE INTEGER,
PRIMARY KEY(P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR
ON UPDATE(CASCADE));

6-16
Database Systems, 6e / Rob & Coronel
Creating Table Structures
❑ Use one line per column (attribute) definition
❑ Use spaces to line up the 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 a semicolon
6-17
Database Systems, 6e / Rob & Coronel
Other SQL Constraints
❑ NOT NULL constraint
◼ Ensures that a column does not accept nulls
❑ UNIQUE constraint
◼ Ensures that all values in a column are unique
❑ DEFAULT constraint
◼ Assigns a value to an attribute when a new row is added to a
table
❑ CHECK constraint
◼ Validates data when an attribute value is entered
❑ ON UPDATE CASCADE
◼ Ensures that a change any VENDOR’s V_CODE will
automatically be applied to all FK references throughout the
system
◼ Also have ON DELETE CASCADE and ON UPDATE CASCADE
6-18
Database Systems, 6e / Rob & Coronel
SQL Constraint Examples
❑CUS_AREACODE CHAR(3) DEFAULT ‘615’ NOT NULL
CHECK (CUS_AREACODE IN (‘615’,’713’,’931’)),
◼ DEFAULT value applies only when new rows are added to a
table and only if no value is entered for that field
◼ CHECK is always validated when a row id added or modified

❑CUS_BALANCE NUMBER(9,2) DEFAULT 0.00,

❑CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME)

6-19
Database Systems, 6e / Rob & Coronel
SQL Constraint Examples
CREATE TABLE INVOICE(
INV_NUMBER NUMBER PRIMARY KEY,
CUS_CODE NUMBER NOT NULL
REFERENCES CUSTOMER(CUS_CODE),
INV_DATE DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT INV_CK1
CHECK(INV_DATE >
TO_DATE(’01-JAN-2002’,’DD-MON-YYYY’)));

6-20
Database Systems, 6e / Rob & Coronel
SQL Constraint Examples

CREATE TABLE LINE(

INV_NUMBER NUMBER NOT NULL,
LINE_NUMBER NUMBER(2,0)NOT NULL,
P_CODE VARCHAR(10) NOT NULL,
LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL,
LINE_PRICE NUMBER(9,2) DEFAULT 0.00 NOT NULL,
PRIMARY KEY(INV_NUMBER, LINE_NUMBER),
FOREIGN KEY(INV_NUMBER) REFERENCES INVOICE ON DELETE
CASCADE,
FOREIGN KEY(P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE(INV_NUMBER,P_CODE));

6-21
Database Systems, 6e / Rob & Coronel
Advanced Data Definition Commands

❑ All changes in the table structure are

made by using the ALTER command
◼ Followed by a keyword that produces
specific change
◼ Three options are available
 ADD
 MODIFY
 DROP

6-22
Database Systems, 6e / Rob & Coronel
Changing a Column’s Data Type
❑ ALTER can be used to change the data type
from integer to character
◼ ALTER TABLE PRODUCT
MODIFY(V_CODE CHAR(5));
◼ If V_CODE in PRODUCT references the same
field in VENDOR, there will be a referential
integrity violation

❑ Some RDBMSs (such as Oracle) do not

permit changes to data types unless the
column to be changed is empty

6-23
Database Systems, 6e / Rob & Coronel
Changing a Column’s Data
Characteristics
❑ Use ALTER to change data characteristics –
increase the width of P_PRICE
◼ ALTER TABLE PRODUCT
MODIFY(P_PRICE DECIMAL(9,2));

❑ If the column to be changed already contains

data, changes in the column’s characteristics are
permitted if those changes do not alter the data
type

❑ Some databases will allow you to increase but not

decrease the width of a column

6-24
Database Systems, 6e / Rob & Coronel
Adding or Dropping a Column
❑ Use ALTER to add a column
◼ ALTER TABLE PRODUCT
ADD(P_SALECODE CHAR(1));
◼ Do not include the NOT NULL clause for new column
because when it is added to an existing row it will
default to NULL. It can be added to the table structure
after all the data has been entered

❑ Use ALTER to drop a column

◼ ALTER TABLE PRODUCT
DROP COLUMN V_ORDER;
◼ Some RDBMSs impose restrictions on the deletion of an
attribute e.g., if involved in a FK relationship

6-25
Database Systems, 6e / Rob & Coronel
Data Manipulation Commands
❑ Adding table rows
❑ Saving table changes
❑ Listing table rows
❑ Updating table rows
❑ Restoring table contents
❑ Deleting table rows
❑ Inserting table rows with a select
subquery
6-26
Database Systems, 6e / Rob & Coronel
Data Manipulation Commands
❑ INSERT INTO tablename VALUES( value1, value2, …
valueN)
◼ String and date values must be entered between apostrophes
◼ Numerical entries are not enclosed in apostrophes
◼ Attribute entries are separated by commas
◼ A value is required for each column in the table
◼ Fields that accept NULL values can be set through INSERT
◼ You can specify the field names and then just supply those
values
 INSERT INTO PRODUCT(P_CODE, P_DESCRIPT) VALUES (‘BRT-
45’, ‘Titanium drill bit’);

6-27
Database Systems, 6e / Rob & Coronel
Common SQL Data Manipulation
Commands

6-28
Database Systems, 6e / Rob & Coronel
A Data View and Entry Form

6-29
Database Systems, 6e / Rob & Coronel
Saving Table Changes

❑ Changes made to table contents are

not physically saved on disk until
◼ Database is closed
◼ Program is closed
◼ COMMIT command is used
❑ Syntax
◼ COMMIT [WORK]
❑ Will permanently save any changes
made to any table in the database
6-30
Database Systems, 6e / Rob & Coronel
Listing Table Rows
❑ SELECT
◼ Used to list contents of table
❑ Syntax
◼ SELECT columnlist
FROM tablename
❑ columnlist represents one or more
attributes, separated by commas
❑ Asterisk can be used as wildcard character
to list all attributes

6-31
Database Systems, 6e / Rob & Coronel
Updating Table Rows
❑ UPDATE - Modify data in a table
◼ UPDATE tablename
SET columnname = expression [, columname =
expression] [WHERE conditionlist];
❑ If more than one attribute is to be updated in the
row, separate corrections with commas
◼ UPDATE PRODUCT SET P_INDATE=’18-JAN-2004’ ,
P_PRICE=16.99 WHERE P_CODE=’13-Q2/P2’;
◼ What would happen if the WHERE condition is omitted?

6-32
Database Systems, 6e / Rob & Coronel
Restoring Table Contents
❑ ROLLBACK
◼ Used restore the database to its previous
condition
◼ Only applicable if COMMIT command has not
been used to permanently store the changes
in the database
❑ Syntax
◼ ROLLBACK;
❑ COMMIT and ROLLBACK only work with
data manipulation commands that are
used to add, modify, or delete table rows
6-33
Database Systems, 6e / Rob & Coronel
Deleting Table Rows
❑ DELETE - Deletes a table row
◼ DELETE FROM tablename [WHERE conditionlist ];

❑ WHERE condition is optional

❑ If WHERE condition is not specified, all rows
from the specified table will be deleted

6-34
Database Systems, 6e / Rob & Coronel
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
 Values returned should match the attributes and
data types of the table in in INSERT statement
❑ Syntax
◼ INSERT INTO tablename SELECT columnlist
FROM tablename
6-35
Database Systems, 6e / Rob & Coronel
Selecting Rows with Conditional
Restrictions
❑ Select partial table contents by placing
restrictions on rows to be included in output
◼ Add conditional restrictions to the SELECT
statement, using WHERE clause

❑ Syntax
◼ SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;

6-36
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes
for VENDOR Code 21344

6-37
Database Systems, 6e / Rob & Coronel
The Microsoft Access QBE and its
SQL

6-38
Database Systems, 6e / Rob & Coronel
Comparison Operators

6-39
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes for
VENDOR Codes Other than 21344

6-40
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes
with a P_PRICE Restriction

P_PRICE <=10

6-41
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes:
The ASCII Code Effect

P_CODE <
‘15558-QW1’

6-42
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes:
Date Restriction

P_INDATE>= ’20-
Jan-2004’

Access uses # for

date delimeter

6-43
Database Systems, 6e / Rob & Coronel
SELECT Statement
with a Computed Column

SELECT
P_ONHAND*P_PRICE
FROM PRODUCT

6-44
Database Systems, 6e / Rob & Coronel
SELECT Statement with a Computed
Column and an Alias

SELECT
P_ONHAND*P_PRICE
AS TOTVALUE FROM
PRODUCT

6-45
Database Systems, 6e / Rob & Coronel
SELECT statement: date
computations
❑ SELECT P_CODE, P_INDATE, DATE()-90 AS
CUTDATE FROM PRODUCT WHERE
P_INDATE <=DATE()-90;

❑ SELECT P_CODE, P_INDATE, P_INDATE+90

AS EXPDATE FROM PRODUCT;

6-46
Database Systems, 6e / Rob & Coronel
Arithmetic Operators:
The Rule of Precedence
❑ Perform operations within parentheses

❑ Perform power operations

❑ Perform multiplications and divisions

❑ Perform additions and subtractions

6-47
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes:
The Logical OR

V_CODE=21344
OR
V_CODE=24288

6-48
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes:
The Logical AND

P_PRICE < 50
AND
P_INDATE>
’15-Jan-2004’

6-49
Database Systems, 6e / Rob & Coronel
Selected PRODUCT Table Attributes:
The Logical AND and OR

(P_PRICE <50
AND
P_INDATE>
’15-Jan-2004’)
OR
V_CODE=24288

6-50
Database Systems, 6e / Rob & Coronel
 Special Operators
❑ BETWEEN
◼ Used to check whether attribute value is within a
range P_PRICE BETWEEN 50.00 AND 100.00
❑ IS NULL
◼ Used to check whether attribute value is null
❑ LIKE
◼ Used to check whether attribute value matches a
given string pattern V_CONTACT [NOT] LIKE
‘Smith*’
◼ MS Access uses * and ? instead of % and _

6-51
Database Systems, 6e / Rob & Coronel
 Special Operators
❑ IN
◼ Used to check whether attribute value matches any
value within a value list
 V_CODE IN (21344,24288) if numeric
 SELECT V_CODE, V_NAME FROM VENDOR
WHERE V_CODE IN
(SELECT V_CODE FROM PRODUCT)
❑ EXISTS
◼ Used to check if a subquery returns any rows
 SELECT * FROM VENDOR
WHERE EXISTS
(SELECT * FROM PRODUCT
WHERE P_ONHAND <=P_MIN)

6-52
Database Systems, 6e / Rob & Coronel
The Effect of Data Entry into the
New P_SALECODE Column
UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_CODE = ‘1546-QQ2’

6-53
Database Systems, 6e / Rob & Coronel
Update of the P_SALECODE Column
in Multiple Data Rows

UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_CODE IN
(‘2232/QWE’,
’2232/QTY’);

6-54
Database Systems, 6e / Rob & Coronel
The Effect of Multiple Data Updates
in the PRODUCT Table

UPDATE PRODUCT
SET P_SALECODE = ‘2’
WHERE P_INDATE
<’25-Dec-2003’;

UPDATE PRODUCT
SET P_SALECODE = ‘1’
WHERE P_INDATE
>=’16-Jan-2004’
AND
P_INDATE
<=’10-Feb-2004’;

6-55
Database Systems, 6e / Rob & Coronel
Updates with Arithmetic Operators
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND+2
WHERE P_CODE=‘2232/QWE’;

UPDATE PRODUCT
SET P_PRICE = P_PRICE*1.10
WHERE P_PRICE < 50.00;

6-56
Database Systems, 6e / Rob & Coronel
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)
❑ First create the PART table structure
◼ Row names need not be the same nor the
number of columns as the original table but
those columns being copied must have the
same attributes
❑ Next add rows to new PART table using
PRODUCT table rows

6-57
Database Systems, 6e / Rob & Coronel
PART Attributes Copied
from the PRODUCT Table

INSERT INTO PART

(PART_CODE,
PART_DESCRIP,
PART_PRICE)
SELECT P_CODE,
P_DESCRIPT,
P_PRICE
FROM PRODUCT;

6-58
Database Systems, 6e / Rob & Coronel
Creating the PART table and Copying
from the PRODUCT in One Step
CREATE TABLE PART AS
SELECT
P_CODE AS PART_CODE,
P_DESCRIPT AS PART_DESCRIPT,
P_PRICE AS PART_PRICE
FROM PRODUCT;

❑Column names need not be identical

❑New table need not have the same number of
columns as the original
❑No entity integrity (PK) or referential integrity
(FK) rules are automatically applied to the new
table
6-59
Database Systems, 6e / Rob & Coronel
Additional SQL Operations
❑You can add primary (single and composite) and foreign
keys using the ALTER command

ALTER TABLE LINE

ADD PRIMARY KEY(INV_NUMBER,LINE_NUMBER),
ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE,
ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;

❑Deleting a table from the database

DROP TABLE PART;

6-60
Database Systems, 6e / Rob & Coronel
Additional SQL Operations
❑ Ordering a listing
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;

ORDER BY P_PRICE DESC puts the listing in descending order

❑ Cascading order sequence – multilevel ordering

•You can also include a WHERE clause before the ORDER clause

SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL,

EMP_AREACODE, EMP_PHONE FROM EMPLOYEE
WHERE EMP_AREACODE IN (‘718’, ‘212’)
ORDER BY
EMP_LNAME, EMP_FNAME, EMP_INITIAL;

6-61
Database Systems, 6e / Rob & Coronel
Additional SQL Operations

❑ Listing unique values – find out how many different

vendors are in the PRODUCT table
SELECT DISTINCT V_CODE FROM PRODUCT;

6-62
Database Systems, 6e / Rob & Coronel
Advanced Select Queries
❑ SQL provides useful functions
◼ Count

◼ Sum

◼ Find minimum and maximum values

◼ Calculate averages

❑ SQL allows the user to limit queries to only

those entries having no duplicates or entries
whose duplicates may be grouped
6-63
Database Systems, 6e / Rob & Coronel
Some Basic SQL Aggregate Functions

6-64
Database Systems, 6e / Rob & Coronel
COUNT Function Output Examples

COUNT(*) allows us to find out how

many rows in PRODUCT have a P_PRICE
<= $10.00
Includes rows that contain nulls, other
COUNTs do not include rows with nulls

MS Access does not allow the use of COUNT with DISTINCT

Use SELECT COUNT(*) FROM SELECT DISTINCT V_CODE WHERE
V_CODE IS NOT NULL
6-65
Database Systems, 6e / Rob & Coronel
MAX and MIN Function Output

Can not use WHERE P_PRICE = MAX(P_PRICE)

6-66
Database Systems, 6e / Rob & Coronel
The Total Value of All Items
in the PRODUCT Table

6-67
Database Systems, 6e / Rob & Coronel
AVG Function Output Examples

6-68
Database Systems, 6e / Rob & Coronel
GROUP BY Clause
❑ The SELECT’s columnlist must include a
combination of column names and aggregate
functions
❑ The GROUP BY clause’s columnlist must include all
non-aggregate function columns specified in the
SELECT’s columnlist.
◼ If required, you could also group by an aggregate
function columns that appear in the SELECT’s columnlist
❑ The GROUP BY clause columnlist can include any
columns from the tables in the FROM clause of the
SELECT statement, even if they do not appear in
the SELECT columnlist
6-69
Database Systems, 6e / Rob & Coronel
GROUP BY Clause Output Examples

 Note that nulls are included in the output

6-70
Database Systems, 6e / Rob & Coronel
Incorrect and Correct Use
of the GROUP BY Clause

GROUP BY requires an aggregate function

The number of products

supplied by each vendor

6-71
Database Systems, 6e / Rob & Coronel
An Application of the HAVING Clause
❑ HAVING operates like the WHERE clause
in the SELECT statement except…
◼ WHERE applies to columsn and expressions
for individual rows
◼ HAVING is applied to the output of a GROUP
operation

6-72
Database Systems, 6e / Rob & Coronel
An Application of the HAVING Clause

Number of products
supplied by each
vendor with the
average price

Number of products
supplied by each vendor
with the average price
<= $10.00

6-73
Database Systems, 6e / Rob & Coronel