There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

DDL SQL command

used to create a table or a database in relational database management

system.

Creating a Database

To create a database in RDBMS, create command is used. Following is the syntax,

CREATE DATABASE <DB_NAME>;

Example for creating Database

CREATE DATABASE Test;

The above command will create a database named Test, which will be an empty schema without
any table.

To create tables in this newly created database, we can again use the create command.

Creating a Table

create command can also be used to create tables. Now when we create a table,
we have to specify the details of the columns of the tables too. We can specify
the names and data types of various columns in the create command itself.

Following is the syntax:

CREATE TABLE <TABLE_NAME>

column_name1 datatype1,

column_name2 datatype2,

column_name3 datatype3,

column_name4 datatype4

);

Example for creating Table

CREATE TABLE Student(

student_id INT,

name VARCHAR(100),

age INT);
The above command will create a new table with name Student in the current
database with 3 columns, namely student_id, name and age. Where the
column student_id will only store integer, name will hold upto 100 characters
and age will again store only integer value.

If you are currently not logged into your database in which you want to create
the table then you can also add the database name along with table name,
using a dot operator .

For example, if we have a database with name Test and we want to create a
table Student in it, then we can do so using the following query:

CREATE TABLE Test.Student(

student_id INT,

name VARCHAR(100),

age INT);

Most commonly used data types for Table columns

Here we have listed some of the most commonly used data types used for
columns in tables.

Data
Use
type

INT used for columns which will store integer values.

FLOAT used for columns which will store float values.

DOUBLE used for columns which will store float values.

used for columns which will be used to store characters and

VARCHAR
integers, basically a string.

CHAR used for columns which will store char values(single character).

DATE used for columns which will store date values.

used for columns which will store text which is generally long in
length. For example, if you create a table for storing profile
TEXT
information of a social networking website, then for about
me section you can have a column of type TEXT.

SQL: ALTER command

alter command is used for altering the table structure, such as,

 to add a column to existing table

 to rename any existing column

 to change datatype of any column or to modify its size.

 to drop a column from the table.

ALTER Command: Add a new Column

Using ALTER command we can add a column to any existing table. Following is
the syntax,

ALTER TABLE table_name ADD(

column_name datatype);

example:

ALTER TABLE student ADD(

address VARCHAR(200)

);

ALTER Command: Add multiple new Columns

Using ALTER command we can even add multiple new columns to any existing
table. Following is the syntax,

ALTER TABLE table_name ADD(

column_name1 datatype1,

column-name2 datatype2,

column-name3 datatype3);

ALTER TABLE student ADD(

father_name VARCHAR(60),

mother_name VARCHAR(60),

dob DATE);
ALTER Command: Add Column with default value

ALTER command can add a new column to an existing table with a default
value too. The default value is used when no value is inserted in the column.
Following is the syntax,

ALTER TABLE table_name ADD(

column-name1 datatype1 DEFAULT some_value

);

ALTER TABLE student ADD(

dob DATE DEFAULT '01-Jan-99'

);

ALTER Command: Modify an existing Column

ALTER command can also be used to modify data type of any existing column.
Following is the syntax,

ALTER TABLE student MODIFY(

address varchar(300));

ALTER Command: Rename a Column

ALTER TABLE student RENAME

address TO location;

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns.

ALTER TABLE student DROP(

address);

SQL Truncate, Drop or Rename a Table

TRUNCATE command

TRUNCATE command removes all the records from a table. But this command
will not destroy the table's structure. When we use TRUNCATE command on a
table its (auto-increment) primary key is also initialized.
TRUNCATE TABLE student;

DROP command

DROP command completely removes a table from the database. This command
will also destroy the table structure and the data stored in it.

DROP TABLE student;

It can also be used on Databases, to delete the complete database. For

example, to drop a database,
DROP DATABASE Test;

RENAME query

RENAME command is used to set a new name for any existing table.

RENAME TABLE student to students_info;

 SQL Constraints
SQL Constraints are rules used to limit the type of data that can go into a
table, to maintain the accuracy and integrity of the data inside table.

Constraints can be divided into the following two types,

1. Column level constraints: Limits only column data.

2. Table level constraints: Limits whole table data.

Constraints are used to make sure that the integrity of data is maintained in
the database. Following are the most used constraints that can be applied to a
table.

 NOT NULL

 UNIQUE

 PRIMARY KEY

 FOREIGN KEY

 CHECK

 DEFAULT

NOT NULL Constraint

By default, a column can hold NULL values. If you do not want a column to
have a NULL value, use the NOT NULL constraint.

 It restricts a column from having a NULL value.

 We use ALTER statement and MODIFY statement to specify this
constraint.

One important point to note about this constraint is that it cannot be defined
at table level.
Example using NOT NULL constraint:

CREATE TABLE Student

( s_id int NOT NULL,

name varchar(60),

age int

);

The above query will declare that the s_id field of Student table will not take
NULL value.

If you wish to alter the table after it has been created, then we can use the
ALTER command for it:
ALTER TABLE Student

MODIFY s_id int NOT NULL;

UNIQUE Constraint

It ensures that a column will only have unique values. A UNIQUE constraint
field cannot have any duplicate data.

 It prevents two records from having identical values in a column

 We use ALTER statement and MODIFY statement to specify this
constraint.

Example of UNIQUE Constraint:

Here we have a simple CREATE query to create a table, which will have a
column s_id with unique values.

CREATE TABLE Student

( s_id int NOT NULL,

name varchar(60),

age int NOT NULL UNIQUE

);

The above query will declare that the s_id field of Student table will only have
unique values and wont take NULL value.

If you wish to alter the table after it has been created, then we can use the
ALTER command for it:

ALTER TABLE Student

MODIFY age INT NOT NULL UNIQUE;

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary

Key must contain unique value and it must not contain null value. Usually
Primary Key is used to index the data inside the table.

PRIMARY KEY constraint at Table Level

CREATE table Student

( s_id int PRIMARY KEY,

Name varchar(60) NOT NULL,

Age int);

PRIMARY KEY constraint at Column Level

ALTER table Student

ADD PRIMARY KEY (s_id);

Foreign Key Constraint

Foreign Key is used to relate two tables. The relationship between the two
tables matches the Primary Key in one of the tables with a Foreign Key in the
second table.

 This is also called a referencing key.

  We use ALTER statement and ADD statement to specify this constraint.

To understand FOREIGN KEY, let's see its use, with help of the below tables:

Customer_Detail Table

c_id Customer_Name address

101 Adam Noida

102 Alex Delhi

103 Stuart Rohtak

Order_Detail Table

Order_id Order_Name c_id

10 Order1 101

11 Order2 103

12 Order3 102

In Customer_Detail table, c_id is the primary key which is set as foreign key
in Order_Detail table. The value that is entered in c_id which is set as foreign
key in Order_Detail table must be present in Customer_Detail table where it
is set as primary key. This prevents invalid data to be inserted into c_id column
of Order_Detail table.

If you try to insert any incorrect data, DBMS will return error and will not allow
you to insert the data.

FOREIGN KEY constraint at Table Level

CREATE table Order_Detail(

order_id int PRIMARY Key,

order_name varchar(60) NOT NULL,

c_id int REFERENCES Customer_Detail(c_id)

);

FOREIGN KEY constraint at Column Level

ALTER table Order_Detail

ADD FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a
particular record is deleted in the main table. When two tables are connected
with Foriegn key, and certain data in the main table is deleted, for which a
record exits in the child table, then we must have some mechanism to save the
integrity of data in the child table.
 1. On Delete Cascade : This will remove the record from child table, if that
value of foriegn key is deleted from the main table.

2. On Delete Null : This will set all the values in that record of child table as
NULL, for which the value of foriegn key is deleted from the main table.

3. If we don't use any of the above, then we cannot delete data from the
main table for which data in child table exists. We will get an error if we
try to do so.

ERROR : Record in child table exist

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It

performs check on the values, before storing them into the database. Its like
condition checking before saving data into a column.

Using CHECK constraint at Table Level

CREATE table Student(

s_id int NOT NULL CHECK(s_id > 0),

Name varchar(60) NOT NULL,

Age int
);

Using CHECK constraint at Column Level

ALTER table Student ADD CHECK(s_id > 0);

DML Commands
Using INSERT SQL command
Data Manipulation Language (DML) statements are used for managing data in
database. DML commands are not auto-committed. It means changes made
by DML command are not permanent to database, it can be rolled back.

Consider a table student with the following fields.

s_id name age

INSERT INTO student VALUES(101, 'Adam', 15);

Insert value into only specific columns

INSERT INTO student(id, name) values(102, 'Alex');

Insert NULL value to a column

Both the statements below will insert NULL value into age column of
the student table.

INSERT INTO student(id, name) values(102, 'Alex');

OR

INSERT INTO Student VALUES(102,'Alex', null);

The above command will insert only two column values and the other column is set to null.
 S_id S_Name age

101 Adam 15

Alex
102

Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default);

the column age in our tabel has a default value of 14.

Also, if you run the below query, it will insert default value into the age
column, whatever the default value may be.

INSERT INTO Student VALUES(103,'Chris');

Using UPDATE SQL command

Syntax:

UPDATE table_name SET column_name = new_value WHERE some_condition;

Example:

UPDATE student SET age=18 WHERE student_id=102;

S_id S_Name age

101 Adam 15
102 Alex 18

103 chris 14

In the above statement, if we do not use the WHERE clause, then our update
query will update age for all the columns of the table to 18.

Updating Multiple Columns

UPDATE student SET name='Abhi', age=17 where s_id=103;

s_id name age

101 Adam 15

102 Alex 18

103 Abhi 17

UPDATE Command: Incrementing Integer Value

When we have to update any integer value in a table, then we can fetch and
update the value in the table in a single statement.

UPDATE student SET age = age+1;

NOTE: This style only works for integer values.

 Using DELETE SQL command

DELETE FROM table_name;

Delete all Records from a Table

DELETE FROM student;

Delete a particular Record from a Table

DELETE FROM student WHERE s_id=103;

S_id S_Name age

101 Adam 15

102 Alex 18

Isn't DELETE same as TRUNCATE

TRUNCATE command is different from DELETE command. The delete command

will delete all the rows from a table whereas truncate command not only
deletes all the records stored in the table, but it also re-initializes the table(like
a newly created table).

For eg: If you have a table with 10 rows and an auto_increment primary key,
and if you use DELETE command to delete all the rows, it will delete all the
rows, but will not re-initialize the primary key, hence if you will insert any row
after using the DELETE command, the auto_increment primary key will start
from 11. But in case of TRUNCATE command, primary key is re-initialized, and it
will again start from 1.
 TCL Commands

Commit, Rollback and Savepoint SQL commands

These are used to manage the changes made to the data in a table by DML
statements. It also allows statements to be grouped together into logical
transactions.

COMMIT command

COMMIT command is used to permanently save any transaction into the

database. When we use any DML command like INSERT, UPDATE or DELETE,
the changes made by these commands are not permanent, until the current
session is closed, the changes made by these commands can be rolled back.
To avoid that, we use the COMMIT command to mark the changes as
permanent.

COMMIT;

ROLLBACK command

This command restores the database to last commited state. It is also used
with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

If we have used the UPDATE command to make some changes into the
database, and realise that those changes were not required, then we can use
the ROLLBACK command to rollback those changes, if they were not commited
using the COMMIT command.

ROLLBACK TO savepoint_name;

SAVEPOINT command
SAVEPOINT command is used to temporarily save a transaction so that you can
rollback to that point whenever required.

SAVEPOINT savepoint_name;

In short, using this command we can name the different states of our data in
any table and then rollback to that state using the ROLLBACK command
whenever required.

Using Savepoint and Rollback

Following is the table class,

id name

1 Abhi

2 Adam

4 Alex

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;

UPDATE class SET name = 'Abhijit' WHERE id = '5';

SAVEPOINT A;

INSERT INTO class VALUES(6, 'Chris');

SAVEPOINT B;

INSERT INTO class VALUES(7, 'Bravo');

SAVEPOINT C;

SELECT * FROM class;

NOTE: SELECT statement is used to show the data stored in the table.
The resultant table will look like,

id name

1 Abhi

2 Adam
4 Alex

5 Abhijit

6 Chris

7 Bravo

ROLLBACK TO B;

SELECT * FROM class;

Now our class table will look like,

id name

1 Abhi

2 Adam

4 Alex
5 Abhijit

6 Chris

Now let's again use the ROLLBACK command to roll back the state of data to
the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Now the table will look like,

id name

1 Abhi

2 Adam

4 Alex

5 Abhijit

So now you know how the

commands COMMIT, ROLLBACK and SAVEPOINT works.

Data Control Language(DCL)

Data Control Language(DCL) is used to control privileges in Database. To
perform any operation in the database, such as for creating tables, sequences
or views, a user needs privileges. Privileges are of two types,

 System: This includes permissions for creating session, table, etc and all
types of other system privileges.

 Object: This includes permissions for any command or query to perform

any operation on the database tables.

In DCL we have two commands,

 GRANT: Used to provide any user access privileges or other priviliges for
the database.

 REVOKE: Used to take back permissions from any user.

Allow a User to create session

When we create a user in SQL, it is not even allowed to login and create a
session until and unless proper permissions/priviliges are granted to the user.

Following command can be used to grant the session creating priviliges.

GRANT CREATE SESSION TO username;

Allow a User to create table

GRANT CREATE TABLE TO username;

Grant all privilege to a User

sysdba is a set of priviliges which has all the permissions in it. So if we want to
provide all the privileges to any user, we can simply grant them
the sysdba permission.

GRANT sysdba TO username;

 Grant permission to create any table

Sometimes user is restricted from creating come tables with names which are
reserved for system tables. But we can grant privileges to a user to create any
table using the below command,

GRANT CREATE ANY TABLE TO username;

Grant permission to drop any table

As the title suggests, if you want to allow user to drop any table from the
database, then grant this privilege to the user,

GRANT DROP ANY TABLE TO username;

To take back Permissions

And, if you want to take back the privileges from any user, use
the REVOKE command.

REVOKE CREATE TABLE FROM username;

SELECT SQL Query

SELECT query is used to retrieve data from a table. It is the most used SQL
query. We can retrieve complete table data, or partial by specifying conditions
using the WHERE clause.

Syntax of SELECT query

SELECT query is used to retieve records from a table. We can specify the
names of the columns which we want in the resultset.

Consider the following student table,

 s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

SELECT s_id, name, age FROM student;

The above query will fetch information of s_id, name and age columns of
the student table and display them,

s_id name age

101 Adam 15

102 Alex 18

103 Abhi 17
104 Ankit 22

As you can see the data from address column is absent, because we did not
specif it in our SELECT query.

Select all records from a table

A special character asterisk * is used to address all the data(belonging to all

columns) in a query. SELECT statement uses * character to retrieve all records
from a table, for all the columns.

SELECT * FROM student;

The above query will show all the records of student table, that means it will
show complete dataset of the table.

s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

 Select a particular record based on a condition

We can use the WHERE clause to set a condition,

SELECT * FROM student WHERE name = 'Abhi';

The above query will return the following result,

103 Abhi 17 Rohtak

Performing Simple Calculations using SELECT Query

Consider the following employee table.

eid name age salary

101 Adam 26 5000

102 Ricky 42 8000

103 Abhi 25 10000

104 Rohan 22 5000

Here is our SELECT query,

SELECT eid, name, salary+3000 FROM employee;

The above command will display a new column in the result, with 3000 added
into existing salaries of the employees.

eid name salary+3000

101 Adam 8000

102 Ricky 11000

103 Abhi 13000

104 Rohan 8000

So you can also perform simple mathematical operations on the data too
using the SELECT query to fetch data from table.

Using the WHERE SQL clause

WHERE clause is used to specify/apply any condition while retrieving, updating

or deleting data from a table. This clause is used mostly
with SELECT, UPDATE and DELETEquery.

When we specify a condition using the WHERE clause then the query executes
only for those records for which the condition specified by the WHERE clause is
true.

Consider a table student,

 s_id name age address

101 Adam 15 Chennai

102 Alex 18 Delhi

103 Abhi 17 Banglore

104 Ankit 22 Mumbai

Now we will use the SELECT statement to display data of the table, based on a
condition, which we will add to our SELECT query using WHERE clause.

Let's write a simple SQL query to display the record for student with s_id as
101.

SELECT s_id,

name,

age,

address

FROM student WHERE s_id = 101;

s_id name age address

101 Adam 15 Noida

Applying condition on Text Fields
In the above example we have applied a condition to an integer value field,
but what if we want to apply the condition on name field. In that case we must
enclose the value in single quote ' '. Some databases even accept double
quotes, but single quotes is accepted by all.

SELECT s_id,

name,

age,

address

FROM student WHERE name = 'Adam';

s_id name age address

101 Adam 15 Noida

Operators for WHERE clause condition

Following is a list of operators that can be used while specifying

the WHERE clause condition.

Operator Description

= Equal to
!= Not Equal to

< Less than

> Greater than

<= Less than or Equal to

>= Greate than or Equal to

BETWEEN Between a specified range of values

LIKE This is used to search for a pattern in value.

IN In a given set of values

SQL LIKE clause

LIKE clause is used in the condition in SQL query with
the WHERE clause. LIKE clause compares data with an expression using
wildcard operators to match pattern given in the condition.
Wildcard operators

There are two wildcard operators that are used in LIKE clause.

 Percent sign %: represents zero, one or more than one character.

 Underscore sign _: represents only a single character.

Example of LIKE clause

Consider the following Student table.

s_id s_Name age

101 Adam 15

102 Alex 18

103 Abhi 17

SELECT * FROM Student WHERE s_name LIKE 'A%';

The above query will return all records where s_name starts with character 'A'.

s_id s_Name age

101 Adam 15

102 Alex 18

103 Abhi 17

Using _ and %

SELECT * FROM Student WHERE s_name LIKE '_d%';

The above query will return all records from Student table
where s_name contain 'd' as second character.

s_id s_Name age

101 Adam 15

Using % only

SELECT * FROM Student WHERE s_name LIKE '%x';

The above query will return all records from Student table
where s_name contain 'x' as last character.
 s_id s_Name age

102 Alex 18

SQL ORDER BY Clause

Order by clause is used with SELECT statement for arranging retrieved data in
sorted order. The Order by clause by default sorts the retrieved data in
ascending order. To sort the data in descending order DESC keyword is used
with Order by clause.

Syntax of Order By

SELECT column-list|* FROM table-name ORDER BY ASC | DESC;

Using default Order by

Consider the following Emp table,

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SELECT * FROM Emp ORDER BY salary;

The above query will return the resultant data in ascending order of the salary.

eid name age salary

403 Rohan 34 6000

402 Shane 29 8000

405 Tiger 35 8000

401 Anu 22 9000

404 Scott 44 10000

Using Order by DESC

Consider the Emp table described above,

SELECT * FROM Emp ORDER BY salary DESC;

The above query will return the resultant data in descending order of
the salary.

eid name age salary

404 Scott 44 10000

401 Anu 22 9000

405 Tiger 35 8000

402 Shane 29 8000

403 Rohan 34 6000

SQL Group By Clause

Group by clause is used to group the results of a SELECT query based on one or
more columns. It is also used with SQL functions to group the result from one
or more tables.
DISTINCT keyword
The distinct keyword is used with SELECT statement to retrieve unique values from
the table. Distinct removes all the duplicate records while retrieving records from
any table in the database.

Example using DISTINCT Keyword

Consider the following Emp table. As you can see in the table below, there is
employee name, along with employee salary and age.

In the table below, multiple employees have the same salary, so we will be
using DISTINCT keyword to list down distinct salary amount, that is currently
being paid to the employees.

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 10000

404 Scott 44 10000

405 Tiger 35 8000

SELECT DISTINCT salary FROM Emp;

The above query will return only the unique salary from Emp table.

salary

5000

8000

10000

SQL AND & OR operator

The AND and OR operators are used with the WHERE clause to make more
precise conditions for fetching data from database by combining more than
one condition together.

AND operator

AND operator is used to set multiple conditions with the WHERE clause,
alongside, SELECT, UPDATE or DELETE SQL queries.

Example of AND operator

Consider the following Emp table

 eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

select * from emp where salary <1000 and age>25;

The above query will return records where salary is less

than 10000 and age greater than 25. Hope you get the concept here. We have
used the AND operator to specify two conditions with WHERE clause.

eid name age salary

402 Shane 29 8000

405 Tiger 35 9000

OR operator

OR operator is also used to combine multiple conditions with WHERE clause.

The only difference between AND and OR is their behaviour.

When we use AND to combine two or more than two conditions, records
satisfying all the specified conditions will be there in the result.

But in case of OR operator, atleast one condition from the conditions specified
must be satisfied by any record to be in the resultset.

Example of OR operator

Consider the following Emp table

eid name age salary

401 Anu 22 5000

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

select * from emp where salary <1000 or age>25;

The above query will return records where either salary is greater than
10000 or age is greater than 25.

402 Shane 29 8000

403 Rohan 34 12000

404 Scott 44 10000

405 Tiger 35 9000

What are SQL Functions?

SQL provides many built-in functions to perform operations on data. These
functions are useful while performing mathematical calculations, string
concatenations, sub-strings etc. SQL functions are divided into two categories,

1. Aggregate Functions

2. Scalar Functions
Aggregate Functions

These functions return a single value after performing calculations on a

group of values. Following are some of the frequently used Aggregrate
functions.

AVG() Function

Average returns average value after calculating it from values in a numeric

column.

Its general syntax is,

SELECT AVG(column_name) FROM table_name

Using AVG() function

Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query to find average salary will be,

SELECT avg(salary) from Emp;

Result of the above query will be,

avg(salary)

8200

COUNT() Function

Count returns the number of rows present in the table either based on some
condition or without condition.
Using COUNT() function
Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query to count employees, satisfying specified condition is,

SELECT COUNT(name) FROM Emp WHERE salary = 8000;

Result of the above query will be,

count(name)

Example of COUNT(distinct)
Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query is,

SELECT COUNT(DISTINCT salary) FROM emp;

Result of the above query will be,

count(distinct salary)

MAX() Function

MAX function returns maximum value from selected column of the table.
Using MAX() function
Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query to find the Maximum salary will be,

SELECT MAX(salary) FROM emp;

Result of the above query will be,

MAX(salary)

10000

MIN() Function

MIN function returns minimum value from a selected column of the table.
Using MIN() function
Consider the following Emp table,

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query to find minimum salary is,

SELECT MIN(salary) FROM emp;

Result will be,

MIN(salary)

6000

SUM() Function

SUM function returns total sum of a selected columns numeric values.

Using SUM() function
Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 Scott 44 10000

405 Tiger 35 8000

SQL query to find sum of salaries will be,

SELECT SUM(salary) FROM emp;

result:

SUM(salary)

41000
Scalar Functions

Scalar functions return a single value from an input value. Following are some
frequently used Scalar Functions in SQL.

UCASE() Function

UCASE function is used to convert value of string column to Uppercase

characters.
Using UCASE() function
Consider the following Emp table

eid name age salary

401 anu 22 9000

402 shane 29 8000

403 rohan 34 6000

404 scott 44 10000

405 Tiger 35 8000

SQL query for using UCASE is,

SELECT UCASE(name) FROM emp;

Result is,

UCASE(name)

ANU

SHANE

ROHAN

SCOTT

TIGER

LCASE() Function

LCASE function is used to convert value of string columns to Lowecase

characters.
Using LCASE() function
Consider the following Emp table

eid name age salary

401 Anu 22 9000

402 Shane 29 8000

403 Rohan 34 6000

404 SCOTT 44 10000

405 Tiger 35 8000

SQL query for converting string value to Lower case is,

SELECT LCASE(name) FROM emp;

Result will be,

LCASE(name)

anu

shane

rohan
scott

tiger

MID() Function

MID function is used to extract substrings from column values of string

type in a table. Using MID() function
Consider the following Emp table

eid name age salary

401 anu 22 9000

402 shane 29 8000

403 rohan 34 6000

404 scott 44 10000

405 Tiger 35 8000

SQL query will be,

SELECT MID(name,2,2) FROM emp;

result will come out to be,

MID(name,2,2)

nu

ha

oh

co

ig

ROUND() Function

ROUND function is used to round a numeric field to number of nearest

integer. It is used on Decimal point values.
Using ROUND() function
Consider the following Emp table

eid name age salary

401 anu 22 9000.67

402 shane 29 8000.98

403 rohan 34 6000.45

404 scott 44 10000

405 Tiger 35 8000.01

SQL query is,

SELECT ROUND(salary) from emp;

Result will be,

ROUND(salary)

9001

8001

6000
10000

8000

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.

JOIN Keyword is used in SQL queries for joining two or more tables. Minimum
required condition for joining table, is (n-1) where n, is number of tables. A
table can also join to itself, which is known as, Self Join.

Types of JOIN

Following are the types of JOIN that we can use in SQL:

 Inner

 Outer

 Left

 Right

Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows from the tables in Join.
It will return a table which consists of records which combines each row from
the first table with each row of the second table.
Example of Cross JOIN
Following is the class table,

ID NAME

1 abhi

2 adam

4 alex

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

Cross JOIN query will be,

SELECT * FROM

class CROSS JOIN class_info;

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 1 DELHI

4 alex 1 DELHI

1 abhi 2 MUMBAI

2 adam 2 MUMBAI

4 alex 2 MUMBAI

1 abhi 3 CHENNAI

2 adam 3 CHENNAI

4 alex 3 CHENNAI
As you can see, this join returns the cross product of all the records present in
both the tables.

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.
Example of INNER JOIN
Consider a class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address
1 DELHI

2 MUMBAI

3 CHENNAI

Inner JOIN query will be,

SELECT * from class INNER JOIN class_info where class.id = class_info.id;

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

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.
Example of Natural JOIN
Here is the class table,
 ID NAME

1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The resultset table will look like,

 ID NAME Address

1 abhi DELHI

2 adam MUMBAI

3 alex CHENNAI

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,

1. Left Outer Join

2. Right Outer Join

3. 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.
Example of Left Outer Join
Here is the class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI
3 CHENNAI

7 NOIDA

8 PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id =

class_info.id);

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

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.
Example of Right Outer Join
Once again the class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address
1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id =

class_info.id);

The resultant table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI
null null 7 NOIDA

null null 8 PANIPAT

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.
Example of Full outer join is,
The class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish
and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id =

class_info.id);

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI
2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

null null 7 NOIDA

null null 8 PANIPAT

SET Operations in SQL

SQL supports few Set operations which can be performed on the table data.
These are used to get meaningful results from data stored in the table, under
different special conditions.

In this tutorial, we will cover 4 different types of SET operations, along with
example:

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS
UNION Operation

UNION is used to combine the results of two or more SELECT statements.

However it will eliminate duplicate rows from its resultset. In case of union,
number of columns and datatype must be same in both the tables, on which
UNION operation is being applied.

Example of UNION
The First table,

ID Name

1 abhi

2 adam

The Second table,

ID Name
2 adam

3 Chester

Union SQL query will be,

SELECT * FROM First

UNION

SELECT * FROM Second;

The resultset table will look like,

ID NAME

1 abhi

2 adam

3 Chester

UNION ALL

This operation is similar to Union. But it also shows the duplicate rows.
Example of Union All
The First table,

ID NAME

1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Union All query will be like,

SELECT * FROM First

UNION ALL

SELECT * FROM Second;

The resultset table will look like,

ID NAME

1 abhi

2 adam

2 adam

3 Chester

INTERSECT

Intersect operation is used to combine two SELECT statements, but it only

retuns the records which are common from both SELECT statements. In case
of Intersect the number of columns and datatype must be same.

NOTE: MySQL does not support INTERSECT operator.

Example of Intersect
The First table,

ID NAME

1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Intersect query will be,

SELECT * FROM First

INTERSECT

SELECT * FROM Second;

The resultset table will look like

ID NAME

2 adam

MINUS

The Minus operation combines results of two SELECT statements and return
only those in the final result, which belongs to the first set of the result.

Example of Minus
The First table,

ID NAME
1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Minus query will be,

SELECT * FROM First

MINUS

SELECT * FROM Second;

The resultset table will look like,

ID NAME

1 abhi
 What is an SQL Sequence?
Sequence is a feature supported by some database systems to produce
unique values on demand. Some DBMS
like MySQL supports AUTO_INCREMENT in place of Sequence.

AUTO_INCREMENT is applied on columns, it automatically increments the

column value by 1 each time a new record is inserted into the table.

Sequence is also some what similar to AUTO_INCREMENT but it has some

additional features too.

Creating a Sequence

syntax:

CREATE SEQUENCE sequence-name

START WITH initial-value

INCREMENT BY increment-value

MAXVALUE maximum-value

CYCLE | NOCYCLE;

 The initial-value specifies the starting value for the Sequence.

 The increment-value is the value by which sequence will be

incremented.

 The maximum-value specifies the upper limit or the maximum value

upto which sequence will increment itself.

 The keyword CYCLE specifies that if the maximum value exceeds the set
limit, sequence will restart its cycle from the begining.
  And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an
error will be thrown.

Using Sequence in SQL Query

Let's start by creating a sequence, which will start from 1, increment by 1 with a
maximum value of 999.

CREATE SEQUENCE seq_1

START WITH 1

INCREMENT BY 1

MAXVALUE 999

CYCLE;

Now let's use the sequence that we just created above.

Below we have a class table,

ID NAME

1 abhi

2 adam

4 alex

The SQL query will be,

INSERT INTO class VALUE(seq_1.nextval, 'anu');

Resultset table will look like,

ID NAME

1 abhi

2 adam

4 alex

1 anu

Once you use nextval the sequence will increment even if you don't Insert any
record into the table.

SQL VIEW
A VIEW in SQL is a logical subset of data from one or more tables. View is used
to restrict data access.

Creating a VIEW

Consider following Sale table,

oid order_name previous_balance customer

11 ord1 2000 Alex

12 ord2 1000 Adam

13 ord3 2000 Abhi

14 ord4 1000 Adam

15 ord5 2000 Alex

SQL Query to Create a View from the above table will be,

CREATE or REPLACE VIEW sale_view

AS

SELECT * FROM Sale WHERE customer = 'Alex';

The data fetched from SELECT statement will be stored in another object
called sale_view. We can use CREATE and REPLACE seperately too, but using
both together works better, as if any view with the specified name exists, this
query will replace it with fresh data.

Displaying a VIEW

The syntax for displaying the data in a view is similar to fetching data from a
table using a SELECT statement.

SELECT * FROM sale_view;

Force VIEW Creation

FORCE keyword is used while creating a view, forcefully. This keyword is used
to create a View even if the table does not exist. After creating a force View if
we create the base table and enter values in it, the view will be automatically
updated.

CREATE or REPLACE FORCE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;

Update a VIEW

Syntax to Update a View is,

UPDATE view-name SET VALUE

WHERE condition;

Copy
NOTE: If we update a view it also updates base table data automatically.

Read-Only VIEW

We can create a view with read-only option to restrict access to the view.

Syntax to create a view with Read-Only Access

CREATE or REPLACE FORCE VIEW view_name AS

SELECT column_name(s)
 FROM table_name

WHERE condition WITH read-only;

Copy
The above syntax will create view for read-only purpose, we cannot Update or
Insert data into read-only view. It will throw an error.

Types of View

There are two types of view,

 Simple View

 Complex View

Simple View Complex View

Created from one table Created from one or more table

Does not contain functions Contain functions

Does not contain groups of data Contains groups of data

UPDATE command for view is same as for tables

BETWEEN operator
The SQL BETWEEN condition allows you to easily test if an expression is within a range of
values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT,
INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the
records where expression is within the range of value1 and value2.
Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Examples:
Consider the following Employee Table,

Queries

 Using BETWEEN with Numeric Values:

List all the Employee Fname, Lname who is having salary between 30000 and 45000.

SELECT Fname, Lname

FROM Employee
WHERE Salary
BETWEEN 30000 AND 45000;
Output:

 Using BETWEEN with Date Values:

Find all the Employee having Date of Birth Between 01-01-1985 and 12-12-1990.

SELECT Fname, Lname

FROM Employee
where DOB
BETWEEN '1985-01-01' AND '1990-12-30';
Output:

 Using NOT operator with BETWEEN

Find all the Employee name whose salary is not in the range of 30000 and 45000.

SELECT Fname, Lname

FROM Emplyoee
WHERE Salary
NOT BETWEEN 30000 AND 45000;
Output:

IN
IN operator allows you to easily test if the expression matches any value in the list of values.
It is used to remove the need of multiple OR condition in SELECT, INSERT, UPDATE or
DELETE. You can also use NOT IN to exclude the rows in your list. We should note that any
kind of duplicate entry will be retained.
Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (list_of_values);

Queries

 Find the Fname, Lname of the Employees who have Salary equal to 30000, 40000 or
25000.

SELECT Fname, Lname

FROM Employee
WHERE Salary IN (30000, 40000, 25000);
Output:
 Find the Fname, Lname of all the Employee who have Salary not equal to 25000 or
30000.
SELECT Fname, Lname
FROM Employee
WHERE Salary NOT IN (25000, 30000);
Output: