1 / 9

Multi-Table Operation

Multi-Table Operation. Sungchul Hong. clientNo. clientNo. fName. propertyNo. lName. viewDate. telNo. comment. pretType. maxRent. CO56. CR56. Aline. PA14. Stewart. 5/24/2001. 0141-848-1825. too small. Flat. 350. CR56. CR62. PG36. Mary. Tiregear. 4/28/2001. 01224-196720.

mckenzie-english
Télécharger la présentation

Multi-Table Operation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Multi-Table Operation Sungchul Hong

  2. clientNo clientNo fName propertyNo lName viewDate telNo comment pretType maxRent CO56 CR56 Aline PA14 Stewart 5/24/2001 0141-848-1825 too small Flat 350 CR56 CR62 PG36 Mary Tiregear 4/28/2001 01224-196720 Flat 600 CR56 CR74 Mike PG4 Richie 5/26/2001 01475-392178 House 750 CR62 CR76 John PA14 Kay 5/14/2001 0207-774-5632 no dining room Flat 425 CR74 PG4 4/21/2001 CR76 PG4 4/20/2001 too remote Joining Tables • List the names of all clients who have viewed a property along with any comment supplied.

  3. SQL • SELECT c.clientNo, fName, lName, propertyNo, comment • FROM Client c, Viewing v • WHERE c.client = v.clientNo;

  4. Q2 • List both names and grades of all students who have made A’s or B’s in any course.

  5. SQL • SELECT DISTINCT Student.sname, gr.grade • FROM Student, Grade_report gr • WHERE Student.stno = gr.student_number • AND (gr.grade =‘B’ OR gr.grade = ‘A’);

  6. branchNo street city postcode B002 56 Clover Dr London NW10 6EU B003 163 Main St Glasgow G11 9QX B004 32 Manse Rd Bristol BS99 1NZ B005 22 Deer Rd London SW1 4EH B007 16 Argyll St Aberdeen AB2 3SU Q3 • List the staff who work in the branch at ‘163 Main St’.

  7. SQL • SELECT staffNo, fName, lName, position • FROM Staff • WHERE branchNo = (SELECT branchNO • FROM Branch • WHERE street = 163 • Main St’);

  8. Q4 • List all staff whose salary is greater than the average salary, and show by how much their salary is greater than the average.

  9. SQL • SELECT staffNo, fName, lName, position, salary – (SELECT AVG(salary) FROM Staff) AS salDiff • FROM Staff • WHERE salary > (SELECT AVG(salary FROM Staff)

More Related