200 likes | 221 Views
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;
E N D
Advanced SQL Complex Queries, Joining Tables
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);
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;
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;
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
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;
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
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’;
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
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
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
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……..)
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.
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
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.
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.
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.
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