Description
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:
- Display student name, major who is the youngest of all students
- Display student name, major who is the oldest of all students
- Display student name, major who is the youngest of all students Using “LIMIT” Keyword
- Display student name, major who is the oldest of all students Using “LIMIT” Keyword
- Display students records whose major starts with “D” or ends with “N”
- Display student records in alphabetical order
- Determine faculty id, dept id , who has highest salary
- Increment salary of faculty by 20%
- Display faculty id and fname who are from computers department or salary >
=50000 in ascending order of there salary
- Display student records who are from “S.E” major and taking DBMS course in alphabetical order
- Display student id, name whose name starts with “S” or ends with ”A”
- Find all student records containing the word “ha” .
- Display senior students from “sofware engineering”
- Display records of students who took a course on DBMS
- Determine the faculty id and the department id where a faculty teaches courses in room H05
- Display Average salary of the faculty from Computers department
- Determine courseid, course name in which maximum students are enrolled
- Determine course id,course name in which minimum students are enrolled
- Determine faculty id, faculty name who is offering more number of courses
- Find the names of all Juniors (level = JR) who are enrolled in a class taught by ‘Jagadessh.K’.
- Display the names of all online courses a particular faculty
