Introduction to SQL A 05 Carla Pereira carla

Introduction to SQL A 05 Carla Pereira carla. pereira@ca. com (c) 2002 Computer Associates International, Inc. (CA) All trademarks, trade names, service marks and logos herein belong to their respective referenced companies. CA confidential and proprietary information for CA internal use only. No unauthorized copying or distribution permitted. ca. com

Introduction to SQL § Abstract – This session includes an introduction to the relational database model, concepts of relational databases, relational operations, SQL terminology and how these are all reflected in the Advantage™ CA-IDMS® Database SQL Option ca. com

Course Outline § Different database models § Terminology and concepts of relational database § Relational operations § Terminology and concepts of SQL § Advantage CA-IDMS SQL Language ca. com

What is a Database? A collection of related data, organized and stored in a systematic manner ca. com

Database Models § § Hierarchical Network Relational How data is accessed — Data Manipulation Language (DML) ca. com

Hierarchical Data Model Based on Parent/Child Relationship DEPARTMENT EMPLOYEE BENEFITS ca. com

Network Data Model Relationship Implemented Through the Use of Embedded Pointers JOB DEPARTMENT POSITION EMPLOYEE BENEFITS ca. com

Relational Data Model Relationships Implemented via Foreign Keys Department Employee DEPT_ID DEPT_NAME EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 1001 SALES 12345 CLEMENS SAMUEL J 200 1010 HUMAN RESOURCES 21343 MC GEE LOUISE J 001 1050 ACCOUNTING 31254 GIBSON JOHN J 010 1050 1090 PAYROLL 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 Job JOB_ID JOB_NAME J 001 SECRETARY J 010 ACCOUNTANT J 200 J 410 Benefits EMP_ID BEN_TYPE PLAN_NAME START_DATE 12345 HEALTH STAY HEALTHY 1993 -01 -01 DIRECTOR 31254 HEALTH STAY HEALTHY 1996 -05 -01 VICE PRESIDENT 31254 LIFE INS GOOD LIFE 1995 -01 -31 53421 HEALTH GOOD HEALTH 1990 -02 -14 ca. com

Relational Database Definition § A collection of tables that follow certain rules § Contains an operator to create new tables from existing tables § Underlying data access (for Advantage CA-IDMS) is through familiar physical structures: indexes, CALC keys, clustered records and more ca. com

Benefits of a Relational Database § § § Quick and easy access Easy to understand Industry-standard Isolates program from physical storage Easy to use and maintain ca. com

Course Outline § Different database models § Terminology and concepts of relational database § Relational operations § Terminology and concepts of SQL § Advantage CA-IDMS SQL Language ca. com

Relational Concepts § § § Relational data model Integrity constraints Null data value Isolated physical and logical definitions Optimization Great power…at a cost ca. com

Relational Data Model § § Table Column Row Cell ca. com

Table § A group of related data items that describe an entity § A table is comprised of rows and columns Employee EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 EMPLOYEE TABLE ca. com

Column § The vertical component of a table § Columns have a NAME and a DATA TYPE COLUMN EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 ca. com

Row § An occurrence of the data § The number of rows will get larger and smaller § There is a set of values for each COLUMN in a table ROW EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 ca. com

Cell § The intersection of a ROW and a COLUMN containing a data value EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 CELL ca. com

Integrity Constraints § Unique § Referential § Domain ca. com

Primary Key § A column or combination of columns for which a unique constraint has been defined and which is not null § Uniquely identifies each row and prevents duplicate rows from being stored § A table will have one (and only one) primary key ca. com

Unique Constraint § Each row of a table must be unique with respect to the value of a column or a combination of columns § Duplicate values may not be stored for a column defined as unique § Multiple unique constraints can be defined on a table ca. com

Unique Constraint EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 PRIMARY KEY UNIQUE KEY ca. com

Referential Constraint § A Referential Constraint is a relationship between two tables § Identifies a FOREIGN KEY in one table, which references a PRIMARY KEY in another table § The FOREIGN KEY can be a column or combination of columns whose values must exist as a PRIMARY KEY in the referenced table ca. com

Referential Constraint Department Table Employee Table DEPT_ID DEPT_NAME EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 1001 SALES 12345 CLEMENS SAMUEL J 200 1010 HUMAN RESOURCES 21343 MC GEE LOUISE J 001 1050 ACCOUNTING 31254 GIBSON JOHN J 010 1050 1090 PAYROLL 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 PRIMARY KEY FOREIGN KEY ca. com

Domain Constraint § A Domain Constraint restricts column values § Three types of Domain Constraints – Data – Check – Null ca. com

Data Type — Domain Constraint § Limits the set of values that can occur in a column § Determines the operations that can be performed on values in the column § Determines its internal representation and storage requirements ca. com

Check Constraint — Domain Constraint § Restricts column values to a range of values that satisfies a search condition § Check constraint is defined at the TABLE level ca. com

Null Constraint — Domain Constraint § Requires that each column of a row contains an actual value and not the absence of a value § Null Constraint is defined when a Column is added to a Table ca. com

Domain Constraint Benefits Table EMP_ID BEN_TYPE PLAN_NAME START_DATE INS_COST PLAN_STAT 12345 HEALTH STAY HEALTHY 1993 -01 -01 34. 50 A 31254 HEALTH STAY HEALTHY 1996 -05 -01 34. 50 A 31254 LIFE INS GOOD LIFE 1995 -01 -31 10. 75 A 53421 HEALTH GOOD HEALTH 1990 -02 -14 <NULL> I NOT NULL CHECK CONSTRAINT NULL FIELD ca. com

Data Access § Set-at-a-time – Operation limited only by specified criteria – Don’t know number of rows to be returned – Return summary or detail data § Optimization – – No control over physical access Separation of logical and physical Statistics imperative Production-sized test environment ca. com

Course Outline § Different database models § Terminology and concepts of relational database § Relational operations § Terminology and concepts of SQL § Advantage CA-IDMS SQL Language ca. com

Relational Operations § Select § Project § Join ca. com

Select § Specific rows § Based on optionally specified criteria § Default is to return all rows ca. com

Project § § Specifies which columns to include Listed by name Project all (*) Remove duplicates (distinct) ca. com

Join § Accessing multiple tables § Inner Join – All rows in both tables that match join criteria – Join column(s) must have equivalent data type § Outer Join – All rows in both tables that match join criteria as well as rows from one table that have no match – Join column(s) must have equivalent data type § Cartesian Product ca. com

More Joins § Union Join – Adds result set of one select to the result set of another – Removes duplicates, by default § Nested Join – SUBQUERY, a SELECT statement nested within another SELECT – Returns a set of values to be used in the outer SELECT – WHERE = , IN, EXISTS ca. com

Course Outline § Different database models § Terminology and concepts of relational database § Relational operations § Terminology and concepts of SQL § Advantage CA-IDMS SQL Language ca. com

What is SQL? § Structured Query Language § A standardized, non-procedural language used to retrieve and update information § Easy to learn § Standard on multiple platforms § Eliminates need to know how the data is physically stored § DML controls retrieving and reporting ca. com

SQL Language Components DDL Statements CREATE table, view, synonym, index ALTER table, index COMMENT ON table, column DECLARE CURSOR DROP table, view, synonym, index § DML Statements SELECT OPEN CURSOR INSERT FETCH UPDATE CLOSE CURSOR DELETE § DCL Statements GRANT REVOKE COMMIT ROLLBACK § Embedded SQL § ca. com

SQL Statements § A structured set of English-like elements § Contains a VERB and additional OPTIONS – Verb — tells what action is to be performed – Options — modifies the verb and defines the operation § Executed interactively (dynamic) or embedded (static) in a program § Can span multiple lines ca. com

SQL Statements Basic SQL Statement: select distinct city from employee where emp-id > 1000; VERB NAMED ENTITY OPTION CLAUSE IDENTIFYING TABLE CLAUSE SPECIFYING SELECTION CRITERIA DELIMITER ca. com

Data Definition Language § CREATE § ALTER § DROP § § Schemas Tables Views Indexes ca. com

View Department Employee DEPT_ID DEPT_NAME EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 1001 SALES 12345 CLEMENS SAMUEL J 200 1010 HUMAN RESOURCES 21343 MC GEE LOUISE J 001 1050 ACCOUNTING 31254 GIBSON JOHN J 010 1050 1090 PAYROLL 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 DEPT_ID DEPT_NAME EMP_LNAME 1001 SALES MC GEE LOUISE 1010 HUMAN RESOURCES CLEMENS SAMUEL 1010 HUMAN RESOURCES KIRK HELEN 1050 ACCOUNTING SCOTT WESTON ca. com

Index § Expedites access to a row based on column values Employee Table EMP_ID EMP_LNAME EMP_FNAME JOB DEPT 12345 CLEMENS SAMUEL J 200 1010 21343 MC GEE LOUISE J 001 1001 31254 GIBSON JOHN J 010 1050 43251 SCOTT WESTON J 410 1050 53421 KIRK HELEN J 001 1010 PRIMARY KEY Name INDEX ca. com

DDL Examples § CREATE SCHEMA CUSTSCHM; CREATE TABLE CUSTSCHM. CUSTOMER (C_NO SMALLINT NOT NULL, NAME CHAR(20), CITY CHAR(15), STATE CHAR(2), ZIPCODE CHAR(5), STATUS CHAR(1), BALANCE DECIMAL(10, 2) CHECK (STATUS IN (‘I’ , ‘A’, ‘L’) ) ); § ca. com

DDL Examples § CREATE VIEW EMPSCHM. DEPT_EMP AS SELECT D 1. DEPT_ID, D 1. DEPT_NAME, E 1. EMP_LNAME, E 1. EMP_FNAME FROM EMPSCHM. DEPARTMENT D 1, EMPSCHM. EMPLOYEE E 1 WHERE E 1. DEPT_ID = D 1. DEPT ; § CREATE INDEX ENAME-IX ON EMPSCHM. EMPLOYEE (EMP_LNAME); ca. com

Data Manipulation Language § Select – All or specific columns – All or specific rows – Tables, Views, Table Procedures, Procedures – SQL and Non-SQL § Insert § Update § Delete ca. com

Conditions Composed of OPERATORS and/or KEYWORDS Comparison Operator Meaning = Equal to <> Not Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to ca. com

Conditions KEYWORDS are Used to Compare Values Keyword Meaning IS NULL Checks for a null value BETWEEN Locates values in a range IN Locates values specified in a list LIKE Retrieves rows using a character combination ca. com

Aggregate Functions Function AVG COUNT MAX MIN SUM Meaning Average of all values in a column Counts number of rows Returns highest value in a column Returns lowest value in a column Returns total of all values in a column ca. com

DML Examples § SELECT ITEM, ITEM_DESC, ITEM_COST * 1. 10 AS NEW_COST FROM ITEM_TABLE WHERE ITEM_TYPE LIKE ‘AA 11%%%%’ ; § SELECT C_NO, NAME, CITY, STATE FROM CUSTOMER WHERE BALANCE IS NULL; § SELECT JOB_ID, AVG(SALARY_AMT) FROM POSITION GROUP BY JOB_ID HAVING AVG(SALARY_AMT) > 20000 ORDER BY JOB_ID; ca. com

DML Examples (cont’d) § INSERT INTO CUSTOMER VALUES (12345, ‘BAYSIDE COMPUTERS’, ‘ 1234 SHORE ROAD’ , ‘BAYSIDE’, MA’, 03212’, NULL); § UPDATE CUSTOMER SET STREET = ‘ 42 SHORE ROAD’, PHONE = ‘ 999 -888 -1111’ WHERE C_NO = 1231; § DELETE CUSTOMER WHERE C_NO = 4321; § DELETE CUSTOMER ; ca. com

DML Join Examples § SELECT C. C_NO, C. NAME, O. ORD_NO, O. ORD_DATE, O. ORD_AMT FROM CUSTOMER C, ORDER O WHERE C. C_NO = O. C_NO AND BALANCE > 1000 ; § SELECT C. C_NO, C. NAME, O. ORD_NO, O. ORD_DATE, O. ORD_AMT FROM CUSTOMER C, ORDER O WHERE C. C_NO = O. C_NO AND BALANCE > 1000 PRESERVE C ; § SELECT C. C_NO, C. NAME, O. ORD_NO, O. ORD_DATE, O. ORD_AMT FROM CUSTOMER C, ORDER O ; (no WHERE clause) ca. com

DML Join Examples (cont’d) § SELECT CON_ID, CON_LNAME, CON_FNAME FROM CONSULTANT WHERE CONSULTANT. DEPT_ID > 1000 UNION SELECT EMP_ID, EMP_LNAME, EMP_FNAME FROM EMP WHERE EMP. DEPT_ID > 1000 ; § SELECT EMP_LNAME, EMP_FNAME, EMP_PHONE FROM EMP WHERE EMP_ID IN (SELECT DEPT_HEAD_ID FROM DEPT) ; § SELECT EMP_LNAME, EMP_FNAME FROM EMP WHERE NOT EXISTS (SELECT * FROM EXP WHERE EMP_ID = EXP. EMP_ID); ca. com

Data Control Language § Grant – Users or Groups – Various Levels § Revoke § Commit § Rollback ca. com

DCL Examples § GRANT SELECT, INSERT, UPDATE ON EMPSCHM. EMPLOYEE TO HR_GROUP; § REVOKE INSERT, UPDATE ON CUSTOMER FROM CUST_USER_GROUP; § COMMIT CONTINUE; § ROLLBACK ; ca. com

Embedded SQL § Error handling – SQLState – Whenever § Cursors – Allow record-at-a-time languages (such as COBOL) to process SETS of rows, one row at a time – Declare, Open, Close – Shared: Global/External § Host variables – Select or Fetch INTO – Matching data type as column – Name preceded by colon (: EMP-NAME) ca. com

Course Outline § Different database models § Terminology and concepts of relational database § Relational operations § Terminology and concepts of SQL § Advantage CA-IDMS SQL Language ca. com

Advantage CA-IDMS SQL Language § Extensions to standard – Access to non-SQL databases – Table Procedures and Call Procedures – Many options on each type of statement § Enforce ANSI-compliance for portability § Physical performance/tuning options – – Calc keys Clustered records Indexes Separate areas ca. com

Questions & Answers ca. com