1 / 39

Chapter 5

Chapter 5. Structured Query Language (SQL) Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn:. The basic commands and functions of SQL How SQL is used for data manipulation (to add, modify, delete, and retrieve data)

kaden
Download Presentation

Chapter 5

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. Chapter 5 Structured Query Language (SQL) Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel

  2. In this chapter, you will learn: • The basic commands and functions of SQL • How SQL is used for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information • How SQL is used for data administration (to create tables, indexes, and views) • About more advanced SQL features such as updatable views, stored procedures, and triggers Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  3. Introduction to SQL • Ideal database language • Create database and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information • SQL is the ideal DB language • Data definition language • Data manipulation language Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  4. Good Reasons to Study SQL • ANSI standardization effort led to de facto query standard for relational database • Forms basis for present and future DBMS integration efforts • Becomes catalyst in development of distributed databases and database client/server architecture Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  5. Data Definition Commands • Create database structure • Holds all tables and is a collection of physical files stored on disk • DBMS automatically creates tables to store metadata • Database administrator creates structure or schema • Logical group of tables or logical database • Groups tables by owner • Enforces security • CREATE SCHEMA AUTHORIZATION <creator> • Example:CREATE SCHEMA AUTHORIZATION JONES Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  6. Creating Table Structure • Tables store end-user data • May be based on data dictionary entries CREATE TABLE <table name>(<attribute1 name and attribute1 characteristics,attribute2 name and attribute2 characteristics,attribute3 name and attribute3 characteristics,primary key designation,foreign key designation and foreign key requirement>); Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  7. Using Domains • Domain is set of permissible values for a column • Definition requires: • Name • Data type • Default value • Domain constraint or condition CREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>] [CHECK (<condition>)] Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  8. SQL Integrity Constraints • Adherence to entity integrity and referential integrity rules is crucial • Entity integrity enforced automatically if primary key specified in CREATE TABLE command sequence • Referential integrity can be enforced in specification of FOREIGN KEY • Other specifications to ensure conditions met: • ON DELETE RESTRICT • ON UPDATE CASCADE Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  9. Data Manipulation Commands Common SQL Commands Table 5.3 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  10. Data Entry and Saving • Enters data into a table • Saves changes to disk INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); COMMIT <table names> ; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  11. Listing Table Contents and Other Commands • Allows table contents to be listed • UPDATE command makes data entry corrections • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used • DELETE command removes table row SELECT <attribute names> FROM <table names>; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  12. Queries SELECT <column(s)>FROM <table name>WHERE <conditions>; • Creating partial listings of table contents Table 5.4 Mathematical Operators Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  13. Examples SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; • Mathematical operators • Mathematical operators on character attributes • Mathematical operators on dates SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’; SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘01/20/2002’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  14. Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  15. Operators SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288; • Logical: AND, OR, NOT • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra • Special • BETWEEN - defines limits • IS NULL - checks for nulls • LIKE - checks for similar string • IN - checks for value in a set • EXISTS - opposite of IS NULL Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  16. Advanced Data ManagementCommands • ALTER - changes table structure • ADD - adds column • MODIFY - changes column characteristics • Entering data into new column ALTER TABLE <table name>ADD (<column name> <new column characteristics>); ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>); UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  17. Advanced Data Management Commands (con’t.) • Dropping a column • Arithmetic operators and rules of precedence ALTER TABLE VENDORDROP COLUMN V_ORDER; Table 5.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  18. Advanced Data Management Commands (con’t.) • Copying parts of tables • Deleting a table from database • Primary and foreign key designation INSERT INTO <receiving table> <receiving table’s column names>SELECT <column names of the columns to be copied>FROM <contributing table name>; DROP TABLE PART; ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  19. Example Aggregate Function Operations SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT; SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE P_PRICE <= 10.00; • COUNT • MAX and MIN SELECT MIN(P_PRICE)FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = MAX(P_PRICE); Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  20. Example Aggregate Function Operations (con’t.) • SUM • AVG SELECT SUM(P_ONHAND * P_PRICE)FROM PRODUCT; SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  21. More Complex Queries and SQL Functions • Ordering a listing • Results ascending by default • Descending order uses DESC • Cascading order sequence ORDER BY <attributes> ORDER BY <attributes> DESC ORDER BY <attribute 1, attribute 2, ...> Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  22. More Complex Queries and SQL Functions (con’t.) • Listing unique values • DISTINCT clause produces list of different values • Aggregate functions • Mathematical summaries SELECT DISTINCT V_CODE FROM PRODUCT; Table 5.6 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  23. 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 PRODUCT_2GROUP BY P_SALECODE; SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT_2 GROUP BY V_CODE HAVING AVG(P_PRICE) < 10; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  24. More Complex Queries and SQL Functions (con’t.) • Virtual tables: creating a view • CREATE VIEW command • Creates logical table existing only in virtual memory • SQL indexes CREATE VIEW PRODUCT_3 ASSELECT P_DESCRIPT, P_ONHAND, P_PRICEFROM PRODUCTWHERE P_PRICE > 50.00; CREATE INDEX P_CODEXON PRODUCT(P_CODE); Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  25. More Complex Queries and SQL Functions (con’t.) • Joining database tables • Data are retrieved from more than one table • Recursive queries joins a table to itself • Outer joins can be used when ‘null’ values need to be included in query result 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; SELECT A.EMP_NUM,A.EMP_LNAME,A.EMP_MGR, B.EMP_LNAME FROM EMP A, EMP B WHERE A.EMP_MGR=B.EMP_NUM ORDER BY A.EMP_MGR Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  26. Updatable Views • Common operation in production environments is use of batch routines to update master table attributes using transaction data • Overnight batch jobs • Not all views are updatable • Restrictions • GROUP BY expressions cannot be used • Cannot use set operators---UNION, INTERSECTION, etc. • Most restrictions based on use of JOINS or group operators in views Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  27. Procedural SQL • SQL shortcomings • Doesn’t support execution of stored procedures based on logical condition • Fails to support looping operations • Solutions • Embedded SQL can be called from within procedural programming languages • Shared Code is isolated and used by all application programs. • Procedural SQL (PL/SQL) stored within the database, executed by DBMS, and invoked by the end user • Triggers • Stored procedures • PL/SQL functions Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  28. Procedural SQL (con’t.) • 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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  29. Triggers • Procedural SQL code invoked before or after data row is selected, inserted, or updated • Associated with a database table • Table may have multiple triggers • Executed as part of transaction • Can enforce particular constraints • Automate critical actions and provide warnings for remedial action • Can update values, insert records, and call procedures • Add processing power Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  30. Triggers (con’t.) • Oracle example CREATE OR REPLACE TRIGGER <trigger_name>[BEFORE/AFTER] [DELETE/INSERT/UPDATE OF <column_name] ON <table_name>[FOR EACH ROW]BEGIN PL/SQL instructions;……………END; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  31. Stored Procedures • Named collection of procedural and SQL statements stored in database • Invoked by name • Executed as unit • Invoked with EXEC CREATE OR REPLACE PROCEDURE procedure_name (argument IN/OUT data-type, etc) IS/AS BEGIN DECLARE variable name and data type PL/SQL or SQL statements;END; EXEC store_procedure_name (parameter, parameter, …) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  32. PL/SQL Stored Functions • 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 CREATE FUNCTION function_name (argument IN data-type, etc)RETURN data-typeAS BEGIN PL/SQL statements; RETURN (value); ……END; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  33. Artist Database ERD and Tables Figure 5.55 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  34. General Rules Governing Relationships Among Tables Figure 5.56: M:N, Both Sides Mandatory Figure 5.57: M:N, Both Sides Optional Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  35. General Rules Governing Relationships Among Tables (Con’t.) Figure 5.58: M:N, One Side Optional Figure 5.59: 1:M, Both Sides Mandatory Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  36. General Rules Governing Relationships Among Tables (Con’t.) Figure 5.60: 1:M, Both Sides Optional Figure 5.61: 1:M, Many Side Optional, One Side Mandatory Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  37. General Rules Governing Relationships Among Tables (Con’t.) Figure 5.62: 1:M, One Side Optional, One Side Mandatory Figure 5.63: 1:1, Both Sides Mandatory Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  38. General Rules Governing Relationships Among Tables (Con’t.) Figure 5.64: 1:1, Both Sides Optional Figure 5.65: 1:1, One Side Optional, One Side Mandatory Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  39. General Rules Governing Relationships Among Tables (Con’t.) Figure 5.66: Weak Entity, Foreign Key Located in Weak Entity Figure 5.67: Multivalued Attributes (New Table in 1:M Relationship, Foreign Key CAR_VIN in the New Table Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

More Related