Introduction to SQL

What is SQL?
– When a user wants to get some information
from a database file, he can issue a query.

– A query is a user–request to retrieve data or

information with a certain condition.
– SQL is a query language that allows user to
specify the conditions. (instead of algorithms)
 Concept of SQL

– The user specifies a certain condition.

– The program will go through all the records
in the database file and select those records
that satisfy the condition.(searching).

– Statistical information of the data.

– The result of the query will then be stored in
form of a table.
The SQL Standard
• SQL – Structured Query Language
⚫ a ‘standard’ that specifies how
⚫ a relational schema is created
⚫ data is inserted / updated in the relations
⚫ data is queried
⚫ transactions are started and stopped
⚫ programs access data in the relations
• Every relational database management system
(RDBMS) is required to support / implement the SQL
standard.
• Pronounced as “S-Q-L” or “Sequel.”
SQL Continued
⚫ Based on relational algebra, but not entirely identical.
⚫ Relations ⇔ Tables
⚫ Tuples ⇔ Rows
⚫ Attributes ⇔ Columns
⚫ Unlike a relation, a table is not a set. Duplicates are not
automatically removed.
⚫ This is for practical reasons. Duplicate eliminations are
inefficient in implementation.
⚫ Like a relation, the order of rows in a table is irrelevant.
AIRPORT
airportcode name city state

FLT-SCHEDULE

flt# airline dtime from-airportcode atime to-airportcode miles price

FLT-WEEKDAY

flt# weekday

FLT-INSTANCE

flt# date plane# #avail-seats

AIRPLANE

plane# plane-type total-#seats

CUSTOMER

cust# first middle last phone# street city state zip

RESERVATION

flt# date cust# seat# check-in-status ticket#

Components of SQL Standard
⚫ Both as a DDL and DML language.
⚫ DDL (Data Definition Language): define the schema of the
database.
⚫ DML (Data Manipulation Language): provides commands
to manipulate the database (query, insert, update, delete).
⚫ Data Control Language (DCL)
Commands that control a database, including
administering privileges and committing data
⚫ Embedded SQL and Dynamic SQL
Specifies how SQL commands can be embedded in a
high-level host language such as C, C++ or
Java for programmatic access to the data.
⚫ Transaction Control
Specifies how transactions can be started / stopped,
how a set of concurrently executing transactions
can be managed.
⚫ Authorization
Specifies how to restrict a user / set of users to access
only certain parts of data, perform only certain types of
queries etc.
⚫ Views
⚫ Integrity
8
⚫ INT - A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from
-2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a
width of up to 11 digits.
⚫ TINYINT - A very small integer that can be signed or unsigned. If signed, the allowable range is from
-128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
⚫ SMALLINT - A small integer that can be signed or unsigned. If signed, the allowable range is from
-32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5
digits.
⚫ MEDIUMINT - A medium-sized integer that can be signed or unsigned. If signed, the allowable range is
from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a
width of up to 9 digits.
⚫ BIGINT - A large integer that can be signed or unsigned. If signed, the allowable range is from
-9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to
18446744073709551615. You can specify a width of up to 20 digits.
⚫ FLOAT(M,D) - A floating-point number that cannot be unsigned. You can define the display length (M)
and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of
decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places
for a FLOAT.
⚫ DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define the
display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4
is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for
DOUBLE.
⚫ DECIMAL(M,D) - An unpacked floating-point number that cannot be unsigned. In unpacked decimals,
each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is
required. NUMERIC is a synonym for DECIMAL.
 Date and Time Types:

⚫ DATE - A date in YYYY-MM-DD format, between 1000-01-01 and

9999-12-31. For example, December 30th, 1973 would be stored as
1973-12-30.
⚫ DATETIME - A date and time combination in YYYY-MM-DD
HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31
23:59:59. For example, 3:30 in the afternoon on December 30th, 1973 would
be stored as 1973-12-30 15:30:00.
⚫ TIMESTAMP - A timestamp between midnight, January 1, 1970 and
sometime in 2037. This looks like the previous DATETIME format, only
without the hyphens between numbers; 3:30 in the afternoon on December
30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS
).
⚫ TIME - Stores the time in HH:MM:SS format.
⚫ YEAR(M) - Stores a year in 2-digit or 4-digit format. If the length is
specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to
69). If the length is specified as 4, YEAR can be 1901 to 2155. The default
length is 4.
⚫ CHAR(M) - A fixed-length string between 1 and 255 characters in length (for example CHAR(5)),
right-padded with spaces to the specified length when stored. Defining a length is not required, but
the default is 1.
⚫ VARCHAR(M) - A variable-length string between 1 and 255 characters in length; for example
VARCHAR(25). You must define a length when creating a VARCHAR field.
⚫ BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large
Objects" and are used to store large amounts of binary data, such as images or other types of files.
Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts
and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT
fields. You do not specify a length with BLOB or TEXT.
⚫ TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255
characters. You do not specify a length with TINYBLOB or TINYTEXT.
⚫ MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of
16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.
⚫ LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of
4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.
⚫ ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you are
creating a list of items from which the value must be selected (or it can be NULL). For example, if
you wanted your field to contain "A" or "B" or "C", you would define your ENUM as ENUM ('A',
'B', 'C') and only those values (or NULL) could ever populate that field.
Basic DDL Commands in SQL
⚫ CREATE: to define new tables (to define relation
schemas)
⚫ DROP: to delete table definitions (to delete relation
schemas)
⚫ ALTER: to change the definitions of existing tables (to
change relation schema)
⚫ Other features as DDL
⚫ Specify referential integrity constraints (FKs)
⚫ Specify user-defined attributes constraints
 SQL: CREATE TABLE Statement
• Things to consider before you create your table are:
The type of data
•

the table name

•

what column(s) will make up the primary key

•

the names of the columns

•

• CREATE TABLE statement syntax:

CREATE TABLE <table name>

( field1 datatype ( NOT NULL ),
field2 datatype ( NOT NULL )
);
 Primary & Foreign Keys
Primary Keys
▪ 1 or More Columns Used to Uniquely Identify a record.
▪ All Columns Defined as PK’’s MUST be populated

Foreign Keys
Value on a table that references a Primary Key from a different
table
 BASIC SQL COMMANDS
CREATING A TABLE WITH PRIMARY KEY
Syntax:
Create table <Table Name>
( <Field1> <Type> <(width)> Constraint <constraint name> Primary Key,
<Field2> <Type> <(width)>,
..................................) :

Example:
Create table student
(Reg_no int Constraint student_Reg_pk primary key,
Name Varchar(25),
Class Varchar (5)
);
16
 BASIC SQL COMMANDS
CREATING A TABLE WITH COMPOSITE PK
Syntax:
Create table <Table Name>
( <Field1> <Type> <(width)> ,
<Field2> <Type> <(width)>,
..................................<field-n> <type-n> <width-n>,
Constraint <constraint-name> Primary Key (Field1, Field2)) :
Example:
Create table student_table3
(
Reg_no int, Name varchar (25), Class varchar (5),
Constraint student_Reg_Class_pk primary key (Reg_no)
); 17
 BASIC SQL COMMANDS
CREATING A TABLE WITH FOREIGN KEY
Create table student_table2
( R_no int,
Name varchar (25),
Class varchar(5),
index (R_No),
constraint u_pk primary key(R_no) ,
constraint u_fk Foreign Key (R_no) References student_table3(Reg_No)
);

18
Points to remember while creating table
⚫ Creates a table with one or more columns of the specified
dataType.
⚫ With NOT NULL, system rejects any attempt to insert a
null in the column.
⚫ Can specify a DEFAULT value for the column.
⚫ Primary keys should always be specified as NOT NULL.
⚫ FOREIGN KEY clause specifies FK along with the
referential action
 Basic SQL Commands
▪ SELECT

▪ FROM table

▪ WHERE [conditions]

▪ ORDER BY [columns]

▪;
Defines the end of an SQL statement
Some programs require it, some do not
Needed only if multiple SQL statements run in a script
SELECT Statement
▪ SELECT Statement Defines WHAT is to be returned
(separated by commas)
o Database Columns (From Tables or Views)
o Constant Text Values
o Formulas
o Pre--defined Functions
o Group Functions (COUNT, SUM, MAX, MIN, AVG)

▪ “*” Mean All Columns From All Tables In the FROM

Statement

▪ Example:
a) Select * From Student where course= "BTech";
b) Select Name, Regno From Student where course= "BTech";
FROM Statement
▪ Defines the Table(s) or View(s) Used by the SELECT or WHERE Statements
▪ You MUST Have a FROM statement
▪ Multiple Tables/Views are separated by Commas

WHERE Clause
▪ Optional
▪ Defines what records are to be included in the query
▪ Uses Conditional Operators
▪ =, >, >=, <, <=, != (<>)
▪BETWEEN x AND y
▪IN (list)
▪LIKE ‘‘%string’’ (“%” is a wild--card)
▪IS NULL
▪NOT {BETWEEN / IN / LIKE / NULL}
▪ Multiple Conditions Linked with AND & OR Statements
▪ Strings Contained Within SINGLE QUOTES!!
AND & OR
▪ Multiple WHERE conditions are Linked by AND / OR Statements
▪ “AND” Means All Conditions are TRUE for the Record
▪ “OR” Means at least 1 of the Conditions is TRUE
▪ You May Group Statements with ( )
▪ BE CAREFUL MIXING “AND” & “OR” Conditions

ORDER BY Statement
▪ Defines How the Records are to be Sorted
▪ Must be in the SELECT statement to be ORDER BY
▪ Default is to order in ASC (Ascending) order
▪ Can Sort in Reverse (Descending) Order with “DESC” After the
Column Name

Group Functions
▪ Performs Common Mathematical Operations on a Group of Records
▪ Must define what Constitutes a Group by Using the GROUP BY
Clause
▪ All non--Group elements in the SELECT Statement Must be in the
GROUP BY Clause (Additional Columns are Optional)
 General Structure

Eg. List all the student records.

SELECT * FROM student;

Result
 General Structure
eg. List the names, id of 1A students with no fee
remission.
SELECT name, id, class FROM student ;
WHERE class="1A" AND NOT remission

Result
 SQL: ALTER TABLE Statement
• To add or drop columns on existing tables.

• ALTER TABLE statement syntax:

Syntax:
Alter Table <Table-Name>
Add <Field Name> <Type> (width);
Example:
CREATE TABLE FoodCart (
date DATE, FoodCart
food varchar(20), date food profit
profit float
);

FoodCart
ALTER TABLE FoodCart
date food profit sold
ADD sold int;

FoodCart
ALTER TABLE FoodCart
DROP COLUMN profit; date food sold

DROP TABLE FoodCart;

Adding the Dno FK to EMPLOYEE
⚫ If “create table EMPLOYEE” is issued first, we cannot
specify Dno as a FK in that create command.
⚫ An ALTER command must be used to change the schema of
EMPLOYEE, after the “create table DEPARTMENT,”
to add a FK.

alter table EMPLOYEE

add constraint
foreign key (Dno)
references DEPARTMENT(Dnumber);
 Adding the Default Constraint
⚫ The DEFAULT constraint can also be used to insert system
values, by using functions like GETDATE():
CREATE TABLE Employee
(
Id int,
Name varchar(25),
City varchar(25),
JoiningDate date DEFAULT GETDATE()
);
⚫ DEFAULT Constraint on ALTER TABLE
ALTER TABLE Employee
ALTER City SET DEFAULT ‘India' ;
 DROP
To Delete a Table along with all contents: Remove
the table completely from the database

Syntax:
Drop Table <Table-Name>;
Example:
Drop Table student;

TRUNCATE
▪Quicker way for deleting all the rows froma table
▪ It releases the space used by the table
▪The operation cannot be rolled back

Syntax:
Truncate Table TableName;
Eg. : Truncate table employee;
30
 SQL: DML Commands
• INSERT: adds new rows to a table.

• UPDATE: modifies one or more attributes.

• DELETE: deletes one or more rows from a table.

 SQL: INSERT Statement
•To insert a row into a table, it is necessary to have a value
for each attribute, and order matters.
•INSERT statement syntax:
INSERT INTO table_name (column, …, column)
VALUES (value, …, value);
Example: INSERT into FoodCart
VALUES (’02/26/08', ‘pizza', 70 );

FoodCart
date food sold
date food sold 02/25/08 pizza 350
02/25/08 pizza 350 02/26/08 hotdog 500
02/26/08 hotdog 500 02/26/08 pizza 70
 INSERT (Cont.)
● String data must be enclosed in single quotes
● Numbers are not quoted

● Inserting into a table

– Insert into employe (emp_Name, Dept_no, gender, salary)
Values (‘Sara johns’, 1, ‘F’, 1440);

● You can omit the column names if you supply a value for every column
– Insert into employe Values (‘Suzy Alan’, 10, ‘F’, 1200);
● Inserting from another table

– INSERT INTO emp_senior

select * from employee where age > 60;
The main condition in this case, that both tables has the same attributes and ordered in
the same order
 INSERT (Cont.)
● Inserting into a table with null attributes
● INSERT INTO table1 values ("A001","Jodi","London",.12,NULL);

● Inserting into a table with default attributes

● insert into table1 (field1, field3) values (5,10) (other columns will be set to default if
defined)
● insert into table1 values (5, DEFAULT, 10, DEFAULT)
 SQL: UPDATE Statement
To update the content of the table:
•

UPDATE statement syntax:

UPDATE <table name> SET <column> = <value>
WHERE <selection condition>;
Example: UPDATE FoodCart SET sold = 349
WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart

date food sold date food sold

02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
 SQL: DELETE Statement
To delete rows from the table:
•

DELETE statement syntax:

DELETE FROM <table name>
WHERE <condition>;
Example: DELETE FROM FoodCart where food = ‘hotdog’;

FoodCart

date food sold date food sold

02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 pizza 70
02/26/08 pizza 70
 Delete cont.
● Delete certain rows (depending on a condition)
– Delete from employee where age<30;

● Delete all rows

– Delete from employee;

Delete Syntax:
Delete from TableName;
Eg. : Delete from employee;
▪ Just deleting all the rows from a table leaves a “blank” table with column
names and types
▪ After performing a DELETE operation you need to COMMIT or ROLLBACK
the transaction to make the change permanent or to undo it.
The SELECT DISTINCT Statement

⚫ The DISTINCT keyword is used to return only distinct

(different) values.
Syntax: SELECT DISTINCT column_name(s)
FROM table_name;

⚫ To select only DIFFERENT values from the column named

"Company" we use a SELECT
⚫ DISTINCT statement like this:
SELECT DISTINCT Company FROM Orders;
The LIKE Condition
⚫ The LIKE condition is used to specify a search for a pattern in a column.
⚫ A "%" sign can be used to define wildcards (missing letters in the pattern)
both before and after the pattern.
Syntax : SELECT column FROM table
WHERE column LIKE pattern

⚫ The following SQL statement will return persons with first names that end
with an 'a':
SELECT * FROM Persons
WHERE FirstName LIKE '%a‘ ;

⚫ The following SQL statement will return persons with first names that
contain the pattern 'la':
SELECT * FROM Persons
WHERE FirstName LIKE ‘%la%’ ;
IN Operator
⚫ SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
SQL ALIAS
SQL aliases are used to temporarily rename a table or a
column heading.
⚫ Column Name Alias
The syntax is:
SELECT column AS column_alias FROM table

⚫ Table Name Alias

The syntax is:
SELECT column FROM table AS table_alias
Note: Mostly used with join queries.
NULL indicates that something has no value

•For instance, looking for cars without owners…

Wrong: SELECT regno from car where owner = NULL
Wrong: SELECT regnofrom car where owner = ‘NULL’

•Instead there are two special operators, IS NULL, and IS NOT

NULL

SELECT regno from car WHERE OWNER is null;

SELECT regnofrom car WHERE OWNER is not null;
 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
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum

⚫ The syntax for using functions is,

SELECT function type(column_name)
FROM table_name;
⚫ AVG() Syntax
⚫ SELECT AVG(column_name) FROM table_name;

⚫ COUNT(column_name)
⚫ 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;

⚫ COUNT(*)
⚫ The COUNT(*) function returns the number of records in a table:

⚫ SELECT COUNT(*) FROM table_name;

⚫ COUNT(DISTINCT column_name)
⚫ The COUNT(DISTINCT column_name) function returns the number of
distinct values of the specified column:
⚫ SELECT COUNT(DISTINCT column_name) FROM table_name;
GROUP BY Syntax
The SQL GROUP BY clause is used in collaboration with
the SELECT statement to arrange identical data into groups.

SELECT column name,

aggregate function(column name)
FROM table name
WHERE conditions
GROUP BY column name;
 Aggregate functions cont.
Consider a flight database.
⚫ “Count flights scheduled for Tuesdays from FLT-WEEKDAY”
SELECT COUNT( *)
FROM FLT-WEEKDAY
WHERE WEEKDAY = “TU”;
⚫ “Find the average ticket price of airline from FLT-SCHEDULE”
SELECT AIRLINE, AVG(PRICE)
FROM FLT-SCHEDULE
GROUP BY AIRLINE;
HAVING
⚫ The HAVING clause enables you to specify conditions that
filter which group results appear in the final results.
⚫ The WHERE clause places conditions on the selected
columns, whereas the HAVING clause places conditions on
groups created by the GROUP BY clause.
⚫ The syntax for the HAVING function is:
SELECT column, SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
Example:
SELECT Company, SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000
Summary of SQL Queries
⚫ The SELECT-clause lists the attributes or functions to be
retrieved
⚫ The FROM-clause specifies all relations (or aliases) needed in
the query but not those needed in nested queries
⚫ The WHERE-clause specifies the conditions for selection and
join of tuples from the relations specified in the FROM-clause
⚫ GROUP BY specifies grouping attributes
⚫ HAVING specifies a condition for selection of groups
⚫ ORDER BY specifies an order for displaying the result of a
query
⚫ A query is evaluated by first applying the WHERE-clause, then
GROUP BY and HAVING, and finally the SELECT-clause

Slide 8-51