2. (50 points) (SQL. Queries) r the following relational database schema that re
ID: 3755444 • Letter: 2
Question
2. (50 points) (SQL. Queries) r the following relational database schema that records information about student registrations. The primary key for each relation is underlined. Students( string, stuName: string, status: string, age: integer, GPA: float) rotessors(ProfnD:string, ProfName: string, deptName: string, age: integer, Courses Classrooms( Requires(crsNe: string, prerequisitee strin, enforcedSince: string) Classes(crsNo: string, sectNo: string, semester: string, salary: integer) string, crs Title: string, deptName: string, creditHours: string) string, seats: integer) academicYear year thook: string, classTime: string, enrollment: integer, string) maxEnrollment: integer, ProflD: string Transcript string, crsNo: string, sectNo: string, semester string. academicYear: year, grade: string) exactly one SOL statement for each question. Also, the query answers must no contain duplicates, but you should use the SQL keyword DISTINCT only when necessary Write the following queries in SQL using ANSI Equijoin. Note that you have to write Find the names of all Information Technology professors. Note that Information Technology is a department name (deptName) 1. 2. Find the names of Juniors (status JR') who receive an A for some course in Spring 2018
Explanation / Answer
Hey. Here is your solution.
If it helps, please do upvote. Thank You.
1. Select ProfName from Professors where deptName = 'Information Technology';
2.select stuName from Student INNER JOIN Transcripts ON Student.stuID = Transcripts.stuID WHERE grade = "A" AND status = "JR" AND semester = "Spring 2018";
3. Select count(*) as NoProfessor, avg(age) as averageAge from Professors group by deptName;
4. Select count(*) as NoStudents from Student INNER JOIN Transcripts ON Student.stuID = Transcripts.stuID WHERE (crsTitle = "Database Management" or crsTitle = " Data Mining") AND grade = "A" AND semester = "Spring 2018";
5. Note : This question is having ambiguity. Please elaborate in comment section below.
1. Average grade - Grade is non numeric so taking average is not possible.
2. Average grade - Can we assume average mean most secured grade?
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.