1 / 20

Advanced SQL

Advanced SQL. Complex Queries, Joining Tables. Some Basic SQL Numeric Functions. Example Aggregate Function Operations. COUNT MAX and MIN. SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT; SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT WHERE P_PRICE <= 10.00;. SELECT MIN(P_PRICE) FROM PRODUCT;

tortega
Download Presentation

Advanced SQL

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 Complex Queries, Joining Tables

  2. Some Basic SQL Numeric Functions

  3. Example Aggregate Function Operations • COUNT • MAX and MIN SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT; SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE P_PRICE <= 10.00; SELECT MIN(P_PRICE)FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);

  4. More Complex Queries and SQL Functions • SUM • SELECT SUM(P_ONHAND*P_PRICE)FROM PRODUCT; • AVG • SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC;

  5. More Complex Queries and SQL Functions (con’t.) • Grouping data • Creates frequency distributions • Only valid when used with SQL arithmetic functions • HAVING clause operates like WHERE for grouping output SELECT P_SALECODE, MIN(P_PRICE)FROM PRODUCTGROUP BY P_SALECODE; SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE HAVING AVG(P_PRICE) < 10;

  6. More Complex Queries and SQL Functions • Virtual tables: creating a view • CREATE VIEW command • Creates logical table existing only in virtual memory • CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT, P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE > 50.00; • SQL indexes: used to improve the efficiency of data searches CREATE INDEX P_CODEXON PRODUCT(P_CODE); - Oracle automatically creates indexes on primary keys

  7. Complex Queries • Joining Database Tables • SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODE;

  8. The Results of a JOIN

  9. Using Prefixes when Joining Tables • Most current-generation DBMS, such as Oracle, do not require the table name to be used as prefixes, unless the same attribute name occurs in more then 1 table being joined. • If the product number was defined as p_prodnum in the product table and v_prodnum in the vendor table, the table name need not be specified when referenced • If the same attribute name occurs in several places, its origin(table) must be specified • Product.prod_num and Vendor.prod_num

  10. Complex Queries • SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE = VENDOR.V_CODEAND P_INDATE > ‘08/15/1999’;

  11. Natural Joins • Old style: SELECT P_PRODUCT, V_VENDOR FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE • New style: SELECT P_PRODUCT, V_VENDOR FROM PRODUCT NATURAL JOIN VENDOR

  12. Updatable Views • Views that can be updated which will update their corresponding tables. • Views can be updatable if the primary key of the base table is a column in the view that also has unique values Complete Lab #6 – Functions, Complex Queries, Views, Joining Tables

  13. Sequences • Sequences are independent objects in the database • They are considered “Auto-numbers” • They are created and used as primary keys in tables where the key is a sequential number CREATE SEQUENCE Part_Num_Seq START WITH 100

  14. Sequences on Insert • Refer to the sequence (auto-number) object in place of the value needed for the key when inserting data into a table • Can be used with NEXTVAL (sequence is incremented) or CURRVAL (current value of sequence is used) INSERT INTO PARTS VALUES ( PART_ID_SEQ.NEXTVAL, ‘PART_NAME, ect……..)

  15. Procedural SQL • Shortcomings of SQL • SQL doesn’t support execution of a stored set of procedures based on some logical condition. • SQL fails to support the looping operations. • Solutions • Embedded SQL • Embedded SQL can be called from within the procedural programming language • Shared Code • Critical code is isolated and shared by all application programs.

  16. Procedural SQL • Procedural SQL • Procedural SQL allows the use of procedural code and SQL statements that are stored within the database. • The procedural code is executed by the DBMS when it is invoked by the end user. • End users can use procedural SQL (PL/SQL) to create: • Triggers • Stored procedures • PL/SQL functions

  17. Procedural SQL • Triggers • A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a data manipulation event. • A trigger is always invoked before or after a data row is selected, inserted, or updated. • A trigger is always associated with a database table. • Each database table may have one or more triggers. • A trigger is executed as part of the transaction that triggered it.

  18. Procedural SQL • Role of triggers • Triggers can be used to enforce constraints that cannot be enforced at the design and implementation levels. • Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action. • Triggers can be used to update table values, insert records in tables, and call other stored procedures. • Triggers add processing power to the RDBMS and to the database system.

  19. Procedural SQL • Stored Procedures • A stored procedure is a named collection of procedural and SQL statements. • Stored procedures are stored in the database and invoked by name. • Stored procedures are executed as a unit. • The use of stored procedures reduces network traffic, thus improving performance.

  20. PL/SQL Stored Functions • Stored Functions • is a named group of procedural and SQL statements that returns a value. • Invoked from within stored procedures or triggers • Cannot be invoked from within SQL statements

More Related