1 / 19

Database

Database. cs453 Lab10. Objectives. Unions, intersections, difference indexes , Views Lab # 10. Unions, intersections, difference Indexes Views. Union Compatibility. Builds a relation consisting of all tuples appearing in either or both of two specified relations.

max
Download Presentation

Database

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. Database cs453 Lab10 Ins.Ebtesam AL-Etowi

  2. Objectives Unions, intersections, differenceindexes , ViewsLab # 10 • Unions, intersections, difference • Indexes • Views Ins.Ebtesam AL-Etowi

  3. Union Compatibility • Builds a relation consisting of all tuples appearing in either or both of two specified relations. • Requirement for the traditional set operators • Strong requirement • Same number of columns • Each corresponding column is compatible • Positional correspondence Ins.Ebtesam AL-Etowi

  4. UNION operation A RESULT=WORKS_ON(Essn=333445555) WORKS_ON(Essn=123456789) B selectPno from WORKS_ON where Essn=333445555 union selectPno from WORKS_ON where Essn=123456789 Ins.Ebtesam AL-Etowi

  5. Conserving Duplicates selectPnofrom WORKS_ON whereEssn=333445555 UNIONALL selectPnofrom WORKS_ON whereEssn=123456789 Ins.Ebtesam AL-Etowi

  6. Intersect Builds a relation consisting of all tuples appearing in both of two specified relations INTERSECTION operation A B RESULT=WORKS_ON(Essn=333445555) WORKS_ON(Essn=123456789) selectPno from WORKS_ON whereEssn=333445555 intersect selectPno from WORKS_ON whereEssn=123456789 SELECTPnoFROM WORKS_ON WHEREEssn=333445555 and Pno in (SELECTPnoFROM WORKS_ON whereEssn=123456789) Ins.Ebtesam AL-Etowi

  7. Ins.Ebtesam AL-Etowi

  8. SET DIFFERENCE operation b) a) A a)RESULT=Pno_Essn(333445555)- Pno_Essn(123456789) B b) RESULT=Pno_Essn(123456789)- Pno_Essn(333445555) (selectPnofrom WORKS_ON whereEssn=333445555) MINUS (selectPnofrom WORKS_ON whereEssn=123456789); SELECTPnoFROM WORKS_ON WHEREEssn=333445555 and Pno not in (SELECTPnoFROM WORKS_ON where Essn=123456789) Ins.Ebtesam AL-Etowi

  9. Ins.Ebtesam AL-Etowi

  10. Indexes REALLYimportant to speed up query processing time. Suppose we have a relation SELECTLname, Bdate FROM Employee WHERELname = ‘Smith’ Employee(Lname, Bdate) Sequential scan of the file employee may take long Create an index on name: B+ trees have fan-out of 100s: max 4 levels ! Ins.Ebtesam AL-Etowi

  11. Creating Indexes Syntax: CREATE INDEXnameIndexON Employee(Lname) SELECTLname, Bdate , address FROM Employee WHERELname = ‘Smith’ Ins.Ebtesam AL-Etowi

  12. Indexes can be created on more than one attribute: Example: CREATE INDEXdoubleindexONEmplyee(Bdate,Dno) SELECT * FROM EmployeeWHEREBdate = ‘1965-01-09’ AND Dno = 5 Helps in: Ins.Ebtesam AL-Etowi

  13. Indexes can be useful in range queries too CREATE INDEXDnoIndexON Employee (Dno) SELECT * FROM EmployeeWHEREDno>= 1 AND Dno<= 8 Why not create indexes on everything? B+ trees help in: Ins.Ebtesam AL-Etowi

  14. SQL CREATE VIEW STATEMENT • In SQL, a view is virtual table based on the result-set of an SQL statement. • A view contains rows and columns, just like areal table. The fields in a view are fields from one or more real tables in the database. • If data is changing in the underlying table , the same change is reflected in the view. A view can be built on top of a single table or multiple tables. • You can add SQL functions , WHERE and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition Views are relations, except that they are not physically stored. For presenting different information to different users WORKS_ON1(Fname, Lname, Pname, Hours) Ins.Ebtesam AL-Etowi

  15. CREATE VIEW WORKS_ON1 AS SELECTFname, Lname, Pname, Hours FROM EMPLOYEE, PROJECT, WORKS_ON WHERESsn=Essn AND Pno=Pnumber; Ins.Ebtesam AL-Etowi

  16. You can query the view above as followes: SELECT * FROM [Current Product List] SELECT* FROM WORKS_ON1 Ins.Ebtesam AL-Etowi

  17. CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal) AS SELECT Dname, COUNT (*), SUM (Salary) FROMdepartement, EMPLOYEE WHEREDnumber=Dno GROUP BY Dname; Ins.Ebtesam AL-Etowi

  18. Updating Views UPDATE WORKS_ON1 SETPname = 'ProductYY' WHERELname='Smith' ANDFname='John' ANDPname='ProductX'; UPDATE WORKS_ON1 SETPno= (SELECT Pnumber FROM PROJECT WHEREPname='ProductY' ) WHEREEssn IN ( SELECT Ssn FROM EMPLOYEE WHERELname='Smith' AND Fname='John' ) AND Pno= (SELECTPnumber FROM PROJECT WHEREPname='ProductX' ); Ins.Ebtesam AL-Etowi

  19. SQL DROPPING A VIEW You can delete a view with the DROP VIEW command. SQL DROP VIEW Syntax: DROP VIEW view_name Ins.Ebtesam AL-Etowi

More Related