Introduction To Structured Query Language (SQL)

1 Copyright © 20009 by Royal Institute of Information Technology

Introduction

SQL (Structured Query Language)

is a database sublanguage for querying and modifying relational databases. It was developed by IBM Research in the mid 70's and standardized by ANSI (American National Standards Institute) in 1986.

2 Copyright © 20009 by Royal Institute of Information Technology

SQL’s Two Function Categories 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.

Data Manipulation Language(DML) :

It includes commands to insert, update, delete. And retrieve data within the database tables.

3 Copyright © 20009 by Royal Institute of Information Technology

Some Common SQL Data Types

Data Type Numeric Format

NUMBER(L,D) INTEGER SMALLINT

Comments

NUMBER(7,2) indicates number that will be stored with two decimal places and many be up to six digits long, including the sign and the decimal place. E.g.: 12.32, 134.99

May be abbreviated as INT. Integers are (whole) counting numbers, so they cannot be used if you want to store numbers that require decimal places.

Like INTEGER, but limited to integer values up to six digits. If your integer values are relatively small, use SMALLINT instead of INT.

DECIMAL(L,D) Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller once are not. DECIMAL(9,2), DECIMAL(9), and DECIMAL are all acceptable.

Copyright © 20009 by Royal Institute of Information Technology (Continues) 4

Some Common SQL Data Types

(Continued)

Data Type Character Format

CHAR(L)

Comments

Fixed-length character data for up to 255 characters. If you store strings that are not as long as the CHAR parameter value, the remaining spaces are left unused. Therefore, if you specify CHAR(25), strings such as “Smith” and “RoyalEducation” are each stored as 25 characters. However, a U.S. area code is always three digits long, so CHAR(3) would be appropriate if you wanted to store such codes.

VARCHAR(L) or VARCHAR2(L) Variable-length character data. The designation VARCHAR(25) will let you store characters up to 25 characters long. However, VARCHAR will not leave unused spaces. Oracle users may use VARCHAR2 as well as VARCHAR.

Date

DATE Stores dates in the Julian date format.

5 Copyright © 20009 by Royal Institute of Information Technology

SQL Constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

When you create the column definition, it is known as column constraint.

When you use the CONSTRAINT keyword, it is known as table constraint. 6 Copyright © 20009 by Royal Institute of Information Technology

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Example CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) 7 Copyright © 20009 by Royal Institute of Information Technology

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only one primary key.

Example: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) 8 Copyright © 20009 by Royal Institute of Information Technology

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created: Example: CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) ) 9

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Example: CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) Copyright © 20009 by Royal Institute of Information Technology 10

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 single column it allows 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.

Example: CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) 11

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created: Example: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT ‘Gandhinagar' 12 Copyright © 20009 by Royal Institute of Information Technology

Adding Table Rows (INSERT)

The INSERT INTO statement is used to insert a new row in a table.

Syntax: INSERT INTO table_name VALUES (value1, value2, value3,...) Example: INSERT INTO Persons VALUES (1,‘Dhiraj', ‘Thakkar', ‘Sector 22', ‘Gandhinagar'); 13 Copyright © 20009 by Royal Institute of Information Technology

Listing Table Rows (SELECT)

The SELECT command is used to list contents of a table.

Syntax SELECT column_list FROM table_name The column_list represents one or more attributes, separated by commas. You could use the * (asterisk) as a wildcard character to list all attributes.

Syntax SELECT * FROM table_name 14 Copyright © 20009 by Royal Institute of Information Technology

Saving Table Changes (COMMIT)

The COMMIT Statement terminates the current transaction and makes all changes under the transaction persistent. It the database.

commits

the changes to Syntax COMMIT [WORK] WORK is an optional keyword that does not change the semantics of COMMIT.

15 Copyright © 20009 by Royal Institute of Information Technology

Updating table Rows (UPDATE)

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

Syntax UPDATE table_name SET column1=value, column2=value2,...

WHERE some_column=some_value Example: UPDATE Persons SET Address=‘Memnagar', City=‘Ahmedabad' WHERE FirstName=‘Dhiraj' AND LastName ='Thakkar ' 16 Copyright © 20009 by Royal Institute of Information Technology

Restoring Table Contents (ROLLBACK)

The ROLLBACK Statement terminates the current transaction and rescinds all changes made under the transaction. It

rolls back

the changes to the database.

Syntax: ROLLBACK [WORK] WORK is an optional keyword that does not change the semantics of ROLLBACK 17 Copyright © 20009 by Royal Institute of Information Technology

Deleting Table Rows (DELETE)

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

Syntax DELETE FROM table_name WHERE some_column=some_value Example: DELETE FROM Persons WHERE FirstName=‘Dhiraj’ AND LastName='Thakkar' 18 Copyright © 20009 by Royal Institute of Information Technology

Selecting Rows with Conditional Restrictions

This Restrictions is done by adding conditional restrictions to the SELECT statement, using the WHERE clause.

Syntax SELECT column_name(s) FROM table_name WHERE column_name operator value Example: SELECT * FROM Persons WHERE City=‘Gandhinagar‘ For Numeric Values: SELECT * FROM Persons WHERE Year=1965 19 Copyright © 20009 by Royal Institute of Information Technology

Arithmetic Operators: The Rule of Precedence

As we perform mathematical operations on attributes, remember the rules of precedence. As the name suggests, the rules of precedence are the rules that establish the order in which computations are completed.

For Example, note the order of the following computational sequence: 1.

Perform operations within parentheses.

2.

Perform power operations.

3.

Perform multiplications and divisions.

4.

Perform additions and subtractions (Continues) 20 Copyright © 20009 by Royal Institute of Information Technology

Arithmetic Operators: The Rule of Precedence

(Continued) The application of the rules of precedence will tell you that 8+2*5 = 8+10 = 18, But (8+2)*5 = 10*5 =50.

Similarly, 4+5^2*3 = 4+25*3 =79 but (4+5)^2*3 = 81*3 =243, while the operation expressed by (4+5^2) * 3 yield the answer (4+25)*3 = 29*3 = 87

Arithmetic Operator Description

+ / * ^ Add Substract Multiply Divide Raise to the power of (Some applications use ** instead of ^) Copyright © 20009 by Royal Institute of Information Technology 21

Logical Operators : AND, OR and NOT

The AND operator displays a record if both the first condition and the second condition is true.

The OR operator displays a record if either the first condition or the second condition is true Example: SELECT * FROM Persons WHERE FirstName =‘Dhiraj’ AND (LastName='Thakkar' OR FirstName='Ola') The NOT operator is typically used to find the rows that do not match a certain condition 22 Copyright © 20009 by Royal Institute of Information Technology

The BETWEEN Special Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 Example: SELECT * FROM Persons WHERE FirstName BETWEEN ‘Dev' AND ‘Dipak' 23 Copyright © 20009 by Royal Institute of Information Technology

The IS NULL Special Operator

Standard SQL allows the use of IS NULL to check for a null attribute value.

Example: P_Id ProductName 1 2 3 Jarlsberg Mascarpone Gorgonzola UnitPrice UnitsInStock UnitsOnOrder 10.45

32.56

15.67

16 23 9 15 20 SELECT ProductName,UnitPrice*(UnitsInStock + ISNULL(UnitsOnOrder,0)) FROM Products 24 Copyright © 20009 by Royal Institute of Information Technology

The LIKE Special Operator

The LIKE operator is used to search for a specified pattern in a column.

Syntax SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern Example: SELECT * FROM Persons WHERE City LIKE ‘a%' The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

25 Copyright © 20009 by Royal Institute of Information Technology

The IN Special Operator

The IN operator allows you to specify multiple values in a WHERE clause.

Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2,...) Example: SELECT * FROM Persons WHERE FirstName IN (‘Dhiraj', ‘Dev’) 26 Copyright © 20009 by Royal Institute of Information Technology

Changing a Column’s Data Type

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

Syntax: ALTER TABLE table_name MODIFY column_name datatype Example: ALTER TABLE persons MODIFY (YEAR DECIMAL(4)) 27 Copyright © 20009 by Royal Institute of Information Technology

Adding a Column

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

Syntax: ALTER TABLE table_name ADD column_name datatype Example: ALTER TABLE Persons ADD DateOfBirth date 28 Copyright © 20009 by Royal Institute of Information Technology

Dropping a Column

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

Syntax: ALTER TABLE table_name DROP column_name Example: ALTER TABLE Persons DROP COLUMN DateOfBirth 29 Copyright © 20009 by Royal Institute of Information Technology

Ordering a Listing (ORDER BY)

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

Syntax: SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC Example: SELECT * FROM Persons ORDER BY LastName 30 Copyright © 20009 by Royal Institute of Information Technology

Listing Unique Values

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

Syntax: SELECT DISTINCT column_name(s) FROM table_name Example: SELECT DISTINCT City FROM Persons Copyright © 20009 by Royal Institute of Information Technology 31

Aggregate Functions

Function

COUNT MIN MAX SUM AVG

Output

The number of rows containing non null values The minimum attribute value encountered in a given column The maximum attribute value encountered in a given column The sum of all values for a given column The arithmetic mean (average) for a specified column Copyright © 20009 by Royal Institute of Information Technology 32

COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

Syntax: SELECT COUNT(column_name) FROM table_name The COUNT(*) function returns the number of records in a table: The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column: 33 Copyright © 20009 by Royal Institute of Information Technology

MAX and MIN Function

The MAX() function returns the largest value of the selected column Syntax: SELECT MAX(column_name) FROM table_name The MIN() function returns the smallest value of the selected column.

Syntax: SELECT MIN(column_name) FROM table_name 34 Copyright © 20009 by Royal Institute of Information Technology

SUM() and AVG() Functions

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

Syntax: SELECT SUM(column_name) FROM table_name The AVG() function returns the average value of a numeric column.

Syntax: SELECT AVG(column_name) FROM table_name 35 Copyright © 20009 by Royal Institute of Information Technology

Outer Joins : LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Syntax: SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Copyright © 20009 by Royal Institute of Information Technology 36

Outer Joins : RIGHT JOIN

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Syntax: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name = table_name2.column_name

Copyright © 20009 by Royal Institute of Information Technology 37