ASSIGNMENT No.
5 211003003056
QUESTION: 1. Drop and create Student and Department tables. Don’t create
Primary key, unique key, or foreign key constraints on the tables now.
QUERY:
DROP TABLE DEPARTMENT
DROP TABLE STUDENT
CREATE TABLE STUDENT(ID INT, NAME VARCHAR(20), ADDRESS
VARCHAR(20),PHONE NUMERIC(10,0), EMAIL VARCHAR(20),DOB DATE, DOA
DATE,DOP DATE, DEPTID INT);
create table DEPARTMENT(DEPTID int, DEPTNAME varchar(20));
OUTPUT:
QUESTION: 2. Create clustered index on studentId column of Student table
QUERY: CREATE CLUSTERED INDEX STUDENTINDEX ON STUDENT (ID)
OUTPUT:
QUESTION: 3. Create unique clustered index on deptId column of Department
table
QUERY: CREATE UNIQUE CLUSTERED INDEX DEPTIDINDEX ON
DEPARTMENT(DEPTID)
OUTPUT:
QUESTION: 4. Create a nonclustered index on name, address columns of the
Student table only for the students whose address is Kolkata.
QUERY: CREATE NONCLUSTERED INDEX NONINDEX ON STUDENT
(NAME,ADDRESS) WHERE ADDRESS='KOLKATA'
OUTPUT:
QUESTION: 5. Drop and recreate the nonclustered index. Email and phone
columns should be added as included columns.
QUERY: DROP INDEX STUDENT.NONINDEX
CREATE NONCLUSTERED INDEX NONINDEX ON STUDENT
(NAME,ADDRESS,EMAIL,PHONE) WHERE ADDRESS='KOLKATA'
OUTPUT:
QUESTION: 6. Insert 10 records in the Student table and 4 records in the
Department table.
QUERY: INSERT INTO STUDENT
VALUES(01,'AMIT','DELHI',1234567890,'AMIT11@GMAIL.COM',
'2002-05-09','2021-08-29','2025-09-15',101);
INSERT INTO STUDENT
VALUES(02,'NISHA','MUMBAI',1236547890,'NISHA1@GMAIL.COM',
'2003-06-03','2020-01-27','2024-07-16',102);
INSERT INTO STUDENT
VALUES(03,'RAJ','KOLKATA',9874563210,'RAJ11@GMAIL.COM',
'2000-12-12','2019-03-17','2021-11-09',103);
INSERT INTO STUDENT
VALUES(04,'PIU','GOA',1234567890,'RIYA11@GMAIL.COM','2006-04-15',
'2023-09-12','2026-10-10',104);
INSERT INTO STUDENT
VALUES(05,'RAMEN','BIHAR',9874567890,'RAM51@GMAIL.COM','2010-09-02',
'2025-04-11','2027-11-05',104);
INSERT INTO STUDENT
VALUES(07,'PIZZA','KOLKATA',1234567881,'DOMINOS1@GMAIL.COM','1997-11-12',
'2017-02-11','2019-11-07',101);
INSERT INTO STUDENT
VALUES(06,'DOSA','TAMIL',3214567881,'SOUTH4@GMAIL.COM','1999-02-17',
'2018-01-10','2020-12-04',103);
INSERT INTO STUDENT
VALUES(08,'SHAKE','KOLKATA',3214547201,'MILK9@GMAIL.COM','2005-04-17',
'2025-07-16','2021-12-04',102);
INSERT INTO STUDENT
VALUES(09,'MOMO','TAMIL',9874563210,'MOMO33@GMAIL.COM','2012-07-22',
'2025-04-25','2028-12-29', 106) ;
INSERT INTO STUDENT
VALUES(10,'LILY','DHAKA',9975473210,'LIL73@GMAIL.COM','2011-07-12',
'2025-12-25','2027-10-25', 101) ;
NSERT INTO DEPARTMENT
VALUES(101,'CSE'),(102,'CIVIL'),(103,'CSE_AI'),(104,'BIOTECH');
SELECT * FROM DEPARTMENT
OUTPUT:
QUESTION: 7. Create a view named vStudentDept to retrieve student id,
studentname and departname by joining both the tables. WITH
SCHEMABINDING option should be added.
QUERY: CREATE VIEW vStudentDept WITH SCHEMABINDING AS(SELECT
STUDENT.NAME, STUDENT.ID,DEPARTMENT.DEPTNAME FROM STUDENT
INNER JOIN DEPARTMENT ON STUDENT.DEPTID = DEPARTMENT.DEPTID);
select * FROM vStudentDept
OUTPUT:
QUESTION: 8. Retrieve Studentname and departname from vStudentDept view
only for CSE department.
QUERY: SELECT NAME,DEPTNAME FROM vStudentDept WHERE DEPTNAME='CSE';
OUTPUT:
