Relational Schema Client(clientNo, forename, surname, gender, address, telNo, pr

Question

Relational Schema

Client(clientNo, forename, surname, gender, address, telNo, proLicenceNo)

Instructor(instructorID, forename, surname, gender, address, telNo, licenceNo,carNo)

Car(carNo, regNo, model)

Lesson(clientNo,onDate,atTime,instructorID)

Test(clientNo,onDate,atTime,instructorID,centreID, status, reason)

Centre(centreID, name, address, telNo)

Question 1:

a) Get the instructor (drop off and pick up) Forename and Surname, the Client Forename and Surname and Date for every Test on or after 10/03/2016.

b)Find the Name and Address for all clients, with the Date and Time of their tests for everyone who has had a lesson with Jill Reid.

c) Get the Forenames, Surnames and Telephone Numbers for all people,both clients and instructors,in the database.Sort by Surname, reverse alphabetical.

Explanation / Answer

select i.forename,i.surname,c.forename,c.surname
from client c,instructor i,test t
where c.clientNo=t.clientNo
and i.instructorID=t.instructorID
and t.onDate>='10/03/2016'

select c.forename,c.surname,c.address,t.onDate,t.atTime
from client c,test t,instructor i
where c.clientID=t.clientID and i.instructorID=t.instructorID
and i.instructorID like 'Jill Reid'

select forename,surname,telNo
from client
sort by surname desc
union
select forename,surname,telNo
from instructor
sort by surname desc

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.