Transcript SQL II

SQL II
CIS*2450
Advanced Programming Concepts
1
Data Types
• INTEGER
– numbers without a decimal point
– range is -2147483648 to 2147483647
• SMALLINT
– like INTEGER but smaller range -32768 to 32767
• DECIMAL(p,q)
– decimal numbers p digits long with q of these being
decimal places
• $99,999.99 = DECIMAL(7,2)
2
Data Types
• CHAR(n)
– character string n characters long
• VARCHAR(n)
– string up to n characters long
• DATE
– dates in the form DD-MON-YY or
MM/DD/YYYY
3
Nulls
• The values for some columns may be
unknown when a row is added to a table.
• SQL provides a special character referred to
as a null data value or a null.
– 3 meanings: unknown, not applicable, missing
• In any system that supports null values,
SQL allows one to choose whether or not to
allow nulls for each column.
4
Nulls
integrity
constraints
CREATE TABLE COMPUTER
( COMPID
DECIMAL (2) NOT NULL,
MFGNAME CHAR (15) NOT NULL,
MFGMODEL CHAR (25),
PROCTYPE DECIMAL (7,2),
PRIMARY KEY(COMPID) )
5
Comparison Operators
Comparison Operators
=
<
>
<=
>=
<> or !=
Meaning
Equal to
Less than
Greater than
Less than or equal to
Greater than or equal to
Not equal to
(implementation dependent)
6
Comparison Operators
• The BETWEEN feature allows one to specify a
range of values without using the comparison
operators.
SELECT PACKID, PACKNAME, PACKCOST
FROM PACKAGE
WHERE PACKCOST BETWEEN 200 AND 400
SELECT PACKID, PACKNAME, PACKCOST
FROM PACKAGE
WHERE PACKCOST > 200
AND PACKCOST < 400
7
Compound Conditions
• Compound conditions are formed by
connecting two or more simple statements
using AND, OR, and NOT.
• Simple conditions connected by AND must
all be TRUE for the compound statement to
be TRUE.
8
Compound Conditions
• Simple conditions connected by OR: the
compound statement is TRUE if any one
(or both) of the simple conditions is TRUE.
• Preceding a condition by the word NOT
reverses the truth of the original condition.
9
Compound Conditions
SELECT PACKNAME
FROM PACKAGE
WHERE PACKTYPE <> 'Database'
SELECT PACKNAME
FROM PACKAGE
WHERE NOT (PACKTYPE = 'Database')
10
Ordering
• The order of rows in a table is immaterial so one
cannot predict the order of responses to queries.
• If the order is important then one can specifically
request that the results be displayed in a desired
order with the ORDER BY clause.
SELECT EMPNUM, EMPNAME, EMPPHONE
FROM EMPLOYEE
ORDER BY EMPNAME
11
Ordering
• One can also sort on multiple keys and use
descending order.
• The keys to be used are listed in order of importance
after ORDER BY, and if this is followed by DESC
then the ordering is descending.
SELECT PACKID, PACKNAME, PACKTYPE, PACKCOST
FROM PACKAGE
ORDER BY PACKTYPE, PACKCOST DESC
12
Ordering
ORDER BY PACKTYPE, PACKCOST DESC
PACKID
14
5
10
23
15
PACKNAME
Oracle
DB2
Access
Visual Studio
C++Builder
PACKTYPE
Database
Database
Database
IDE
IDE
PACKCOST
20000
15000
500
800
400
13
Aggregate Functions
Built-in Function
COUNT
SUM
AVG
Meaning
Number of rows satisfying the
WHERE clause
Sum of the values in a column
for all rows satisfying the
WHERE clause
Average of the values in a
column for all rows satisfying
the WHERE clause
14
Aggregate Functions
Built-in Function Meaning
MAX
MIN
Largest value in a column for
all rows satisfying the
WHERE clause
Smallest value in a column
for all rows satisfying the
WHERE clause
15
Aggregate Functions
SELECT COUNT(PACKID), MAX(PACKCOST)
FROM PACKAGE
COUNT1
5
MAX2
20000
16
Aggregate Functions
• The AS operator is used in the following
examples to name the resultant column.
17
Aggregate Functions
SELECT COUNT(*) AS NUM_SUPPLIERS
FROM SP
NUM_SUPPLIERS
5
• This counts the number of active suppliers since the
COUNT(*) means count all the rows of SP
– some suppliers may be duplicates!
18
Aggregate Functions
SELECT COUNT(DISTINCT SP.SNO)
AS NUM_SUPPLIERS
FROM SP
NUM_SUPPLIERS
4
• More accurate: This counts the number of
different suppliers supplying parts.
19
Aggregate Functions
SELECT SUM(SP.QTY)
AS QTY_P2
FROM SP
WHERE SP.PNO = 'P2'
• This calculates the total quantity of part P2.
20
Aggregate Functions
SELECT SP.PNO, SUM(SP.QTY) AS QTY
FROM SP
GROUP BY SP.PNO
SP.PNO
P1
P2
P4
QTY
600
400
400
• GROUP BY creates groups
based upon column entries.
SUM adds within group.
• This produces a table of
part numbers and their total
quantities for each part
supplied.
21
Nested Queries
• Can place one query inside another.
• The inner query is called a subquery and is
executed first.
SELECT PACKID, PACKNAME
FROM PACKAGE
WHERE PACKTYPE= 'Database‘
AND PACKCOST >
( SELECT AVG (PACKCOST)
FROM PACKAGE
WHERE PACKTYPE = 'Database' )
22
Client-Server and SQL
• An SQL-agent is an application which includes
some SQL operations.
• The application is bound to an SQL-client which
is used to communicate with an SQL-server.
• The server carries out the database operations.
• The client and server are connected through the
CONNECT operation.
• After the transactions are completed, use the
DISCONNECT statement.
23