DB Lab Manual

Database Systems Lab_Manual
DATABASE SYSTEMS
Tool
Microsoft SQL Server Management Studio
Semester-Fall
Spring-2023
Instructor
Dr. Zahid Iqbal
Course
Database Systems
Department
Computer Science
LAB#01______________________________________
1
1-The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.
SQL CREATE TABLE Syntax

CREATE TABLE table_name
(
column_name1 data_type,
....
)
The data type specifies what type of data the column can hold. For a complete reference of all
the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types
reference.
1-CREATE TABLE Example
Now we want to create a table called "Persons" that contains five columns: P_Id, LastName,
FirstName, Address, and City.
We use the following CREATE TABLE statement:
CREATE TABLE Persons

(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
SQL UPDATE Statement

The UPDATE statement is used to update records in a table
The UPDATE Statement
The UPDATE statement is used to update existing records in a table.
2
SQL UPDATE Syntax

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which
record or records that should be updated. If you omit the WHERE clause, all records will be
updated!
SQL UPDATE Example
The "Persons" table:
P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
4 Nilsen Johan Bakken 2 Stavanger
5 Tjessem Jakob
Now we want to update the person "Tjessem, Jakob" in the "Persons" table.
We use the following SQL statement:
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'
The "Persons" table will now look like this:

5 Tjessem Jakob Nissestien 67 Sandnes
3
2- SQL DELETE Statement

The DELETE statement is used to delete records in a table.
The DELETE Statement
The DELETE statement is used to delete rows in a table.
SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value
Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which
record or records that should be deleted. If you omit the WHERE clause, all records will be
deleted!
3-SQL DELETE Example
The "Persons" table:

5 Tjessem Jakob Nissestien 67 Sandnes
Now we want to delete the person "Tjessem, Jakob" in the "Persons" table.
DELETE FROM Persons

WHERE LastName='Tjessem' AND FirstName='Jakob'

4
Delete All Rows
It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact:
DELETE FROM table_name
or
DELETE * FROM table_name
Note: Be very careful when deleting records. You cannot undo this statement!
5
LAB#02______________________________________
1-SQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a table will result in loss of complete
information stored in the table!
SQL DROP TABLE Example
The following SQL statement drops the existing table "Shippers":
DROP TABLE Shippers;
2-SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table
itself.
Syntax
TRUNCATE TABLE table_name;
3-SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.
6
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE - RENAME COLUMN
To rename a column in a table, use the following syntax:
RENAME COLUMN old_name to new_name;
7
ALTER TABLE - ALTER/MODIFY DATATYPE
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
MODIFY COLUMN column_name datatype;
Oracle 10G and later:
MODIFY column_name datatype;
I LastName FirstName Address City

D
SQL ALTER TABLE Example
Look at the "Persons" table:
8
Now we want to add a column named "DateOfBirth" in the "Persons" table.
ALTER TABLE Persons
ADD DateOfBirth date;
Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data
type specifies what type of data the column can hold. For a complete reference of all the data
types available in MS Access, MySQL, and SQL Server, go to our complete Data Types
reference.
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or
four-digit format.
4-DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
ID LastName FirstName Address City
9
LAB#03______________________________________
The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
 The AND operator displays a record if all the conditions separated by AND are TRUE.

 The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
1-AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
10
2-OR Syntax
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
3-NOT Syntax
FROM table_name
WHERE NOT condition;
AND Example
The following SQL statement selects all fields from "Customers" where country is "Germany"
AND city is "Berlin":
Example
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';
OR Example
The following SQL statement selects all fields from "Customers" where city is
"Berlin" OR "München":
Example
WHERE City='Berlin' OR City='München';
OR "Spain":
Example
WHERE Country='Germany' OR Country='Spain';
11
NOT Example
The following SQL statement selects all fields from "Customers" where country is NOT
"Germany":
Example
WHERE NOT Country='Germany';
Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.
AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):
Example
WHERE Country='Germany' AND (City='Berlin' OR City='München');
The following SQL statement selects all fields from "Customers" where country is NOT
"Germany" and NOT "USA":
Example
WHERE NOT Country='Germany' AND NOT Country='USA';
LAB#04______________________________________
SQL NOT NULL Constraint

By default, a column can hold NULL values.
12
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot
insert a new record, or update a record without adding a value to this field.
1-SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT
accept NULL values when the "Persons" table is created:
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already
created, use the following SQL:
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
My SQL / Oracle (prior version 10G):
ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;
Oracle 10G and later:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
13
2-The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to
list the different (distinct) values.
3-SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample

database:
Customer CustomerNa ContactNa Address City PostalCo Countr

ID me me de y
1 Alfreds Maria Obere Str. Berlin 12209 Germa

Futterkiste Anders 57 ny
2 Ana Trujillo Ana Trujillo Avda. de la Méxic 05021 Mexico

Emparedados Constitución o D.F.
y helados 2222
3 Antonio Antonio Mataderos Méxic 05023 Mexico

Moreno Moreno 2312 o D.F.
Taquería
4 Around the Thomas 120 Londo WA1 1DP UK

Horn Hardy Hanover n
Sq.
14
5 Berglunds Christina Berguvsväg Luleå S-958 22 Swede

snabbköp Berglund en 8 n
SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the
"Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
Note: The example above will not work in Firefox! Because

COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox is
using Microsoft Access in our examples.
Example
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
SQL DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
15
SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Persons"
table is created:
My SQL / SQL Server / Oracle / MS Access:
ID int NOT NULL,
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using

functions like GETDATE():
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the
following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server:
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;
MS Access:
16
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL Server:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
LAB#05______________________________________
1-The SQL WHERE Clause

The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
17
WHERE Syntax
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statements, it is also used

in UPDATE, DELETE, etc.!
Demo Database
database:
CustomerI CustomerNam ContactNam Address City PostalCod Country

D e e e
1 Alfreds Maria Anders Obere Str. 57 Berlin 12209 German

Futterkiste y
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico

Emparedados y Constitución D.F.
helados 2222
3 Antonio Moreno Antonio Mataderos México 05023 Mexico

Taquería Moreno 2312 D.F.
4 Around the Horn Thomas 120 Hanover Londo WA1 1DP UK

Hardy Sq. n
5 Berglunds Christina Berguvsväge Luleå S-958 22 Sweden
18
snabbköp Berglund n8
WHERE Clause Example
The following SQL statement selects all the customers from the country "Mexico", in the
"Customers" table:
Example
WHERE Country='Mexico';
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double
quotes).
However, numeric fields should not be enclosed in quotes:
Example
WHERE CustomerID=1;
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:
Operator Description
19
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be

written as !=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
2-What is a NULL Value?
A field with a NULL value is a field with no value.
20
If a field in a table is optional, it is possible to insert a new record or update a record without
adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a
NULL value is one that has been left blank during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
Custome CustomerN ContactN Address City PostalC Count

rID ame ame ode ry
1 Alfreds Maria Obere Str. Berli 12209 Germ

Futterkiste Anders 57 n any
2 Ana Trujillo Ana Avda. de Méxi 05021 Mexic

Emparedad Trujillo la co
21
os y helados Constituci D.F. o

ón 2222
3 Antonio Antonio Mataderos Méxi 05023 Mexic

Moreno Moreno 2312 co o
Taquería D.F.
4 Around the Thomas 120 Lond WA1 UK

Horn Hardy Hanover on 1DP
Sq.
5 Berglunds Christina Berguvsv Lule S-958 Swede

snabbköp Berglund ägen 8 å 22 n
The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
22
The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
Example
FROM Customers
WHERE Address IS NOT NULL;
3-The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
ORDER BY Syntax
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Demo Database
database:
CustomerI CustomerNam ContactNam Address City PostalCod Country

D e e e
1 Alfreds Maria Anders Obere Str. 57 Berlin 12209 German

Futterkiste y
23
2 Ana Trujillo Ana Trujillo Avda. de la México 05021 Mexico

Emparedados y Constitución D.F.
helados 2222
3 Antonio Moreno Antonio Mataderos México 05023 Mexico

Taquería Moreno 2312 D.F.
4 Around the Thomas 120 Hanover Londo WA1 1DP UK

Horn Hardy Sq. n
5 Berglunds Christina Berguvsväge Luleå S-958 22 Sweden

snabbköp Berglund n8
ORDER BY Example
The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" column:
Example
ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table, sorted
DESCENDING by the "Country" column:
Example
ORDER BY Country DESC;
24
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table, sorted by the
"Country" and the "CustomerName" column. This means that it orders by Country, but if some
rows have the same Country, it orders them by CustomerName:
Example
ORDER BY Country, CustomerName;
Example
ORDER BY Country ASC, CustomerName DESC;
LAB#06______________________________________
SQL Wildcard Characters
A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in

a WHERE clause to search for a specified pattern in a column.
Symbo Description Example

l
* Represents zero or more bl* finds bl, black, blue, and blob
25
characters
? Represents a single character h?t finds hot, hat, and hit
[] Represents any single character h[oa]t finds hot and hat, but not
within the brackets hit
! Represents any character not in h[!oa]t finds hit, but not hot and
the brackets hat
- Represents any single character c[a-b]t finds cat and cbt

within the specified range
# Represents any single numeric 2#5 finds 205, 215, 225, 235,
character 245, 255, 265, 275, 285, and 295
Wildcard Characters in SQL Server
Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue,

and blob
_ Represents a single character h_t finds hot, hat, and hit
26
[] Represents any single character within h[oa]t finds hot and hat,
the brackets but not hit
^ Represents any character not in the h[^oa]t finds hit, but not
brackets hot and hat
- Represents any single character within c[a-b]t finds cat and cbt
the specified range
All the wildcards can also be used in combinations!
Here are some examples showing different LIKE operators with '%' and '_'
wildcards:
LIKE Operator Description
WHERE CustomerName Finds any values that starts with "a"

LIKE 'a%'
WHERE CustomerName Finds any values that ends with "a"

LIKE '%a'
WHERE CustomerName Finds any values that have "or" in any position
LIKE '%or%'
WHERE CustomerName Finds any values that have "r" in the second
LIKE '_r%' position
27
WHERE CustomerName Finds any values that starts with "a" and are at
LIKE 'a__%' least 3 characters in length
WHERE ContactName LIKE Finds any values that starts with "a" and ends with
'a%o' "o"
Using the % Wildcard
The following SQL statement selects all customers with a City starting with "ber":
Example
WHERE City LIKE 'ber%';
The following SQL statement selects all customers with a City containing the
pattern "es":
Example
WHERE City LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with any character,
followed by "ondon":
Example
WHERE City LIKE '_ondon';
28
The following SQL statement selects all customers with a City starting with "L", followed by
any character, followed by "n", followed by any character, followed by "on":
Example
WHERE City LIKE 'L_n_on';
Using the [charlist] Wildcard
The following SQL statement selects all customers with a City starting with "b", "s", or "p":
Example
WHERE City LIKE '[bsp]%';
The following SQL statement selects all customers with a City starting with "a", "b", or "c":
Example
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard
The two following SQL statements select all customers with a City NOT starting with "b", "s",
or "p":
Example
WHERE City LIKE '[!bsp]%';
Or:
Example
WHERE City NOT LIKE '[bsp]%';
29
LAB#07______________________________________
1-The SQL ALL Operator
The ALL operator:
 returns a boolean value as a result

 returns TRUE if ALL of the subquery values meet the condition
 is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
ALL Syntax With SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;
ALL Syntax With WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
2-SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a column it will allow only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on
values in other columns in the row.
30
SQL CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table
is created. The CHECK constraint ensures that the age of a person must be 18, or older:
MySQL:
ID int NOT NULL,
Age int,
CHECK (Age>=18)
);
ID int NOT NULL,
Age int CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple

columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ID int NOT NULL,
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the
following SQL:
31
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple

columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
3-What is a NULL Value?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without
adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a
NULL value is one that has been left blank during record creation!
32
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
Custome CustomerN ContactN Address City PostalC Count

rID ame ame ode ry
1 Alfreds Maria Obere Str. Berli 12209 Germ

Futterkiste Anders 57 n any
2 Ana Trujillo Ana Avda. de Méxi 05021 Mexic

Emparedad Trujillo la co o
os y helados Constituci D.F.
ón 2222
3 Antonio Antonio Mataderos Méxi 05023 Mexic

Moreno Moreno 2312 co o
Taquería
33
D.F.
4 Around the Thomas 120 Lond WA1 UK

Horn Hardy Hanover on 1DP
Sq.
5 Berglunds Christina Berguvsv Lule S-958 Swede

snabbköp Berglund ägen 8 å 22 n
The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
Example
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
34
Example
FROM Customers
WHERE Address IS NOT NULL;
LAB#08______________________________________
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single
or multiple columns (fields).
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons"
table is created:
MySQL:
ID int NOT NULL,
Age int,
PRIMARY KEY (ID)
);
ID int NOT NULL PRIMARY KEY,
Age int
);
35
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint

on multiple columns, use the following SQL syntax:
ID int NOT NULL,
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the
VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use
the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have
been declared to not contain NULL values (when the table was first created).
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
36
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between
tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY
KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is
called the referenced or parent table.
Look at the following two tables:
Persons Table
PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
3 Pettersen Kari 20
37
Orders Table
OrderID OrderNumber PersonID
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the
"Persons" table.
The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key
column, because it has to be one of the values contained in the parent table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders"
table is created:
MySQL:
OrderID int NOT NULL,
PersonID int,
38
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
OrderID int NOT NULL PRIMARY KEY,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint

OrderID int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint

39
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
LAB#09______________________________________
SQL Functions
SQL has many built-in functions for performing calculations on data.
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
 AVG() - Returns the average value

 COUNT() - Returns the number of rows
 FIRST() - Returns the first value
 LAST() - Returns the last value
40
 MAX() - Returns the largest value

 MIN() - Returns the smallest value
 SUM() - Returns the sum
1-SQL AVG() Function

The AVG() Function
The AVG() function returns the average value of a numeric column.
SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name
SQL AVG() Example
We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the average value of the "OrderPrice" fields.
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
The result-set will look like this:
OrderAverage
950
Now we want to find the customers that have an OrderPrice value higher than the average
OrderPrice value.
41
SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
Customer
Hansen
Nilsen
Jensen
2-SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be
counted) of the specified column:
SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct values of the
specified column:
SELECT COUNT(DISTINCT column_name) FROM table_name
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with
Microsoft Access.
42
SQL COUNT(column_name) Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to count the number of orders from "Customer Nilsen".
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders

WHERE Customer='Nilsen'
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders
in total:
CustomerNilsen
2
SQL COUNT(*) Example
If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders
NumberOfOrders
6
which is the total number of rows in the table.
43
SQL COUNT(DISTINCT column_name) Example
Now we want to count the number of unique customers in the "Orders" table.
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
NumberOfCustomers
3
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.
3-SQL FIRST() Function
The FIRST() Function
The FIRST() function returns the first value of the selected column.
SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name
SQL FIRST() Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
44
4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the first value of the "OrderPrice" column.
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
FirstOrderPrice
1000
4-SQL LAST() Function

The LAST() Function
The LAST() function returns the last value of the selected column.
SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name
SQL LAST() Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
45
5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen
Now we want to find the last value of the "OrderPrice" column.
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
LastOrderPrice
100
5-SQL MAX() Function

The MAX() Function
The MAX() function returns the largest value of the selected column.
SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name
SQL MAX() Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the largest value of the "OrderPrice" column.
46
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
LargestOrderPrice
2000
6-SQL MIN() Function

The MIN() Function
The MIN() function returns the smallest value of the selected column.
SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name
SQL MIN() Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the smallest value of the "OrderPrice" column.
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
47
SmallestOrderPrice
100
7-SQL SUM() Function

The SUM() Function
The SUM() function returns the total sum of a numeric column.
SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name
SQL SUM() Example

1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the sum of all "OrderPrice" fields".
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
48
OrderTotal
5700
LAB#10______________________________________
1-SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;
Alias Table Syntax
FROM table_name AS alias_name;
Alias for Columns Examples
The following SQL statement creates two aliases, one for the CustomerID column and one for
the CustomerName column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
49
The following SQL statement creates two aliases, one for the CustomerName column and one
for the ContactName column. Note: It requires double quotation marks or square brackets if the
alias name contains spaces:
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns
(Address, PostalCode, City and Country):
Example
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Note: To get the SQL statement above to work in MySQL use the following:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address

FROM Customers;
Note: To get the SQL statement above to work in Oracle use the following:
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;
Alias for Tables Example
The following SQL statement selects all the orders from the customer with CustomerID=4
(Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases
of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
The following SQL statement is the same as above, but without aliases:
Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
50
WHERE Customers.CustomerName='Around the
Horn' AND Customers.CustomerID=Orders.CustomerID;
Aliases can be useful when:
 There are more than one table involved in a query

 Functions are used in the query
 Column names are big or not very readable
 Two or more columns are combined together
2-The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text,
or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT
BETWEEN:
Example
WHERE Price NOT BETWEEN 10 AND 20;
51
BETWEEN with IN Example
The following SQL statement selects all products with a price between 10 and
20. In addition; do not show products with a CategoryID of 1,2, or 3:
Example
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
3-The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
IN Operator Examples
The following SQL statement selects all customers that are located in
"Germany", "France" or "UK":
52
Example
WHERE Country IN ('Germany', 'France', 'UK');
4-The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used

with aggregate functions.
HAVING Syntax
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SQL HAVING Examples
The following SQL statement lists the number of customers in each country.
Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
LAB#11______________________________________
1-The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a

column.
53
There are two wildcards often used in conjunction with the LIKE operator:
 The percent sign (%) represents zero, one, or multiple characters

 The underscore sign (_) represents one, single character
Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a
question mark (?) instead of the underscore (_).
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
FROM table_name
WHERE columnN LIKE pattern;
Here are some examples showing different LIKE operators with '%' and '_'
wildcards:
LIKE Operator Description
WHERE CustomerName LIKE Finds any values that start with "a"
'a%'
WHERE CustomerName LIKE Finds any values that end with "a"
'%a'
54
WHERE CustomerName LIKE Finds any values that have "or" in any position
'%or%'
WHERE CustomerName LIKE Finds any values that have "r" in the second
'_r%' position
WHERE CustomerName LIKE Finds any values that start with "a" and are at
'a_%' least 2 characters in length
WHERE CustomerName LIKE Finds any values that start with "a" and are at
'a__%' least 3 characters in length
WHERE ContactName LIKE Finds any values that start with "a" and ends with
'a%o' "o"
Example
WHERE CustomerName LIKE 'a%';
2-The SQL SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records.

Returning a large number of records can impact performance.
Note: Not all database systems support the SELECT TOP clause. MySQL supports

the LIMIT clause to select a limited number of records, while Oracle uses FETCH
FIRST n ROWS ONLY and ROWNUM.
SQL Server / MS Access Syntax:
55
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
MySQL Syntax:
FROM table_name
WHERE condition
LIMIT number;
Oracle 12 Syntax:
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
Older Oracle Syntax:
FROM table_name
WHERE ROWNUM <= number;
Older Oracle Syntax (with ORDER BY):
SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;
SQL TOP, LIMIT and FETCH FIRST Examples
The following SQL statement selects the first three records from the
"Customers" table (for SQL Server/MS Access):
Example
SELECT TOP 3 * FROM Customers;
SQL TOP PERCENT Example
The following SQL statement selects the first 50% of the records from the
"Customers" table (for SQL Server/MS Access):
56
Example
SELECT TOP 50 PERCENT * FROM Customers;
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes
you only want to list the different (distinct) values.
SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;
SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the
"Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
LAB#12______________________________________
1-The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions

(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.
57
GROUP BY Syntax
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SQL GROUP BY Examples
The following SQL statement lists the number of customers in each country:
Example
FROM Customers
GROUP BY Country;
Example
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
2-The SQL UNION Operator
The UNION operator is used to combine the result-set of two or

more SELECT statements.
 Every SELECT statement within UNION must have the same number of

columns
 The columns must also have similar data types
 The columns in every SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
58
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate

values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the column
names in the first SELECT statement.
SQL UNION Example
The following SQL statement returns the cities (only distinct values) from both
the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
SQL UNION ALL Example
The following SQL statement returns the cities (duplicate values also) from both
the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
LAB#13______________________________________
59
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a

related column between them.
Let's look at a selection from the "Orders" table:
OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Then, look at a selection from the "Customers" table:
CustomerI CustomerName ContactName Country

D
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico
Notice that the "CustomerID" column in the "Orders" table refers to the
"CustomerID" in the "Customers" table. The relationship between the two tables
above is the "CustomerID" column.
60
Then, we can create the following SQL statement (that contains an INNER JOIN),
that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:
 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Returns all records from the left table, and the
matched records from the right table
 RIGHT (OUTER) JOIN: Returns all records from the right table, and the
matched records from the left table
 FULL (OUTER) JOIN: Returns all records when there is a match in either
left or right table

1-SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both

tables.
INNER JOIN Syntax

FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
61
SQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
JOIN Three Tables
The following SQL statement selects all orders with customer and shipper
information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
2-SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records from the right table (table2). The result is 0 records from the
right side, if there is no match.
LEFT JOIN Syntax

FROM table1
LEFT JOIN table2
62
SQL LEFT JOIN Example
The following SQL statement will select all customers, and any orders they
might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
3-SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records from the left table (table1). The result is 0 records from the
left side, if there is no match.
RIGHT JOIN Syntax

FROM table1
RIGHT JOIN table2
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they
might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
63
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.
Tip: FULL OUTER JOIN and FULL JOIN are the same.
FULL OUTER JOIN Syntax

FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
LAB#14______________________________________
1-SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are
fields from one or more real tables in the database.
64
You can add SQL statements and functions to a view and present the data as if
the data were coming from one single table.
A view is created with the CREATE VIEW statement.
CREATE VIEW Syntax

CREATE VIEW view_name AS
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database engine recreates the
view, every time a user queries it.
SQL CREATE VIEW Examples
The following SQL creates a view that shows all customers from Brazil:
Example
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
We can query the view above as follows:
Example
SELECT * FROM [Brazil Customers];
SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
FROM table_name
WHERE condition;
The following SQL adds the "City" column to the "Brazil Customers" view:
65
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
SQL Dropping a View
A view is deleted with the DROP VIEW statement.
SQL DROP VIEW Syntax

DROP VIEW view_name;
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
2-identity(for increment)
Note: no.15 and no.16 labs can be arranged and managed up to need of time, and these labs
will be considered for the evaluation of Project/Presentation( Depends on Instructor).
66

DB Lab Manual

Uploaded by

Copyright:

Available Formats

DB Lab Manual

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DB Lab Manual

Uploaded by

Copyright:

Available Formats

Database Systems Lab_Manual

Dr. Zahid Iqbal

1-The CREATE TABLE Statement

SQL CREATE TABLE Syntax

1-CREATE TABLE Example

We use the following CREATE TABLE statement:

CREATE TABLE Persons

SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

SQL UPDATE Example

The "Persons" table:

P_Id LastName FirstName Address City

We use the following SQL statement:

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

2- SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

3-SQL DELETE Example

The "Persons" table:

P_Id LastName FirstName Address City

We use the following SQL statement:

DELETE FROM Persons

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

Delete All Rows

DELETE FROM table_name

DELETE * FROM table_name

The DROP TABLE statement is used to drop an existing table in a database.

SQL DROP TABLE Example

The following SQL statement drops the existing table "Shippers":

2-SQL TRUNCATE TABLE

3-SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

The following SQL adds an "Email" column to the "Customers" table:

ALTER TABLE - DROP COLUMN

ALTER TABLE - RENAME COLUMN

To rename a column in a table, use the following syntax:

ALTER TABLE - ALTER/MODIFY DATATYPE

SQL Server / MS Access:

My SQL / Oracle (prior version 10G):

Oracle 10G and later:

I LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

SQL ALTER TABLE Example

Look at the "Persons" table:

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

The "Persons" table will now look like this:

Change Data Type Example

We use the following SQL statement:

4-DROP COLUMN Example

We use the following SQL statement:

The "Persons" table will now look like this: