Chapter 5In this chapter, you will learn:Introduction to SQLGood Reasons to Study SQLData Definition CommandsCreating Table StructureUsing DomainsSQL Integrity ConstraintsData Manipulation CommandsData Entry and SavingListing Table Contents and Other CommandsQueriesExamplesComputed ColumnsOperatorsAdvanced Data Management CommandsAdvanced Data Management Commands (con’t.)Slide 18Example Aggregate Function OperationsExample Aggregate Function Operations (con’t.)More Complex Queries and SQL FunctionsMore Complex Queries and SQL Functions (con’t.)More Complex Queries and SQL Functions (con’t.)Slide 24Slide 25Updatable ViewsProcedural SQLProcedural SQL (con’t.)TriggersTriggers (con’t.)Stored ProceduresPL/SQL Stored FunctionsArtist Database ERD and TablesGeneral Rules Governing Relationships Among TablesGeneral Rules Governing Relationships Among Tables (Con’t.)Slide 36Slide 37Slide 38Slide 395Chapter 5Structured Query Language (SQL)Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and CoronelDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel25In 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 triggersDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel35Introduction 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 languageDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel45Good 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 architectureDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel55Data 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 securityCREATE SCHEMA AUTHORIZATION <creator>Example:CREATE SCHEMA AUTHORIZATION JONESDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel65Creating Table Structure•Tables store end-user data•May be based on data dictionary entriesCREATE 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 & Coronel75Using Domains•Domain is set of permissible values for a column•Definition requires:–Name–Data type–Default value–Domain constraint or conditionCREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>][CHECK (<condition>)]Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel85SQL 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 CASCADEDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel95Data Manipulation CommandsCommon SQL CommandsTable 5.3Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel105Data Entry and Saving•Enters data into a table•Saves changes to diskINSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.);COMMIT <table names> ;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel115Listing 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 rowSELECT <attribute names> FROM <table names>;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel125Queries•Creating partial listings of table contentsSELECT <column(s)>FROM <table name>WHERE <conditions>;Table 5.4 Mathematical OperatorsDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel135Examples•Mathematical operators•Mathematical operators on character attributes•Mathematical operators on datesSELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE <> 21344;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 & Coronel145Computed 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 statementSELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel155Operators•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 NULLSELECT *FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288;Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel165Advanced Data ManagementCommands•ALTER - changes table structure•ADD - adds column•MODIFY - changes column characteristics•Entering