[SOLVED] SQL Assignment 1

Assignment 1

BANA 273 Business Intelligence for Analytical Decisions

This assignment must be completed individually. Submit Word file to online drop box.
Write your name in the Word file.

1. Given the following four tables:

Customer (CustID, CustName, AnnualRevenue, CustType)

Shipment (ShipmentNumber, CustID, Weight, TruckID, DestinationCity, ShipDate)

Truck (TruckID, DriverName)

City(CityName, Population)

The primary key has a solid underline and foreign key is highlighted.

Answer the following queries with SQL script. Use the tables provided above. Please
use correct syntax. You do not need MS Access to answer this question. DO NOT
submit SQL copied from MS Access.

1)List the names of drivers who have delivered shipments for customers with annual
revenue over $25 million to cities with populations over 3 million? 1 point

2)How many packages weighing more than 4 pounds were sent to Los Angeles by
customers having annual revenue greater than $500 million? 1 point

3)For customers who sent a shipment first to Irvine and another shipment later to New
York, what is their name and annual revenue?1 points

4) List the names of customers who shipped at least 5 packages, each weighing more
than 5 pounds to Irvine. 1 point

To solve part 3 above, you need to review practice problem set 3 (solutions posted on
Canvas).

I have copied and pasted the relevant parts below:

Frequent_flier(Name, ffID, City, Street, zip, miles)

Reservation(ResID, ffID, flightID, date1, class, price, seatID)

Flight(FlightID, Origin, Dest, DepTime, ArrTime, equipID)

Equipment(EquipID, Desc, numFClass, numEClass)

14. The equipment that flies in to Irvine as flight 120, departs between 1600 and 1630
hours for which city?

SELECT f2.dest

FROM Flight AS f1, Flight AS f2

WHERE f1.equipID=f2.equipID AND f1.dest=Irvine AND f1.flightID=120 AND
f2.deptime>1600 AND f2.deptime<1630 AND f2.Origin=Irvine; The keyword “AS”, allows you to rename a table, in this case table “Flight” as “f1”. It also allows you to make 2 copies of this table, one called f1 and another called f2. Once you have 2 copies, you can join the two tables as seen in query above. ————————————2. Consider the following table LoanApp.2 points Based on these contigency tables find the following parameters: i. N[Income=low] ii. N[Income=low, Approve=no] iii. N[Liability=high] iv. N[Liability=high, Approve=no] v. P[Income=low] vi. P[Income=low, Approve=no] vii. P[Approve=no| Income=low] viii. P[Approve=yes| Income=low] ix. P[Liability=high] x. P[Liability=high, Approve=no] xi. P[Approve=no| Liability=high] xii. P[Approve=yes| Liability=high] 3. (a) Find the information gain provided by the features Income and Liability on the goal Approve (compute I(Approve; Income) and I(Approve; Liability)). 2 points (b) Find the gain ratio provided by the features Income and Liability on the goal Approve. 2 points