5 chapter 5 structured query language (sql) hachim haddouti

5

Chapter 5

Structured Query Language (SQL)

Hachim Haddouti

Hachim Haddouti and Rob & Coronel, Ch5 2

5

In this chapter, you will learn:

• Basic commands and functions of SQL• SQL for data manipulation (DML) • 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) DDL• Advanced SQL features such as views, stored

procedures, and triggers


5

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

– Non-procedural language (only what and not how)


5

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• Note:

– SQL: as successor of the prototype language SEQUEL (IBM Almaden Research Center San Jose, around 1975).

– 2 knowm version of SQL (SQL2 also called SQL-92, and SQL99 or SQL 3).

– For a complete pecification of SQL see [ Melton93]. About 700 pages or www.ansi.org or A Guide to the SQL Standard [DATE97].

– But, some dialects (difference is minor)


5

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


5

Creating Table Structure

• Used to describe layout of a table

• Tables store end-user data• May be based on data dictionary entries• Typical restrictions placed by DBMS

– Names cannot exceed 18 characters– Names must start with a letter– Names can contain only letters, numbers, and underscores

(_)– Names cannot contain spaces


5Typical Data Types

• INTEGER– Numbers without a decimal point

• SMALLINT– Uses less space than INTEGER (up to 6 digits)

• DECIMAL(p,q)– P number of digits; q number of decimal places

• CHAR(n)– Character string n places long (fixed length)

• VARCHAR(n)– Variable Length string up to n characters long

• DATE– Dates in DD-MON-YYYY or MM/DD/YYYY


5

Creating Table Structure cont.

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

CREATE TABLE CUSTOMER(customer_ID INTEGER NOT NULL UNIQUE, customer_name char(25) NOT NULL, PRIMARY KEY (customer_id));


5

Creating Table Structure cont.

CREATE TABLE EMPLOYEE (

EMP_NUM CHAR(3) NOT NULL,

EMP_LNAME CHAR(15) NOT NULL,

EMP_FNAME CHAR(15) NOT NULL,

EMP_INITIAL CHAR(1),

EMP_HIREDATE DATE NOT NULL,

JOB_CODE CHAR(3),

PRIMARY KEY (EMP_NUM),

FOREIGN KEY (JOB_CODE) REFERENCES JOB);

Note: Composite key!


5

Using Domains

• Domain is set of permissible values for a column• Definition requires:

– Name– SQL common Data type– Default value– Domain constraint or condition

CREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>][CHECK (<condition>)]

CREATE DOMAIN WEEK AS VARCHAR(15)CHECK ( VALUE IN (‘Monday’, ‘Tuesday’, … ‘Sunday’));

Note: in Oracle 8i use CREATE TYPE


5SQL 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

CREATE TABLE EMPLOYEE(EMP_NUM CHAR(25) NOT NULL UNIQUE, ..., PRIMARY KEY (EMP_NUM));

CREATE TABLE link( ..., JOBCODE Integer NOT NULL, FOREIGN KEY (JOBCODE) REFERENCES table2);


5

Data Manipulation Commands

Common SQL Commands


5

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


5

Data Entry and Saving cont.

INSERT INTO EMPLOYEE

VALUES ('101', 'News', 'John', 'G', '11/8/94', '502');

INSERT INTO EMPLOYEE

VALUES ('102', 'Senior', 'David', 'H', '7/12/87', '501');


5

Result of Create and Insert

EMP_NUM EMP_LNAME EMP_FNAME EMP_INITIAL EMP_HIREDATE

JOB_CODE

101 News John G 08-Nov-94 502

102 Senior David H 12-Jul-87 501

After many Inserts:


5

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 (undoes all updates performed by the transaction)

• DELETE command removes table row

SELECT <attribute names> FROM <table names>;


5

Motivating example: Rollback

– Transfer of money from one account to another involves two steps:

• deduct from one account and credit to another

– If one steps succeeds and the other fails, database is in an inconsistent state

– Therefore, either both steps should succeed or neither should

• If any step of a transaction fails, all work done by the transaction can be undone by rollback work.

• Rollback of incomplete transactions is done automatically, in case of system failures


5

Queries• Creating partial listings of table contents

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

Mathematical Operators


5

Examples

• Mathematical operators

• Mathematical operators on character attributes

• Mathematical operators on dates

SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, JOB_CODE

FROM EMPLOYEEWHERE JOB_CODE = 500;

SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, EMP_INITIAL FROM EMPLOYEEWHERE EMP_INITIAL 500 > ‘G’;

SELECT EMP_LNAME, EMP_FNAME, EMP_NUM, EMP_HIREDATE

FROM EMPLOYEE WHERE EMP_HIREDATE >= ‘01/20/2002’;


5

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;


5

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;


5

Examples of Special Operators• SELECT * FROM PRODUCT

WHERE P_PRICE BETWEEN 50.000 AND 100.000;

• SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE P_MIN IS NULL

• SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME LIKE ‘S%’

• SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME LIKE ‘Ha_him’

• SELECT EMP_LNAME FROM EMPLOYEE WHERE EMP_LNAME NOT LIKE ‘Ha_him’

• SELECT EMP_LNAME FROM EMPLOYEE WHERE UPPER(EMP_LNAME) LIKE ‘HA_HIM’

• Note: some other RDBMS use * and ? Instead of % and _


5Advanced Data Management

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

ALTER TABLE Employee MODIFY (Emp_Lname CHAR (35)); /* this will work only in the case that the column is empty*/

NOTE: Be careful by including NOT NULL clause in add a new column


5

Advanced Data ManagementCommands cont.

DELETE FROM EMPLOYEE WHERE EMP_NUM = ‘102’;

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

•Entering data into new column

• Deleting Table Rows


5

Query Example

• Assume: account (account-number, branch-name, balance)

• Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%.

–Write two update statements:•update account

set balance = balance 1.06where balance > 10000

•update accountset balance = balance 1.05where balance 10000


5

Advanced Data Management Commands (con’t.)

• Dropping a column

• Arithmetic operators and rules of precedence

ALTER TABLE VENDORDROP COLUMN V_ORDER;


5

Advanced Data Management Commands (con’t.)

• Copying parts of tables

• Deleting a table from database• DROP TABLE PART;

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

ALTER TABLE LINEADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER)ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICEADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;


5

More Complex Queries and SQL Functions

• Ordering a listing ( always last in command sequence)

• Results ascending by default– Descending order uses DESC

• Cascading order sequence

ORDER BY <attributes>

ORDER BY <attributes> DESC

ORDER BY <attribute 1, attribute 2, ...>


5

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;

Num of non-null rows in a given columnMin attribute valueMax attribute valueSum of of all selected attribute valuesArithmetic average for the give column


5Example Aggregate

Function Operations• 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);


5

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;

NOTE: Except COUNT, all aggregations apply to a single attribute (eg. SELECT Count(*) FROM Purchase)


5

Example Query

• Assume: account (account-number, branch-name, balance)

• Delete the record of all accounts with balances below the average at the bank.

delete from account where balance < (select avg (balance)

from account)


5


• Grouping data– Creates frequency distributions– Only valid when used with SQL arithmetic functions

– HAVING clause operates like WHERE for grouping output , not to columns

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;


5

Query Example

SELECT Std_Name, STD_GPAFROM StudentGROUP BY STD_GPA;

Assume the following table:Student (SID, Std_Name, STD_Address, STD_GPA)

Is the following SQL statement correct?

And this? COUNTS HOW MANY PRODCUT EACH VENDOR PROVIDES

SELECT V_CODE, COUNT(DISTINCT P_CODE) FROM PRODUCT GROUP BY V_CODE;


5

Query Example cont.

select Std_Name, avg (Std_GPA)from Student

group by Std_Namehaving avg (Std_GPA) > 3

Find the names of all students where the average GPAis more than 3.0.


5More Complex Queries and

SQL Functions (con’t.)

• Virtual tables: creating a view– CREATE VIEW command

– Creates logical table existing only in virtual memory

– Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.

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


5


• Joining database tables– Data are retrieved from more than one table

– Recursive queries joins a table to itself (List of all employees with their manager’s name?)

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 BWHERE A.EMP_MGR=B.EMP_NUMORDER BY A.EMP_MGR


5

Query Example

Relation loan Relation borrower

Customer_name Loan_number

Jones

Smith

Hayes

L-170

L-230

L-155

amount

3000

4000

1700

Branch_name

Downtown

Redwood

Perryridge

Loan_number

L-170

L-230

L-260

SELECT * FROM loan L, borrower B

WHERE L.loan_number = B.loan_number

Branch_name amount

Downtown

Redwood

3000

4000

Customer_name Loan_number

Jones

Smith

L-170

L-230

Loan_number

L-170

L-230


5

Nested Subqueries

• SQL provides a mechanism for the nesting of subqueries.

• A subquery is a select-from-where expression that is nested within another query.

• A common use of subqueries is to perform tests for set membership, set comparisons, and set cardinality.


5

Query Example• Find all customers who have both an account and

a loan at the bank.

• Find all customers who have a loan at the bank but do not have an account at the bank

select distinct customer-namefrom borrowerwhere customer-name not in (select customer-name

from depositor)

select distinct customer-namefrom borrowerwhere customer-name in (select customer-name

from depositor)


5

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


5Procedural SQL

• SQL shortcomings– Doesn’t support execution of stored procedures based on

logical condition (IF THEN, DO WHILE)

– 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 (Oracle)


5

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

• SQL provides a module language – permits definition of procedures in SQL, with if-then-

else statements, for and while loops, etc


5

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


5

Triggers (con’t.)

• An example (for oracle)

CREATE OR REPLACE TRIGGER <trigger_name>[BEFORE/AFTER][DELETE/INSERT/UPDATE OF <column_name] ON <table_name>[FOR EACH ROW]BEGIN

PL/SQL instructions;……………

END;

See a concret example on page 270;


5

Stored Procedures

• Named collection of procedural and SQL statements stored in database e.g. to represent multiple update transactions

• Transmitted and executed as entire reduces network traffic and improve performance

• permit external applications to operate on the database without knowing about internal details

• Invoked by name


5

Stored Procedures• Executed as unit

• Invoked with EXEC or call

CREATE OR REPLACE PROCEDURE procedure_name (argument IN/OUT data-type, etc) IS/AS BEGIN

DECLARE variable name and data typePL/SQL or SQL statements;

END;

EXEC store_procedure_name (parameter, parameter, …)


5

Embedded SQL, see also procedural SQL

• The SQL standard defines embeddings of SQL in a variety of programming languages such as Cobol, Pascal, Fortran, C, and Java.

• A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.

• EXEC SQL statement is used to identify embedded SQL request to the preprocessor

EXEC SQL <embedded SQL statement > END-EXEC

Note: this varies by language. E.g. the Java embedding uses

# SQL {<embedded SQL statement > } ;


5

Example Query

• Specify the query in SQL and declare a cursor for it

EXEC SQLdeclare c cursor for select customer-name, customer-cityfrom depositor D, customer C, account Awhere D.customer_name = C.customer_name and D.account_number = A.account_number

and account.balance > :amountEND-EXEC

From within a host language, find the names and cities of customers with more than the variable amount dollars in some account.


5

Embedded SQL (Cont.)

• The open statement causes the query to be evaluatedEXEC SQL open c END-EXEC

• The fetch statement causes the values of one tuple in the query result to be placed on host language variables.

EXEC SQL fetch c into :cn, :cc END-EXECRepeated calls to fetch get successive tuples in the query result

• The close statement causes the database system to delete the temporary relation that holds the result of the query.

EXEC SQL close c END-EXEC

Note: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples.


5

Dynamic SQL

• Allows programs to construct and submit SQL queries at run time (programs can create SQL queries qt run time).

• Example of the use of dynamic SQL from within a C program. In contrast to embedded SQL, SQL statements are precompiled.

• char * sqlprog = “update account set balance = balance * 1.05 where account-number = ?”EXEC SQL prepare dynprog from :sqlprog;char account [10] = “A-101”;EXEC SQL execute dynprog using :account;

• The dynamic SQL program contains a ?, which is a place holder for a value that is provided when the SQL program is executed.


5

ODBC

• Open DataBase Connectivity(ODBC) standard – standard for application program to communicate

with a database server.

– Initiated by Microsoft

– application program interface (API) to • open a connection with a database, • send queries and updates, • get back results.

• Applications such as GUI, spreadsheets, etc. can use ODBC


5ODBC (Cont.)

• Each database system supporting ODBC provides a "driver" library that must be linked with the client program.

• When client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results.

• ODBC program first allocates an SQL environment, then a database connection handle.

• Opens database connection using SQLConnect(). Parameters for SQLConnect:– connection handle,– the server to which to connect– the user identifier, – password

• Must also specify types of arguments:– SQL_NTS denotes previous argument is a null-terminated

string.


5ODBC Code

• int ODBCexample()

{

RETCODE error;

HENV env; /* environment */

HDBC conn; /* database connection */

SQLAllocEnv(&env);

SQLAllocConnect(env, &conn);

SQLConnect(conn, "aura.bell-labs.com", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS);

{ …. Do actual work … }

SQLDisconnect(conn);

SQLFreeConnect(conn);

SQLFreeEnv(env);

}


5

JDBC

• JDBC is a Java API for communicating with database systems supporting SQL, initiated by SUN

• JDBC supports a variety of features for querying and updating data, and for retrieving query results

• JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes

• Model for communicating with the database:– Open a connection– Create a “statement” object– Execute queries using the Statement object to send

queries and fetch results– Exception mechanism to handle errors


5

JDBC Code

public static void JDBCexample(String dbid, String userid, String passwd)

{ try {

Class.forName ("oracle.jdbc.driver.OracleDriver"); Connection conn =

DriverManager.getConnection( "jdbc:oracle:thin:@aura.bell-labs.com:2000:bankdb", userid, passwd);

Statement stmt = conn.createStatement(); … Do Actual Work …. stmt.close(); conn.close(); } catch (SQLException sqle) { System.out.println("SQLException : " + sqle); }

}


5

JDBC Code (Cont.)• Update to database

try { stmt.executeUpdate( "insert into account values

('A-9732', 'Perryridge', 1200)"); } catch (SQLException sqle) { System.out.println("Could not insert tuple. " + sqle);}

• Execute query and fetch and print results ResultSet rset = stmt.executeQuery( "select branch_name,

avg(balance) from account group by branch_name");

while (rset.next()) {System.out.println(

rset.getString("branch_name") + " " + rset.getFloat(2));

}


5

JDBC Code Details

• Getting result fields:– rs.getString(“branchname”) and rs.getString(1)

equivalent if branchname is the first argument of select result.

• Dealing with Null valuesint a = rs.getInt(“a”);

if (rs.wasNull()) Systems.out.println(“Got null value”);


5

Review: Link correctly

• SELECT * FROM Company WHERE

country=“Maroc” AND stockPrice > 50

• SELECT name, stock price FROM Company WHERE

country=“Maroc” AND stockPrice > 50

• Selections

• Projection


5

Review: Resulting Table?

• SELECT name AS company, stockprice AS price FROM Company WHERE country=“Maroc” AND stockPrice > 50


5

Procedural or non-procedural?

Open Table

Go to row where Name = „Franz“ (using index)

If found:

While:

Print

Read table rows in a seauence by using the index Name

At the end of Table leave While

While end

....

5 chapter 5 structured query language (sql) hachim haddouti

Documents