Introduction to SQL

• SQL – structured query language.

• It is composed of commands that enable users to
create database and table structures, perform
various types of data manipulation and data
administration, and query the database to extract
useful information.
• All relational DBMS software supports SQL.
• SQL is a nonprocedural language; that is, the user
specifies what must be done, but not how it is to be
done.
 Database language
• A database language allows a user to create database and
table structures, to perform basic data management chores
(add, delete, and modify), and to perform complex queries
designed to transform the raw data into useful information.
• A database language must perform such basic functions with
minimal user effort, and its command structure and syntax
must be easy to learn.
• Finally, it must be portable; that is, it must conform to some
basic standard so that an individual does not have to relearn
the basics when moving from one RDBMS to another.
 SQL
• It is a data definition language (DDL): SQL includes
commands to create database objects(such as
tables, indexes, and views), as well as commands
to define access rights to those database objects.
• It is a data manipulation language (DML): SQL
includes commands to insert, update, delete, and
retrieve data within the database tables.
• A schema is a group of database objects such as
tables and indexes that are related to each other.
DDL commands
DML commands
DML commands continued…
 Constraints
• The NOT NULL constraint ensures that a column
does not accept nulls.
• The UNIQUE constraint ensures that all values
in a column are unique.
• The DEFAULT constraint assigns a value to an
attribute when a new row is added to a table.
• The CHECK constraint is used to validate data
when an attribute value is entered. it checks to
see that a specified condition exists.
 Creating a table
• CREATE TABLE tablename (
column1 data type constraint,
column2 data type constraint,
column2 data type constraint,
PRIMARY KEY (column1),
FOREIGN KEY (column 2) REFERENCES tableX);
NOTE: Do not use mathematical symbols such as +, −,
and / in your column names; instead, use an
underscore to separate words, if necessary.
 Select query
select attributes
from relation
where condition;

The select clause is used to list the attributes

desired in the result of a query.
 Adding Table Rows
Syntax:
INSERT INTO tablename VALUES (value1, value2, ... , valuen);
Inserting Rows with Null Attributes:
INSERT INTO tablename VALUES (value1, value2, NULL,... ,
valuen);
Inserting Rows with Optional Attributes:
INSERT INTO tablename(column1,column2) VALUES (value1,
value2);
 Listing Table Rows
The SELECT command is used to list the contents of a table.
Syntax:
– SELECT column1 FROM tablename
– SELECT column1,column2,…,columnN FROM tablename
– SELECT * FROM tablename
– SELECT * FROM tablename WHERE conditionlist
– SELECT column1,column2,…,columnN FROM tablename
WHERE conditionlist
An asterisk denotes “all attributes”
 Listing Table Rows
Select all records from the loans table
SELECT *
FROM loan;
Find the names of all branches in the loan
relation.
SELECT branch_name
FROM loan;
 Updating Table Rows
The UPDATE command is used to modify data in
a table.
Syntax:
UPDATE tablename
SET column1 = expression1, column2 = expression2
WHERE conditionlist;
 Deleting Table Rows
The DELETE command is used to delete rows.
Syntax:
DELETE FROM tablename
WHERE conditionlist;
 Saving Table Changes
• The COMMIT command permanently saves all
changes—such as rows added, attributes
modified, and rows deleted—made to any
table in the database.
• Syntax:
COMMIT [WORK];
 Restoring Table Contents
ROLLBACK undoes any changes since the last
COMMIT command and brings the data back to
the values that existed before the changes were
made.
Syntax:
ROLLBACK;
Note: COMMIT and ROLLBACK work only with
data manipulation commands that are used to
add, modify, or delete table rows.
 Comparison operators
Comparison operators are used in the condition
clause of the query.
Syntax:
SELECT *
FROM students
WHERE course = ‘DICTM’
When using a character/string attribute the
quotation mark is used
 Using Computed Columns and
Column Aliases
Supposing you want to determine the total value of each of
the products held in an inventory.
SELECT p_code, p_name, p_quantity,p_price,
p_quantity * p_price
FROM products
OR:
SELECT p_code, p_name, p_quantity,p_price,
p_quantity * p_price as TOTALVALUE
FROM products
 Arithmetic Operators: The Rule of
Precedence
1. Perform operations within parentheses.
2. Perform power operations.
3. Perform multiplications and divisions.
4. Perform additions and subtractions.
 Special Operators
These special operators are used in conjunction with the WHERE
clause.
•BETWEEN: Used to check whether an attribute value is within a
range.
•IS NULL: Used to check whether an attribute value is null
•LIKE: Used to check whether an attribute value matches a given
string pattern.
•IN: Used to check whether an attribute value matches any value
within a value list.
•EXISTS: Used to check whether a subquery returns any rows.
 Special operators cont…
The LIKE special operator is used alongside wildcards to
find patterns within strings.
The percent sign (%) and underscore (_) wildcard
characters to make matches when the entire string is not
known:
• % means any and all following or preceding characters
are eligible. Example: Ja% includes jane, james etc
• _ means any one character may be substituted for the
underscore. Example 123_ includes 1234, 1235, 1238
etc.
 Ordering a Listing
The ORDER BY clause is especially useful when the listing
order is important to you.
Syntax:
SELECT columnlist
FROM tablelist
WHERE conditionlist
ORDER BY columnlist ASC/DESC ;
In the order by clause specify either ascending or
descending but not both.
By default the ascending order is used if you don’t specify.
 Grouping Data
Frequency distributions can be created quickly
and easily using the GROUP BY clause within the
SELECT statement.
Syntax:
SELECT columnlist
FROM tablelist
WHERE conditionlist
GROUP BY columnlist;
Deleting a Table from the Database
The DROP operator is used to delete a table
Syntax:
DROP TABLE tablename;
 Union
The UNION statement combines rows from two or
more queries without including duplicate rows. The
syntax of the UNION statement is:
 query UNION query
Example:
SELECT column-list FROM T1
UNION
SELECT column-list FROM T2
UNION
SELECT column-list FROM T3
 Union explained..
Suppose SaleCo has bought another company. SaleCo’s
management wants to make sure that the acquired
company’s customer list is properly merged with
SaleCo’s customer list. Because it is quite possible that
some customers have purchased goods from both
companies, the two lists might contain common
customers. SaleCo’s management wants to make sure
that customer records are not duplicated when the two
customer lists are merged. The UNION query is a perfect
tool for generating a combined listing of customers—one
that excludes duplicate records.
 Intersect
The INTERSECT statement can be used to combine rows
from two queries, returning only the rows that appear in
both sets. The syntax for the INTERSECT statement is:
 query INTERSECT query
Example:
SELECT column-list FROM T1
INTERSECT
SELECT column-list FROM T2
INTERSECT
SELECT column-list FROM T3
 Intersect illustrated
• If SaleCo’s management wants to know which
customer records are duplicated in the
CUSTOMER and CUSTOMER_2 tables, the
INTERSECT statement can be used to combine
rows from two queries, returning only the
rows that appear in both sets.