Database 6

saikrupa123

Chapter7_IntrotoSQL.ppt

Home >Information Systems homework help >Database 6

Database Systems Design, Implementation, and Management

Coronel | Morris

11e

Chapter 7

Introduction to Structured Query Language (SQL)

Learning Objectives

In this chapter, you will learn:
The basic commands and functions of SQL
How to use SQL for data administration (to create tables and indexes)
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

Structured Query Language (SQL)

Categories of SQL function
Data definition language (DDL)
Data manipulation language (DML)
Nonprocedural language with basic command vocabulary set of less than 100 words
Differences in SQL dialects are minor

Table 7.1 - SQL Data Definition Command

Table 7.2 - SQL Data Manipulation Commands

Figure 7.1 - The Database Model

Tasks to be Completed Before Using a New RDBMS

Create database structure
RDBMS creates physical files that will hold database
Differs from one RDBMS to another
Authentication: Process DBMS uses to verify that only registered users access the data
Required for the creation tables
User should log on to RDBMS using user ID and password created by database administrator

The Database Schema

Logical group of database objects related to each other
Command
CREATE SCHEMA AUTHORIZATION {creator};
Seldom used directly

Common SQL Data Types

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
Features of table creating command sequence
NOT NULL specification
UNIQUE specification
Syntax to create table
CREATE TABLE tablename();

Primary Key and Foreign Key

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 semicolon
ANSI SQL allows use of following clauses to cover CASCADE, SET NULL, or SET DEFAULT
ON DELETE and ON UPDATE

SQL Constraints

Ensures that column does not accept nulls

NOT NULL

Ensures that all values in column are unique

UNIQUE

Assigns value to attribute when a new row is added to table

DEFAULT

Validates data when attribute value is entered

CHECK

SQL Indexes

When primary key is declared, DBMS automatically creates unique index
Composite index:
Is based on two or more attributes
Prevents data duplication
Syntax to create SQL indexes
CREATE INDEX indexname ON tablename();
Syntax to delete an index
DROP INDEX indexname;

Data Manipulation Commands

Inserting Table Rows with a
SELECT Subquery

Syntax
INSERT INTO tablename SELECT columnlist FROM tablename
Used to add multiple rows using another table as source
SELECT command - Acts as a subquery and is executed first
Subquery: Query embedded/nested inside another query

Selecting Rows Using Conditional Restrictions

Following syntax enables to specify which rows to select
SELECT columnlist
FROM tablelist
[WHERE conditionlist];
Used to select partial table contents by placing restrictions on the rows
Optional WHERE clause
Adds conditional restrictions to the SELECT statement

Comparison Operators

Add conditional restrictions on selected table contents
Used on:
Character attributes
Dates

Table 7.6 - Comparison Operators

Comparison Operators: Computed Columns and Column Aliases

SQL accepts any valid expressions/formulas in the computed columns
Alias: Alternate name given to a column or table in any SQL statement to improve the readability
Computed column, an alias, and date arithmetic can be used in a single query

Arithmetic operators

The Rule of Precedence: Establish the order in which computations are completed
Perform:
Operations within parentheses
Power operations
Multiplications and divisions
Additions and subtractions

Table 7.7 - The Arithmetic Operators

Figure 7.12 - Selected PRODUCT Table Attributes: The logical OR

Figure 7.13 - Selected PRODUCT Table Attributes: The Logical AND

Figure 7.14 - Selected PRODUCT Table Attributes: The Logical AND and OR

Special Operators

Advanced Data Definition Commands

ALTER TABLE command: To make changes in the table structure
Keywords use with the command
ADD - Adds a column
MODIFY - Changes column characteristics
DROP - Deletes a column
Used to:
Add table constraints
Remove table constraints

Changing 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
Syntax –
ALTER TABLE tablename MODIFY (columnname(datatype));

Changing Column’s Data Characteristics

Use ALTER to change data characteristics
Changes in column’s characteristics are permitted if changes do not alter the existing data type
Syntax
ALTER TABLE tablename MODIFY (columnname(characterstic));

Adding Column, Dropping Column

Adding a column
Use ALTER and ADD
Do not include the NOT NULL clause for new column
Dropping a column
Use ALTER and DROP
Some RDBMSs impose restrictions on the deletion of an attribute

Advanced Data Updates

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

Copying Parts of Tables

SQL permits copying contents of selected table columns
Data need not be reentered manually into newly created table(s)
Table structure is created
Rows are added to new table using rows from another table

Adding Primary and Foreign Key Designations

ALTER TABLE command
Followed by a keyword that produces the specific change one wants to make
Options include ADD, MODIFY, and DROP
Syntax to add or modify columns
ALTER TABLE tablename
{ADD | MODIFY} ( columnname datatype [ {ADD | MODIFY} columnname datatype] ) ;
ALTER TABLE tablename
ADD constraint [ ADD constraint ] ;

Deleting a Table from the Database

DROP TABLE: Deletes table from database

Syntax - DROP TABLE tablename;
Can drop a table only if it is not the one side of any relationship
RDBMS generates a foreign key integrity violation error message if the table is dropped

Additional SELECT Query Keywords

Logical operators work well in the query environment
SQL provides useful functions that:
Counts
Find minimum and maximum values
Calculate averages
SQL allows user to limit queries to entries:
Having no duplicates
Whose duplicates may be grouped

Ordering a Listing

ORDER BY clause is useful when listing order is important
Syntax - SELECT columnlist

FROM tablelist

[WHERE conditionlist]

[ORDER BY columnlist [ASC | DESC]];

Cascading order sequence: Multilevel ordered sequence
Created by listing several attributes after the ORDER BY clause

Listing Unique Values

DISTINCT clause: Produces list of values that are unique
Syntax - SELECT DISTINCT columnlist

FROM tablelist;

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

Table 7.8 - Some Basic SQL Aggerate Functions

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]];

HAVING Clause

Extension of GROUP BY feature
Applied to output of GROUP BY operation
Used in conjunction with GROUP BY clause in second SQL command set
Similar to WHERE clause in SELECT statement

Joining Database Tables

Performed when data are retrieved from more than one table at a time
Equality comparison between foreign key and primary key of related tables
Tables are joined by listing tables in FROM clause of SELECT statement
DBMS creates Cartesian product of every table in the FROM clause