Lab Assignment #1
Task 1
Schema:
Student (sid, sname, level, age, sex)
sid: primary key
age : Not NULL
1. Create the above table Student.
2. Insert five records in it.(Assume it
yourself)
3. Display all records.
4. Add new column contactno
5. Remove column level.
6. Add new column level
7. Change the datatype varchar to char in
sname.
8. Put the values in contactno.
1
9. Delete the record of amit who left the
college recently.
10. Delete all records
11. Remove the student table.
12. Again, create the table Student (sid,
sname, level, age, sex)
Sid: primary key
Age : Not NULL
Level: Default “ Graduate”
Age<=20
2
create table student
( sid int Primary key,
sname varchar(15),
level varchar(20),
age int not null,
sex varchar(8));
desc student;
2. insert into student values
( 05,'Amit','Graduate',19,'male');
3. select * from student;
3
4. alter table student add contactno integer;
select * from student;
5. alter table student drop level;
select * from student;
6. alter table student add level varchar(20);
select * from student;
7. alter table student modify sname char(20);
desc student;
4
8. update student
set contactno=9841567567;
[ note: for all , but due to limited byte in
integer, real values cannot be display so modify
the datatype integer to varchar[15]]
alter table student modify contactno
varchar(15);
update student
set contactno=9851989878;
select * from student;
update student
set contactno='9891989878'
where sid=2;
select * from student;
9. delete from student where sname='Amit';
5
select * from student;
10. delete from student;
select * from student;
11. drop table student;
select * from student;
[Error Code : 1146
Table 'dina1.student' doesn't
exist
(0 ms taken)]
13. create table student
( sid int Primary key,
sname varchar(15),
level varchar(20)default 'Graduate',
age int not null,
sex varchar(8),
6
check (age<=20)
);
7
Lab Assignment #2
Task 1
Create the following tables:
Student(sid, sname, level, age, sex)
Instructor( iid,lname, age, sex)
Course(cid, cname, credits_hours)
Enrollby(sid,cid)
Taught_by(iid,cid)
Task 2
Insert the datas in the tables (book page no 92)
Task 3
Write the SQL statement for the following
Queries
1. Print Or Display or Find all the records
of the student.
2. Print Or Display or Find all the records
of the instructor.
8
3. Print Or Display or Find all the records
of the course.
4. Find the name and level of the female
student over the age 22.
5. Find the name of the instructor whose
age is less than 28.
6. Find id, name, level and age of student
by increasing age by 1.
7. List the name of the instructors whose
age is greater than 30.
8. Find the name of the student who study
SAD.
9. Find the Name and age of those student
whose age is greater than 25 and sex is
male.
10. Increase 50% credit _hours to all
courses.
11. Display the name of those students
whose level is unknown.
9
12. List the students who are not enrolled in
any course.(hint: Select * from student
natural join enrolled by natural join course
where cid is NULL).
13. List all the course names of 3 credit
hours.
14. Update the level of student Nirab with
Postgraduate.
15. Delete the instructor Sweta from the
instructor relation.
16. Find id, name of all instructors whose
course of 3 credit hours.
17. Find id and name of all instructors in the
order of sex and then in descending order
of age.
18. Find id and name of all undergraduate
students in ascending order of name.
19. Find record of all courses taught by
instructor 302.
10
20. Find id and name of students who
enrolled in course csc-403.
21. Find id ,name ,level of all students
whose age is greater than at least one
student.(page no 108-109)
22. Find average age of all undergraduate
students.
23. Find average age for all levels of
students.
24. Display the name of instructor with
maximum age.
25. Find number of courses taught by each
male instructors.
26. Find average age for all levels of student
that have average age more than 20.
27. Add new column ‘addresses’ to student
table.
28. Remove column ‘addresses’ from
student table.
11
29. Modify student relation by changing
data type of sname to varchar(30);
30. Create view that contains id, name,
level of those students whose age is greater
than 24.
31. Remove the view table just created.
Answer for assignment 2
Task 1:
create table student( sid integer primary key,
snamevarchar(15),level varchar(15),age
integer, sex varchar(8));
create table instructor(iid integer primary key,
inamevarchar(15),age integer, sex varchar(15));
create table course( cidvarchar(6) primary key,
cnamevarchar(15),credit_hours integer);
create table enrolled_by( sidinteger,cidint,
foreign key( sid) REFERENCES
12
student(sid),foreign key(cid) REFERENCES
course(cid));
create table taught_by( iidinteger,cidint,
foreign key( iid) REFERENCES
instructor(iid) ,foreign key(cid) REFERENCES
course(cid));
Task 2:
insert into student
values(202,'Sohan','postgraduate' ,32,'male');
select * from student;
insert into instructor
values(202,'Sohan',32,'male');
select * from instructor;
13
insert into course values( 406,'UML',1);
select * from course;
[ Here , in this software data are not directly
entered in relationship tables so we have to
insert each record.]
insert into taught_by values(202,402);
select *from taught_by;
insert into enrolled_by values( 202,404);
select *from enrolled_by;
Task 3:
1. select * from student;
14
4.selectsname,level from student where
age>22;
15
6.select sid,sname,level,age+1 as newage
from student;
8. select sname from student natural join
taught_by natural JOIN course where
cname='SAD';
select sname from student natural
join course where cname='SAD';
16
10.
UPDATE course
setcredit_hours=credit_hours+credit_hours*.5;
SELECT * from course;
alter table course modify credit_hours float;
SELECT * from course;
17
Assignment 3:
1. Find ID and name of all females who are
instructor or student. Also identify the
different between union and union all.
2. Find ID,name, and age of all persons
who are instructors as well as student.
3. Find ID,name and age of all persons who
are instructors but not student.
4. Find the average age for all levels of
students.
5. Find average age for all levels of
students that have average age more than
22.
6. Select the name of instructor who
teaches computer science, and whose
names are neither raja nor ram.
7. Find id, name and age of all instructor
whose age is greater than age of all
students.
18
8. Increase credit hour of courses by 5
whose credit hour is more than 3 otherwise
increase credit hour by 1.
19
