Oracle Databse SQL:

A home cleaning service is building a database to help organize its service and client records. A client may choose to have just one individual job done, e.g., washing the windows, waxing the floor, steam-cleaning carpet, etc. The cleaning service also offers packages of services, which combine several services at a cheaper cost-per-hour than if they were purchased individually. For example, the "Once Over Lightly" package includes dusting, vacuuming, and washing the kitchen floor. The "My Mother's Coming" package includes the same jobs as Once Over Lightly, plus washing the windows, steam-cleaning the carpet, and polishing the furniture. For each kind of work, the database records the name of the job (e.g., dusting or "Once Over Lightly"), the cost-per-hour, and if the work item is a package or not. A package always contains more than one job, a job may be part of more than one package, but not all jobs are included in a package.

A client is someone who purchases individual jobs, packages, or both. Naturally, a client may purchase more than one, and may purchase the same job or package on different dates. Jobs and packages may be purchased by more than one client. For each client, the database should record the client_id, name, address, home-phone, work-phone, and a comment field about pets. This field may contain "none" if there aren't any, or comments such as "Dog will be shut in the basement", or "Cat is not allowed out". The database also records the size of the house in square feet, and the number of bathrooms (since these can take a while to clean).

When a client purchases a job or a package, the database records the date on which the service will do the work, and an estimate of how long the work should take, given the size of the house. For example, dusting a large house might take 2.5 hours, dusting a small house might take only 1hour.

Once you have created your database you will need to enter data to test the database. Makeup records and enter them into your tables. You should include information about at least 10 jobs, 3 packages, 5 clients, and 20 purchases. Make sure the records you enter will allow you to answer the following queries (Recall that represents an appropriate value from your database.)

a. Count the number of houses which have the size of 2,000 square feet or more.

b. What jobs does package X contain, and how much does each job cost per hour?

c. What is the amount of the discount customers will get when purchasing the package X (from the query B)? You can calculate the discount by subtracting the cost of the package from the average cost of the individual jobs included in the package. You may find it easier to answer this in 2 steps.

d. In the last month, who have purchased package X? Give complete information about the client, the date that they ordered the package, and order the query by date.

e. Create a report that lists a client's complete information. It should include complete information about the client and the time estimate of all work done forthem.

f. Give the names and addresses of clients for whom we did work on date X.

g. Assuming that the estimate for purchase order X is accurate, how much will the work cost the client? (hint: Multiply time_est * cost/hr.)