1 / 12

Torsdag

Torsdag. Opfølgning SQL fra i går Create table med cascade Select Inner join outer join SQL views. Inner join. select pnumber , dnum , fname , lname , address from ((project join department on dnum = dnumber ) join Employee on mgrssn = ssn )

tahlia
Download Presentation

Torsdag

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. Torsdag • Opfølgning SQL fra i går • Createtable med cascade • Select • Innerjoin • outerjoin • SQL views

  2. Innerjoin • select pnumber, dnum, fname, lname, address • from ((project join department on dnum = dnumber) • join Employee on mgrssn = ssn) • whereplocation = 'Stafford'

  3. Outerjoin • select fname, lname, dname as lederAf • from (employee left join department on • ssn = mgrssn) John Smith NULL FrankLinWong Research Joyce English NULL RameshNarayalan NULL James Borg Headquarters Jennifer Wallace Administration Ahmad Jabbar NULL AliciaZelaya NULL

  4. Select • select < attributeand function list> • from < tablelist> • [where < condition>] • [group by <groupingattributelist>] • [Having <groupcondition>] • [Order by < attributelist>]

  5. Views in SQL • A view is a “virtual” table that is derived from other tables • Allows for limited update operations (since the table may not physically be stored) • Allows full query operations • A convenience for expressing certain operations

  6. Specification of Views • SQL command: CREATE VIEW • a view (table) name • a possible list of attribute names (for example, when arithmetic operations are specified or when we want the names to be different from the attributes in the base relations) • a query to specify the table contents

  7. SQL Views: An Example CREATE view ViewWorksOn(fname, lname, pname, hours) AS (SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY fname, lname, PNAME,hours)

  8. Using a Virtual Table • We can specify SQL queries on a newly create table (view): SELECT FNAME, LNAME FROM ViewWorksOn WHERE PNAME=‘ProductX’; • When no longer needed, a view can be dropped: DROP VIEW ViewWorksOn;

  9. View Update • Update on a single view without aggregate operations: update may map to an update on the underlying base table • Views involving joins: an update may map to an update on the underlying base relations • not always possible

  10. Un-updatable Views • Views defined using groups and aggregate functions are not updateable • Views defined on multiple tables using joins are generally not updateable

  11. Exercises • 8.21 nested sql • 8.24, 8.25 views

More Related