Chapter 5
This presentation is the property of its rightful owner.
Sponsored Links
1 / 39

Chapter 5 PowerPoint PPT Presentation


  • 72 Views
  • Uploaded on
  • Presentation posted in: General

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)

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


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

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


Introduction to sql

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


Good reasons to study sql

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


Data definition commands

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


Creating table structure

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


Using domains

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


Sql integrity constraints

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


Data manipulation commands

Data Manipulation Commands

Common SQL Commands

Table 5.3

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data entry and saving

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


Listing table contents and other commands

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


Queries

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


Examples

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


Computed columns

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


Operators

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


Advanced data management commands

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


Advanced data management commands con t

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


Advanced data management commands con t1

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


Example aggregate function operations

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


Example aggregate function operations con t

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


More complex queries and sql functions

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


More complex queries and sql functions con t

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


More complex queries and sql functions con t1

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


More complex queries and sql functions con t2

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


More complex queries and sql functions con t3

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


Updatable views

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


Procedural sql

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


Procedural sql con t

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


Triggers

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


Triggers con t

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]BEGINPL/SQL instructions;……………END;

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Stored procedures

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 BEGINDECLARE variable name and data typePL/SQL or SQL statements;END;

EXEC store_procedure_name

(parameter, parameter, …)

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Pl sql stored functions

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 BEGINPL/SQL statements;RETURN (value); ……END;

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Artist database erd and tables

Artist Database ERD and Tables

Figure 5.55

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


General rules governing relationships among tables

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


General rules governing relationships among tables con t

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


General rules governing relationships among tables con t1

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


General rules governing relationships among tables con t2

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


General rules governing relationships among tables con t3

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


General rules governing relationships among tables con t4

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


  • Login