[Solved] DBS Lab2-Student database

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