What is Database

The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also
used to organize the data in the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

o Database management system is a software which is used to manage the database. For example: MySQL, Oracle,
etc are a very popular commercial database which is used in different applications.
o DBMS provides an interface to perform various operations like database creation, storing data in it, updating data,
creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users, it also maintains data
consistency.

DBMS allows users the following tasks:

o Data Definition: It is used for creation, modification, and removal of definition that defines the organization of
data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various
purposes.
o User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data
security, dealing with concurrency control, monitoring performance and recovering information corrupted by
unexpected failure.

Characteristics of DBMS
o It uses a digital repository established on a server to store and manage the information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID ( Atomicity, Consistency, Isolation, and Durability) properties which maintain data in a healthy
state in case of failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
o It can view the database from different viewpoints according to the requirements of the user.

Advantages of DBMS
o Controls database redundancy: It can control data redundancy because it stores all the data in one single
database file and that recorded data is placed in the database.
 o Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create automatic backup of data
from hardware and software failures and restores the data if required.
o multiple user interface: It provides different types of user interfaces like graphical user interfaces, application
program interfaces

Disadvantages of DBMS
o Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS
software.
o Size: It occupies a large space of disks and large memory to run them efficiently.
o Complexity: Database system creates additional complexity and requirements.
o Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the
data stored in a single database and if the database is damaged due to electric failure or database corruption then
the data may be lost forever.

What is RDBMS (Relational Database Management System)

RDBMS stands for Relational Database Management System.

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are
based on RDBMS.

It is called Relational Database Management System (RDBMS) because it is based on the relational model introduced by
E.F. Codd.

How it works
Data is represented in terms of tuples (rows) in RDBMS.

A relational database is the most commonly used database. It contains several tables, and each table has its primary key.

Due to a collection of an organized set of tables, data can be accessed easily in RDBMS.

Brief History of RDBMS

From 1970 to 1972, E.F. Codd published a paper to propose using a relational database model.

RDBMS is originally based on E.F. Codd's relational model invention.

Following are the various terminologies of RDBMS:

What is table/Relation?
Everything in a relational database is stored in the form of relations. The RDBMS database uses tables to store data. A
table is a collection of related data entries and contains rows and columns to store data. Each table represents some real-
world objects such as person, place, or event about which information is collected. The organized collection of data into a
relational table is known as the logical view of the database.

Properties of a Relation:

o Each relation has a unique name by which it is identified in the database.

o Relation does not contain duplicate tuples.
o The tuples of a relation have no specific order.
o All attributes in a relation are atomic, i.e., each cell of a relation contains exactly one value.

A table is the simplest example of data stored in RDBMS.

Let's see the example of the student table.

ID Name AGE COURSE

1 Ajeet 24 [Link]

2 aryan 20 C.A

3 Mahesh 21 BCA

4 Ratan 22 MCA

5 Vimal 26 BSC
What is a row or record?
A row of a table is also called a record or tuple. It contains the specific information of each entry in the table. It is a
horizontal entity in the table. For example, The above table contains 5 records.

Properties of a row:

o No two tuples are identical to each other in all their entries.

o All tuples of the relation have the same format and the same number of entries.
o The order of the tuple is irrelevant. They are identified by their content, not by their position.

Let's see one record/row in the table.

ID Name AGE COURSE

1 Ajeet 24 [Link]

What is a column/attribute?
A column is a vertical entity in the table which contains all information associated with a specific field in a table. For
example, "name" is a column in the above table which contains all information about a student's name.

Properties of an Attribute:

o Every attribute of a relation must have a name.

o Null values are permitted for the attributes.
o Default values can be specified for an attribute automatically inserted if no other value is specified for an attribute.
o Attributes that uniquely identify each tuple of a relation are the primary key.

Name

Ajeet

Aryan

Mahesh

Ratan

Vimal
What is data item/Cells?
The smallest unit of data in the table is the individual data item. It is stored at the intersection of tuples and attributes.

Properties of data items:

o Data items are atomic.

o The data items for an attribute should be drawn from the same domain.

In the below example, the data item in the student table consists of Ajeet, 24 and Btech, etc.

ID Name AGE COURSE

1 Ajeet 24 [Link]

Degree:

The total number of attributes that comprise a relation is known as the degree of the table.

For example, the student table has 4 attributes, and its degree is 4.

ID Name AGE COURSE

1 Ajeet 24 [Link]

2 aryan 20 C.A

3 Mahesh 21 BCA

4 Ratan 22 MCA

5 Vimal 26 BSC

Cardinality:

The total number of tuples at any one time in a relation is known as the table's cardinality. The relation whose cardinality is
0 is called an empty table.

For example, the student table has 5 rows, and its cardinality is 5.
ID Name AGE COURSE

1 Ajeet 24 [Link]

2 aryan 20 C.A

3 Mahesh 21 BCA

4 Ratan 22 MCA

5 Vimal 26 BSC

Domain:

The domain refers to the possible values each attribute can contain. It can be specified using standard data types such as
integers, floating numbers, etc. For example, An attribute entitled Marital_Status may be limited to married or unmarried
values.

NULL Values

The NULL value of the table specifies that the field has been left blank during record creation. It is different from the value
filled with zero or a field that contains space.

Difference between DBMS and RDBMS

Although DBMS and RDBMS both are used to store information in physical database but there are some remarkable
differences between them.

The main differences between DBMS and RDBMS are given below:

No. DBMS RDBMS

1) DBMS applications store data as file. RDBMS applications store data in a tabular form.

2) In DBMS, data is generally stored in In RDBMS, the tables have an identifier called primary
either a hierarchical form or a key and the data values are stored in the form of tables.
navigational form.

3) Normalization is not present in Normalization is present in RDBMS.

DBMS.
4) DBMS does not apply any RDBMS defines the integrity constraint for the
security with regards to data purpose of ACID (Atomocity, Consistency, Isolation and
manipulation. Durability) property.

5) DBMS uses file system to store data, in RDBMS, data values are stored in the form of tables,
so there will be no relation between so a relationship between these data values will be
the tables. stored in the form of a table as well.

6) DBMS has to provide some uniform RDBMS system supports a tabular structure of the data
methods to access the stored and a relationship between them to access the stored
information. information.

7) DBMS does not support distributed RDBMS supports distributed database.

database.

8) DBMS is meant to be for small RDBMS is designed to handle large amount of data. it
organization and deal with small supports multiple users.
data. it supports single user.

9) Examples of DBMS are file Example of RDBMS are mysql, postgre, sql
systems, xml etc. server, oracle etc.

After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are
many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS
application is DBMS application and vice-versa.

Data model Schema and Instance

o The data which is stored in the database at a particular moment of time is called an instance of the database.
o The overall design of a database is called schema.
o A database schema is the skeleton structure of the database. It represents the logical view of the entire database.
o A schema contains schema objects like table, foreign key, primary key, views, columns, data types, stored
procedure, etc.
o A database schema can be represented by using the visual diagram. That diagram shows the database objects and
relationship with each other.

o A database schema is designed by the database designers to help programmers whose software will interact with
the database. The process of database creation is called data modeling.

A schema diagram can display only some aspects of a schema like the name of record type, data type, and constraints.
Other aspects can't be specified through the schema diagram. For example, the given figure neither show the data type of
each data item nor the relationship among various files.

In the database, actual data changes quite frequently. For example, in the given figure, the database changes whenever we
add a new grade or add a student. The data at a particular moment of time is called the instance of the database.
Database Languages in DBMS
o A DBMS has appropriate languages and interfaces to express database queries and updates.
o Database languages can be used to read, store and update the data in the database.

Types of Database Languages

1. Data Definition Language (DDL)
o DDL stands for Data Definition Language. It is used to define database structure or pattern.
o It is used to create schema, tables, indexes, constraints, etc. in the database.
o Using the DDL statements, you can create the skeleton of the database.
o Data definition language is used to store the information of metadata like the number of tables and schemas,
their names, indexes, columns in each table, constraints, etc.

Here are some tasks that come under DDL:

o Create: It is used to create objects in the database.

o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema that's why they come under Data definition language.

2. Data Manipulation Language (DML)

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user
requests.

Here are some tasks that come under DML:

o Select: It is used to retrieve data from a database.

o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
o Merge: It performs UPSERT operation, i.e., insert or update operations.
o Call: It is used to call a structured query language or a Java subprogram.
o Explain Plan: It has the parameter of explaining data.
o Lock Table: It controls concurrency.

3. Data Control Language (DCL)

o DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
o The DCL execution is transactional. It also has rollback parameters.

(But in Oracle database, the execution of data control language does not have the feature of rolling back.)

Here are some tasks that come under DCL:

o Grant: It is used to give user access privileges to a database.

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

There are the following operations which have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

4. Transaction Control Language (TCL)

TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction.

Here are some tasks that come under TCL:

o Commit: It is used to save the transaction on the database.

o Rollback: It is used to restore the database to original since the last Commit.

ER (Entity Relationship) Diagram in DBMS

o ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the
data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.

For example, Suppose we design a school database. In this database, the student will be an entity with attributes like
address, name, id, age, etc. The address can be another entity with attributes like city, street name, pin code, etc and there
will be a relationship between them.

Component of ER Diagram
1. Entity:

An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles.

Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity.

a. Weak Entity

An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key attribute of its own.
The weak entity is represented by a double rectangle.
2. Attribute

The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.

a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is
represented by an ellipse with the text underlined.
b. Composite Attribute

An attribute that composed of many other attributes is known as a composite attribute. The composite attribute is
represented by an ellipse, and those ellipses are connected with an ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used
to represent multivalued attribute.

For example, a student can have more than one phone number.

d. Derived Attribute

An attribute that can be derived from other attribute is known as a derived attribute. It can be represented by a dashed
ellipse.

For example, A person's age changes over time and can be derived from another attribute like Date of birth.
3. Relationship

A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship

When only one instance of an entity is associated with the relationship, then it is known as one to one relationship.

For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship

When only one instance of the entity on the left, and more than one instance of an entity on the right associates with the
relationship then this is known as a one-to-many relationship.
For example, Scientist can invent many inventions, but the invention is done by the only specific scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an entity on the right associates with the
relationship then it is known as a many-to-one relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance of an entity on the right associates
with the relationship then it is known as a many-to-many relationship.

For example, Employee can assign by many projects and project can have many employees.

Notation of ER diagram
Database can be represented using the notations. In ER diagram, many notations are used to express the cardinality. These
notations are as follows:
Fig: Notations of ER diagram

Mapping Constraints
o A mapping constraint is a data constraint that expresses the number of entities to which another entity can be
related via a relationship set.
o It is most useful in describing the relationship sets that involve more than two entity sets.
o For binary relationship set R on an entity set A and B, there are four possible mapping cardinalities. These are as
follows:
1. One to one (1:1)
2. One to many (1:M)
3. Many to one (M:1)
4. Many to many (M:M)

One-to-one
In one-to-one mapping, an entity in E1 is associated with at most one entity in E2, and an entity in E2 is associated with at
most one entity in E1.
One-to-many
In one-to-many mapping, an entity in E1 is associated with any number of entities in E2, and an entity in E2 is associated
with at most one entity in E1.

Many-to-one
In one-to-many mapping, an entity in E1 is associated with at most one entity in E2, and an entity in E2 is associated with
any number of entities in E1.

Many-to-many
In many-to-many mapping, an entity in E1 is associated with any number of entities in E2, and an entity in E2 is associated
with any number of entities in E1.
Keys
o Keys play an important role in the relational database.
o It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify
relationships between tables.

For example, ID is used as a key in the Student table because it is unique for each student. In the PERSON table,
passport_number, license_number, SSN are keys since they are unique for each person.

Types of keys:
1. Primary key
o It is the first key used to identify one and only one instance of an entity uniquely. An entity can contain multiple
keys, as we saw in the PERSON table. The key which is most suitable from those lists becomes a primary key.
o In the EMPLOYEE table, ID can be the primary key since it is unique for each employee. In the EMPLOYEE table, we
can even select License_Number and Passport_Number as primary keys since they are also unique.
o For each entity, the primary key selection is based on requirements and developers.

2. Candidate key
o A candidate key is an attribute or set of attributes that can uniquely identify a tuple.
o Except for the primary key, the remaining attributes are considered a candidate key. The candidate keys are as
strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the attributes, like SSN,
Passport_Number, License_Number, etc., are considered a candidate key.
3. Super Key

Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two employees can be the
same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key.

The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

4. Foreign key
o Foreign keys are the column of the table used to point to the primary key of another table.
o Every employee works in a specific department in a company, and employee and department are two different
entities. So we can't store the department's information in the employee table. That's why we link these two tables
through the primary key of one table.
o We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute in the EMPLOYEE table.
o In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.
5. Alternate key

There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These
attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these
candidate keys, and the remaining candidate key, if it exists, is termed the alternate key. In other words, the total number
of the alternate keys is the total number of candidate keys minus the primary key. The alternate key may or may not exist.
If there is only one candidate key in a relation, it does not have an alternate key.

For example, employee relation has two attributes, Employee_Id and PAN_No, that act as candidate keys. In this relation,
Employee_Id is chosen as the primary key, so the other candidate key, PAN_No, acts as the Alternate key.

6. Composite key

Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is also known as
Concatenated Key.

For example, in employee relations, we assume that an employee may be assigned multiple roles, and an employee may
work on multiple projects simultaneously. So the primary key will be composed of all three attributes, namely Emp_ID,
Emp_role, and Proj_ID in combination. So these attributes act as a composite key since the primary key comprises more
than one attribute.
7. Artificial key

The key created using arbitrarily assigned data are known as artificial keys. These keys are created when a primary key is
large and complex and has no relationship with many other relations. The data values of the artificial keys are usually
numbered in a serial order.

For example, the primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large in employee relations. So it
would be better to add a new virtual attribute to identify each tuple in the relation uniquely.

Generalization
o Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher
level entity if they have some attributes in common.
o In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further
higher level entity.
o Generalization is more like subclass and superclass system, but the only difference is the approach. Generalization
uses the bottom-up approach.
o In generalization, entities are combined to form a more generalized entity, i.e., subclasses are combined to make a
superclass.

For example, Faculty and Student entities can be generalized and create a higher level entity Person.
Specialization
o Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one higher level
entity can be broken down into two lower level entities.
o Specialization is used to identify the subset of an entity set that shares some distinguishing characteristics.
o Normally, the superclass is defined first, the subclass and its related attributes are defined next, and relationship
set are then added.

For example: In an Employee management system, EMPLOYEE entity can be specialized as TESTER or DEVELOPER based
on what role they play in the company.

Aggregation
In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its
corresponding entities is aggregated into a higher level entity.

For example: Center entity offers the Course entity act as a single entity in the relationship which is in a relationship with
another entity visitor. In the real world, if a visitor visits a coaching center then he will never enquiry about the Course only
or just about the Center instead he will ask the enquiry about both.
Reduction of ER diagram to Table
The database can be represented using the notations, and these notations can be reduced to a collection of tables.

In the database, every entity set or relationship set can be represented in tabular form.

The ER diagram is given below:

There are some points for converting the ER diagram to the table:

o Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

o All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table.
Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

o A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key
attribute of the entity.

o The multivalued attribute is represented by a separate table.

In the student table, a hobby is a multivalued attribute. So it is not possible to represent multiple values in a
single column of STUDENT table. Hence we create a table STUD_HOBBY with column name STUDENT_ID
and HOBBY. Using both the column, we create a composite key.

o Composite attribute represented by components.

In the given ER diagram, student address is a composite attribute. It contains CITY, PIN, DOOR#, STREET,
and STATE. In the STUDENT table, these attributes can merge as an individual column.

o Derived attributes are not considered in the table.

In the STUDENT table, Age is the derived attribute. It can be calculated at any point of time by calculating the
difference between current date and Date of Birth.

Using these rules, you can convert the ER diagram to tables and columns and assign the mapping between the
tables. Table structure for the given ER diagram is as below:

Figure: Table structure

Relationship of higher degree

The degree of relationship can be defined as the number of occurrences in one entity that is associated with the number
of occurrences in another entity.

There is the three degree of relationship:

1. One-to-one (1:1)
2. One-to-many (1:M)
 3. Many-to-many (M:N)

1. One-to-one
o In a one-to-one relationship, one occurrence of an entity relates to only one occurrence in another entity.
o A one-to-one relationship rarely exists in practice.
o For example: if an employee is allocated a company car then that car can only be driven by that employee.
o Therefore, employee and company car have a one-to-one relationship.

2. One-to-many
o In a one-to-many relationship, one occurrence in an entity relates to many occurrences in another entity.
o For example: An employee works in one department, but a department has many employees.
o Therefore, department and employee have a one-to-many relationship.

3. Many-to-many
o In a many-to-many relationship, many occurrences in an entity relate to many occurrences in another entity.
o Same as a one-to-one relationship, the many-to-many relationship rarely exists in practice.
o For example: At the same time, an employee can work on several projects, and a project has a team of many
employees.
o Therefore, employee and project have a many-to-many relationship.

Relational Model in DBMS

Relational model can represent as a table with columns and rows. Each row is known as a tuple. Each table of the column
has a name or attribute.

Domain: It contains a set of atomic values that an attribute can take.

Attribute: It contains the name of a column in a particular table. Each attribute Ai must have a domain, dom(Ai)
Relational instance: In the relational database system, the relational instance is represented by a finite set of tuples.
Relation instances do not have duplicate tuples.

Relational schema: A relational schema contains the name of the relation and name of all columns or attributes.

Relational key: In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely.

Example: STUDENT Relation

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35

Laxman 33289 8583287182 Gurugram 20

Mahesh 27857 7086819134 Ghaziabad 27

Ganesh 17282 9028 9i3988 Delhi 40

o In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the attributes.
o The instance of schema STUDENT has 5 tuples.
o t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

Properties of Relations
o Name of the relation is distinct from all other relations.
o Each relation cell contains exactly one atomic (single) value
o Each attribute contains a distinct name
o Attribute domain has no significance
o tuple has no duplicate value
o Order of tuple can have a different sequence

Relational Algebra
Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses
operators to perform queries.

Types of Relational operation

 1. Select Operation:
o The select operation selects tuples that satisfy a given predicate.
o It is denoted by sigma (σ).

1. Notation: σ p(r)

Where:

σ is used for selection prediction

r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as
relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN Relation

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

 Input:

1. σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:
o This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are
eliminated from the table.
o It is denoted by ∏.

1. Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:

1. ∏ NAME, CITY (CUSTOMER)

 Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:
o Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both
in R & S.
o It eliminates the duplicate tuples. It is denoted by ∪.

1. Notation: R ∪ S

A union operation must hold the following condition:

o R and S must have the attribute of the same number.

o Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176
 Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner
 Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S.
o It is denoted by intersection ∩.

1. Notation: R ∩ S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Smith

Jones

5. Set Difference:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S.
o It is denoted by intersection minus (-).

1. Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

 Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product
o The Cartesian product is used to combine each row in one table with each row in the other table. It is also known
as a cross product.
o It is denoted by X.

1. Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B

DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal
 Input:

1. EMPLOYEE X DEPARTMENT

Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

1. ρ(STUDENT1, STUDENT)

Join Operations:
A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied. It is
denoted by ⋈.

Example:

EMPLOYEE
 EMP_CODE EMP_NAME

101 Stephan

102 Jack

103 Harry

SALARY

EMP_CODE SALARY

101 50000

102 30000

103 25000

1. Operation: (EMPLOYEE ⋈ SALARY)

Result:

EMP_CODE EMP_NAME SALARY

101 Stephan 50000

102 Jack 30000

103 Harry 25000

Types of Join operations:

 1. Natural Join:
o A natural join is the set of tuples of all combinations in R and S that are equal on their common attribute names.
o It is denoted by ⋈.

Example: Let's use the above EMPLOYEE table and SALARY table:

Input:

1. ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

EMP_NAME SALARY

Stephan 50000

Jack 30000

Harry 25000

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing information.

Example:
 EMPLOYEE

EMP_NAME STREET CITY

Ram Civil line Mumbai

Shyam Park street Kolkata

Ravi M.G. Street Delhi

Hari Nehru nagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:

1. (EMPLOYEE ⋈ FACT_WORKERS)

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru nagar Hyderabad TCS 50000

An outer join is basically of three types:

 a. Left outer join
b. Right outer join
c. Full outer join

a. Left outer join:

o Left outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute
names.
o In the left outer join, tuples in R have no matching tuples in S.
o It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

1. EMPLOYEE ⟕ FACT_WORKERS

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

b. Right outer join:

o Right outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute
names.
o In right outer join, tuples in S have no matching tuples in R.
o It is denoted by ⟖.

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:

1. EMPLOYEE ⟖ FACT_WORKERS

Output:

EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai

 Shyam Wipro 20000 Park street Kolkata

Hari TCS 50000 Nehru street Hyderabad

Kuber HCL 30000 NULL NULL

c. Full outer join:

o Full outer join is like a left or right join except that it contains all rows from both tables.
o In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no matching tuples in R in
their common attribute name.
o It is denoted by ⟗.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

1. EMPLOYEE ⟗ FACT_WORKERS

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

Kuber NULL NULL HCL 30000

3. Equi join:

It is also known as an inner join. It is the most common join. It is based on matched data as per the equality condition. The
equi join uses the comparison operator(=).

Example:

CUSTOMER RELATION

CLASS_ID NAME
 1 John

2 Harry

3 Jackson

PRODUCT

PRODUCT_ID CITY

1 Delhi

2 Mumbai

3 Noida

Input:

1. CUSTOMER ⋈ PRODUCT

Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi

2 Harry 2 Mumbai

3 Harry 3 Noida

Integrity Constraints
o Integrity constraints are a set of rules. It is used to maintain the quality of information.
o Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a
way that data integrity is not affected.
o Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints
o Domain constraints can be defined as the definition of a valid set of values for an attribute.
o The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute
must be available in the corresponding domain.

Example:

2. Entity integrity constraints

o The entity integrity constraint states that primary key value can't be null.
o This is because the primary key value is used to identify individual rows in relation and if the primary key has a null
value, then we can't identify those rows.
o A table can contain a null value other than the primary key field.

Example:
3. Referential Integrity Constraints
o A referential integrity constraint is specified between two tables.
o In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every
value of the Foreign Key in Table 1 must be null or be available in Table 2.

Example:

4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set uniquely.
o An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a
unique and null value in the relational table.
 Example:

Functional Dependency
The functional dependency is a relationship that exists between two attributes. It typically exists between the primary key
and non-key attribute within a table.

1. X → Y

The left side of FD is known as a determinant, the right side of the production is known as a dependent.

For example:

Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id,
we can tell that employee name associated with it.

Functional dependency can be written as:

1. Emp_Id → Emp_Name

We can say that Emp_Name is functionally dependent on Emp_Id.

Types of Functional dependency

 1. Trivial functional dependency
o A → B has trivial functional dependency if B is a subset of A.
o The following dependencies are also trivial like: A → A, B → B

Example:

1. Consider a table with two columns Employee_Id and Employee_Name.

2. {Employee_id, Employee_Name} → Employee_Id is a trivial functional dependency as
3. Employee_Id is a subset of {Employee_Id, Employee_Name}.
4. Also, Employee_Id → Employee_Id and Employee_Name → Employee_Name are trivial dependencies too.

2. Non-trivial functional dependency

o A → B has a non-trivial functional dependency if B is not a subset of A.
o When A intersection B is NULL, then A → B is called as complete non-trivial.

Example:

1. ID → Name,
2. Name → DOB

Inference Rule (IR):

o The Armstrong's axioms are the basic inference rule.
o Armstrong's axioms are used to conclude functional dependencies on a relational database.
o The inference rule is a type of assertion. It can apply to a set of FD(functional dependency) to derive other FD.
o Using the inference rule, we can derive additional functional dependency from the initial set.

The Functional dependency has 6 types of inference rule:

1. Reflexive Rule (IR1)

In the reflexive rule, if Y is a subset of X, then X determines Y.

1. If X ⊇ Y then X → Y

Example:

1. X = {a, b, c, d, e}
2. Y = {a, b, c}

2. Augmentation Rule (IR2)

The augmentation is also called as a partial dependency. In augmentation, if X determines Y, then XZ determines YZ for
any Z.

1. If X → Y then XZ → YZ

Example:
1. For R(ABCD), if A → B then AC → BC

3. Transitive Rule (IR3)

In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z.

1. If X → Y and Y → Z then X → Z

4. Union Rule (IR4)

Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z.

1. If X → Y and X → Z then X → YZ

Proof:

1. X → Y (given)
2. X → Z (given)
3. X → XY (using IR2 on 1 by augmentation with X. Where XX = X)
4. XY → YZ (using IR2 on 2 by augmentation with Y)
5. X → YZ (using IR3 on 3 and 4)

5. Decomposition Rule (IR5)

Decomposition rule is also known as project rule. It is the reverse of union rule.

This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately.

1. If X → YZ then X → Y and X → Z

Proof:

1. X → YZ (given)
2. YZ → Y (using IR1 Rule)
3. X → Y (using IR3 on 1 and 2)

6. Pseudo transitive Rule (IR6)

In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ determines W.

1. If X → Y and YZ → W then XZ → W

Proof:

1. X → Y (given)
2. WY → Z (given)
3. WX → WY (using IR2 on 1 by augmenting with W)
4. WX → Z (using IR3 on 3 and 2)
Normalization
A large database defined as a single relation may result in data duplication. This repetition of data may result in:

o Making relations very large.

o It isn't easy to maintain and update data as it would involve searching many records in relation.
o Wastage and poor utilization of disk space and resources.
o The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data into smaller, simpler,
and well-structured relations that are satisfy desirable properties. Normalization is a process of decomposing the relations
into relations with fewer attributes.

What is Normalization?
o Normalization is the process of organizing the data in the database.
o Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate
undesirable characteristics like Insertion, Update, and Deletion Anomalies.
o Normalization divides the larger table into smaller and links them using relationships.
o The normal form is used to reduce redundancy from the database table.

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate anomalies leads to data
redundancy and can cause data integrity and other problems as the database grows. Normalization consists of a series of
guidelines that helps to guide you in creating a good database [Link] between JDK, JRE, and JVM

Data modification anomalies can be categorized into three types:

o Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a relationship due to
lack of data.
o Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data results in the
unintended loss of some other important data.
o Updatation Anomaly: The update anomaly is when an update of a single data value requires multiple rows of
data to be updated.

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply to individual relations. The
relation is said to be in particular normal form if it satisfies constraints.

Following are the various types of Normal forms:

Normal Description
Form

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional
dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.

4NF A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued
dependency.

5NF A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining
should be lossless.

Advantages of Normalization
o Normalization helps to minimize data redundancy.
o Greater overall database organization.
o Data consistency within the database.
o Much more flexible database design.
 o Enforces the concept of relational integrity.

Disadvantages of Normalization
o You cannot start building the database before knowing what the user needs.
o The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
o It is very time-consuming and difficult to normalize relations of a higher degree.
o Careless decomposition may lead to a bad database design, leading to serious problems.

First Normal Form (1NF)

o A relation will be 1NF if it contains an atomic value.
o It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.
o First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

Example: Relation EMPLOYEE is not in 1NF because of multi-valued attribute EMP_PHONE.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238

20 Harry 8574783832 Bihar

12 Sam 7390372389, Punjab

8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP

14 John 9064738238 UP

20 Harry 8574783832 Bihar

12 Sam 7390372389 Punjab

12 Sam 8589830302 Punjab

Second Normal Form (2NF)
o In the 2NF, relational must be in 1NF.
o In the second normal form, all non-key attributes are fully functional dependent on the primary key

Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can
teach more than one subject.

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30

25 Biology 30

47 English 35

83 Math 38

83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a
candidate key. That's why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE

25 30

47 35

83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT
25 Chemistry

25 Biology

47 English

83 Math

83 Computer

Third Normal Form (3NF)

o A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
o 3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
o If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.

A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function
dependency X → Y.

1. X is a super key.
2. Y is a prime attribute, i.e., each element of Y is part of some candidate key.

Example:

EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida

333 Stephan 02228 US Boston

444 Lan 60007 US Chicago

555 Katharine 06389 UK Norwich

666 John 462007 MP Bhopal

Super key in the table above:

1. {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so on

 Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID. The non-prime
attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID). It violates the rule of third normal
form.

That's why we need to move the EMP_CITY and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a
Primary key.

EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010

333 Stephan 02228

444 Lan 60007

555 Katharine 06389

666 John 462007

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida

02228 US Boston

60007 US Chicago

06389 UK Norwich

462007 MP Bhopal

Boyce Codd normal form (BCNF)

o BCNF is the advance version of 3NF. It is stricter than 3NF.
 o A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one department.

EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO

264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

 Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:

EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:

For the first table: EMP_ID

For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional dependencies is a key.

Boyce Codd normal form (BCNF)

o BCNF is the advance version of 3NF. It is stricter than 3NF.
o A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

Example: Let's assume there is a company where employees work in more than one department.

EMPLOYEE table:

EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO

 264 India Designing D394 283

264 India Testing D394 300

364 UK Stores D283 232

364 UK Developing D283 549

In the above table Functional dependencies are as follows:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.

To convert the given table into BCNF, we decompose it into three tables:

EMP_COUNTRY table:

EMP_ID EMP_COUNTRY

264 India

264 India

EMP_DEPT table:

EMP_DEPT DEPT_TYPE EMP_DEPT_NO

Designing D394 283

Testing D394 300

Stores D283 232

Developing D283 549

EMP_DEPT_MAPPING table:
 EMP_ID EMP_DEPT

D394 283

D394 300

D283 232

D283 549

Functional dependencies:

1. EMP_ID → EMP_COUNTRY
2. EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}

Candidate keys:

For the first table: EMP_ID

For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}

Now, this is in BCNF because left side part of both the functional dependencies is a key.

Fifth normal form (5NF)

o A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.
o 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy.
o 5NF is also known as Project-join normal form (PJ/NF).

Example
SUBJECT LECTURER SEMESTER

Computer Anshika Semester 1

Computer John Semester 1

Math John Semester 1

Math Akash Semester 2

Chemistry Praveen Semester 1

In the above table, John takes both Computer and Math class for Semester 1 but he doesn't take Math class for Semester
2. In this case, combination of all these fields required to identify a valid data.

Suppose we add a new Semester as Semester 3 but do not know about the subject and who will be taking that subject so
we leave Lecturer and Subject as NULL. But all three columns together acts as a primary key, so we can't leave other two
columns blank.

So to make the above table into 5NF, we can decompose it into three relations P1, P2 & P3:

P1

SEMESTER SUBJECT

Semester 1 Computer

Semester 1 Math

Semester 1 Chemistry

Semester 2 Math

P2

SUBJECT LECTURER

Computer Anshika

Computer John

Math John

Math Akash

Chemistry Praveen

P3

SEMSTER LECTURER

Semester 1 Anshika
Semester 1 John

Semester 1 John

Semester 2 Akash

Semester 1 Praveen
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. This query
language became the standard of ANSI in the year of 1986 and ISO in the year of 1987.

If you want to get a job in the field of data science, then it is the most important query language to learn. Big enterprises
like Facebook, Instagram, and LinkedIn, use SQL for storing the data in the back-end.

Why SQL?
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.

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 Syntax
When you want to do some operations on the data in the database, then you must have to write the query in the
predefined syntax of SQL.

The syntax of the structured query language is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax
is defined and maintained by the ISO and ANSI standards.

Following are some most important points about the SQL syntax which are to remember:

o You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase
improves the readability of the SQL query.
o SQL statements or syntax are dependent on text lines. We can place a single SQL statement on one or multiple
text lines.
o You can perform most of the action in a database with SQL statements.
o SQL syntax depends on relational algebra and tuple relational calculus.

SQL Statement
 SQL statements tell the database what operation you want to perform on the structured data and what information you
would like to access from the database.

The statements of SQL are very simple and easy to use and understand. They are like plain English but with a particular
syntax.

Simple Example of SQL statement:

1. SELECT "column_name" FROM "table_name";

Each SQL statement begins with any of the SQL keywords and ends with the semicolon (;). The semicolon is used in the
SQL for separating the multiple Sql statements which are going to execute in the same call. In this SQL tutorial, we will use
the semicolon (;) at the end of each SQL query or statement.

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.

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:

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, 45000, 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

Data Types in MySQL, SQL Server and Oracle Databases

MySQL Data Types

A list of data types used in MySQL database. This is based on MySQL 8.0.

MySQL String 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(Size) It is equal to VARCHAR() but stores binary byte strings. Its size parameter
specifies the maximum column length in bytes.

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

TINYTEXT It holds a string with a maximum length of 255 characters.

MEDIUMTEXT It holds a string with a maximum length of 16,777,215.

LONGTEXT It holds a string with a maximum length of 4,294,967,295 characters.

ENUM(val1, val2, It is used when a string object having only one value, chosen from a list of
val3,...) possible values. It contains 65535 values in an ENUM list. If you insert a value
that is not in the list, a blank value will be inserted.

SET( It is used to specify a string that can have 0 or more values, chosen from a list of
val1,val2,val3,....) possible values. You can list up to 64 values at one time in a SET list.

BLOB(size) It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.

MySQL Numeric Data Types

BIT(Size) It is used for a bit-value type. The number of bits per value is specified in size. Its size
can be 1 to 64. The default value is 1.

INT(size) It is used for the integer value. Its signed range varies from -2147483648 to
2147483647 and unsigned range varies from 0 to 4294967295. The size parameter
specifies the max display width that is 255.

INTEGER(size) It is equal to INT(size).

FLOAT(size, d) It is used to specify a floating point number. Its size parameter specifies the total
number of digits. The number of digits after the decimal point is specified
by d parameter.

FLOAT(p) It is used to specify a floating point number. MySQL used p parameter to determine
whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes
FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().
DOUBLE(size, It is a normal size floating point number. Its size parameter specifies the total number
d) of digits. The number of digits after the decimal is specified by d parameter.

DECIMAL(size, It is used to specify a fixed point number. Its size parameter specifies the total number
d) of digits. The number of digits after the decimal parameter is specified
by d parameter. The maximum value for the size is 65, and the default value is 10. The
maximum value for d is 30, and the default value is 0.

DEC(size, d) It is equal to DECIMAL(size, d).

BOOL It is used to specify Boolean values true and false. Zero is considered as false, and
nonzero values are considered as true.

MySQL Date and Time Data Types

DATE It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-
01-01' to '9999-12-31'.

DATETIME(fsp) It is used to specify date and time combination. Its format is YYYY-MM-DD
hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31
23:59:59'.

TIMESTAMP(fsp) It is used to specify the timestamp. Its value is stored as the number of seconds
since the Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD
hh:mm:ss. Its supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09
03:14:07' UTC.

TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss. Its supported range is
from '-838:59:59' to '838:59:59'

YEAR It is used to specify a year in four-digit format. Values allowed in four digit format
from 1901 to 2155, and 0000.

SQL Server Data Types

SQL Server String Data Type

char(n) It is a fixed width character string data type. Its size can be up to 8000 characters.

varchar(n) It is a variable width character string data type. Its size can be up to 8000 characters.
varchar(max) It is a variable width character string data types. Its size can be up to 1,073,741,824
characters.

text It is a variable width character string data type. Its size can be up to 2GB of text data.

nchar It is a fixed width Unicode string data type. Its size can be up to 4000 characters.

nvarchar It is a variable width Unicode string data type. Its size can be up to 4000 characters.

ntext It is a variable width Unicode string data type. Its size can be up to 2GB of text data.

binary(n) It is a fixed width Binary string data type. Its size can be up to 8000 bytes.

varbinary It is a variable width Binary string data type. Its size can be up to 8000 bytes.

image It is also a variable width Binary string data type. Its size can be up to 2GB.

SQL Server Numeric Data Types

bit It is an integer that can be 0, 1 or null.

tinyint It allows whole numbers from 0 to 255.

Smallint It allows whole numbers between -32,768 and 32,767.

Int It allows whole numbers between -2,147,483,648 and 2,147,483,647.

bigint It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

float(n) It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n
parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.

real It is a floating precision number data from -3.40E+38 to 3.40E+38.

money It is used to specify monetary data from -922,337,233,685,477.5808 to

922,337,203,685,477.5807.

SQL Server Date and Time Data Type

datetime It is used to specify date and time combination. It supports range from January 1, 1753, to
December 31, 9999 with an accuracy of 3.33 milliseconds.

datetime2 It is used to specify date and time combination. It supports range from January 1, 0001 to
December 31, 9999 with an accuracy of 100 nanoseconds

date It is used to store date only. It supports range from January 1, 0001 to December 31, 9999

time It stores time only to an accuracy of 100 nanoseconds

timestamp It stores a unique number when a new row gets created or modified. The time stamp value
is based upon an internal clock and does not correspond to real time. Each table may
contain only one-time stamp variable.

SQL Server Other Data Types

Sql_variant It is used for various data types except for text, timestamp, and ntext. It stores up to
8000 bytes of data.

XML It stores XML formatted data. Maximum 2GB.

cursor It stores a reference to a cursor used for database operations.

table It stores result set for later processing.

uniqueidentifier It stores GUID (Globally unique identifier).

Oracle Data Types

Oracle String data types

CHAR(size) It is used to store character data within the predefined length. It can be stored up
to 2000 bytes.

NCHAR(size) It is used to store national character data within the predefined length. It can be
stored up to 2000 bytes.

VARCHAR2(size) It is used to store variable string data within the predefined length. It can be
stored up to 4000 byte.

VARCHAR(SIZE) It is the same as VARCHAR2(size). You can also use VARCHAR(size), but it is
 suggested to use VARCHAR2(size)

NVARCHAR2(size) It is used to store Unicode string data within the predefined length. We have to
must specify the size of NVARCHAR2 data type. It can be stored up to 4000 bytes.

Oracle Numeric Data Types

NUMBER(p, s) It contains precision p and scale s. The precision p can range from 1 to 38, and the
scale s can range from -84 to 127.

FLOAT(p) It is a subtype of the NUMBER data type. The precision p can range from 1 to 126.

BINARY_FLOAT It is used for binary precision( 32-bit). It requires 5 bytes, including length byte.

BINARY_DOUBLE It is used for double binary precision (64-bit). It requires 9 bytes, including length
byte.

Oracle Date and Time Data Types

DATE It is used to store a valid date-time format with a fixed length. Its range varies from
January 1, 4712 BC to December 31, 9999 AD.

TIMESTAMP It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss format.

Oracle Large Object Data Types (LOB Types)

BLOB It is used to specify unstructured binary data. Its range goes up to 232-1 bytes or 4 GB.

BFILE It is used to store binary data in an external file. Its range goes up to 2 32-1 bytes or 4 GB.

CLOB It is used for single-byte character data. Its range goes up to 232-1 bytes or 4 GB.

NCLOB It is used to specify single byte or fixed length multibyte national character set (NCHAR)
data. Its range is up to 232-1 bytes or 4 GB.

RAW(size) It is used to specify variable length raw binary data. Its range is up to 2000 bytes per row.
Its maximum size must be specified.

LONG It is used to specify variable length raw binary data. Its range up to 231-1 bytes or 2 GB, per
RAW row.
 SQL Operators
Every database administrator and user uses SQL queries for manipulating and accessing the data of database tables and
views.

The manipulation and retrieving of the data are performed with the help of reserved words and characters, which are used
to perform arithmetic operations, logical operations, comparison operations, compound operations, etc.

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.

Syntax of Unary SQL Operator

55.6M

1K

Java Try Catch

Next

Stay

1. Operator SQL_Operand

Syntax of Unary SQL Operator

1. Operand1 SQL_Operator Operand2

Note: SQL operators are used for filtering the table's data by a specific condition in the SQL statement.

What is the Precedence of SQL Operator?

The precedence of SQL operators is the sequence in which the SQL evaluates the different operators in the same
expression. Structured Query Language evaluates those operators first, which have high precedence.

In the following table, the operators at the top have high precedence, and the operators that appear at the bottom have
low precedence.

SQL Operator Symbols Operators

** Exponentiation operator

+, - Identity operator, Negation operator

*, / Multiplication operator, Division operator

 +, -, || Addition (plus) operator, subtraction (minus) operator, String
Concatenation operator

=, !=, <, >, <=, >=, IS NULL, LIKE, Comparison Operators

BETWEEN, IN

NOT Logical negation operator

&& or AND Conjunction operator

OR Inclusion operator

For Example,

1. UPDATE employee
2. SET salary = 20 - 3 * 5 WHERE Emp_Id = 5;

In the above SQL example, salary is assigned 5, not 85, because the * (Multiplication)

Operator has higher precedence than the - (subtraction) operator, so it first gets multiplied with 3*5 and then subtracts
from 20.

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:

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_details;

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:

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.

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:

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_details;

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_details;

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 Employee_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_Salary > ALL ( SELECT Emp_Salary FROM Employee_det
ails WHERE Emp_City = 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 AND condition2 AND condition3 AND ....... AND cond
itionN;

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 OR 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 OR 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 Emp_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 table_Name WHERE column_nameBETWEEN value1 an
d 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 table_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 NOT 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 column_name comparison_operator ANY ( SELECT colu
mn_name FROM table_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 table_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' ;

SQL Set Operators

The Set Operators in SQL combine a similar type of data from two or more SQL database tables. It mixes the result, which
is extracted from two or more SQL queries, into a single result.

Set operators combine more than one select statement in a single query and return a specific result set.

Following are the various set operators which are performed on the similar data stored in the two SQL database
tables:

1. SQL Union Operator

2. SQL Union ALL Operator
3. SQL Intersect Operator
4. SQL Minus Operator

SQL Union Operator

The SQL Union Operator combines the result of two or more SELECT statements and provides the single output.

The data type and the number of columns must be the same for each SELECT statement used with the UNION operator.
This operator does not show the duplicate records in the output table.
 Syntax of UNION Set operator:

1. SELECT column1, column2 ...., columnN FROM table_Name1 [WHERE conditions]

2. UNION
3. SELECT column1, column2 ...., columnN FROM table_Name2 [WHERE conditions];

Let's understand the below example which explains how to execute Union operator in Structured Query Language:

In this example, we used two tables. Both tables have 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 Delhi

203 Saket 30000 Aligarh

Table: Employee_details1

Emp Id Emp Name Emp Salary Emp City

203 Saket 30000 Aligarh

204 Saurabh 40000 Delhi

205 Ram 30000 Kerala

201 Sanjay 25000 Delhi

Table: Employee_details2

o Suppose, we want to see the employee name and employee id of each employee from both tables in a single
output. For this, we have to write the following query in SQL:

1. SELECT Emp_ID, Emp_Name FROM Employee_details1

2. UNION
3. SELECT Emp_ID, Emp_Name FROM Employee_details2 ;

SQL Union ALL Operator

The SQL Union Operator is the same as the UNION operator, but the only difference is that it also shows the same record.

Syntax of UNION ALL Set operator:

1. SELECT column1, column2 ...., columnN FROM table_Name1 [WHERE conditions]
2. UNION ALL
3. SELECT column1, column2 ...., columnN FROM table_Name2 [WHERE conditions];

Let's understand the below example which explains how to execute Union ALL operator in Structured Query
Language:

In this example, we used two tables. Both tables have 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 Delhi

203 Saket 30000 Aligarh

Table: Employee_details1

Emp Id Emp Name Emp Salary Emp City

203 Saket 30000 Aligarh

204 Saurabh 40000 Delhi

205 Ram 30000 Kerala

201 Sanjay 25000 Delhi

Table: Employee_details2

o If we want to see the employee name of each employee of both tables in a single output. For this, we have to
write the following query in SQL:

1. SELECT Emp_Name FROM Employee_details1

2. UNION ALL
3. SELECT Emp_Name FROM Employee_details2 ;

SQL Intersect Operator

The SQL Intersect Operator shows the common record from two or more SELECT statements. The data type and the
number of columns must be the same for each SELECT statement used with the INTERSECT operator.

Syntax of INTERSECT Set operator:

1. SELECT column1, column2 ...., columnN FROM table_Name1 [WHERE conditions]
2. INTERSECT
3. SELECT column1, column2 ...., columnN FROM table_Name2 [WHERE conditions];

Let's understand the below example which explains how to execute INTERSECT operator in Structured Query
Language:

In this example, we used two tables. Both tables have 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 Delhi

203 Saket 30000 Aligarh

Table: Employee_details1

Emp Id Emp Name Emp Salary Emp City

203 Saket 30000 Aligarh

204 Saurabh 40000 Delhi

205 Ram 30000 Kerala

201 Sanjay 25000 Delhi

Table: Employee_details2

Suppose, we want to see a common record of the employee from both the tables in a single output. For this, we have to
write the following query in SQL:

1. SELECT Emp_Name FROM Employee_details1

2. INTERSECT
3. SELECT Emp_Name FROM Employee_details2 ;

SQL Minus Operator

The SQL Minus Operator combines the result of two or more SELECT statements and shows only the results from the first
data set.

Syntax of MINUS operator:

1. SELECT column1, column2 ...., columnN FROM First_tablename [WHERE conditions]
2. MINUS
3. SELECT column1, column2 ...., columnN FROM Second_tablename [WHERE conditions];

Let's understand the below example which explains how to execute INTERSECT operator in Structured Query
Language:

In this example, we used two tables. Both tables have 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 Delhi

203 Saket 30000 Aligarh

Table: Employee_details1

Emp Id Emp Name Emp Salary Emp City

203 Saket 30000 Aligarh

204 Saurabh 40000 Delhi

205 Ram 30000 Kerala

201 Sanjay 25000 Delhi

Table: Employee_details2

Suppose, we want to see the name of employees from the first result set after the combination of both tables. For this, we
have to write the following query in SQL:

1. SELECT Emp_Name FROM Employee_details1

2. MINUS
3. SELECT Emp_Name FROM Employee_details2 ;

SQL Unary Operators

The Unary Operators in SQL perform the unary operations on the single data of the SQL table, i.e., these operators
operate only on one operand.

These types of operators can be easily operated on the numeric data value of the SQL table.
 Following are the various unary operators which are performed on the numeric data stored in the SQL table:

1. SQL Unary Positive Operator

2. SQL Unary Negative Operator
3. SQL Unary Bitwise NOT Operator

SQL Unary Positive Operator

The SQL Positive (+) operator makes the numeric value of the SQL table positive.

Syntax of Unary Positive Operator

1. SELECT +(column1), +(column2) ...., +(columnN) FROM table_Name [WHERE conditions] ;

Let's understand the below example which explains how to execute a Positive unary operator on the data of SQL
table:

This example consists of anEmployee_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 Suppose, we want to see the salary of each employee as positive from the Employee_details table. For this, we
have to write the following query in SQL:

1. SELECT +Emp_Salary Employee_details ;

SQL Unary Negative Operator

The SQL Negative (-) operator makes the numeric value of the SQL table negative.

Syntax of Unary Negative Operator

1. SELECT -(column_Name1), -(column_Name2) ...., -(column_NameN) FROM table_Name [WHERE conditions] ;

Let's understand the below example which explains how to execute Negative unary operator on the data of SQL
table:
 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 Suppose, we want to see the salary of each employee as negative from the Employee_details table. For this, we
have to write the following query in SQL:

1. SELECT -Emp_Salary Employee_details ;

o Suppose, we want to see the salary of those employees as negative whose city is Kolkatain the Employee_details
table. For this, we have to write the following query in SQL:

1. SELECT -Emp_Salary Employee_details WHERE Emp_City = 'Kolkata';

SQL Bitwise NOT Operator

The SQL Bitwise NOT operator provides the one's complement of the single numeric operand. This operator turns each bit
of numeric value. If the bit of any numerical value is 001100, then this operator turns these bits into 110011.

Syntax of Bitwise NOT Operator

1. SELECT ~(column1), ~(column2) ...., ~(columnN) FROM table_Name [WHERE conditions] ;

Let's understand the below example which explains how to execute the Bitwise NOT operator on the data of SQL
table:

This example consists of aStudent_details table, which has four columns Roll_No, Stu_Name, Stu_Marks, and Stu_City.

Emp Id Stu Name Stu Marks Stu City

101 Sanjay 85 Delhi

102 Ajay 97 Chandigarh

 103 Saket 45 Delhi

104 Abhay 68 Delhi

105 Sumit 60 Kolkata

If we want to perform the Bitwise Not operator on the marks column of Student_details, we have to write the following
query in SQL:

1. SELECT ~Stu_Marks Employee_details ;

SQL Bitwise Operators

The Bitwise Operators in SQL perform the bit operations on the Integer values. To understand the performance of Bitwise
operators, you just knew the basics of Boolean algebra.

Following are the two important logical operators which are performed on the data stored in the SQL database
tables:

1. Bitwise AND (&)

2. Bitwise OR(|)

Bitwise AND (&)

The Bitwise AND operator performs the logical AND operation on the given Integer values. This operator checks each bit
of a value with the corresponding bit of another value.

Syntax of Bitwise AND Operator

1. SELECT column1 & column2 & .... & columnN FROM table_Name [WHERE conditions] ;

Let's understand the below example which explains how to execute Bitwise AND operator on the data of SQL
table:

This example consists of the following table, which has two columns. Each column holds numerical values.

When we use the Bitwise AND operator in SQL, then SQL converts the values of both columns in binary format, and the
AND operation is performed on the converted bits.

After that, SQL converts the resultant bits into user understandable format, i.e., decimal format.

Column1 Column2

1 1

2 5
 3 4

4 2

5 3

o Suppose, we want to perform the Bitwise AND operator between both the columns of the above table. For this, we
have to write the following query in SQL:

1. SELECT Column1 & Column2 From TABLE_AND ;

Bitwise OR (|)

The Bitwise OR operator performs the logical OR operation on the given Integer values. This operator checks each bit of a
value with the corresponding bit of another value.

Syntax of Bitwise OR Operator

1. SELECT column1 | column2 | .... | columnN FROM table_Name [WHERE conditions] ;

Let's understand the below example which explains how to execute Bitwise OR operator on the data of SQL table:

This example consists of a table that has two columns. Each column holds numerical values.

When we used the Bitwise OR operator in SQL, then SQL converts the values of both columns in binary format, and the OR
operation is performed on the binary bits. After that, SQL converts the resultant binary bits into user understandable
format, i.e., decimal format.

Column1 Column2

1 1

2 5

3 4

4 2

5 3

o Suppose, we want to perform the Bitwise OR operator between both the columns of the above table. For this, we
have to write the following query in SQL:

1. SELECT Column1 | Column2 From TABLE_OR ;

 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.

Syntax of Create Database statement in SQL

1. CREATE DATABASE Database_Name;

In this syntax, Database_Name specifies the name of the database which we want to create in the system. We have to
type the database name in query just after the 'Create Database' keyword.

Following are the most important points which are required to learn while creating a database:

o The database we want to create should be a simple and unique name, which can be easily identified.
o Database name should be no more than 128 characters.

Syntax of Create Database statement in MySQL

The same command is used in MySQL to create the new database for storing the structured data.

1. CREATE DATABASE Database_Name;

Syntax of Create Database in Oracle

There is no need to create the database in Oracle systems. In the Oracle database, we can directly create the database
tables.

Examples of Create Database statement in SQL

In this article, we took the following two examples which will help how to run and perform the Create Database query in
SQL:

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:

1. SHOW DATABASE ;

SQL does not allow developers to create the database with the existing database name. Suppose if you want to create
another Student database in the same database system, then the Create Database statement will show the following error
in the output:
 Can't create database 'Student'; database exists

So, firstly you have to delete the existing database by using the Drop Statement. You can also replace the existing
database with the help of Replace keyword.

If you want to replace the existing Student database, then you have to type the following SQL query:

1. CREATE OR REPLACE DATABASE Student ;

Example 2:

Suppose, we want to create the Employee database in the system.

Firstly, we have to type the following command in Structured Query Language:

1. CREATE DATABASE Employee ;

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

Database created successfully

You can also check that your database is created in SQL by typing the following query:

1. SHOW DATABASE ;

We know that SQL does not allow developers to create the database with the existing database name.

Suppose, we want to create another Employee database in the same database system, firstly, we have to delete the
existing database using a drop statement, or we have to replace the existing Employee database with the help of the
'replace' keyword.

To replace the existing Employee database with a new Employee database, we have to type the following query in SQL:

1. CREATE OR REPLACE DATABASE Employee;

SQL DROP Database

The SQL Drop Database statement deletes the existing database permanently from the database system. This statement
deletes all the views and tables if stored in the database, so be careful while using this query in SQL.

Following are the most important points which are required to learn before removing the database from the database
system:

o This statement deletes all the data from the database. If you want to restore the deleted data in the future, you
should keep the backup of data of that database which you want to delete.
o Another most important point is that you cannot delete that database from the system which is currently in use by
another database user. If you do so, then the drop statement shows the following error on screen:

1. Cannot drop database "name_of_the_database" because it is currently in use.

 Syntax of Drop Database Statement in SQL
1. 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. We have to write the name of the database after the DROP DATABASE keyword in every example.

We can also delete multiple databases easily by using the single DROP syntax:

1. DROP DATABASE Database_Name1, [ Database_Name2, ......., Database_NameN ] ;

Using this statement, we have no need to write multiple statements for deleting multiple databases. We can specify all the
databases by using a comma in a single statement, as shown in the above syntax.

Examples of Drop Database Statement in SQL

In this article, we took the following two examples that will help how to run and perform the Drop Database query in SQL:

Example1:

Suppose, we want to delete the Student database with all its data from the database system so, firstly we have to check
that the Student database exists in the system or not by using the following statement:

1. SHOW DATABASES ;

If the Student database is shown in the output, then we have to type the following query in SQL for removing the Student
database:

1. DROP DATABASE Student;

If the Student database does not exist in the database system and we run the above query in SQL, then the query will
show the following output:

Can't drop database 'Student'; database doesn't exist

Example2:

Suppose, we want to delete the College database with all its tables and views from the database system, firstly we have to
check that if the College database exists in the system or not by using the following statement:

1. SHOW DATABASES;

If the College database is shown in the output, then you have to type the following query in SQL for removing the College
database permanently:

1. DROP DATABASE College;

If the College database does not exist in the database system, and we run the above query in SQL, then this query will
show the following output:

Can't drop database 'College'; database doesn't exist

 SQL DROP Database
The SQL Drop Database statement deletes the existing database permanently from the database system. This statement
deletes all the views and tables if stored in the database, so be careful while using this query in SQL.

Following are the most important points which are required to learn before removing the database from the database
system:

o This statement deletes all the data from the database. If you want to restore the deleted data in the future, you
should keep the backup of data of that database which you want to delete.
o Another most important point is that you cannot delete that database from the system which is currently in use by
another database user. If you do so, then the drop statement shows the following error on screen:

1. Cannot drop database "name_of_the_database" because it is currently in use.

Syntax of Drop Database Statement in SQL

1. 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. We have to write the name of the database after the DROP DATABASE keyword in every example.

We can also delete multiple databases easily by using the single DROP syntax:

1. DROP DATABASE Database_Name1, [ Database_Name2, ......., Database_NameN ] ;

Using this statement, we have no need to write multiple statements for deleting multiple databases. We can specify all the
databases by using a comma in a single statement, as shown in the above syntax.

Examples of Drop Database Statement in SQL

In this article, we took the following two examples that will help how to run and perform the Drop Database query in SQL:

Example1:

Suppose, we want to delete the Student database with all its data from the database system so, firstly we have to check
that the Student database exists in the system or not by using the following statement:

1. SHOW DATABASES ;

If the Student database is shown in the output, then we have to type the following query in SQL for removing the Student
database:

1. DROP DATABASE Student;

If the Student database does not exist in the database system and we run the above query in SQL, then the query will
show the following output:

Can't drop database 'Student'; database doesn't exist

Example2:
 Suppose, we want to delete the College database with all its tables and views from the database system, firstly we have to
check that if the College database exists in the system or not by using the following statement:

1. SHOW DATABASES;

If the College database is shown in the output, then you have to type the following query in SQL for removing the College
database permanently:

1. DROP DATABASE College;

If the College database does not exist in the database system, and we run the above query in SQL, then this query will
show the following output:

Can't drop database 'College'; database doesn't exist

SQL RENAME Database

In some situations, database users and administrators want to change the name of the database for some technical
reasons. So, the Rename Database statement in SQL is used to change the name of the existing database.

Sometimes, the Rename Database statement is used because the developers think that the original name is not more
relevant to the data of the database, or they want to give a temporary name to that database.

Syntax of Rename Database in SQL

1. ALTER DATABASE old_database_name MODIFY NAME = new_database_name;
1. EXEC sp_renamedb'old_database_name' , 'new_database_name'

Syntax of Rename Database in MySQL

1. RENAME DATABASE old_database_name TO new_database_name;

This syntax is used when we want to change the name of the database in MySQL.

Examples of Rename Database in SQL

In this article, we have taken the following two examples which will help you how to run and perform the Rename
Database query in SQL:

Example 1:

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. If not, then it will show an error in the output.

Example 2:

Suppose we want to rename the Department Database. For this, we have to type the following query in SQL:

1. ALTER DATABASE Department MODIFY NAME = Company ;

 This query changes the name of the database from Department to Company. To run this query, we must ensure that the
database Department exists in the current database server. If not, then it will show an error in the output.

SQL Table
Table is a collection of data, organized in terms of rows and columns. In DBMS term, table is known as relation and row as
tuple.

Note: A table has a specified number of columns, but can have any number of rows.

Table is the simple form of data storage. A table is also considered as a convenient representation of relations.

Let's see an example of an employee table:

Employee

EMP_NAME ADDRESS SALARY

Ankit Lucknow 15000

Raman Allahabad 18000

Mike New York 20000

In the above table, "Employee" is the table name, "EMP_NAME", "ADDRESS" and "SALARY" are the column names. The
combination of data of multiple columns forms a row e.g. "Ankit", "Lucknow" and 15000 are the data of one row.

SQL TABLE Variable

The SQL Table variable is used to create, modify, rename, copy and delete tables. Table variable was introduced by
Microsoft.

It was introduced with SQL server 2000 to be an alternative of temporary tables.

It is a variable where we temporary store records and results. This is same like temp table but in the case of temp table we
need to explicitly drop it.

Table variables are used to store a set of records. So declaration syntax generally looks like CREATE TABLE syntax.

1. create table "tablename"

2. ("column1" "data type",
3. "column2" "data type",
4. ...
5. "columnN" "data type");

When a transaction rolled back the data associated with table variable is not rolled back.
A table variable generally uses lesser resources than a temporary variable.

Table variable cannot be used as an input or an output parameter.

Topics of SQL TABLE Statement

SQL TABLE Variable

What TABLE variable can do?

SQL CREATE TABLE

How to create a table using SQL query>

SQL DROP TABLE

How to drop a table?

SQL DELETE TABLE

How to delete all the records of a table?

SQL RENAME TABLE

How to rename a table?

SQL TRUNCATE TABLE

How to truncate a table?

SQL COPY TABLE

How to copy a table?

SQL TEMP TABLE

What is temporary table? What are the advantage of temporary table?

SQL ALTER TABLE

How to add, modify, rename and drop column.

SQL CREATE TABLE

SQL CREATE TABLE statement is used to create table in a database.

If you want to create a table, you should name the table and define its column and each column's data type.

Let's see the simple syntax to create the table.

1. create table "tablename"
2. ("column1" "data type",
3. "column2" "data type",
4. "column3" "data type",
5. ...
6. "columnN" "data type");

The data type of the columns may vary from one database to another. For example, NUMBER is supported in Oracle
database for integer value whereas INT is supported in MySQL.

Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that
these fields cannot be NULL while creating records in the table.

1. SQL> CREATE TABLE STUDENTS (

2. ID INT NOT NULL,
3. NAME VARCHAR (20) NOT NULL,
4. AGE INT NOT NULL,
5. ADDRESS CHAR (25),
6. PRIMARY KEY (ID)
7. );

You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else
you can use DESC command as follows:

SQL> DESC STUDENTS;

FIELD TYPE NULL KEY DEFAULT EXTRA

ID Int(11) NO PRI

NAME Varchar(20) NO

AGE Int(11) NO

ADDRESS Varchar(25) YES NULL

4 rows in set (0.00 sec)

Now you have the STUDENTS table available in your database and you can use to store required information related to
students.

SQL CREATE TABLE Example in MySQL

Let's see the command to create a table in MySQL database.

1. CREATE TABLE Employee

2. (
3. EmployeeID int,
4. FirstName varchar(255),
5. LastName varchar(255),
6. Email varchar(255),
7. AddressLine varchar(255),
8. City varchar(255)
9. );

SQL CREATE TABLE Example in Oracle

Let's see the command to create a table in Oracle database.

1. CREATE TABLE Employee

2. (
3. EmployeeID number(10),
4. FirstName varchar2(255),
5. LastName varchar2(255),
6. Email varchar2(255),
7. AddressLine varchar2(255),
8. City varchar2(255)
9. );

SQL CREATE TABLE Example in Microsoft SQLServer

Let's see the command to create a table in SQLServer database. It is same as MySQL and Oracle.

1. CREATE TABLE Employee

2. (
3. EmployeeID int,
4. FirstName varchar(255),
5. LastName varchar(255),
6. Email varchar(255),
7. AddressLine varchar(255),
8. City varchar(255)
9. );

Create a Table using another table

We can create a copy of an existing table using the create table command. The new table gets the same column signature
as the old table. We can select all columns or some specific columns.

If we create a new table using an old table, the new table will be filled with the existing value from the old table.

The basic syntax for creating a table with the other table is:

1. CREATE TABLE table_name AS

2. SELECT column1, column2,...
3. FROM old_table_name WHERE ..... ;
4. The following SQL creates a copy of the employee table.
5. CREATE TABLE EmployeeCopy AS
6. SELECT EmployeeID, FirstName, Email
7. FROM Employee;

SQL Primary Key with CREATE TABLE Statement

The following query creates a PRIMARY KEY on the "D" column when the "Employee" table is created.

MySQL
1. CREATE TABLE Employee(
2. EmployeeID NOT NULL,
3. FirstName varchar(255) NOT NULL,
4. LastName varchar(255),
5. City varchar(255),
6. PRIMARY KEY (EmployeeID)
7. );

SQL Server / Oracle / MS Access

1. CREATE TABLE Employee(
2. EmployeeID NOT NULL PRIMARY KEY,
3. FirstName varchar(255) NOT NULL,
4. LastName varchar(255),
5. City varchar(255)
6. );

Use the following query to define a PRIMARY KEY constraints on multiple columns, and to allow naming of a PRIMARY KEY
constraints.

For MySQL / SQL Server /Oracle / MS Access

1. CREATE TABLE Employee(
2. EmployeeID NOT NULL,
3. FirstName varchar(255) NOT NULL,
4. LastName varchar(255),
5. City varchar(255),
6. CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID, FirstName)
7. );

SQL DROP TABLE

A SQL DROP TABLE statement is used to delete a table definition and all data from a table.

This is very important to know that once a table is deleted all the information available in the table is lost forever, so we
have to be very careful when using this command.

Let's see the syntax to drop the table from the database.
1. DROP TABLE "table_name";

Let us take an example:

First we verify STUDENTS table and then we would delete it from the database.

1. SQL> DESC STUDENTS;

FIELD TYPE NULL KEY DEFAULT EXTRA

ID Int(11) NO PRI

NAME Varchar(20) NO

AGE Int(11) NO

ADDRESS Varchar(25) YES NULL

1. 4 rows in set (0.00 sec)

This shows that STUDENTS table is available in the database, so we can drop it as follows:

1. SQL>DROP TABLE STUDENTS;

Now, use the following command to check whether table exists or not.

1. SQL> DESC STUDENTS;

1. Query OK, 0 rows affected (0.01 sec)

As you can see, table is dropped so it doesn't display it.

SQL DROP TABLE Example in MySQL

Let's see the command to drop a table from the MySQL database.

1. DROP TABLE table_name;

SQL DROP TABLE Example in Oracle

Let's see the command to drop a table from Oracle database. It is same as MySQL.

1. DROP TABLE table_name;

SQL DROP TABLE Example in Microsoft SQLServer

 Let's see the command to drop a table from SQLServer database. It is same as MySQL.

1. DROP TABLE table_name;

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should
use WHERE condition.

1. DELETE FROM table_name [WHERE condition];

But if you do not specify the WHERE condition it will remove all the rows from the table.

1. DELETE FROM table_name;

There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are
not exactly same there are some differences between them.

Difference between DELETE and TRUNCATE statements

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the
table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not
specified.

But it does not free the space containing by the table.

The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

Let's see an "employee" table.

Emp_id Name Address Salary

1 Aryan Allahabad 22000

2 Shurabhi Varanasi 13000

3 Pappu Delhi 24000

Execute the following query to truncate the table:

1. TRUNCATE TABLE employee;

Difference b/w DROP and TRUNCATE statements

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of
that table with other tables will no longer be valid.
 When you drop a table:

o Table structure will be dropped

o Relationship will be dropped
o Integrity constraints will be dropped
o Access privileges will also be dropped

On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the
above problems.

SQL RENAME TABLE

In some situations, database administrators and users want to change the name of the table in the SQL database because
they want to give a more relevant name to the table.

Any database user can easily change the name by using the RENAME TABLE and ALTER TABLE statement in Structured
Query Language.

The RENAME TABLE and ALTER TABLE syntax help in changing the name of the table.

Syntax of RENAME statement in SQL

1. RENAME old_table _name To new_table_name ;

Examples of RENAME statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to change the name of the SQL
table in the database using RENAME statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

 Table: Cars

o Suppose, you want to change the above table name into "Car_2021_Details". For this, you have to type the
following RENAME statement in SQL:

1. RENAME Cars To Car_2021_Details ;

o After this statement, the table "Cars" will be changed into table name "Car_2021_Details".

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose, you want to change the name of the above table into the "Coding_Employees". For this, you have to
type the following RENAME statement in SQL:

1. RENAME Employee To Coding_Employees ;

o After this statement, the table "Employee" will be changed into the table name "Coding_Employees".

Syntax of ALTER TABLE statement in SQL

1. ALTER TABLE old_table_name RENAME TO new_table_name;

In the Syntax, we have to specify the RENAME TO keyword after the old name of the table.

Examples of ALTER TABLE statement in SQL

Here, we have taken the following three different SQL examples, which will help you how to change the name of the table
in the SQL database using ALTER TABLE statement:

Example 1: Let's take an example of a table named Bikes:

Bike_Name Bike_Color Bike_Cost

 KTM DUKE Black 185,000

Royal Enfield Black NULL

Pulsar Red 90,0000

Apache White NULL

Livo Black 80,000

KTM RC Red 195,000

Table : Bikes

o Suppose, you want to change the name of the above table into "Bikes_Details" using ALTER TABLE statement. For
this, you have to type the following query in SQL:

1. ALTER TABLE Bikes RENAME TO Bikes_Details ;

After this statement, the table "Bikes" will be changed into the table name "Bikes_Details".

Example 2: Let's take an example of a table named Student:

Stu_ID Stu_Name Stu_Marks

1001 Abhay 85

1002 Ankit 75

1003 Bheem 60

1004 Ram 79

1005 Sumit 80

Table : Student

o Suppose, you want to change the name of the above table into "MCA_Student_Details" using ALTER TABLE
statement. For this, you have to type the following query in SQL:

1. ALTER TABLE Student RENAME TO MCA_Student_Details ;

 After this statement, the table "Student" will be changed into table name "MCA_Student_Details".

Example 3: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose, you want to change the name of the above table into the "Coding_Employees" using an ALTER TABLE
statement. For this, you have to type the following query in SQL:

1. ALTER TABLE Employee RENAME To Coding_Employees ;

After this statement, the table "Employee" will be changed into the table name "Coding_Employees".

SQL TRUNCATE TABLE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement
with no WHERE clause.

TRUNCATE TABLE Vs DELETE TABLE

Truncate table is faster and uses lesser resources than DELETE TABLE command.

TRUNCATE TABLE Vs DROP TABLE

Drop table command can also be used to delete complete table but it deletes table structure too. TRUNCATE TABLE
doesn't delete the structure of the table.

Let's see the syntax to truncate the table from the database.
1. TRUNCATE TABLE table_name;

For example, you can write following command to truncate the data of employee table

1. TRUNCATE TABLE Employee;

Note: The rollback process is not possible after truncate table statement. Once you truncate a table you cannot use a
flashback table statement to retrieve the content of the table.

SQL COPY TABLE

If you want to copy the data of one SQL table into another SQL table in the same SQL server, then it is possible by using
the SELECT INTO statement in SQL.

The SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table.
SQL creates the new table by using the structure of the existing table.

Syntax of SELECT INTO statement in SQL

1. SELECT * INTO New_table_name FROM old_table_name;

Examples of SELECT INTO statement in SQL

In this article, we have taken the following three different SQL examples which will help you how to copy the content of
one table into another table in SQL:

Example 1: In this example, we have a table called Cars with three columns:

Play Video

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

 Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose you want to copy the content of the above Car table into the new table Car_Details. For this, you have to
type the following query in SQL:

1. SELECT * INTO Car_Details FROM Cars;

o Let's check the Car_Details table is created successfully or not in the database:

1. SELECT * FROM Car_Details;

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

 203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

o Suppose you want to copy the record of the above Employee table into the new table Coding_Employees. For
this, you have to type the following query in SQL:

1. SELECT * INTO Coding_Employees FROM Employee;

o Let's check the Coding_Employees table is created successfully or not in the database:

1. SELECT * FROM Coding_Employees;

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Coding_Employees

Example 3: In this example, we have a table called Student with four columns:

RollNo Name Marks Age

1001 Bhanu 88 17

1002 Raman 82 16

1003 Sumit 80 16

1004 Shobhit 95 15

1005 Akash 85 16
 Table: Student

o Suppose you want to copy the record of the above Student table into the new table Class_12_Students. For this,
you have to type the following query in SQL:

1. SELECT * INTO Class_12_Students FROM Student;

o Let's check the table is Class_12_Students table created successfully or not in the database:

1. SELECT * FROM Class_12_Students;

RollNo Name Marks Age

1001 Bhanu 88 17

1002 Raman 82 16

1003 Sumit 80 16

1004 Shobhit 95 15

1005 Akash 85 16

Table: Class_12_Students

Example 4: In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars
 o Suppose you want to copy Car_Color and Car_Name columns of the above Cars table into the new
table Car_Color. For this, you have to type the following query in SQL:

1. SELECT Car_Name, Car_Color INTO Car_Color FROM Cars;

o Let's check the Car_Color table is created successfully or not in the database:

1. SELECT * FROM Car_Color;

Car Name Car Color

Hyundai Creta White

Hyundai Venue White

Hyundai i20 Red

Kia Sonet White

Kia Seltos Black

Swift Dezire Red

Table: Car_Color

Syntax of SELECT INTO statement with WHERE clause in SQL

1. SELECT * INTO New_table_name FROM old_table_name WHERE [ condition ] ;

Examples of SELECT INTO statement with WHERE clause in SQL

Here, we have taken the following three different SQL examples, which will help you how to copy the content of one table
into another table with a specific condition in SQL:

Example 1: In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost

Hyundai Creta Black 10,85,000

Hyundai Venue Black 9,50,000

Hyundai i20 Red 9,00,000

 Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose we want to copy only the record of those cars whose color is black. For this, we have to type the
following query in SQL:

1. SELECT * INTO Black_Car_Details FROM Cars WHERE Car_Color = 'Black';

o Let's check the Black_Car_Details table is created successfully or not in the database:

1. SELECT * FROM Black_Car_Details;

Car Name Car Color Car Cost

Hyundai Creta Black 10,85,000

Hyundai Venue Black 9,50,000

Kia Seltos Black 8,00,000

Table: Black_Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 45000 Goa

202 Ankit 45000 Delhi

203 Bheem 38000 Goa

204 Ram 49000 Goa

205 Sumit 40000 Delhi

Table: Employee
 o Suppose we want to copy only the record of those employees whose Salary is more than 40,000. For this, we have
to type the following query in SQL:

1. SELECT * INTO Emp_Salary_40000 FROM Cars WHERE Emp_Salary > 40000;

o Let's check the Emp_Salary_40000 table created successfully or not in the database:

1. SELECT * FROM Emp_Salary_40000;

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 45000 Goa

202 Ankit 45000 Delhi

204 Ram 49000 Goa

Table: Emp_Salary_40000

SQL COPY TABLE

If you want to copy the data of one SQL table into another SQL table in the same SQL server, then it is possible by using
the SELECT INTO statement in SQL.

The SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table.
SQL creates the new table by using the structure of the existing table.

Syntax of SELECT INTO statement in SQL

1. SELECT * INTO New_table_name FROM old_table_name;

Examples of SELECT INTO statement in SQL

In this article, we have taken the following three different SQL examples which will help you how to copy the content of
one table into another table in SQL:

Example 1: In this example, we have a table called Cars with three columns:
 Play Video

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose you want to copy the content of the above Car table into the new table Car_Details. For this, you have to
type the following query in SQL:

1. SELECT * INTO Car_Details FROM Cars;

o Let's check the Car_Details table is created successfully or not in the database:

1. SELECT * FROM Car_Details;

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

 Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

o Suppose you want to copy the record of the above Employee table into the new table Coding_Employees. For
this, you have to type the following query in SQL:

1. SELECT * INTO Coding_Employees FROM Employee;

o Let's check the Coding_Employees table is created successfully or not in the database:

1. SELECT * FROM Coding_Employees;

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

 205 Sumit 40000 Delhi

Table: Coding_Employees

Example 3: In this example, we have a table called Student with four columns:

RollNo Name Marks Age

1001 Bhanu 88 17

1002 Raman 82 16

1003 Sumit 80 16

1004 Shobhit 95 15

1005 Akash 85 16

Table: Student

o Suppose you want to copy the record of the above Student table into the new table Class_12_Students. For this,
you have to type the following query in SQL:

1. SELECT * INTO Class_12_Students FROM Student;

o Let's check the table is Class_12_Students table created successfully or not in the database:

1. SELECT * FROM Class_12_Students;

RollNo Name Marks Age

1001 Bhanu 88 17

1002 Raman 82 16

1003 Sumit 80 16

1004 Shobhit 95 15

1005 Akash 85 16

Table: Class_12_Students
 Example 4: In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose you want to copy Car_Color and Car_Name columns of the above Cars table into the new
table Car_Color. For this, you have to type the following query in SQL:

1. SELECT Car_Name, Car_Color INTO Car_Color FROM Cars;

o Let's check the Car_Color table is created successfully or not in the database:

1. SELECT * FROM Car_Color;

Car Name Car Color

Hyundai Creta White

Hyundai Venue White

Hyundai i20 Red

Kia Sonet White

Kia Seltos Black

Swift Dezire Red

Table: Car_Color
 Syntax of SELECT INTO statement with WHERE clause in SQL
1. SELECT * INTO New_table_name FROM old_table_name WHERE [ condition ] ;

Examples of SELECT INTO statement with WHERE clause in SQL

Here, we have taken the following three different SQL examples, which will help you how to copy the content of one table
into another table with a specific condition in SQL:

Example 1: In this example, we have a table called Cars with three columns:

Car Name Car Color Car Cost

Hyundai Creta Black 10,85,000

Hyundai Venue Black 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose we want to copy only the record of those cars whose color is black. For this, we have to type the
following query in SQL:

1. SELECT * INTO Black_Car_Details FROM Cars WHERE Car_Color = 'Black';

o Let's check the Black_Car_Details table is created successfully or not in the database:

1. SELECT * FROM Black_Car_Details;

Car Name Car Color Car Cost

Hyundai Creta Black 10,85,000

Hyundai Venue Black 9,50,000

Kia Seltos Black 8,00,000

 Table: Black_Car_Details

Example 2: In this example, we have a table called Employee with four columns:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 45000 Goa

202 Ankit 45000 Delhi

203 Bheem 38000 Goa

204 Ram 49000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose we want to copy only the record of those employees whose Salary is more than 40,000. For this, we have
to type the following query in SQL:

1. SELECT * INTO Emp_Salary_40000 FROM Cars WHERE Emp_Salary > 40000;

o Let's check the Emp_Salary_40000 table created successfully or not in the database:

1. SELECT * FROM Emp_Salary_40000;

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 45000 Goa

202 Ankit 45000 Delhi

204 Ram 49000 Goa

Table: Emp_Salary_40000

SQL TEMP TABLE

The concept of temporary table is introduced by SQL server. It helps developers in many ways:

Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These
temporary tables are created inside tempdb database.

There are two types of temp tables based on the behavior and scope.
 1. Local Temp Variable
2. Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from
instances. It is started with hash (#) sign.

1. CREATE TABLE #local temp table (

2. User id int,
3. Username varchar (50),
4. User address varchar (150)
5. )

Global Temp Variable

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always
ready for all users and not deleted until the total connection is withdrawn.

1. CREATE TABLE ##new global temp table (

2. User id int,
3. User name varchar (50),
4. User address varchar (150)
5. )

SQL TEMP TABLE

The concept of temporary table is introduced by SQL server. It helps developers in many ways:

Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These
temporary tables are created inside tempdb database.

There are two types of temp tables based on the behavior and scope.

1. Local Temp Variable

2. Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from
instances. It is started with hash (#) sign.

1. CREATE TABLE #local temp table (

2. User id int,
3. Username varchar (50),
4. User address varchar (150)
5. )
 Global Temp Variable
Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always
ready for all users and not deleted until the total connection is withdrawn.

1. CREATE TABLE ##new global temp table (

2. User id int,
3. User name varchar (50),
4. User address varchar (150)
5. )

SQL ALTER TABLE

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing
table. This statement also allows database users to add and remove various SQL constraints on the existing tables.

Any user can also change the name of the table using this statement.

ALTER TABLE ADD Column statement in SQL

In many situations, you may require to add the columns in the existing table. Instead of creating a whole table or database
again you can easily add single and multiple columns using the ADD keyword.

Syntax of ALTER TABLE ADD Column statement in SQL

1. ALTER TABLE table_name ADD column_name column-definition;

The above syntax only allows you to add a single column to the existing table. If you want to add more than one column
to the table in a single SQL statement, then use the following syntax:

1. ALTER TABLE table_name

2. ADD (column_Name1 column-definition,
3. column_Name2 column-definition,
4. .....
5. column_NameN column-definition);

Examples of ALTER TABLE ADD Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to add the single and multiple
columns in the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

 Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose, you want to add the new column Car_Model in the above table. For this, you have to type the following
query in the SQL:

1. ALTER TABLE Cars ADD Car_Model Varchar(20);

This statement will add the Car_Model column to the Cars table.

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose, you want to add two columns, Emp_ContactNo. and Emp_EmailID, in the above Employee table. For
this, you have to type the following query in the SQL:

1. ALTER TABLE Employee ADD ( Emp_ContactNo. Number(13), Emp_EmailID varchar(50) ;

This statement will add Emp_ContactNo. and Emp_EmailID columns to the Employee table.

ALTER TABLE MODIFY Column statement in SQL

 The MODIFY keyword is used for changing the column definition of the existing table.

Syntax of ALTER TABLE MODIFY Column statement in SQL

1. ALTER TABLE table_name MODIFY column_name column-definition;

This syntax only allows you to modify a single column of the existing table. If you want to modify more than one column
of the table in a single SQL statement, then use the following syntax:

1. ALTER TABLE table_name

2. MODIFY (column_Name1 column-definition,
3. column_Name2 column-definition,
4. .....
5. column_NameN column-definition);

Examples of ALTER TABLE MODIFY Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to modify single and multiple
columns of the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose, you want to modify the datatype of the Car_Color column of the above table. For this, you have to type
the following query in the SQL:

1. ALTER TABLE Cars ADD Car_Color Varchar(50);

Example 2: Let's take an example of a table named Employee:

 Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose, you want to modify the datatypes of two columns Emp_ContactNo. and Emp_EmailID of the above
Employee table. For this, you have to type the following query in the SQL:

1. ALTER TABLE Employee ADD ( Emp_ContactNo. Int, Emp_EmailID varchar(80) ;

ALTER TABLE DROP Column statement in SQL

In many situations, you may require to delete the columns from the existing table. Instead of deleting the whole table or
database you can use DROP keyword for deleting the columns.

Syntax of ALTER TABLE DROP Column statement in SQL

1. ALTER TABLE table_name DROP Column column_name ;

Examples of ALTER TABLE DROP Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to delete a column from the
existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

 Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

o Suppose, you want to delete the Car_Color column from the above table. For this, you have to type the following
query in the SQL:

1. ALTER TABLE Cars DROP COLUMN Car_Color ;

o Let's check using the following statement that the Car_Color column is deleted from the table or not:

1. SELECT * FROM Cars;

Car Name Car Cost

Hyundai Creta 10,85,000

Hyundai Venue 9,50,000

Hyundai i20 9,00,000

Kia Sonet 10,00,000

Kia Seltos 8,00,000

Swift Dezire 7,95,000

Table: Cars

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

 204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

o Suppose, you want to delete the Emp_Salary and Emp_City column from the above Employee table. For this, you
have to type the following two different queries in the SQL:

1. ALTER TABLE Cars DROP COLUMN Emp_Salary ;

2. ALTER TABLE Cars DROP COLUMN Emp_City ;

ALTER TABLE RENAME Column statement in SQL

The RENAME keyword is used for changing the name of columns or fields of the existing table.

Syntax of ALTER TABLE RENAME Column statement in SQL

1. ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Examples of ALTER TABLE RENAME Column statement in SQL

Here, we have taken the following two different SQL examples, which will help you how to change the name of a column
of the existing table using ALTER TABLE statement:

Example 1: Let's take an example of a table named Cars:

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars
 o Suppose, you want to change the name of the Car_Color column of the above Cars table. For this, you have to
type the following query in the SQL:

1. ALTER TABLE Cars RENAME COLUMN Car_Color to Colors;

This statement will change the name of a column of the Cars table. To see the changes, you have to type the following
query:

1. SELECT * FROM Cars;

Car Name Car Color Car Cost

Hyundai Creta White 10,85,000

Hyundai Venue White 9,50,000

Hyundai i20 Red 9,00,000

Kia Sonet White 10,00,000

Kia Seltos Black 8,00,000

Swift Dezire Red 7,95,000

Table: Cars

Example 2: Let's take an example of a table named Employee:

Emp_Id Emp_Name Emp_Salary Emp_City

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee
 o Suppose, you want to change the name of the Emp_City column of the above Employee table. For this, you have
to type the following query in the SQL:

1. ALTER TABLE Employee RENAME COLUMN Emp_City to Emp_Address;

This statement will change the name of a column of the Employee table. To see the changes, you have to type the
following query:

1. SELECT * FROM Employee;

Emp_Id Emp_Name Emp_Salary Emp_Address

201 Abhay 25000 Goa

202 Ankit 45000 Delhi

203 Bheem 30000 Goa

204 Ram 29000 Goa

205 Sumit 40000 Delhi

Table: Employee

SUM Function in SQL

The SUM is an aggregate function in SQL which returns the sum of integer column of the table.

Syntax of SUM Function

In the Structured Query Language, we use the SUM function with the columns of the table as shown in the following
block:

1. SELECT SUM(column_Name) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and column of that table on which we want to perform the SUM function.

Example of SUM function

Here, we will create the new table through which we will perform the SUM function with the columns of the table:

The following shows the syntax to create the new table in SQL:

1. CREATE TABLE Name_of_New_Table

2. (
3. First_Column_of_table Data Type (character_size of First Column),
4. Second_Column_of_table Data Type (character_size of the Second column ),
5. Third_Column_of_table Data Type (character_size of the Third column),
6. .......,
7. Last_Column_of_table Data Type (character_size of the Last column)
8. );

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

1. CREATE TABLE Product_Details

2. (
3. Product_ID INT NOT NULL,
4. Product_Name Varchar(50),
5. Product_Quantity INT,
6. Purchasing_Price INT,
7. Selling_Price INT,
8. Release_Date Date,
9. Product_Rating INT
10. );

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the
Product_Details table:

1. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);
2.
3. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);
4.
5. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);
6.
7. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);
8.
9. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);
10.
11.
12. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);
13.
14. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

1. SELECT * FROM Product_Details;

 Product Product_ Product_ Purchasing Selling_ Release Category Product

_ID Name Quantity _Price Price _Date _Rating

104 P1 10.250 945 NULL 2022-04-30 Cloths NULL

202 P4 15.500 45 75 2022-01-28 Electrical 5

103 P2 18.250 25 NULL 2022-02-18 Toys 4

111 P7 25.250 5 15 2021-12-25 Cloths 9

210 P6 15.500 50 70 2021-10-15 Electrical NULL

212 P8 19.750 110 250 2022-01-28 Cloths 4

112 P10 10.250 550 835 2022-04-11 Toys NULL

Query 1: The following SELECT query uses the SUM function with the Product_Quantity column of the above
Product_Details table:

1. SELECT SUM(Product_Quantity) AS Total_sum_ofproductquantity FROM Product_Details;

Output:

Total_sum_ofproductquantity

114.75

Query 2: The following SELECT query uses the SUM function with the Selling_Price column of the above Product_Details
table:

1. SELECT SUM(Selling_Price) AS Total_ofsellingpriceproducts FROM Product_Details;

This query returns the sum of selling price products.

Output:

Total_ofsellingpriceproducts

1875
 Query 3: The following SELECT query uses the SUM function with the Product_Rating column of the above Product_Details
table:

1. SELECT SUM(Product_Rating) AS total_of_productrating FROM Product_Details;

This query returns the sum of values of Product_rating columns.

Output:

Total_ofofproductrating

22

SUM Function with WHERE clause

We can also use the WHERE clause with the SUM function which adds the values of filtered rows.

The syntax for using the SUM function with the WHERE clause is as follows:

1. SELECT SUM(column_Name) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the SUM function with WHERE clause in the above Product_Details table:

1. SELECT SUM(Product_Quantity) AS Total_ofproduct>200 FROM Product_Details WHERE Product_ID > 200;

This query adds the qunatity of those products from above table whose product id is greater than 200.

Output:

Total_ofproduct>200

50.75

Query 2: The following SELECT query uses the SUM function with WHERE clause in the above Product_Details table:

1. SELECT SUM(Purchasing_Price) AS Total_of_purchasingprice FROM Product_Details WHERE Release_Date = 2022-01-28;

This query adds the purchasing price of those products which are release on 2022-01-28.

Output:

Total_of_purchasingprice

325
 SUM Function with DISTINCT clause
We can also use the DISTINCT clause with the SUM function which adds the distinct values of the column from the table.

The syntax for using the SUM function with the DISTINCT clause is as follows:

1. SELECT SUM(DISTINCT (column_Name)) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the SUM function with the DISTINCT clause in the above Product_Details table:

1. SELECT SUM(DISTINCT(Product_Quantity)) AS Total_ofdistinctproduct FROM Product_Details;

This query adds the quantity of distinct values of Product_quantity column.

Output:

Total_ofdistinctproduct

89

Query 2: The following SELECT query uses the SUM function with the DISTINCT clause of the above Product_Details table:

1. SELECT SUM(DISTINCT(Product_Rating)) AS total_of_distinctproductrating FROM Product_Details;

Output:

Total_ofdistinctproductrating

18

SUM Function with GROUP BY clause

We can also use the GROUP BY clause with the SUM function which adds the values which exist in the same group.

The syntax for using the SUM function with the GROUP BY clause is as follows:

1. SELECT Column_Name1, SUM(column_Name)) AS Alias_Name FROM Table_Name GROUP BY Column_Name1;

Query 1: The following SELECT query uses the SUM function with the GROUP BY clause in the above Product_Details table:

1. SELECT Category, SUM(Product_Quantity) AS Total_ofsame_group FROM Product_Details GROUP BY Category;

This query adds the quantity of same category.

Output:
 Category Total_ofsamegroup

Cloths 55.25

Toys 28.5

Electrical 31

AVG Function in SQL

The AVG is an aggregate function in SQL which returns the average of values of the integer column of the table.

Syntax of AVG Function

In the Structured Query Language, we use the AVG function with the columns of the table as shown in the following block:

1. SELECT AVG(column_Name) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and column of that table on which we want to perform the AVG function.

Example of AVG function

Here, we will create the new table through which we will perform the AVG function with the columns of the table:

The following shows the syntax to create the new table in SQL:

1. CREATE TABLE Name_of_New_Table

2. (
3. First_Column_of_table Data Type (character_size of First Column),
4. Second_Column_of_table Data Type (character_size of the Second column ),
5. Third_Column_of_table Data Type (character_size of the Third column),
6. .......,
7. Last_Column_of_table Data Type (character_size of the Last column)
8. );

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

1. CREATE TABLE Product_Details

2. (
3. Product_ID INT NOT NULL,
4. Product_Name Varchar(50),
5. Product_Quantity INT,
6. Purchasing_Price INT,
7. Selling_Price INT,
8. Release_Date Date,
9. Product_Rating INT
10. );

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the
Product_Details table:

1. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);
2.
3. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);
4.
5. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);
6.
7. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);
8.
9. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);
10.
11.
12. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);
13.
14. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

1. SELECT * FROM Product_Details;

Product_ Product_Na Product_Quan Purchasing_Pr Selling_Pri Release_D Catego Product_Rati

ID me tity ice ce ate ry ng

104 P1 10.250 945 NULL 2022-04-30 Cloths NULL

202 P4 15.500 45 75 2022-01-28 Electrical 5

103 P2 18.250 25 NULL 2022-02-18 Toys 4

111 P7 25.250 5 15 2021-12-25 Cloths 9

210 P6 15.500 50 70 2021-10-15 Electrical NULL

 212 P8 19.750 110 250 2022-01-28 Cloths 4

112 P10 10.250 550 835 2022-04-11 Toys NULL

Query 1: The following SELECT query uses the AVG function with the Product_Quantity column of the above
Product_Details table:

1. SELECT AVG(Product_Quantity) AS Average_ofproductquantity FROM Product_Details;

Output:

Average_ofproductquantity

16.39

Query 2: The following SELECT query uses the AVG function with the Selling_Price column of the above Product_Details
table:

1. SELECT AVG(Selling_Price) AS Average_ofsellingpriceproducts FROM Product_Details;

This query returns the average of selling price products.

Output:

Average_ofsellingpriceproducts

249

Query 3: The following SELECT query uses the AVG function with the Product_Rating column of the above Product_Details
table:

1. SELECT AVG(Product_Rating) AS Average_of_productrating FROM Product_Details;

This query returns the average of values of Product_rating columns.

Output:

Average_ofofproductrating

5.5

AVG Function with WHERE clause

 We can also use the WHERE clause with the AVG function which adds the values of filtered rows.

The syntax for using the AVG function with the WHERE clause is as follows:

1. SELECT AVG(column_Name) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the AVG function with WHERE clause in the above Product_Details table:

1. SELECT AVG(Product_Quantity) AS Average_ofproduct>200 FROM Product_Details WHERE Product_ID > 200;

This query finds the average of the quantity of those products from above table whose product id is greater than 200.

Output:

Average_ofproduct>200

16.91

Query 2: The following SELECT query uses the AVG function with WHERE clause in the above Product_Details table:

1. SELECT AVG(Purchasing_Price) AS Average_of_purchasingprice FROM Product_Details WHERE Release_Date = 2022-01-

28;

This query finds the average the purchasing price of those products which are release on 2022-01-28.

Output:

Average_of_purchasingprice

162.5

AVG Function with DISTINCT clause

We can also use the DISTINCT clause with the AVG function which adds the distinct values of the column from the table.

The syntax for using the AVG function with the DISTINCT clause is as follows:

1. SELECT AVG(DISTINCT (column_Name)) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the AVG function with the DISTINCT clause in the above Product_Details table:

1. SELECT AVG(DISTINCT(Product_Quantity)) AS Average_ofdistinctproduct FROM Product_Details;

This query adds the quantity of distinct values of Product_quantity column and then finds the average.

Output:
 Average_ofdistinctproduct

17.8

Query 2: The following SELECT query uses the AVG function with the DISTINCT clause of the above Product_Details table:

1. SELECT AVG(DISTINCT(Product_Rating)) AS Average_ofdistinctproductrating FROM Product_Details;

Output:

Average_ofdistinctproductrating

COUNT Function in SQL

The COUNT is an aggregate function in SQL which returns the total number of rows from the table.

Syntax of COUNT Function

In the Structured Query Language, we use the COUNT function with the columns of the table as shown in the following
block:

1. SELECT COUNT(column_Name) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and column of that table on which we want to perform the COUNT function.

Examples of COUNT function

Here, we will create the new table through which we will perform the COUNT function with the columns of the table:

The following shows the syntax to create the new table in SQL:

1. CREATE TABLE Name_of_New_Table

2. (
3. First_Column_of_table Data Type (character_size of First Column),
4. Second_Column_of_table Data Type (character_size of the Second column ),
5. Third_Column_of_table Data Type (character_size of the Third column),
6. .......,
7. Last_Column_of_table Data Type (character_size of the Last column)
8. );

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

1. CREATE TABLE Product_Details

2. (
3. Product_ID INT NOT NULL,
4. Product_Name Varchar(50),
5. Product_Quantity INT,
6. Purchasing_Price INT,
7. Selling_Price INT,
8. Release_Date Date,
9. Product_Rating INT
10. );

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the
Product_Details table:

1. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);
2.
3. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);
4.
5. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);
6.
7. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);
8.
9. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);
10.
11.
12. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);
13.
14. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

1. SELECT * FROM Product_Details;

Product_I Product_Nam Product_Quantit Purchasing_Pric Selling_Pric Release_Dat Product_Ratin

D e y e e e g

104 P1 10.250 945 NULL 2022-04-30 NULL

202 P4 15.500 45 75 2022-01-28 5

 103 P2 18.250 25 NULL 2022-02-18 4

111 P7 25.250 5 15 2021-12-25 9

210 P6 15.500 50 70 2021-10-15 NULL

212 P8 19.750 110 250 2022-01-28 4

112 P10 10.250 550 835 2022-04-11 NULL

Query 1: The following SELECT query uses the COUNT function with the Product_Quantity column of the above
Product_Details table:

1. SELECT COUNT(Product_Quantity) AS Total_Number_ofproduct FROM Product_Details;

Output:

Total_Number_ofproduct

Query 2: The following SELECT query uses the COUNT function with the Selling_Price column of the above Product_Details
table:

1. SELECT COUNT(Selling_Price) AS Total_Number_ofsellingpriceproducts FROM Product_Details;

This query shows the five as result because this column contains two NULL values.

Output:

Total_Number_ofsellingpriceproducts

Query 3: The following SELECT query uses the COUNT function with the Product_Rating column of the above
Product_Details table:

1. SELECT COUNT(Product_Rating) AS total_Number_of_productrating FROM Product_Details;

This query shows the four as result because this column contains three NULL values.

Output:
 Total_Number_ofproductrating

Count Function with WHERE clause

We can also use the WHERE clause with the COUNT function which returns the number of rows according to the filtered
rows.

The syntax for using the COUNT function with the WHERE clause is as follows:

1. SELECT COUNT(column_Name) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the COUNT function with WHERE clause in the above Product_Details table:

1. SELECT COUNT(Product_Quantity) AS Total_Number_ofproduct>200 FROM Product_Details WHERE Product_ID > 200;

This query counts those products from above table whose product id is greater than 200.

Output:

Total_Number_ofproduct>200

Query 2: The following SELECT query uses the COUNT function with WHERE clause in the above Product_Details table:

1. SELECT COUNT(Release_Date) AS Total_Number_ofdate FROM Product_Details WHERE Release_Date = 2022-01-28;

This query counts those products which are release on 2022-01-28.

Output:

Total_Number_ofdate

Count Function with DISTINCT clause

We can also use the DISTINCT clause with the COUNT function which counts only distinct values of the column from the
table.

The syntax for using the COUNT function with the DISTINCT clause is as follows:
1. SELECT COUNT(DISTINCT (column_Name)) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the COUNT function with the DISTINCT clause in the above Product_Details
table:

1. SELECT COUNT(DISTINCT(Product_Quantity)) AS Total_Number_ofproduct FROM Product_Details;

This query counts the product according to the distinct values of product_quantity column.

Output:

Total_Number_ofproduct

Query 3: The following SELECT query uses the COUNT function with the DISTINCT clause of the above Product_Details
table:

1. SELECT COUNT(DISTINCT(Product_Rating)) AS total_Number_of_distinctproductrating FROM Product_Details;

This query shows the three as result because this column contains three NULL values and two same values.

Output:

Total_Number_ofdistinctproductrating

MAX Function in SQL

The MAX is an aggregate function in SQL which returns the maximum or largest value from the specified column of the
table.

Syntax of MAX Function

In the Structured Query Language, we use the MAX function with the columns of the table as shown in the following
block:

1. SELECT MAX(column_Name) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and column of that table on which we want to perform the MAX function.

Example of MAX function

Here, we will create the new table through which we will perform the MAX function with the columns of the table:
 The following shows the syntax to create the new table in SQL:

1. CREATE TABLE Name_of_New_Table

2. (
3. First_Column_of_table Data Type (character_size of First Column),
4. Second_Column_of_table Data Type (character_size of the Second column ),
5. Third_Column_of_table Data Type (character_size of the Third column),
6. .......,
7. Last_Column_of_table Data Type (character_size of the Last column)
8. );

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

1. CREATE TABLE Product_Details

2. (
3. Product_ID INT NOT NULL,
4. Product_Name Varchar(50),
5. Product_Quantity INT,
6. Purchasing_Price INT,
7. Selling_Price INT,
8. Release_Date Date,
9. Product_Rating INT
10. );

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the
Product_Details table:

1. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);
2.
3. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);
4.
5. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);
6.
7. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);
8.
9. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);
10.
11.
12. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);
13.
14. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

1. SELECT * FROM Product_Details;

Product_ Product_Na Product_Quan Purchasing_Pr Selling_Pri Release_D Catego Product_Rati

ID me tity ice ce ate ry ng

104 P1 10.250 945 NULL 2022-04-30 Cloths NULL

202 P4 15.500 45 75 2022-01-28 Electrical 5

103 P2 18.250 25 NULL 2022-02-18 Toys 4

111 P7 25.250 5 15 2021-12-25 Cloths 9

210 P6 15.500 50 70 2021-10-15 Electrical NULL

212 P8 19.750 110 250 2022- Cloths 4

01-28

112 P10 10.250 550 835 2022-04-11 Toys NULL

Query 1: The following SELECT query uses the MAX function with the Product_Quantity column of the above
Product_Details table:

1. SELECT MAX(Product_Quantity) AS Maximum_in_productquantity FROM Product_Details;

This query shows the largest value of product quantity column.

Output:

Maximum_in_productquantity

25.250

Query 2: The following SELECT query uses the MAX function with the Selling_Price column of the above Product_Details
table:

1. SELECT MAX(Selling_Price) AS Maximum_in_sellingpriceproducts FROM Product_Details;

 This query returns the maximum value of selling price products.

Output:

Maximum_in_sellingpriceproducts

835

Query 3: The following SELECT query uses the MAX function with the Product_Rating column of the above Product_Details
table:

1. SELECT MAX(Product_Rating) AS Maximum_in_productrating FROM Product_Details;

This query returns the largest values of Product_rating columns.

Output:

Maximum_in_productrating

Query 4: The following SELECT query uses the MAX function with the addition of two columns of the above
Product_Details table:

1. SELECT MAX(Purchasing Price + Selling Price) AS Maximum_of_Purchasing+Selling FROM Product_Details;

This query returns the largest values from the addition of purchasing and selling price.

Output:

Maximum_of_Purchasing+Selling

1385

MAX Function with WHERE clause

We can also use the WHERE clause with the MAX function which returns the largest value from the filtere rows.

The syntax for using the MAX function with the WHERE clause is as follows:

1. SELECT MAX(column_Name) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the MAX function with WHERE clause in the above Product_Details table:
1. SELECT MAX(Product_Quantity) AS Maximum_in_product>200 FROM Product_Details WHERE Product_ID > 200;

This query returns the largest quantity from those products whose product id is greater than 200.

Output:

Maximum_in_product>200

19.750

Query 2: The following SELECT query uses the MAX function with WHERE clause in the above Product_Details table:

1. SELECT MAX(Purchasing_Price) AS Maximum_in_purchasingprice FROM Product_Details WHERE Release_Date = 2022-

01-28;

This query returns the largest purchasing price from those products which are release on 2022-01-28.

Output:

Maximum_in_purchasingprice

110

MAX Function with GROUP BY clause

We can also use the GROUP BY clause with the MAX function which returns the maximum value from the same group.

The syntax for using the MAX function with the GROUP BY clause is as follows:

1. SELECT Column_Name1, MAX(column_Name)) AS Alias_Name FROM Table_Name GROUP BY Column_Name1;

Query 1: The following SELECT query uses the MAX function with the GROUP BY clause in the above Product_Details
table:

1. SELECT Category, MAX(Product_Quantity) AS Maximum_in_samegroup FROM Product_Details GROUP BY Category;

This query returns the maximum value from each specified group.

Output:

Category Maximum_in_samegroup

Cloths 25.250
 Toys 18.250

Electrical 15.500

MIN Function in SQL

The MIN is an aggregate function in SQL which returns the minimum or smallest value from the specified column of the
table.

Syntax of MIN Function

In the Structured Query Language, we use the MIN function with the columns of the table as shown in the following block:

1. SELECT MIN(column_Name) AS Alias_Name FROM Table_Name;

Example of MIN function

Here, we will create the new table through which we will perform the MIN function with the columns of the table:

The following shows the syntax to create the new table in SQL:

1. CREATE TABLE Name_of_New_Table

2. (
3. First_Column_of_table Data Type (character_size of First Column),
4. Second_Column_of_table Data Type (character_size of the Second column ),
5. Third_Column_of_table Data Type (character_size of the Third column),
6. .......,
7. Last_Column_of_table Data Type (character_size of the Last column)
8. );

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

1. CREATE TABLE Product_Details

2. (
3. Product_ID INT NOT NULL,
4. Product_Name Varchar(50),
5. Product_Quantity INT,
6. Purchasing_Price INT,
7. Selling_Price INT,
8. Release_Date Date,
9. Product_Rating INT
10. );

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the
Product_Details table:

1. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (104, P1, 10.250, 945, NULL, 2022-04-30, NULL);
2.
3. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (202, P4, 15.500, 45, 75, 2022-01-28, 5);
4.
5. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (103, P2, 18.250, 25, NULL, 2022-02-18, 4);
6.
7. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (111, P7, 25.250, 5, 15, 2021-12-25, 9);
8.
9. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (210, P6, 15.500, 50, 70, 2021-10-15, NULL);
10.
11.
12. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (212, P8, 19.750, 110, 250, 2022-01-28, 4);
13.
14. INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Dat
e, Product_Rating) VALUES (112, P10, 10.250, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

1. SELECT * FROM Product_Details;

Product_ Product_Na Product_Quan Purchasing_Pr Selling_Pri Release_D Catego Product_Rati

ID me tity ice ce ate ry ng

104 P1 10.250 945 NULL 2022-04-30 Cloths NULL

202 P4 15.500 45 75 2022-01-28 Electrical 5

103 P2 18.250 25 NULL 2022-02-18 Toys 4

111 P7 25.250 5 15 2021-12-25 Cloths 9

210 P6 15.500 50 70 2021-10-15 Electrical NULL

212 P8 19.750 110 250 2022-01-28 Cloths 4

112 P10 10.250 550 835 2022-04-11 Toys NULL

Query 1: The following SELECT query uses the MIN function with the Product_Quantity column of the above
Product_Details table:
1. SELECT MIN(Product_Quantity) AS Minimum_in_productquantity FROM Product_Details;

This query shows the smallest value of product quantity column.

Output:

Minimum_in_productquantity

10.250

Query 2: The following SELECT query uses the MIN function with the Selling_Price column of the above Product_Details
table:

1. SELECT MIN(Selling_Price) AS Minimum_in_sellingpriceproducts FROM Product_Details;

This query returns the minimum value of selling price products.

Output:

Minimum_in_sellingpriceproducts

15

Query 3: The following SELECT query uses the MIN function with the Product_Rating column of the above Product_Details
table:

1. SELECT MIN(Product_Rating) AS Minimum_in_productrating FROM Product_Details;

This query returns the smallest values of Product_rating columns.

Output:

Minimum_in_productrating

Query 4: The following SELECT query uses the MIN function with the addition of two columns of the above
Product_Details table:

1. SELECT MIN(Purchasing Price + Selling Price) AS Minimum_of_Purchasing+Selling FROM Product_Details;

This query returns the smallest values from the addition of purchasing and selling price.

Output:
 Minimum_of_Purchasing+Selling

20

MIN Function with WHERE clause

We can also use the WHERE clause with the MIN function which returns the smallest value from the filtere rows.

The syntax for using the MIN function with the WHERE clause is as follows:

1. SELECT MIN(column_Name) AS Alias_Name FROM Table_Name WHERE Condition;

Query 1: The following SELECT query uses the MIN function with WHERE clause in the above Product_Details table:

1. SELECT MIN(Product_Quantity) AS Minimum_in_product>200 FROM Product_Details WHERE Product_ID > 200;

This query returns the smallest quantity from those products whose product id is greater than 200.

Output:

Minimum_in_product>200

15.500

Query 2: The following SELECT query uses the MIN function with WHERE clause in the above Product_Details table:

1. SELECT MIN(Purchasing_Price) AS Minimum_in_purchasingprice FROM Product_Details WHERE Release_Date = 2022-01-

28;

This query returns the smallest purchasing price from those products which are release on 2022-01-28.

Output:

Minimum_in_purchasingprice

15.500

MIN Function with GROUP BY clause

We can also use the GROUP BY clause with the MIN function which returns the minimum value from the same group.

The syntax for using the MIN function with the GROUP BY clause is as follows:

1. SELECT Column_Name1, MIN(column_Name)) AS Alias_Name FROM Table_Name GROUP BY Column_Name1;

 Query 1: The following SELECT query uses the MIN function with the GROUP BY clause in the above Product_Details table:

1. SELECT Category, MIN(Product_Quantity) AS Minimum_in_samegroup FROM Product_Details GROUP BY Category;

This query returns the minimum value of each specified group.

Output:

Category Minimum_in_samegroup

Cloths 10.250

Toys 10.250

Electrical 15.500