SQL
o SQL stands for Structured Query Language. It is used for storing and managing data
in relational database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user to create,
read, update and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL
as their standard database language.
o SQL allows users to query the database in a number of ways, using English-like
statements
Rules:
SQL follows the following rules:
o Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL statement on one or
multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.
Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.
SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used
to perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table, modify
the table, set permission for users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table, deleting a table, altering a table,
etc.
o All the command of DDL are auto-committed that means it permanently save all the changes
in the database.
Here are some commands that come under DDL:
o CREATE
o ALTER
o DROP
o TRUNCATE
a. CREATE It is used to create a new table in the database.
1. Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
2. Data Manipulation Language
o DML commands are used to modify the database. It is responsible for all form of changes in
the database.
o 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.
Here are some commands that come under DML:
o INSERT
o UPDATE
o DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a
table.
Syntax:
1. INSERT INTO TABLE_NAME
2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);
Or
1. INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);
3. Data Control Language
DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
o Grant
o Revoke
a. Grant: It is used to give user access privileges to a database.
b. Revoke: It is used to take back permissions from the user.
4. Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE
only.
These operations are automatically committed in the database that's why they cannot be used
while creating tables or dropping them.
Here are some commands that come under TCL:
o COMMIT
o ROLLBACK
o SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the database.
b. Rollback: Rollback command is used to undo transactions that have not already been saved to the
database.
c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the
entire transaction.
SQL Joins
SQL Join statement is used to combine data or rows from two or more tables based
on a common field between them. Different types of Joins are as follows:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
NATURAL JOIN
Consider the two tables below as follows:
Student
StudentCourse
Inner Join:
The INNER JOIN keyword selects all rows from both the tables as long as the condition is
satisfied. This keyword will create the result-set by combining all rows from both the tables
where the condition satisfies i.e value of the common field will be the same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.
Example:
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Left Join:
This join returns all the rows of the table on the left side of the join and
matches rows for the table on the right side of the join. For the rows for which there
is no matching row on the right side, the result-set will contain null. LEFT JOIN is
also known as LEFT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
RIGHT JOIN : It is similar to LEFT JOIN. This join returns all the rows of the
table on the right side of the join and matching rows for the table on the left side of
the join. For the rows for which there is no matching row on the left side, the result-
set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the
same.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
FULL JOIN creates the result-set by combining results of both LEFT JOIN and
RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows
for which there is no matching, the result-set will contain NULL values.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Example Queries(FULL JOIN):
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output:
NAME COURSE_ID
HARSH 1
PRATIK 2
RIYANKA 2
NAME COURSE_ID
DEEP 3
SAPTARHI 1
DHANRAJ NULL
ROHIT NULL
NIRAJ NULL
NULL 4
NULL 5
NULL 4
In SQL a Subquery can be simply defined as a query within another query. In
other words we can say that a Subquery is a query that is embedded in
WHERE clause of another SQL query. Important rules for Subqueries:
You can place the Subquery in a number of SQL
clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can
be used with SELECT, UPDATE, INSERT, DELETE statements along
with expression operator. It could be equality operator or comparison
operator such as =, >, =, <= and Like operator.
A subquery is a query within another query. The outer query is called
as main query and inner query is called as subquery.
Subquery must be enclosed in parentheses.
Subqueries are on the right side of the comparison operator.
ORDER BY command cannot be used in a
Subquery. GROUPBY command can be used to perform same
function as ORDER BY command.
Use single-row operators with singlerow Subqueries. Use multiple-row
operators with multiple-row Subqueries.
There is not any general syntax for Subqueries. However, Subqueries
are seen to be used most frequently with SELECT statement as shown
below:
SELECT column_name
FROM table_name
WHERE column_name expression operator
( SELECT COLUMN_NAME from TABLE_NAME WHERE ... );
Example: Select NAME, LOCATION, PHONE_NUMBER from DATABASE
WHERE ROLL_NO IN
(SELECT ROLL_NO from STUDENT where SECTION=’A’);
Example 2: To delete students from Student2 table whose rollno is same as
that in Student1 table and having location as Chennai.
Query: DELETE FROM Student2
WHERE ROLL_NO IN ( SELECT ROLL_NO
FROM Student1
WHERE LOCATION = ’chennai’);
