Assignment No: 3
1. Select all the students who are active, sorted by studentid in descending order.
Select * from Student as s inner join StudentStatus as ss on s.StatusId = ss. StatusId where
s.StatusId=1 or s.StatusId-=4 order by StudentId desc;
2. Create a copy of the student table named student_copy only having records for CSE department
students using SELECT INTO clause.
select * into student_copy from Student where DeptId=1;
select * from student_copy as sc inner join Department as d on sc. DeptId = d.DeptId;
3. Select the count of students in each department only when the count is more than 2.
select s.DeptId, d. DeptName, count (s. StudentId) as 'TotalStudent' from Student as s inner join
Department as d on s. DeptId = d. DeptId group by d. DeptName, s.DeptId having
count(s.StudentId) > 2;
4. Insert two different sets of records in student and student_copy table.
insert into Student values (11, 'Hari', 'Kolkata', 9825963174, 'hari.d@gmail.com', '1998-06-04', '2017-
08-15", "2022-05-06", "Kolkata',1,2,1,3);
insert into Student values (12, 'Sima', 'Delhi', 8693175627, 'sima.m@gmail.com', '2000-12-25",
"2018-08-27", "2822-05-22", "Kolkata",3,1,4,4);
insert into student copy values (9, "Shivam', 'Mumbai', 9825796348, 'shivam.s@gmail.com', '1999-
04-30', '2018-08-01","2023-05-16", "Kolkata", 1,2,1,3);
insert into student copy values (13, "Anisha', 'Kolkata", 9433677259, 'anisha.a@gmail.com', '1998-
10-25', '2018-08-07', '2022-05-12', 'Delhi",1,1,4,4);
select * from Student
select * from student_copy;
5. Select the records from student table which are not present in student_copy table.
select * from Student except select * from student_copy;
6. Drop all the five tables created so far ie, Student, Department, Course, Batch, StudentStatus.
drop table Student;
drop table Department;
drop table Course;
drop table Batch;
drop table StudentStatus;
7. Create the Student table again with a new column AadharId NUMERIC(16,0). StudentId and
AadharId should be declared as NOT NULL. Create Primary key on studentid column and unique
key on AadharId column.
create table Student (StudentId int not null, StudentName varchar(100), StudentAddress
varchar(200), Phone numeric(10,0), Email varchar(50), DateOfBirth date, DateOfAdmission date,
DateOfPassing date, PresentAddress varchar(200), DeptId int, CourseId int, BatchId int, StatusId int,
Aadhar Id numeric (16,0) not null, Constraint pk primary key (StudentId), Constraint uk unique
(AadharId));
8. Create the below four tables again with primary key constraint on the id columns:
a. Department – DeptId
b. Course – CourseId
c. Batch -- BatchId
d. StudentStatus – StatusId
create table Department (DeptId int not null, DeptName varchar(50), Constraint pk1 primary key
(DeptId) );
create table Course (CourseId int not null, CourseName varchar(50), Constraint pk2 primary key
(CourseId) );
create table Batch(BatchId int not null, BatchName varchar(50), Constraint pk3 primary key
(BatchId));
create table StudentStatus (StatusId int not null, StatusDesc varchar(50), Constraint pk4 primary key
(StatusId) );
9. Insert 4 records in each of the four tables.
insert into Department values(1, 'CSE');
insert into Department values (2, 'ECE');
insert into Department values (3, 'EE');
insert into Department values (4, 'CE');
insert into Course values (1, 'B.Tech-cse');
insert into Course values (2, 'B.Tech-ece');
insert into Course values (3, 'B.Tech-ee');
insert into Course values (4, 'B.Tech-ce');
insert into Batch values (1, 'BCS3A');
insert into Batch values (2, 'BCS3B');
insert into Batch values (3, 'BCS3C');
insert into Batch values (4, 'BEC3');
insert into StudentStatus values (1, 'active');
insert into StudentStatus values (2, 'left');
insert into StudentStatus values (3, 'completed');
insert into StudentStatus values (4, 'active');
select * from Department;
select * from Course;
select * from Batch;
select * from StudentStatus;
10. Insert 10 records in Student table.
insert into Student values 1, 'Gita', 'Kolkata", 9876543210, 'gita.d@gmail.com', '1999-07-14', '2018-
08-04', '2023-05-26', 'Kolkata',1,2,2,3,4789651230523951);
insert into Student values (2. "Soumya', 'Patna', 9836789245, 'soumya.b@gmail.com', '1998-03-25',
'2018-08-11', '2022-05-12', 'Kolkata', 4,1,2,3,4789657527941643);
insert into Student values (3, 'Debasmita', 'Kolkata', 8017471283, 'debs.m@gmail.com ', '1998-11-
30', '2017-08-13', '2023-05-28', 'Delhi', 4,1,3,1,4789657896512345);
insert into Student values 4, 'Rahul', 'Delhi', 9433822162, 'rahul.k@gmail.com', '2000-12-24', '2019-
08-23', '2023-05-08', 'Mumbai,1,1,2,3,5896471230523951);
insert into Student values (5, 'Nikhil', 'Mumbai', 9434066528, 'nikhil.m@gmail.com', '1998-01-12',
'2017-08-04', 2022-05-06', 'Kolkata', 1,2,4,6,4712365490523951);
insert into Student values (6, 'Trisha', 'Kolkata', 8697602559, 'trisha.s@gmail.com', '1999-10-23',
'2018-08-18', '2022-05-09', 'Delhi', 2,1,3,9, 7896541230523951);
insert into Student values (7, 'Sumit', 'Bhopal', 8902677851, 'sumit.g@gmail.com', '2000-09-01',
'2018-08-25', '2023-05-19', 'Kolkata' 3,2,1,2,4789657412589630);
insert into Student values (8, 'Piyali', 'Kolkata', 9828367419, 'piyali.d@gmail.com', '1999-06-28',
'2018-08-31', '2023-05-13', 'Kolkata',1,1,4,4,7412961230523951);
insert into Student values (9, 'Reshmi', 'Jaipur', 9433772596, 'reshmi.a@gmail.com', '1998-04-14',
'2017-08-29', '2023-05-17', 'Delhi', 4,2,3,1,5896743621023951);
insert into Student values (10, 'Deepak', 'Mumbai', 9878963182, 'deepak.r@gmail.com', '2000-05-
27', '2017-08-14', '2023-05-30', 'Mumbai',2,2,4,5,4789651789641234);
select * from Student;
11. Alter the Student table to add a new column named studenttype CHAR(1). It should be Not
NULL and default value is ‘R’. Possible values are ‘R’ , ‘P’ and ‘C’.
alter table Student add studenttype char(1) not null constraint default_val default('R');
select * from Student
12. Create a Check constraint on studenttype column in Student table to restrict the values only to
‘R’, ‘P’ and ‘C’.
alter table Student add constraint ckh_val check (studenttype in ('R', 'P', 'C'));
update Student set studenttype='C' where StudentId=3;
select * from Student
