1 votes 1 votes Class(name, meets_at, room, fid) Faculty(fid, fname, deptid) Find the names of faculty members who teach in every room in which some class is taught Databases sql databases query + – aditi19 asked May 18, 2019 edited May 20, 2019 by aditi19 aditi19 1.2k views answer comment Share Follow See all 5 Comments See all 5 5 Comments reply srestha commented May 18, 2019 reply Follow Share This could be done by division operation in RA 0 votes 0 votes aditi19 commented May 18, 2019 reply Follow Share im trying division operation only 0 votes 0 votes srestha commented May 19, 2019 reply Follow Share In RA it will be $\prod _{fname}\left ( Class\bowtie Faculty \right )\div \prod _{name}\left ( Class \right )$ 0 votes 0 votes aditi19 commented May 20, 2019 i edited by aditi19 May 20, 2019 reply Follow Share found a good resource https://www.geeksforgeeks.org/sql-division/ implementation 2 has been used in most of the solution manuals of standard textbooks implementation 1 is a bit confusing so I tried to modify it a bit. please correct me if I'm wrong R(x, y) ÷ S(y) select * from R where x NOT IN (select x from (select x, y from (select distinct x from R CROSS JOIN select y from S) EXCEPT (select x, y from R) ) ) 0 votes 0 votes srestha commented May 20, 2019 reply Follow Share taking table , we need to verify it 0 votes 0 votes Please log in or register to add a comment.
0 votes 0 votes https://gateoverflow.in/?qa=blob&qa_blobid=16701151949787819818 nitin_kumar answered May 19, 2019 nitin_kumar comment Share Follow See 1 comment See all 1 1 comment reply srestha commented May 19, 2019 reply Follow Share where is table?? 0 votes 0 votes Please log in or register to add a comment.
0 votes 0 votes https://gateoverflow.in/?qa=blob&qa_blobid=5814885073351823572 nitin_kumar answered May 19, 2019 nitin_kumar comment Share Follow See all 0 reply Please log in or register to add a comment.
0 votes 0 votes Faculty table: Classroom table: Result table: SQL query : select fname from faculty NATURAL JOIN classroom group by faculty.fid having count(fid)=(select count(distinct room) from classroom); abhiarns answered Mar 20, 2020 abhiarns comment Share Follow See all 4 Comments See all 4 4 Comments reply abhiarns commented Mar 20, 2020 reply Follow Share @srestha Please check this answer.. Is it correct? Tables given in book : Class( name: string, meets_at: time, room: string, fid: integer) Faculty (fid: integer, fnarne: string, deptid: integer) I assumed all rooms are utilised ( there is no NULL room ). 0 votes 0 votes srestha commented Mar 20, 2020 reply Follow Share I assumed all rooms are utilised there may be some room, where no class has taught, like store room 0 votes 0 votes abhiarns commented Mar 20, 2020 reply Follow Share @srestha oh. More than one class can be taken in a room (at different times ) and there is meets_at (Class start time) and fid so i thought that all rooms are for teaching purposes only. I will think of a different solution then and update the answer. 0 votes 0 votes ninja_hattori commented Apr 1, 2020 reply Follow Share You have missed the case in which same faculty teaches more than one topic in the same room at different times. Example: name fid room DS-Theory 7 1105 Algo-Theory 7 1105 According to your query, faculty name with fid=7 will also come into the result but it should not. So taking the above case into consideration, I think below SQL query should be the correct one: SQL Query select fname from faculty NATURAL JOIN classroom group by faculty.fid having count(distinct room)=(select count(distinct room) from classroom); Please correct, If I am mistaken. 0 votes 0 votes Please log in or register to add a comment.