Structured Query Language

SQL is a standard language for storing, manipulating and retrieving data in databases. Although SQL
is an ANSI/ISO standard, there are different versions of the SQL language, used by various RDBMS’s.
However, to be compliant with the ANSI standard, they all support at least the major commands (such
as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Advantages of using SQL:
1) SQL is portable: SQL is running in all servers, mainframes, PCs, laptops, and even mobile phones.
2) High speed: SQL queries can be used to retrieve large amounts of records from a database quickly
and efficiently.
3) Easy to learn and understand: SQL generally consists of English statements and as such, it is very
easy to learn and understand. Besides, it does not require much coding unlike in programming
languages.
4) SQL is used with any DBMS system with any vendor: SQL is used by all the vendors who develop
DBMS. It is also used to create databases, manage security for a database, etc. It can also be used for
updating, retrieving and sharing data with users.
5) SQL is used for relational databases: SQL is widely used for relational databases.
6) SQL acts as both programming language and interactive language: SQL can do both the jobs of
being a programming language as well as an interactive language at the same time.
7) Client/Server language: SQL is used for linking front end computers and back end databases. It
provides client server architecture (Email, and the World Wide Web - all apply the client-server
architecture).
8) Supports object based programming: SQL supports the latest object based programming and is
highly flexible
Types of SQL statements
The SQL statements are categorized into different categories based upon the purpose. They are;
i) Data Definition Language (DDL) statement
ii) Data Manipulation Language (DML) statement
iii) Transaction Control Statement
iv) Session Control Statement
v) System Control Statement
vi) Embedded SQL Statement
Out of these six, we will be studying only the first two types in this course.
1. DATA DEFINITION LANGUAGE (DDL) COMMANDS:
A database scheme is specified by a set of definitions which are expressed by a special language called
a data definition language(DDL). The result of DDL statements is a set of tables which are stored in a
special file called data dictionary.
A Data Dictionary is a file that contains “metadata” i.e., “data about data”.
The DDL provides a set of definitions to specify the storage structure and access methods used by the
database system.
Listed are few ideal DDL functions:
1. It should identify the types of data division such as data item, segment, record, and data-base file.
2. It should give a unique name to each data-item-type, record-type, file-type, database, and other data
subdivision.
3. It should specify the proper data types.
4. It should specify how the record types are related to each other.
5. It may define the type of encoding the program uses in the data items, binary, character, bit, string
etc.
Data Definition Language (DDL) commands allow us to perform tasks related to data definition. One
can perform the following tasks:
i. Create, alter and drop schema objects - DDL commands are used to create, define, change or
delete objects such as a table, a view or an index etc.
ii. Grant and revoke privileges and roles - DDL commands are used to grant or revoke permissions
or privileges to work upon schema objects.
iii. Maintenance commands - These commands are used to analyze information on a table with an
aim of maintaining it.
2. DATA MANIPULATION LANGUAGE (DML) COMMANDS
Data manipulation means:
Retrieval of information stored in database
Insertion of new information into database
Deletion of information from database
Modification of data stored in database
A Data Manipulation Language (DML) is a language that enables user to access or manipulate data as
organized by the appropriate data model. DML commands query and manipulate data in existing
schema objects. Some DML commands are as following:
INSERT INTO: Used to insert a tuple in a table.
UPDATE: Used to modify a tuple in a table.
DELETE: Used to delete a tuple in a table.
Other examples of DML commands are: SELECT, LOCK TABLE etc.
Types of DMLs:
1. Procedural DMLs - These require a user to specify what data is needed and how to get it.
2. Non-Procedural DMLs - These require a user to specify what data is needed without specifying
how to get it.

Concept Of Data Types

Each column in a database table is required to have a name and a data type. An SQL developer must
decide what type of data that will be stored inside each column when creating a table. The data type is
a guideline for SQL to understand what type of data is expected inside of each column, and it also
identifies how SQL will interact with the stored data.

In ANSI SQL there are three main data types: string, numeric, and date and time.
 Conventions
and Terminology used in SQL Query:
Keywords: Keywords are words that have a special meaning and printed in capital letters. They are
understood to be instruction.
Commands or Statements: Commands are instruction given by us to a SQL database. Commands
consist of one or more logically distinct parts called clauses.
Clauses: Clauses begin with a keyword for which they are generally named, and consist of
keywords and arguments.
Arguments: Argument complete or modify the meaning of a clause.

Symbols used in Syntax Statements:

Symbol Meaning
| Symbolic way of saying “or” i.e., whatever precedes this symbol may optionally be replaced
by whatever follows it.
{} Everything enclosed in it, is treated as a unit for the purpose of evaluating |, . , . . Or other
symbols.
[] Everything enclosed in it is optional.
… This means whatever precedes it may be repeated any number of times.
.,.. Whatever precedes this, may be repeated any number of times with the individual occurrence
separated by commas.
<> SQL and other special terms are in angle brackets.
VARIOUS SQL COMMANDS AND FUNCTIONS
1. Creating Databases:
The CREATE DATABASE command is used to create a database.
Syntax: CREATE DATABASE [IF NOT EXISTS] <database name>;
The IF NOT EXISTS clause, if used, will first test whether a database by mentioned name already
exists or not. If exists, then create database command is simply ignored, otherwise a database with the
mentioned name is created.
Create database if not exists SampleDB;
2. Opening Databases:
To perform operation on database we first need to open database .
Syntax: USE <database name>;
Example: USE SampleDB;
Before opening a database we need to ensure that it must already exist.
To check the names of existing database, following command is used:
SHOW DATABASES;

4. CREATE TABLE Command:

The CREATE TABLE statement is used to create a table in a database.
Each table must have at least one column.
Syntax: CREATE TABLE <table-name>
( <column name> <data type> [ (size)], <column name> <data type> [ (size) …] );
Example: CREATE TABLE emp
( Eno integer, Ename char(20), Job char(20), Mgr integer(10),
Hiredate date, Sal decimal, Comm decimal, Dno integer );

In the following sections we assume that we have database named SampleDB in which there
are two tables namely EMP and DEPT.
CREATE TABLE emp ( Eno integer PRIMARY KEY ,
Ename char(20),
Job char(20), Salary decimal CHECK > 2000 );

Inserting Data Into Table:

A. INSERT INTO Command:
INSERT INTO command is used to add rows to relations.
Syntax: INSERT INTO <tablename> [ <column list> ]
VALUES ( <value>, <value> …. );
Example: INSERT INTO emp
VALUES (142, ‘SAMIR’, ‘CLERK’, 125, 21-MAY-81, 5000, 500, 20);
Above statement insert a row in to emp table. The same can be done with the alternative command as
shown below:
Example: INSERT INTO emp (eno, ename, job, mgr, hiredate, sal, comm, dno)
VALUES (142, ‘SAMIR’, ‘CLERK’, 125, 21-MAY-81, 5000, 500, 20);
Note that the data values are in the same order as the column names in the table. Data can be added
only
to some columns in a row by specifying the columns and their data as shown below:
Example: INSERT INTO emp (eno, ename, job)
VALUES (199, ‘TEENA’, ‘MANAGER’, );
 In an INSERT statement, only those columns can be skipped that columns have either default value
defined
or they allow NULL values.
B. Inserting NULL values:
NULL value can be inserted in to a column by typing NULL without quotes.
Example: INSERT INTO emp (eno, ename, job, mgr, hiredate, sal, comm, dno)
VALUES (142, ‘SAMIR’, NULL, 125, 21-MAY-81, 5000, NULL, 20);
Here, for Mgr and Comm columns, NULL values have been inserted.
C. Inserting Data from Another Table:
INSERT INTO can also be used to take values from one table and place them in another table.
Example: INSERT INTO emp (eno, ename, sal)
SELECT emp_no, ename, sal FROM Temp WHERE Months > 24 ;
It will extract all those rows from Temp that have months more than 24 and insert this produced
result, into the table emp.
To insert using a query, the following conditions must be true:
1. Both the tables must be already created.
2. The columns of the tables being inserted into, must match the columns output by the subquery.
The SELECT Command:
The SELECT statement can be used to retrieve a subset of rows or columns from one or more tables.
Syntax: SELECT <column name> [, <column name>, … ]
FROM <table name> ;
Example : SELECT ename, job, sal FROM emp;
Above statement display the information of columns ename, job and sal as shown below:
Selecting All Columns:
To select all the columns from the table the asterisk (*) can be used.
Example : SELECT * FROM emp;
Above statement display all the rows present in the emp table.
Eliminating Redundant Data - DISTINCT keyword :
The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement.
Only one NULL value is returned in the results with DISTINCT keyword.
DISTINCT applies to entire output row, not a specific field.
The DISTINCT keyword can be specified only once in a given SELECT clause.
Example : SELECT DISTINCT job
FROM emp;
Above statement display job column as shown below:
DISTINCT Job

MANAGER

CLERK

SALSEMAN

ANALYST

PRESIDENT

5 rows in a set (0.03 sec)

Selecting Specific Rows – WHERE clause:
The WHERE clause in SELECT statement specifies the criteria for selection of rows to be returned.
 With the use of WHERE clause, only those rows that satisfied the given condition are displayed in
the output.
Syntax: SELECT <column name> [, <column
name>, ….. ]
FROM <table name>
WHERE <condition> ;
Example : SELECT eno, ename, sal
FROM emp
WHERE sal < 1200 ;
Above statement display the eno, ename and sal for employees having salary less than 1200.
Relational Operator:
Relational operator is used to compare two values.
Result of comparison is true or false.
The SQL recognizes following relational operators:
=, >, <, >=, <=, <> (not equal to)
In CHARACTER data type comparison, < means earlier in the alphabet and > means later in the
alphabet.
Apostrophes are necessary around all CHAR, DATE and TIME data.
Example : SELECT eno, ename, job
FROM emp
WHERE job <> ‘CLERK’;
Above statement display the eno, ename and sal for employees not having job as clerk
Logical Operators:
The logical operators OR (||), AND (&&), and NOT (!) are used to connect search conditions in the
WHERE clause.
When all the logical operators are used together, the order of precedence is NOT (!), AND (&&),
and OR(||).
Example :
1. To list the employee’s details having Job as ‘CLERK’ or ‘MANAGER’ from table emp, logical
operator OR
will be used as:

SELECT *
FROM emp
WHERE ( job = ‘CLERK’ OR job = ‘MANAGER’ );
2. To list the employee’s details having Job as ‘CLERK’ but salary < 1000 from table emp, logical
operator AND
will be used as:
SELECT eno, ename, job, sal
FROM emp
WHERE ( job = ‘CLERK’ AND sal < 1000);
3. To list the employee’s details having Job is other than ‘CLERK’ from table emp, logical operator
NOT will be
used as:
SELECT eno, ename, job, sal
FROM emp
WHERE ( NOT job = ‘CLERK’ );

LIKE OPERATOR
LIKE OPERATOR is used to search a value similar to specific pattern in a column using wildcard
operator. There are two wildcard operators - percentage sign (%) and underscore ( _ ). The percentage
sign represents zero, one, or multiple characters, while the underscore represents a single number or
character. The symbols can be used in combinations.
Adno Name Class Section Fees

111 Anu Jain 12 A 2500

222 Mohit Sharma 11 B 4500

333 K.P.Gupta 12 B 3000

444 Ajit Kumar 10 A 2000

555 Nandini 12 C 3000

666 Rohan Sharma 11 B 2500

For example:
1. Display the names that start with letter "A".
SELECT name
FROM student
WHERE name LIKE "A%";
Here, % replaces one or more characters.
Name
Anu Jain
Ajit Kumar
2. Display names, whose name's second letter is 'o'.
SELECT name
FROM student
WHERE name LIKE "_ o%";
Here, % replaces one or more than one character and _ replaces only one character.
Name
Mohit Sharma
Rohan Sharma
3. Display names, whose name has 7 characters.
SELECT name
FROM student
WHERE name LIKE "_______";
Here, _ replaces only one character. As such, 7 underscores replace 7 characters.
Name
Nandini
IN Operator
The IN operator allows us to specify multiple values in a WHERE clause
For example:
Display students' information, who are in section A and B.
SELECT *
FROM student
WHERE section IN ("A","B");
Adno Name Class Section Fees

111 Anu Jain 12 A 2500

222 Mohit Sharma 11 B 4500

333 K.P.Gupta 12 B 3000

444 Ajit Kumar 10 A 2000

666 Rohan Sharma 11 B 2500

BETWEEN Operator
The BETWEEN operator is used to test whether or not a value (stated before the keyword
BETWEEN) is "between" the two values stated after the keyword BETWEEN.
For example:
Display students' information, who are paying fees between 2500 and 3500.
SELECT * FROM student
WHERE fees BETWEEN 2500 AND 3500;
[Note: In the above Query 2500 and 3500 is also included]
Adno Name Class Section Fees

111 Anu Jain 12 A 2500

333 K.P.Gupta 12 B 3000

444 Ajit Kumar 10 A 2000

555 Nandini 12 C 3000

666 Rohan Sharma 11 B 2500