ORACLE DATABASE QUESTIONS

Follow these basic instructions for all questions as applicable:

If a concatenated patient name is used, FORMAT A18

If a concatenated employee name is used, FORMAT A18

If a description field is used (from any table), FORMAT A25

Write a query to produce a result table that is a Cartesian product (i.e., dont join the tables) of the bed and bedClassification tables. The result table should include the BedNumber and Availability columns from bed and the Description column from bedClassification. You DONT need to show all output- but the final line with the number of rows should be shown.

The Cartesian product produced in question 1 is not terribly useful. Alter the query for question 1 to restrict row output by joining the two tables on the BedType column of each table. Write two queries, one that joins the tables with the FROM clause and one that joins the tables with the WHERE clause. Do not sort the output.

The chief of surgery requires a listing of services provided by service category. Your query uses the service and serviceCategory tables. Display four columns as follows: Service.CategoryID, serviceCategory.Description, service.ServiceID, and service.Description. Use headings of Category, Category Description, Service, and Service Description respectively. Sort the output by service.CategoryID, then by service.ServiceID.

Produce a query that will list all employee last names, employee gender, dependent names and dependent gender where the employee's have dependents of the opposite gender. Also list the dependent relationship. The columns needed in the result table are LastName, employee.Gender, dependent.Name, dependent.Gender, and RelationshipToEmployee. Use the employee and dependent tables. Use the FROM clause to join the tables. Use the column names and formats shown below. Sort the result table by LastName.

COLUMN "Employee" FORMAT A10;

COLUMN "Emp Gender" FORMAT A10;

COLUMN "Dependent" FORMAT A10;

COLUMN "Dep Gender" FORMAT A10;

COLUMN "Relationship" FORMAT A12;

The Nursing director would like to see a list of patient names currently assigned to a room. The report needs to include the Patient First Name and Last Name (concatenated), along with the room Description they are assigned to and a description of the bedtype.

The chief of physicians requires a listing of patients receiving treatment. The result table must display the patient name (last and first concatenated from the patient table), Description column (service table), and employee name providing the treatment (last and first name concatenated from the employee table). Sort the result table by the patient last name then first name. Your query joins four tables. Format each column of the result table.