Database Fundamentals- Content Analysis

wostinabin2
Lecture8-ch08.ppt
Home>Information Systems homework help>Database Fundamentals- Content Analysis

Database Principles: Fundamentals of Design, Implementations and Management

Lecture7- CHAPTER 8 : Beginning Structured Query Language

Presented by Rabia Cherouk

*

Objectives

  • 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 for useful information

*

Introduction to SQL

  • SQL functions fit into two broad categories:
  • Data definition language (DDL)
  • Create database objects, such as tables, indexes, and views
  • Define access rights to those database objects
  • Data manipulation language (DML)
  • SQL is relatively easy to learn
  • Basic command set has vocabulary of less than 100 words – Non-procedural language
  • American National Standards Institute (ANSI) prescribes a standard SQLand standards are accepted by ISO (International Organisation for Standardisation) .
  • Several SQL dialects exist

Introduction to SQL (cont..)

*

Introduction to SQL (cont..)

*

*

Data Definition Commands

  • The database model
  • In this chapter, a simple database with these tables is used to illustrate commands:
  • CUSTOMER
  • INVOICE
  • LINE
  • PRODUCT
  • VENDOR
  • Focus on PRODUCT and VENDOR tables

*

The Database Model

Figure 8.1 in your book

The Database Model (cont..)

*

*

Creating the Database

  • Two tasks must be completed:

1/ Create database structure

2/ Create tables that will hold end-user data

  • First task:
  • RDBMS creates physical files that will hold database
  • Differs substantially from one RDBMS to another

*

The Database Schema

  • Authentication
  • Process through which DBMS verifies that only registered users are able to access database
  • Log on to RDBMS using user ID and password created by database administrator
  • Schema
  • Is a group of database objects—such as tables and indexes— that are related to each other. Usually a schema belongs to a single user or application. A single database can hold multiple schemas belonging to different users or applications.

*

Data Types

  • Data type selection is usually dictated by nature of data and by intended use
  • Pay close attention to expected use of attributes for sorting and data retrieval purposes
  • Supported data types:
  • Number(L,D), Integer, Smallint, Decimal(L,D)
  • Char(L), Varchar(L), Varchar2(L)
  • Date, Time, Timestamp
  • Real, Double, Float
  • Interval day to hour
  • Many other types

Data Types (cont..)

*

*

Creating Table Structures

  • Use one line per column (attribute) definition
  • Use spaces to line up 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 for foreign keys

*

Creating Table Structures (cont..)

  • Command sequence ends with semicolon

Example:

CREATE TABLE EMP_2

( EMP_NUM CHAR(3) NOT NULL UNIQUE,

EMP_LNAME VARCHAR(15) NOT NULL,

EMP_FNAME VARCHAR(15) NOT NULL,

EMP_INITIAL CHAR(1),

EMP_HIRE DATE NOT NULL,

JOB_CODE CHAR(3) NOT NULL,

PRIMARY KEY (EMP_NUM),

FOREIGN KEY (JOB_CODE) REFERENCES JOB);

 

*

SQL Constraints

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

*

SQL Indexes

  • When primary key is declared, DBMS automatically creates unique index
  • Often need additional indexes
  • Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute
  • Composite index
  • Index based on two or more attributes
  • Often used to prevent data duplication

SQL Indexes (cont..)

*

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

*

*

*

Data Manipulation Commands

  • INSERT
  • SELECT
  • COMMIT
  • UPDATE
  • ROLLBACK
  • DELETE

*

Adding Table Rows

  • INSERT
  • Used to enter data into table
  • Syntax:
  • INSERT INTO columnname
    VALUES (value1, value2, … , valueN);

*

Adding Table Rows (cont..)

  • When entering values, notice that:
  • Row contents are entered between parentheses
  • Character and date values are entered between apostrophes
  • Numerical entries are not enclosed in apostrophes
  • Attribute entries are separated by commas
  • A value is required for each column
  • Use NULL for unknown values

*

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

*

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

Listing Table Rows (cont..)

*

*

Updating Table Rows

  • UPDATE
  • Modify data in a table
  • Syntax:

UPDATE tablename
SET columnname = expression [, columnname = expression]
[WHERE conditionlist];

  • If more than one attribute is to be updated in row, separate corrections with commas

*

Restoring Table Contents

  • ROLLBACK
  • Used to restore database to its previous condition
  • Only applicable if COMMIT command has not been used to permanently store changes in database
  • Syntax:
  • ROLLBACK;
  • COMMIT and ROLLBACK only work with manipulation commands that are used to add, modify, or delete table rows

*

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 specified table will be deleted

*

Inserting Table Rows with a
SELECT Subquery

  • INSERT
  • Inserts multiple rows from another table (source)
  • Uses SELECT subquery
  • Subquery: query that is embedded (or nested) inside another query
  • Subquery is executed first
  • Syntax:

INSERT INTO tablename SELECT columnlist FROM tablename;

*

SELECT Queries

  • Fine-tune SELECT command by adding restrictions to search criteria using:
  • Conditional restrictions
  • Arithmetic operators
  • Logical operators
  • Special operators

*

Selecting Rows with
Conditional Restrictions

  • Select partial table contents by placing restrictions on rows to be included in output
  • Add conditional restrictions to SELECT statement, using WHERE clause
  • Syntax:

SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;

Selecting Rows with
Conditional Restrictions (continued)

*

Selecting Rows with
Conditional Restrictions (continued)

*

*

Selecting Rows with
Conditional Restrictions (cont..)

Selecting Rows with
Conditional Restrictions (continued)

*

Selecting Rows with
Conditional Restrictions (cont..)

*

Selecting Rows with
Conditional Restrictions (continued)

*

Selecting Rows with
Conditional Restrictions (continued)

*

Selecting Rows with
Conditional Restrictions (cont..)

*

Selecting Rows with
Conditional Restrictions (continued)

*

Selecting Rows with
Conditional Restrictions (continued)

*

*

Arithmetic Operators:
The Rule of Precedence

  • Perform operations within parentheses
  • Perform power operations
  • Perform multiplications and divisions
  • Perform additions and subtractions
Table 8.7 in your book in T

*

Logical Operators: AND, OR, and NOT

  • Searching data involves multiple conditions
  • Logical operators: AND, OR, and NOT
  • Can be combined
  • Parentheses placed to enforce precedence order
  • Conditions in parentheses always executed first
  • Boolean algebra: mathematical field dedicated to use of logical operators
  • NOT negates result of conditional expression

*

Special Operators

  • BETWEEN: checks whether attribute value is within a range
  • IS NULL: checks whether attribute value is null
  • LIKE: checks whether attribute value matches given string pattern
  • IN: checks whether attribute value matches any value within a value list
  • EXISTS: checks if subquery returns any rows

*

Advanced Data Definition Commands

  • All changes in table structure are made by using ALTER command
  • Three options
  • ADD adds a column
  • MODIFY changes column characteristics
  • DROP deletes a column
  • Can also be used to:
  • Add table constraints
  • Remove table constraints

*

Changing a Column’s Data Type

  • ALTER can be used to change data type
  • Some RDBMSs do not permit changes to data types unless column is empty

Changing a Column’s Data Characteristics

  • Use ALTER to change data characteristics
  • Changes in column’s characteristics permitted if changes do not alter the existing data type

*

Adding a Column
Dropping a Column

  • Use ALTER to add column
  • Do not include the NOT NULL clause for new column
  • Use ALTER to drop column
  • Some RDBMSs impose restrictions on the deletion of an attribute

*

Summary

  • SQL commands can be divided into two overall categories:
  • Data definition language commands
  • Data manipulation language commands
  • The ANSI standard data types are supported by all RDBMS vendors in different ways
  • Basic data definition commands allow you to create tables, indexes, and views

*

Summary (cont..)

  • DML commands allow you to add, modify, and delete rows from tables
  • The basic DML commands:
  • SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK
  • SELECT statement is main data retrieval command in SQL

*

Summary (cont..)

  • WHERE clause can be used with SELECT, UPDATE, and DELETE statements
  • Aggregate functions
  • Special functions that perform arithmetic computations over a set of rows
  • ORDER BY clause
  • Used to sort output of SELECT statement
  • Can sort by one or more columns
  • Ascending or descending order

*

Summary (cont..)

  • Join output of multiple tables with SELECT statement
  • Join performed every time you specify two or more tables in FROM clause
  • If no join condition specified, DBMX performs Cartesian product
  • Natural join uses join condition to match only rows with equal values in specified columns
  • Right outer join and left outer join select rows with no matching values in other related table

*

Advanced Data Updates

  • UPDATE command updates only data in existing rows
  • If relationship between entries and existing columns, can assign values to slots
  • Arithmetic operators useful in data updates
  • In Oracle, ROLLBACK command undoes changes made by last two UPDATE statements

*

Advanced Data Updates

*

Copying Parts of Tables

  • SQL permits copying contents of selected table columns
  • Data need not be reentered manually into newly created table(s)
  • First create the table structure
  • Next add rows to new table using table rows from another table

Copying Parts of Tables (cont..)

*

*

Adding Primary and Foreign Key Designations

  • When table is copied, integrity rules do not copy
  • Primary and foreign keys manually defined on new table
  • User ALTER TABLE command
  • Syntax:

ALTER TABLE tablename

ADD PRIMARY KEY (fieldname);

  • For foreign key, use FOREIGN KEY in place of PRIMARY KEY

*

Deleting a Table from the Database

  • DROP
  • Deletes table from database
  • Syntax:

DROP TABLE tablename;

  • Can drop a table only if it is not the “one” side of any relationship
  • Otherwise RDBMS generates an error message
  • Foreign key integrity violation

*

Advanced SELECT Queries

  • Logical operators work well in the query environment
  • SQL provides useful functions that:
  • Count
  • Find minimum and maximum values
  • Calculate averages, etc.
  • SQL allows user to limit queries to:
  • Entries having no duplicates
  • Entries whose duplicates may be grouped

*

Ordering a Listing

  • ORDER BY clause useful when listing order important
  • Syntax:

SELECT columnlist

FROM tablelist

[WHERE conditionlist]

[ORDER BY columnlist [ASC | DESC]];

  • Ascending order by default

Ordering a Listing

*

Ordering a Listing (cont..)

*

Ordering a Listing (cont..)

*

*

Listing Unique Values

  • DISTINCT clause produces list of only values that are different from one another
  • Example:

SELECT DISTINCT V_CODE

FROM PRODUCT;

  • Access places nulls at the top of the list
  • Oracle places it at the bottom
  • Placement of nulls does not affect list contents

Listing Unique Values

*

*

Aggregate Functions

  • COUNT function tallies number of non-null values of an attribute
  • Takes one parameter: usually a column name
  • MAX and MIN find highest (lowest) value in a table
  • Compute MAX value in inner query
  • Compare to each value returned by the query
  • SUM computes total sum for any specified attribute
  • AVG function format similar to MIN and MAX

Aggregate Functions

*

Aggregate Functions (cont..)

Figure 8.21 COUNT function output examples

*

Aggregate Functions (cont..)

Figure 8.22 MIN and MAX Output Examples

*

Aggregate Functions (cont..)

Figure 8.23 The total values of all items in the PRODUCT table

*

Aggregate Functions (cont..)

Figure 8.24 AVG Function Output Examples

*

*

Grouping Data

  • Frequency distributions created by GROUP BY clause within SELECT statement
  • Syntax:

SELECT columnlist

FROM tablelist

[WHERE conditionlist]

[GROUP BY columnlist]

[HAVING conditionlist]

[ORDER BY columnlist [ASC | DESC] ] ;

Grouping Data

Figure 8.25 GROUP BY Clause Output Examples

*

Grouping Data (cont..)

Figure 8.27 An application of the HAVING clause

*

*

Virtual Tables: Creating a View

  • View is virtual table based on SELECT query
  • Create view by using CREATE VIEW command
  • Special characteristics of relational view:
  • Name of view can be used anywhere a table name is expected
  • View dynamically updated
  • Restricts users to only specified columns and rows
  • Views may be used as basis for reports

Virtual Tables: Creating a View (cont..)

Figure 8.28 Creating a virtual table using the CREATE VIEW command

*

*

Joining Database Tables

  • Ability to combine (join) tables on common attributes is most important distinction between relational database and other databases
  • Join is performed when data are retrieved from more than one table at a time
  • Equality comparison between foreign key and primary key of related tables
  • Join tables by listing tables in FROM clause of SELECT statement
  • DBMS creates Cartesian product of every table

Joining Database Tables (cont..)

*

Joining Database Tables (cont..)

*

*

Joining Tables with an Alias

  • Alias identifies the source table from which data are taken
  • Alias can be used to identify source table
  • Any legal table name can be used as alias
  • Add alias after table name in FROM clause
  • FROM tablename alias

Joining Database Tables (cont..)

*

*

Recursive Joins - Outer Joins

  • Alias especially useful when a table must be joined to itself
  • Recursive query
  • Use aliases to differentiate the table from itself
  • Two types of outer join
  • Left outer join
  • Right outer join

Recursive Joins

*

Recursive Joins (cont..)

*

Outer Joins

*

Outer Joins (cont..)

*

Grouping Data (cont..)

Figure 8.26 Incorrect and Correct use of the GROUP BY Clause

*