6 Chapter 6 Introduction to Structured Query Language

6 Chapter 6 Introduction to Structured Query Language (SQL) Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel

6 In this chapter, you will learn: • The basic commands and functions of SQL • How to use SQL for data administration (to create tables, indexes, and views) • How to use SQL for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 2

6 Introduction to SQL • SQL functions fit into two broad categories: • Data definition language – SQL includes commands to create • Database objects such as tables, indexes, and views • Commands to define access rights to those database objects • Data manipulation language – Includes commands to insert, update, delete, and retrieve data within the database tables Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 3

6 Introduction to SQL (continued) • SQL is relatively easy to learn • Basic command set has a vocabulary of less than 100 words • Nonprocedural language • American National Standards Institute (ANSI) prescribes a standard SQL • Several SQL dialects exist Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 4

6 SQL Data Definition Commands Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 5

6 Data Manipulation Commands Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 6

6 Data Definition Commands • Examine the simple database model and the database tables that will form the basis for the many SQL examples • Understand the data environment Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 7

6 The Database Model Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 8

6 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 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 9

6 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 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 10

6 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 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 11

6 Some Common SQL Data Types Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 12

6 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 • NOT NULL specification • UNIQUE specification • Primary key attributes contain both a NOT NULL and a UNIQUE specification • RDBMS will automatically enforce referential integrity foreign keys • Command sequence ends with a semicolon Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 13

6 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 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 14

6 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 • Composite index – Index based on two or more attributes – Often used to prevent data duplication Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 15

6 A Duplicated TEST Record Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 16

6 Data Manipulation Commands • Adding table rows • Saving table changes • Listing table rows • Updating table rows • Restoring table contents • Deleting table rows • Inserting table rows with a select subquery Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 17

6 Common SQL Data Manipulation Commands Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 18

6 A Data View and Entry Form Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 19

6 Saving Table Changes • Changes made to table contents are not physically saved on disk until – Database is closed – Program is closed – COMMIT command is used • Syntax – COMMIT [WORK] • Will permanently save any changes made to any table in the database Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 20

6 Listing Table Rows • SELECT – Used to list contents of table • Syntax – SELECT columnlist FROM tablename • Columnlist represents one or more attributes, separated by commas • Asterisk can be used as wildcard character to list all attributes Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 21

6 Updating Table Rows • UPDATE – Modify data in a table • Syntax – UPDATE tablename SET columnname = expression [, columname = expression] [WHERE conditionlist]; • If more than one attribute is to be updated in the row, separate corrections with commas Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 22

6 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 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 23

6 Deleting Table Rows • DELETE – Deletes a table row • Syntax – DELETE FROM tablename [WHERE conditionlist ]; • WHERE condition is optional • If WHERE condition is not specified, all rows from the specified table will be deleted Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 24

6 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 INTO tablename SELECT columnlist FROM tablename Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 25

6 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 – SELECT columnlist FROM tablelist [ WHERE conditionlist ] ; Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 26

6 Selected PRODUCT Table Attributes for VENDOR Code 21344 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 27

6 The Microsoft Access QBE and its SQL Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 28

6 Comparison Operators Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 29

6 Selected PRODUCT Table Attributes for VENDOR Codes Other than 21344 Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 30

6 Selected PRODUCT Table Attributes with a P_PRICE Restriction Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 31

6 Selected PRODUCT Table Attributes: The ASCII Code Effect Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 32

6 Selected PRODUCT Table Attributes: Date Restriction Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 33

6 SELECT Statement with a Computed Column Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 34

6 SELECT Statement with a Computed Column and an Alias Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 35

6 Arithmetic Operators: The Rule of Precedence • Perform operations within parentheses • Perform power operations • Perform multiplications and divisions • Perform additions and subtractions Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 36

6 Selected PRODUCT Table Attributes: The Logical OR Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 37

6 Selected PRODUCT Table Attributes: The Logical AND Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 38

6 Selected PRODUCT Table Attributes: The Logical AND and OR Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 39

6 Special Operators • BETWEEN – Used to check whether attribute value is within a range • IS NULL – Used to check whether attribute value is null • LIKE – Used to check whether attribute value matches a given string pattern • IN – Used to check whether attribute value matches any value within a value list • EXISTS – Used to check if a subquery returns any rows Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 40

6 Advanced Data Definition Commands • All changes in the table structure are made by using the ALTER command – Followed by a keyword that produces specific change – Three options are available • ADD • MODIFY • DROP Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 41

6 Changing a Column’s Data Type • ALTER can be used to change data type • Some RDBMSs (such as Oracle) do not permit changes to data types unless the column to be changed is empty Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 42

6 Changing a Column’s Data Characteristics • Use ALTER to change data characteristics • If the column to be changed already contains data, changes in the column’s characteristics are permitted if those changes do not alter the data type Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 43

6 Adding or Dropping a Column • Use ALTER to add a column – Do not include the NOT NULL clause for new column • Use ALTER to drop a column – Some RDBMSs impose restrictions on the deletion of an attribute Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 44

6 The Effect of Data Entry into the New P_SALECODE Column Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 45

6 Update of the P_SALECODE Column in Multiple Data Rows Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 46

6 The Effect of Multiple Data Updates in the PRODUCT Table (MS Access) Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 47

6 Copying Parts of Tables • SQL permits copying contents of selected table columns so that the data need not be reentered manually into newly created table(s) • First create the PART table structure • Next add rows to new PART table using PRODUCT table rows Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 48

6 PART Attributes Copied from the PRODUCT Table Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 49

6 Advanced Select Queries • SQL provides useful functions – Count – Find minimum and maximum values – Calculate averages • SQL allows the user to limit queries to only those entries having no duplicates or entries whose duplicates may be grouped Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 50

6 Selected PRODUCT Table Attributes: Ordered by (Ascending) P_PRICE Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 51

6 Partial Listing of EMPLOYEE Table Contents Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 52

6 Telephone List Query Results Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 53

6 A Query Based on Multiple Restrictions Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 54

6 A Listing of Distinct (Different) V_CODE Values in the PRODUCT Table Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 55

6 Some Basic SQL Aggregate Functions Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 56

6 COUNT Function Output Examples Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 57

6 MAX and MIN Function Output Examples Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 58

6 The Total Value of All Items in the PRODUCT Table Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 59

6 AVG Function Output Examples Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 60

6 GROUP BY Clause Output Examples Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 61

6 Incorrect and Correct Use of the GROUP BY Clause Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 62

6 An Application of the HAVING Clause Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 63

6 Virtual Tables: Creating a View • View is a virtual table based on a SELECT query – Can contain columns, computed columns, aliases, and aggregate functions from one or more tables • Base tables are tables on which the view is based • Create a view by using the CREATE VIEW command Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 64

6 Creating a Virtual Table with the CREATE VIEW Command Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 65

6 Joining Database Tables • Ability to combine (join) tables on common attributes is most important distinction between a relational database and other databases • Join is performed when data are retrieved from more than one table at a time • Join is generally composed of an equality comparison between the foreign key and the primary key of related tables Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 66

6 Creating Links Through Foreign Keys Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 67

6 The Results of a Join Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 68

6 An Ordered and Limited Listing After a JOIN Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 69

6 The Contents of the EMP Table Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 70

6 Using an Alias to Join a Table to Itself Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 71

6 The Left Outer Join Results Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 72

6 The Right Outer Join Results Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 73

6 Converting an ER Model into a Database Structure • Requires following specific rules that govern such a conversion • Decisions made by the designer to govern data integrity are reflected in the foreign key rules • Implementation decisions vary according to the problem being addressed Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 74

6 The Ch 06_Artist Database ERD and Tables Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 75

6 A Data Dictionary for the Ch 06_Artist Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 76

6 A Summary of Foreign Key Rules Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 77

6 Summary • SQL commands can be divided into two overall categories: – Data definition language commands – Data manipulation language commands • Basic data definition commands allow you to create tables, indexes, and views • Many SQL constraints can be used with columns • Aggregate functions – Special functions that perform arithmetic computations over a set of rows Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 78

6 Summary (continued) • ORDER BY clause – Used to sort output of a SELECT statement – Can sort by one or more columns and use either an ascending or descending order • Join output of multiple tables with SELECT statement • Natural join uses join condition to match only rows with equal values in specified columns • Right outer join and left outer join used to select rows that have no matching values in other related table Database Systems: Design, Implementation, & Management, 6 th Edition, Rob & Coronel 79