STRUCTURED QUERY LANGUAGE (SQL)

WHAT IS SQL?

▶ SQL stands for Structured Query Language. It is used for storing and managing data in
relational database management system (RDMS).

▶ It is a standard language for Relational Database System. It enables a user to create,

read, update and delete relational databases and tables.

▶ All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their
standard database language.

▶ SQL allows users to query the database in a number of ways, using English-likestatements.
CHARACTERISTICS

SQL is easy to learn.

SQL is used to access data from relational database management systems.

SQL can execute queries against the database.

SQL is used to describe the data.

SQL is used to define the data in the database and manipulate it when needed.

SQL is used to create and drop the database and table.

SQL is used to create a view, stored procedure, function in a database.

SQL allows users to set permissions on tables, procedures, and views.

Advantages of SQL

HIGH SPEED

Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records from
a database.

NO CODING NEEDED

In the standard SQL, it is very easy to manage the database system. It does not require
substantial amount of code to manage the database system.

WELL DEFINED STANDARDS

Long established are used by the SQL databases that are being used by ISO and ANSI.
PORTABILITY

SQL can be used in laptop, PCs, server and even some mobile phones.

INTERACTIVE LANGUAGE

SQL is a domain language used to communicate with the database. It is also used toreceive answers
to the complex questions in seconds.

MULTIPLE DATA VIEWS

Using the SQL language, the users can make different views of the database structure.
DATA TYPES

What are Data types?

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

SQL Supports the following data types:

CHARACTER(n)
CHARACTER VARYING/VARCHAR(n)BIT(n)
BIT VARYING/VARBIT(n)
NUMERIC(p, q)
DECIMAL(p, q)
INTEGER SMALLINT
FLOAT(p) DATE

We have classified those data types into three categories which are:

1. String Data Types: Data types CHARACTER and CHARACTER VARYING are known
collectively as Character String Data Types. Similar data types BIT and BIT VARYING are
known as Bit String Data Types. Character and Bit String Data Types aretogether known
as String Data Types.

a. CHARACTER(n): This data type represents a fixed length string of exactly ‘n’
characters where ‘n’ is greater than zero and should be an integer. In SQL, we write
itas CHAR where ‘CHAR’ is an abbreviation for CHARACTER.

b. CHARACTER VARYING(n): This data type represents a varying length string whose
maximum length is ‘n’ characters. Here also ‘n’ is a positive integer. In SQL. We write
it as VARCHAR where VARCHAR is an abbreviation for CHARACTER VARYING.
c. BIT(n)/BIT VARYING(n): BIT(n) represents a fixed length string of exactly ‘n’ bits and
BIT VARYING(n) represents a varying length string whose maximum length can be ‘n’
bits. VARBIT is an abbreviation for BIT VARYING. BIT and BIT VARYING are used to
store binary string, VARBIT/BIT VARYING is used for Hexadecimal binary string.

2. Numeric Data types: Data types NUMERIC, DECIMAL, INTEGER and SMALLINT are
known as Exact Numeric Data Types. The data type FLOAT(or REAL or DOUBLE
PRECISION) is known as Approximate Numeric Data Types. Exact and Numeric Data
Types are collectively known as Numeric Data Types.

*Approximate : close or be similar

to something in quality.

a. NUMERIC(p, q): This data types represents a Decimal number, ‘p’ represents the
total number of digits in the number both to the left and the right of the decimal
point. ‘q’ represents the number of digits of the right to the decimal point. Example,
NUMERIC(8, 2) can store numbers like 12345.67(8 total digits with 3 decimal places).

b. DECIMAL(p, q): DECIMAL(p, q) is used interchangeably with NUMERIC(p, q). It is

important to know that ‘DECIMAL(p, q)’ is equivalent to ‘NUMERIC(p, q)’.

c. INTEGER: This data type is used to store whole numbers. ‘INT’ is an abbreviation
for INTEGER. The INTEGER data type store whole numbers that range from -
2,147,483,647 to 2,147,483,647 for 9 to 10 digits of precision. This data typically
occupies 4 bytes of storage. In INT data type, specifying the size is not necessary.

d. SMALLINT: This data type is used to store whole numbers. It is designed to occupy
less storage space than the standard ‘INTEGER’ data type. The SMALLINT data type
store whole numbers that range from -32,768 to 32,768. This data type typically
uses 2 bytes of storage. Specifying the size is not necessary for this data type as
well.

e. FLOAT(p): FLOAT(p) represents a floating-point number, ‘FLOAT’ is an

abbreviation for FLOAT(p), where ‘p’ is implementation defined.

3. Date: It is used to store date values(day, month, year).

Example:
Create Table <tablename> (
<column_name> DATE
);
insert into <tablename>(column_name) values(TO_DATE(
‘24/12/2003’, ‘DD/MM/YYYY’));

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an

identifier. SQL literals are fixed, unchanging values used in SQL queries and statements, like
specific text, numbers, or dates, to represent themselves directly.

There are various types of literals in SQL which are:

1. String Literals: These are enclosed in single quote(‘ ‘) or double quotes(“ “) and
represent character or text values. Examples include ‘Chen’ or “57Street”.
2. Numeric Literals: These are numeric values without quotes and can represent integers
orfloating-point numbers. Examples include 42 or 3.14159.
3. Date and Time Literals: These represent date and time values in a specific formal. The
format can vary between different SQL database systems. Examples include DATE ’02-
09-2023’ or TIMESTAMP ‘2023-09-02 14:30:00’.

Constraints

Constraints in SQL means we are applying certain conditions or restrictions on the database. This
further means that before inserting data information on the database, we are checking for some
conditions. If the condition we have applied to the database holds true for thedata which is to be
inserted, then only the data will be inserted into the database tables.

Common Constraints available in SQL are:

1. NOT NULL: Null means empty, i.e., the value is not available. Whenever a table’s
column is declared as NOT NULL, then the value for that column cannot be empty for
any of the table’s records. There must exist a value in the column to which the NOT
NULL constraint is applied.

Note: NULL does not mean zero(0). NULL means empty column, not a zero.

2. UNIQUE: Duplicate values are not allowed in the columns to which the unique
constraint is applied. The column with the unique constraint will always contain a
unique value. This constraint can be applied to one or more than one column of a table,
which means more than one unique constraint can exist on a single table. Using the
UNIQUE constraint, we can also modify the already created tables.
Syntax:
ALTER TABLE <tablename> ADD UNIQUE(columnName);

3. PRIMARY KEY: PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE
constraints. NOT NULL and UNIQUE constraint together forms a PRIMARY KEY
constraint. The column to which we have applied the primary constraint will always
contain a unique value and will not allow null values.

4. FOREIGN KEY: Establishes a link between two tables by ensuring that the values in a
column(usually a primary key in one table) match values in another table(usually a
foreign key in the other table). This enforces referential integrity.

5. CHECK: Whenever a check constraint is applied to the table’s column, and the user
wants to insert the value in it, then the value will be first checked for certain
conditions before inserting the value into that column.

Example:

If we have an age column in a table, then the user will insert any value of his choice. The user
will also enter even a negative value or any other invalid value. But if the user has applied
CHECK constraint on the age column with the condition age greater than 18. Then, in such
cases, even if a user tries to insert an invalid value such as zero or any other value less than 18,
then the age column will not accept that value and will not allow the user to insert it dueto the
application of CHECK constraint on the age column.

Create Table STUDENT(

Age INT CHECK(Age<=18)
);

6. DEFAULT: Whenever a DEFAULT constraint is applied to the table’s column and the
user has not specified the value to be inserted in it, then the DEFAULT value which was
specified while applying the DEFAULT constraint will be inserted into that particular
column.

Example:
1. We create the ‘Employees’ table with several columns, including
‘EmployeeID’, ‘FirstName’, ‘LastName’ and ‘HireDate’.

2. The ‘HireDate’ column has a DEFAULT constraint with ‘CURRENT_DATE’, which is a function
that retrieves the current date. This means that if we don’t specify a ‘HireDate’ when inserting
a new employee, the current date will be used as the default value.
Create Table Employees(
EmployeeID INT PRIMARY KEY,
FirstName CHAR(50), LastName
CHAR(50),
HireDate DATE DEFAULT CURRENT_DATE,
);

However, if we provide a specific ‘HireDate’ during the insertion, the provided value will be used
instead of the DEFAULT.

SQL COMMANDS

Data Definition Language

Data Definition Language is used to create alter and delete database objects. The
commands used are CREATE, ALTER and DROP. DDL changes the structure of the table like
create table, create view, create index, alter table, drop table, drop view and drop index.
Commands that come under DDL are:

1. Create - Create is used to create a new table in a database.

Syntax: Create Table
<table_name>(
<attribute_name>
data_type(data_size), primary
key (attribute));

Example : OUTPUT:
Create Table Customer
(
ID
char(2)
,
NAME
char(1
5), CITY
char(1
0),
COMMISSION
number(2),
primary key (ID));

2. Drop - Drop is used to delete or remove an existing table in a database.

Syntax: drop table <table_name>
Example:

drop table premier league table;

3. Alter - Alter is used to alter/modify table in a database. This change could be either to
modifythe existing attribute or to add a new attribute.

 Add Attribute
Syntax: alter table <table_name> add <column_name> data_type;

Example: OUTPUT:

alter table student add div varchar(6)

 Drop Column
Syntax: alter table <table_name> drop <column_name>;

Example:

alter table student drop div;

OUTPUT:

 Rename Column
Syntax: alter table <table_name> rename column <column_name> to <new_name>;

Example:

alter table student rename column div to subj;

OUTPUT:

 Alter/Modify Datatype
Syntax: alter table <table_name> modify <column_name> datatype;

Example:

alter table student modify name char(50);

4. Truncate - It delete all the data from a table excepts the structure of the table.
Syntax: truncate <table_name>;

Example:
truncate <student>

OUTPUT: Data Manipulation Language

Data Manipulation Language commands is used to insert, modify and delete the data in a
database. SQL commands under DML are:

1. Insert - The Insert statement is used to insert data into the row of a table.
Syntax: insert into <table_name> (col1,col2,col3, ) values (value1,value2,value3);

Example:

insert into student (rno,name,marks,div) values (1,’Alpha’,20,’FAILED’);

OUTPUT:

2. Update - This command is used to update the value of a column in the table.
Syntax: update table <table_name> set <column_name> = <new_value> where
<condition>;
Example:

update table student set name=‘Zakira’ where

rno=1; OUTPUT:
3. Delete - Delete is used to delete a particular row from a table.
Syntax: delete from <table_name> where <condition>;
Example: OUTPUT:

delete from student where name=“Alpha";

Data Control Language

DCL commands are used to control user access to data stored in a database. It is used to
grant (give access) and take back authority from any database user. It is related to the
security issues of a database. Commands under DCL are:

1. Grant - This command grants permissions to the users on the database

objects. This command also allows users to grant permissions to other users too.
Syntax: grant <privilege_name> on <table_name> to <user_name>
Example:

Give access to insert data to user ‘A’: grant insert on student to user_a;

2. Revoke - Revoke is used to take away or remove permission given to the users on
databaseobjects.
Syntax: revoke <privilege_name> on <object_name> from <user_name>

Example:

Remove permission to insert data from user ‘A’: revoke insert on student from user_a;

SQL OPERATOR

What is SQL Operation?

SQL Operation refers to the actions that can be performed on a database using that SQL
language. SQL is a domain specific language that allows you to interact, manage and
manipulatedata stored in a RDBMS.

The operator that performs the operation on the expression is classified into four operators:

1. Arithmetic Operator: It performs the mathematical operation on the

numerical data of the SQL table. These types of operators are used between
two numerical operands for performing addition, subtraction, multiplication,
and division operations on the
numerical operands.
The arithmetic operator in SQL is categorized into five types as:

1. Addition Operator (+): It performs the addition on the numerical data of

database table. 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 of the existing numbers of the
specific column.
Syntax:

SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name

WHERECondition;

SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee;

2. Subtraction Operator (-): It performs 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 columns as the first
and second operand. We can also subtract the number from the existing
number of the specific table column.
Syntax: SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM
Table_NameWHERE Condition;

SELECT Emp_Salary - Emp_Panelty AS Emp_Total_Salary FROM Employee;

3. Multiplication Operator (*): It 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 columns as the first and second operand.

Syntax: SELECT Column_Name_1 Multiplication_Operator Column_Name2 FROM

Table_NameWHERE Condition;

SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars;

4. Division Operator (/): It divides the operand on the left side by the
operand on the right side. In SQL, we can easily divide the numerical values
of two columns of the same table by specifying both the columns as the
first and second operand. We can also perform the division operation on
the stored numbers in the column of the SQL table.

Syntax: SELECT Column_Name_1 Division_Operator Column_Name2 FROM

Table_NameWHERE Condition;

SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars;

5. Modulus Operator (%): It provides the remainder when the operand on the
left sideis divided by the operand on the right side.

Syntax: SELECT Column_Name1 Modulus_Operator Column_Name2 FROM

Table_Name: SELECT Student_English % Student_Maths AS Remainder FROM

Student;

2. Comparison Operator: It compares two different data of SQL table and check
whetherthey are same, greater, and lesser.

The comparison operators in SQL are categorized into the following six
operatorscategory:

1. Equal Operator (=): It is highly used in SQL queries. The equal operator in
SQL shows only data that matches the specified value in the memory.

Syntax to access data: SELECT * FROM Table_Name WHERE Column_Name = Value;

Syntax to update data:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name = Value;

Syntax to delete data: DELETE FROM Table_Name WHERE Field_Name = Value;

SELECT * FROM Employee WHERE Emp_Salary = 35000;

2. Equal Not Operator (<>, !=): It shows only those data that do not match the
query’s specified value. It returns those records or rows from the database
views and table if the value of both operands specified in the query is not
matched with each other.
Syntax to access data: SELECT * FROM Table_Name WHERE Column_Name !=
Value;Syntax to update data:
UPDATE Table_Name SET Column_Name = Value WHERE Field_Name !=

Value; Syntax to delete data: DELETE FROM Table_Name WHERE

Field_Name != Value; SELECT * FROM Cars WHERE Car_Price != 900000;

3. Greater Than Operator (>): It shows only those data which are greater than
the valueof the right-hand operand.
Syntax to access data: SELECT * FROM Table_Name WHERE Column_Name >
Value;Syntax toupdate data:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name

> Value; Syntax to delete data: DELETE FROM Table_Name WHERE

Field_Name > Value;

SELECT * FROM Cars_Details WHERE Car_Number > 6000;

4. Greater Than Equals Operator: It shows only those data which are
greater than or equal to the value of the right-hand operand.

Syntax to access data: SELECT Column_Name1, Column_Name2, …..,

Column_NameN FROMTable_Name WHERE Column_Name >= Value;

Syntax to update data: UPDATE Table_Name SET Column_Name = Value WHERE Column_Name
>= Value;

Syntax to delete data: DELETE FROM Table_Name WHERE Column_Name

>= Value SELECT * FROM Student_Details WHERE Student_Total_Marks>=

120;
5. Less Than Operator (<): It shows only those data from the database which are less
than the value of the right-side operand.

Syntax to access data: SELECT * FROM Table_Name WHERE Column_Name < Value;

Syntax to update data:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name < Value;

Syntax to delete data: DELETE FROM Table_Name WHERE Field_Name < Value;

SELECT * FROM Cars_Details WHERE Car_Amount < 6;

6. Less Than Equals Operator (<=): It shows those data from the table which are lesser
and equal to the value of the right-side operand.

Syntax to access data:

SELECT Column_Name1, Column_Name2, ….., Column_NameN FROM Table_Name WHERE Colu
mn_Name <= Value;

Syntax to update data:

UPDATE Table_Name SET Column_Name = Value WHERE Column_Name <= Value;

Syntax to delete data:

DELETE FROM Table_Name WHERE Column_Name <= Value;
SELECT Student_Id, Student_Maths, Student_English, Student_Total_Marks FROM Student_Det
ails WHERE Student_Id <= 202;

3. Logical Operator: It performs the Boolean operation which give two results True or
False. These operators provide true value if both operands match the logical condition.

The logical operators are classified as follows:

1. ALL Operator: It 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 – SELECT, HAVING, and WHERE.
Syntax:
SELECT column_Name1, ....., column_NameN FROM table_Name WHERE column Comparison_o
perator ALL (SELECT column FROM tablename2)

SELECT Emp_Id, Emp_Name FROM Employee_details WHERE Emp_Salary > ALL ( SELECT Emp_S
alary FROM Employee_details WHERE Emp_City = Jaipur)

Here, we used the SQL ALL operator with greater than the operator.

2. AND Operator: It 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 WHERE clause.

Syntax:
SELECT column1, .... , columnN FROM table_Name WHERE condition1 AND condition2 AND con
dition3 AND ........ AND conditionN;

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.

3. OR Operator: It shows the record from the table if any of the conditions separated by
the OR operator evaluates to True.

Syntax:
SELECT column1, .... , columnN FROM table_Name WHERE condition1 OR condition2 OR conditi
on3 OR ........ OR conditionN;

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.

4. BETWEEN Operator: It shows the record within the range mentioned in the SQL
query. It operates numbers, characters and date/time operands.
Syntax:
SELECT column_Name1, column_Name2 .... , column_NameN FROM table_Name WHERE colum
n_name BETWEEN value1 and value2;

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:

SELECT * FROM Employee_details WHERE Emp_Salary BETWEEN 30000 AND 45000;

5. IN Operator: It allows database users to specify two or more values in a where

clause. The logical operator minimizes the requirement of multiple OR conditions.

Syntax:
SELECT column_Name1, column_Name2 .... , column_NameN FROM table_Name WHERE colum
n_name IN (list_of_values);

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

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

6. NOT Operator: It shows the record from the table if the condition evaluates to
False./ It takes a single Boolean as an argument and change its value from False to
True or from True to False. It is always used with the WHERE clause.

Syntax: SELECT column1, column2 .... , columnN FROM table_Name WHERE NOT condition;

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 City is not Delhi and Chandigarh. For this, we have to
write the following query in SQL:

SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' AND NOT Emp_City = 'Chandi
garh';
7. ANY Operator: It shows the records where any of the values returned by the sub-
query meet the condition.

Syntax:
SELECT column1, column2 .... , columnN FROM table_Name WHERE column_name comparison_
operator ANY ( SELECT column_name FROM table_name WHERE Condition(s));

8. LIKE Operator: It shows those records from the table which match with the given
pattern specified in the sub-query.

Syntax:
SELECT column_Name1, column_Name2 .... , column_NameN FROM table_Name WHERE colum
n_name LIKE pattern;

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_details
whose name ends with ''y''. For this, we have to write the following query in SQL:

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

o If we want to show all the information of those employees from the Employee_details
whose name starts with ''S'' and ends with ''y''. For this, we have to write the following
query in SQL:

SELECT * FROM Employee_details WHERE Emp_Name LIKE 'S%y' ;

4. Set Operator: 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. It combines more than one selects statement in a single
query and return a specific result set.

It is categorized into four types:

1. UNION Operator: It combine 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:
SELECT column1, column2 .... , columnN FROM table_Name1 [WHERE conditions]UNION
SELECT column1, column2 .... , columnN FROM table_Name2 [WHERE conditions];

SELECT Emp_ID, Emp_Name FROM Employee_details1 UNION SELECT Emp_ID, Emp_Name FR

OM Employee_details2;

2. UNION ALL Operator: It is the same as union operator, but the only difference is
that it has also showed the same record.

Syntax:
SELECT column1, column2 ...., columnN FROM table_Name1 [WHERE conditions] UNION ALL
SELECT column1, column2 .... , columnN FROM table_Name2 [WHERE conditions];

SELECT Emp_Name FROM Employee_details1

UNION ALL
SELECT Emp_Name FROM Employee_details2 ;

3. INTERSECT Operator: It 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:
SELECT column1, column2 ...., columnN FROM table_Name1 [WHERE conditions] INTERSECT
SELECT column1, column2 .... , columnN FROM table_Name2 [WHERE conditions];

SELECT Emp_Name FROM Employee_details1

INTERSECT
SELECT Emp_Name FROM Employee_details2 ;

4. MINUS Operator: It combines the result of two or more select statements

and shows only the results from the first data set.
Syntax: SELECT column1, column2 .... , columnN FROM First_tablename [WHERE conditions]

MINUS
SELECT column1, column2 .... , columnN FROM Second_tablename [WHERE conditions];

SELECT Emp_Name FROM Employee_details1

MINUS
SELECT Emp_Name FROM Employee_details2 ;

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.

Operators at top have high precedence and the operators at bottom have low precedence.

SQL OPERATOR SYMBOLS OPERATORS

** Exponentiation Operator

+,- Identity Operator, Negation operator

*, / Multiplication Operator, Division Operator

+, -, || Addition Operator, Subtraction Operator,

String Concatenation

=, !=, <,>, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison Operator

NOT Logical Negation Operator

&& or AND Conjunction Operator

OR Inclusion Operator
AGGREGATE FUNCTIONS

GENERAL RULES:

SQL provides six aggregate functions. These are powerful tools and can improve data
retrieval power considerably. There are some rules which must be followed while using these
functions.They are:

For SUM and AVG the argument must be of type numeric.

Except for the special case COUNT (*), the argument may be preceded by the key word
DISTINCT to eliminate the duplicate rows before the function is applied to a column.
The alternative to DISTINCT is ALL, which is the default. The DISTINCT is legal for MAX
and MIN but meaningless.
The special function COUNT (*) which is used to all rows without any duplicate
elimination and so the keyword DISTINCT is not allowed for this function.
The argument cannot involve any aggregate function references or table expressions at
any level of nesting. For example, the SQL ' SELECT AVG(MIN(QTY)) AS AVERAGE' is
illegal.
Any NULL in the column is eliminated before the function is applied, regardless of
whether DISTINCT is specified or not except in the case of COUNT (*) where nulls are
handled like normal values.
When using the MIN and MAX with string data, the comparison of the strings is
dependent on the character set that is being used. In computers using ASCIl character
set, digits come before letters in the sorting sequence and all uppercase characters
come before the lowercase characters. On machines that use the EBCDIC character set,
the
order is lower case characters, uppercase characters and then digits. Because of
this difference in collating sequence, a query using the ORDER BY clause can
produce
different results in the two systems-hence there will be differences in the results of
theMIN and MAX functions.

1. What is SQL Aggregate Functions?

SQL aggregation functions are used to perform calculations on multiple rows of a single
column of a table. It returns a single value. It is also used to summarize the data.
Aggregate functions are mathematical operations that take a collection of values
(suchas numbers or data) and perform a calculation to summarize or condense those
valuesinto a single result.
Aggregate Functions consists of different types of SQL, the commonly used aggregate
functions are:

i. COUNT
ii. SUM
iii. AVG
iv. MAX
v. MIN

COUNT:

COUNT function is used to Count the number of rows in a database table. It can work on
both numeric and non-numeric data types.
Its purpose is to count the number of occurrences of a specific element or
conditionwithin a given set of data.

For Example:
Syntax: Select Count (*) from Emp;

Output: 6

Count (*) returns the number of all rows.

It can also be used to count the number of occurrences of a specific element

orcondition within a given set of data.
For Example:

Syntax: select count (odate) from orders where

odate = '10-Mar-1999’;

Output:
To count the number of occurrences of a specific element or condition within a given setof data.

SUM:
Sum function is used to calculate the sum of all selected columns. It works on
numericfields only.

For Example:
Syntax: select sum (AMOUNT), odate from

orders group by odate

Output:

It is used to calculate the sum of a sequence of numbers.

AVG:
The AVG function is used to calculate the average value of the numeric type. AVG
function returns the average of all non-Null values.

For Example:
Syntax: Select avg (AMOUNT) from orders;

Output:
In SQL, ‘AVG’ function is used to calculate the Average(mean) value of a set of numeric
values within a specified column of a table.

MAX:
Max () is used to find the maximum value in a column.
This function determines the largest value of all selected values of a

column.For Example:
Syntax: select cname, city, rating from customer

where rating= (select max(rating) from customer);

Output:

In SQL, the MAX () function is an aggregate function used to retrieve the maximum
valuefrom a specific column in a table or a set of rows that meet certain criteria.

MIN:
MIN function is used to find the minimum value of a certain column.
This function determines the smallest value of all selected values of a
column.

For Example:

Syntax: Select min (damage_amount) from participated

Output
In SQL, the ‘MIN ()’ function is used to retrieve the minimum value from a specified
column in a table. It is often used with the ‘SELECT’ statement to find the
smallest valuein a particular column.

What is Alter?
The ALTER TABLE statement in SQL is used to add, remove, or modify columns in an existing
table. The ALTER TABLE statement is also used to add and remove various constraints on
existing tables.

ALTER TABLE ADD Column Statement in SQL

ADD is used to add columns to the existing table. Sometimes we may require to add
additional information, in that case, we do not require to create the whole database
again, ADD comes to our rescue.

ALTER TABLE ADD Column Statement Syntax:

ALTER TABLE table_name ADD (Columnname_1 datatype,
Columnname_2 datatype, …Columnname_n datatype);
The following SQL adds an “Email” column to the “Students” table:
ALTER TABLE ADD Column Statement Example:
ALTER TABLE Students
ADD Email varchar(255);

SQL ALTER TABLE Queries

Suppose there is a student database:

Roll No Name
1 Alpha
2 Dina
3 Beni
4 Hruaia

To ADD 2 columns AGE and COURSE to table Student.

ALTER TABLE Student
ADD (AGE number (3), COURSE varchar (40));

OUTPUT:
Roll No Name Age Course
1 Alpha
2 Dina
3 Beni
4 Hruaia
MODIFY column COURSE in table Student.
ALTER TABLE Student
MODIFY COURSE varchar (20);

After running the above query the maximum size of the Course Column is reduced to 20
from 40.

What is drop?
DROP COLUMN is used to drop columns in a table. Deleting the unwanted columns from the
table.

ALTER TABLE DROP Column Statement Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL drop an “Email” column to the “Students” table:
ALTER TABLE DROP Column Statement Example:
ALTER TABLE Students
DROP COLUMN Email;

DROP column COURSE in table Student.

ALTER TABLE Student
DROP COLUMN COURSE;

OUTPUT :
Roll No Name Age
1 Alpha
2 Dina
3 Beni
4 Hruaia
INSERT, UPDATE AND DELETE

DML commands are used to manipulate data in a

database.Command of DML are as follows:

I ) INSERT : In SQL, the “insert” statement is used to add new records or data into a

database table.

Syntax: INSERT INTO table_name (column1, column2,

column3, ...) VALUES (value1, value2, value3, ...);

Example:

Suppose there is a Student database and we want to add values.

ROLL_NO NAME ADDRESS PHONE AGE

1 Ram Delhi xxxxxxxxxxxxxx 18

2 RAMESH GURGAON xxxxxxxxxxxxxx 18

3 SUJIT ROHTAK xxxxxxxxxxxxxx 20

4 SURESH ROHTAK xxxxxxxxxxxxxx 18

If we want to insert values in the specified columns then we use the following

query: Query:

INSERT INTO Student (ROLL_NO, NAME,

Age) VALUES ('5','PRATIK','19');

Output:

The table Student will now look like this:

ROLL_NO NAME ADDRESS PHONE Age

1 Ram Delhi XXXXXXXXXX 18

2 RAMESH GURGAON XXXXXXXXXX 18

3 SUJIT ROHTAK XXXXXXXXXX 20

4 SURESH Delhi XXXXXXXXXX 18

5 PRATIK null null 19

II ) UPDATE : The UPDATE statement in SQL is used to update the data of an existing table

in the database. We can update single columns as well as multiple columns using the

UPDATEstatement as per our requirement.

Syntax : UPDATE table_name SET column1 = value1, column2 = value2,… WHERE

condition;

Example : Suppose we have a Customer table and we want to update data.

Query:

UPDATE Customer SET CustomerName = 'Nitin' WHERE Age = 22;

Output:

III ) DELETE : DELETE is a basic SQL operation used to delete data in a database.

This SQL DELETE operation is important for database size

management, data accuracy, and integrity.

Syntax : DELETE FROM table_name WHERE

some_condition; Example :

Suppose there is a Student database and we want to add values.

ROLL_NO NAME ADDRESS PHONE AGE

1 Ram Delhi xxxxxxxxxxxxxx 18

2 RAMESH GURGAON xxxxxxxxxxxxxx 18

3 SUJIT ROHTAK xxxxxxxxxxxxxx 20

4 SURESH ROHTAK xxxxxxxxxxxxxx 18

Query:

DELETE FROM Student WHERE address =

'Delhi'; Output:

ROLL_NO NAME ADDRESS PHONE AGE

2 RAMESH GURGAON xxxxxxxxxxxxxx 18

3 SUJIT ROHTAK xxxxxxxxxxxxxx 20

What is Cursor in SQL ?

Cursor is a Temporary Memory or Temporary Work Station. It is allocated by Database Server

at the Time of Performing DML operations on Table by User. Cursors are used to store Database
Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained
as following below.

1. Implicit Cursors:
Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are
allocated by SQL SERVER when the user performs DML operations.

2. Explicit Cursors:
Explicit Cursors are created by Users whenever the user requires them. Explicit Cursors
are usedfor Fetching data from Table in Row-By-Row Manner.

How to create Explicit Cursor:

1. Declare Cursor Object.

Syntax : DECLARE cursor_name CURSOR FOR SELECT * FROM

table_name DECLARE s1 CURSOR FOR SELECT * FROM studDetails

2. Open Cursor Connection.

Syntax : OPEN

cursor_connection OPEN s1

3. Fetch Data from cursor.

There are total 6 methods to access data from cursor. They are as follows :
FIRST is used to fetch only the first row from cursor table.
LAST is used to fetch only last row from cursor table.
NEXT is used to fetch data in forward direction from cursor
table. PRIOR is used to fetch data in backward direction from
cursor table. ABSOLUTE n is used to fetch the exact nth row
from cursor table.
RELATIVE n is used to fetch the data in incremental way as well as decremental way.
Syntax : FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM

cursor_name

FETCH FIRST FROM s1

FETCH LAST FROM
s1 FETCH NEXT
FROM s1 FETCH
PRIOR FROM s1
FETCH ABSOLUTE 7 FROM
s1 FETCH RELATIVE -2
FROM s1
4. Close cursor connection.
Syntax : CLOSE
cursor_name

CLOSE s1

5. Deallocate cursor memory.

Syntax : DEALLOCATE

cursor_nameDEALLOCATE s1

What is Embedded SQL?

Embedded SQL are SQL statements included in the programming language. It is a technique that
allows SQL statement to be included directly within a program written in a high-level
programming language.

It is a method of combining SQL statements with programming languages. This allows direct
interactionwith the database from the code.
The programming language in which the SQL statements are included is called the host

language.Some of the host languages are C, C++, COBOL, FORTRAN, etc.

Why is embedded SQL needed?

Embedded SQL is used to bridge the gap between the database and the application providing
means for developers to access and manipulate databases using programming language they
are familiar with.
The goal is to make the database interactions simpler for application developer and end users.

Structure of Embedded SQL

The structure of Embedded SQL contains a connection to the database, a declaration statement
(host variables and indicator of variables), an execution statements and error handling.
1. Connection to the database: The first step in using Embedded SQL is establishing a
connection to the database. This is done by using CONNECT keyword, preceded by EXEC
SQL toindicate that it is SQL statement.
EXEC SQL CONNECT <DB_NAME>;

EXEC SQL CONNECT <DB_USER>;

2. Declaration Section: The declaration section is used to declare variables that will be
used inSQL queries and to capture the results of the queries.

Types of variables used in host languages:-

1. Host variables

2. Indicator variables

Host variables: - Host variables are used to store data values passed between a program and
SQL statements.
Example:

Indicator variables: - Indicator variables are used to capture NULL values which are returned by
queries or to set NULL values in INSERT or UPDATE statement.
Example:
3. Execution Section: - The execution section contains all the SQL queries and statements
thatare prefixed by EXEC SQL.

4. Error Handling: - Error handling is essential in embedded SQL. In embedded SQL,

error handling is based on the host language. In C programs, error handling is
typically done using labels and the WHENEVER statement.

The WHENEVER statement is used to define the action to be taken when a

certaincondition occurs.
The condition can be SQLWARNING, SQLERROR, or NOT FOUND. The action can
be CONTINUE, DO <function>, GOTO , or STOP.
Advantages of Embedded SQL

Ease of Database Usage

Accessing databases is made simple with embedded SQL, which eliminates the need for
lengthy coding or other programs. It offers a simple method for getting data by feeding it into
the database, doing away with the need for complex and complicated programming.

Security and authorization

With embedded SQL, developers may specify and implement appropriate authorization
procedures for database access. This helps prevent unauthorized access to sensitive data and
ensures that only users with the proper authorization can execute certain tasks.

Integration of Frontend and Backend

By permitting a direct connection between the user interface and the database,
embedded SQL makes it easier to integrate the front end and back end of an application. This
improves the overall user experience by enabling real−time data retrieval and changes.

Error prevention
Developers can prevent logical mistakes from happening when processing database
transactions by integrating SQL queries into the application code. The integration of SQL with
the host language guarantees security and improves the application's dependability.

Disadvantages of Embedded SQL

Knowledge of the Host Language Required

Developers must have a solid understanding of the host language in which the SQL is
contained to use embedded SQL effectively. This can require more programming language
knowledge or training.

Complex Development Model

By using embedded SQL, the development process becomes even more difficult. It
may be difficult for developers to handle the integration of SQL statements within host
language code, especially for larger systems.

Limited SQL Flexibility

SQL statements must be declared at design time for embedded SQL, which means that
only predefined queries may be used in application code. The application's ability to react to
shifting requirements may be hampered by the absence of dynamic SQL flexibility.
EXAMPLE: -

#include <stdio.h>
#include <stdlib.h>
#include <sqlca.h>

int main() {
/* Connect to the database (replace 'student' with your database connection details) */
EXEC SQL CONNECT TO :database;

/* Declare host variables matching the database schema */

EXEC SQL BEGIN DECLARE SECTION;
int std_id;
int roll_no;
char name[50]; /* Adjust the size according to the actual column size */
char address[100]; /* Adjust the size according to the actual column size */
EXEC SQL END DECLARE SECTION;

/* Set up error processing */

EXEC SQL WHENEVER SQLWARNING DO display_warning();
EXEC SQL WHENEVER SQLERROR DO display_error();

/* Execute the SQL query */

EXEC SQL SELECT ROLL_NO, NAME, ADDRESS INTO :roll_no, :name, :address
FROM STUDENT WHERE STUDENT_ID = :std_id;

/* Check if any records were found */

if (sqlca.sqlcode == 1403) {
printf("No records found for student with ID %d.\n", std_id);
} else if (sqlca.sqlcode < 0) {
printf("SQL error occurred.\n");
} else {
/* Display the result */
printf("Roll No: %d\n", roll_no);
printf("Name: %s\n", name);
printf("Address: %s\n", address);
}

/* Disconnect from the database */

EXEC SQL DISCONNECT;

return 0;
}