Unit 1 :

Introduction to SQL

Que 1: Introduction SQL

 SQL (Structured Query Language) is used to perform operations on the records stored in
the database, such as updating records, inserting records, deleting records, creating and
modifying database tables, views, etc.
 SQL is not a database system, but it is a query language.
 SQL has two broad categories:
o DDL (Data Definition Language):used for defining, managing, and manipulating
the structure of a relational database. DDL statements are primarily concerned
with the creation, modification, and deletion of database objects such as tables,
indexes, views, and constraints. The data definition contains commands like
create, drop, truncate, alter and rename .
o DML(Data Manipulation Language): DML statements are used to perform
operations like retrieving, inserting, updating, and deleting data from database
tables. It contains Select, Insert, Update and Delete command

Que 2: Data types.

 Data types are used to represent the nature of the data that can be stored in the
database table.
 Data types mainly classified into three categories for every database.

o String Data types

o Numeric Data types
o Date and time Data types
 MySQL String Data Types

It is used to specify a fixed length string that can contain

CHAR(Size) 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

1|Page
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.

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
val3,...) from a list of 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.

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

65,535 bytes. stores any kind of binary data in random-access
chunks, called sbspaces

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.

2|Page
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
d) the total number 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
d) the total number 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.

Que 3: How to create Table

3|Page
 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.
Syntax:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type",
...
"columnN" "data type");
Example:
create table student(name varchar(10),address varchar(50),mobn int(10));

Que 4: Explain Sql constraints .

 SQL constraints are used to specify rules for the data in a table.
 Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.
 Constraints can be specified when the table is created with the CREATE TABLE
statement, or after the table is created with the ALTER TABLE statement.
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

 The following constraints are commonly used in SQL:

1. NOT NULL
 Ensures that a column cannot have a NULL value
Example
create table employee(ename varchar(10) not null,address varchar(10) not null,
mnumber int not null);
2. UNIQUE
4|Page
 The UNIQUE constraint ensures that all values in a column are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for
a column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint.
 However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
Example
create table emp2 (enamevarchar(12) not null unique , adress varchar(40) not null);
3. PRIMARY KEY
 The PRIMARY KEY constraint uniquely identifies each record in a table.
 Primary keys must contain UNIQUE values, and cannot contain NULL values.
Example
create table emp3(eid int not null,ename varchar(12) not null , adress varchar(40) not
null,primary key (eid));

4. FOREIGN KEY
 A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY
KEY in another table.
 The table with the foreign key is called the child table, and the table with the primary
key is called the referenced or parent table.
Example
CREATE TABLE teacher(tid int not null, tname varchar(10), PRIMARY KEY (tid));
CREATE TABLE student ( sid int NOT NULL, sname varchar(12) NOT NULL, tid int,
PRIMARY KEY (sid),FOREIGN KEY (tid) REFERENCES teacher(tid));

5. CHECK–
 The CHECK constraint is used to limit the value range that can be placed in a column.
 If you define a CHECK constraint on a column it will allow only certain values for this
column.
 If you define a CHECK constraint on a table it can limit the values in certain columns
based on values in other columns in the row.
Example

CREATE TABLE student1 ( sid int NOT NULL, sname varchar(12) NOT NULL,age int not
null, check (age>=10));

5|Page
6. DEFAULT–
 The DEFAULT constraint is used to set a default value for a column.
 The default value will be added to all new records, if no other value is specified.
Example
CREATE TABLE student (sid int not null,sname varchar(12), scity varchar(12) default
"kalol");
7. CREATE INDEX
 The CREATE INDEX statement is used to create indexes in tables.
 Indexes are used to retrieve data from the database more quickly than otherwise. The
users cannot see the indexes, they are just used to speed up searches/queries.
Example
create index index_sname on student(sname);
Que 4: How to add Table row ?
 SQL requires the use of the INSERT command to enter data into a table.
Syntax
 It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);
Example
insert into student (sid,sname)values(1,"monali");

sid sname

1 monali

2. If you are adding values for all the columns of the table, you do not need to
specify the column names in the SQL query. However, make sure the order of the
values is in the same order as the columns in the table. Here, the INSERT INTO
syntax would be as follows:

INSERT INTO table_name VALUES (value1, value2, value3, ...);

6|Page
Example

insert into student values (3,"tulsi","amdavad");

Que

Que 5 :Saving table changes and restoring table data or commit or rollback
command in sql

Commit

 If you want to save all the commands which are executed in a transaction, then just after
completing the transaction, you have to execute the commit command.
 This command will save all the commands which are executed on a table. All these
changes made to the table will be saved to the disk permanently.

Example: Consider the following STAFF table with records:

STAFF

sql> SELECT *FROM Staff WHERE Allowance = 400;

7|Pa ge
sql> COMMIT;

Output:

rollback

 The rollback command is used to get back to the previous permanent status of the
table, which is saved by the commit command.
 Suppose, we have started editing a table and later thought that the changes that we
have recently carried out on a table are not required. Then, in that case, we can roll back
our transaction, which simply means to get back to the previous permanent status of
the table, which is saved by the commit command.

Example:

sql> SELECT *FROM EMPLOYEES WHERE ALLOWANCE = 400;

sql> ROLLBACK;

Difference between COMMIT and ROLLBACK

COMMIT ROLLBACK

1. COMMIT permanently saves the ROLLBACK undo the changes made by the

8|Pa ge
COMMIT ROLLBACK

changes made by the current current transaction.

transaction.

The transaction can not undo

Transaction reaches its previous state after
changes after COMMIT
ROLLBACK.
2. execution.

When the transaction is When the transaction is aborted, incorrect

3. successful, COMMIT is applied. execution, system failure ROLLBACK occurs.

COMMIT statement permanently In ROLLBACK statement if any operations

save the state, when all the fail during the completion of a transaction,
statements are executed it cannot permanently save the change and
4. successfully without any error. we can undo them using this statement.

Syntax of COMMIT statement

Syntax of ROLLBACK statement are:
are:
ROLLBACK;
COMMIT;
5.

Que 6: How to select data from table or Listing Table Rows.

 The SELECT Statement in SQL is used to retrieve or fetch data from a table
Syntax:
Select *from table;
Example
Select *from student;
Output

9|Page
Que 7:How to update rows in table?
 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 UPDATE
statement as per our requirement.

Syntax

UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

Example

update student set sid=5,sname="janki" where scity="amdavad";

Output

Que 8:How to delete table row? Or how to delete records from table?

 The DELETE statement is used to delete existing records in a table.

10 | P a g e
Syntax

DELETE FROM table_name WHERE condition;

Example
delete from student where scity="amdavad";
Output
You have made changes to the database. Rows affected: 3
SELECT * FROM [student]

Que 9: What is What is SQL Operator? List out Types of operator .

Explain select query conditional operators or with conditional restriction.

1. Conditional Operators
2. Arithmetic Operators
3. Logical Operators
4. Special Operators
 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.You can select partial table contents by placing restrictions on the rows to be
included in the output.
 This is done by using the WHERE clause to add conditional restrictions to the SELECT
statement.
 Numerous conditional restrictions can be placed on the selected table contents. For
example, the comparison
 operators shown in following Table can be used to restrict output.
11 | P a g e
 The following syntax enables you to specify which rows to select:
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SID SNAME SCITY
1 TULSI AMDAVAD
2 JANVI AMDAVAD
3 HIRAL KALOL
4 MONALI MEHSANA
5 SUCHITA KADI

Select sname from student where SCITY=”AMDAVAD”;

Output:
SID SNAME SCITY
1 TULSI AMDAVAD
2 JANVI AMDAVAD

Que 10: Explain select query with Arithmetic Operators.

 We can use these operators with the SELECT statement in SQL.
 We can also use the WHERE clause in the SELECT statement for performing operations
on particular rows.
 These types of operators are used between two numerical operands for performing
addition, subtraction, multiplication, and division operations.
 The arithmetic operators shown in following Table

12 | P a g e
1. SQL Addition Operator (+)
 The SQL Addition Operator performs the addition on the numerical columns in the
table.
 If you want to add the values of two numerical columns in the table, then you have to
specify both columns as the first and second operand.
 You can also add the new integer value in the value of the integer column.

Syntax of SQL Addition Operator:

SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name;

Addition Operator with WHERE Clause
The addition operator can also be used with the WHERE clause in the SQL SELECT
query.

The syntax for using the WHERE clause with the addition operator is given
below:

SELECT Column_Name_1 Addition_Operator Column_Name2 FROM Table_Name

WHERE Condition

Example

13 | P a g e
SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee;

The following query performs the addition operation on the above Employee table
with the WHERE clause:
SELECT Emp_Salary + Emp_Bonus AS Emp_Total_Salary FROM Employee WHERE
Emp_Salary> 25000;

2. SQL Subtraction Operator (-)

 The SQL Subtraction Operator performs the subtraction on the numerical
columns in the table.
 If we want to subtract the values of one numerical column from the values of
another numerical column, then we have to specify both columns as the first
and second operand.
 We can also subtract the integer value from the values of the integer column.

14 | P a g e
Syntax of SQL Subtraction Operator:

SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name;

Subtraction Operator with WHERE Clause
 The subtraction operator can also be used with the WHERE clause in the SELECT
query.
The syntax for using the WHERE clause with the subtraction operator is given
below:
SELECT Column_Name_1 Subtraction_Operator Column_Name2 FROM Table_Name
WHERE condition;
Example

SELECT Emp_Salary - Emp_Panelty AS Emp_Total_Salary FROM Employee;

The following query performs the subtraction operation on the above Employee
table with the WHERE clause:

SELECT Emp_Panelty - Emp_Salary AS Emp_Total_Salary FROM Employee WHERE

Employee_ID =104;

15 | P a g e
3. SQL Multiplication Operator (*)
 The SQL Multiplication Operator performs the multiplication on the numerical
columns in the table.
 If you want to multiply the values of two numerical columns, then you have to
specify both columns as the first and second operand.
 You can also multiply the integer value with the values of an integer column.

Syntax of SQL Multiplication Operator:

SELECT Column_Name_1 Multiplication_Operator Column_Name2 FROM Table_Name;
Multiplication Operator with WHERE Clause
 The multiplication operator (*) can also be used with the WHERE clause in the
SELECT query.

The syntax for using the WHERE clause with the multiplication operator is given
below:
SELECT Column_Name_1 Multilplication_Operator Column_Name2 FROM Table_Name
WHERE Condition;

Example

16 | P a g e
SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars;

SELECT Car_Amount * Car_Price AS Car_Total_Price FROM Cars WHERE Car_Price>=

1000000;

4. SQL Division Operator (/)

 The SQL Division operator divides the numerical values of one column by the
numerical values of another column.

Syntax of SQL Division Operator:

SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name;

17 | P a g e
Division Operator with WHERE Clause
The SQL division operator can also be used with the WHERE clause in the SELECT
query.

The syntax for using the WHERE clause with the division operator is given below:

SELECT Column_Name_1 Division_Operator Column_Name2 FROM Table_Name

WHERE Condition;

Example

SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars;

SELECT Car_Price / Car_Amount AS One_Car_Price FROM Cars WHERE Car_Number =

9258;

18 | P a g e
Que 10: Explain select query with 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 NOT operator
5. SQL ANY 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:

SELECT,
HAVING, and
WHERE.

1. Syntax of ALL operator:

SELECT column_Name1, ...., column_NameN FROM table_Name WHERE column Compari

son_operator

Example

19 | P a g e
SELECT Emp_Id, Emp_Name FROM Employee_details WHERE Emp_Salary > ALL ( SELECT Emp_Sa
lary FROM Employee_details WHERE Emp_City = Jaipur)

2. 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:

SELECT column1, ...., columnN FROM table_Name WHERE condition1 AND condition2
AND condition3AND ....... AND conditionN;

Example

20 | P a g e
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:

SELECT * FROM Employee_details WHERE Emp_Salary = 25000 AND 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. 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:

SELECT column1, ...., columnN FROM table_Name WHERE condition1 OR condition2 OR

condition3 OR....... OR conditionN;

Example

SELECT * FROM Employee_details WHERE Emp_Salary = 25000 OR Emp_City = 'Delhi';

4. 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:

SELECT column1, column2 ...., columnN FROM table_Name WHERE NOT condition;
Example
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:
SELECT * FROM Employee_details WHERE NOT Emp_City = 'Delhi' AND NOT Emp_City =
'Chandigarh';

5. SQL ANY Operator

21 | P a g e
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:

SELECT column1, column2 ...., columnN FROM table_Name WHERE column_name comp
arison_operator ANY ( SELECT column_name FROM table_name WHERE condition(s)) ;
Example

The following SQL statement lists the ProductName if it finds ANY records in the
OrderDetails table has Quantity equal to 10 (this will return TRUE because the Quantity
column has some values of 10):

SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID

FROM OrderDetails WHERE Quantity = 10);

Que 11:Explain Special Operators .

The different special operators in SQL are as follows

1. Between Operator
2. Is Null Operator
3. Like Operator
4. In Operator
5. Exists Operator
1. 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:

SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE co

lumn_name BETWEEN value1 and value2;
Example

SELECT * FROM Employee_details WHERE Emp_Salary BETWEEN 30000 AND 45000;

22 | P a g e
2. Is Null Operator
IS NULL operator is used to test for a NULL value.
Syntax
SELECT [column_name1,column_name2 ]FROM [table_name]WHERE [column_name] IS
[NOT] NULL;
Example

To get data of all columns from the 'listofitem' table with following condition -

1. coname column contain NULL value,

the following sql statement can be used :

SELECT * FROM listofitem WHERE coname IS NULL;

3. Like Operator
Syntax of Like operator
SELECT column_Name1, column_Name2 ...., column_NameN FROM table_Name WHERE colum
n_name LIKE pattern;
Example
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:
SELECT * FROM Employee_details WHERE Emp_Name LIKE 's%' ;

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

23 | P a g e
Syntax of IN operator:

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

column_name IN (list_of_values);
Example
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:

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

5. Exists Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
Syntax

SELECT column_name(s)FROM table_nameWHERE EXISTS(SELECT column_name FROM

table_name WHERE condition);

Example
SELECT fname, lname FROM Customers WHERE EXISTS (SELECT * FROM Orders
WHERE Customers.customer_id = Orders.c_id);

Que 12:Explain alter command .

 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

24 | P a g e
ALTER TABLE table_name ADD column_name column-definition;

Example

alter table student add scity varchar(12);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some
database systems don't allow deleting a column):

Syntax

ALTER TABLE table_nameDROP COLUMN column_name;

Example

ALTER TABLE student DROP COLUMN scity;

ALTER TABLE - RENAME COLUMN

To rename a column in a table, use the following syntax:

Syntax

ALTER TABLE table_name rename COLUMN old to new;

Example

alter table student rename column scity to city;

ALTER TABLE - ALTER/MODIFY DATATYPE

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_nameMODIFY COLUMN column_name datatype;

Example

ALTER TABLE student ALTER COLUMN city text;

ALTER TABLE Column’s Data characteristics in SQL

25 | P a g e
If the column to be changed already contains data, you can make changes in the
column’s characteristics if thosechanges do not alter the data type.

For example, if you want to increase the width of the P_PRICE column to 9 digits, use the
command:

Example

ALTER TABLE PRODUCTMODIFY (P_PRICE DECIMAL(9,2));

Que 12: How to delete Table from Database.

Using drop keyword we can delete the table from database.

Example:

Drop table student;

Que 13: Explain SQL Command

DDL (Data Definition Language) :

CREATE: This command is used to create the database or its objects (like table, index, function,
views, store procedure, and triggers).

26 | P a g e
 If you want to create a table, you should name the table and define its column and each
column's data type.
Syntax:
create table "tablename"
("column1" "data type",
"column2" "data type",
"column3" "data type",
...
"columnN" "data type");
Example:
create table student(name varchar(10),address varchar(50),mobn int(10));

DROP: This command is used to delete objects from the database.

Syntax
DROP object object_name ;
Example
DROP TABLE table_name;
ALTER: This is used to alter the structure of the database. (Syntax and Examples and already
given in above question no.12)

TRUNCATE: This is used to remove all records from a table, including all spaces allocated for
the records are removed. The major difference between TRUNCATE and DROP is that truncate
is used to delete the data inside the table not the whole table.

Syntax
TRUNCATE TABLE table_name;
Example:
To truncate the Student_details table from the student_data database.
Query:
TRUNCATE TABLE Student_details;
COMMENT: This is used to add comments to the data dictionary.
 Comments can be written in the following three formats:

Single-line comments
Comments starting and ending in a single line are considered single-line comments. A line
starting with ‘–‘ is a comment and will not be executed.
Example
SELECT * FROM customers;
-- This is a comment that explains

27 | P a g e
the purpose of the query.
Multi-line comments
Comments starting in one line and ending in different lines are considered as multi-line
comments.
A line starting with ‘/*’ is considered as starting point of the comment and is terminated
when ‘*/’ is encountered.
Example
/*
This is a multi-line comment that explains
the purpose of the query below.
The query selects all the orders from the orders
table that were placed in the year 2022.
*/
SELECT * FROM orders WHERE YEAR(order_date) = 2022;
In-line comments
In-line comments are an extension of multi-line comments, comments can be stated in
between the statements and are enclosed in between ‘/*’ and ‘*/’.
Example
SELECT * FROM /* Customers; */

RENAME: Sometimes we may want to rename our table to give it a more relevant name. For
this purpose, we can use ALTER TABLE to rename the name of the table.

DQL (Data Query Language)

 DQL statements are used for performing queries on the data within schema objects.
 The purpose of the DQL Command is to get some schema relation based on the query
passed to it.
 We can define DQL as follows it is a component of SQL statement that allows getting data
from the database and imposing order upon it. It includes the SELECT statement.
List of DQL:
SELECT: It is used to retrieve data from the database.

DML(Data Manipulation Language)

 The SQL commands that deal with the manipulation of data present in the database
belong to DML or Data Manipulation Language and this includes most of the SQL
statements.

28 | P a g e
 It is the component of the SQL statement that controls access to data and to the
database. Basically, DCL statements are grouped with DML statements.
List of DML commands:
1) Insert Command
• The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
1) Specify both the column names and the values to be inserted:
Syntax:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2,
value3, ...);
Example: INSERT INTO Person (id,name,age) VALUES (1,’mira’,30);
2) If you are adding values for all the columns of the table, you do not need to
specifythe column names in the SQL query. However, make sure the order of the
values is in the same order as the columns in the table. Here, the INSERT INTO
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3, ...);
Example:
INSERT INTO Person VALUES (1,’dixita’,30);
2) Update Command
• The UPDATE statement is used to modify the existing records in a table.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
UPDATE Customers SET ContactName = 'Alfred' WHERE CustomerID = 1;
3) Delete Command
• The DELETE statement is used to delete existing records in a table.
Syntax:
DELETE FROM table_name WHERE condition;
Example:

29 | P a g e
DELETE FROM Customers WHERE CustomerID = 1;
4) Select Command
• The SELECT statement is used to select data from a database.
Syntax:
SELECT column1, column2, FROM table_name;
Here, column1, column2, are the field names of the table you want to select data from. If
you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;
Example:
SELECT * FROM Person;

1. SELECT with DISTINCT Clause

• The SELECT DISTINCT statement is used to return only distinct (different) values.

Syntax:
SELECT DISTINCT column1, column2,FROM table_name;
Example:
SELECT DISTINCT Country FROM Customers;
2. SELECT with WHERE Clause
• The WHERE clause is used to filter records.
• The WHERE clause is used to extract only those records that fulfill a specified
condition.
Syntax:
SELECT column1, column2, FROM table_name WHERE condition;

30 | P a g e
Example:
SELECT * FROM Customers WHERE Country='Mexico';
3. SELECT with ORDER BY Keyword
• The ORDER BY keyword is used to sort the result-set in ascending or descending
order.
• The ORDER BY keyword sorts the records in ascending order by default. To sort the
records in descending order, use the DESC keyword.
Syntax:
SELECT column1, column2, FROM table_name ORDER BY column1, column2, ASC|DESC;
Example:
SELECT * FROM Customers ORDER BY Country DESC;
4. SELECT with Group by Clause
• The GROUP BY statement groups rows that have the same values into summary rows
like "find the number of customers in each country".
• The GROUP BY statement is often used with aggregate functions (COUNT (), MAX (),
MIN (), SUM (), AVG ()) to group the result-set by one or more columns.
Syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY
column_name(s) ORDER BY column_name(s);

Example:
SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country;
Output:

31 | P a g e
5. SELECT with Having Clause
• The HAVING clause was added to SQL because the WHERE keyword cannot be
used with aggregate functions.
Syntax:
SELECT expression1, expression2,expression_n,aggregate_function
(aggregate_expression) FROM tables WHERE conditions GROUP BY expression1,
expression2, ... expression_n HAVING having_condition; ORDER BY column_name(s);
• expression1, expression2, expression_n: It specifies the expressions that are
not encapsulated within aggregate function. These expressions must be included
in GROUP BY clause.
• aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN,
MAX or AVG functions.
• aggregate_expression: It specifies the column or expression on that the aggregate
function is based on.
• tables: It specifies the table from where you want to retrieve records.
• conditions: It specifies the conditions that must be fulfilled for the record to be
selected.
• having_conditions: It specifies the conditions that are applied only to the
aggregated results to restrict the groups of returned rows.
Example
Table Name: sales_department

32 | P a g e
Example:
SELECT item, SUM(sale) FROM salesdepartment GROUP BY item HAVING SUM(sale) <
1000;
Output:

DCL (Data Control Language)

 DCL includes commands such as GRANT and REVOKE which mainly deal with the rights,
permissions, and other controls of the database system.
List of DCL commands:
 GRANT: This command gives users access privileges to the database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.
Syntax:

33 | P a g e
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

TCL (Transaction Control Language)

 Transactions group a set of tasks into a single execution unit. Each transaction begins
with a specific task and ends when all the tasks in the group are successfully completed.
If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results:
success or failure. You can explore more about transactions here. Hence, the following
TCL commands are used to control the execution of a transaction:
BEGIN: Opens a Transaction.
COMMIT: Commits a Transaction.
Syntax:
COMMIT;

ROLLBACK: Rollbacks a transaction in case of any error occurs.

Syntax:
ROLLBACK;

SAVEPOINT: Sets a save point within a transaction.

SAVEPOINT SAVEPOINT_NAME;

34 | P a g e
Unit 2:
Transaction Management and concurrency control

Que 1: What is Transaction?

Any logical work or set of works that are done on the data of a database is
known as a transaction. Logical work can be inserting a new value in the current
database, deleting existing values, or updating the current values in the
database.

For example, adding a new member to the database of a team is a transaction.

To complete a transaction, we have to follow some steps which make a
transaction successful. For example, we withdraw the cash from ATM is an
example of a transaction, and it can be done in the following steps:
o Initialization if transaction
o Inserting the ATM card into the machine
o Choosing the language
o Choosing the account type
o Entering the cash amount
o Entering the pin
o Collecting the cash
o Aborting the transaction

So, in the same way, we have three steps in the DBMS for a transaction which are the
following:
o Read Data
o Write Data
o Commit

We can understand the above three states by an example. Let suppose we have two
accounts, account1, and account2, with an initial amount as 1000Rs. each. If we want to
transfer Rs.500 from account1 to account2, then we will commit the transaction.
o All the account details are in secondary memory so that they will be brought into
primary memory for the transaction.
o Now we will read the data of account1 and deduct the Rs.500 from the account1.
Now, account1 contains Rs.500.

P a g e 1 | 17
o Now we will read the data of the account2 and add Rs.500 to it. Now, account2
will have Rs.1500.
o In the end, we will use the commit command, which indicates that the transaction
has been successful, and we can store the changes in secondary memory.
o If, in any case, there is a failure before the commit command, then the system will
be back into its previous state, and no changes will be there.

During the complete process of a transaction, there are a lot of states which are
described below:

Active State:
When the transaction is going well without any error, then this is called an active state.
If all the operations are good, then it goes to a partially committed state, and if it fails,
then it enters into a failed state.

Partially Committed State:

All the changes in the database after the read and write operation needs to be reflected
in permanent memory or database. So, a partially committed state system enters into a
committed state for the permanent changes, and if there is any error, then it enters into
a failed state.

Failed State:
If there is any error in hardware or software which makes the system fail, then it enters
into the failed state. In the failed state, all the changes are discarded, and the system
gets its previous state which was consistent.

Aborted State:
If there is any failure during the execution, then the system goes from failed to an
aborted state. From an aborted state, the transaction will start its execution from a fresh
start or from a newly active state.

Committed State:
If the execution of a transaction is successful, the changes are made into the main
memory and stored in the database permanently, which is called the committed state.

Terminated State:
If the transaction is in the aborted state(failure) or committed state(success), then the
execution stops, and it is called the terminated state.
P a g e 2 | 17
Que 1: Explain concurrency control .
 The coordination of the simultaneous execution of transactions in a multiuser
database system is known as concurrency control.
 Concurrency control is important because the simultaneous execution of
transactions over a shared database can create several data integrity and
consistency problems.
 The three main problems are following
o Lost updates
o Uncommitted data
o Inconsistent retrievals.
1. Lost updates

 The lost update problem occurs when two concurrent transactions, T1 and
T2, are updating the same data element and one of the updates is lost
(overwritten by the other transaction).
Example
 Consider the following PRODUCT table example.
 One of the PRODUCT table’s attributes is (PROD_QOH).

 Assume that you have a product whose current PROD_QOH value is 35.

 Also assume that two concurrent transactions, T1 and T2, occur that
update the PROD_QOH value for some item in the PRODUCT table.

The transactions are as follows.

Two concurrent transactions update PROD_QOH:
P a g e 3 | 17
Transaction Operation
T1: Purchase 100 units PROD_QOH = PROD_QOH + 100
T2: Sell 30 units PROD_QOH = PROD_QOH – 30

The Following table shows the serial execution of those transactions under
normal circumstances, yielding the correct answer PROD_QOH = 105.

 But suppose that a transaction is able to read a product’s PROD_QOH

value from the table before a previous transaction (using the same
product) has been committed.
 The sequence depicted in the following Table shows how the lost update
problem can arise.
 Note that the first transaction (T1) has not yet been committed when the
second transaction (T2) is executed.
 Therefore, T2 still operates on the value 35, and its subtraction yields 5 in
memory.
 In the meantime, T1 writes the value 135 to disk, which is promptly
overwritten by T2. In short, the addition of 100 units is “lost” during the
process.

2. Uncommitted Data:
 uncommitted data occurs when two transactions, T1 and T2, are executed
concurrently(parallel) and the first transaction (T1) is rolled back after the
second transaction (T2) has already accessed the uncommitted data—thus
violating the isolation property of transactions.
 It is also known as dirty read problems
 To illustrate that possibility, let’s use the same transactions described
during the lost updates discussion.
P a g e 4 | 17
 T1 has two atomic parts to it, one of which is the update of the inventory,
the other possibly being the update of the invoice total (not shown).
 T1 is forced to roll back due to an error during the updating of the invoice’s
total; hence, it rolls back all the way, undoing the inventory update as well.
This time, the T1 transaction is rolled back to eliminate the addition of the
100 units. Because T2 subtracts 30 from the original 35 units, the correct
answer should be 5.
Example
Transaction Operation
T1: Purchase 100 units PROD_QOH = PROD_QOH + 100 (Rolled back)
T2: Sell 30 units PROD_QOH = PROD_QOH – 30
The following Table shows how, under normal circumstances, the serial execution of
those transactions yields the correct answer.

The following Table shows how the uncommitted data problem can arise when the
ROLLBACK is completed after T2 has begun its execution.

3. Inconsistent Retrievals:
 Inconsistent retrievals, also known as "non-repeatable reads" or "fuzzy reads,"
can occur in a database when one transaction reads a piece of data, and before
it completes, another transaction modifies or deletes that data.
 This situation can lead to inconsistencies in the retrieved data, and it highlights
the challenges of maintaining isolation between concurrent transactions.

P a g e 5 | 17
Example:

Consider two transactions, TX and TY, performing the read/write

operations on account A, having an available balance = $300. The diagram
is shown below:

o At time t1, transaction TX reads the value from account A, i.e., $300.
o At time t2, transaction TY reads the value from account A, i.e., $300.
o At time t3, transaction TY updates the value of account A by adding $100 to the
available balance, and then it becomes $400.
o At time t4, transaction TY writes the updated value, i.e., $400.
o After that, at time t5, transaction TX reads the available value of account A, and
that will be read as $400.
o It means that within the same transaction T X, it reads two different values of
account A, i.e., $ 300 initially, and after updating made by transaction TY, it reads
$400. It is an unrepeatable read and is therefore known as the Unrepeatable read
problem.

Thus, in order to maintain consistency in the database and avoid such problems that
take place in concurrent execution, management is needed, and that is where the
concept of Concurrency Control comes into role.

Que 3: Explain Lock granularity.

P a g e 6 | 17
 Lock granularity indicates the level of lock use. Locking can take place at the
following levels:
o Database Level
o Table Level
o Page Level
o Row Level or even field (attribute).
1. Database Level
 In a database-level lock, the entire database is locked, thus preventing the use of any
tables in the database by transaction T2 while transaction Tl is being executed.
 This level of locking is good for batch processes, but it is unsuitable for multiuser
DBMSs.
 You can imagine how s-l-o-w the data access would be if thousands of transactions had
to wait for the previous transaction to be completed before the next one could reserve
the entire database.
 Following Figure illustrates the database-level lock. Note that because of the database-
level lock, transactions T1 and T2 cannot access the same database concurrently even
when they use different tables.

2. Table Level

P a g e 7 | 17
 Table Locking is mainly used to solve concurrency problems. It will be used while
running a transaction, i.e., first read a value from a table (database) and then write
it into the table (database).
 Figure illustrates the effect of a table-level lock. Note that in Figure transactions T1
and T2 cannot access the same table even when they try to use different rows; T2
must wait until T1 unlocks the table.

3. Page Level Lock

 In a page-level lock, the DBMS will lock an entire disk page. A disk page, or page, is
the equivalent of a disk block, which can be described as a directly addressable section
of a disk.
 A page has a fixed size, such as 4K, 8K, or 16K.
For example, if you want to write only 73 bytes to a 4K page, the entire 4K page must
be read from disk, updated in memory, and written back to disk.
 A table can span several pages, and a page can contain several rows of one or more
tables.
 Page-level locks are currently the most frequently used multiuser DBMS locking
method.
An example of a page-level lock is shown in Figure Note that T1 and T2 access the
same table while locking different disk pages.
If T2 requires the use of a row located on a page that is locked by T1, T2 must wait
until the page is unlocked by T1

P a g e 8 | 17
4. Row Level Lock
 A row-level lock is much less restrictive than the locks discussed earlier.
 The DBMS allows concurrent transactions to access different rows of the same
table even when the rows are located on the same page. Although the row-
level locking approach improves the availability of data, its management
requires high overhead because a lock exists for each row in a table of the
database involved in a conflicting transaction.
 Modern DBMSs automatically escalate a lock from a row-level to a page-level
lock when the application session requests multiple locks on the same page.
Figure illustrates the use of a row-level lock.

Que 3: Explain Lock Types

1. Binary Lock
 A binary lock, also known as a 1/0 lock or simple lock, is the simplest form of lock
used in database management systems (DBMS) for concurrency control.
 A binary lock has two states: locked (1) or unlocked (0). When a transaction acquires
a lock on a resource, the lock is set to 1, indicating that the resource is currently in
use and other transactions should be prevented from accessing it.
 When a transaction wants to access a particular resource, it checks the state of the
corresponding binary lock.

P a g e 9 | 17
 If the lock is set to 0 (unlocked), the transaction can acquire the lock (set it to 1) and
proceed with its operation.
 If the lock is set to 1 (locked), the transaction must wait until the lock is released by
the transaction that currently holds it.

Merits of Binary Locks :

 They are simple to implement since they are effectively mutually exclusive and
establish isolation perfectly.
 Binary Locks demand less from the system since the system must only keep a
record of the locked items. The system is the lock manager subsystem which is a
feature of all DBMSs today.

Drawbacks of Binary Locks :

 Binary locks are highly restrictive.

 They do not even permit reading of the contents of item X. As a result, they are
not used commercially.

2. Shared Lock

 It is also known as a Read-only lock. In a shared lock, the data item can only read
by the transaction.
 It can be shared between the transactions because when the transaction holds a
lock, then it can't update the data on the data item.

Example: consider a case where initially A=100 and there are two transactions which
are reading A. If one of transaction wants to update A, in that case other transaction
would be reading wrong value. However, Shared lock prevents it from updating until
it has finished reading.

3. Exclusive lock

 In the exclusive lock, the data item can be both reads as well as written by the
transaction.
 This lock is exclusive, and in this lock, multiple transactions do not modify the
same data simultaneously.

Example: consider a case where initially A=100 when a transaction needs to deduct
50 from A. We can allow this transaction by placing X lock on it. Therefore, when the
any other transaction wants to read or write, exclusive lock prevent it.

Compatibility matrix for locks

P a g e 10 | 17
 If the transaction T1 is holding a shared lock in data item A, then the control
manager can grant the shared lock to transaction T2 as compatibility is TRUE,
but it cannot grant the exclusive lock as compatibility is FALSE.
 In simple words if transaction T1 is reading a data item A, then same data
item A can be read by another transaction T2 but cannot be written by
another transaction.
 Similarly if an exclusive lock (i.e. lock for read and write operations) is hold
on the data item in some transaction then no other transaction can acquire
Shared or Exclusive lock as the compatibility function denoted FALSE.

Difference between Shared Lock and Exclusive Lock :

S.No. Shared Lock Exclusive Lock

Lock mode is read as well as write

1. Lock mode is read only operation. operation.

Shared lock can be placed on objects Exclusive lock can only be placed on
that do not have an exclusive lock objects that do not have any other
2. already placed on them. kind of lock.

Prevents others from reading or

3. Prevents others from updating the data. updating the data.

Issued when transaction wants to read Issued when transaction wants to

4. item that do not have an exclusive lock. update unlocked item.

Any number of transaction can hold Exclusive lock can be hold by only
5. shared lock on an item. one transaction.

S-lock is requested using lock-S X-lock is requested using lock-X

6. instruction. instruction.

Example: Multiple transactions reading Example: Transaction updating a

7. the same data table row

Que 4: Explain Two Phase Locking

 A transaction is said to follow the Two-Phase Locking protocol if Locking and

Unlocking can be done in two phases.
P a g e 11 | 17
There are two phases of 2PL:

 Growing phase: In the growing phase, a new lock on the data item may be acquired
by the transaction, but none can be released.
 Shrinking phase: In the shrinking phase, existing lock held by the transaction may
be released, but no new locks can be acquired.
 Lock Point: The Point at which the growing phase ends, i.e., when a transaction
takes the final lock it needs to carry on its work.

Example

The following way shows how unlocking and locking work with 2-PL

Transaction T1:

o Growing phase: from step 1-3

o Shrinking phase: from step 5-7
o Lock point: at 3

P a g e 12 | 17
Transaction T2:

o Growing phase: from step 2-6

o Shrinking phase: from step 8-9
o Lock point: at 6

Que 5: Explain deadlock in concurrency control with locking methods.

 A deadlock is a condition where two or more transactions are waiting indefinitely for
one another to give up locks.
 Deadlock is said to be one of the most feared complications in DBMS as no task ever
gets finished and is in waiting state forever.

Example:

 In the student table, transaction T1 holds a lock on some rows and needs to update some
rows in the grade table. Simultaneously, transaction T2 holds locks on some rows in the grade
table and needs to update the rows in the Student table held by Transaction T1.
 Now, the main problem arises. Now Transaction T1 is waiting for T2 to release its lock
and similarly, transaction T2 is waiting for T1 to release its lock. All activities come to
a halt state and remain at a standstill. It will remain in a standstill until the DBMS
detects the deadlock and aborts one of the transactions.

 The three basic techniques to control deadlocks are:

1. Deadlock Prevention

 Deadlock prevention method is suitable for a large database. If the resources are
allocated in such a way that deadlock never occurs, then the deadlock can be
prevented.

P a g e 13 | 17
 The Database management system analyses the operations of the transaction
whether they can create a deadlock situation or not. If they do, then the DBMS never
allowed that transaction to be executed.

2. Deadlock Detection

 The DBMS periodically tests the database for deadlocks. If a deadlock is found, one
of the transactions (the “victim”) is aborted (rolled back and restarted) and the other
transaction continues.

3. Deadlock avoidance

 When a database is stuck in a deadlock state, then it is better to avoid the database
rather than aborting or restating the database. This is a waste of time and resource.
 Deadlock avoidance mechanism is used to detect any deadlock situation in advance.
A method like "wait for graph" is used for detecting the deadlock situation but this
method is suitable only for the smaller database. For the larger database, deadlock
prevention method can be used.

Que 5: Explain concurrency control with time stamping methods.

 Timestamp is a unique identifier created by the DBMS to identify the relative starting
time of a transaction.
 Typically, timestamp values are assigned in the order in which the transactions are
submitted to the system. So, a timestamp can be thought of as the transaction start
time.
 Timestamps must have two properties: uniqueness and monotonicity.
 Uniqueness ensures that no equal time stamp values can exist
 Monotonicity ensures that time stamp values always increase.

Wait-Die scheme

In this scheme, if a transaction requests for a resource which is already held with a
conflicting lock by another transaction then the DBMS simply checks the timestamp of
both transactions. It allows the older transaction to wait until the resource is available
for execution.

Let's assume there are two transactions Ti and Tj and let TS(T) is a timestamp of any
transaction T. If T2 holds a lock by some other transaction and T1 is requesting for
resources held by T2 then the following actions are performed by DBMS:

1. Check if TS(Ti) < TS(Tj) - If Ti is the older transaction and Tj has held some
resource, then Ti is allowed to wait until the data-item is available for execution.
That means if the older transaction is waiting for a resource which is locked by

P a g e 14 | 17
the younger transaction, then the older transaction is allowed to wait for resource
until it is available.
2. Check if TS(Ti) < TS(Tj) - If Ti is older transaction and has held some resource and
if Tj is waiting for it, then Tj is killed and restarted later with the random delay but
with the same timestamp.

Wound wait scheme

o In wound wait scheme, if the older transaction requests for a resource which is
held by the younger transaction, then older transaction forces younger one to kill
the transaction and release the resource. After the minute delay, the younger
transaction is restarted but with the same timestamp.
o If the older transaction has held a resource which is requested by the Younger
transaction, then the younger transaction is asked to wait until older releases it.

Wait – Die Wound -Wait

It is based on a preemptive
It is based on a non-preemptive technique.
technique.

In this, older transactions must wait for the In this, older transactions never
younger one to release its data items. wait for younger transactions.

The number of aborts and rollbacks is In this, the number of aborts and
higher in these techniques. rollback is lesser.

Que 6: Explain concurrency control with time optimistic object.

 The optimistic approach requires neither locking nor time stamping techniques.
Instead, a transaction is executed without restrictions until it is committed.
 Using an optimistic approach, each transaction moves through two or three phases,
referred to as read, validation, and write.
 During the read phase, the transaction reads the database, executes the needed
computations, and makes the updates to a private copy of the database values. All
update operations of the transaction are recorded in a temporary update file, which
is not accessed by the remaining transactions.
 During the validation phase, the transaction is validated to ensure that the changes
made will not affect the integrity and consistency of the database. If the validation
test is positive, the transaction goes to the write phase. If the validation test is
negative, the transaction is restarted and the changes are discarded.
 During the write phase, the changes are permanently applied to the database.

P a g e 15 | 17
Que 7: Explain Database Recovery Management.
 Database recovery management is a crucial aspect of database systems, ensuring the
strength and consistency of data in the face of failures such as system crashes or
hardware faults.
 Recovery mechanisms help bring a database back to a consistent state after a failure
and preserve the integrity of the data.
 Following are components and concepts related to database recovery management:

1.Transaction Logging:

 Purpose: Transaction logging is a fundamental technique for ensuring recoverability.

It involves maintaining a log of all changes made to the database during transactions.
 Process: Before a transaction modifies data, the database management system
(DBMS) writes a log entry describing the change to a log file. The log file is usually
stored on a different disk or system to ensure availability even in the case of a disk
failure.
2.Write-Ahead Logging (WAL):

 Principle: A common practice in transaction logging is the "write-ahead logging"

principle, where changes are recorded in the log before they are applied to the actual
database.
 Advantage: This ensures that the log reflects the state of the database before any
changes, making it possible to reconstruct the database to a consistent state in case
of a failure.
3.Checkpointing:

 Purpose: Checkpointing is a mechanism to create a point-in-time snapshot of the

database state, allowing for faster recovery.
 Process: Periodically, the DBMS writes a checkpoint record to the log, indicating that
all changes up to that point have been flushed to the database. This reduces the
amount of log that needs to be processed during recovery.
4.Recovery Process:

 Analysis Phase: After a system crash, the DBMS performs an analysis phase where it
examines the log to determine which transactions were active and which were
committed at the time of the crash.
 Redo Phase: In the redo phase, the DBMS applies changes recorded in the log to the
database, bringing it to a consistent state. This involves reapplying committed
transactions and ensuring that all changes are reflected in the database.
 Undo Phase: If necessary, the DBMS performs an undo phase to roll back
transactions that were active but not committed at the time of the crash. This ensures
that uncommitted changes are not applied.
5.Transaction Atomicity, Consistency, Isolation, and Durability (ACID)
Properties:
P a g e 16 | 17
 Role: Recovery management is closely tied to the ACID properties of transactions.
 Atomicity: Transactions are atomic, and recovery mechanisms ensure that
transactions are either fully applied or fully undone in case of a failure.
 Consistency: Recovery ensures that the database returns to a consistent state,
reflecting the state before the failure.
 Isolation: Transactions that were uncommitted at the time of the failure are rolled
back to maintain isolation.
 Durability: Durability is achieved by persistently storing transaction logs and
ensuring that changes are applied to the database in a way that can be recovered.

P a g e 17 | 17
Unit 3:
Distributed Database Management System
S 1: Evolution of DDBMS
Que
 A distributed database management system (DDBMS) directions the storage and
processing of logically related data over interconnected computer systems in which
both data and processing are distributed among several sites.
 The use of a centralized database required that corporate data be stored in a single
central site, usually a mainframe computer.
 Data access was provided through dumb terminals. The centralized approach worked
well to fill the structured information needs of corporations, but it fell short when
quickly moving events required faster response times and equally quick access to
information. The slow progression from information request to approval to specialist
to user simply did not serve decision makers well in a dynamic environment.

The factors influenced the evolution of the DDBMS:

The different factors influenced the evolution of the DDBMS are as follows.
1. Growing Data Volumes
The increasing volume of data generated by organizations and applications required
more scalable and efficient solutions.
2. Advancements in Networking Technologies
Improvements in networking technologies, such as faster and more reliable
communication protocols, have played a crucial role.
3. Globalization and Geographically Dispersed Operations:
As businesses expanded globally and adopted geographically dispersed operations,
the need for data access across different locations became critical. DDBMS allows
organizations to store and manage data in a distributed manner, enabling users in
different locations to access and update information.
4. Demand for High Availability and Fault Tolerance:
DDBMS provides mechanisms for data replication and distribution, ensuring that if one
part of the system fails, data remains accessible from other nodes.
5. Cost Efficiency and Resource Utilization:
Distributed databases offer better resource utilization by distributing data and
processing across multiple servers.

6. Advancements in Distributed Computing:

P a g e 1 | 10
The evolution of distributed computing paradigms, including the development of
distributed computing models and frameworks, has influenced DDBMS. Technologies
like Apache Hadoop, Apache Spark, and others have provided tools and frameworks
for distributed data processing, complementing the capabilities of DDBMS.
7. Rise of Cloud Computing:
Cloud-based databases power distributed infrastructure, allowing businesses to scale
resources dynamically and pay for what they use. This has influenced the design and
deployment of DDBMS in the cloud environment.
8. Increased Emphasis on Data Security and Privacy:
Concerns about data security and privacy have grown, prompting the development of
distributed solutions that incorporate robust security measures. DDBMS often include
features such as encryption, access controls, and authentication mechanisms to
address these concerns.
Que 2: Distributed Processing and Distributed Database
 In distributed processing, a database’s logical processing is shared among two or more
physically independent sites that are connected through a network.
 For example, the data input/output (I/O), data selection, and data validation might be
performed on one computer, and a report based on that data might be created on
another computer.
 A basic distributed processing environment is illustrated in Figure 12.2, which shows
that a distributed processing system shares the database processing chores among
three sites connected through a communications network. Although the database
resides at only one site (Miami), each site can access the data and update the database.
 The database is located on Computer A, a network computer known as the database
server.

P a g e 2 | 10
 A distributed database, on the other hand, stores a logically related database over two
or more physically independent sites. The sites are connected via a computer network.
 In contrast, the distributed processing system uses only a single-site database but
shares the processing chores among several sites. In a distributed database system, a
database is composed of several parts known as database fragments.
 The database fragments are located at different sites and can be replicated among
various sites. Each database fragment is, in turn, managed by its local database process.
An example of a distributed database environment is shown in Figure 12.3.

 The database in Figure 12.3 is divided into three database fragments (E1, E2, and E3)
located at different sites. The computers are connected through a network system. In a
fully distributed database, the users Alan, Betty, and Hernando do not need to know the
name or location of each database fragment in order to access the database.
 Also, the users might be located at sites other than Miami, New York, or Atlanta and still
be able to access the database as a single logical unit.
 As you examine Figures 12.2 and 12.3, you should keep the following points in mind:
o Distributed processing does not require a distributed database, but a distributed
database requires distributed processing (each database fragment is managed
by its own local database process).
o Distributed processing may be based on a single database located on a single
computer. For the management of distributed data to occur, copies or parts of
the database processing functions must be distributed to all data storage sites.

P a g e 3 | 10
Both distributed processing and distributed databases require a network to connect all
components.
Que 3: Explain Levels of Data and Process Distribution

 Current database systems can be classified on the basis of how process

distribution and data distribution are supported.
 For example, a DBMS may store data in a single site (centralized DB) or in
multiple sites (distributed DB) and may
 support data processing at a single site or at multiple sites. Table 12.2 uses a
simple matrix to classify database systems
 according to data and process distribution. These types of processes are
discussed in the sections that follow.

1. Single-Site Processing, Single-Site Data (SPSD):

 The simplest model.
 Both data and processing are located at a single site.
 No distribution involved.
 Example: A small database running on a single computer.

2. Multiple-Site Processing, Single-Site Data (MPSD):

 Data is stored at a single site, but processing is distributed across multiple
sites.
P a g e 4 | 10
 Often used to improve performance or provide local access to data.
 Example: A web application with a central database server and multiple web
servers.

3. Multiple-Site Processing, Multiple-Site Data (MPMD):

 The most complex model.
 Both data and processing are distributed across multiple sites.
 Offers advantages in scalability, availability, and fault tolerance.
 Example: A global banking system with databases in different countries.

Benefits of distributing data and processing:

 Improved Performance: Distribute workload across multiple sites, reducing
response times.
 Enhanced Availability: Data and processing remain accessible even if one site
fails.
 Increased Reliability: Data replication across sites safeguards against data loss.
 Better Scalability: Ease of adding new sites to accommodate growth.
 Improved Local Autonomy: Local sites can manage their data and processes
independently.
Challenges of distributed systems:
 Complexity: Managing distributed systems is more complex than centralized
ones.

P a g e 5 | 10
 Communication Overhead: Data exchange between sites can increase network
traffic.
 Data Consistency: Ensuring data consistency across multiple sites requires
careful synchronization.
 Security: Distributed systems can introduce additional security vulnerabilities.

Que 4 : Explain Distributed Database Transparency Features.

 Transparency, in the context of distributed databases, refers to the ability of a distributed
database management system (DDBMS) to hide the complexity of its distributed
architecture from users and applications.
The DDBMS transparency features are:

1. Distribution transparency
 which allows a distributed database to be treated as a single logical database.
 If a DDBMS displays distribution transparency, the user does not need to know
 That the data are partitioned—meaning the table’s rows and columns are
split vertically or horizontally and stored among multiple sites.
 That the data can be replicated at several sites.
 The data location

2. Transaction transparency
 which allows a transaction to update data at more than one network site

3. Failure transparency

 Users should not be affected by the failure of individual nodes or components.

 The system should handle failures, whether it's a node going down or a network partition,
transparently and continue to provide a consistent view of the data.
 After a failure occurs, the system should recover and restore consistency without
requiring manual intervention from users.

4. Performance Transparency

 Aims to provide performance similar to a centralized database, even in a distributed

environment.

5. Heterogeneity (collection) transparency

 Allows integration of different database systems and platforms within a distributed
database

Que 5: Explain Distributed Transparency .

 Distribution transparency is the property of distributed databases by the virtue of which
the internal details of the distribution are hidden from the users.

P a g e 6 | 10
 The DDBMS designer may choose to fragment tables, replicate the fragments and store
them at different sites.
 However, since users are oblivious of these details, they find the distributed database
easy to use like any centralized database.

 The three dimensions of distribution transparency are

Location Transparency:
o Users and applications don't need to know the physical location of data.
o They can access data as if it were all stored locally.
Fragmentation Transparency:
o Users and applications don't need to know how tables are fragmented or
distributed across sites.
o They can interact with the database as if it were a single, non-fragmented unit.
Replication Transparency:
o Users and applications are unaware of data duplication across multiple sites.
o They see a single, consistent view of the data, regardless of where it's physically
stored.
Que 6: Explain Transaction Transparency .
Transaction transparency is a DDBMS property that ensures that database transactions will
maintain the distributed database’s integrity and consistency
To understand how the transactions are managed, you should know the basic concepts
governing remote requests, remote transactions, distributed transactions, and distributed
requests which are as follow
1. Distributed Request and distributed transaction.
Distributed Transaction
 A distributed transaction is like a series of steps that need to be done together
successfully. Imagine you're transferring money between two banks.
 The transaction involves deducting money from one account and adding it to another.
 A distributed transaction ensures that either both these steps happen successfully or
neither of them does.
 It helps maintain consistency and integrity in complex operations involving multiple
systems.
 distributed transaction is about ensuring that a series of steps in a task either all succeed
or all fail together.
P a g e 7 | 10
Distributed Request
 A distributed request is like breaking down a big task into smaller parts and asking different
computers (or servers) to handle those parts.
 Each computer does its job, and together they complete the entire task. It's a way to share
the workload among multiple machines.
 distributed request is about dividing work among different computers.

2. Distributed Currency control

 In a database management system (DBMS), when we talk about "distributed currency
control," we mean keeping track of changes to the data in a database when many computers
or locations are involved.
P a g e 8 | 10
 It's like making sure that if multiple people are updating the same piece of information at
the same time, we don't end up with a confusing or incorrect result.

For example
 Adding Money (Updating Data): Each person (or computer) wants to add money to the
piggy bank (update the database). Distributed currency control ensures that if two people
try to add money at the same time, the system makes sure it's done in a way that everyone
agrees on the correct total.
 Taking Money Out (Reading Data): Similarly, when someone wants to see how much
money is in the piggy bank (read data from the database), the system ensures that they get
the most up-to-date and accurate information, even if others are making changes.

So, in the world of databases, distributed currency control helps prevent confusion and
mistakes when multiple computers are working together on updating and accessing data. It's
like making sure everyone agrees on the current state of the piggy bank, no matter how many
people are contributing or checking the balance.

3. Two Phase commit protocol

 Used to maintain consistency and atomicity of transactions that span multiple
databases within a distributed database system.
 Ensures that all participating databases either commit or abort the transaction
together, even in the event of failures.
Phases
1. Prepare Phase
Transaction Manager sends a "prepare" message to all participating databases.
Each database:
a. Checks if it can commit the transaction locally.
b. If able to commit, records a "prepared" state in its log, acquires locks on
necessary resources, and responds "Yes" to the Transaction Manager.
c. If unable to commit, releases resources and responds "No" to the Transaction
Manager.
2. Commit Phase:
a. If all databases respond "Yes":
i. Transaction Manager sends a "commit" message to all databases.
ii. Each database commits the transaction locally and releases locks.
b. If any database responds "No":
i. Transaction Manager sends an "abort" message to all databases.
ii. Each database rolls back the transaction and releases locks.
Que 7 : Explain performance transparency and query optimization .
Performance Transparency:
 Performance transparency in a database system refers to the ability of the system to
hide the complexity of its internal operations from the end-users and applications
while providing consistent and efficient performance.

P a g e 9 | 10
 It is the ability of a distributed database management system (DDBMS) to provide
users with information about the performance of the system.
 This information can include the response time of the system, the number of
transactions processed, and the number of queries executed.
 Performance transparency is important because it helps users to understand how
the system is performing the system.
Query optimization
 It is the process of improving the performance of database queries.
 It involves analyzing the query execution plan, identifying inefficiencies, and re-writing
the query to improve performance.
 This can involve techniques such as indexing, caching, and partitioning.
Example
Suppose you have a table with a million rows, and you want to retrieve all the rows where
the value of a particular column is greater than 100. A naive approach would be to scan
the entire table and check each row for the condition. This would be very slow. A better
approach would be to create an index on the column, which would allow the database to
quickly find the relevant rows without scanning the entire table.

P a g e 10 | 10
Data types in SQL:
Numeric:
Short int, int, long int, number(p,s), decimal (p,s), float, real

Character:

Char – Stores characters. Max. size is upto 2000. It is using static memory
allocation.

Ename char(50)

varchar(size) – Variable character. Max. size is upto 2000. It is using dynamic

memory allocation.

Ename varchar(50)

varchar2(size) – Same as varchar. Max. size is upto 2GB.

varchar2(max)

nvarchar(size) – Nationalized variable character. Every character will occupy 2

bytes of memory.

nvarchar2(size)
nvarchar2(max),

long – It stores character data. When we have to store large character data like
books, stories, dictioneries etc. Max. size is upto 2GB. We cannot create index
on such field. You can create only 1 column of long type in a table.

Date – It can store date and time. Default date format is dd-mon-yy (Ex: 05-jan-
22). Time format is HH:MM:SS (24 hour format)

LOBS (Large objects) data types : BLOB, CLOB, BFILE.

BLOB : Binary Large Objects. (Ex: Audio, Video etc). Its size is upto 2GB. Per
table only 1 column of such type allowed. Indexing on such column not
possible.
CLOB: Character Large Objects. It is same as long.

BFILE: It stores path of binary data upto 2 GB.

RAW : It stores binary data upto 255 bytes.

Long RAW : It stores binary data upto 2 GB.

SELECT SQL STATEMENT

It is used to read / retrive the data from a table / tables as per the criteria
specified.

Syntax:

SELECT COLUMN-NAME, COLUMN,NAME … | *

FROM TABLE-LIST SEPARATED BY COMMA,
[WHERE CONDITION]
[ORDER BY COLUMN-NAME [DESC],COLUMN-NAME [DESC]..]
[GROUP BY COLUMN-NAME [HAVING GROUP-CONDITION]] ;

Ex:

Select * from employee ;

select deptno, dname from dept ;

select deptno "department no.",dname "department name" from dept ;

select empno,ename,salary,salary*0.10 "Bonus" from employee

select ename || ',' || desig "Basic details" from employee

Relational operators:

< , <= , =, >, >=, <>

Logical operators:

And, or, not

Ex:

Display all employees who are working in department no. 10 and 20.

select * from employee where deptno=10 or deptno = 20 ;

Display empno, ename of all employees who are earning 10000 to 25000.

Select empno,ename from employee where salary >= 10000 and salary <=
25000
OR
Select empno,ename from employee where salary between 10000 and 25000

Special operators :

Like:
When we use wildcard characters in a condition, we have to use Like operator.
% : to ignore many characters
_ (underscore) : to ignore a single character.

Ex:
Display empno, ename and salary of all employees whose name begins with a.

Select empno, ename, salary from employee where ename like ‘a%’;

Display all employees whose name has ‘i’ as a last character.

Select empno,ename from employee where ename like ‘%i’;

Display all employees whose name contains at least 2 i.

Select empno, ename from employee where ename like ‘%i%i%’;

Display all employees whose name contains max. 4 characters.

Select empno,ename from employee where ename like ‘____’;

Display all employees whose name’s second character is a.

Select empno, ename from employee where ename like ‘_a%’;

Between….and : It is used to specify a range of values

Between lower-value and upper-value
In

It is used to specify a list of values in a condition.

Ex:

Display all employees who works in department no. 10,20 or 40.

Select empno,ename from employee where deptno=10 or deptno=20 or
deptno=40
OR
Select empno,ename from employees where deptno in (10,20,40)

Display all employees who are developer or programmer

Select * from employee where desig in (‘Programmer’,’Developer’);

Display all employees who are earning either 30000, 40000 or 50000 salary
Select * from employee where salary in (30000,40000,50000);

Is null

When we have to compare a field value with null

Display all employees whose ename is null

Select * from employee where ename is null;

Exists

ORDER BY CLAUSE:

 Order by clause can be used with select sql statement

 It is used to display data in either ascending order or in descending order
as per a particular column
 Default order is ascending order

ORDER BY column-name [DESC],…

Ex:

Display empno, ename, salary of all employees with highest salary record
should come first.
Select empno, ename, salary
From employee
Order by salary;

select empno, ename, desig, salary

from employee
order by desig,salary desc

GROUP BY :

 To create groups of records based on a column or columns, this clause is

used with select-sql statement
 When we use group by, only group by column or aggregate function can
come in column-list.

Ex:

Display all designations.

Select desig
From employee
Group by desig;

AGGREGATE FUNCTIONS:

Sum( )
Avg( )
Count( )
Max( )
Min( )

Ex:

Display salary paid in each department.

Select deptno, sum(salary)

From employee
Group by deptno ;
How many employees are working in each department?

Select deptno, count(*)

From employee
Group by deptno;

List deptno, average salary paid in that department

Select deptno, avg(salary)

From employee
Group by deptno;

List deptno, max salary paid in that department, no. of employees working in
that department from employee table.

Select deptno, max(salary),count(*)

From employee
Group by deptno;

HAVING:

 It can be used with group by only.

 It is used to write conditions on groups
 Condition written in having is applicable to groups i.e. which group to
include in the output is decided by having.
 It is used to write condition on group by expression only

Ex:

Display no. of employees working in every department. Include only those

departments who are having more than 2 employees.

Select deptno,count(*)
From employee
Group by deptno having count(*) > 2 ;

Ex:

Display maximum salary paid to employees in each designation. Don’t include

managers in the list.
Select desig, max(salary)
From employee
Group by desig having desig not in (‘Manager’,’manager’);

Create table … as :

Create table table-name as select-sql statement ;

Ex:
Create a new table employee1 which includes details of all employees works in
deptno 10,20,30.

Create table employee1 as

Select * from employee where deptno in (10,20,30) ;

Exercise :

1. Display every department no. with average salary paid in that department.
Select deptno,avg(salary) from employee group by deptno
2. Increase programmmer salary by 15 percent in department 20.
Update employee
Set salary = salary + 0.15*salary
Where deptno=20 and desig=’programmer’ ;
3. Delete all employees where department no. is not given in employee table.
Delete from employee where deptno is null;
4. Update department no. with 50 where department number is not given.
Update employee
Set deptno=50 where deptno is null ;
5. List all employees whose names begins with vovel.
Select * from employee
Where ename like ‘a%’ or ename like ‘e%’ or ename like ‘i%’ or ename like
‘o%’ or ename like ‘u%’

SQL Functions:

 Are also identified as single row functions i.e. per row one result will be
produced.
1) Numeric functions
2) Character and string functions
3) Date functions
4) Conversion functions

To test these functions, we can use dual table which is a system table
consisting only one column and one row (i.e. only one cell)

1) Numeric functions:

Abs(value) : It returns a positive value for a given value.

Ex:
Select abs(-57) from dual ;
57

Ceil(value):

It returns next integer value for a given value.

Ex:
Select ceil(89.1) from dual;
90
Select ceil(125.7) from dual ;
126

Floor(value):

It removes fractional part from a value and returns integral part only.
Ex:
Select floor(35.7) from dual ;
35
Select floor(1220.1) from dual;
1220

Mod(val,divisor) :

It returns a reminder if we divide a val with divisor.

Ex:
Select mod(25,7) from dual ;
4
Select mod(50,17) from dual;
16
Power(x,n):

It returns x raise to n.
Ex:

Select power(5,3) from dual;

125

Round(val,no. of digits after decimal) :

It will round a value as per decimal places spefied in a function. If no. of digits
are not given, then it returns integral value by rounding it.
Ex:
Select round(65.39,1) from dual;
65.4
select round(99.444,2) from dual
99.44
select round(99.445,2) from dual
99.45
select round(99.512) from dual
100
select round(99.444) from dual
99

Trunc(val, no. of digits after decimal) :

 It is a truncate function.
 It will keep no. of digits specified after the decimal place and will remove
rest of the digits without changing a value. If no. of digits after decimal
point is not given then it will return only integral part of a given value.
Ex:
Select trunc(84.887,1) from dual ;
84.8
Select trunc(84.887) from dual ;
84

SQRT(val) :

It returns square root of a given value.

Ex:
Select sqrt(9) from dual;
3
Select sqrt(11) from dual;

Sign(value):

 If a given value is positive, it will return 1. If a given value is negative, it

returns -1. If a given value is zero, it will return 0.

Ex:
Select sign(-44.3) from dual;
-1
Select sign(5007) from dual;
1
Select sign(0) from dual;
0

String functions OR Character functions:

Ascii(character) :
It returns ascii code for a given character.
A-Z : 65-90
a-z : 97-122
0-9 : 48-57

Ex:
Select ascii(‘C’) from dual ;
67

Chr(ascii code):

It returns a character for given ascii code.

Ex:
Select chr(65) from dual;
A

Upper(string):

It returns given string in upper case

Ex:
Select upper(‘Cpica’) from dual;
CPICA
Select upper(‘vishal’) from dual;
VISHAL

Lower(string):

It returns given string into lower case.

Ex:
Select lower(‘CPICA’) from dual ;
cpica

Initcap(string)

It converts the given string into sentense case. i.e. first character of a word will
be in upper case and rest of all characters in lower case for every word.

Ex:
Select initcap(‘HELLO EVERYONE’) from dual;
Hello Everyone

Ltrim(string):

It removes unnecessary spaces from left hand side of a string.

Ex:
Select ltrim(‘ kite flying ‘) || ltrim(‘ festival ‘) from dual ;

Rtrim(string):

It removes additional unnecessary spaces from RHS of a string.

Ex:

Select rtrim(‘ kite flying ‘) || rtrim(‘ festival ‘) from dual ;

Trim(string):

It removes unnecessary speces from both side of a string.

Ex:
Select trim(‘ kite flying ‘) || trim(‘ festival ‘) from dual ;

Lpad(String):

Syntax:
Lpad(string,max. length required, padding character)

It adds additional characters on LHS of a string.

Ex:

Select lpad(‘Ahmedabad’,15,’*’) from dual;

******Ahmedabad

Rpad(string):

Syntax:
Rpad(string,max. length required, padding character)

It adds additional characters on RHS of a string.

Ex:

Select rpad(‘Ahmedabad’,15,’*’) from dual;

Ahmedabad******

Concat(string1,string2):

It concatenates two strings.

Syntax:
Concat(string1,string2)

Select concat(‘kite flying’,’festival’) from dual ;

Instr(string1,string2):

It searches string2 into string1. If found then it returns the position no. of
string2 into string1 otherwise it returns 0.

Ex:
Select instr(‘ahmedabad’,’ing’) from dual ;
Print all employees whose name contains p.
Select empno,ename from employee where instr(ename,’p’) > 0 ;

Substr(string, starting pos,len):

It returns portion of a string from given string. String portion begins at position
with length len will be returned.

Ex:
Select substr(‘good morning’,6,4) from dual;

Ex:
Display all employees whose name contains a as a 3rd character.

Select empno,ename from employee

Where substr(ename,3,1) = ‘a’;

Replace(string1,string2,string3):

It will replace string2 with string3 into string1.

Ex:
Select replace(‘Hi.. how are you?’,’are’,’were’) from dual ;
Hi.. how were you?

Date functions:

Sysdate :
It is a keyword in oracle. It returns today’s date.

Ex:
Select sysdate from dual ;

Extract :

It extracts and returns day, month or year from a given date.

Ex:
Select extract(year from sysdate) from dual;
Select extract(month from sysdate) from dual;
Select extract(day from sysdate) from dual;
List all employees who joined during 1st week of any month

select empno,ename,doj from employee

where extract(day from doj) between 1 and 7

List all employees who joined during 1st week of june

select empno,ename,doj from employee
where extract(day from doj) between 1 and 7 and extract(month from doj)=6

ADD_Months(date,no. of months):

It adds no. of months in a given date.

Ex:
Select add_months(sysdate,3) from dual ;

Last_day(date):

It returns last day of month date of given date.

Ex:

Select last_day(sysdate) from dual;

31-jan-22

Months_between(date1,date2):

It returns months difference between two dates given in a function.

Display all employees who joined duties during last 1.5 years.

Select empno,ename,doj
From employee
Where months_between(sysdate,doj) <= 18 ;

Next_day(date,day):

It returns a date of given day coming immediate next.

Ex:
Select next_day(sysdate,’friday’) from dual;
Display all employees who joined during current week of any year

select empno,ename,doj
from employee
where extract(month from doj)=1 and extract(day from doj) between
extract(day from next_day(sysdate,'saturday'))-6 and extract(day from
next_day(sysdate,'saturday'));

Trunc(date,’format’):

Ex:
Select trunc(sysdate,’month’) from dual ;
Select trunc(sysdate,’year’) from dual ;

Conversion functions

To_date(string,date-format):

To_date( ) function converts string into equivalent date value.

Ex:

Select to_date(‘22/03/18’,’dd/mm/yy’) from dual ;

To_char(date,format) :

It converts a date into string with given format.

Ex:
Select to_char(sysdate,’yyyy’) from dual ;
Select to_char(sysdate,’yy’) from dual ;
Select to_char(sysdate,’mm’) from dual ;
Select to_char(sysdate,’mon’) from dual ;
Select to_char(sysdate,’month’) from dual ;
Select to_char(sysdate,’dd’) from dual ;
Select to_char(sysdate,’day’) from dual ;
Select to_char(sysdate,’wday’) from dual ;

Display all employee who joined during month of march

select empno,ename,doj from employee
where to_char(doj,'mm')=3

to_number(string,format):

It converts a string into equivalent numeric value.

Ex:
Select to_number(‘1,05,325’,’9,99,999’) from dual;

Set Operators :

 Union
 Union all
 Intersect
 Minus

Union:

It is used to combine results of two queries.

In both the queries, the column-list must be same. It is not necessary to have
same column name but must have similar data type and data.

Syntax:

Select-sql statement 1
Union
Select-sql statement2
Ex:
Select deptno from employee
union
Select deptno from dept ;

select * from employee2018

union
select * from employee2019 ;

Note: Union operator eliminates duplicate values from both satatement. I.e.
duplicate values will not be repeated.
Union all :

It is same as union with only one difference – it adds duplicate values as well in
the result.

Syntax:

Select-sql statement1
Union all
Select-sql statement2

Ex:
Select deptno from employee
Union all
Select deptno from dept ;

Intersect :

It includes only those data which is common in multiple queries.

Syntax:

Select-sql statement1
Intersect
Select-sql statement2 ;

Ex:

Minus:

It includes only those data which is present in 1st select-sql output list but
absent in 2nd select-sql output list.

Syntax:

Select-sql statement1
Minus
Select-sql statement2 ;
Ex:

Select * from employee

Minus
Select * from employee2018 ;

SQL JOINS:

 To extract the data from 2 or more tables by relating them based on a

common column.

 Cross join
 Inner join
o Old styled join
o Natural join
o Join….using
o Join….on
 Outer join
o Left outer join
o Right outer join
o Full join

Cross join:

 It is used to generate cartesian product of 2 tables.

 For ex. There are 5 rows in table1 and 6 rows in table2. So, the result will
be 30 rows. Each row of table1 will connect with every row of table2.
 Any common column is not required to implement cross join.

Syntax:

Select column-list from table1 cross join table2

Ex:
select empno,ename,d.deptno,dname from employee e cross join dept d ;

Inner joins

Old styled join:

Old styled join connects 2 or more tables based on a common column exist in
all tables.

Syntax:

Select column-list
From table-list
Where join-conditon and other conditions;

Ex:

Display all employee no., ename, dept no and department name.

select empno,ename,e.deptno,dname
from employee e,dept d
where e.deptno = d.deptno

Display empno, ename, desig and salary of all employees working in

production department.

Select empno, ename, desig, salary

From employee e, dept d
Where e.deptno = d.deptno and dname=’production’;

Display average salary paid to every employee in Admin department.

Select avg(salary)
From employee e, dept d
Where e.deptno = d.deptno and dname=’Admin’;

Display department name and average salary in each department.

Select dname,avg(salary)
From employee e, dept d
Where e.deptno=d.deptno
Group by dname;