Directorate of Online Education
SRM Institute of Science and Technology
SRM Nagar, Kattankulathur-603203
LAB MANUAL
Course :MCA
SEMESTER
I
SUBJECT TITLE
DATABASE TECHNOLOGY
SUBJECT CODE
V20PCA103
Prepared By
[Link]
Assistant Professor DataScience and Business Systems,
SRM IST
Directorate of Online Education
OBJECTIVES
[Link] introduce students to the basic knowledge of Database concepts
2. To impart writing skill of SQL commands to the students.
3. To impart the concepts like Basic commands, PLSQL and Triggers
COURSE OUTCOME
1. Understand the concepts for SQL.
2. Write the Process Steps of a given scenario.
3. Apply all the concepts that have been covered in the theory course
4. Evaluate methodology of a given query.
5. Recognize and understand the syntax and construction of SQL command.
6. Know the steps involved in execution of SQL Query.
7. Know the alternative ways of providing solution to a given query
INTRODUCTION ABOUT VIRTUAL LAB
Virtual Lab Content is prepared by Course Coordinator of concern subject to help
the students with their practical understanding and development of programming
skills, and may be used as a base reference during the Practical Assignments. The
model lab programs and List of Exercise Assignment prepared by staff members
will be upload in LMS. Students have to submit Lab Exercise through LMS as
Assignment Sections as Separate Folder of concern subject. The course
coordinator of concern subject can be evaluated after students submit all program
assignments for end semester sessional examination.. The lab Program reporting
style in the prescribed format (Appendix-I) and List of Lab Exercises as
Assignments prescribed format (Appendix-II)
Directorate of Online Education
VIRTUAL LAB CONTENTS(STUDENT)
This manual is intended for the First year students of MCA branch in the subject
of Database Technology . This manual typically contains practical/Lab Sessions
related to, SQL, Triggers and PL/SQL Language covering various aspects related
the subject to enhanced understanding.
Although, as per the syllabus, SQL are prescribed, we have made the efforts to
cover various aspects of SQL Languages
Students are advised to thoroughly go through this manual rather than only topics
mentioned in the syllabus as practical aspects are the key to understanding and
conceptual visualization of theoretical aspects covered in the online contents.
Guidelines
1. Students are instructed to perform their lab exercises/assignments at their
own system from their respective residences
[Link] and editing the program in your system.
Compiling and Executing the program and save the output
3. The students are also advised to submit completed Lab assignments in the
prescribed format (Appendix-1) in LMS
4. The students are advised to complete the weekly activities/assignments well in
time.
5. The submitted Lab Assignment will be evaluated for end semester Practical
examination
6. The students must get the completed Lab Assignments evaluated by the
concerned course coordinator by LMS , Failing which the Lab assignments for that
week will be treated as incomplete.
7. At least TEN (10) such timely completed Lab assignments are compulsory,
failing which students will not be allowed to appear in the final end semester Lab
Examination.
Directorate of Online Education
APPENDIX-I
1. Title : Data Definition Languages
Aim - Study basic data definition language commands and execute them using
oracle sql plus.
2. Process Steps/Description
1. Install Oracle 11 g / 12 C version
2. select SQL Plus
3. Log in to SQL window
4. Go to start → sql plus
5. SQL> After obtain this SQL prompt start execution of DDL commands
[Link]
Oracle 11g - SQL platform
[Link] Commands -
1. CREATE: It is used to create a new table.
Syntax:
create table <tablename>(col 1 data type(size of col1), col2 data type(size of col2)….);
Example:
SQL> CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email
VARCHAR2(100), DOB DATE);
Table created.
2. DESC: It is used to describe the contents and their data types.
Syntax:
DESC Table-name;
Example:
Directorate of Online Education
SQL> DESC EMPLOYEE
Name Null? Type
---------------------------- ------------ ----------------------------
NAME VARCHAR2(20)
EMAIL VARCHAR2(100)
DOB DATE
3. ALTER: It is used to alter the structure of the database. This change could be either to
modify the characteristics of an existing attribute or probably to add a new attribute.
Syntax:
ALTER TABLE table_name ADD column_name COLUMN-definition;
ALTER TABLE MODIFY(COLUMN DEFINITION....);
Example:
SQL> ALTER TABLE EMPLOYEE ADD(ADDRESS VARCHAR(20));
Table altered.
4. DROP: It is used to drop a column or the whole table.
Syntax:
DROP COLUMN column-name;
Example:
SQL> DROP TABLE EMPLOYEE;
Table dropped.
5. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
SQL> TRUNCATE TABLE EMPLOYEE1;
Table truncated.
Directorate of Online Education
[Link] Input/Output:
6. Result / Inference - Basic DDL language commands were successfully
executed using sqlplus.
Directorate of Online Education
II. 1. Title : Data Manipulation Languages
Aim - Study basic data manipulation language commands and
execute them using oracle sql plus.
2. Process Steps/Description
1. Install Oracle 11 g / 12 C version
2. select SQL Plus
3. Log in to SQL window
4. Go to start → sql plus
5. SQL> After obtain this SQL prompt start execution of DML commands
[Link]
Oracle 11g - SQL platform
[Link] Commands -
In this exercise, students will be able to do the following:
Describe each DML statement
Insert rows into a table
Update rows in a table
Delete rows from a table
Merge rows in a table
DML statements – INSERT, UPDATE, DELETE, MERGE
Adding new row into a table
INSERT INTO emp VALUES(101,’vadivu’,33000,’Programmer’,’01-jan-98’);
Modifying the existing data
UPDATE emp SET salary = 10000 WHERE id = 101;
Deleting rows
Directorate of Online Education
DELETE FROM emp where id = 101;
Merge
Provides the ability to conditionally update or insert data into a database table.
Performs an UPDATE if the row exists, and an INSERT if it is a new row.
Eg.
MERGE INTO copy_emp c USING employees e ON ([Link] = [Link])
WHEN MATCHED THEN
UPDATE SET
[Link] = [Link],
[Link] = [Link]
WHEN NOT MATCHED THEN
INSERT VALUES([Link], [Link], [Link]);
[Link] Input/Output:
6. Result / Inference - Basic DML language commands were
successfully executed using sqlplus.
Directorate of Online Education
Directorate of Online Education
APPENDIX-II
LIST OF EXPERIMENTS -ASSIGNMENTS -LMS
Assignment Title of Program
No
1 Create a student table with various types of fields
2 Show the structure of the table
3 Modify the table structure by adding a new column and remove
the existing column
4 Write SQL commands to delete the last 5 rows
5 Create a table by adding a Data of birth column to employee
table.
6 Delete the table along with table structure
7 Write SQL commands to insert 5 records in to the table
8 Write a SQL command to update the address field whose name is
[Link]
9 Remove all the records from the table created
10 Consider a case to have a database consists of 10 sports goods
items with the description of item name, cost,
product_quality,product delivery date
Directorate of Online Education