11

SQLSQLTarek El-ShishtawyTarek El-Shishtawy

Professor Ass. Of Computer EngineeringProfessor Ass. Of Computer Engineering

22

SQL - AdvantagesSQL - Advantages

Stands for Stands for SStructured tructured QQuery uery LLanguageanguageLow training cost – simple languageLow training cost – simple languageFixed rigid simple syntaxFixed rigid simple syntax Independent of Operating systemIndependent of Operating system Independent of Data Base Independent of Data Base Provides interconnection between forms, Provides interconnection between forms,

reprts and all applications with the DBreprts and all applications with the DB

33

SQL CategoriesSQL Categories DDLDDL

Data Definition LanguageData Definition Language Used to create data base elements such as tables, Used to create data base elements such as tables,

viewsviews

DMLDML Data Manipulation LanguageData Manipulation Language Used to process data such as selecting, Inserting, Used to process data such as selecting, Inserting,

deleting datadeleting data

DCLDCL Data Control LanguageData Control Language Used by database administrator to give previelages to Used by database administrator to give previelages to

users, back up, tuning performance,users, back up, tuning performance,

44

DDL, DML, DCL, and the database development DDL, DML, DCL, and the database development process process

55

Relational TablesRelational Tables

Table NameTable NameCOURSECOURSE

Field NamesField NamesCRS : CRS : Code for the course Code for the course CTITLE CTITLE Course TitleCourse TitleCHRSCHRS Course HoursCourse HoursCHRSWCHRSW Course Hours per weekCourse Hours per week

66

Relational ModelRelational Model

CRS CTITLE CHRS CHRSW 701 INTRO TO COMPUTERS 45 15 702 COBOL PROGRAMMING 100 20 703 C PROGRAMMING 90 15 704 SYSTEM ANALYSIS AND DESIGN 90 15 705 FUNDAMENTALS OF DATABASES 45 15 706 CASE TOOLS 60 15 707 DESIGN OF DATABASES 75 15 708 SQL QUERY LANGUAGE 60 15 709 NATURAL PROGRAMMING 60 15 710 SOFTWARE ENGINEERING 60 15 711 SOFTWARE QUALITY ASSURANCE 45 15

77

Relational ModelRelational Model

Note the followingNote the followingThe intersection between each column and The intersection between each column and

row has only one valuerow has only one valueNo two rows has identical valuesNo two rows has identical valuesNo order is assumed.No order is assumed. When inserting new row, its location is not When inserting new row, its location is not

known known The table can be represented asThe table can be represented as

COURSE (COURSE (CRSCRS, CTITLE, CHRS, CHRSW) , CTITLE, CHRS, CHRSW)

88

ORACLE Basic data typesORACLE Basic data types

String typesString typesCHAR(n)CHAR(n)

fixed-length character data fixed-length character data n characters long n characters long

VARCHAR2VARCHAR2(n) (n) variable length character data variable length character data maximum 4000 bytes in oracle 9 Imaximum 4000 bytes in oracle 9 I

LONGLONG variable-length character data variable-length character data up to 4GB up to 4GB Maximum 1 per table Maximum 1 per table

99

ORACLE Basic data typesORACLE Basic data types

Numeric typesNumeric types NUMBER(p,q)NUMBER(p,q)

general purpose numeric data typegeneral purpose numeric data typeTotal width = p, out of which q decimal numbersTotal width = p, out of which q decimal numbers

INTEGERINTEGER(p) (p) signed integer, p digits wide signed integer, p digits wide

FLOAT(p)FLOAT(p)floating point in scientific notation with p binary digits floating point in scientific notation with p binary digits

precision precision

Date/time typeDate/time type DATEDATE length date/time in dd-mm-yyyy/hh:mm:ss form length date/time in dd-mm-yyyy/hh:mm:ss form

1010

Table KeysTable Keys

Primary KeysPrimary KeysUniqueness Uniqueness NOT NULL NOT NULL Minimum Number of columnsMinimum Number of columns

Foreign KeysForeign KeysUsed to relate rows of two or more tablesUsed to relate rows of two or more tablesForeign key at the child table should be of the Foreign key at the child table should be of the

same type (and values) of the primary key at same type (and values) of the primary key at the parent table the parent table

1111

Example of relationshipsExample of relationships

OFF LOC

01 RIYAD

02 JEDDAH

Table LOCATION

Parent

CRS OFF ODATE

701 01 21-SEP-91

701 02 16-NOV-91

702 01 26-NOV-91

702 02 01-FEB-92

Table Offer

Child

Primary Key

Foreign KeyComes from

LOCATION.OFF

1212

Example of Database TablesExample of Database Tables COURSE (COURSE (CRSCRS, CTITLE, CHRS, CHRSW) , CTITLE, CHRS, CHRSW) OFFER (OFFER (CRSCRS, , OFFOFF, ODATE), ODATE) LOCATION (LOCATION (OFF,OFF, LOC) LOC)

LO C A T IO N

O F FL O C

C O U R S E

C R SC T I T L EC HR SC HR S W

O F F E R

C R S

O F F

O D AT E

1313

QuestionsQuestionsDetermine suitable data types and lengths Determine suitable data types and lengths

for fields in previous tablesfor fields in previous tables If we want to add data for PERSONS who If we want to add data for PERSONS who

ATTEND offer of courses. How many ATTEND offer of courses. How many tables should be added? What relations tables should be added? What relations are required?are required?Note data of persons are ID, Name, and Note data of persons are ID, Name, and

address.address.Determine also primary and foreign key for Determine also primary and foreign key for

new tables and suitable field types and new tables and suitable field types and widths.widths.

1414

Selecting data from the tableSelecting data from the table

General FormGeneral FormSelectSelect Column Names Column Names

FromFrom Table Name Table Name

[[WhereWhere Conditions] /* Optional where */ Conditions] /* Optional where */Examples for Examples for

COURSE (COURSE (CRSCRS, CTITLE, CHRS, CHRSW) , CTITLE, CHRS, CHRSW)

SELECT CRS, CTITLESELECT CRS, CTITLEFROM COURSE;FROM COURSE;

1515

Example ContinuedExample Continued

SELECT CTITLE, CHRSWSELECT CTITLE, CHRSWFROM COURSE;FROM COURSE;

SELECT * FROM COURSE;SELECT * FROM COURSE;

SELECT SELECT CTITLE, CHRSWCTITLE, CHRSW FROMFROM COURSECOURSEWHEREWHERE CRS = 706;CRS = 706;

1616

Example ContinuedExample Continued

SELECT SELECT **

FROMFROM COURSECOURSE

WHEREWHERE CHRS >= 60 AND CHRSW = CHRS >= 60 AND CHRSW = 15;15;

SELECT SELECT CTITLE, CHRSWCTITLE, CHRSW

FROMFROM COURSECOURSE

WHERE CHRS >= 60 and CHRS < =100; WHERE CHRS >= 60 and CHRS < =100;

1717

ContinuedContinued

SELECT SELECT CTITLE, CHRSWCTITLE, CHRSW

FROMFROM COURSECOURSE

WHERE CHRS BETWEEN 60 and 100; WHERE CHRS BETWEEN 60 and 100;

SELECT SELECT CTITLE, CHRSWCTITLE, CHRSW

FROMFROM COURSECOURSE

WHERE CHRS >= 60 OR CRS = 706;WHERE CHRS >= 60 OR CRS = 706;

1818