AGGREGATE FUNCTIONS : EXAMPLE How many maintenance items have NULL values for sl

Question

AGGREGATE FUNCTIONS :

EXAMPLE

How many maintenance items have NULL values for slMaintenance.MaintenanceCost?

List NullCnt for all maintenance items where NullCnt is the number of rows having NULL for slMaintenance.MaintenanceCost.

ANSWER : SELECT COUNT (*) AS NULLCNT
FROM slMaintenance
WHERE slMaintenance.MaintenanceCost is NULL

NEED HELP WITH 1-8

1) Schema : CostStats

List AVGcost, MINcost, MAXcost, and CNTcost for all repairs.

Use AVG(), MAX(), MIN(), COUNT(*).

2) Schema : Stats1518
List AVGcost, MINcost, MAXcost, and CNTcost for repairs for SupplierID 1518.

Use AVG(), MAX(), MIN(), COUNT(*).

3) Schema : Last1518
List LastRepair for a repair that was supplied by SuplierID 1518.

LastRepair is the last slRepair.RepairDate of a repair for a supplier.

4) Schema : First1504
List FirstRepair for a repair that was supplied by SupplierID 1504.

FirstRepair is the first slRepair.RepairDate of a repair for a supplier.

5) Schema : TripCnt75
List TripCnt for all trips where slTrip.WaterTemp is greater than 75 and the trip launched from slTrip.LaunchID 2 or 7 or 8.

6) Schema : MaxCold
List MaxTripHour for all trips where slTrip.WaterTemp is less than 65 and the trip launched from slTrip.LaunchID 2.

MaxTripHour is the maximum of slTrip.TripHour which is the duration of the trip in hours.

7) Schema : CaptainCnt
How many distinct captians have piloted a trip?

List CaptainCnt for all distinct values of slTrip.CaptainID.

8) Schema : SuppRepCnt
How many suppliers have supplied items for repairs?

List SuppCnt for all distinct values of slRepair.SupplierID.

Explanation / Answer

Question 1

average cost

select avg(cost) as AVGcost from CostStats.slRepair

Minimum cost

select min(cost) as MINcost from CostStats.slRepair

maximum cost

select max(cost) as MAXcost from CostStats.slRepair

count

select count(*) as CNTcost from CostStats.slRepair

Question 2

average cost

select avg(cost) as AVGcost from Stats1518.slRepair where slRepair.SupplierID = 1518

Minimum cost

select min(cost) as MINcost from Stats1518.slRepair where slRepair.SupplierID = 1518

maximum cost

select max(cost) as MAXcost from Stats1518.slRepair where slRepair.SupplierID = 1518

count

select count(*) as CNTcost from Stats1518.slRepair where slRepair.SupplierID = 1518

Question 3

select RepairDate as LastRepair from Last1518.slRepair where slRepair.RepairDate =

(select max(RepairDate) from Last1518.slRepair where slRepair.SuplierID = 1518)

or

select max(RepairDate) as LastRepair from Last1518.slRepair where slRepair.SupilerID = 1518

question 4

select RepairDate as FirstDate from First1504.slRepair where slRepair.RepairDate=

(select min(RepairDate) from First1504.slRepair where slRepair.SuplierID = 1504)

or

select min(RepairDate) as FirstRepair from First1504.slRepair where slRepair.SupilerID = 1504

Question 5

select count(Cnt) as TripCnt from TripCnt75.slTrip where slTrip.WaterTemp > 75 AND slTrip.LaunchID = 2 or slTrip.LaunchID = 7 or slTrip.LaunchID = 8

Question 6

select max(TripHour) as MaxTripHour from MaxCold.slTrip where slTrip.WaterTemp < 65 AND slTrip.LaunchID = 2

Question 7

select count(DISTINCT CaptainID) from CaptainCnt.slTrip

Question 8

select count(DISTINCT SupplierID) from SuppRepCnt.slRepair

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.