Consider a database system with following schemas; Hospital(hname, haddress, hspecilaity) Doctor(did, dname, dspecilization, ) Worksat(did, hname, workinghrs) Pharmacy(phname, hname, no_of_sales, total_revenue) Now write SQL statements and relational algebra statements for following queries: a. Sele
Consider a database system with following schemas; Hospital(hname, haddress, hspecilaity) Doctor(did, dname, dspecilization, ) Worksat(did, hname, workinghrs) Pharmacy(phname, hname, no_of_sales, total_revenue) Now write SQL statements and relational algebra statements for following queries: a. Select name of all doctors having specialization 'gyno', b. Select the name and address of hospital where working hours is 'day', c. Using natural join select the name of doctors whose working hours are 'night', d. Find the average salary of the doctors, e. Find names of hospital and their pharmacy which generate revenue more than 10000. Sort the result in descending order on the basis of pharmacy name.[10]
--- SQL: Relational Algebra: First apply selection on dspecialization = 'gyno', then project dname. --- SQL: Relational Algebra: Join Hospital and Worksat on hname, apply selection on workinghrs = 'day', then project required attributes. --- SQL: Note: Natural join automatically joins on the comm...