SQL Tables And Queries (Database Development, MySQL, Oracle, Oracle Forms, Oracle PL/SQL)

SQL Tables And Queries

GetACoder Blog Articles

Job Summary:

Job Type:	Question
Budget:	$ 20-300
Required Skills:	Database Development, MySQL, Oracle, Oracle Forms, Oracle PL/SQL
Attached Files:	(None)

Status:	Closed for bidding

Buyer Summary:

Username:

Feedback Score:

Rating: (No Feedback Yet)

Award Reliability:

4 posted 0 paid

Location:

Jalandhar, Punjab, India

Member Since:

September 30, 2010

Invited Users:

There are no invited users

Description

Hello,
I need Create Table statement from the following:

Medcruise run cruises on a range of large ships around the Mediterranean region. These cruises have a name, a duration of 2,3,4,or 5 days, and a cost that varies between £600 and £5000. Each cruise is repeatedly run on different dates along the same standard route that starts at one of four different ports (Athens, Rome, Naples and Nice). These runs are called voyages, and the start date of each voyage, its starting location and its ship are recorded. Customers book on to voyages and Medcruise need to record the customer name, gender, telephone number, and address. Each time a customer takes a cruise, their cabin number and class (First, Second, Third) is recorded. Customers will also give an evaluation of the cruise (the evaluation is a number from 0 to 4). Medcruise need to record the evaluation from each customer each time they take a cruise. It is not a requirement that all voyages have passengers registered on them, and it is also not required that every customer has to have participated in a cruise. Each ship has a name, a port of registration and gross tonnage (between 25,000 and 60,000 tonnes).

Also, once these tables are created. the following queries should be run:

a. Find details of all cruises starting at Athens
b. Find the cruise that runs the most number of times.
c. Find the total number of passengers on all voyages of each cruise
d. Show details of the cruises along with the total revenue that they each generate
e. Show details of customer names and the names of the cruises in which
they have participated
f. Write and test a query to list the name and cost of each cruise. Add a column that compares the cost of the cruise to the average cost i.e., shows the difference between the cruise cost and the average cost of all cruises.
g. List all customers who have not been a passenger on a voyage of a cruise starting at Naples.
Hint : With this query it is extremely important to work back and check your output against your test data.

h. Find the voyages of cruises that have an occupancy below the average occupancy for voyages of that cruise.
i. Write and test a query to list the cruise ID and name of EVERY cruise, and for those cruises that have voyages starting within the past 200 days, also include starting date, ending date, and location name for those voyages in the same single query output.
Hint : Remember to include details (cruise ID and name ) of any cruise that does not have voyages within the past 200 days.

j. Find names of all female customers who have participated in a cruise in 2006.
k. Find full details of the ships that are allocated to less than two voyages. (Hence, include those ships that have not taken part in any voyage as yet).

Please let me know if anyone can do this job, I need its solution as soon as possible.

Thanks

Reminder

You may not start working in this and any request before your bid is accepted. Users who violate this policy may have their accounts permanently suspended.

Bids Received (19)

Shortlist (0)

Declined Bids (0)

Average bid amount:

$204.21

Average delivery time:

7 Day(s)

| | |

Order by:

Remember that contacting the other party outside the site (by email, phone, etc.) on all business jobs (before the request is awarded) is a violation of our terms of use. We supervise all site activity for such infringements and can immediately expel transgressors on the spot, so we thank you in advance for your cooperation. If you notice a violation please help out the site and report it. Thank you for your help.