Sandbox‎ > ‎IPT 2010-11‎ > ‎Cheuk's HSC Page‎ > ‎Class Work‎ > ‎

15/11/10 - Learning Activities pg.63

posted Nov 14, 2010, 3:43 PM by Unknown user   [ updated Nov 14, 2010, 5:10 PM ]
Create a data dictionary and an ERD for this database.
 
Field Name
Data Type 
Field Size 
Description 
RegistrationNo 
 
 
 
PeopleCapacity
 
 
 
Model
 
 
 
Colour 
 
 
 
Details
 
 
 
PatronID
 
 
 
FirstName 
 
 
 
LastName
 
 
 
Address
 
 
 
JobNumber
 
 
 
RegistrationNo
 
 
 
AmountReceived
 
 
 
Destination
 
 
 
a)
SELECT Model
FROM SMALL BUS
WHERE PeopleCapacity >8
ORDER BY Model ASC
 
b)
SELECT Model
FROM SMALL BUS
WHERE Model = "Toyota"
ORDER BY Model ASC
 
c)
SELECT PatronID, FirstName, LastName
FROM SMALL BUS
WHERE LastName = S*
ORDER BY LastName ASC
 
d)
SELECT PatronID, FirstName, LastName
FROM SMALL BUS
WHERE wat da beep
ORDER BY LastName ASC
 
e)
SELECT JobNumber, AmountReceived
FROM SMALL BUS
WHERE AmountReceived > $40
ORDER BY AmountReceived DESC
 
f)
SELECT PatronID, FirstName, LastName
FROM SMALL BUS
WHERE AmountReceived < $40
ORDER BY AmountReceived DESC
 
g)
SELECT JobNumber
FROM SMALL BUS
Where Model = "Toyota" OR Destination = "Sydney"
ORDER BY
 
h)
SELECT PatronID, FirstName, LastNae
FROM SMALL BUS
Where Colour = "Red" AND Address contains a
 
2. a) First names and last names of employees will be displayed from tables "Employee" and "Wage" who work in the sport department and whose weekly pay is over $1000. The results are ordred by last name.
 
b) EmployeeIDs will be displayed of employees from tables "Employee" and "Wage" who live in Cabramatta or get paid on the 31/07/01. The results are ordered by last name in descending order.
 
c)
SELECT FirstName, LastName
FROM Employee, Wage
WHERE Employee.Deparrtment = "Office" AND Suburb = "Ryde" OR "Hunters Hill"
ORDER BY LastName
 
d)
SELECT FirstName, LastName, Address
FROM Employee, Wage
WHERE Wage.WeeklyTax < 50
ORDER BY Wage.WeeklyTax
 
e)
 
Comments