Course Outline Volume 1
--->RELATIONAL DATABASES & SQL • ABOUT RELATIONAL DATABASES • ELEMENTS OF SQL --->CHOOSING A SQL & PL/SQL INTERFACE • ABOUT DATABASE CONNECTIONS • ABOUT BIND VARIABLES • USING SQL DEVELOPER • USING SQL*PLUS • USING APPLICATION EXPRESS --->BUILDING A SELECT STATEMENT • ABOUT THE SELECT STATEMENT • USING ALIAS NAMES --->RESTRICTING DATA WITH THE WHERE CLAUSE • ABOUT LOGICAL OPERATORS • EQUALITY OPERATOR • BOOLEAN OPERATORS • NULL & BETWEEN OPERATORS • IS [NOT] NULL Operator • [NOT] BETWEEN Operator • FINDING TEXT STRINGS • [NOT] LIKE Operator • REGEXP_LIKE() • IN OPERATOR --->SORTING DATA WITH THE ORDER BY CLAUSE • ABOUT THE ORDER BY CLAUSE • MULTIPLE COLUMN SORTS • SPECIFYING THE SORT SEQUENCE • ABOUT NULL VALUES WITHIN SORTS • USING COLUMN ALIASES --->PSEUDO COLUMNS & FUNCTIONS • USING ROWID • USING ROWNUM • USING THE FUNCTIONS • SYSDATE • USER & UID • USING THE DUAL TABLE • SESSIONTIMEZONE FUNCTION --->JOINING TABLES • ABOUT JOINS • INNER JOIN • REFLEXIVE JOIN • NON-KEY JOIN • OUTER JOIN --->USING THE SET OPERATORS • ABOUT THE SET OPERATORS • SET OPERATOR EXAMPLES --->SUMMARY FUNCTIONS • ABOUT SUMMARY FUNCTIONS • SUMMARY FUNCTIONS WITH DISTINCT --->USING SUB-QUERIES • ABOUT SUB-QUERIES • STANDARD SUB-QUERIES • CORRELATED SUB-QUERIES --->AGGREGATING DATA WITHIN GROUPS • ABOUT SUMMARY GROUPS • FINDING GROUPS WITHIN THE BASE TABLES • SELECTING DATA FROM THE BASE TABLES • SELECTING GROUPS FROM THE RESULT TABLE --->BUILD SIMPLE SQL*PLUS REPORTS • FORMAT COLUMN OUTPUT WITH COLUMN • DEFINING REPORT BREAKS WITH BREAK • PRODUCE SUBTOTALS WITH COMPUTE --->USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES • CREATE TABLE STATEMENT • NOT NULL • DEFAULT • ALTER TABLE STATEMENT • DROP TABLE STATEMENT • SUPPORTIVE STATEMENTS • DESCRIBE • RENAME --->USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA • ABOUT THE INSERT STATEMENT • ABOUT THE DELETE STATEMENT • ABOUT THE UPDATE STATEMENT • ABOUT TRANSACTIONS • ROLLBACK • COMMIT • SAVEPOINT • SET TRANSACTION • TRUNCATE TABLE • COMPLEX TABLE REFERENCES --->APPENDIX A • REFERENCE INFORMATION • SQL DDL STATEMENT REFERENCE • SQL DML STATEMENT REFERENCE • SQL TRANSACTION CONTROL STATEMENT REFERENCE • SQL OPERATORS REFERENCE
Course Outline Volume 2
--->SQL99 JOIN TECHNIQUES • CROSS JOINS • NATURAL JOINS • INNER JOINS • OUTER JOINS • ANTI JOINS • USING NAMED SUBQUERIES --->ENHANCING GROUPS WITH ROLLUP & CUBE • USING ROLLUP • USING CUBE --->USING THE CASE EXPRESSION SQL FUNCTIONS: CHARACTER • STRING FORMATTING FUNCTIONS • ASCII CODES FUNCTIONS • PAD & TRIM FUNCTIONS • STRING MANIPULATION FUNCTIONS • STRING COMPARISON FUNCTIONS • PHONETIC SEARCH FUNCTIONS --->SQL FUNCTIONS: NUMERIC • ABOUT THE NUMERIC FUNCTIONS • NULL VALUE FUNCTIONS --->SQL FUNCTIONS: DATE • DATE FORMAT FUNCTIONS • DATE ARITHMETIC FUNCTIONS --->DATABASE OBJECTS: RELATIONAL VIEWS • ABOUT DATABASE OBJECTS • ABOUT RELATIONAL VIEWS • UPDATING VIEW DATA • MAINTAINING VIEW DEFINITIONS • ALTER VIEW • DROP VIEW --->DATABASE OBJECTS: DATA DICTIONARY STORAGE • ABOUT THE DATA DICTIONARY • OBJECT-SPECIFIC DICTIONARY VIEWS • USER_UPDATABLE_COLUMNS • UNDERSTANDING THE DATA DICTIONARY STRUCTURE --->DATABASE OBJECTS: INDEXES • ABOUT INDEXES • USE B-TREE INDEXES --->DATABASE OBJECTS: OTHER OBJECTS • MORE ABOUT CREATING TABLES • ABOUT SEQUENCES • ALTER SEQUENCE & DROP SEQUENCE • ALTER SEQUENCE • DROP SEQUENCE • ABOUT SYNONYMS • DROP SYNONYM • CREATE SCHEMA AUTHORIZATION --->DATABASE OBJECTS: SECURITY • USER PASSWORDS • OBJECT SECURITY --->DATA INTEGRITY USING CONSTRAINTS • ABOUT CONSTRAINTS • NOT NULL CONSTRAINT • CHECK CONSTRAINT • UNIQUE CONSTRAINT • PRIMARY KEY CONSTRAINT • REFERENCES CONSTRAINT • DEFINING CONSTRAINTS ON EXISTING TABLES --->MANAGING CONSTRAINT DEFINITIONS • RENAMING & DROPPING CONSTRAINTS • ENABLING & DISABLING CONSTRAINTS • DEFERRED ENFORCEMENT • SET CONSTRAINTS • HANDLING CONSTRAINT EXCEPTIONS • CONSTRAINTS WITH VIEWS • DATA DICTIONARY STORAGE --->ADVANCED SQL*PLUS REPORTS • BREAK • COMPUTE • COLUMN • TTITLE & BTITLE --->OPTIONAL - ADVANCED SQL*PLUS REPORTS • REPORT-1 • REPORT-2 • SOLUTIONS • REPORT-1 • REPORT-2 --->OPTIONAL - BUILDING SQL*PLUS SCRIPTS • USING SQL*PLUS VARIABLES • DEFINE & UNDEFINE • ACCEPT • PROMPT • PAUSE --->OPTIONAL - SQL*PLUS SESSION CONTROL • CONTROLLING SYSTEM VARIABLES • SET • SHOW
