Description

NAME: ARNAB SEN
EN.NO: 510519006 (Gx)
SUBJECT: DBMS Lab
Assignment No: 7
1. Create a view of all students in dept CSE.
query:
CREATE VIEW CSE_students AS
SELECT *
FROM students
WHERE deptcode = ‘CSE’;

2. Create a view named as cse_stud for ‘CSE’ deptstudents having attributes rollno, name, hostel
query:
CREATE VIEW cse_stud AS
SELECT rollno, name, hostel
FROM students
WHERE deptcode = ‘CSE’;

3. Insert a new student of CSE. Analyse the result.
query: INSERT INTO cse_stud
VALUES
(‘51052987’, ‘Rashmi’, ‘8’);

Analysis:
Inserting the new record didn’t reflect in the virtual table but did show up in the base table. As we can see the record in the base table doesn’t have any deptcode. Hence the query used for the View doesn’t apply here.
4. Increment parental income by Rs. 5000 (HRA).
query:
UPDATE cse_students
SET parent_inc = parent_inc + 5000;

5. Delete the view.
query: DROP VIEW cse_stud;

6.Create another view of all students in dept Mechanical Engineering (department Name). The view will contain attributes namely Roll-No, Name, Department Name, Age
query:
CREATE VIEW mec_stud AS
SELECT rollno, name, deptcode,
DATE_PART(‘year’, AGE(bdate)) AS age
FROM students
WHERE deptcode = ‘MEC’;

7. Insert a new student of Mechanical Engineering Department.
query:
INSERT INTO students
VALUES
(
‘MEC’, 6,
573600
);

Analysis: When we perform the INSERT on the base table it get’s reflected in the view as well.
8. Delete a student (for a given Name) of the samedepartment
query:
DELETE FROM students
WHERE name = ‘Krishnendu’;

Analysis: When we perform the DELETE on the base table it get’s reflected in the view as well.
9. Shift a student (for a given Name) fromMechanical to Computer Science.
query:
UPDATE mec_stud
SET deptcode = ‘CSE’
WHERE name = ‘Misha’;

Analysis: After performing the update command the mec_stud get’s updated and teh cse_students also reflect the change.