DBS-Lab 2 Creating a Student Database Solved

Create a student database with following Tables: 

Student(snum: integer, sname: string, major: string, level: string, age: integer)  

Class(coursename: string, meets_at: time, room: string, fid: integer)  

Enrolled(snum: integer, coursename: string)  

Faculty (fid: integer, fname: string, deptid: integer,Salary:integer) 

 

Level includes FR(Fresher), SO(Sophomore), JR(Junior) and SR(Senior). 

Major denotes the specializations of the department a student is enrolled in. For e.g. COE department can have Software Engineering, Human Computer Interaction, Data Science, Theoretical Computer Science as its majors. Enrolled has one record per student-class pair such that the student is enrolled in the course 

A student may attend courses assigned in different rooms. Assume that a faculty can also teach an online video lecture course(e.g. couresera, educity) in a class room without students being enrolled in it. So, a faculty can teach maximum 5 courses. 

 

 

Write sql queries for the following questions: 

 

1.     Display student name, major who is the youngest of all students

2.     Display student name, major who is the oldest of all students

3.     Display student name, major who is the youngest of all students Using “LIMIT” Keyword

4.     Display student name, major who is the oldest of all students Using “LIMIT” Keyword  

5.     Display students records whose major starts with “D” or ends with “N”

6.     Display student records in alphabetical order

7.     Determine faculty id, dept id , who has highest salary

8.     Increment salary of faculty by 20%  

9.     Display faculty id and fname who are from computers department or salary

=50000 in ascending order of there salary  

10. Display student records who are from “S.E” major and taking DBMS course in alphabetical order

11. Display student id, name whose name starts with “S” or  ends with ”A”

12. Find all student records containing the word "ha" .

13. Display senior students from “sofware engineering”   

14. Display records of students who took a course  on DBMS

15. Determine the faculty id and the department id where a faculty teaches  courses in room H05

16. Display Average salary of the faculty from Computers department   

17. Determine courseid, course name  in which maximum students are enrolled

18. Determine  course id,course name  in which minimum students are enrolled

19. Determine faculty id, faculty name who is offering more number of courses

20. Find the names of all Juniors (level = JR) who are enrolled in a class taught by ‘Jagadessh.K’.

21. Display the names of all online courses a particular faculty