SQL statements

Basic SQL Statements

1. CREATE: create a new table
2. INSERT: add data into a table
3. SELECT: read/retrieve some data from tables
4. DROP: delete a table, column, constraint, …
5. UPDATE: modify data in a table
6. DELETE: remove some data in a table
7. TRUNCATE: delete the complete data inside a table, but not
the table itself
8. ALTER: modify a table
 Basic SQL Statements

Tables, columns: Rows (records):

• CREATE • INSERT
• DROP • SELECT
• ALTER • UPDATE
• DELETE
• TRUNCATE
 1. CREATE a table
General form An example instance

CREATE TABLE <table name> ( CREATE TABLE Customer

<name> <datatype>,
<name> <datatype>, (
<name> <datatype>, CID INT,
<name> <datatype>, LastName
Etc.. Etc..
);
VARCHAR(255),
FirstName
VARCHAR(255),
Address
VARCHAR(255),
City VARCHAR(255)
);
 More about CREATING TABLES
The Enhanced Form
 The enhanced form of the CREATE TABLE
statement is:
CREATE TABLE supplier (
SNO INTEGER NOT NULL IDENTITY,
sname CHAR(100) NOT NULL,
address CHAR(100) NOT NULL,
CONSTRAINT pk_SNO PRIMARY KEY (SNO)
);

 More complex as it specifies:

 auto increments (via the IDENTITY keyword),
 constraints (not nulls and PRIMARY KEY)
 and possibly default values (note: none in this example).
1. CREATE table
 Things to note

 A column with the IDENTITY property allows only integer values.

 Each value is calculated by incrementing the last inserted value of the column
by the increment, incr.
 A column with the IDENTITY property must be numeric, i.e., of type INTEGER,
SMALLINT, TINYINT, NUMERIC or DECIMAL, and it allows only integer
values.
 The default value for the initial value and increment are both 1.
 There can be at most one column in a table with the IDENTITY property, and
the column with this property does not allow null value.

 The DEFAULT clause specifies the default value of the column.

 The DEFAULT property can be used with the CREATE TABLE, the ALTER
TABLE, and the SELECT statement.
 The FOREIGN KEY Clause
An example:

CREATE TABLE product (

PNO INTEGER NOT NULL IDENTITY,
pname CHAR(100) NOT NULL,
descr CHAR(100) NOT NULL,
SNO INTEGER NOT NULL,
CONSTRAINT pk_pno PRIMARY KEY (pno),
CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES supplier(sno));

fk_sno is the name for fk_sno applies fk_sno references the

our constraint to sno supplier tables’s sno
 The FOREIGN KEY Clause
• The FOREIGN KEY clause defines all column(s) explicitly that belong to the
foreign key.
• The REFERENCES clause specifies the table name with all column(s) that
bind to the corresponding PRIMARY KEY.
• The number and the data types of the columns in the FOREIGN KEY clause
must match the number and the corresponding data types of columns in the
REFERENCES clause, and both of these must match the number and the
corresponding data types of columns in the PRIMARY KEY of the
referenced table.
• The table that contains the foreign key is called the referencing table, and
the table that contains the corresponding primary key is called the target
table or referenced table.
 2. Inserting data
 Two methods of using the INSERT

1. The first form doesn't specify the column names where the
data will be inserted, only their values, i.e.

INSERT INTO table_name VALUES (value1, value2, value3,...)

2. A second form specifies both the column names and the

values to be inserted:

INSERT INTO table_name (column1, column2, column3,...) VALUES

(value1, value2, value3,...)
 3. Retrieving Data
 The SQL SELECT Statement
□The SELECT statement is used to select data
from a database.
□ The result is stored in a result table, called the
result-set.

 SQL SELECT Syntax

SELECT column_name(s) FROM table_name

or
SELECT * FROM table_name
 3. Retrieving Data
 Query the Customer Table

Select * from Customer;

/* selects all columns – the entire table */

Select LastName, FirstName from Customer;

/* selects ONLY LastName and FirstName */

Select FirstName + ' ' + LastName as Name from

Customer;
/* selects FirstName and LastName and concatentates them
together storing the result in an alias called Name*/
4. DROP
• Deleting a table will result in loss of complete information stored in the
table!
5. Update data

The WHERE clause specifies which record(s) that should be

updated. If you omit the WHERE clause, all records in the
table will be updated!

update the ContactName to "Juan" for all records

where country is "Mexico"
6. Delete data

The WHERE clause specifies which

record(s) should be deleted. If you omit
the WHERE clause, all records in the table
will be deleted!

deletes all rows in the "Customers" table, without deleting the table.
This means that the table structure, attributes, and indexes will be intact
7. Truncate
The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.
 8. Altering tables
 Removing a column from the Customer Table

ALTER TABLE Customer DROP COLUMN NI;

/* removes the national insurance number field */

SELECT * FROM Customer;

/* display the new table */
8. ALTER
• 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.
8. ALTER
• 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.
 8. ALTER
• 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.

The Customer table has been created. ‘ID’ is name of a column

in the table. The column must have been declared to not
contain NULL values (when the table was first created).
This statement is to create an primary key for the ID column.

Or
8. ALTER
• 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.

The Order table has been created. ‘PersonID’ is

name of a column in the table. PersonID in Order
table must have been declared to match with
Or PersonID in Person table.
SQL Aggregate Functions
• An aggregate function is a function that performs a
calculation on a set of values, and returns a single value.
 MIN() - returns the smallest value within the selected column
 MAX() - returns the largest value within the selected column
 COUNT() - returns the number of rows in a set
 SUM() - returns the total sum of a numerical column
 AVG() - returns the average value of a numerical column