Practical File

of
Database Management System

Submitted To: Submitted By:

Ms. Dolly
Assistant Professor
DCSA

DEPARTMENT OF
COMPUTER SCIENCE AND ENGINEERING
GALAXY GLOBAL GROUP OF INSTITUTIONS, AMBALA
 INDEX

S.NO PRACTICALS SIGNATUR

E
1. Write An Introduction Of SQL In Brief

2. Write The Data Types In SQL.

What is SQL? Write the queries for Data Definition
3. Language (DDL) in RDBMS.

4. Commands of DDL (Update, Delete, Rename,

Truncate).
Write the queries for DML (Data Manipulation Language)
5.
in RDBMS

Create a database and perform the Like predicate for

6. pattern matching in database.

Write SQL queries for the aggregation and relational

7. algebra.

8. Write SQL queries for extracting data from more than one
table.

9. Concepts for ROLL BACK, COMMIT & CHECK

POINTS.

Write SQL queries on the customer table whose

output will exclude all customers with a rating
10 greater than equal to 100 unless they are located
in Shimla.

11. Write date and time functions in SQL

Create a VIEW to display Employees Name and their

12. Projects
 Practical-1
AIM - Write an Introduction of SQL in brief.

SQL (Structured Query Language) is a language to operate databases; it includes Database

Creation, Database Deletion, Fetching Data Rows, Modifying & Deleting Data rows, etc.
SQL stands for Structured Query Language which is a computer language for storing,
manipulating and retrieving data stored in a relational database. SQL was developed in the
1970s by IBM Computer Scientists and became a standard of the American National
Standards Institute (ANSI) in 1986, and the International Organization for Standardization
(ISO) in 1987.

Why SQL?

Nowadays, SQL is widely used in data science and analytics. Following are the reasons which
explain why it is widely used:

 Allows users to access data in the relational database management systems.

 Allows users to describe the data.
 Allows users to define the data in a database and manipulate that data.
 Allows embedding within other languages using SQL modules, libraries & pre-
compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
 Allows users to set permissions on tables, procedures and views.

History of SQL

 1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases.
He described a relational model for databases.
 1974 − Structured Query Language (SQL) appeared.
 1978 − IBM worked to develop Codd's ideas and released a product named System/R.
 1986 − IBM developed the first prototype of relational database and standardized by
ANSI. The first relational database was released by Relational Software which later
came to be known as Oracle.
 1987 − SQL became the part of the International Organization for Standardization
(ISO).

Working of SQL:
When you are executing an SQL command for any RDBMS, the system determines the best
way to carry out your request and SQL engine figures out how to interpret the task.
There are various components included in this process. These components are −
 Query Dispatcher
 Optimization Engines
 Classic Query Engine
 SQL Query Engine, etc.
A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle
logical files. Following is a simple diagram showing the SQL Architecture −
 Practical-2
AIM - Write the Data Types in SQL?

Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each column when
creating a table. The data type is a guideline for SQL to understand what type of data is
expected inside of each column, and it also identifies how SQL will interact with the stored
data.

For every database, data types are primarily classified into three categories.
 Numeric Datatypes
 Date and Time Database
 String Database

Numeric Data Types in SQL

Exact Numeric data Types

There are nine subtypes which are given below in the table. The table contains the range of
data in a particular type.
Data Type From To

-263 (- 263 -1
bigint
9,223,372,036,854,775,808) (9,223,372,036,854,775,807)

int -231 (-2,147,483,648) 231-1 (2,147,483,647)

smallint -215 (-32,768) 215-1 (32,767)

tinyint 0 28-1 (255)

bit 0 1

decimal -1038+1 1038-1

numeric -1038+1 1038-1

money -922,337,203,685,477.5808 922,337,203,685,477.5807

smallmoney -214,748.3648 214,748.3647

Approximate Numeric Datatype

The subtypes of this datatype are given in the table with the range.
 Data Type From To

Float -1.79E+308 1.79E+308

Real -3.40E+38 3.40E+38

String Data Types in MYSQL

Character String Datatype

The subtypes are given in below table –
Data Type Description

The maximum length of 8000 characters.(Fixed-Length non-Unicode

Char
Characters)

The maximum length of 8000 characters.(Variable-Length non-Unicode

varchar
Characters)

The maximum length of 231 characters(SQL Server 2005 only).(Variable

varchar(max)
Length non-Unicode data)

The maximum length of 2,127,483,647 characters(Variable Length non-

Text
Unicode data)

Server String Data Type in SQL

There are four subtypes of this datatype which are given below:
Datatypes Description

Binary The maximum length of 8000 bytes(Fixed-Length binary data)

varbinary The maximum length of 8000 bytes(Variable Length binary data)

The maximum length of 231 bytes(SQL Server 2005 only).(Variable

varbinary(max)
Length binary data)
 Practical-3
AIM: What is SQL? Write the queries for Data Definition Language (DDL) in
RDBMS.

THEORY:
SQL – SQL is Structured Query Language which is computer language for
storing, Manipulating and retrieving data stored in a relational database. SQL
is the standard language for Relational Database System. SQL is categorized
into five different languages:
▪ DDL – Data Definition Language
▪ DML – Data Manipulation Language
▪ DCL – Data Control Language
▪ DQL – Data Query Language
▪ TCL – Transaction Control Language
DDL (Data Definition Language) - is to Create, Alter, Drop, Delete, Truncate
and Rename the table.
DML (Data Manipulation Language) – is to Insert, Select, Delete, Update.

DCL (Data Control Language) – is to Grant, Revoke and Commit.

DQL (Data Query Language) is also known as DRL (Data Retrieval Language) - select

TCL (Transaction Control Language) – is to Commit , Rollback and Safepoint.

SOURCE CODE:
Commands for DDL:

(a) Create Table:

Syntax: create table table_name(column1 datatype(size), column2

datatype(size), column3 datatype(size));
Query: create table student_info(Roll_No int(10), Name char(20), City char(20));
Output:
 (b) Insert Command:

(i) Insert Data Only in Specified Column

Syntax: Insert into table_name(column1)values(value1);
Query: insert into student_info (Roll_No) value
(2321024); Output:

(ii) Specify both the column names and the values to be inserted::
Syntax: Insert into table_name(column1, column2, column3,--)values(value1,
value2, value3,--);
Query: insert into student_info (Roll_No,Name,City) values (2321025,'Aanchal','Ambala');
Output:

(ii) To insert many values in multiple rows in the table:

Syntax: insert into table_name value(value1, value2, value3,--),(value1, value2, value3,--
),(value1, value2, value3,--);
Query: insert into student_info values
(2321026,'Aryan','Ambala'), (2321027,'Divya','Amabala'),
(2321028,'Badal','Ambala');
Output:
 (c) Alter Command:

(i) ADD a new column in the table:

Syntax: alter table table_name ADD(Column1 datatype(size), column2 datatype(size));
Query: alter table student_info ADD (Branch char(10));
Output:

(ii) CHANGE a attribute in the table:

Syntax: alter table table_name CHANGE column1 column1datatype(size);
Query: alter table student_info change name S_Name char(20);

Output:
 Practical-4
AIM: Commands of DDL (Update, Delete, Rename, Truncate ).
THEORY:
(a) Update Command:
Syntax: update table_name SET column1 = value1, where condition;
Query: update student_info SET S_Name = 'Vanshika' where Roll_No='2321024';
Output:

(b)Delete Command:
Syntax: Delete from table_name where condition;
Query: delete from student_info where Roll_No = '2321025';
Output:

(c) Rename Command:

Syntax: Rename table table_name to new
table_name; Query: Rename table
student_info To student_information; Output:
(d) Truncate Command:
Syntax: Truncate table table_name;

Query: truncate table

student_information; Output:
 Practical-5
AIM: Write the queries for DML (Data Manipulation Language) in RDBMS.
(a) Create Command Using Primary key:
Syntax: Create table table_name (column_name datatype(size),
column2_name datatype(size) Primary Key);
Query: create table Employee(Id int(10) PRIMARY KEY, Name
char(20),Salary int(20));

(b) Select Command

(a) MIN command:

Syntax: Select MIN(column_Name) from table_name;
Query: Select MIN(Salary) from Employee;

(b) MAX command:

Syntax: Select MAX(column_Name) from table_name;
Query: Select MAX(Salary) from Employee;
(c) Arithmetic Operations Command:
(a) Less than
Syntax: Select column_name fromtable_name where column_name < value;
Query: select Salary from Employee where Salary<600000;

(b) Greater than

Syntax: Select column_name from table_name where column_name < value;
Query: select Salary from Employee where Salary>700000;

(d) Delete Command

(a)Delete Row Command:
Syntax: Delete from table_name where column_name =”value”;
Query: Delete from Employee where Names = "Aanchal";

(b)Delete Column Command:

Syntax: Alter table table_name Drop column_name;
Query: Alter table Employee DROP Salary;
 Practical-6
AIM: Create a database and perform the following operations:
(i) Like predicate for pattern matching in database.

(a) Create Table Command:

Syntax: create table tableName (ColumnName1 datatype (size) ,
ColumnName2 datatype (size)……);
Query: create table S_Name(Rollno int(10),Name char(10),Address char(10));

(b) Like predicate Command:

1) alphabet%
Syntax: select* from tableName where attributeName like “alphabet%”;
Query: select* from S_Name where Name like "V%";

2) %alphabet
Syntax: select* from tableName where attributeName like “%alphabet”;
Query: select* from S_Name where Address like ”%t”;

3) %alphabet
Syntax: select* from tableName where attributeName like “%alphabet ”;
Query: select* from S_Name where Name like “%i ”;
(c) DEFAULT Command:
1. Adding default in new column:
Syntax: Alter table tableName ADD(clm_name datatype(size) DEFAULT “value”;
Query: alter table S_Name ADD(College char(20) DEFAULT “ACE”);

2. Adding default in existing column:

Syntax: Alter table tableName Alter ColumnName SET DEFAULT ‘value’;
Query: alter table S_Name Alter Address SET DEFAULT “Ambala”;

(d)NOT NULL Command:

Syntax: Alter table tableName ADD ColumnName datatype NOT NULL;
Query: alter table S_Name ADD(Age int NOT NULL);
 Practical-7
AIM: Write SQL queries for the aggregation and relational algebra.
Count Command:
Syntax: select count (column_name) from table_name;
Query: select count(E_Name) from Employee;

Average Command:
Syntax: Select avg (column_name) from table_name;
Query: select avg (E_Salary) from Employee;

Sum Command:
Syntax: Select sum (column_name) from table_name;
Query: select sum(E_Salary) from Employee;

Distinct Command:
Syntax: Select distinct column_name from table_name;
 Query: select distinct E_Salary,E_D_id from Employee;

Relational Operators:

AND Command:
Syntax: Select * from table_name where column_name AND column_name;
Query: Select * from Employee where E_D_id = 1 AND E_Name = ‘Shawn’;

OR Command:
Syntax: Select * from table_name where column_name OR column_name;
Query: Select * from Employee where E_D_id = 2 AND E_Name = ‘Shawn’;

NOT Command:
Syntax: Select * from table_name where NOT column_name;
Query: Select * from Employee where not E_Name = ‘John’;
 Practical-8
AIM- Write SQL queries for extracting data from more than one table.
Theory- SQL Join statement is used to combine data or rows from two or more tables based on
a common field between them. Different types of Joins are as follows:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN;
Inner Join- The INNER JOIN keyword selects all rows from both the tables as long as the
condition is satisfied. This keyword will create the result-set by combining all rows from both
the tables where the condition satisfies i.e value of the common field will be the same.
Syntax- SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 INNER JOIN table2 ON
table1.matching_column = table2.matching_column;
Query: select * from
studentdata;
Select* from
stucoursedata;
Select stucoursedata.Course , studentdata.Name, studentdata.Gender from
studentdata INNER JOIN stucoursedata ON
studentdata.RollNo=stucoursedata.RollNo;

Left Join-This join returns all the rows of the table on the left side of the join and
matches rows for the table on the right side of the join. For the rows for which
there is no matching row on the right side, the result-set will contain null. LEFT
JOIN is also known as LEFT OUTER JOIN.
Syntax- SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;
Query: select studentdata.Name, studentdata.RollNo, stucoursedata.Course from studentdata
LEFT JOIN stucoursedata on studentdata.RollNo= stucoursedata.RollNo;

Right Join- RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the
table on the right side of the join and matching rows for the table on the left side of the join.
For the rows for which there is no matching row on the left side, the result-set will contain
null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax- SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 RIGHT JOIN table2 ON table1.matching_column
= table2.matching_column;
Query: select studentdata.Name, studentdata.RollNo, stucoursedata.Course
from stucoursedata
RIGHT JOIN studentdata on studentdata.RollNo= stucoursedata.RollNo;

Cross join- We use the cross join to combine two different tables, then we will get the
Cartesian product of the sets of rows from the joined table. When each row of the first table
is combined with each row from the second table, it is known as Cartesian join or cross join.
Syntax-
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1
CROSS JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
Query- select * from studentdata CROSS JOIN stucoursedata ON studentdata.RollNo=
stucoursedata.RollNo;
 Practical-9
AIM- Concepts for ROLL BACK, COMMIT & CHECK POINTS.

THEORY- Transaction Control Language (TCL) commands are used to manage

transactions in database. These are used to manage the changes made by DML
statements. It also allows statements to be grouped together into logical transactions.
Commit command:
Commit command is used to permanently save any transaction into database.
Syntax:

COMMIT;
Query:

COMMIT;

Rollback command:
This command restores the database to last committed state. It is also use with
savepoint command to jump to a savepoint in a transaction.
Syntax:
ROLLBACK;
Query:
ROLLBACK;
Savepoint command:
Savepoint command is used to temporarily save a transaction so that you can
rollback to that point whenever necessary.
Syntax: SAVEPOINT name;
Query: SAVEPOINT P;
It is used with rollback.
Syntax: ROLLBACK to
name; Query: ROLLBACK to
P;
 Practical-10
AIM- Write SQL queries for the following:
1. Write a query on the customer table whose output will exclude all
customers with a rating greater than equal to 100 unless they are
located in Shimla.

2. Increase the rating with 100 where location is Shimla.

3. Write a query that will produce the salesman id value of all salespeople with
order currently in the table without any rates.
4. Write a query to get the details of that salesman whose amount paid is neither null
nor zero.

5. Write a query to display the details of all Employees in the descending order of DOJ.

6. Write a query to add a new row with the following: 19, ‘Harish Roy’ , ‘HEAD-IT’,
‘S02’, ’09-SEP-2007’ , ’21-APR-1983’.
7. Write a query to display NAME and DESIG of those Employees whose
SALGRADE is either (SGRADE) S02 or S03.

8. Write a query to display the content of all the EMPLOYEE table, whose DOJ is in between
‘09-Feb-2006’ and ‘08-Feb-2009’.
 Practical-11

Aim – Write Date and Time Functions in SQL.

The Date and Time functions are used in SQL to fetch the date and time by using the SQL query.
Various Date and Time functions are as follows:
Select now();

The Date format in MySQL is YYYY-MM-DD

The time format is given as HH-MM-SS
Select month(‘date’);

Select monthname(‘date’);

Select day(‘date’);
Select dayname(‘date’);

Select year(‘date’);
 Practical-12
Aim – Create a View to display Employee’s Name and their Projects.
The employees table is created as follows-

The project table is created as follows-

Now the view is created using the following SQL query-

create view v1 as select employees.name,project.name as p_name from employees,project where
E_id = project.id;