CHAPTER- RELATIONAL DATABASE & SQL
Q-Consider the tables STUDENT and STREAM given below:
Table: STREAM Table: STUDENT
scode sname rno adno name class section fees scode
S01 Science 1 111 Anu Jain 12 A 2500 S01
S02 Commerce 2 222 Mohit Sharma 11 B 4500 S02
3 333 K.P.Gupta 12 B 3000 S02
S03 Humanities
4 444 Ajit Kumar 10 A 2000 S04
S04 General
5 555 Nandini 12 C 3000 S03
6 666 Rohan Sharma 11 B 2500 S02
Write SQl commands for the following:
1.Create a database school.
Create database school;
2.Change the database to school
Use School;
3.Create a table STUDENT & STREAM with the specified structure and constraints:
Table: Stream
Field Datatypes Size Constraint
scode char 3 PRIMARY KEY
sname varchar 15
TABLE:STUDENT
Field Datatype Size Constraint
rno integer 2 PRIMARY KEY
adno integer 3 UNIQUE, NOT NULL
name varchar 20
class integer 2
section char 1
fees float 6, 2
scode char 3 FOREIGN KEY
Create table stream
(scode char(3) PRIMARY KEY,
sname varchar(15));
Create table student
( rno int(2) PRIMARY KEY,
adno int(3) UNIQUE NOT NULL,
name varchar(20),
class int(2),
section Char(1),
fees float(6,2),
scode char(3),
FOREIGN KEY (scode) REFERENCES stream(scode));
4.To display the structure of table student.
Describe student
5.To display the structure of table stream.
Describe stream
6.Insert records in both the tables.
Insert into stream values(‘S01’,’Science’);
Insert into stream values(‘S02’,’Commerce’);
Insert into stream values(‘S03’,’Humanities’);
Insert into stream values(‘S04’,’General’);
Insert into student values(1,111,’Anu Jain’,12,’A’,2500,’S01’)
Insert into student values(2,222,’Mohit Sharma’,11,’B’,4500,’S02’)
Insert into student values(3,333, ‘K.P.Gupta’,12,’B’,3000,’S02’)
Insert into student values(4,444, ‘Ajit Kumar’,10,’A’,2000,’S04’)
Insert into student values(5,555,’Nandini’,12,’C’,3000,’S03’)
Insert into student values(6,666,’Rohan Sharma’,11,’B’, 2500,’S02’)
7. To display the details of all students from the student table.
Select * from student;
8. To display adno, name of all students.
Select adno, name from student;
9. To display the details of all students who are in class 12.
Select * from student where class=12;
10. To display the details of all students who are in not in class 10
Select * from student where class!=10;
11. To display the details of all students who are in class 12 and section is ‘A’.
Select * from student where class=12 and section=’A’
12. To display the details of all students who are in class 11 or 12;
Select * from student where class=11 or class=12;
13. To display the details of all students who are in not in class 11;
Select * from student where not class=11;
14. To display the details of all students whose fees are greater than or equal to 3000 and less than or
equal to 4500.
Select * from student where fees >=3000 and fees<=4500;
15. To display the details of all students who are in class 10 or 11;
Select * from student where class in (10,11);
16.To display the roll numbers and names of all students who are neither in Class 10 nor in Class 11.
Select rno, name from student where class not in (10,11);
17. To display the names of all students whose fees are between 2000 and 3000.
Select name from student where fees between 2000 and 3000;
18.To display the names of all students whose fees are not between 2000 and 3000.
Select name from student where fees not between 2000 and 3000;
19. To display the admission number, name, class and annual fees for all students.
Select adno, name, fees*12 from student;
20. To display the admission number, name, and annual fees of all students in class 12 .
Select adno, name, class, fees*12 “Annual Fees” from student where class=12;
21.To display different types of classes without repetition.
Select distinct class from student;
22. To display the details of all students whose section is not assigned or is null.
Select * from student where section is null;
23.To display the details of students whose names begin with the character 'A'
Select * from student where name like ‘A%’;
24. To display the details of students whose names ends with the character 'a'
Select * from student where name like ‘%A’;
25. To display the details of students whose names contain the characters 'ar’
Select * from student where name like ‘%ar%’;
26.To display the roll numbers and names of students whose names contain the letter 'o' in the second
place.
Select rno, name from student where name like ‘_o%’ ;
27.To display the admission number, name and class of students whose names consist of exactly seven
letters.
Select adno, name, class from student where name like ‘_ _ _ _ _ _ _’
28.To display the admission number, name and annual fees of all students.
Select adno, name, fees*12 as "Annual Fees" from student;
29. To display student details based on their names in ascending order.
Select * from student order by name;
30. To display the roll numbers, names, and classes of all students in descending order of their class and
ascending order of their names.
Select rno, name, class from student order by class desc, name asc.
31. To count the total number of records in the student table.
Select count(*) from student.
32. To count the total number of values present in the fees column of the student table.
Select count(fees) from student
33. To count the total number of classes in the student table without repetition.
Select count(distinct class) from student;
34. To count the total number of students of class 12 where section = 'B'.
Select count(*) from student where class=12 and section=’B’
35.To find the sum of the fees of Class 11 students.
Select sum(fees) from student class=11.
36. To find the maximum and minimum fees in the students’ table.
Select max(fees),min(fees) from student;
37. To find the average fees of all students whose section is either A or B.
Select avg(fees) from student where section in(‘A’,’B’);
38. To display the total number of records for each class of students.
Select class, count(*) from student group by class;
39. To display the total number of records for each class of students where the number of records is less
than 3.
Select class, count(*) from student group by class having count(*) <3;
40.To display the maximum fees, minimum fees, and the sum of fees for each class where the total sum
exceeds 7000.
Select class, max(fees), min(fees), sum(fees) from student group by class having sum(fees)>7000;
41. To display the maximum and minimum fees for each class of 11th and 12th grade students.
Select class, max(fees),min(fees) from student where class in(11,12) group by class;
42. To display rno,name,sname of students from class 12.
Select adno, name, sname from student A, stream B where A.scode=B.scode and class=12;
43. To display scode,name,class,sname of students.
Select student.scode,name,sname from student,stream where student.scode=stream.scode;
44. To display scode,name,class,sname of students whose fees between 2000 and 5000
Select A.scode,rno,name,sname from student A,stream B where A.scode=B.scode and
fees between 2000 and 5000;
