SQL – Structured
Query Language
CIS 324 – Chapter 5
CREATE TABLE


CREATE TABLE
newtablename (
Three-part column description,
Three-part column description,
Etc.,
);
Three-part column description: column name, data
type, and (optional) Primary Key, Not Null, Null
End with semi-colon;
DATA TYPES













Binary
Char
Datetime
Image
Integer
Money
Numeric
Smalldatetime
Smallint
Smallmoney
Text
Tinyint
Varchar
Binary
Character 0 -8,000 bytes
8 byte datetime (1753 - 9999)
Variable-length binary data
4 byte integer
8 byte money
Decimal
bytes
4 byte datetime (1900 – 2079)
2 byte integer (+-32768)
4 byte money
1 – 2 billion + characters
1 byte integer 0-255
Variable length character 0 – 8000
CREATE TABLE PROJECT (
ProjectID
Integer
Name
Char(25)
Department
VarChar (100)
MaxHours
Numeric (5,2));
PrimaryKey,
Not Null,
Null,
(Null is allowed)
(5 numbers, 2
decimal)
CREATE TABLE EMPLOYEE (
EmployeeNumber
Integer
Not Null,
Name
Char (25)
Not Null,
Phone
Char(8),
Department
VarChar (100)
);
ALTER TABLE EMPLOYEE
ADD CONSTRAINT EmployeePK PRIMARY KEY
(EmployeeNumber);
(Add a primary key, EmployeePK is an arbitrary name for the
constraint – must be unique)
ALTER TABLE statement is necessary for a composite primary key
CREATE TABLE ASSIGNMENT (
ProjectID
Integer
Not Null,
EmployeeNum Integer
Not Null,
HoursWorked
Numberic (5,2));
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT AssignmentPK PRIMARY KEY (ProjectID,
EmployeeNum);
DEFINING FOREIGN KEYS

ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK FOREIGN KEY (EmployeeNum)
REFERENCES EMPLOYEE;

ALTER TABLE ASSIGNMENT
ADD CONSTRAINT ProjectFK FOREIGN KEY (ProjectID)
REFERENCES PROJECT ON DELETE CASCADE;
On Delete Cascade  Allow Cascading Delete
QUERY THE TABLES
SELECT Statement: The result of a SQL SELECT statement is always a
relation. They start with one or more relations, manipulate them, and produce a
relation (could be as little as one column, one row – a single cell – but it is still a
relation)
SELECT column name, column name
 the order of
column names
Determines the order of
the results
FROM
table name;
SELECT
Name, MaxHours, Department
FROM
Project;
(what would be results)
SELECT
FROM
(different order)
Name, Department, MaxHours
Project;
SELECT
FROM
Department
PROJECT;
Results:
SELECT
FROM
Finance
Accounting
Marketing
Finance
The requirements for checking and eliminating duplicate
rows within a DBMS is difficult and time consuming – often
not done. In reality duplicate rows can exist within a SQL
relation.
DISTINCT Department
PROJECT;
Results:
Finance
Accounting
Marketing
 Avoid duplicate row
SELECTING ROWS
SELECT
FROM
WHERE
ProjectID, Name, Department, MaxHours
PROJECT
Department = ‘Finance’;
SELECT
FROM
WHERE
*
PROJECT
Department = ‘Finance’;
(What are results)
 ALL COLUMNS
SELECT / FROM / WHERE – is the pattern for SQL select statements
WHERE –
character data types in ‘ ‘ , values
ARE case-sensitive
Multiple where conditions joined by
AND
SELECT
FROM
WHERE
*
PROJECT
Department = ‘Finance’ AND MaxHours > 100;
(What are results)
SELECTING ROWS & COLUMNS
SELECTING ROWS AND COLUMNS – combine above
SELECT
FROM
WHERE
Name, Department
EMPLOYEE
Department = ‘Accounting’;
RANGES, WILDCARDS, NULLS
 WHERE Department IN (‘Accounting’, ‘Finance’,
‘Marketing’);
NOT IN keyword  WHERE Department NOT IN (‘Accounting’, ‘Finance’,
‘Marketing’);
BETWEEN keyword  WHERE EmployeeNumber BETWEEN 200 AND
500;
LIKE keyword  SELECT
*
FROM
Project
WHERE Name LIKE ‘Q_Portfolio Analysis’;
IN keyword
LIKE
_
%
 Selects Partial Values
 Selects a Single Wildcard Character
 Multiple Wildcard Characters
(Phone Like ‘285-%’;)
(ACCESS uses ? not _ and * not %)
NULL keyword  Searches for null values
WHERE Phone is NULL
SORTING RESULTS
SORTING RESULTS:
ORDER BY
(default in ascending order)
ORDER BY ASC
(ascending order)
ORDER BY DESC
(descending order)
SELECT
Name, Department
FROM
EMPLOYEE
ORDER BY
Department;
Can have multiple sort order – separate by comma
Select
FROM
ORDER BY
Name, Department
EMPLOYEE
Department DESC, Name ASC;
BUILT IN FUNCTIONS
BUILT-IN Functions:
COUNT 
SUM
AVG
MAX
MIN




SELECT
FROM
Operate on the results of a SELECT statement
counts the number of rows in the resulting
relation
sum of NUMERIC columns
average of NUMERIC columns
max value of NUMERIC columns
min value of NUMERIC columns
COUNT (*)
PROJECT;
Result  4
SELECT
FROM
Result  3
COUNT (DISTINCT Department)
PROJECT;
SELECT
FROM
WHERE
MIN (MaxHours), MAX (MaxHours), SUM (MaxHours)
PROJECT
ProjectID < 1500;
Result 
75.00
145.00
358.00
Cannot combine regular column names and built-in functions in the same
select statement (except when using a grouping statement)
Not Allowed:
SELECT
MaxHours, SUM(MaxHours)
FROM
PROJECT
WHERE
ProjectID < 1500;
Allowed:
SELECT
Department, Count(*)
FROM
EMPLOYEE
GROUP BY
Department;
This counts the number of employees in each department
Accounting
2
Marketing
2
Finance
1
Info Systems
2
GROUP BY / HAVING
GROUP BY - This sorts the table by the named column and then applies
the built in function to groups of rows having the same value as the named
column.
HAVING – Restricting conditions on the grouping
SELECT Department, Count(*)
FROM
EMPLOYEE
GROUP BY
Department
HAVING
COUNT(*) > 1;
Only groups departments with more than one member:
Accounting
2
Marketing
2
Info Systems
2
When using WHERE clauses with GROUP clauses there may be ambiguity
as to which is done first, do you check the where condition before grouping
or group before the where???
Standards indicate you apply the WHERE before the GROUPING
SELECT
FROM
WHERE
GROUP BY
HAVING
Department, Count(*)
EMPLOYEE
EmployeeNumber < 600
Department
COUNT (*) > 1;
Result  Accounting
2
 Evaluated First
 Evaluated Second

PowerPoint プレゼンテーション

WaM-DaM: A Data Model to Synthesize and Organize Water