Database Management System - Old Questions

1. Consider a database system with following schemas; [10]

        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.