Transcript 5(b)

IS2803
Developing Multimedia
Applications for Business (Part 2)
Spare slides for class resource
Rob Gleasure
[email protected]
robgleasure.com
IS2803

Today's lecture
 The SQL DDL
 SQL functions
The Data Definition Language (DDL)

So far we have worked the Data Manipulation Language (DML)
component of SQL

The DML lets us access and modify the data stored in specific table,
which is typically all we will want to do from the perspective of
application developers, sophisticated users, and naïve users

From a Database Administrator perspective, we will also need to
know how to use the Data Definition Language (DDL), which lets us
create tables with new schemas or modify the schemas of existing
tables
Create Table

The most basic part of the DDL is the CREATE TABLE query

This query lets us add a new table in a database

The basic structure of a CREATE TABLE query is as follows
CREATE TABLE table_name (
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
An Aside on Data Types

Note that the data types we specify vary according to the DBMS that
we are using

You can find a list of data types for oracle at
 http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatyp
e.htm

You can find a list of data types for MS Access, MySQL and MS
SQL Server at
 http://www.w3schools.com/sql/sql_datatypes.asp
Create Table

For example, say we want to create a new student for
Exchange_Students, that contains a Student_ID, a Name, and a
Referring_University, we might have the following
CREATE TABLE Exchange_Students (
Student_ID varchar(10),
Name varchar (50),
Referring_University varchar(255)
);

Example
http://www.w3schools.com/sql/trysql.asp?filename=trysql_create_table
Create Table and Constraints

As we discussed last term, often there are constraints for certain
fields that must be captured in the database (failure to comply with
constraints will cause actions to be rejected)

We can nest these constraints in our CREATE TABLE query as
follows
CREATE TABLE table_name (
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
Constraints: Not Null

The basic constraints in SQL are
 NOT NULL
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
);

http://www.w3schools.com/sql/trysql.asp?filename=trysql_create
_constraint_not_null
Constraints: Unique

UNIQUE
 We can specify that an individual column must be unique
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
);
Constraints: Unique

UNIQUE
 Alternatively, we can specify that two or more combined
columns must be unique when taken together using the
CONSTRAINT keyword
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT ID_Nm UNIQUE (PersonID,LastName)
);
Constraints: Primary Key

PRIMARY KEY
 Combines NOT NULL and UNIQUE but may only be used
once per table
 As with UNIQUE, we can specify that an individual column
must be the primary key
 E.g.
CREATE TABLE Persons(
PersonID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
);
Constraints: Primary Key

PRIMARY KEY
 Alternatively, we can specify that two or more combined
columns must be unique when taken together using the
CONSTRAINT keyword
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT Pk PRIMARY KEY (PersonID,LastName)
);
Constraints: Auto-Increment

AUTO-INCREMENT
 Sometimes (most often for the primary key) we want to
automatically generate a new unique number
 E.g. for MS Access, we will write
CREATE TABLE Persons(
PersonID int NOT NULL PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
);
Constraints: Auto-Increment

AUTO-INCREMENT
 Note the syntax for Oracle is little different and significantly
more complicated

First we need to create a sequence object
CREATE SEQUENCE Cust_sequence MINVALUE 1
START WITH 1 INCREMENT BY 1 CACHE 10;

Then we refer to a function of that object called nextval
INSERT INTO Persons (ID) VALUES (Cust_sequence .nextval');
Constraints: Foreign Key

FOREIGN KEY
 Ensures referential integrity with other table(s)
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT Pk PRIMARY KEY (PersonID, LastName),
CONSTRAINT fk_PerOrders FOREIGN KEY (PersonID)
REFERENCES Customers(CustomerID)
);
Constraints: Check

CHECK
 Limits the value range that can be placed in a column
 Can be used for a simply check on one column
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL CHECK (PersonID > 0),
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT Pk PRIMARY KEY (PersonID, LastName),
CONSTRAINT fk_PerOrders FOREIGN KEY (PersonID)
REFERENCES Customers(CustomerID)
);
Constraints: Check

CHECK
 May also be used for a more complex check
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL CHECK (PersonID > 0),
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT Pk PRIMARY KEY (PersonID, LastName),
CONSTRAINT Fk_PerOrders FOREIGN KEY (PersonID)
REFERENCES Customers(CustomerID)
CONSTRAINT Chk CHECK
(PersonID > 0 AND City LIKE '[a-z]')
);
Constraints: Default

DEFAULT (specifies a default value)
 Inserts a default value into a column
 E.g.
CREATE TABLE Persons(
PersonID int NOT NULL CHECK (PersonID > 0),
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT ‘Cork’,
CONSTRAINT Pk PRIMARY KEY (PersonID, LastName),
CONSTRAINT Fk_PerOrders FOREIGN KEY (PersonID)
REFERENCES Customers(CustomerID)
CONSTRAINT Chk CHECK
(PersonID > 0 AND City LIKE '[a-z]')
);
Drop

We use DROP queries when we wish to remove indices, tables, or
entire databases within our SQL

Dropping an index is done from the following structure
 DROP INDEX index_name ON table_name;

Dropping a table is done as follows
 DROP TABLE table_name;

Dropping an database as follows
 DROP DATABASE database_name;
Alter

Finally, sometimes we want to add or modify the data type of one or
more columns in our SQL

To add one or more columns
 ALTER TABLE table_name ADD column_name datatype;

We can also combine ALTER and DROP to remove specific
columns as follows
 ALTER TABLE table_name DROP COLUMN column_name;
Alter

Altering datatypes varies subtly for different DBMSs (see
http://www.w3schools.com/sql/sql_alter.asp)

For MS Access, we use


ALTER TABLE table_name ALTER COLUMN column_name datatype
For Newer versions of Oracle, we use

ALTER TABLE table_name MODIFY column_name datatype;
SQL Functions

Sometimes there are complex tasks or calculations we will need to
perform again and again

Rather than spell them out each time, we refer to these functions by
name

SQL provides us with a set of inbuilt functions for common needs
 Aggregate functions return a value calculated from data in a
column,
e.g. AVG (), COUNT(), FIRST(), LAST(), MAX(), MIN(), SUM()
 Scalar functions return an adapted value for each column
e.g. UCASE(), LCASE(), MID(), LEN(), ROUND(), and NOW()
AVG()

Returns the average value of a numeric column, either directly or
stored in a variable

Syntax:
SELECT AVG(col_name) AS var_name FROM table_name
E.g.
SELECT AVG(Price) AS PriceAverage FROM Products;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_avg
COUNT()

Returns the number of rows that matches a specified criteria

Syntax:
SELECT COUNT(col_name) AS var_name FROM table_name
E.g.
SELECT COUNT(CustomerID) AS NumberOfCustomer FROM Orders;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_cou
nt_all

Note null values will not be included but duplicates will unless
DISTINCT is added, e.g.
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomer FROM Orders;
FIRST() and LAST()

Returns the returns the first and last value of the selected column,
respectively

Syntax:
SELECT FIRST(Col_Name) AS Var_Name FROM Table_Name;
E.g.
SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;


http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_first
&ss=-1
Unfortunately this only works for MS Access…
First() and Last() Oracle Workarounds

To get the first record in Oracle, we use the following

Syntax:
SELECT Col_Name FROM Table_Name WHERE ROWNUM <=1;
E.g.
SELECT CustomerName FROM Customers WHERE ROWNUM <=1;

We can get the last by reverse ordering the records
E.g.
SELECT CustomerName FROM Customers WHERE ROWNUM <=1
ORDER BY CustomerID DESC;
MAX() and MIN()

Returns the largest or smallest value of the selected column,
respectively

Syntax:
SELECT MAX(col_name) AS var_name FROM table_name
E.g.
SELECT MAX(Price) AS HighestPrice FROM Products;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_max
GROUP BY

Sometimes we want to retrieve some computed value (average,
min, max, etc.) but we want to retrieve these values in groups

The syntax for these queries uses GROUP BY, as follows
SELECT column_name, aggregate_function(column_name2)
FROM table_name
WHERE column_name condition
GROUP BY column_name;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_gr
oupby
HAVING



The WHERE condition can’t be used with aggregate functions, so
we use a different term HAVING
E.g.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) condition;
http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_ha
ving
UCASE() and LCASE()

Returns the value of a field in upper or lowercase, respectively

Syntax:
SELECT UCASE(column_name) FROM table_name;
E.g.
SELECT UCASE(SupplierName) AS UC_SNm FROM Suppliers;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_ucas
e&ss=-1
MID()
The first character = 1

Returns the characters within a text field

Syntax:
Specifying the length is optional
SELECT MID(col_name,start,length) AS var_nm FROM table_name;
E.g.
SELECT MID(Phone,2,3) AS ThreeDigitAreaCode FROM Suppliers
WHERE Phone LIKE '(___)%';

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_mid
&ss=-1
LEN()

Returns the returns the length of the value in a text field

Syntax:
SELECT LEN (column_name) FROM table_name;
E.g.
SELECT CustomerName,City, LEN(PostalCode) as
LengthOfPostCode FROM Customers;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_len&
ss=-1
ROUND()

Returns a numeric field rounded to the number of decimals specified

Syntax:
SELECT ROUND(column_name,decimals) FROM table_name;
E.g.
SELECT ProductName, ROUND(Price,0) AS RoundedPrice
FROM Products;;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_roun
d
NOW()

Returns the current time and data in the format
 Month/Day/Year Hour:Minute:Second AM/PM

Syntax:
SELECT NOW() FROM table_name;
E.g.
SELECT ShipperName, NOW() AS Shippers11Feb FROM Shippers;

http://www.w3schools.com/sql/trysql.asp?filename=trysql_func_now
&ss=-1
Exercise

Consider the following problems related to the Customers database,
what queries best solve them?
1.
We want to create a new table in our database called Inventory with
the following criteria
I.
Three columns for Part_ID, Part_Name, and Part_of_Product
II.
All three of these columns set to not null
III.
The Part_ID set as primary key
IV.
The Part_of_Product set as a foreign key to
Products(ProductID)
V.
The default Part_Name is ‘Phalange’
Exercise
Run the following query to see what the database looks like
INSERT INTO Inventory (Part_ID,Part_of_Product) VALUES ('1', '4');
INSERT INTO Inventory (Part_ID,Part_of_Product,Part_Name) VALUES
('3', '5', 'Cogs');
Exercise

2.
3.
Consider the following problems, what queries best solve them?
We want to select the average Quantity for each ProductID in the
OrderDetails table?
We want to create the same result but with the ProductName from
the Products table instead of the ProductID (Hint – use LEFT JOIN)?
Exercise
4.
We want to create the same result but with the AverageQuantity
rounded to two decimal places?
5.
We want the same result but displayed according to the first three
letters of the ProductName in upper case, e.g.
CompanyAbbrev
AverageQuantity
ALI
30.09
ANI
40