Showcase of queries in project specification

a. Names and phone numbers of managers in each office

select staffName, staffNo from staff where posNo in(select posNo from position where posName = ‘manager’) group by officeNo

b. names of all female drivers in Glasgow office

select staffName from staff where officeNo in (select officeNo from office where city = ‘Glasgow’)
and staffGender = ‘F’ and posNo in (select posNo from position where posName = ‘Driver’)

c. number of staff in each office

select officeNo, count(*) as staff_count from staff group by officeNo

d. details of all taxis in Glasgow office

select * from driver where driverNo in (select staffNo from staff where officeNo in
(select officeNo from office where city = ‘Glasgow’))

e. number of registered taxis

select count(taxiNo) from taxi

f. number of drivers allocated to each taxi

select taxiNo, count(driverNo) from driver group by taxiNo

g. name and number of owners with more than 1 taxi

select taxi.ownerNo, staff.staffName from taxi inner join staff on taxi.ownerNo = staff.staffNo
group by ownerNo having count(taxiNo)>1

h. address of all business clients in Glasgow

select companyAddress from company where city = ‘Glasgow’

i. details of current conracts with musiness clients in Glasgow

select * from businessOrder where orderNo in (select orderNo from order_list where clientNo in
(select clientNo from client where companyNo in (select companyNo from company where city = ‘glasgow’)))

j. number of private clients in each city

select city, count(clientNo) from client where companyNo = 0 group by city

k. details of jobs undertaken in a given day

select * from order_list where orderdate = ‘<input>’ group by driverNo

l. names of driver over 55 years old

select staffName from staff where timestampdiff(year, staffDob, curdate())>55 and
posNo in (select posNo from position where posName = ‘driver’)

m. names and numbers of private clients who hired a taxi in November 2016

select clientName, clientNo from client where companyNo = 0 and clientNo in
(select clientNo from order_list where orderdate between ‘2016-11-01’ and ‘2016-11-30’)

n. names and addresses of private clients who hired a taxi over 3x

select order_list.clientNo, client.clientName, client.city from client
inner join order_list on order_list.clientNo = client.clientNo
group by order_list.clientNo having count(orderNo)>3

o. average number of miles driven during a job

select orderNo, avg(miles) as miles_avg from privateorder group by orderNo

p. total number of jobs alllocated to each car

select taxiNo, count(orderNo) as order_count from order_list group by taxiNo

q. total number of jobs allocated to each driver

select driverNo, count(orderNo) as order_count from order_list group by driverNo

r. total amount charged for each car in November 2016

select taxiNo, sum(price) from order_list where orderDate between ‘2016-11-01’ and ‘2016-11-30’
group by taxiNo order by orderDate

s. total number of jobs and miles driven for a given contract

select order_list.orderNo, count(businessorder.orderNo) as job_count, sum(businessorder.miles) as total_miles from businessorder
inner join order_list on order_list.orderNo = businessorder.orderNo group by order_list.orderNo

Database Systems

Group: Eric Savero, Ikhwan Fikri, Brigitta Gloria