UQ Students should read the Disclaimer & Warning
Note: This page dates from 2005, and is kept for historical purposes.
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>INFS1200 - Assignment Two</title>
<style type="text/css">
<!--
body {
background: url(_img/DSC04989.jpg) fixed center center;
}
.underline {
text-decoration: underline;
}
th, td, textarea {
border: 1px solid #000000;
padding: 0 1ex;
background: transparent;
overflow: hidden;
}
table {
border: none;
}
-->
</style>
</head>
<body>
<h1>INFS1200 – Assignment 2</h1>
<p><a href="#results">Results</a> | <a href="#sample">Sample Solution</a> | <a href="#submission">My
Submission </a></p>
<h2 id="results">Results</h2>
<p><img src="_img/INFS1200-ass2-marks-dist.png" alt="Marks Distribution" width="436" height="286" /></p>
<p><img src="_img/INFS1200-ass2-marks-hist.png" alt="Marks Histogram" width="485" height="286" /></p>
<table border="1">
<tr>
<td>My Result</td>
<td>9 out of 10</td>
</tr>
<tr>
<td>Mean</td>
<td>7.476422764</td>
</tr>
<tr>
<td>Standard Error</td>
<td>0.111063628</td>
</tr>
<tr>
<td>Median</td>
<td>7.85</td>
</tr>
<tr>
<td>Mode</td>
<td>9.2</td>
</tr>
<tr>
<td>Standard Deviation</td>
<td>1.741964938</td>
</tr>
<tr>
<td>Sample Variance</td>
<td>3.034441845</td>
</tr>
<tr>
<td>Kurtosis</td>
<td>0.405479961</td>
</tr>
<tr>
<td>Skewness</td>
<td>-0.905435138</td>
</tr>
<tr>
<td>Range</td>
<td>8</td>
</tr>
<tr>
<td>Minimum</td>
<td>2</td>
</tr>
<tr>
<td>Maximum</td>
<td>10</td>
</tr>
<tr>
<td>Count</td>
<td>246</td>
</tr>
<tr>
<td>Confidence Level (95.0%)</td>
<td>0.218761214</td>
</tr>
</table>
<h2 id="sample">Sample Solution</h2>
<p>
<textarea name="textarea2" cols="80" rows="137" readonly="readonly" title="Java Code - Copyright 2003 Ned Martin">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
</textarea>
</p>
<h2 id="submission">My Submission</h2>
<p>
<textarea name="textarea" cols="80" rows="84" readonly="readonly" title="Java Code - Copyright 2003 Ned Martin">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
</textarea>
</p>
<hr />
<p> My submission work © Copyright 2003 Ned Martin</p>
<p>10-Nov-2003</p>
</body>
</html>