Query Languages Specify the following queries in SQL and relational algebra on t
ID: 3768443 • Letter: Q
Question
Query Languages
Specify the following queries in SQL and relational algebra on the following database schema:
Students(sid: String, sName: String, gender: String, age: Integer, year: Year, gpa: Float)
Departments(dName: String, numPhDs: Integer)
ProfWorks(pid: String, pName:String, dName: String)
CoursesOffer(cNo: String, cName: String, dName: string)
Majors(dName: String, sid: Integer)
Sections(cNo: String, semester: String, sectNo: Integer, pid: String)
Enrolls(sid: String, cNo: String, semester: string, sectNo: Integer, grade: Integer)
The domain of each attribute is listed after the attribute name. The primary key attributes are in bold and underlined.
Task 1: write the following queries in SQL. Note that you have to write exactly one SQL statement for each question. Also, the query answers must not contain duplicates, but you should use the SQL keyword distinct only when necessary.
Query 1: Find the name(s) of the oldest students.
Query 2: For each Fall 2015 course per section, retrieve its course name, highest grade and lowest grade. Note that the scale of a grade is in the range of 0 to 100.
Answer: Query 1
Answer: Query 2
Task 2: write the following query in Relational Algebra. If this query cannot be expressed in relational algebra, explain why.
Query 3: Find the name(s) of the second oldest students.
Answer: Query 3
Explanation / Answer
Query1. Select * from students order by year asc limit 50
Limit 50 tell to take only 50 students. you can inscrease the limit or limit 1 to get only 1
Query2.
select s.semester, s.sectno, cno, max(grade), min(grade) from Enrolls as E
inner join Sections as s on s.cno = E.cno and E.semester=s.semester and e.sectNo=e.sectno
inner join CoursesOffer as c on c.cNo = E.cNo
group by 1,2,3
Query3.
This cannot be done in relational algebra since rank can be duplicated in values, it is not unique, and extrating specified is tough.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.