GALGOTIAS COLLEGE OF ENGINEERING AND TECHNOLOGY
Department of Computer Science and Engineering and Allied Specialized Branches
Odd- Semester/2024-25
Subject code: BCS-551
Subject Name: DBMS Lab
Sem/Sec: V
Name - Roll No. -
INDEX
Date Remarks Signature
Exp. No. Name of Experiment
with Date
Introduction to DBMS,
Introduction to SQL & Types of SQL
Statements (DDL, DML, TCL, DCL)
1 Introduction to various RDBMS software
Oracle Installation, Oracle Live
Creating Entity-Relationship Diagram
2 using case tools.
DDL Commands: CREATE,
ALTER, DROP, TRUNCATE,
3 CREATING A TABLE From a
Table Using SELECT Command
DML Commands:
Managing data using INSERT,
4 DELETE and UPDATE
command
Data Constraints at Column Level
5 and Table level
DML Commands:
Retrieving database using
6 SELECT command, Order by
clause
SQL Functions: Date functions,
7 Conversion functions
SQL Functions: Aggregate
8 functions, Single row functions
Retrieving information from
9 database using Group by and
Having clause
Retrieving data using Sub queries
Single row, Multiple rows, ANY
10 operator, ALL operator, EXIST,
Scalar, Multiple columns, Nested,
Correlated
11 Retrieving data from multiple
tables using JOIN
EQUI, INNER, SELF, OUTER
Retrieving data using SET
operators
UNION, UNION ALL,
INTERSECT, MINUS, retrieving
data from multiple tables using
JOIN Practice Questions
PL/SQL Function, Procedure
12 Cursors & Trigger
Experiment1
Objective: Introduction to DBMS, Introduction to SQL & Types of SQL Statements (DDL,
DML, TCL, DCL) Introduction to various RDBMS software, Oracle Installation.
Introduction to DBMS: is a software for storing and retrieving user’s data while considering
appropriate security measures.
Ex: Oracle, MS SQL, MongoDB, MySQL etc.
An Application Program interacts with a database by issuing an appropriate request
(typically a SQL statement)
RDBMS (Relational Database Management System): A database management system that
manages data as a collection of tables in which all relationships are represented by common
values in related tables. Relational Model represents how data is stored in Relational
Databases. A relational database stores data in the form of relations (tables).
SQL (Structured Query Language): SQL (Structured Query Language) is used to perform
operations on the records stored in the database such as updating records, deleting records,
creating, and modifying tables, views, etc.
Structured Query Language (SQL) provides four types of languages based on type of
operation to be performed on a database. These languages can be considered as subsets of
SQL and logical groups only.
ORACLE INSTALLATION
Step1: Go to the Oracle website at http://www.oracle.com.
Step2: Click on Downloads, and then under Database, click on Oracle Database 11g Express Edition.
Step3: Accept the license agreement and select the download version you are interested in.
For example, you can download the Oracle Database 11g Release 2 Express Edition for Windows
64 database. The OracleXE112_Win64.zip file is downloaded.
Step4: Unzip the .zip file by right-clicking on the file and selecting Extract All..., then click Extract on the
next window. You will now see a DISK1 folder, and inside it is the setup.exe executable file.
Step5: Double-click on setup.exe to start the Oracle database installation.
Step6: On the Welcome to Install Wizard window, click Next.
Step7: Accept the terms in the license agreement, and click Next.
Step8: Enter a password, and click Next. This is the password to the SYSTEM account and you can use any
password you wish just note it down.
Step9: Continue the next windows, click Install to install the database, and click Finish once installation is
complete.
Step10: If the completion of the previous step does not automatically take you to the database
administrative window in your browser, you can manually open your browser and access the database
administrative screen by typing the following URL into your browser: http://localhost:8080.
After installation How to use Oracle11g
Step1: Double click on Oracle11g Express Edition icon
Step2: Login with username system and password oracle
Step3: Click on Application Express and create the workspace
Step4: Login to workspace
Step5: Click on SQL Workshop
Step6: Click on SQL commands
Step7: Write queries
Experiment2
Objective: Creating Entity-Relationship Diagram using case tools.
Entity-Relationship Diagram: The entity-relationship (ER) data model is based on a perception of a real-
world that consists of a collection of basic objects, called entities, and of relationships among these objects.
Entities are described by a set of attributes.
The entity-relationship (ER) data model describes data as entities, attributes, and relationships
The database design created using these graphical symbols is referred to as Entity-Relationship Diagram or
E-R Diagram or simply ERD.
Case Tool used for ER-Diagram:
Drawio: draw.io is free online diagram software for making flowcharts, process diagrams, org charts,
UML, ER and network diagrams.
Example: In the entity-relationship design involving Student, Teacher, and Course, three key entities are
defined. The Student entity has attributes like rollNo (the unique identifier for each student), name, and
branch (the department or field of study the student belongs to). The Teacher entity includes attributes such
as teacherId (a unique identifier), name, and department, indicating the department the teacher is associated
with. The Course entity contains courseId (unique identifier for each course), courseName, and credits,
representing the academic weight of the course.
The relationships between these entities include a many-to-many relationship between Student and Course,
as students can enroll in multiple courses, and each course can have many students. This is managed through
an associative entity called Enrollment, which links students to the courses they take. Additionally, a one-to-
many relationship exists between Teacher and Course, where a teacher can teach multiple courses, but each
course is taught by one teacher. This structure allows for efficient tracking of student enrollments and
teacher-course assignments within the system.
ER-Diagram:
Experiment3
Objective: DDL Commands: CREATE, ALTER, DROP, TRUNCATE, CREATING A TABLE From a
Table Using SELECT Command.
DDL Commands: DDL or Data Definition Language actually consists of the SQL commands that can be
used to define the database schema.
Examples of DDL STATEMENTS:
• CREATE – is used to create the database or its objects (like table, index, function, views, store
procedure and triggers).
• DROP – is used to delete objects from the database.
• ALTER-is used to alter the structure of the database.
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the
records are removed.
• COMMENT –is used to add comments to the data dictionary.
CREATE: This DDL command is used to create any database with its different objects such as tables,
indexes, triggers, views, stored procedures, built-in functions etc.
Oracle Datatype:
Syntax:
CREATE TABLE TableName (Column1 Datatype(size), Column2 Datatype(size) ,…,ColumnN
Datatype(size));
Question1: Write a SQL statement to create a table for following schema
Faculty (FacultyId,Name,Address,Doj,DepartmentName)
SQL Statement:
CREATE TABLE faculty (FacultyId number(10), name varchar2(20), address varchar2(20), doj date,
departmentname varchar2(20));
Output:
Question2: Describe faculty table structure
Syntax: Describe tableName;
SQL Statement:
Describe Faculty;
Output:
Question3: insert 1 record into the faculty table
Syntax
INSERT INTO tableName VALUES(value1,value2….valuen);
SQL Statement:
insert into faculty values(101,’Mohit Agarwal’,’Delhi’, ’02-Jan-22’,’CSE’);
Output
Question4: Display all column values of faculty.
Syntax: SELECT * FROM tableName;
SQL Statement: select * from faculty;
Output
RENAME Statement: The Rename DDL command query allows renaming any database objects in the
server if needed for any admin works.
Syntax:
RENAME table_name to new table_name;
Question5: Write a SQL query to change the table name faculty to facultyInfo
SQL Statement: RENAME faculty to facultyInfo;
Output
ALTER Statement:
Oracle ALTER statement is used when you want to change the name of your table or any table field. It is
also used to add or delete an existing column in a table.
The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the
situation.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ADD a column in the table:
Syntax:
ALTER TABLE table_name ADD new_column_name column_definition;
Add multiple columns in the table:
Syntax:
ALTER TABLE table_name
ADD new_column_name column_definition, ADD new_column_name column_definition;
MODIFY column in the table: The MODIFY command is used to change the column definition of the
table.
Syntax:
ALTER TABLE table_name MODIFY column_name column_definition;
DROP column in table:
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
RENAME column in table:
Syntax:
ALTER TABLE table_name RENAME COLUMN old_name new_name;
SQL Question: Write a SQL query to add a research area attribute in Faculty tabe.
SQL Statement:
Alter table faculty add researchArea varchar2(20);
Output:
SQL Question: Write a SQL query to increase the size of faculty name attribute to 30.
SQL Statement: alter table faculty name varchar2(30);
Output
SQL Question: Write a SQL query to decrease the size of faculty name attribute to 3.
SQL Statement:
Alter table faculty modify name varchar2(3);
Output
SQL Question: Write a SQL query to rename departmentname to dept
SQL Statement: Alter table faculty rename departmentname to dept;
Output:
SQL Question: Write a query to drop column researcharea
SQL Statement: alter table faculty column researharea;
Output:
TRUNCATE statement:
The TRUNCATE statement in MySQL removes the complete data without removing its structure. It is a part
of DDL or data definition language command. Generally, we use this command when we want to delete an
entire data from a table without removing the table structure.
Syntax:
TRUNCATE TABLE table name;
SQL Question: truncate faculty table.
SQL Statement: truncate table faculty;
Output:
DROP Table: is used to remove data from the table with schema.
Syntax
DROP Table tablename;
SQL Question: Drop faculty table.
SQL Statement: drop table faculty
