Maulana Abul Kalam Azad University of Technology, West Bengal
Vincent Kiplimo Ngetich
Roll Number-30011218016
Assignment-1
Subject : Advanced Database Management System Lab
Subject Code : PGCSE291/PGSE291
1. Create table DEPARTMENT
Column Data Type Size Constraints
Name
DeptCode Varchar2 4 Not null, Primary key
DeptName Varchar2 40 Not null
HOD Varchar2 4 Not null
CODE
CREATE TABLE DEPARTMENT(
DeptCode VARCHAR(4) not null PRIMARY KEY,
DeptName VARCHAR(40) not null,
HOD VARCHAR(4) not null);
OUTPUT
2. Insert values into DEPARTMENT table, as follows:
DeptCod DeptName HOD
e
IT Information F201
Technology
BIO Bio Technology F901
CSE Computer Science and F101
Engineering
NS Natural Science F506
SQL CODE
INSERT INTO DEPARTMENT(DeptCode, DeptName, HOD) VALUES ('IT',
'Information Technology','F201');
Maulana Abul Kalam Azad University of Technology, West Bengal
INSERT INTO DEPARTMENT(DeptCode, DeptName, HOD) VALUES ('BIO',
'Bio Technology','F901');
INSERT INTO DEPARTMENT(DeptCode, DeptName, HOD) VALUES ('CSE',
'Computer Science and Engineering','F101');
INSERT INTO DEPARTMENT(DeptCode, DeptName, HOD) VALUES ('NS',
'Natural Science','F506');
OUTPUT
3. Add a foreign key constraint in STUDENT against DeptCode column which
references department.
4. Create table: FACULTY
Column Data Type Size Constraints
Name
FacultyCode Varchar2 4 Primary key
FacultyNam Varchar2 15 Not null
e
DateOfJoin Date Not null
DeptCode Varchar2 4 Not null
CODE
CREATE TABLE FACULTY(
FacultyCode VARCHAR(4) not null PRIMARY KEY,
FacultyName VARCHAR(15) not null,
DateOfJoin DATE not null,
DeptCode varchar(4) not null);
Output
5. Insert appropriate values in the above table.
Maulana Abul Kalam Azad University of Technology, West Bengal
FacultyCode FacultyName DateOfJoin
DeptCode
F101 M. Sinha 01-01-2005 CSE
F105 P. Sarkar 01-02-2019 CSE
F201 S. Mazumder 15-09-2005 IT
F301 S. Mondal 01-08-2018 CSE
F401 D. Majumdar 01-12-2003 IT
F506 N. Biswas 31-12-2013 NS
F607 R. Paul 10-04-2007 BIO
F704 S. Sarkar 01-01-2012 IT
F808 K. Das 15-06-2010 IT
F901 R. Roy 15-06-2017 BIO
F902 R. Biswas 15-06-2018 BIO
CODE
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F101', 'M. Sinha','2005-01-01','CSE');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F105', 'P. sarkar','2019-02-01','CSE');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F201', 'S. Mazumder','2005-09-15','IT');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F301', 'S. Mondal','2018-08-01','CSE');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F401', 'D. Majumdar','2003-01-12','IT');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F506', 'N. Biswas','2013-12-31','NS');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F607', 'R. Paul','2007-04-10','BIO');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F704', 'S. sarkar','2012-01-01','IT');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F808', 'K. Das','2010-06-15','IT');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F901', 'R. Roy','2017-06-15','BIO');
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('F902', 'R. Biswas','2018-06-15','BIO');
OUTPUT
Maulana Abul Kalam Azad University of Technology, West Bengal
6. Alter the table Faculty and add check constraint such that FacultyCode starts
with ‘F’
Code
ALTER TABLE FACULTY
ADD CONSTRAINT_CHECK CHECK( SUBSTR(FacultyCode,1,1) = 'F');
wrong input that does not sutisfy the constraint.
INSERT INTO FACULTY(FacultyCode, FacultyName, DateOfJoin, DeptCode)
VALUES ('f902', 'R. Biswas','2018-06-15','BIO');
output
CHECK constraint failed: FACULTY: INSERT INTO FACULTY(FacultyCode,
FacultyName,
DateOfJoin, DeptCode) VALUES ('f902', 'R. Biswas','2018-06-15','BIO')
7. Alter the table Faculty and add check constraint such DeptCode is either
CSE,IT, BIO,NS
CODE
ALTER TABLE FACULTY
ADD CHECK (DeptCode IN ('CSE','IT','BIO','NS'));
8. Add constraint : DeptCode of Faculty is foreign key and references DeptCode
in Department.
Maulana Abul Kalam Azad University of Technology, West Bengal
Code
ALTER TABLE FACULTY
ADD FOREIGN KEY (DeptCode) REFERENCES Department(DeptCode);
Output
CREATE TABLE `faculty` ( `FacultyCode` varchar(4) NOT NULL,
`FacultyName` varchar(15) NOT NULL, `DateOfJoin` date NOT NULL,
`DeptCode` varchar(4) NOT NULL, PRIMARY KEY (`FacultyCode`), KEY
`DeptCode` (`DeptCode`), CONSTRAINT `faculty_ibfk_1` FOREIGN KEY
(`DeptCode`) REFERENCES `department` (`DeptCode`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
9. Add Constraint: HOD of Department table is foreign key and references
FacultyCode of Faculty.
10. Find the names of faculties of CSE Department.
Code
SELECT facultyName FROM faculty WHERE DeptCode= 'cse';
Output
11. Find the number of faculties in the IT department
Code
SELECT COUNT( * ) as "Number of IT Faculty"
FROM faculty
WHERE DeptCode='IT';
Output
Maulana Abul Kalam Azad University of Technology, West Bengal
12. Show the names of the heads of departments with department name.
CODE
SELECT HOD, DeptName, FacultyName
FROM DEPARTMENT
FULL JOIN FACULTY ON FacultyCode=HOD
OUTPUT
13. Find the number of faculties who joined in August.
14. Add an extra attribute to the faculty table - Salary Number(8,2)
Code
ALTER TABLE Faculty ADD COLUMN SalaryNumber DECIMAL (8,2) not
null;
Output
Maulana Abul Kalam Azad University of Technology, West Bengal
15. Insert values into the corresponding field Salary Number(8,2) (Enter
distinct values).
16. Find the Department having more than one faculty.
17. Find the name, department of the faculties who earn between 8000 and
12000.
18. Find the name of the department with maximum faculties.
19. Find the senior most faculty.
20.Find the name of the faculty who has completed 5 years.
