UQ Students should read the Disclaimer & Warning

Note: This page dates from 2005, and is kept for historical purposes.

INFS1200 – Assignment 2

Results | Sample Solution | My Submission

Results

My Result	9 out of 10
Mean	7.476422764
Standard Error	0.111063628
Median	7.85
Mode	9.2
Standard Deviation	1.741964938
Sample Variance	3.034441845
Kurtosis	0.405479961
Skewness	-0.905435138
Range	8
Minimum	2
Maximum	10
Count	246
Confidence Level (95.0%)	0.218761214

Sample Solution

INFS1200 Assignment 2 Reference Answers 2003 Q1. Display all buses of the model “HiLux” that have a weight > 1.5 and seat ranging from 12 to 35. SELECT * FROM Bus WHERE Model =”HiLux” AND Weight >1.5 AND Seat BETWEEN 12 AND 35; Q2. Display the number (Int-Number) of all branches, along with the name of the company to which they belong . SELECT Int_Number AS BRANCH_NUMBER, C.Name AS COMPANY FROM Branch B, Company C WHERE B.ABN = C.ABN; Q3. Display the name of a company, the routes it runs (display route number), and the corresponding names of all stops that the route goes through. Order by company name, and then by route number. SELECT C.Name, CR.Route#, S.StopName FROM Company C, CompanyRoute CR, RouteStops RS, Stop S WHERE C. ABN = CR.ABN AND CR.Route# = RS.Route# AND RS.StopNo = S.StopNo ORDER BY C.Name, CR.Route#; Q4. Display the name of the company that owns the largest number of buses. SELECT C.Name FROM Company C WHERE C.ABN IN ( SELECT ABN FROM Bus GROUP BY ABN HAVING COUNT(*) >= ALL (SELECT COUNT (*) FROM Bus GROUP BY ABN) ); Q5. Find the route(s) that stop in all the stops of the city “New Castle”. SELECT Route# FROM Route R1 WHERE NOT EXISTS ( SELECT * FROM Stop WHERE City = ‘New Castle’ AND StopNo NOT IN ( SELECT S.StopNo FROM RouteStops R, Stop S WHERE R1.Route# = R.Route# AND R.StopNo = S.StopNo ) ); Q6. Find the average distance of routes for the company “Southern Cross Transport”. SELECT AVERAGE(Distance) FROM Route R, CompanyRoute CR, Company C WHERE C.Name = ‘Southern Cross Transport’ AND C.ABN = CR.ABN AND CR.Route# = R.Route#; Q7. Find all companies with head office located in “Sydney”, who have no buses with a road worth certificate dated before 31.12.2000. Display all details of the company, and order by company name. SELECT * FROM Company C WHERE C.City = ‘Sydney’ AND NOT EXISTS ( SELECT * FROM Bus WHERE CertificationDate < ’31.12.2001’ AND ABN IN ( SELECT ABN FROM Bus B WHERE C.ABN = B.ABN ) ) ORDER BY C.Name; Q8. Display company names of companies who have at least one route which has a distance greater than the average distance of routes for each company. SELECT C.Name FROM Comapany C WHERE ABN IN ( SELECT ABN FROM CompanyRoute CR, Route R WHERE CR.Route# = R.Route# AND Distance > ALL ( SELECT AVERAGE(Distance) FROM CompanyRoute CR1, Route R1 WHERE CR1.Route# = R1.Route# GROUP BY CR1.ABN ) ); Q9 Display the route number, start and end stop number of all routes that have less than 3 intermediate stops. SELECT Route#, Start_StopNo, End_StopNo FROM Route WHERE Route# IN ( SELECT Route# FROM RouteStops GROUP BY Route# HAVING COUNT(*) <5 ); Q10 List pairs of companies who run exactly the same routes. Display company names only. SELECT C1.Name, C2.Name FROM Company C1, Company C2 WHERE C1.ABN > C2.ABN AND NOT EXISTS (SELECT * FROM CompanyRoute# CR1 WHERE C1.ABN = CR1.ABN AND CR1.Route# NOT IN ( SELECT Route# FROM CompanyRoute CR2 WHERE C2.ABN = CR2.ABN ) ) AND NOT EXISTS (SELECT * FROM CompanyRoute# CR3 WHERE C2.ABN = CR3.ABN AND CR3.Route# NOT IN ( SELECT Route# FROM CompanyRoute CR4 WHERE C1.ABN = CR4.ABN ) ); 1 of 3

My Submission

INFS1200 Assignment Two Ned Martin - 40529927 1 SELECT * FROM bus WHERE model="HiLux" And weight>1.5 And Seats between 12 and 35; 2 SELECT intnumber, name FROM branch, company WHERE branch.abn=company.abn; 3 SELECT c.name, r.routen, s.stopname FROM company AS c, route AS r, routestops AS rs, companyroute AS cr, stop AS s WHERE (c.abn=cr.abn and r.routen=cr.routen and rs.routen=r.routen and s.stopno=rs.stopno) ORDER BY c.name, r.routen; 4 SELECT c.name FROM company AS c, bus AS b WHERE c.abn=b.abn GROUP BY c.name HAVING count(b.regn) >= all ( select count(bus.regn) from bus group by bus.abn); 5 SELECT routen FROM stop AS s, routestops AS rs WHERE city='new castle' and s.stopno=rs.stopno GROUP BY routen HAVING count(routen) = ( select count(stopno) from stop where city = 'new castle'); 6 SELECT avg(distance) FROM company AS c, route AS r, companyroute AS cr WHERE (cr.abn=c.abn and r.routen=cr.routen and name='southern cross transport'); 7 SELECT DISTINCT c.* FROM company AS c, bus AS b WHERE (city='sydney') and (c.abn=b.abn) and c.abn not in ( select abn from bus where certificationdate <#12/31/2000#) ORDER BY c.name; 8 SELECT DISTINCT c.name FROM company AS c, companyroute AS cr, route AS r WHERE c.abn=cr.abn and cr.routen=r.routen and r.distance>all ( select avg(r.distance) from route as r, company as c, companyroute as cr where c.abn=cr.abn and cr.routen=r.routen group by c.name); 9 SELECT rs.routen, [start-stopno], [end-stopno] FROM route AS r, routestops AS rs WHERE r.routen=rs.routen GROUP BY rs.routen, [start-stopno], [end-stopno] HAVING (count(rs.stopno)<3); INFS1200 Assignment Two Ned Martin - 40529927 10-Nov-2003 Page 2 of 2

---

My submission work © Copyright 2003 Ned Martin

10-Nov-2003