ITM University Gwalior
(Font times new roman font size 24, Central align)
Practical File
On
Database Management System (MCA-201)
(Font times new roman font size 20, Central align)
Submitted to Submitted by
[Link] Sharma <Name of Student>
Assistant Professor, Dept. of CSA <Roll No.>
(Font times new roman font size 20, Central align)
Index
Sno Tittle Page no Signature Remark
Note: Use one side A4 paper
Font: Times New Roman
Experiment No. 1
Objective:
To create a new table using SQL with different data types.
Program:
CREATE TABLE Student
(
Student_ID NUMBER,
Student_Name VARCHAR2(50),
Course VARCHAR2(20),
Age NUMBER,
City VARCHAR2(30)
);
Screenshot of Output:
Experiment No. 2
Objective:
To alter the structure of an existing table.
Program:
ALTER TABLE Student
ADD Email VARCHAR2(40);
DESC Student;
Screenshot of Output:
Experiment No. 3
Objective:
To add constraints in a table.
Program:
ALTER TABLE Student
MODIFY Student_ID NUMBER PRIMARY KEY;
ALTER TABLE Student
MODIFY Student_Name VARCHAR2(50) NOT NULL;
DESC Student;
Screenshot of Output:
Experiment No. 4
Objective:
To insert data using different insert statements.
Program:
INSERT INTO Student VALUES
(8,'Ayush','MCA',22,'Bhopal','ayush@[Link]');
INSERT INTO Student (Student_ID,Student_Name,Course,Age)
VALUES (9,'Priya','BCA',20);
SELECT * FROM Student;
Screenshot of Output:
Experiment No. 5
Objective:
To update and delete records.
Program:
UPDATE Student
SET City='Mumbai'
WHERE Student_Name='Akshat';
DELETE FROM Student
WHERE Student_Name='Ashwin';
SELECT * FROM Student;
Screenshot of Output:
Experiment No. 6
Objective:
To use single row functions.
Program:
SELECT UPPER(Student_Name) FROM Student;
SELECT LOWER(Course) FROM Student;
SELECT LENGTH(Student_Name) FROM Student;
Screenshot of Output:
Experiment No. 7
Objective:
To use group functions.
Program:
SELECT COUNT(*) FROM Student;
SELECT MAX(Age) FROM Student;
SELECT MIN(Age) FROM Student;
SELECT AVG(Age) FROM Student;
Screenshot of Output:
Experiment No. 8
Objective:
To use date and time functions.
Program:
SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM DUAL;
Screenshot of Output:
Experiment No. 9
Objective:
To apply pattern matching using LIKE operator.
Program:
SELECT * FROM Student
WHERE Student_Name LIKE 'A%';
SELECT * FROM Student
WHERE Course LIKE 'M%';
Screenshot of Output:
Experiment No. 10
Objective:
To perform join operation.
Program:
CREATE TABLE Department
(
Dept_ID NUMBER,
Course VARCHAR2(20),
Department_Name VARCHAR2(30)
);
INSERT INTO Department VALUES (1,'MCA','Computer');
INSERT INTO Department VALUES (2,'BCA','Computer');
INSERT INTO Department VALUES (3,'MSC','Science');
INSERT INTO Department VALUES (4,'MBA','Management');
SELECT Student.Student_Name,[Link],Department.Department_Name
FROM Student
JOIN Department
ON [Link] = [Link];
Screenshot of Output:
Experiment No. 11
Objective:
To create and use subquery.
Program:
SELECT * FROM Student
WHERE Age = (SELECT MAX(Age) FROM Student);
Screenshot of Output:
Experiment No. 12
Objective:
To create a view.
Program:
CREATE VIEW Student_View AS
SELECT Student_Name,Course,Age
FROM Student;
SELECT * FROM Student_View;
Screenshot of Output: