1 / 42

Advanced SQL Topics

Advanced SQL Topics. Edward Wu. Lesson Objectives. Learn how to create and use indexes Create, Alter, and Drop Views Outer / Self Join Nested Queries Useful Function (Decode, Case, Rownum). Database Indexes. Similar to an index in a book

booker
Download Presentation

Advanced SQL Topics

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. Advanced SQL Topics Edward Wu

  2. Lesson Objectives • Learn how to create and use indexes • Create, Alter, and Drop Views • Outer / Self Join • Nested Queries • Useful Function (Decode, Case, Rownum)

  3. Database Indexes • Similar to an index in a book • Table with list of sorted data values and corresponding physical location • Used to speed searches • Primary key indexed automatically • Unlimited number allowed, but more indexes means more processing time for action queries (insert, update, delete)

  4. Creating an Index • Create index after table data is loaded • CREATE INDEX index_name ON tablename (index_fieldname); • Convention for naming index: tablename_fieldname.

  5. Composite Index • Contains multiple (up to 16) sorted columns • Used for queries with multiple search conditions • CREATE INDEX index_name ON tablename(index_fieldname1, index_fieldname2, …);

  6. Viewing Index Information • Use data dictionary view USER_INDEXES

  7. Dropping an Index • If an index is no longer needed or does not improve performance, delete it • DROP INDEX index_name;

  8. Use an Index When • Table contains a large number of records (a rule of thumb is that a large table contains over 100,000 records) • The field contains a wide range of values • The field contains a large number of NULL values • Application queries frequently use the field in a search condition or join condition • Most queries retrieve less than 2% to 4% of the table rows

  9. Do Not Use an Index When • The table does not contain a large number of records • Applications do not use the proposed index field in a query search condition • Most queries retrieve more than 2% to 4% of the table records • Applications frequently insert or modify table data

  10. Database Views • Logical table based on a query • Does not physically exist in the database • Presents data in a different format from underlying tables • Uses: • Security • Simplifying complex queries

  11. Database Views • Creating a view: CREATE VIEW view_name AS SQL_command; • Views can be queried just like tables: SELECT * FROM view_name;

  12. Simple Views • Based on SQL query that retrieves data from only one table (limit data for different user) • View can support all table DML operations: • INSERT • UPDATE • DELETE

  13. Complex Views • Based on query that retrieves data from multiple tables • Can only be used to support SELECT operations • No DML operations supported • For reporting purposes. (Sales Report) • Can create index on view.

  14. Join Queries • Retrieve data from multiple tables by joining tables using foreign key references • Join query types: • Outer • Self • Inequality

  15. Inner Join with Group by • Find the total inventory amount on hand for each item Different price

  16. Outer Joins • Limitation of inner joins: some records may be omitted if corresponding records don’t exist in one of the tables • Example: retrieve records for all students, along with their corresponding ENROLLMENT information

  17. Outer Joins • Student 105 (Michael Connoly) does not have any ENROLLMENT records

  18. Outer Joins • No records retrieved for Michael:

  19. Outer Joins • To include records in first (inner) table, even when they do not have matching records in second (outer) table, place outer join marker (+) beside outer table name in join clause

  20. Outer Joins Outer join marker

  21. Note: Orange is the area that is returned A B A.id = B.id A B (+)A.id = B.id A B A.id = B.id(+)

  22. Self Joins • Used to join a table to itself when the table has a hierarchical relationship

  23. Self Joins • To create a self-join, you need to create a table alias, which gives an alternate name to the table so you can create a join condition • Syntax to create table alias in FROM clause: FROM table1 alias1, table2 alias2

  24. PARENT_PROJECT Self Joins SUB_PROJECT PROJECT

  25. Self Join Example

  26. Inequality Joins • Join created by placing making join condition satisfy an inequality condition • Only makes sense when primary/foreign key values are not surrogate keys

  27. Inequality Joins

  28. Nested Queries • Created when a subquery is nested within a main query • Main query: first query listed in SELECT command • Subquery: retrieves one or more values that specify the main query’s search condition

  29. Nested Query WhereSubquery Returns a Single Value • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 = (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Subquery that returns one value Note: subquery is just another sql command contained within a parenthesis

  30. Example Nested Query WhereSubquery Returns a Single Value Find all the student that goes to Amanda class Find the class that Amanda goes to.

  31. Nested Query WhereSubquery Returns Multiple Values • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 IN (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Subquery that returns multiple values

  32. Example Nested Query WhereSubquery Returns Multiple Values Find all the students that enrolled in c_sec_id in (1010, 1011) Find all the course that Amanda enrolled in. which returned c_sec_id: 1010, 1011

  33. Example of nested queries (cont…) select c_sec_id from student s, enrollment e, course_section c where s.s_id = e.s_id and s_last = 'Mobley‘ and s_first = 'Amanda‘ and c_sec_id in (1000, 1002, 1007, 1010); Find the c_sec_id that use bldg_code=‘CR’ which is 1000, 1002, 1007, 1010

  34. Remember previous example of Inner Join with Group by • Find the total inventory amount on hand for each item Different price

  35. Creating Table Alias from select statement • Add an extra column for the percentage of inventory on hand

  36. Using Set Operators in Queries • Performs set operations on outputs of two unrelated queries • Both queries must have: • same number of display fields • corresponding display fields must have same data type

  37. Query Set Operators • UNION: combines results, suppresses duplicate rows • UNION ALL: combines results, displays duplicates • INTERSECT: finds matching rows • MINUS: returns the difference between returned record sets

  38. Decode Function • Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement • Syntax Decode(column/expression, search1, result1 [, search2, result2, …, ] [, default ] );

  39. The Decode Function The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each searchvalue. If the expression is the same as search, result is returned. Note: If the default value is omitted, a null value is returned where a search value does not match any of the result value.

  40. Using the DECODE Function

  41. Row Num

  42. Search Result for web page

More Related