Objectives of SQL. Terminology for Relational Model. Introduction to SQL

(1)

Adapted for a textbook by Date C. J. An Introduction to

Database Systems

Pearson Addison Wesley, 2004

Introduction to SQL

Remigijus GUSTAS Phone: +46-54 700 17 65 E-mail: [email protected] http://www.cs.kau.se/~gustas/

Karlstad University Department of Information Systems

Remigijus Gustas

4-2

Terminology for Relational Model

SQL terms Internal level terms

Relation,Relvar

Objectives of SQL

Database language should allow user to:

create the database and relation structures;

perform insertion, modification, deletion of data from

relations;

perform queries.

Two major components:

Data Definition Language (DDL) Data Manipulation Language (DML)

(2)

Remigijus Gustas

4-4

ISO SQL Data Types

Any given type is either system-defined or user-defined

CREATE TYPE <type name> AS <representation> FINAL ; CREATE TYPE NAME AS CHAR(15) FINAL ;

<representation> is another (system or user-defined) type

Remigijus Gustas

4-5

CREATE TABLE specifies the name

and structure of a base table

CREATE TABLE DEPT ( DEPTNO CHAR(2),

DNAME NAME, BUDGET INTEGER, PRIMARY KEY ( DEPTNO ) ) ;

10000 Marketing

D1

BUDGET DNAME

DEPTNO

DEPT

BNF notation

Material in lowercase enclosed in angle brackets

"<" and ">" represents a syntactic category that appears on the left side of another production rule, and hence must eventually be replaced by specific items chosen by the user.

Vertical bars "|" are used to separate alternatives.

Square brackets "[" and "]" are used to indicate

that the material enclosed in those brackets is optional.

(3)

Remigijus Gustas

4-7

Manipulative operations: INSERT

INSERT INTO DEPT

( DEPTNO, DNAME, BUDGET ) VALUES ( ‘D1’, ’Marketing’, 10000 ) ; INSERT INTO <table name> (<column name> [, < column name>] )

VALUES (<column value> [, < column name>] ) ;

10000 Marketing

D1

DEPTNO

DEPT

Remigijus Gustas

4-8

Manipulative operations: DELETE

DELETE FROM DEPT

WHERE DNAME = ‘Marketing’ ; DELETE FROM <table name> WHERE (<condition>) ;

DEPT

5000 Research

D3

12000 Development

D2

10000 Marketing

D1

DEPTNO

Manipulative operations: UPDATE

UPDATE DEPT SET BUDGET = 20000

WHERE DNAME = ‘Marketing’ ; UPDATE <table name>

SET <column name> = <new value> WHERE <condition> ;

DEPT

12000 Development

D2

20000 Marketing

D1

(4)

Remigijus Gustas

4-10

Relational operators in SQL

Restrict is implemented by SELECT

Subset of rows

Uses WHERE clause to narrow result Uses SELECT * to copy entire table

Project is also implemented by SELECT

Subset of columns

Join is also implemented by SELECT

Result is a single table matched by values in the join

columns of each source table

Uses WHERE clause to specify source tables Uses dot operator to disambiguate column references

Remigijus Gustas

4-11

Restrict is implemented by SELECT

5000 Research

D3

12000 Development

D2

10000 Marketing

D1

DEPTNO

DEPT

12000 Development

D2

10000 Marketing

D1

DEPT#

SELECT DEPTNO, DNAME, BUDGET FROM DEPT

WHERE DNAME <> ’Research’ ;

Result

SELECT * FROM DEPT

WHERE DNAME <> ’Research’ ;

Project is implemented by SELECT

5000 Research

D3

12000 Development

D2

10000 Marketing

D1

DEPTNO

DEPT

5000 D3

12000 D2

10000 D1

BUDGET DEPTNO

SELECT DEPTNO, BUDGET FROM DEPT ;

(5)

Remigijus Gustas

4-13

Join is implemented by SELECT

35K D2 Saito E4 30K D2 Finzi E3 42K D1 Cheng E2 40K D1 Lopez E1 SALARY DEPTNO ENAME EMPNO 5.000K Research D3 12.000K Development D2 10.000K Marketing D1 BUDGET DNAME DEPTNO 35K Saito E4 12.000K Development D2 30K Finzi E3 12.000K Development D2 42K Cheng E2 10.000K Marketing D1 40K Lopez E1 10.000K Marketing D1 SALARY ENAME EMPNO BUDGET DNAME DEPTNO DEPT EMP

SELECT DEPTNO, DNAME, BUDGET, EMPNO, ENAME, SALARY FROM DEPT, EMP

WHERE DEPT.DEPTNO = EMP.DEPTNO ;

Remigijus Gustas

4-14

INSERT, DELETE and UPDATE are

operations on sets

DELETE FROM EMP WHERE DEPTNO = ‘D1’ ;

EMP 35K D2 Saito E4 30K D2 Finzi E3 42K D1 Cheng E2 40K D1 Lopez E1 SALARY DEPTNO ENAME EMPNO UPDATE SUPPLIER

SET STATUS = 2 * STATUS, CITY = ‘Rome’

WHERE CITY = ‘Paris’ ;

Doubles the status of the Parisian suppliers and moves them to Rome.

The Catalog in SQL

Every database has a catalog

A catalog has many schemas, one per user

Each catalog includes one Information Schema

The Information Schema is a collection of all other

schemas as views

Represents a hypothetical Definition Schema for all

(6)

Remigijus Gustas

4-16

The Information Schema - Examples

Tables (includes views)

Views (does not include tables)

Table constraints

Referential constraints

Column constraints

Privileges

Note: All these objects are tables, therefore

they can be queried

Remigijus Gustas

4-17

Views

View is a virtual relation that can be derived from

base relations upon request

CREATE VIEW <viewName> …;

CREATE VIEW GOOD_SUPPLIER

AS SELECT SNUMBER, STATUS, CITY FROM SUPPLIER

WHERE STATUS > 15;

You can operate on the view, for instance:

SELECT SNUMBER, STATUS FROM GOOD_SUPPLIER WHERE CITY = ‘London’;

Views

Creating a view, and then selecting from the view is equivalent to a SELECT sub-query

SELECT GOOD_SUPPLIER.SNUMBER, GOOD_SUPPLIER .STATUS FROM (SELECT SNUMBER, STATUS, CITY

FROM SUPPLIER WHERE STATUS > 15 ) AS GOOD_SUPPLIER;

WHERE GOOD_SUPPLIER.CITY = ‘London’ ;

This query is what is actually executed SELECT SNUMBER, STATUS FROM SUPPLIER WHERE STATUS > 15 AND CITY = ‘London’;

(7)

Remigijus Gustas

4-19

Embedded SQL

SQL statements can be embedded in a host

program

SQL standard includes support for C, COBOL,

PL/1, Java, and many others

Initiated in host via EXEC SQL statement

Any embedded SQL statement is valid after

EXEC SQL

SQL operations retrieve many rows (not just one)

and host languages are generally not equipped to handle more than one row at a time