Find the start and end dates of cruise number

Assignment Help Database Management System
Reference no: EM131373963

EXERCISES

1. Consider the following relational database that Best Airlines uses to keep track of its mechanics, their skills, and their airport locations. Mechanic number (MECHNUM), airport name (AIRNAME), and skill number are all unique fields SIZE is an airport's size in acres. SKILLCAT is a skill category, such as an engine skill, wing skill, tire skill, etc. YEARQUAL is the year that a mechanic first qualified in a particular skill; PROFRATE is the mechanic's proficiency rating in a particular skill.

MEXHANIC Table

MECHNUM

MECHNAME

AGE

SALLARY

AIRNAME

AIRPORT Table

AIRNAME

CITY

STATE

SIZE

YEAROPENED

SKILL Table

SKILLNUM

SKILLNAME

SKILLCAT

QUALIFICATION Table

MEXHNUM

SKILLNUM

YEARQUAL

PROFRATE

Write SQL SELECT commands to answer the following queries.

a. List the names and ages of all the mechanics.

b. List the airports in California that are at least 20 acres in size and have been open since 1935. Order the results from smallest to largest airport.

c. List the airports in California that are at least 20 acres in size or have been open since 1935.

d. Find the average size of the airports in California that have been open since 1935.

MINICASES

1. Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A "cruise" is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2011, is a cruise. Note the following facts about this environment.

• Both ship number and ship name are unique in the SHIP Table.

• A ship goes on many cruises over time. A cruise is associated with a single ship.

• A port is identified by the combination of port name and country.

• As indicated by the VISIT Table, a cruise includes visits to several ports and a port is typically included in several cruises.

• Both Passenger Number and Social Security Number are unique in the PASSENGER Table. A particular person has a single Passenger Number that is used for all the cruises she takes.

• The VOYAGE Table indicates that a person can take many cruises and a cruise, of course, has many passengers.

SHIP Table

SHIPNUM

SHIPNAME

BUILDER

IAUNCHDAIE

WEIGHT

CRUISE Table

CRUISENUM

STARTDATE

ENODATE

DIRECTOR

SHIPNUM

PORT Table

PORTNAME

COUNTRY

NUMDOCKS

MANAGER

VISIT Table

CRUISENUM

PORTNAME

COUNTRY

ARRDATE

DEPDATE

PASSENGER Table

PASSENGERNUM

PASSENGERNAME

SOCSECNUM

STATE

COUNTRY

VOYAGE Table

PASSENGERNUM

CRUISENUM

ROOMNUM

FARE

Write SQL SELECT commands to answer the following queries.

a. Find the start and end dates of cruise number 35218.

b. List the names and ship numbers of the ships built by the Ace Shipbuilding Corp. that weigh more than 60,000 tons.

c. List the companies that have built ships for Happy Cruise Lines.

d. Find the total number of docks in all the ports in Canada.

e. Find the average weight of the ships built by the Ace Shipbuilding Corp. that have been launched since 2000.

f. How many ports in Venezuela have at least three docks?

g. Find the total number of docks in each country. List the results in order from most to least. It Find the total number of ports in each country.

i. Find the total number of docks in each country but include only those countries that have at least twelve docks in your answer.

j. Find the name of the ship that operated on (was used on) cruise number 35218.

k. List the names, states and countries of the passengers who sailed on The Spirit of Nashville on cruises that began during July, 2011.

l. Find the names of the company's heaviest ships.

m. Find the names of the company's heaviest ships that began a cruise between July 15, 2011 and July 31, 2011.

Reference no: EM131373963

Questions Cloud

Female skunk of genotype : A male skunk of genotype FG/fg mates with a female skunk of genotype fg/fg. What genotypes of baby skunks should they produce, and in what ratios?
Corruption in country and economic growth : What is the relationship between corruption in a country (i.e., government officials taking bribes) and economic growth? Is corruption always bad?
Why do most heat pumps have two metering devices : What keeps both metering devices from being in the refrigeration circuit at the same time?
What is the meaning of the story title : Shortly after writing Trifles, Susan Glaspell rewrote it as a short-story, "A Jury of Her Peers." What is the meaning of the story's title? What does it imply about who is fit to judge someone
Find the start and end dates of cruise number : Write SQL SELECT commands to answer the following queries. Find the start and end dates of cruise number 35218. List the names and ship numbers of the ships built by the Ace Shipbuilding Corp. that weigh more than 60,000 tons.
What is the difference between a cop and an hspf : Why are the coils in an air-source heat pump referred to as the outdoor coil and indoor coil instead of the condenser and evaporator?
Which investment would you favor and why : Both investments promise the same long-run return, so your choice is driven by risk considerations. Assess the various risks of doing business in each of these nations. Which investment would you favor and why?
Comprehensive understanding of meteorology : Explain how a comprehensive understanding of meteorology has moral implications for pilots and how this relates to biblical principles. Of all of the gasses that compose the troposphere, which one is the most influential for weather? Why?
What is the difference between an eer and a seer : Why are accumulators and/or charge compensator tanks necessary in air-source heat pumps?

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd