Chapter 3

SQL Fundamentals
Introduction to Query Languages
 SQL stands for Structured Query Language. It is used for storing and
managing data in relational database management system (RDBMS).
 It is a standard language for Relational Database System. It enables a
user to create, read, update and delete relational databases and tables.
 All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server
use SQL as their standard database language.
 SQL allows users to query the database in a number of ways, using
English-like statements.
SQL Commands
SQL defines following ways to manipulate data stored in an RDBMS.
DDL: Data Definition Language
This includes changes to the structure of the table like creation of table, altering table,
deleting a table etc.
All DDL commands are auto-committed. That means it saves all the changes permanently
Comman Description
in the database.
d
create to create new table or
database
alter for alteration
DML commands are used for manipulating Command Description
the data stored in the table and not the table insert to insert a new row
itself. update to update existing row
DML commands are not auto-committed. It
delete to delete a row
means changes are not permanent to
database, they can be rolled back. merge merging two rows or two tables

Comman Description Data query language is used to

d fetch data from tables based on
conditions that we can easily apply.
select retrieve records from one or more
table
DDL commands in SQL are:
i) CREATE = CREATE statements is used to define the database structure schema:
Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
For example:
Create table book(bookid int, bookname varchar(20), author varchar(20), publication
varchar(30), no_of_pages int);
ii) DROP = Drops commands remove tables and databases from RDBMS.
Syntax: drop table;
For example : drop table book;
iii) ALTER: It is used to alter the structure of the database. This change could be
either to modify the characteristics of an existing attribute or probably to add a new
attribute.
Syntax: To add a new column in the table
 ALTER TABLE table_name ADD column_name column-datatype;
Alter table book add column price float;

[Link]
iv) TRUNCATE: It is used to delete all the rows from the table and free the
space containing the table.
Syntax:
 TRUNCATE TABLE table_name;
Example:
 TRUNCATE TABLE book;
2. Data Manipulation Language
 DML commands are used to modify the database. It is responsible for all
form of changes in the database.
 The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be rollback.
i) INSERT: The INSERT statement is a SQL query. It is used to insert data into
the row of a table.
Syntax:
 INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value
2, value3, .... valueN);
Or
 INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
For example:
 INSERT INTO books(Author, Subject) VALUES ("Korth", "DBMS");
b. UPDATE: This command is used to update or modify the value of a
column in the table.
Syntax:
 UPDATE table_name SET [column_name1= value1,...column_nameN = valu
eN] [WHERE CONDITION]
For example:
 UPDATE student SET sname = 'Ram' WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more row from a table.
Syntax:
 DELETE FROM table_name [WHERE condition];
For example:
 DELETE FROM books WHERE Author="Korth";
SQL - Expressions
 An expression is a combination of one or more values, operators and SQL
functions that evaluate to a value.
 These SQL EXPRESSIONs are like formulae, and they are written in query
language. You can also use them to query the database for a specific set of
data.
 Syntax
Consider the basic syntax of the SELECT statement as follows −
SELECT column1, column2, columnN FROM table_name
WHERE [CONDITION|EXPRESSION];
1) SQL Boolean Expressions
A Boolean expression is a way of expressing a condition or criterion which can be
either true or false.
Syntax:
SELECT * FROM tableName WHERE boolean condition ;
Example 1: Let us find out employees whose salary is 20000.
Query: SELECT * FROM employee WHERE salary = 20000 ;
2. SQL Numeric Expressions
We use numerical expressions to perform mathematical operations on the
stored data.
Syntax:
SELECT * FROM tableName WHERE condition or expression ;
Example 1: Let us first find employees whose age, if doubled, will be more
than 50.
Query: SELECT * FROM employees WHERE age*2 > 50 ;

3. SQL Date Expressions

SQL date expressions are used to compare and get data according to various
date-related queries and conditions.
Syntax:
SELECT * FROM tableName WHERE date condition ;
Example 1: Let us find the employees who were born after 1995 January.
Query: SELECT * FROM employee WHERE DoB > DATE(‘1995/01/01’) ;
SQL Character String Data Types
In SQL, the character string data types have contained a different type of
character data types, those are
 char datatype
 varchar datatype
SQL Char DataType
In SQL, char datatype is used to store a fixed length of characters. Suppose if
we declare a char(50), then it will allocate a memory for 50 characters to
hold 50 characters of data. In case, if we insert only 10 characters of a
string, then only 10 characters of memory will be used and
remaining 40 characters of memory will be wasted. Its size can be up to
8000 characters.
SQL Varchar DataType
In SQL, varchar means variable characters and it is used to store non-
Unicode characters. It will allocate the memory based on the number
characters inserted. Suppose if we declare varchar(50), then it will
allocate memory of 0 characters at the time of declaration. Now if we
insert only 10 characters of a string, then it will allocate memory for
only 10 characters. Its size can be up to 8000 characters.
SQL Set Operation
The SQL Set operation is used to combine the two or more SQL SELECT statements.
1. Union
 The SQL Union operation is used to combine the result of two or more SQL
SELECT queries.
 In the union operation, all the number of datatype and columns must be same in
both the tables on which UNION operation is being applied.
 The union operation eliminates the duplicate rows from its result set.
Syntax
 SELECT column_name FROM table1 UNION SELECT column_name FROM table2;
ID NAME ID NAME SELECT * FROM First UNION SELECT * FROM Second;

1 Jack 3 Jackson
ID NAME
2 Harry 4 Stephan
1 Jack
3 Jackson 5 David
2 Harry
The First The Second
table table 3 Jackson
4 Stephan
5 David
2. Union All ID NAME
Union All operation is equal to the Union operation. It returns the set without 1 Jack
removing duplication and sorting the data.
2 Harry
Syntax:
3 Jackson
 SELECT column_name FROM table1 UNION ALL SELECT column_name FRO
M table2; 3 Jackson
 SELECT * FROM First UNION ALL SELECT * FROM Second; 4 Stephan
5 David

3. Intersect
 It is used to combine two SELECT statements. The Intersect operation
returns the common rows from both the SELECT statements.
 In the Intersect operation, the number of datatype and columns must be the
same.
 It has no duplicates and it arranges the data in ascending order by default.
Syntax
 SELECT column_name FROM table1 INTERSECT SELECT column_name FRO
ID
M table2; NAME
 3
SELECT Jackson
* FROM First INTERSECT SELECT * FROM Second;
4. Minus
 It combines the result of two SELECT statements. Minus operator is used
to display the rows which are present in the first query but absent in the
second query.
 It has no duplicates and data arranged in ascending order by default.
Syntax:
 SELECT column_name FROM table1 MINUS SELECT column_name FROM ta
ble2;
ID NAME ID First
SELECT * FROM NAMEMINUS SELECT * FROM Second;
1 Jack 3 Jackson ID NAME

2 Harry 4 Stephan 1 Jack

3 Jackson 5 David 2 Harry

The First The Second

table table
Aggregate Functions in SQL
An aggregate function in SQL performs a calculation on multiple values and
returns a single value. SQL provides many aggregate functions that
include avg, count, sum, min, max, etc. An aggregate function ignores
NULL values when it performs the calculation, except for the count
function.
What is an Aggregate Function in SQL?
An aggregate function in SQL returns one value after calculating multiple
values of a column. We often use aggregate functions with the GROUP BY
and HAVING clauses of the SELECT statement.
Various types of SQL aggregate functions are:
 Count()
 Sum()
 Avg()
 Min()
 Max()
SQL Aggregate Functions
Now let us understand each Aggregate function with a example:
Id Name Salary
-----------------------
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F Null

1) count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of non-null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct non null values over the column salary .i.e 4

2)sum():

sum(salary): Sum all non null values of Column salary i.e., 310
3) avg():
avg(salary) = sum(salary) / count(salary) = 310/5
avg(Distinct salary) = sum(Distinct salary) / count(Distinct Salary) =
250/4

4)min():
min(salary): Minimum value in the salary column except null i.e., 40.

5) max():
max(salary): Maximum value in the salary i.e., 80.
SQL JOIN
SQL Join is used to fetch data from two or more tables, which is joined to
appear as single set of data. It is used for combining column from two or
more tables by using values common to both tables.
Types of JOIN
Following are the types of JOIN that we can use in SQL:
 Inner
 Outer
 Left
 Right
INNER Join or EQUI Join
This is a simple JOIN in which the result is based on matched data as per the
equality condition specified in the SQL query.
Inner Join Syntax is,
SELECT column-name-list FROM table-name1 INNER JOIN table-name2 WHERE
[Link]-name = [Link]-name;
Student Student_info
ID NAME ID NAME ID Address
ID Address
1 abhi 1 abhi 1 DELHI
1 DELHI
2 adam 2 adam 2 MUMBAI
2 MUMBAI
3 alex 3 alex 3 CHENNAI
3 CHENNAI
4 anu

SELECT * from StudentINNER JOIN Student_info where [Link] = Student_info.id;

Natural JOIN
Natural Join is a type of Inner join which is based on column having same
name and same datatype present in both the tables to be joined.
The syntax for Natural Join is,
SELECT * FROM
table-name1 NATURAL JOIN table-name2;

ID NAME
1 abhi ID Address ID NAME Address
2 adam 1 DELHI 1 abhi DELHI
3 alex 2 MUMBAI 2 adam MUMBAI
4 anu 3 CHENNAI 3 alex CHENNAI

Natural join query will be,

SELECT * from Student NATURAL JOIN Student_info;

In the above example, both the tables being joined have ID column(same name and
same datatype), hence the records for which value of ID matches in both the tables
will be the result of Natural Join of these two tables.
OUTER JOIN
Outer Join is based on both matched and unmatched data. Outer Joins
subdivide further into,
 Left Outer Join
 Right Outer Join
 Full Outer Join
LEFT Outer Join
The left outer join returns a resultset table with the matched data from the
two tables and then the remaining rows of the left table and null from
the right table's columns.
Syntax for Left Outer Join is,
SELECT column-name-list FROM
table-name1 LEFT OUTER JOIN table-name2
ON [Link]-name = [Link]-name;
To specify a condition, we use the ON keyword with Outer Join.
SELECT column-name-list FROM
table-name1, table-name2 on [Link]-name = table-
[Link]-name(+);
 ID Address ID NAME ID Address
ID NAME
1 DELHI 1 abhi 1 DELHI
1 abhi
2 MUMBAI 2 adam 2 MUMBAI
2 adam
3 CHENNAI 4 Alex Null null
4 alex
7 NOIDA
8 PANIPAT

RIGHT Outer Join

The right outer join returns a resultset table with the matched data from the
two tables being joined, then the remaining rows of the right table and null for
the remaining left table's columns.
Syntax for Right Outer Join is,
SELECT column-name-list FROM table-name1 RIGHT OUTER JOIN table-name2
ON [Link]-name = [Link]-name;
SELECT column-name-list FROM
table-name1, table-name2
ON [Link]-name(+) = [Link]-name;

ID NAME ID Address ID NAME ID Address

1 abhi 1 DELHI 1 abhi 1 DELHI
2 adam 2 MUMBAI 2 adam 2 MUMBAI
3 alex 3 CHENNAI 3 alex 3 CHENNAI
4 anu 7 NOIDA nul null 7 NOIDA
5 ashish 8 PANIPAT l
nul null 8 PANIPAT
l
SELECT * FROM class RIGHT OUTER JOIN class_info ON ([Link] = class_info.id);
Full Outer Join
The full outer join returns a resultset table with the matched data of two
table then remaining rows of both left table and then the right table.
Syntax of Full Outer Join is,
SELECT column-name-list FROM table-name1 FULL OUTER JOIN table-name2
ON [Link]-name = [Link]-name;

ID NAME ID Address

ID NAME 1 abhi 1 DELHI

ID Address
1 abhi 2 adam 2 MUMBAI
1 DELHI
2 adam 3 alex 3 CHENNAI
2 MUMBAI
3 alex 4 anu null null
3 CHENNAI
4 anu 5 ashish null null
7 NOIDA
5 ashish null null 7 NOIDA
8 PANIPAT
null null 8 PANIPAT

SELECT * FROM class FULL OUTER JOIN class_info ON ([Link] = class_info.id);

Views in SQL
 Views in SQL are considered as a virtual table. A view also contains rows and
columns.
 To create the view, we can select the fields from one or more tables present in the
database.
 A view can either have specific rows based on certain condition or all the rows of
a table.
 SQL Views are basically virtual tables. They don’t exist in
reality(database), hence doesn’t require any storage in a database.
Virtual Tables also have rows and columns similar to a real table in a
database. Such views are simply made by selecting data(fields) from one
or more tables, present in the Database, with some conditions for
selecting rows of the table.
 STU NAME MARK AGE
Student_Detail Table Student_Marks
_ID
Table S
STU_I NAME ADDRESS
D 1 Stepha 97 19
n
1 Stephan Delhi
2 Kathrin 86 21
2 Kathrin Noida
3 David 74 18
3 David Ghaziabad
4 Alina 90 20
4 Alina Gurugram
1. Creating view
A view can be created using the CREATE VIEW statement. We can create a
view from a single table or multiple tables.
 Syntax:
CREATE VIEW view_name AS SELECT column1, column2.....
FROM table_name WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table
Student_detail.
Query: CREATE VIEW DetailsView AS SELECT NAME, ADDRESS
FROM Student_Details WHERE STU_ID < 4;
Just like table query, we can query the view to view the data.
NAME ADDRESS
SELECT * FROM DetailsView; Output :
Stephan Delhi
Kathrin Noida
David Ghaziabad
3. Creating View from multiple tables
View from multiple tables can be created by simply include multiple tables in the
SELECT statement.
In the given example, a view is created named MarksView from two tables
Student_Detail and Student_Marks.
Query:
CREATE VIEW MarksView AS
SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Marks
WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

SELECT * FROM MarksView; Output :
NAME ADDRESS MARKS
Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90
4. Deleting View
A view can be deleted using the Drop View statement.
Syntax
DROP VIEW view_name;
Example:
If we want to delete the View MarksView, we can do this as:
DROP VIEW MarksView;
SQL LIKE Keyword
The LIKE command is used in a WHERE clause to search for a specified pattern in a
column.
You can use two wildcards with LIKE:
• % - Represents zero, one, or multiple characters
• _ - Represents a single character

The following SQL selects all customers with a CustomerName starting with "a":
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

The following SQL selects all customers with a CustomerName ending with "a":
SELECT * FROM Customers WHERE CustomerName LIKE '%a';

The following SQL selects all customers with a CustomerName that have "or" in any
position:
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

The following SQL statement selects all customers with a CustomerName that
starts with "a" and are at least 3 characters in length:

SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';

The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name ORDER BY column1, column2, ... ASC|DESC;
SQL | GROUP BY
The GROUP BY Statement in SQL is used to arrange identical data into
groups with the help of some functions. i.e if a particular column has same
values in different rows then it will arrange these rows in a group.
Important Points:
•GROUP BY clause is used with the SELECT statement.
•In the query, GROUP BY clause is placed after the WHERE clause.
•In the query, GROUP BY clause is placed before ORDER BY clause if used
any.
•In the query , Group BY clause is placed before Having clause .
•Place condition in the having clause

Syntax:

SELECT column1, function_name(column2) FROM table_name WHERE

condition GROUP BY column1, column2;
Example:
Group By single column: Group By single column means,
to place all the rows with same value of only that particular
column in one group. Consider the query as shown below:
SELECT NAME, SUM(SALARY) FROM Employee GROUP BY
NAME;
The above query will produce the below output: