Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer,...

Database Design and SQL

The following relations keep track of airline flight information:

Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer)
Aircraft (aid: integer, aname : string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename : string, salary: integer)

The Employees relation describe pilots and other kinds of employees as well. Every pilot is certified for some aircraft and only pilots are certified to fly.

Based on the schemas, formulate the following SQL queries:

a. A customer wants to travel from Madison to New York with no more than two changes of flight. List the choice of departure times from Madison if the customer wants to arrive in New York by 6 p.m.

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.

Solutions

Expert Solution

Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer)
Aircraft (aid: integer, aname : string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename : string, salary: integer)
The Employees relation describe pilots and other kinds of employees as well.
Every pilot is certified for some aircraft and only pilots are certified to fly.
Based on the schemas, formulate the following SQL queries:

a. A customer wants to travel from Madison to New York with no more than two changes of flight.
List the choice of departure times from Madison if the customer wants to arrive in New York by 6 p.m.
SELECT flno, from, departs
FROM Flights
WHERE CURRTIME(arrive) == "0600"

UNION

SELECT F1.flno, F1.from, f1.departs
FROM Flights F1, Flights F2
WHERE F1.from = "Madison"
AND F2.to = "New York"
AND F1.arrive < F2.depart
AND CURRTIME(F2.arrive) == "0600"

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).
SELECT avg(salary) FROM Employee WHERE eid IN
(SELECT eid FROM Certified)
-
SELECT avg(salary) FROM Employee

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.
SELECT ename, salary FROM Employee WHERE
eid NOT IN (SELECT eid FROM Certified)
AND salary > (SELECT avg(salary) FROM Employee WHERE eid IN (SELECT eid FROM Certified))

venereology answered 1 year ago

Next > < Previous

Related Solutions

1. A cosmetic product retailer needs to create a database to keep track of the information...

1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track...

Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students' transcripts. (a) The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to...

Using c++ Design a system to keep track of employee data. The system should keep track...

Using c++ Design a system to keep track of employee data. The system should keep track of an employee’s name, ID number and hourly pay rate in a class called Employee. You may also store any additional data you may need, (hint: you need something extra). This data is stored in a file (user selectable) with the id number, hourly pay rate, and the employee’s full name (example): 17 5.25 Daniel Katz 18 6.75 John F. Jones Start your main...

JAVA - Design and implement a class called Flight that represents an airline flight. It should...

JAVA - Design and implement a class called Flight that represents an airline flight. It should contain instance data that represent the airline name, the flight number, and the flight’s origin and destination cities. Define the Flight constructor to accept and initialize all instance data. Include getter and setter methods for all instance data. Include a toString method that returns a one-line description of the flight. Create a driver class called FlightTest, whose main method instantiates and updates several Flight...

In the airline business, "on-time" flight arrival is important for connecting flights and general customer satisfaction....

In the airline business, "on-time" flight arrival is important for connecting flights and general customer satisfaction. Is there a difference between summer and winter average on-time flight arrivals? Let x1 be a random variable that represents percentage of on-time arrivals at major airports in the summer. Let x2 be a random variable that represents percentage of on-time arrivals at major airports in the winter. A random sample of n1 = 16 major airports showed that x1 = 74.5%, with s1...

Design the database using the ER approach. Then using Java and SQL, implement the following functionality:...

Design the database using the ER approach. Then using Java and SQL, implement the following functionality: Implement a button called “Initialize Database”. When a user clicks it, all necessary tables will be created (or recreated) automatically, with each table be populated with at least 10 tuples so that each query below will return some results. All students should use the database name “sampledb”, username “john”, and password “pass1234”. Implement a user registration and login interface so that only a registered...

Ava wants to use a database to keep track of the data recordsfor her insurance...

Ava wants to use a database to keep track of the data records for her insurance company and to enforce the following business policies/requirements: USE MS ACCESS TO CREATE A DATABASE & RELATIONASHIP-Every customer must be uniquely identified.-A customer can have many insurance policies.-Every insurance policy must be uniquely identified.-An insurance policy must belong to a valid customer.-Every customer must be served by a valid insurance agent (employee).-An insurance agent (employees) serves many customers.-Every insurance agent (employee) must be uniquely...

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of...

A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory. CPR has several warehouses across the country. Each warehouse is uniquely named. CPR also wants to record the location, city, state, zip, and space (in cubic meters) of each warehouse. There are several warehouses in any single city. CPR stores its products in the warehouses. A product may be stored in multiple warehouses. A warehouse may store multiple products....

Scenario: An auto shop is designing a database to keep track of repairs. So far, we...

Scenario: An auto shop is designing a database to keep track of repairs. So far, we have this UNF relation, with some sample data shown. Normalize to 1NF. REPAIRS: # VIN, Make, Model, Year, ( Mileage, Date, Problem, Technician, Cost ) VIN Make Model Year Mileage Date Problem Technician Cost 15386355 Ford Taurus 2000 128242 6/6/2014 Won’t start Gary $300 15386355 Ford Taurus 2000 129680 6/20/2014 Tail light out Trisha 43532934 Honda Civic 2010 38002 6/18/2014 Brakes slow Gary $240...

Scenario: A builder needs a database to keep track of contractors he hires for various projects....

Scenario: A builder needs a database to keep track of contractors he hires for various projects. So far, we have this 2NF relation, with sample data shown. Normalize to 3NF. CONTRACTOR: # ConID, Lname, Fname, JobTitle, Company, Street, City, State, Zip, CompanyPhone, CellPhone ConID Lname Fname JobTitle Company Street City State Zip Phone CellPhone 2 Garcia Mary Carpenter Construct Co 123 Main Portland OR 97204 823-1234 645-5423 14 Jones Tomas Welder Construct Co 123 Main Portland OR 97204 823-1234 344-3475...

Subjects