Structured Query
Language
The Structured Query Language(SQL) is a language that enables you
to create and operate on relational databases,which are sets of
related information stored in tables.
The original version was developed at IBM’s San Jose Research
Laboratory(now the Almanden Research Center).This language
originally called Sequel was implemented as part of the System R
Project in early 1970s.
The Sequel language has evolved since then and its name is changed
to SQL. In 1986, The American National Standards Institute (ANSI)
published an SQL standard that was updated again in 1992.
Processing Capabilities of SQL
Data Definition Language (DDL)- includes commands for defining
relation schemas,deleting relations,creating indexes and modifying
relation schemas.
Data Manipulation Language – includes commands to insert,delete and
modify tuples in the database.
Embeded Data Manupulation language-designed for use within
general-purpose programming language such as PL/1,Cobol,Pascal and
C.
View definition – includes commands for defining views.
Authorization –includes commands for specifying access rights to
relations and views.
Integrity-provies forms of integrity checking.
Transaction control-includes commands for specifying the beginning
and ending of transactions along with commands to have a control over
transaction processing.
Data Dictionary : A Data Dictionary is a file that contains “metadata”
i.e. data about data.
Data Types:Each field has a data
type :char,Decimal,Numeric,integer,smallint,float,double,real
SQL COMMANDS
CREATE TABLE: To create a table
Syntax:
Create Table<table name>(column name1 data type, column name2
data type ……);
Example:
Create Table student(rollno integer,name char(20),stream char(30));
While creating a table few constraints can be put -
A constraint is a condition or check applicable on a field or set of fields.
Various constraints are :
Unique constraint – to ensure that no two rows have the same value in
the specified columns.
Example :
Create Table student(rollno integer UNIQUE,name char(20),stream
char(30));
Primary Key constraint –declares a column as the primary key of the
table
Example :
Create Table student(rollno integer Primary key,name char(20),stream
char(30));
Default constraint –when the user does not define a value, it takes the
default value.
Create Table student(rollno integer,name char(20),stream char(30) ,fees
decimal default 1000);
Check constraint – limits the values that can be inserted into a column
of a table.
Create Table student(rollno integer,name char(20),stream char(30),fees
decimal check fees>=1000);
The constraints can be used together.
NOT NULL used to denote that the columns will not have NULL values.
The Insert Command
Used to insert the rows(records)/tuples in the table.
Syntax:
Insert into table name [<column list>] values (<value 1,value2….);
Example :
Insert into student values(1,’Raman’,’Humanities’,5000,’Shakti’);
A new row is added.
The Select Command
Rollno Name Stream Fees
Used to make queries on the databases.
1 Rajiv Verma Science 5000
Syntax:
2 Anita Mehta Commerce 4500
Select<column name> from table ;
3 Mohan Science 5000
Example : Table : Student Kapoor
4 Jasmine Humanities 4000
Query : To display the roll number and name of all the students:
Select rollno,name from Student;
To display the roll number name and stream for students who have
opted for ‘Science ‘ stream
Select rollno,name,stream from student where stream= “Science”;
(where clause used to set condition on the query)
The result will be:
Rollno Name Stream
1 Rajiv Verma Science
3 Mohan Kapoor Science
To display all the records from the table student :
Select * from student;
Select all the records from the table.
Query using Logical operators –And,or,not
To display the names of students who have stream as ‘Commerce’ and
fees more than 4000.
Select name from Student where stream = “Commerce” and fees >4000;
Will display the following result:
Name
Anita Mehta
To display the names of the students who have stream as either
‘Commerce’ or ‘Humanities’
Select name from student where stream = “Commerce” or stream =
“Humanities”;
To display the names and roll numbers of students who have opted for
stream other than humanities.
Select rollno,name from student where not stream = “Humanities’;
Query based on range
To display the name,stream and fees of those students whose fees is
more than 2000 and less than4000
Select name,stream,fees where fees between 2000 and 4000;
Or
Select name,stream,fees where fees>30 and fees <50;
Query based on a list:
To display the roll numbers,names of students who belong to house
‘Ekta’,’Shakti’,’Pragati’
Select rollno,name from student where house
in(‘Ekta’,’Shakti’,’Pragati’);
To display the names of the students who do not belong to Pragati or
Ekta.
Select rollno,name from student where house not in(‘Pragati’,’Ekta’);
Exercise :
Q. Study the following table and answer the query that follow:
Table : Employee
Employee Empname Department Designation Salary
number
1 Ashish Verma HR Manager 20000
2 Deepa kapoor Accounts Assistant 7000
3 Neha Sales Receptionist 5500
4 Gaurav Sales Manager 35000
Mehta
5 Ranjan Finance Assistant 10000
Manager
6 Rajesh Accounts CA 350000
7 Dinesh Finance Assistant 6000
1. Display the names,designation of all the managers.
2. Display the employee number,name,department and salar y of
those employees who are in Sales department.
3. Display the employee number ,name of those employees who are
either in HR or in Accounts department.
4. Display the names of those employee whose salary is above
10000.
5. Display the names,designation and salary of those employee who
are not in Accounts department.
6. Display the employee number and salary of those employees
whose salary is in the range of 7000 and 20000.
Condition based on pattern matches
Like and not like
Patterns are described using two special characters:
Percent(%) – matches any substring.
Underscore(_)- matches a character.
Example:
To display the name of the students whose name starts with ‘M’
Select name from student where name like ‘M%’;
Output:
Name
Mohan Kapoor
To display the name of students whose name has 5 characters and ends
with the letter ‘D’
Select name from student where name Like ‘____D’;
To display the names of those employees whose department does not
start with ‘A’
Select name from employee where department not like ‘A%’;
ORDER BY CLAUSE
The ORDER BY clause allows sorting of query results by one or more
columns.
The sorting can be done either in ascending or descending order,the
default order is ascending.
Syntax:
SELECT <column name> from table name where <condition/predicate>
order by column name;
The order by clause is always put at the end of the syntax.
To display the names of the students in alphabetical order.
Select name from student order by name;
To display the names of the students in decreasing order.
Select name from student order by name DESC;
DESC arranges the names in decreasing order of names.
Aggregate functions:
avg – to compute the average value
min – to find the minimum value
max – to find the maximum value
Sum – to find the total value
Stddev – to find the standard deviation
Count – to count non-null values in a column
Count(*) – to count the total number of rows in a table
Variance - to compute the variance of values in column.
To find the total fees of the students belonging to ‘Science’ stream
Select sum(fees) from student where stream = ‘Science’;
To find the maximum fees of the student
Select max(fees) from student;
To count the number of students
Select count(*) from student;
To count different number of streams
Select count(Distinct stream) from student;
Group By clause
This clause is used to divide the table into
groups.
Example :
To calculate the number of employees in each
department for employees .
Select count(*) from employee group by department;
Output
Department Count(*)
HR 1
Accounts 2
Sales 2
Finance 2
Employee
Employee Empname
Empname Department
Department Designation
Designation Salary
Salary
number
number
11 Ashish
AshishVerma
Verma HR
HR Manager
Manager 20000
20000
22 Deepa
Deepa kapoor
kapoor Accounts
Accounts Assistant
Assistant 7000
7000
33 Neha
Neha Sales
Sales Receptionist
Receptionist 5500
5500
44 Gaurav
Gaurav Sales
Sales Manager
Manager 3500
35000
Mehta
Mehta
55 Ranjan
Ranjan Finance
Finance Assistant
Assistant 10000
10000
Manager
Manager
66 Rajesh
Rajesh Accounts
Accounts CA
CA 3500
350000
77 Dinesh
Dinesh Finance
Finance Assistant
Assistant 6000
6000
Group by using the aggregate functions
To display the total salary of the employees in each department
Select department,sum(salary) from employee group by department
Output
Department sum
HR 20000
Accounts 10500
Finance 9000
Sales 16000
Having Clause:
The Having clause is used to place conditions in the group by clause
The Create View command
A View is a virtual table. It is created from another existing table called the
base table.
Create view stu As select * from student ;
Create view stu(rollno,name ,stream)As select * from student where
stream = “Humainties”;
Alter table
To add the column in the table:
Syntax:
Alter table<table name>ADD <column name><data type><size>;
Example:
Alter Table student Add Busroute integer;
Will add a new column name busroute to the table.
Drop table
This command is used to delete the entire table
Syntax
DROP TABLE <table name>
Example :
To delete the student table
DROP TABLE student;
DROP VIEW
To delete a view
DROP view stu;
DROP VIEW does not delete the base table
Drop Table deletes the table and the view.
UPDATE command
To modify the tuples in the table
Syntax: Update student set fees =fees +200;
Increases the fees of all the students by `200 .
Example:
To calculate the average salary of employees for ‘sales’ department.
Select avg(salary) from employees group by department having
department = ‘sales’;
The Delete command
The Delete command is used to delete a row or tuple from the table.
Syntax:
Delete From <tablename> [where <condition / predicate];
Delete from student ;
Deletes all the rows from the table;
Delete from student where stream = “Humanities’;
Deletes the rows of those students who have stream as Humanities.
Exercise
Q. Refer to the Employee table:
Answer the following queries:
1. Display the name and salary of the employees according to their
department.
2. Display the average salary of employees in the accounts
department.
3. Display the name and salary of the employee with maximum
salary.
4. Delete the record of Receptionist.
5. Create a view with employee number and designation from the
employee table.
6. Increase the salary of employees in Finance department by `1000
.
7. Add a new column Grade in the table.
QUERIES ON TWO TABLES
Whenever the query on the two tables is done there should be a common
primary key so that the data can be related.
Example :
Table 1 : Employee
Employee
Employee Empname
Empname Department
Department Designation
Designation Salary
Salary
number
number
11 Ashish
AshishVerma
Verma HR
HR Manager
Manager 20000
20000
22 Deepa
Deepa kapoor
kapoor Accounts
Accounts Assistant
Assistant 7000
7000
33 Neha
Neha Sales
Sales Receptionist
Receptionist 5500
5500
44 Gaurav
Gaurav Sales
Sales Manager
Manager 35000
35000
Mehta
Mehta
55 Ranjan
Ranjan Finance
Finance Assistant
Assistant 10000
10000
Manager
Manager
66 Rajesh
Rajesh Accounts
Accounts CA
CA 350000
350000
77 Dinesh
Dinesh Finance
Finance Assistant
Assistant 6000
6000
Table 2 : Empinfo
Empnumber Casual leaves Earned leaves Grade
1 10 20 B
2 5 12 B
3 8 20 A
5 10 15 B
6 3 18 A
7 2 20 C
To display the employee number,name,department,salary ,number
of casual leaves and grade from the employee and empinfo
tables.
Syntax:
Select <column names>from table 1,table 2 where table1.primary
key = table 2.primary key;
Select
employeenumber,name,department,salary,casualleaves,grade from
employee,empinfo where employee.employeenumber =
empinfo.empnumber;
Output :
Employee nam Depar Salary casual grade
number
e tment leaves
1 Ashish HR 20000 10 B
Verma
2 Deepa Accounts 7000 5 A
kapoor
3 Neha Sales 5500 8 B
5 Ranjan Finance 10000 10 A
6 Rajesh Accounts 350000 3 C
7 Dinesh Finance 6000 2
