1 / 149

Chapter 7 Introduction to Structured Query Language (SQL)

Chapter 7 Introduction to Structured Query Language (SQL). Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel. In this chapter, you will learn:. The basic commands and functions of SQL

antonyl
Download Presentation

Chapter 7 Introduction to Structured Query Language (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. 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;

More Related