IIMT COLLEGE OF MANAGEMENT

COURSE: BCA
SUBJECT : DATABASE MANAGEMENT SYSTEM
SUBJECT CODE: 507
ROLL NO. 220613106113

SUBMITTED TO : SUBMITTED BY:

MRS. LAXMI MAM GAURAV
 INDEX
S.NO PROGRAM DATE SIGN.
1. Implementation of DDL commands of SQL with
suitable examples
 Create table
 Alter table
 Drop Table
2. Implementation of DML commands of SQL with
suitable examples
 Insert
 Update
 Delete
3. Implementation of different types of function with
suitable examples
 Number function
 Aggregate Function
 Character Function
 Conversion Function
 Date Function
4. Implementation of different types of operators in
SQL
 Arithmetic Operators
 Logical Operators
 Comparison Operator
 Special Operator
 Set Operation
5. Implementation of different types of Joins
 Inner Join
 Outer Join
 Natural Join etc..
6. Study and Implementation of
 Group By & having clause
 Order by clause
 Indexing
7. Implementation of
 Sub queries
 Views
8. Implementation of different types of constraints

9. Implementation of Database Backup & Recovery

commands. Study & Implementation of Rollback,
Commit, Savepoint.
10. Creating Database /Table Space
 Managing Users: Create User, Delete User
 Managing roles:-Grant, Revoke.
 INTRODUCTION OF DBMS
A database is a collection of interrelated data that helps in the efficient retrieval,
insertion, and deletion of data from the database and organizes the data in the form of
tables, views, schemas, reports, etc

What is DBMS.
A Database Management System (DBMS) is a software system that is designed to
manage and organize data in a structured manner. It allows users to create, modify, and
query a database, as well as manage the security and access controls for that database.
DBMS provides an environment to store and retrieve data in convenient and efficient
manner.

Key features of DBMS

 Data modeling: A DBMS provides tools for creating and modifying data models,
which define the structure and relationships of the data in a database.
 Data storage and retrieval: A DBMS is responsible for storing and retrieving data
from the database, and can provide various methods for searching and querying
the data.
 Concurrency control: A DBMS provides mechanisms for controlling concurrent
access to the database, to ensure that multiple users can access the data without
conflicting with each other.
 Data integrity and security: A DBMS provides tools for enforcing data integrity
and security constraints, such as constraints on the values of data and access
controls that restrict who can access the data.
 DATABASE LANGUAGES
1. DATA DEFINITION LANGUAGE (DDL)
DDL is the short name for Data Definition Language, which deals with database
schemas and descriptions, of how the data should reside in the database.
 CREATE
To create a database and its objects like (table, index,

 ALTER
This is used to add some extra fields into existing relation.
1.ALTER ..TABLE..ADD..COLUMN
To add a new column to previous table.

2.ALTER TABLE- RENAME COLUMN

It is used to rename the column name .

 DROP TABLE
It is used to delete the table
2.DATA MANIPULATION LANGUAGE(DML)
The Data Manipulation Language (DML) is used to retrieve, insert and modify
database information. These commands will be used by all database users during
the routine operation of the database. Let's take a brief look at the basic DML
commands:
1. Insert
The insert into statement is a fundamental sql command used to add new rows to
a table in a database.

2. Update
The UPDATE statement in SQL is used to update the data of an existing table in
the database. We can update single columns as well as multiple columns using the
UPDATE statement as per our requirement.

3. Delete
SQL DELETE is a basic SQL operation used to delete data in a database. SQL
DELETE is an important part of database management DELETE can be used to
selectively remove records from a database table based on certain conditions.
 DIFFERENT TYPES OF FUNCTION
1.NUMERIC FUNCTION
Numeric Functions are used to perform operations on numbers and return numbers.
 Abs():
It returns the absolute value of a number.

 Acos():
It returns the cosine of a number, in radians.

 DEGREES():
It converts a radian value into degrees.

 FLOOR():
It returns the largest integer value that is less than or equal to a number.
2.AGGREGATE FUNCTION
SQL Aggregate functions are functions where the values of multiple rows are grouped as
input on certain criteria to form a single value result of more significant meaning.

 Count()
Return the total number of records in table

 Sum():
It returns the total salary of the employee

 MAX():
IT RETURNS THE MAXIMUM VALUE FROM THE RECORD.

 Avg():
It returns the average salary of the record.
CHARACTER FUNCTION
SQL provides a rich set of character functions that allow you to get
information about strings and modify the contents of those strings in
multiple ways. Character functions are of the following two types:
 LOWER
This function converts alpha character values to lowercase. LOWER
will actually return a fixed-length string if the incoming string is fixed-
length.

 UPPER
This function converts alpha character values to uppercase. Also
UPPER function too, will actually return a fixed-length string if the
incoming string is fixed-length.

 CONCAT
This function always appends ( concatenates ) string2 to the end of
string1. If either of the string is NULL, CONCAT function returns the
non-NULL argument.
Syntax:
CONCAT('String1', 'String2')
 CONVERSION FUNCTION
When you define expressions and local variables then you should specify what type of
data will be stored in those objects such as text data, money, dates, numbers, or
characters.

Implicit Data-Type Conversion

In this type of conversion, the data is converted from one type to another implicitly (by
itself/automatically).
1. Query
Here, we want to retrieve the employee_id, first_name, and salary from the employees
table whose salary is greater than 15000 then the query is
SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Explicit Data-Type Conversion

In this type of conversion, the data is converted from one type to another explicitly (by
the user). simply we can say, users define the type to which the expression is to be
converted.
TO_CHAR Function
TO_CHAR function is used to typecast a numeric or date input to a character type with a
format model (optional).
SELECT employee_id, TO_CHAR(hire_date, ’MM/YY’) Month_Hired
FROM employees
WHERE last_name = ’Higgins’;
Output :
EMPLOYEE_ID MONTH_HIRED

205 06/94
DATE FUCTION
In SQL, dates are complicated for newbies, since while working with a database, the
format of the data in the table must be matched with the input data to insert. In various
scenarios instead of date, datetime (time is also involved with date) is used.
NOW()
Returns the current date and time.
SELECT NOW();

CURDATE()
Returns the current date.
SELECT CURDATE();

CURTIME()
Returns the current time.
SELECT CURTIME();

DATE()
Extracts the date part of a date or date/time expression. Example: For the below table
named ‘Test’
SELECT Name, DATE(BirthTime)
AS BirthDate FROM Test;
Name BirthDate

Pratik 1996-09-26
 DIFFERENT TYPES OF OPERATIONS
ARITHEMATIC OPERATORS
We can use various Arithmetic Operators on the data stored in the tables.
Addition (+) :
It is used to perform addition operation on the data items, items include either single
column or multiple columns.
SELECT employee_id, employee_name, salary, salary + 100
AS "salary + 100" FROM addition;

employee_id employee_name salary salary+100

1 alex 25000 25100

2 rr 55000 55100

3 jpm 52000 52100

4 ggshmr 12312 12412

Subtraction (-) :
It is use to perform subtraction operation on the data items, items include either single
column or multiple columns.
SELECT employee_id, employee_name, salary, salary - 100
AS "salary - 100" FROM subtraction;

employee_id employee_name salary salary-100

12 Finch 15000 14900

22 Peter 25000 24900

32 Warner 5600 5500

42 Watson 90000 89900

Logical Operators
SQL logical operators are used to test for the truth of the condition. A logical operator
like the Comparison operator returns a boolean value of TRUE, FALSE, or UNKNOWN.

AND Operator
The AND operator is used to combines two or more conditions but if it is true when all
the conditions are satisfied.
SELECT * FROM employee
WHERE emp_city = 'Allahabad'
AND emp_country = 'India';

IN Operator
It is used to remove the multiple OR conditions in SELECT, INSERT, UPDATE,
or DELETE. and We can also use NOT IN to minimize the rows in your list and any kind
of duplicate entry will be retained.
SELECT * FROM employee
WHERE emp_city
IN ('Allahabad', 'Patna');

OR Operator
The OR operator is used to combines two or more conditions but if it is true when one of
the conditions are satisfied.
SELECT * FROM employee
WHERE emp_city = 'Varanasi'
OR emp_country = 'India';
Comparison Operators
SQL Comparison Operators are used to compare two values and check if they meet
the specific criteria. Some comparison operators are = Equal to, > Greater than , <
Less than,
Equal to (=) Operator
It returns the rows/tuples which have the value of the attribute equal to the given value.
SELECT * FROM MATHS WHERE MARKS=50;

Greater than (>) Operator:

It returns the rows/tuples which have the value of the attribute greater than the given
value.
SELECT * FROM MATHS WHERE MARKS>60;
Less than (<) Operator:
It returns the rows/tuples which have the value of the attribute lesser than the given
value.
SELECT * FROM MATHS WHERE MARKS<40;

Greater than or equal to (>=) Operator:

It returns the rows/tuples which have the value of the attribute greater or equal to the
given value.
SELECT * FROM MATHS WHERE MARKS>=80;
SET OPERATORS
SET operators are special type of operators which are used to combine the result of two queries.
Operators covered under SET operators are:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION:
o UNION will be used to combine the result of two select statements.
o Duplicate rows will be eliminated from the results obtained after performing the UNION operation.
mysql> SELECT *FROM t_employees UNION SELECT *FROM t2_employees;
ID Name Department Salary Year_of_Experience

Aakash
1 Development 72000 2
Singh

Abhishek
2 Production 45000 1
Pawar

Pranav
3 HR 59900 3
Deshmukh

UNION ALL
o This operator combines all the records from both the queries.
o Duplicate rows will be not be eliminated from the results obtained after performing the UNION ALL
operation.
mysql> SELECT *FROM t_employees UNION ALL SELECT *FROM t2_employees
ID Name Department Salary Year_of_Experience

Aakash
1 Development 72000 2
Singh

Abhishek
2 Production 45000 1
Pawar
 MINUS

o It displays the rows which are present in the first query but absent in the second query with no
duplicates.

mysql> SELECT *FROM t_employees MINUS SELECT *FROM t2_employees;

ID Name Department Salary Year_of_Experience

Aakash
1 Development 72000 2
Singh

Pranav
3 HR 59900 3
Deshmukh
 DIFFERENT TYPES OF JOINS

SQL joins are fundamental operations used in database management systems to

combine data from two or more tables based on a related column between them. Joins in
SQL allow for efficient data retrieval, enabling users to generate more complex and
informative queries.

Types of JOIN in SQL

There are many types of Joins in SQL. Depending on the use case, you can use different
type of SQL JOIN clause.
Table of Content
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN
SQL INNER JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the condition is
satisfied.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

SQL LEFT JOIN

LEFT 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.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
SQL RIGHT JOIN
RIGHT 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.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

SQL FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT
JOIN. The result-set will contain all the rows from both tables.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
NAME COURSE_ID

HARSH 1

PRATIK 2

RIYANKA 2

DEEP 3
 IMPLEMENTATION OF CLAUSES
GROUPING BY
The GROUP BY clause is often used with aggregate functions (MAX, SUM, AVG) to
group the results by one or more columns or In simple words we can say that The
GROUP BY clause is used in collaboration with the SELECT statement to arrange
required data into groups.
SELECT COUNT (SALARIES) AS COUNT_SALARIES, EMPLOYEES
FROM EMPLOYEES
GROUP BY SALARIES
HAVING COUNT(SALARIES) > 1;

Advantages of Group By Clause

 It allows for the grouping of rows that have the same values in one or more columns.
 It helps to reduce the number of rows in the output table and summarize data.

Disadvantages of Group By Clause

 It can be time-consuming to write and optimize complex GROUP BY queries.
 It may require subqueries or temporary tables to achieve certain types of
aggregations.

Having Clause
The Having Clause is basically like the aggregate function with the GROUP BY clause.
The HAVING clause is used instead of WHERE with aggregate functions. While
the GROUP BY Clause groups rows that have the same values into summary rows.

Advantages of Having Clause:

 It allows for the filtering of groups based on a condition that involves an aggregate
function.
 It can be used to perform calculations on aggregated data, such as calculating
percentage or ratios.
Disadvantages of Having Clause:
 It can slow down query performance if the query involves complex calculations.
 It can be difficult to understand the output of a complex HAVING query.
Difference between Having clause and Group by clause :
S.No. Having Clause GroupBy Clause

The groupby clause is used to group

It is used for applying some extra condition to
1. the data according to particular
the query.
column or row.

Having cannot be used without groupby

groupby can be used without having
2. clause,in aggregate function,in that case it
clause with the select statement.
behaves like where clause.

The having clause can contain aggregate It cannot contain aggregate

3.
functions. functions.

It restrict the query output by using some It groups the output on basis of
4.
conditions some rows or columns.

SQL ORDER BY
SQL ORDER BY clause sorts the result of the SELECT statement either in ascending or
descending order.

ORDER BY in SQL
The ORDER BY statement in SQL is used to sort the fetched data in either ascending
or descending according to one or more columns. It is very useful to present data in a
structured manner.
SELECT * FROM students ORDER BY ROLL_NO DESC;
 INDEXING
An index is a schema object. It is used by the server to speed up the retrieval of rows by
using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method
to locate data quickly.

Creating an Index

CREATE UNIQUE INDEX for Unique Values

If you want to create indexes for unique values in a column, we use the CREATE UNIQUE
INDEX constraint.

Remove Index From Tables

To remove INDEX from a table, we can use the DROP INDEX command.
 SUB QUERIES
In SQL a Subquery can be simply defined as a query within another query. In other
words we can say that a Subquery is a query that is embedded in WHERE clause of
another SQL query.
Syntax:
There is not any general syntax for Subqueries. However, Subqueries are seen to be
used most frequently with SELECT statement as shown below:
SELECT column_name
FROM table_name
WHERE column_name expression operator
(SELECT column_name FROM table_name WHERE ...);

Sample Queries:
 To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE
table whose section is A
SELECT NAME, LOCATION, PHONE_NUMBER
FROM DATABASE
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM STUDENT
WHERE SECTION='A');

DELETING SUB QUERY

To delete students from Student2 table whose rollno is same as that in Student1 table
and having location as chennai
DELETE FROM Student2
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM Student1
WHERE LOCATION = 'chennai');

1 row delete successfully.

 SQL Views
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are
fields from one or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the
data were coming from one single table.

CREATE VIEW Syntax

We can create views in SQL by using the CREATE VIEW command.

Updating a View
It's possible to change or update an existing view using the CREATE OR REPLACE
VIEW command.

Deleting a View
We can delete views using the DROP VIEW command.
 Constraints
Constraints are the rules that we can apply on the type of data in a table. That is, we can
specify the limit on the type of data that can be stored in a particular column in a table
using constraints.

NOT NULL
If we specify a field in a table to be NOT NULL. Then the field will never accept null
value. That is, you will be not allowed to insert a new row in the table without specifying
any value to this field.
CREATE TABLE Student(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);

UNIQUE
This constraint helps to uniquely identify each row in the table. i.e. for a particular
column, all the rows should have unique values. We can have more than one UNIQUE
columns in a table.
CREATE TABLE Student(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20));

PRIMARY KEY
Primary Key is a field which uniquely identifies each row in the table. If a field in a table
as primary key, then the field will not be able to contain NULL values as well as all the
rows should have unique values for this field.
CREATE TABLE Student(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID));
FOREIGN KEY –
Foreign Key is a field in a table which uniquely identifies each row of a another table.
That is, this field points to primary key of another table. This usually creates a kind of link
between the tables.
CREATE TABLE Orders(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID))

(i) CHECK
Using the CHECK constraint we can specify a condition for a field, which should be
satisfied at the time of entering values for this field.
CREATE TABLE Student(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18));

(ii) DEFAULT
This constraint is used to provide a default value for the fields. That is, if at the time of
entering new records in the table if the user does not specify any value for these fields
then the default value will be assigned to them.
CREATE TABLE Student(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18 );
Implementation of Database Backup & Recovery
Commands.
A transaction is a logical unit of work. All changes made to the database can be
referred to as a transaction. Transaction changes can be made permanent to the
database only if they are committed a transaction begins with an executable SQL
statement & ends explicitly with either rollback or commit statement.

COMMIT:
This command is used to end a transaction only with the help of the commit
command transaction changes can be made permanent to the database.
Syntax:
SQL> COMMIT;

SAVE POINT:
Save points are like marks to divide a very lengthy transaction to smaller once.
They are used to identify a point in a transaction to which we can latter role back.
Thus, save point is used in conjunction with role back.
Syntax:
SQL> SAVE POINT ID;

ROLLBACK:
A role back command is used to undo the current transactions. We can role back
the entire transaction so that all changes made by SQL statements are undo (or)
role back a transaction to a save point so that the SQL statements after the save
point are role back.
Syntax:
ROLLBACK (current transaction can be role back) ROLLBACK to save point ID;
 Creating Database/ Table Space
DATABASE
It is a collection of coherent data. To create database we have :
Syntax:
CREATE DATABASE <database_name>
TABLESPACE:
The oracle database consists of one or more logical storage units called
tablespaces. Each tablespace in an Oracle database consists of one or more files
called datafiles, which are physical structures that confirm to the operating system
in which Oracle is running.
Syntax:
CREATE<tablespace name> DATAFILE'C:\oraclexe\app\oracle\product\10.2.0\
server \<file name.dbf ’SIZE 50M;
CREATE USER:
The DBA creates user by executing CREATE USER statement.
The user is someone who connects to the database if enough privilege is granted.
Syntax:
SQL> CREATE USER < username>
IDENTIFIED BY <password>
GRANT:
The DBA uses the GRANT statement to allocate system privileges to other user.
Syntax:
SQL> GRANT privilege [privilege…. … ] TO USER ;
SQL> Grant succeeded
REVOKE:
REVOKE statement is used to remove privileges granted to other users.The
privileges you specify are revoked from the users.
Syntax:
REVOKE [privilege.. …] ON object
FROM user