Chapter 7 Introduction to Structured Query Language (SQL)

1. Chapter 7 Introduction to Structured Query Language (SQL) Database Systems: Design, Implementation, and Management Peter Rob & Carlos 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

3. Introduction to SQL • Ideal database language • Createdatabase and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information

4. Introduction to SQL • SQL (Structured Query Language)meets ideal database language requirements: • SQL coverage fits into two categories: • Data definition language (DDL) • Includes commands to: • Create database objects, such as tables, indexes, and views • Define access rights to those database objects • Data manipulation language (DML) • Includes commands to insert, update, delete, and retrieve data within the database tables

5. Introduction to SQL • SQL (Structured Query Language)meets ideal database language requirements: • SQL is a Nonprocedural language • SQL is relatively easy to learn. • ANSI prescribes a standard SQL. • SQL2 : SQL-92 • SQL3 : SQL-98/99 support object-oriented data management

6. SQL Data Definition Commands

7. Data Manipulation Commands

8. Data Definition Commands • The Database Model • Simple Database -- PRODUCT and VENDOR tables • Each product is supplied by only a single vendor. • A vendor may supply many products.

9. Data Definition Commands • The Tables and Their Components • The VENDOR table contains vendors who are not referenced in the PRODUCT table. PRODUCT is optional to VENDOR. • Some vendors have never supplied a product ( 0,N )

10. Data Definition Commands • The Tables and Their Components • Existing V_CODE values in the PRODUCT table must have a match in the VENDOR table. • A few products are supplied factory-direct, a few are made in-house, and a few may have been bought in a special warehouse sale. That is, a product is not necessarily supplied by a vendor.VENDOR is optional to PRODUCT. ( 0,1 )

11. The Database Model

12. The Database Model

13. The Chen Representation of the Invoicing Problem

14. Creating the Database • Two tasks must be completed • create the database structure • create the tables that will hold the end-user data • First task • RDBMS creates the physical files that will hold the database • Tends to differ substantially from one RDBMS to another • It is relatively easy to create a database structure, regardless of which RDBMS you use.

15. The Database Schema • Authentication • Process through which the DBMS verifies that only registered users are able to access the database • Log on to the RDBMS using a user ID and a password created by the database administrator • Schema • Group of database objects—such as tables and indexes—that are related to each other

16. 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

17. Data Definition Commands • Creating the Database Structure CREATE SCHEMA AUTHORIZATION <creator>; • Example: (For most RDBMS, it is optional)CREATE SCHEMA AUTHORIZATION JONES; • Schema : logical database structurea group of database objects- such as tables and indexes – that are related to each other.

18. Data Dictionary Table 7.3

19. Data Types • Data type selection is usually dictated by the nature of the data and by the intended use • Pay close attention to the expected use of attributes for sorting and data retrieval purposes

20. Some Common SQL Data Types

21. Some Common SQL Data Types Data Type Format Numeric NUMBER(L,D) INTEGER SMALLINT DECIMAL(L,D) Character CHAR(L) VARCHAR(L) Date DATE

22. Data Definition Commands • Creating Table Structures 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 requirements>);

23. Creating Table Structures • Use one line per column (attribute) definition • Use spaces to line up the attribute characteristics and constraints • Table and attribute names are capitalized • Primary key attributes contain both a NOT NULL and a UNIQUE specification • RDBMS will automatically enforce referential integrity for foreign keys • Command sequence ends with a semicolon

24. Other SQL Constraints • NOT NULL constraint • Ensures that a column does not accept nulls • UNIQUE constraint • Ensures that all values in a column are unique • DEFAULT constraint • Assigns a value to an attribute when a new row is added to a table • CHECK constraint • Validates data when an attribute value is entered

25. Data Definition Commands CREATE TABLE VENDOR (V_CODE INTEGER NOT NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(3) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL,PRIMARY KEY(V_CODE));

26. Data Definition Commands CREATE TABLE CUSTOMER(CUS_CODE NUMBER PRIMARY KEY, ..., ..., CUS_AREACODE CHAR(3) DEFAULT ‘615’NOT NULLCHECK(CUS_AREACODEIN(‘615’,’713’,’931’) ), ..., ...,);

27. CREATE TABLE PRODUCT(P_CODE VARCHAR(10) NOT NULL UNIQUE,P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_ONHAND SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE DECIMAL(8,2) NOT NULL, P_DISCOUNT DECIMAL(4,1) NOT NULL, V_CODE SMALLINT,PRIMARY KEY (P_CODE),FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE); • ON UPDATE CASCADEupdate V_CODE in VENDER → update V_CODE in PRODUCT

28. SQL Indexes • When a primary key is declared, DBMS automatically creates a unique index • Often need additional indexes • Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute

29. SQL Indexes • SQL Indexes • Improve the efficiency of data search • Created to meet particular search criteria CREATEINDEX P_INDATEX ON PRODUCT(P_INDATE); • When the index field is a primary key whose values must not be duplicated CREATEUNIQUEINDEX P_CODEXON PRODUCT(P_CODE);

30. A Duplicated TEST Record • Composite index • Index based on two or more attributes • Often used to prevent data duplication • Try to enter duplicate data → Error message: ”duplicate value in index” CREATE UNIQUE INDEX EMP_TESTDEX ON TEST(EMP_NUM, TEST_CODE, TEST_DATE);

31. Common SQL Data Manipulation Commands

32. Data Manipulation Commands • Adding table rows INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); INSERT INTO VENDORVALUES(‘21225, ’Bryson, Inc.’, ’Smithson’, ’615’,’223-3234’, ’TN’, ’Y’); INSERT INTO PRODUCTVALUES(‘11 QER/31’, ’Power painter, 15 psi., 3-nozzle’, ’03-Nov-03’, 8.5, 109.99, 0.00, 25595);

33. A Data View and Entry Form • End-user applications are best created with utilities to create a form-baseddata view and entry screen .

34. Data Manipulation Commands • Saving table changes COMMIT [WORK]; COMMIT; • Will permanently save any changes made to any table in the database • Any changes made to the table contentsare not physically saved on diskuntil • Database is closed • Program is closed • COMMIT command is used

35. Data Manipulation Commands • SELECT command - list table contents • UPDATE command – modify data in the table • ROLLBACK command - restores database back to previous condition if COMMIT hasn’t been used • DELETE command - removes table row

36. Data Manipulation Commands • Listing Table Rows • SELECT • Used to list contents of table • Syntax • SELECTcolumnlistFROMtablename • Columnlist represents one or more attributes, separated by commas • Asterisk ( * )can be used as wildcard character to list all attributes

37. Data Manipulation Commands • Listing Table Rows SELECT*FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P-PRICE, P_DISCOUNT, V_CODE FROM PRODUCT;

38. Listing Table Rows

39. Data Manipulation Commands • Updating Table Rows • UPDATE • Modify data in a table • Syntax • UPDATEtablenameSETcolumnname = expression [, columname = expression][WHEREconditionlist]; • If more than one attribute is to be updated in the row, separate corrections with commas

40. Data Manipulation Commands • Updating table rows UPDATEPRODUCTSET P_INDATE = ‘18-Jan-2004’WHERE P_CODE = ‘13-Q2/P2’; UPDATEPRODUCTSET P_INDATE = ‘18-Jan-2004’, P_PRICE = 15.99, P_MIN = 10WHERE P_CODE = ‘13-Q2/P2’;

41. Data Manipulation Commands • Restoring Table Contents • ROLLBACK • Used restore the database to its previous condition • Only applicable if COMMIT command has not been used to permanently store the changes in the database • Syntax • ROLLBACK; • COMMIT and ROLLBACK only work with data manipulation commands that are used to add, modify, or delete table rows • Oracle will automatically COMMIT data changes when issuing data definition commands

42. Data Manipulation Commands • Deleting Table Rows • DELETE • Deletes a table row • Syntax • DELETEFROMtablename[WHEREconditionlist ]; • WHERE condition is optional • If WHERE condition is not specified, all rows from the specified table will be deleted

43. Data Manipulation Commands • Deleting Table Rows DELETE FROMPRODUCTWHERE P_CODE = ‘2238/QPD’; DELETEFROMPRODUCTWHERE P_MIN = 5;

44. Data Manipulation Commands • Inserting Table Rows with a Select Subquery • INSERT • Inserts multiple rows from another table (source) • Uses SELECT subquery • Query that is embedded (or nested) inside another query • Executed first • Syntax • INSERT INTOtablename SELECTcolumnlist FROMtablename • Subquery – nested query / inner query • is a query that is embedded inside another query. • Is always executed first • INSERT INTOPRODUCT SELECT *FROM P;

45. SELECT Queries • Selecting Rows with Conditional Restrictions • Select partial table contents by placing restrictions on rows to be included in output • Add conditional restrictions to the SELECT statement, using WHERE clause • Syntax • SELECTcolumnlistFROMtablelist[ WHEREconditionlist ] ;

46. SELECT Queries • Selected PRODUCT Table Attributes for VENDOR Code 21344 SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHERE V_CODE = 21344;

47. The Microsoft Access QBE and its SQL QBE (Query By Example) query generator

48. Comparison Operators

49. SELECT Queries • Selected PRODUCT Table Attributes for VENDOR Codes Other than 21344 SELECTP_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROMPRODUCTWHERE V_CODE <> 21344;

50. SELECT Queries • Selected PRODUCT Table Attributes with a P_PRICE Restriction SELECTP_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROMPRODUCTWHERE P_PRICE <= 10;