Sample MYSQL Database Queries Singapore

SQL Assignment help sample from Allassignmenthelp.com, best assignment help provider in Singapore. Refer to the SQL queries below and their solution done by our online assignment experts in Singapore.

SQL Query 1

For all drivers, list their phone number, the total number of bids made, total number of successful and total number of unsuccessful bids. Sort your results in ascending order of Driver’s phone number.

select a.Driver_Num,
b.Booking_ID as No_of_Bids,
count(b.Booking_ID) as successful_bids,
b.Booking_ID-count(b.Booking_ID) as unsuccessful_bids
from g5t7.bids a,g5t7.booking b
where a.Booking_ID=b.Booking_ID
group by a.Driver_Num
order by a.Driver_Num asc;

SQL Query 2 

Given a specific month & year say March 2015, list all Taxi IDs that have been hired, the hire start date, hire end date, the hiring driver’s phone and the hiring driver’s license number. In addition, during that specific month & year, if the hirer is working with partner(s), display the partnering relief driver’s phone number, partnering start date, and end date. Sort your results in ascending order of Taxi ID.

SELECT t.Taxi_ID,Main_Driver as Hirers_phone,t.Start_Date as Hiring_start_date,t.End_Date as Hiring_end_date,License_Num,
p.Relief,p.Start_Date as Partnering_Start_Date,p.End_Date as Partnering_End_Date
FROM g5t7.taxi_hiring t,g5t7.driver d,g5t7.partnering p
where t.Main_Driver=d.Phone_Num
and t.Main_Driver=p.Main
and t.Start_Date BETWEEN '2014-01-01' AND '2014-01-31'
order by Taxi_ID asc;

SQL Query 3

Given specific Car Type(s), say "Standard" and "7 seater maxi cab", list all the Taxis of those type(s) and the corresponding drivers who have hired them last. That is, if the same taxi has been hired by 2 drivers in different periods, the recent one has to be shown. Sort your results in ascending order of Taxi ID. List the Taxi ID, Car Type, Hirer’s Name, Hiring Start Date, Hiring End Date, Hirer’s Bank Account Number, and License Number

SELECT Taxi_ID,Car_Type_Name,a.Name as Hirers_name,Start_Date as Hiring_Start_Date,End_Date as Hiring_End_Date
,License_Num,d.Bank_account
From g5t7.taxi_hiring h
left join g5t7.taxi t on t.ID=h.Taxi_ID
left join g5t7.app_user a on a.Phone_Num=h.Main_Driver
left join g5t7.driver d on d.Phone_Num=h.Main_Driver
where Car_Type_Name IN ('Standard', '7 seater maxi cab')
order by Taxi_ID asc;

SQL Query 4

For a given shift during a specific period (ie. Fri PM, 2014-01-01 to 2014-01-31), we would like to find all drivers (Main or Relief) who are working. As we do not track shift data for main drivers, we assume that if the main driver does not have a relief driver working for the given shift period, the main driver who has hired a taxi for that period is the one working on that shift.

The query is required to list the Period Start Date, Period End Date, Shift Name, Driver’s Name who is working in that shift, "yes" if the driver is a relief driver else " ", the Taxi ID that he is driving at the period.

SELECT d.Start_Date,t.End_Date,Shift_Name,Main as Drivers_Phone_Num,a.Name as Drivers_name,Survey_Option as Relief_driver,Taxi_ID
FROM g5t7.drivers_shift d,g5t7.taxi_hiring t,g5t7.app_user a,g5t7.commuter c
where d.Start_Date=t.Start_Date
and a.Phone_Num=t.Main_Driver
and Shift_Name='Fri PM'
group by Name;

SQL Query 5

For a given period (ie. 2014 Feb 1st to 2014 Feb 15), list all bookings. For each of the booking, list the booking time, the driver’s name, the commuter’s name, the estimated travel distance, estimated minimum cost, maximum cost, actual distance travelled and the actual fare. List all bookings in the descending order of their Booking ID

Select Booking_ID,Book_Time,Driver,Commuter,Distance_Travelled as Estimated_Travel_Distance,
l.Price_Min as Min_Cost,l.Price_Max as Max_cost,c.Basic_Fare*b.Distance_Travelled as Actual_Fare
FROM g5t7.booking b
left outer join g5t7.locations_info l on b.Distance_Travelled=l.Distance
left outer join g5t7.car_type c on b.Car_Type=c.Car_Type_Name
where Book_Time BETWEEN '2014-02-01' AND '2014-02-15'
group by Booking_ID;

SQL QUERY 6

Retrieve all users who were both drivers & commuters. Display the user phone number, user name, the total amount of money he has earned as a taxi driver and the total amount he has spent as a commuter.

For Driver
Select u.Phone_num,u.Name,
sum(t.Basic_fare*b.Distance_Travelled) as Total_Earning
FROM g5t7.booking b, g5t7.booking a,g5t7.car_type t,g5t7.app_user u
where   b.Driver=a.Commuter
and b.Car_Type=t.Car_Type_Name
and   b.Driver=u.Phone_Num
group by b.Driver;

FOR COMMUTER

Select distinct u.Phone_Num,u.Name,
sum(t.Basic_fare*b.Distance_Travelled) as Total_Spending
FROM g5t7.booking b, g5t7.booking a,g5t7.car_type t,g5t7.app_user u
where   b.Commuter=a.Driver
and b.Car_Type=t.Car_Type_Name
and   b.Commuter=u.Phone_Num
group by b.Commuter
;

We provide a working out and ddl files for all the database assignment help we provide to our users from Singapore. You just need to download and run on your SQL setup to execute and submit to the college. Hire a programming helper in Singapore for quick results.