SQL – Structured Query Language

2

Relational Database Operators• Relational algebra determines

table manipulations• Key operators

• SELECT• PROJECT• JOIN

• Other operators• UNION• INTERSECT• DIFFERENCE• PRODUCT• DIVIDE

3

Union

Combines all rows

Figure 2.5

4

Yields rows that appear in both tablesIntersect

Figure 2.6

5

Yields rows not found in other tables

Difference

Figure 2.7

6

Yields all possible pairs from two tables

Product

Figure 2.8

7

Yields a subset of rows based on specified criterion

Select

Figure 2.9

8

Yields all values for selected attributes

Project

Figure 2.10

9

Information from two or more tables is combined

Join

Figure 2.11

Figure 2.14

10

• Links tables by selecting rows with common values in common attribute(s)

• Three-stage process• Product creates one table• Select yields appropriate rows• Project yields single copy of each attribute to

eliminate duplicate columns

Natural Join Process

11

Other Joins• EquiJOIN

• Links tables based on equality condition that compares specified columns of tables

• Does not eliminate duplicate columns • Join criteria must be explicitly defined

• Theta JOIN• EquiJOIN that compares specified columns of each

table using operator other than equality one• Outer JOIN

• Matched pairs are retained • Unmatched values in other tables left null• Right and left

12

Requires user of single-column table and two-column table

Divide

Figure 2.17

13

Data Dictionary and System Catalog• Data dictionary

• Provides detailed account of all tables found within database

• Metadata• Attribute names and characteristics

• System catalog• Detailed data dictionary• System-created database • Stores database characteristics and contents• Tables can be queried just like any other tables• Automatically produces database documentation

14

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 (DDL)• Data manipulation language (DML)

15

DDL vs. DML examplesName DDL: Data Definition

Language DML: Date Manipulation Language

Purpose Defines Structure of Database and Database Objects

Manipulates the Data housed in the tables

Add Create table: creates a new table

Insert Into: adds a new record to a table

Change Alter table: modifies the tables structure (add a column, change a datatype, add constraint, etc.)

Update: changes the values of an attribute in a record

Remove Drop table: deletes the table from the database

Delete: deletes a record from a table

16

Most Common Data TypesData Type Data Type Description

CHAR(n) • fixed length column can contain any printable characters. • If the data entered into CHAR field < length of field, field is

padded with spaces.• maximum length of CHAR column = 200.e.g: a state abbreviation - CHAR(2) since it is always 2

characters long.

VARCHAR2(n) variable length column with a fixed length. If the length of the data is less than the maximum length of the field, then the field is not padded with spaces.

• maximum length of the column = 2000.e.g: a customer’s first name - VARCHAR2(35) since name

length is variable.

NUMBER Integer and real values occupying up to 40 spaces.

INTEGER Same as number, but no decimals.

DATE contains a date and time between the 1st of January 4712 BC to the 31st of December 4712 AD.

• standard date format: DD-MMM-YY (i.e. 01-JAN-99)• Any other format will require input mask.

17

Creating Table Structure

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

Column Names

Data types

and size

Null vs. Not Null (can the field be left blank when data entered

NOTE: null <> 0

Check constraints – show acceptable values for attribute

and/or default value.

18

Drop – table or column

• Delete table from database: • Drop table <tablename> cascade constraints

e.g. drop table student cascade constaints

• Delete column from database• Alter table <tablename> drop column <column name>

e.g. alter table student drop column student_lastname

19

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

20

SQL Integrity Constraints and Data Anomolies

• Entity integrity - enforced automatically with PRIMARY KEY constraint

• Referential integrity - enforced FOREIGN KEY constraint

• Other specifications to ensure conditions met:• ON DELETE RESTRICT• ON UPDATE CASCADE

21

Data Anomalies, Redundacy, and constraints

• Data anomalies• Modification• Insertion• Deletion ON DELETE RESTRICT

ON UPDATE CASCADE

PRIMARY KEY constraint

22

Alter, Add, and Modify

• ALTER - changes table structure• ADD - adds column• MODIFY - changes column characteristics

ALTER TABLE <table name>ADD (<column name> <new column characteristics>);

ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>);

23

Data Manipulation Commands

Common SQL DML Commands

Table 5.3

24

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

25

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

26

Queries• Creating partial listings of table contents

SELECT <column(s)>FROM <table name>WHERE <conditions>;

Table 5.4 Mathematical Operators

27

Examples• Mathematical operators

• Mathematical operators on character attributes

• Mathematical operators on dates

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE <> 21344;

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

28

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;

29

Operators• 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

SELECT *FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288;

30

Drop column and update

• Dropping a column

• Update (add new data to a column)

ALTER TABLE VENDORDROP COLUMN V_ORDER;

Table 5.5

UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’;

31

Arithmetic Operators – Rules of Precedence

• Arithmetic operators and rules of precedence

Table 5.5

32

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 LINEADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER)ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICEADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;

33

Aggregate Function OperationsCount, Max and Min• COUNT

• MAX and MIN

SELECT COUNT(DISTINCT V_CODE)FROM PRODUCT;

SELECT COUNT(DISTINCT V_CODE)FROM PRODUCTWHERE P_PRICE <= 10.00;

SELECT MIN(P_PRICE)FROM PRODUCT;

SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = MAX(P_PRICE);

34

Aggregate Function Operaters Sum/ Avg

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

35

Order by

• 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, ...>

36

Distinct

• Listing unique values

• DISTINCT clause produces list of different values

SELECT DISTINCT V_CODE FROM PRODUCT;

37

Group by/ Having• 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_2GROUP BY V_CODEHAVING AVG(P_PRICE) < 10;

38

Joins• Joining database tables

• Data retrieved from more than one table

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;