Learning Objectives

At the end of this chapter the students will be able to understand:

1. What is SQL?
2. Need for SQL
3. How to create tables in SQL?
4. How to add information to tables?
5. SELECT … FROM…WHERE (with aggregate functions)
6. GROUP BY ….HAVING
7. ORDER BY
8. UPDATE AND DELETE Command
9. ALTER TABLE AND DROP TABLE Command
10.EQUI JOIN
SQL (Structured Query Language) is a standard language for accessing and
manipulating databases. SQL commands are used to create, transform and
retrieve information from Relational Database Management Systems and
used to create interface between user and database. By using SQL
commands, one can search any data in the database and perform other
functions like, create tables, add records, modify data, remove rows, drop
table etc. SQL commands are used to implement the following;

1. SQL can retrieve data from a database

2. SQL can insert records in a database
3. SQL can update records in a database
4. SQL can delete records from a database
5. SQL can create new databases
6. SQL can create new tables in a database
7. SQL can create views in a database
 Creating table in SQL
CREATE TABLE Command
CREATE TABLE command is used to create table structure. In this command, we need to
give full information about table such as number of columns, type of each column and
constraints (primary key).
The CREATE TABLE command requires:
1. Name of the table,
2. Names of fields,
3. Definitions and constrains for each field.

Constrains
In SQL, we have the following constraints:
1. NOT NULL - To check a column cannot store NULL value.
2. PRIMARY KEY - To check that a column have an unique identity which helps to
find a particular record in a table.
Syntax:
CREATE TABLE<table name>
(<column name1> <data type>[size][constraints],
<column name2> <data type>[size][constraints],
.
<column name n> <data type>[size][constraints]);
Column Name Data Type Size Constraints

Example: Adno Numeric 3 Primary key

Create the following table: Name Varchar 20 NOT NULL
Class Numeric 2
Table: Student Section Char 1
Command: Fees Numeric 10, 2
CREATE TABLE student
(Adno Numeric (3) Primary Key, Name varchar (20) not null, Class Numeric (2),
Section char (1),
Fees numeric (10, 2));
 INSERT INTO Command:
This command is used to add rows in the table, but can add only one row
at a time.
Syntax:
INSERT INTO <table name> [Column_name1, Column_name2,
......Column_name n] VALUES (value1,value2,value3,….,value n);
OR
INSERT INTO <table name> VALUES (value1,value2,value3,….,value n);
Note: [] Option

INSERT INTO student VALUES (111,"Anu Jain", 12,"A", 2500);

INSERT INTO student VALUES (222,"Mohit Sharma", 11,"B", 4500);
 SELECT Command
This command is used to view table information from SQL database. By
using SELECT command, we can get one or more fields information, while
using *, one can get all fields information.
Syntax:
SELECT (*/field list) FROM <table name> [WHERE <condition>];
We can specify any condition using where clause. Where clause is
optional.
Display student table information.
SELECT *
FROM student;
This will display all information of the table (student) in the database.
To display name and class of student table information.
SELECT name, class
FROM student;
 Operators used in SQL commands:
Arithmetic operators:
Arithmetic operator takes two operands and performs a mathematical
calculation on them. However, they can be used only in SELECT
command. The arithmetic operators used in SQL are:
Example (string join)

1. + Addition •Table: Name

2. - Subtraction Display first name with second name.
SELECT FirstName + SecondName FROM
3. Multiplication Name;
4. / Division
Output:
First Name Second Name FirstName + SecondName

Anu Jain Anu Jain

Madhu Bhattia Madhu Bhattia

 Basic DA Basic + DA
•Table: Salary
SELECT Basic + DA 25000 5000 30000
FROM Salary;
35000 7000 42000

NET PAY
SELECT Basic + DA as "NET PAY"
30000
FROM Salary;
Output: 42000
 Relational operators

Relational operators are used to implement comparison between two

operands. These operators can be used only in 'where clause'. Relational
operators are -
1. < less than
2. > greater than
3. < = less than or equal to
4. > = greater than or equal to
5. = equal to
6. ! = not equal to
 Example: Table: Student
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

1. Display students' name, who are paying below 3000 fees. SELECT name
FROM student WHERE fees<3000;

2. Display students' name, who are paying above or equal to 3000 fees. SELECT name
FROM student WHERE fees>=3000;

3. Display students' information, who are not in class 10 SELECT *

FROM student WHERE class! = 10;
 Logical operators:

Logical operators are also possible only in 'where clause' and are used to
merge more than one condition. Logical operators are:
1. AND
2. OR
3. NOT

Example:
Display information of students in class 11B.
Adno Name Class Section Fees
SELECT *
FROM student 222 Mohit 11 B 4500
Sharma
WHERE class = 11 AND section = 'B';
666 Rohan 11 B 2500
Sharma
 Logical operators

Display 11th and 12th class students' information.

SELECT *
FROM student
WHERE class =11 OR class=12;

Adno Name Class Section Fees

111 Anu Jain 12 A 2500
222 Mohit Sharma 11 B 4500

333 K.P.Gupta 12 B 3000

555 Nandini 12 C 3000

666 Rohan Sharma 11 B 2500

 Logical operators:

Logical operators are also possible only in 'where clause' and are used to
merge more than one condition. Logical operators are:

Display students' information, who are not in 10th class.

SELECT * Adno Name Class Section Fees
FROM student
111 Anu Jain 12 A 2500
WHERE NOT class = 10;
222 Mohit Sharma 11 B 4500
333 K.P.Gupta 12 B 3000

555 Nandini 12 C 3000

666 Rohan Sharma 11 B 2500
 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.

For example:
Display the names that start with letter "A".
Name

SELECT name Anu Jain

FROM student
WHERE name LIKE "A%";
Here, % replaces one or more characters.
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
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 class 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 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
 ORDER BY
This command is used to arrange values in ascending or descending
order. Adno Name Class Section Fees
444 Ajit Kumar 10 A 2000
111 Anu Jain 12 A 2500
666 Rohan Sharma 11 B 2500
For example: 333 K.P.Gupta 12 B 3000
SELECT * 555 Nandini 12 C 3000

FROM student ORDER BY fees ASC; 222 Mohit Sharma 11 B 4500

'asc' for ascending order. Without asc also the list is displayed with
ascending order only.
SELECT *
FROM student ORDER BY fees DESC;
'desc' for descending order. If the 'desc' is not given, the list will be
displayed with ascending order.
 Aggregate functions

Aggregate functions are used to implement calculation based upon a

column. These functions always return a single value.
Aggregate functions are:

1.SUM()
2.AVG()
3.MAX()
4.MIN()
5.COUNT()
 GROUP BY
The SQL GROUP BY is a clause that enables SQL aggregate functions for
grouping of information. (ie. GROUP BY clause is used in collaboration with
the SELECT statement to arrange identical data into groups.). This clause is
used whenever aggregate functions by group are required.
For example:
Display number of students in each class. SELECT count (*), class
FROM student GROUP BY class;
Display sum of fees for each class. SELECT class, sum (fees)
FROM student GROUP BY class;
Count (*) Class Class Sum (fees)
2 11 11 7000
3 12 12 8500
1 10 10 2000
 Having clause
As mentioned earlier, the 'where' clause is used only to place condition on the
selected columns, whereas the 'HAVING' clause is used to place condition on
groups created by 'group by' clause, because here the 'WHERE' clause is not
useable.
Example:
Display sum of fees which is more than 5000 for each class SELECT class, sum
(fees)
FROM student GROUP BY class
HAVING sum (fees)>5000;
Class Sum (fees)

11 7000

12 8500
 DISTINCT
The DISTINCT keyword is used to remove duplicate values in a column.
For example:
Display class in student table.
SELECT class
FROM student;
Class Class

12 12
11
11
12
10
10
12
11
 UPDATE Command
This command is used to implement modification of the data values.
Syntax:
UPDATE <table name>
SET <column name1>=new value, <column name>=new value etc [WHERE
<condition>];
Example:
Increase fees value by 500. UPDATE student
SET fees = fees + 500; Adno Name Class Section Fees
111 Anu Jain 12 A 3000
222 Mohit Sharma 11 B 5000
333 K.P.Gupta 12 B 3500
444 Ajit Kumar 10 A 2500
555 Nandini 12 C 3500
666 Rohan Sharma 11 B 3000
•Increase the fees value by 100 for adno 222. UPDATE student
SET fees = fees+100 WHERE adno = 222;

Adno Name Class Section Fees

111 Anu Jain 12 A 3000
222 Mohit Sharma 11 B 5100
333 K.P. Gupta 12 B 3500
444 Ajit Kumar 10 A 2500
555 Nandini 12 C 3500
666 Rohan Sharma 11 B 3000
DELETE Command
This command is used to remove information from a particular row or
rows. Please remember that this command will delete only row
information but not the structure of the table.
Syntax:
DELETE
FROM <table name> [WHERE <condition>];
For example:
Remove adno 444 information. DELETE
FROM student WHERE adno = 444; Adno Name Class Section Fees
111 Anu Jain 12 A 3000
222 Mohit Sharma 11 B 5100
333 K.P.Gupta 12 B 3500
555 Nandini 12 C 3500
666 Rohan Sharma 11 B 3000
•Remove all records. Adno Name Class Section Fees
DELETE
FROM student;
 ALTER TABLE command
This command is used to implement modification of the structure of the
table. This is a DDL command. Using this command, we can add a new
column, remove the existing column and modify data type of existing
column.
Syntax:
ALTER TABLE <table name> [ADD/MODIFY/DROP] <column name>;
For example:
Add one new column totalfees with number (10, 2).
ALTER TABLE student
ADD totalfees number(10,2);
Change totalfees datatype as number(12,2). ALTER TABLE student
MODIFY totalfees number(12,2);
Remove totalfees column. ALTER TABLE student DROP totalfees;
 DROP TABLE Command
This command is used to remove the entire structure of the table and
information. This is also from the DDL command.
Syntax:
DROP TABLE<table name>;
For example:
Remove the whole structure of student table. DROP TABLE student;
 Equi Join
Equi Joins are used to give information in different tables. It is a special type
of join in which we use only equality operator.
For example
SELECT *
FROM product, customer
WHERE product.product_no = customer. procuct_no; (or)
SELECT *
FROM product p, customer c WHERE p.product_no=c.procuct_no;
Product_no Product_name Price Cust_no Cust_name City Product_no
111 Computer 50000 301 Rohan Bangalore 111
222 Printer 10000 201 Mohan Mumbai 222
333 Scanner 12000 101 Kavitha Delhi 333
333 Scanner 12000 401 Sahil Mumbai 333
444 Modem 500 501 Rohita Delhi 444
 SQL Non-Equi joins

The non-equi join is a type of join in which, we use only relational

operators except equal operator. These include >, <, >=, >= and !=.
For example

SELECT *
FROM product,customer
WHERE product.product_no!=customer.procuct_no;