What are the Constraints of DBMS?

In DBMS, constraints refer to limitations placed on data or data processes.

This indicates that only a particular type of data may be entered into the
database or that only a particular sort of operation can be performed on the
data inside.
Constraints thereby guarantee data accuracy in a database management
system (DBMS).
The following can be guaranteed via constraints
Data Accuracy − Data accuracy is guaranteed by constraints, which make
sure that only true data is entered into a database. For example, a limitation
may stop a user from entering a negative value into a field that only accepts
positive numbers.
Data Consistency − The consistency of data in a database can be upheld
by using constraints. These constraints are able to ensure that the primary
key value in one table is followed by the foreign key value in another table.
Data integrity − The accuracy and completeness of the data in a database
are ensured by constraints. For example, a constraint can stop a user from
putting a null value into a field that requires one.

Types of Constraints in DBMS

 Domain Constraints
 Key Constraints
 Entity Integrity Constraints
 Referential Integrity Constraints
 Tuple Uniqueness Constraints
Domain Constraints
In a database table, domain constraints are guidelines that specify the
acceptable values for a certain property or field. These restrictions
guarantee data consistency and aid in preventing the entry of inaccurate or
inconsistent data into the database. The following are some instances of
domain restrictions in a DBMS −
 Data type constraints − These limitations define the kinds of
data that can be kept in a column. A column created as VARCHAR
can take string values, but a column specified as INTEGER can
only accept integer values.
 Length Constraints − These limitations define the largest
amount of data that may be put in a column. For instance, a
column with the definition VARCHAR(10) may only take strings
that are up to 10 characters long.
 Range constraints − The allowed range of values for a column
is specified by range restrictions. A column designated as
 DECIMAL(5,2), for example, may only take decimal values up to 5
digits long, including 2 decimal places.
 Nullability constraints − Constraints on a column's capacity to
accept NULL values are known as nullability constraints. For
instance, a column that has the NOT NULL definition cannot take
NULL values.
 Unique constraints − Constraints that require the presence of
unique values in a column or group of columns are known as
unique constraints. For instance, duplicate values are not allowed
in a column with the UNIQUE definition.
 Check constraints − Constraints for checking data: These
constraints outline a requirement that must hold for any data
placed into the column. For instance, a column with the definition
CHECK (age > 0) can only accept ages that are greater than zero.
 Default constraints − Constraints by default: Default
constraints automatically assign a value to a column in case no
value is provided. For example, a column with a DEFAULT value
of 0 will have 0 as its value if no other value is specified.
Key Constraints
Key constraints are regulations that a DBMS uses to ensure data accuracy
and consistency in a database. They define how the values in a table's one
or more columns are related to the values in other tables, making sure that
the data remains correct.
In DBMS, there are several key constraint kinds, including −
 Primary Key Constraint − A primary key constraint is an
individual identifier for each record in a database. It guarantees
that each database entry contains a single, distinct value—or a
pair of values—that cannot be null—as its method of
identification.
 Foreign Key Constraint − Reference to the primary key in
another table is a foreign key constraint. It ensures that the
values of a column or set of columns in one table correspond to
the primary key column(s) in another table.
 Unique Constraint − In a database, a unique constraint ensures
that no two values inside a column or collection of columns are
the same.
Entity Integrity Constraints
A database management system uses entity integrity constraints (EICs) to
enforce rules that guarantee a table's primary key is unique and not null.
The consistency and integrity of the data in a database are maintained by
EICs, which are created to stop the formation of duplicate or incomplete
entries.
Each item in a table in a relational database is uniquely identified by one or
more fields known as the primary key. EICs make a guarantee that every
row's primary key value is distinct and not null. Take the "Employees" table,
for instance, which has the columns "EmployeeID" and "Name." The table's
primary key is the EmployeeID column. An EIC on this table would make
sure that each row's unique EmployeeID value is there and that it is not null.
If you try to insert an entry with a duplicate or null EmployeeID, the
database management system will reject the insertion and produce an error.
This guarantees that the information in the table is correct and consistent.
EICs are a crucial component of database architecture and assist guarantee
the accuracy and dependability of the data contained in a database.

Referential Integrity Constraints

A database management system will apply referential integrity constraints
(RICs) in order to preserve the consistency and integrity of connections
between tables. By preventing links between entries that don't exist from
being created or by removing records that have related records in other
tables, RICs guarantee that the data in a database is always consistent.
By the use of foreign keys, linkages between tables are created in relational
databases. A column or collection of columns in one table that is used as a
foreign key to access the primary key of another table. RICs make sure there
are no referential errors and that these relationships are legitimate.
Consider the "Orders" and "Customers" tables as an illustration. The primary
key column in the "Customers" database corresponds to the foreign key field
"CustomerID" in the "Orders" dataset. A RIC on this connection requires that
each value in the "CustomerID" column of the "Orders" database exist in the
"Customers" table's primary key column.
If an attempt was made to insert a record into the "Orders" table with a non-
existent "CustomerID" value, the database management system would
reject the insertion and notify the user of an error.
Similar to this, the database management system would either prohibit the
deletion or cascade the deletion in order to ensure referential integrity if a
record in the "Customers" table was removed and linked entries in the
"Orders" table.
In general, RICs are a crucial component of database architecture and assist
guarantee that the information contained in a database is correct and
consistent throughout time.

Tuple Uniqeness Contraints

A database management system uses constraints called tuple uniqueness
constraints (TUCs) to make sure that every entry or tuple in a table is
distinct. TUCs impose uniqueness on the whole row or tuple, in contrast to
Entity Integrity Constraints (EICs), which only enforce uniqueness on certain
columns or groups of columns.
TUCs, then, make sure that no two rows in a table have the same values for
every column. Even if the individual column values are not unique, this can
be helpful in cases when it is vital to avoid the production of duplicate
entries.
Consider the "Sales" table, for instance, which has the columns
"TransactionID," "Date," "CustomerID," and "Amount." Even if individual
column values could be duplicated, a TUC on this table would make sure
that no two rows have the same values in all four columns.
The database management system would reject the insertion and generate
an error if an attempt was made to enter a row with identical values in each
of the four columns as an existing entry. This guarantees the uniqueness
and accuracy of the data in the table.
TUCs may be a helpful tool for ensuring data correctness and consistency
overall, especially when it's vital to avoid the generation of duplicate entries.

What is SQL?
SQL is a short-form of the structured query language, and it is pronounced as
S-Q-L or sometimes as See-Quell.

This database language is mainly designed for maintaining the data in

relational database management systems. It is a special tool used by data
professionals for handling structured data (data which is stored in the form of
tables). It is also designed for stream processing in RDSMS.

You can easily create and manipulate the database, access and modify the
table rows and columns, etc

Suppose you want to perform the queries of SQL language on the stored data
in the database. You are required to install any database management
system in your systems, for
example, Oracle, MySQL, MongoDB, PostgreSQL, SQL Server, DB2, etc.

Why SQL?
The IBM researchers Raymond Boyce and Donald Chamberlin originally
developed the SEQUEL (Structured English Query Language) after learning
from the paper given by E.F. Codd. They both developed the SQL at the San
Jose Research laboratory of IBM Corporation in 1970. This SQL was totally
based on RDBMS .Nowadays, SQL is widely used in data science and
analytics. Following are the reasons which explain why it is widely used:

o The basic use of SQL for data professionals and SQL users is to insert,
update, and delete the data from the relational database.
o SQL allows the data professionals and users to retrieve the data from
the relational database management systems.
o It also helps them to describe the structured data.
o It allows SQL users to create, drop, and manipulate the database and
its tables.
o It also helps in creating the view, stored procedure, and functions in
the relational database.
o It allows you to define the data and modify that stored data in the
relational database.
o It also allows SQL users to set the permissions or constraints on table
columns, views, and stored procedures.

Structured Query Language contains the following four components in its

process:

o Query Dispatcher
o Optimization Engines
o Classic Query Engine
o SQL Query Engine, etc.
Some SQL Commands
The SQL commands help in creating and managing the database. The most
common SQL commands which are highly used are mentioned below:

1. CREATE command
2. UPDATE command
3. DELETE command
4. SELECT command
5. DROP command
6. INSERT command

CREATE Command
This command helps in creating the new database, new table, table view,
and other objects of the database.

AD

UPDATE Command
This command helps in updating or changing the stored data in the
database.
DELETE Command
This command helps in removing or erasing the saved records from the
database tables. It erases single or multiple tuples from the tables of the
database.

SELECT Command
This command helps in accessing the single or multiple rows from one or
multiple tables of the database. We can also use this command with the
WHERE clause.

DROP Command
This command helps in deleting the entire table, table view, and other
objects from the database.

INSERT Command
This command helps in inserting the data or records into the database
tables. We can easily insert the records in single as well as multiple rows of
the table.

Advantages of SQL
SQL provides various advantages which make it more popular in the field of
data science. It is a perfect query language which allows data professionals
and users to communicate with the database. Following are the best
advantages or benefits of Structured Query Language:

1. No programming needed

SQL does not require a large number of coding lines for managing the
database systems. We can easily access and maintain the database by using
simple SQL syntactical rules. These simple rules make the SQL user-friendly.

2. High-Speed Query Processing

A large amount of data is accessed quickly and efficiently from the database
by using SQL queries. Insertion, deletion, and updation operations on data
are also performed in less time.

3. Standardized Language
SQL follows the long-established standards of ISO and ANSI, which offer a
uniform platform across the globe to all its users.

4. Portability

The structured query language can be easily used in desktop computers,

laptops, tablets, and even smartphones. It can also be used with other
applications according to the user's requirements.

5. Interactive language

We can easily learn and understand the SQL language. We can also use this
language for communicating with the database because it is a simple query
language. This language is also used for receiving the answers to complex
queries in a few seconds.

6. More than one Data View

The SQL language also helps in making the multiple views of the database
structure for the different database users.

Disadvantages of SQL
With the advantages of SQL, it also has some disadvantages, which are as
follows:

1. Cost

The operation cost of some SQL versions is high. That's why some
programmers cannot use the Structured Query Language.

2. Interface is Complex

Another big disadvantage is that the interface of Structured query language

is difficult, which makes it difficult for SQL users to use and manage it.

3. Partial Database control

The business rules are hidden. So, the data professionals and users who are
using this query language cannot have full database control.
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.
AD

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.

Syntax:

1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100

), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the
table.

Syntax

1. DROP TABLE table_name;

Example

1. DROP TABLE EMPLOYEE;

c. 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

1. ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

 1. ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE

1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the
space containing the table.

Syntax:

1. TRUNCATE TABLE table_name;

Example:

1. TRUNCATE TABLE EMPLOYEE;

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:

AD

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

2. VALUES (value1, value2, value3, .... valueN);

For example:

1. INSERT INTO book (Author, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a

column in the table.

AD

Syntax:

1. UPDATE table_name SET [column_name1= value1,...column_nameN =

valueN] [WHERE CONDITION]

For example:

1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

1. DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";
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.

Example

1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USE

R;

b. Revoke: It is used to take back permissions from the user.

Example

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

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.

Syntax:

1. COMMIT;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not
already been saved to the database.

Syntax:

1. ROLLBACK;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point

without rolling back the entire transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language
DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation of relational

algebra. It is used to select the attribute based on the condition described by
WHERE clause.

Syntax:

1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;

For example:

1. SELECT emp_name
2. FROM employee
 3. WHERE age > 20;

SQL Create Database

In SQL, the 'Create Database' statement is a first step for storing the
structured data in the database.

The database developers and the users use this statement in SQL for
creating the new database in the database systems. It creates the database
with the name which has been specified in the Create Database statement.

Create Database statement in SQL

1. CREATE DATABASE Database_Name;

Example 1:

This example creates the Student database. To create the Student

database, you have to type the following command in Structured Query
Language:

1. CREATE DATABASE Student ;

When this query is executed successfully, then it will show the following
output:

Database created successfully

You can also verify that your database is created in SQL or not by using the
following query:

SHOW DATABASE ;

Drop Database in SQL

2. DROP DATABASE Database_Name;

In this SQL syntax, we have to specify the name of that database which we
want to delete permanently from the database system.
Rename Database in SQL
Suppose we want to rename the Student Database. For this, we have to type
the following query in SQL:

1. ALTER DATABASE Student MODIFY NAME = College ;

This query will change the name of the database from Student to College. To
run this query, we must ensure that the database Student exists in the
current database server.

SQL SELECT Database

Syntax of USE statement in SQL

1. USE database_name;

Most Important SQL Commands and Statements

1. Select Statement
2. Update Statement
3. Delete Statement
4. Create Table Statement
5. Alter Table Statement
6. Drop Table Statement
7. Create Database Statement
8. Drop Database Statement
9. Insert Into Statement
10. Truncate Table Statement
11. Describe Statement
12. Distinct Clause
13. Commit Statement
14. Rollback Statement
 15. Create Index Statement
16. Drop Index Statement
17. Use Statement

Let's discuss each statement in short one by one with syntax and
one example:

1. SELECT Statement
This SQL statement reads the data from the SQL database and shows it as
the output to the database user.

Syntax of SELECT Statement:

1. SELECT column_name1, column_name2, .…, column_nameN

2. [ FROM table_name ]
3. [ WHERE condition ]
4. [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];

Example of SELECT Statement:

1. SELECT Emp_ID, First_Name, Last_Name, Salary, City

2. FROM Employee_details
3. WHERE Salary = 100000
4. ORDER BY Last_Name

This example shows the Emp_ID, First_Name, Last_Name, Salary, and

City of those employees from the Employee_details table
whose Salary is 100000. The output shows all the specified details
according to the ascending alphabetical order of Last_Name.

3. UPDATE Statement
This SQL statement changes or modifies the stored data in the SQL
database.

Syntax of UPDATE Statement:

1. UPDATE table_name
 2. SET column_name1 = new_value_1, column_name2 = new_value_2, ...
., column_nameN = new_value_N
3. [ WHERE CONDITION ];

Example of UPDATE Statement:

1. UPDATE Employee_details
2. SET Salary = 100000
3. WHERE Emp_ID = 10;

This example changes the Salary of those employees of

the Employee_details table whose Emp_ID is 10 in the table.

3. DELETE Statement
This SQL statement deletes the stored data from the SQL database.

AD

Syntax of DELETE Statement:

1. DELETE FROM table_name

2. [ WHERE CONDITION ];

Example of DELETE Statement:

1. DELETE FROM Employee_details

2. WHERE First_Name = 'Sumit';

This example deletes the record of those employees from

the Employee_details table whose First_Name is Sumit in the table.

4. CREATE TABLE Statement

This SQL statement creates the new table in the SQL database.

Syntax of CREATE TABLE Statement:

AD

1. CREATE TABLE table_name

2. (
 3. column_name1 data_type [column1 constraint(s)],
4. column_name2 data_type [column2 constraint(s)],
5. .....
6. .....,
7. column_nameN data_type [columnN constraint(s)],
8. PRIMARY KEY(one or more col)
9. );

Example of CREATE TABLE Statement:

1. CREATE TABLE Employee_details(

2. Emp_Id NUMBER(4) NOT NULL,
3. First_name VARCHAR(30),
4. Last_name VARCHAR(30),
5. Salary Money,
6. City VARCHAR(30),
7. PRIMARY KEY (Emp_Id)
8. );

This example creates the table Employee_details with five columns or

fields in the SQL database. The fields in the table are Emp_Id, First_Name,
Last_Name, Salary, and City. The Emp_Id column in the table acts as
a primary key, which means that the Emp_Id column cannot contain
duplicate values and null values.

5. ALTER TABLE Statement

This SQL statement adds, deletes, and modifies the columns of the table in
the SQL database.

Syntax of ALTER TABLE Statement:

1. ALTER TABLE table_name ADD column_name datatype[(size)];

The above SQL alter statement adds the column with its datatype in the
existing database table.

1. ALTER TABLE table_name MODIFY column_name column_datatype[(

size)];
The above 'SQL alter statement' renames the old column name to the new
column name of the existing database table.

1. ALTER TABLE table_name DROP COLUMN column_name;

The above SQL alter statement deletes the column of the existing database
table.

Example of ALTER TABLE Statement:

1. ALTER TABLE Employee_details

2. ADD Designation VARCHAR(18);

This example adds the new field whose name is Designation with size 18 in
the Employee_details table of the SQL database.

6. DROP TABLE Statement

This SQL statement deletes or removes the table and the structure, views,
permissions, and triggers associated with that table.

Syntax of DROP TABLE Statement:

1. DROP TABLE [ IF EXISTS ]

2. table_name1, table_name2, ……, table_nameN;

The above syntax of the drop statement deletes specified tables completely
if they exist in the database.

Example of DROP TABLE Statement:

1. DROP TABLE Employee_details;

This example drops the Employee_details table if it exists in the SQL

database. This removes the complete information if available in the table.

7. CREATE DATABASE Statement

This SQL statement creates the new database in the database management
system.

Syntax of CREATE DATABASE Statement:

 1. CREATE DATABASE database_name;

Example of CREATE DATABASE Statement:

1. CREATE DATABASE Company;

The above example creates the company database in the system.

8. DROP DATABASE Statement

This SQL statement deletes the existing database with all the data tables and
views from the database management system.

Syntax of DROP DATABASE Statement:

1. DROP DATABASE database_name;

Example of DROP DATABASE Statement:

1. DROP DATABASE Company;

The above example deletes the company database from the system.

9. INSERT INTO Statement

This SQL statement inserts the data or records in the existing table of the
SQL database. This statement can easily insert single and multiple records in
a single query statement.

Syntax of insert a single record:

1. INSERT INTO table_name

2. (
3. column_name1,
4. column_name2, .…,
5. column_nameN
6. )
7. VALUES
8. (value_1,
9. value_2, ..…,
 10. value_N
11. );

Example of insert a single record:

1. INSERT INTO Employee_details

2. (
3. Emp_ID,
4. First_name,
5. Last_name,
6. Salary,
7. City
8. )
9. VALUES
10. (101,
11. Akhil,
12. Sharma,
13. 40000,
14. Bangalore
15. );

This example inserts 101 in the first column, Akhil in the second
column, Sharma in the third column, 40000 in the fourth column,
and Bangalore in the last column of the table Employee_details.

Syntax of inserting a multiple records in a single query:

1. INSERT INTO table_name

2. ( column_name1, column_name2, .…, column_nameN)
3. VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..…, value
_N),….;

Example of inserting multiple records in a single query:

1. INSERT INTO Employee_details

2. ( Emp_ID, First_name, Last_name, Salary, City )
 3. VALUES (101, Amit, Gupta, 50000, Mumbai), (101, John, Aggarwal, 45
000, Calcutta), (101, Sidhu, Arora, 55000, Mumbai);

This example inserts the records of three employees in

the Employee_details table in the single query statement.

10. TRUNCATE TABLE Statement

This SQL statement deletes all the stored records from the table of the SQL
database.

Syntax of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE table_name;

Example of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE Employee_details;

This example deletes the record of all employees from the Employee_details
table of the database.

11. DESCRIBE Statement

This SQL statement tells something about the specified table or view in the
query.

Syntax of DESCRIBE Statement:

1. DESCRIBE table_name | view_name;

Example of DESCRIBE Statement:

1. DESCRIBE Employee_details;

This example explains the structure and other details about

the Employee_details table.

12. DISTINCT Clause

This SQL statement shows the distinct values from the specified columns of
the database table. This statement is used with the SELECT keyword.
Syntax of DISTINCT Clause:

1. SELECT DISTINCT column_name1, column_name2, ...

2. FROM table_name;

Example of DISTINCT Clause:

1. SELECT DISTINCT City, Salary

2. FROM Employee_details;

This example shows the distinct values of the City and Salary column from
the Employee_details table.

13. COMMIT Statement

This SQL statement saves the changes permanently, which are done in the
transaction of the SQL database.

Syntax of COMMIT Statement:

1. COMMIT

Example of COMMIT Statement:

1. DELETE FROM Employee_details

2. WHERE salary = 30000;
3. COMMIT;

This example deletes the records of those employees

whose Salary is 30000 and then saves the changes permanently in the
database.

14. ROLLBACK Statement

This SQL statement undo the transactions and operations which are not yet
saved to the SQL database.

Syntax of ROLLBACK Statement:

1. ROLLBACK

Example of ROLLBACK Statement:

 1. DELETE FROM Employee_details
2. WHERE City = Mumbai;
3. ROLLBACK;

This example deletes the records of those employees

whose City is Mumbai and then undo the changes in the database.

15. CREATE INDEX Statement

This SQL statement creates the new index in the SQL database table.

Syntax of CREATE INDEX Statement:

1. CREATE INDEX index_name

2. ON table_name ( column_name1, column_name2, …, column_nameN );

Example of CREATE INDEX Statement:

1. CREATE INDEX idx_First_Name

2. ON employee_details (First_Name);

This example creates an index idx_First_Name on the First_Name column

of the Employee_details table.

16. DROP INDEX Statement

This SQL statement deletes the existing index of the SQL database table.

Syntax of DROP INDEX Statement:

1. DROP INDEX index_name;

Example of DROP INDEX Statement:

1. DROP INDEX idx_First_Name;

This example deletes the index idx_First_Name from the SQL database.
17. USE Statement
This SQL statement selects the existing SQL database. Before performing the
operations on the database table, you have to select the database from the
multiple existing databases.

Syntax of USE Statement:

1. USE database_name;

Example of USE DATABASE Statement:

1. USE Company;

This example uses the company database.

SQL Data Types

Data types are used to represent the nature of the data that can be stored in
the database table. For example, in a particular column of a table, if we want
to store a string type of data then we will have to declare a string data type
of this column.

Data types mainly classified into three categories for every database.

o String Data types

o Numeric Data types
o Date and time Data types

CHAR(Size) It is used to specify a fixed length string that can contain

numbers, letters, and special characters. Its size can be 0 to
255 characters. Default is 1.

VARCHAR(Size It is used to specify a variable length string that can contain

) numbers, letters, and special characters. Its size can be from 0
to 65535 characters.

BINARY(Size) It is equal to CHAR() but stores binary byte strings. Its size
parameter specifies the column length in the bytes. Default is
1.
 VARBINARY(Si It is equal to VARCHAR() but stores binary byte strings. Its size
ze) parameter specifies the maximum column length in bytes.

TEXT(Size) It holds a string that can contain a maximum length of 255

characters.

What is SQL Operator?

The SQL reserved words and characters are called operators, which are used
with a WHERE clause in a SQL query. In SQL, an operator can either be a
unary or binary operator. The unary operator uses only one operand for
performing the unary operation, whereas the binary operator uses two
operands for performing the binary operation.

Types of Operator
SQL operators are categorized in the following categories:

1. SQL Arithmetic Operators

2. SQL Comparison Operators
3. SQL Logical Operators
4. SQL Set Operators
5. SQL Bit-wise Operators
6. SQL Unary Operators

Let's discuss each operator with their types.

SQL Arithmetic Operators

The Arithmetic Operators perform the mathematical operation on the
numerical data of the SQL tables. These operators perform addition,
subtraction, multiplication, and division operations on the numerical
operands.

Following are the various arithmetic operators performed on the

SQL data:

1. SQL Addition Operator (+)

 2. SQL Subtraction Operator (-)
3. SQL Multiplication Operator (+)
4. SQL Division Operator (-)
5. SQL Modulus Operator (+)

SQL Addition Operator (+)

The Addition Operator in SQL performs the addition on the numerical data
of the database table. In SQL, we can easily add the numerical values of two
columns of the same table by specifying both the column names as the first
and second operand. We can also add the numbers to the existing numbers
of the specific column.

Syntax of SQL Addition Operator:

1. SELECT operand1 + operand2;

Let's understand the below example which explains how to execute

Addition Operator in SQL query:

AD

This example consists of an Employee_details table, which has four

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp Id Emp Name Emp Salary Emp Monthlybonus

101 Tushar 25000 4000

102 Anuj 30000 200

o Suppose, we want to add 20,000 to the salary of each employee

specified in the table. Then, we have to write the following query in the
SQL:

1. SELECT Emp_Salary + 20000 as Emp_New_Salary FROM Employee_det

ails;

In this query, we have performed the SQL addition operation on the single
column of the given table.
 o Suppose, we want to add the Salary and monthly bonus columns of the
above table, then we have to write the following query in SQL:

AD

1. SELECT Emp_Salary + Emp_Monthlybonus as Emp_Total_Salary FROM

Employee_details;

In this query, we have added two columns with each other of the above
table.

AD

SQL Subtraction Operator (-)

The Subtraction Operator in SQL performs the subtraction on the numerical
data of the database table. In SQL, we can easily subtract the numerical
values of two columns of the same table by specifying both the column
names as the first and second operand. We can also subtract the number
from the existing number of the specific table column.

Syntax of SQL Subtraction Operator:

AD

1. SELECT operand1 - operand2;

Let's understand the below example which explains how to execute

Subtraction Operator in SQL query:

This example consists of an Employee_details table, which has four

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp Id Emp Name Emp Salary Penalty

201 Abhay 25000 200

202 Sumit 30000 500

o Suppose we want to subtract 5,000 from the salary of each employee

given in the Employee_details table. Then, we have to write the
following query in the SQL:
 1. SELECT Emp_Salary - 5000 as Emp_New_Salary FROM Employee_detail
s;

In this query, we have performed the SQL subtraction operation on the single
column of the given table.

o If we want to subtract the penalty from the salary of each employee,

then we have to write the following query in SQL:

1. SELECT Emp_Salary - Penalty as Emp_Total_Salary FROM Employee_det

ails;

SQL Multiplication Operator (*)

The Multiplication Operator in SQL performs the Multiplication on the
numerical data of the database table. In SQL, we can easily multiply the
numerical values of two columns of the same table by specifying both the
column names as the first and second operand.

Syntax of SQL Multiplication Operator:

1. SELECT operand1 * operand2;

Let's understand the below example which explains how to execute

Multiplication Operator in SQL query:

This example consists of an Employee_details table, which has four

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_Monthlybonus.

Emp Id Emp Name Emp Salary Penalty

201 Abhay 25000 200

202 Sumit 30000 500

o Suppose, we want to double the salary of each employee given in

the Employee_details table. Then, we have to write the following
query in the SQL:

1. SELECT Emp_Salary * 2 as Emp_New_Salary FROM Employee_details;

In this query, we have performed the SQL multiplication operation on the
single column of the given table.

o If we want to multiply the Emp_Id column to Emp_Salary column of

that employee whose Emp_Id is 202, then we have to write the
following query in SQL:

1. SELECT Emp_Id * Emp_Salary as Emp_Id * Emp_Salary FROM Employee

_details WHERE Emp_Id = 202;

In this query, we have multiplied the values of two columns by using the
WHERE clause.

SQL Division Operator (/)

The Division Operator in SQL divides the operand on the left side by the
operand on the right side.

Syntax of SQL Division Operator:

1. SELECT operand1 / operand2;

In SQL, we can also divide the numerical values of one column by another
column of the same table by specifying both column names as the first and
second operand.

We can also perform the division operation on the stored numbers in the
column of the SQL table.

Let's understand the below example which explains how to execute

Division Operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 25000

202 Sumit 30000

 o Suppose, we want to half the salary of each employee given in the
Employee_details table. For this operation, we have to write the
following query in the SQL:

1. SELECT Emp_Salary / 2 as Emp_New_Salary FROM Employee_details;

In this query, we have performed the SQL division operation on the single
column of the given table.

SQL Modulus Operator (%)

The Modulus Operator in SQL provides the remainder when the operand on
the left side is divided by the operand on the right side.

Syntax of SQL Modulus Operator:

1. SELECT operand1 % operand2;

Let's understand the below example which explains how to execute

Modulus Operator in SQL query:

This example consists of a Division table, which has three

columns Number, First_operand, and Second_operand.

Number First operand Second operand

1 56 4

2 32 8

3 89 9

4 18 10

5 10 5

o If we want to get the remainder by dividing the numbers of

First_operand column by the numbers of Second_operand column, then
we have to write the following query in SQL:

1. SELECT First_operand % Second_operand as Remainder FROM Employe

e_details;
SQL Comparison Operators
The Comparison Operators in SQL compare two different data of SQL table
and check whether they are the same, greater, and lesser. The SQL
comparison operators are used with the WHERE clause in the SQL queries

Following are the various comparison operators which are

performed on the data stored in the SQL database tables:

1. SQL Equal Operator (=)

2. SQL Not Equal Operator (!=)
3. SQL Greater Than Operator (>)
4. SQL Greater Than Equals to Operator (>=)
5. SQL Less Than Operator (<)\
6. SQL Less Than Equals to Operator (<=)

SQL Equal Operator (=)

This operator is highly used in SQL queries. The Equal Operator in SQL
shows only data that matches the specified value in the query.

This operator returns TRUE records from the database table if the value of
both operands specified in the query is matched.

Let's understand the below example which explains how to execute

Equal Operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 30000

202 Ankit 40000

203 Bheem 30000

204 Ram 29000

205 Sumit 30000

 o Suppose, we want to access all the records of those employees from
the Employee_details table whose salary is 30000. Then, we have to
write the following query in the SQL database:

1. SELECT * FROM Employee_details WHERE Emp_Salary = 30000;

In this example, we used the SQL equal operator with WHERE clause for
getting the records of those employees whose salary is 30000.

SQL Equal Not Operator (!=)

The Equal Not Operator in SQL shows only those data that do not match
the query's specified value.

This operator returns those records or rows from the database views and
tables if the value of both operands specified in the query is not matched
with each other.

Let's understand the below example which explains how to execute

Equal Not Operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

o Suppose, we want to access all the records of those employees from

the Employee_details table whose salary is not 45000. Then, we
have to write the following query in the SQL database:

1. SELECT * FROM Employee_details WHERE Emp_Salary != 45000;

In this example, we used the SQL equal not operator with WHERE clause for
getting the records of those employees whose salary is not 45000.

SQL Greater Than Operator (>)

The Greater Than Operator in SQL shows only those data which are
greater than the value of the right-hand operand.

Let's understand the below example which explains how to execute

Greater ThanOperator (>) in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

o Suppose, we want to access all the records of those employees from

the Employee_details table whose employee id is greater than 202.
Then, we have to write the following query in the SQL database:

1. SELECT * FROM Employee_details WHERE Emp_Id > 202;

Here, SQL greater than operator displays the records of those employees
from the above table whose Employee Id is greater than 202.

SQL Greater Than Equals to Operator (>=)

The Greater Than Equals to Operator in SQL shows those data from the
table which are greater than and equal to the value of the right-hand
operand.

Let's understand the below example which explains how to execute

greater than equals to the operator (>=) in SQL query:
This example consists of an Employee_details table, which has three
columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

o Suppose, we want to access all the records of those employees from

the Employee_details table whose employee id is greater than and
equals to 202. For this, we have to write the following query in the SQL
database:

1. SELECT * FROM Employee_details WHERE Emp_Id >= 202;

Here,'SQL greater than equals to operator' with WHERE clause displays

the rows of those employees from the table whose Employee Id is greater
than and equals to 202.

SQL Less Than Operator (<)

The Less Than Operator in SQL shows only those data from the database
tables which are less than the value of the right-side operand.

This comparison operator checks that the left side operand is lesser than the
right side operand. If the condition becomes true, then this operator in SQL
displays the data which is less than the value of the right-side operand.

Let's understand the below example which explains how to execute

less than operator (<) in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

205 Sumit 29000

o Suppose, we want to access all the records of those employees from

the Employee_details table whose employee id is less than 204. For
this, we have to write the following query in the SQL database:

1. SELECT * FROM Employee_details WHERE Emp_Id < 204;

Here,SQL less than operator with WHERE clause displays the records of
those employees from the above table whose Employee Id is less than 204.

SQL Less Than Equals to Operator (<=)

The Less Than Equals to Operator in SQL shows those data from the table
which are lesser and equal to the value of the right-side operand.

This comparison operator checks that the left side operand is lesser and
equal to the right side operand.

Let's understand the below example which explains how to execute

less than equals to the operator (<=) in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, and Emp_Salary.

Emp Id Emp Name Emp Salary

201 Abhay 45000

202 Ankit 45000

203 Bheem 30000

204 Ram 29000

 205 Sumit 29000

o Suppose, we want to access all the records of those employees from

the Employee_details table whose employee id is less and
equals 203. For this, we have to write the following query in the SQL
database:

1. SELECT * FROM Employee_details WHERE Emp_Id <= 203;

Here, SQL less than equals to the operator with WHERE clause displays
the rows of those employees from the table whose Employee Id is less than
and equals 202.

SQL Logical Operators

The Logical Operators in SQL perform the Boolean operations, which give
two results True and False. These operators provide True value if both
operands match the logical condition.

Following are the various logical operators which are performed on

the data stored in the SQL database tables:

1. SQL ALL operator

2. SQL AND operator
3. SQL OR operator
4. SQL BETWEEN operator
5. SQL IN operator
6. SQL NOT operator
7. SQL ANY operator
8. SQL LIKE operator

SQL ALL Operator

The ALL operator in SQL compares the specified value to all the values of a
column from the sub-query in the SQL database.

This operator is always used with the following statement:

 1. SELECT,
2. HAVING, and
3. WHERE.

Syntax of ALL operator:

1. SELECT column_Name1, ...., column_NameN FROM table_Name WHERE

column Comparison_operator ALL (SELECT column FROM tablename2)

Let's understand the below example which explains how to execute

ALL logical operators in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Gurgaon

202 Ankit 45000 Delhi

203 Bheem 30000 Jaipur

204 Ram 29000 Mumbai

205 Sumit 40000 Kolkata

o If we want to access the employee id and employee names of those

employees from the table whose salaries are greater than the salary of
employees who lives in Jaipur city, then we have to type the following
query in SQL.

1. SELECT Emp_Id, Emp_Name FROM Employee_details WHERE Emp_Sala

ry > ALL ( SELECT Emp_Salary FROM Employee_details WHERE Emp_Ci
ty = Jaipur)

Here, we used the SQL ALL operator with greater than the operator.
SQL AND Operator
The AND operator in SQL would show the record from the database table if
all the conditions separated by the AND operator evaluated to True. It is also
known as the conjunctive operator and is used with the WHERE clause.

Syntax of AND operator:

1. SELECT column1, ...., columnN FROM table_Name WHERE condition1 A

ND condition2 AND condition3 AND ....... AND conditionN;

Let's understand the below example which explains how to execute

AND logical operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

o Suppose, we want to access all the records of those employees from

the Employee_details table whose salary is 25000 and the city is
Delhi. For this, we have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Salary = 25000 O
R Emp_City = 'Delhi';

Here,SQL AND operator with WHERE clause shows the record of

employees whose salary is 25000 and the city is Delhi.
SQL OR Operator
The OR operator in SQL shows the record from the table if any of the
conditions separated by the OR operator evaluates to True. It is also known
as the conjunctive operator and is used with the WHERE clause.

Syntax of OR operator:

1. SELECT column1, ...., columnN FROM table_Name WHERE condition1 O

R condition2 OR condition3 OR ....... OR conditionN;

Let's understand the below example which explains how to execute

OR logical operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

o If we want to access all the records of those employees from

the Employee_details table whose salary is 25000 or the city is Delhi.
For this, we have to write the following query in SQL:

1. SELECT * FROM Employee_details WHERE Emp_Salary = 25000 OR Em

p_City = 'Delhi';

Here, SQL OR operator with WHERE clause shows the record of employees
whose salary is 25000 or the city is Delhi.
SQL BETWEEN Operator
The BETWEEN operator in SQL shows the record within the range
mentioned in the SQL query. This operator operates on the numbers,
characters, and date/time operands.

If there is no value in the given range, then this operator shows NULL value.

Syntax of BETWEEN operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM tabl

e_Name WHERE column_nameBETWEEN value1 and value2;

Let's understand the below example which explains how to execute

BETWEEN logical operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

o Suppose, we want to access all the information of those employees

from the Employee_details table who is having salaries between
20000 and 40000. For this, we have to write the following query in
SQL:

1. SELECT * FROM Employee_details WHERE Emp_Salary BETWEEN 30000

AND 45000;

Here, we used the SQL BETWEEN operator with the Emp_Salary field.
SQL IN Operator
The IN operator in SQL allows database users to specify two or more values
in a WHERE clause. This logical operator minimizes the requirement of
multiple OR conditions.

This operator makes the query easier to learn and understand. This operator
returns those rows whose values match with any value of the given list.

Syntax of IN operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM tabl

e_Name WHERE column_name IN (list_of_values);

Let's understand the below example which explains how to execute

IN logical operator in SQL query:

This example consists of an Employee_details table, which has three

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

o Suppose, we want to show all the information of those employees from

the Employee_details table whose Employee Id is 202, 204, and
205. For this, we have to write the following query in SQL:

1. SELECT * FROM Employee_details WHERE Emp_Id IN (202, 204, 205);

Here, we used the SQL IN operator with the Emp_Id column.

 o Suppose, we want to show all the information of those employees from
the Employee_details table whose Employee Id is not equal to 202
and 205. For this, we have to write the following query in SQL:

1. SELECT * FROM Employee_details WHERE Emp_Id NOT IN (202,205);

2.

Here, we used the SQL NOT IN operator with the Emp_Id column.

SQL NOT Operator

The NOT operator in SQL shows the record from the table if the condition
evaluates to false. It is always used with the WHERE clause.

Syntax of NOT operator:

1. SELECT column1, column2 ...., columnN FROM table_Name WHERE NO

T condition;

Let's understand the below example which explains how to execute

NOT logical operator in SQL query:

This example consists of an Employee_details table, which has four

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Abhay 25000 Delhi

202 Ankit 45000 Chandigarh

203 Bheem 30000 Delhi

204 Ram 25000 Delhi

205 Sumit 40000 Kolkata

o Suppose, we want to show all the information of those employees from

the Employee_details table whose Cityis not Delhi. For this, we have
to write the following query in SQL:
 1. SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' ;

In this example, we used the SQL NOT operator with the Emp_City column.

o Suppose, we want to show all the information of those employees from

the Employee_details table whose Cityis not Delhi and Chandigarh.
For this, we have to write the following query in SQL:

1. SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' AND

NOT Emp_City = 'Chandigarh';

In this example, we used the SQL NOT operator with the Emp_City column.

SQL ANY Operator

The ANY operator in SQL shows the records when any of the values
returned by the sub-query meet the condition.

The ANY logical operator must match at least one record in the inner query
and must be preceded by any SQL comparison operator.

Syntax of ANY operator:

1. SELECT column1, column2 ...., columnN FROM table_Name WHERE colu

mn_name comparison_operator ANY ( SELECT column_name FROM tabl
e_name WHERE condition(s)) ;
SQL LIKE Operator
The LIKE operator in SQL shows those records from the table which match
with the given pattern specified in the sub-query.

The percentage (%) sign is a wildcard which is used in conjunction with this
logical operator.

This operator is used in the WHERE clause with the following three
statements:

1. SELECT statement
2. UPDATE statement
3. DELETE statement
Syntax of LIKE operator:

1. SELECT column_Name1, column_Name2 ...., column_NameN FROM tabl

e_Name WHERE column_name LIKE pattern;

Let's understand the below example which explains how to execute

LIKE logical operator in SQL query:

This example consists of an Employee_details table, which has four

columns Emp_Id, Emp_Name, Emp_Salary, and Emp_City.

Emp Id Emp Name Emp Salary Emp City

201 Sanjay 25000 Delhi

202 Ajay 45000 Chandigarh

203 Saket 30000 Delhi

204 Abhay 25000 Delhi

205 Sumit 40000 Kolkata

o If we want to show all the information of those employees from

the Employee_details whose name starts with ''s''. For this, we have
to write the following query in SQL:

1. SELECT * FROM Employee_details WHERE Emp_Name LIKE 's%' ;

In this example, we used the SQL LIKE operator with Emp_Name column
because we want to access the record of those employees whose name
starts with s.

o If we want to show all the information of those employees from

the Employee_detailswhose name ends with ''y''. For this, we have to
write the following query in SQL:

1. SELECT * FROM Employee_details WHERE Emp_Name LIKE '%y' ;

o If we want to show all the information of those employees from
the Employee_detailswhose name starts with ''S'' and ends with ''y''.
For this, we have to write the following query in SQL:
1. SELECT * FROM Employee_details WHERE Emp_Name LIKE 'S%y' ;