This page has 0 threads | Add post

Courseware

Introduction to Oracle course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Oracle PL/SQL courses).

Click here to download a sample of the first two chapters of this Introduction to Oracle manual, or review how to license our courseware.

Chapter 1 - DESIGNING DATABASES▼

Section	Title	Subsections	Page
1.1	The Four Stages of Database Design	Stage 1 – Deciding what to Include Stage 2 – Dividing Data into Tables Stage 3 – Choosing a Primary Key for each Table Stage 4 – Creating Relationships and a Database Diagram	7
1.2	Many-to-Many Relationships		11

Chapter 2 - SETTING UP ORACLE▼

Section	Title	Subsections	Page
2.1	Installing Oracle Server		12
2.2	Installing Oracle SQL Developer		13
2.3	Creating a First Connection	What you Start With Creating a Connection to the System User	14
2.4	Creating a Connection	Creating the User (and Schema) Creating a Connection for the User	16
2.5	Installing and Using the Database		18

Chapter 3 - CREATING TABLES▼

Section	Title	Subsections	Page
3.1	Creating a User and Schema		19
3.2	Creating a Table	Saving and Editing Changes Setting an Identity Column Setting Default Values	20
3.3	Data Types	Text Data The Number Data Type Other Number Types Boolean Field Type Date/Time Data Types	22
3.4	Entering Data in Tables		25

Chapter 4 - RELATIONSHIPS BETWEEN TABLES▼

Section	Title	Subsections	Page
4.1	Understanding Relationships		26
4.2	Creating a Relationship		27
4.3	Creating Data Models	Using Model Browser to Create a New Design Adding Tables	28
4.4	Formatting the Data Model	Simplifying what you See Improving the Lines Selecting Tables Changing Colours Using the Navigator	30
4.5	Browser Operations	Saving Models Redisplaying Models	33
4.6	Creating Subviews		35
4.7	Synchronising Models		36

Chapter 5 - CREATING QUERIES▼

Section	Title	Subsections	Page
5.1	Working with Connections	Viewing Schemas Viewing Tables and Columns	37
5.2	Setting Preferences	Change 1: Setting the Default Font Size Change 2: Changing the New Query Short-Cut Change 3: Widening the Scope of Completion Insight	38
5.3	SQL Worksheets and Schemas	Creating SQL Worksheets Creating Another Worksheet Linked to the Same Schema Viewing and Changing a Worksheet’s Schema Managing Query Windows	40
5.4	Basic SELECT Statements	Where to Put your Commas Using Completion Insight	42
5.5	Running Queries	Running a Single Command Running All of a Worksheet’s Commands Running All a Worksheet’s Commands in Sequence Closing your Outputs	44
5.6	Dealing with Errors		46
5.7	Saving and Retrieving Queries	Saving Queries Opening Queries	47
5.8	Formatting Worksheet Queries	Using Upper and Lower Case Changing the Default Case of SQL Keywords Changing Indentation Auto-formatting SQL Colours in SQL	48
5.9	Comments	Commenting Out Blocks of Code	51

Chapter 6 - THE SELECT STATEMENT▼

Section	Title	Subsections	Page
6.1	SELECT Statement Syntax	Mnemonic for Order of Commands	52
6.2	Table Aliases	Reason 1 for Aliases – Easier to Refer to Field Names Reason 2 for Aliases – Joins Changing a Table Alias	53
6.3	Column Aliases	Basic Column Aliases The Word AS is Optional Aliases in WHERE and ORDER BY Clauses	55
6.4	Ordering Rows	Simple Sorting Sorting by Multiple Columns Sorting Nulls	57
6.5	Miscellaneous SELECT Tricks	Selecting All Columns Using the Asterisk Selecting Unique Rows Showing Top and Bottom Rows Including Ties	59
6.6	Using UNION to Combine Results		61
6.7	Combining Data from Different Schemas		62

Chapter 7 - CRITERIA USING WHERE▼

Section	Title	Subsections	Page
7.1	The WHERE Clause	Relational Operators	63
7.2	Criteria with Numbers	Using Comparisons Finding Numbers in a Given Range	64
7.3	Criteria using Text	Exact Matches Wildcard Matches using LIKE Making Searches Case Insensitive Using Special Characters with LIKE Ranges and Regular Expressions	65
7.4	Criteria for Dates	Using Dates in Criteria	68
7.5	Combining Criteria	Example using AND Example using OR Criteria Explained using Venn Diagrams Using Parentheses	69
7.6	Filtering for Nulls	An Example of Testing for Nulls	71

Chapter 8 - IMPORTING AND EXPORTING DATA▼

Section	Title	Subsections	Page
8.1	Copying and Pasting		72
8.2	Exporting Query Results to Excel		73
8.3	Exporting Table Data	Step 1 – Starting the Export Step 2 – Choosing Columns Step 3 – Viewing the Resulting File	74
8.4	Loading Data in Power Query		76

Chapter 9 - CALCULATIONS▼

Section	Title	Subsections	Page
9.1	Creating Calculated Columns	Giving Calculated Columns Aliases Using Column Aliases in ORDER BY Clauses Column Aliases Don’t Work in WHERE Criteria	77
9.2	Using SQL Functions	Typing an SQL Function Getting Help on Oracle SQL Functions	79
9.3	Casting Data Types	What is Casting? Casting Using the CAST Function Converting Text to Dates Converting Text to Numbers	80
9.4	Numerical Calculations	Mathematical Symbols and BODMAS The Modulus Function (MOD) Powers and Roots Rounding Other Useful Mathematical Functions	82
9.5	Text Calculations	Concatenating Text Changing the Case of Text Getting Numerical Information Manipulating Text	84
9.6	Examples of Text Functions	Example: Counting Words Example: Getting the Last Word	87
9.7	Dealing with Nulls	The COALESCE Function The NVL Function	88

Chapter 10 - THE CASE EXPRESSION▼

Section	Title	Subsections	Page
10.1	The Searched Case Expression	Example: Film Bands Example: Film Era Using CASE Expressions in WHERE Criteria	90
10.2	The Simple Case Statement		93
10.3	Nested CASE Statements		94

Chapter 11 - DATE CALCULATIONS▼

Section	Title	Subsections	Page
11.1	How Dates and Times Work	Using Dump to See Dates The Seven Parts of a Date/Time	95
11.2	Basic Dates and Times	Date and Time Types Getting the Current Date/Time	96
11.3	Representing Dates (and Times)	The TO_CHAR Function The Main Components of a Date The FM Modifier Ordinal Suffices Putting Everything Together Time Format Codes	97
11.4	Extracting Parts of Dates	The Extract Function Keywords for Extracting Dates Keywords for Extracting Times Truncating Dates/Times Rounding Dates	101
11.5	Getting the Difference Between Dates	Simple Date Subtraction Date/Time Subtraction Returning Intervals Getting True Ages using MONTHS_BETWEEN	104
11.6	Adding Dates	Adding and Subtracting Days Adding and Subtracting Other Time Intervals	106

Chapter 12 - JOINS▼

Section	Title	Subsections	Page
12.1	Overview of Joins	What is a Join? The Types of Join	107
12.2	Understanding your Database	How Relationships Work (Reminder)	108
12.3	Inner Joins	The Syntax of an Inner Join Our Example – Joining the Film and Director Tables Joining more than One Table Variations on Inner Join Syntax Composite Joins Joining by Expressions	109
12.4	Outer Joins	Creating Outer Joins in SQL Right Outer Joins Picking Out Unmatched Rows Full Outer Joins	114
12.5	Cross Joins	A Practical Example of Cross Joins	116
12.6	Self-Joins		117

Chapter 13 - SUMMARISING DATA▼

Section	Title	Subsections	Page
13.1	Simple Summarising	Syntax of a Simple Summary	118
13.2	Counting	Counting All of a Table’s Rows Counting Non-Null Columns Counting CLOB fields Counting Unique Values Using Filter to Count a Subset of Rows	119
13.3	Grouping	Why you Need GROUP BY The GROUP BY Clause Grouping by Multiple Columns Grouping by Expressions Grouping without Aggregating	121
13.4	Filtering Results using HAVING		124
13.5	Dealing with Nulls	The Default Treatment of Nulls Forcing SQL to Include Nulls	125
13.6	The CUBE and ROLLUP Operators	Using CUBE to Show All Combinations Using ROLLUP to Show Most Combinations	126
13.7	String Aggregation		128

Chapter 14 - VIEWS▼

Section	Title	Subsections	Page
14.1	What Views Are		129
14.2	Why Views are Useful	Use 1: Pre-Joining Tables Use 2: Virtually Renaming Columns	130
14.3	Creating Views		131
14.4	Changing Views	Method 1 - Scripting Changes Method 2 – Using the Edit View Window Method 3 – Typing your Instructions	133
14.5	Sorting in Views		135
14.6	Joining to Views		136

Chapter 15 - CTES AND DERIVED TABLES▼

Section	Title	Subsections	Page
15.1	Multi-Stage Queries		137
15.2	Derived Tables	Single Derived Tables Multiple Derived Tables	138
15.3	Single CTEs (Common Table Expressions)	Syntax of Single CTEs The CTE for our Example	140
15.4	Multiple CTEs	Syntax of Multiple CTEs Example of a Multiple CTE	141

Chapter 16 - SUBQUERIES▼

Section	Title	Subsections	Page
16.1	Creating Subqueries	Example: Showing the Name of the Longest Film ANY, ALL, IN and NOT IN Using EXISTS to Check whether Rows are Returned	143
16.2	Correlated Subqueries	Definition and Example Alternatives to Correlated Subqueries	146

Chapter 17 - ORACLE SQL AND AI▼

Section	Title	Subsections	Page
17.1	Choosing an AI Tool		147
17.2	Blind Queries		148
17.3	Querying a Specific Database	Creating a Backup of a Schema’s Tables Uploading your Schema File Requesting a Query	149
17.4	Tidying Up Queries		151
17.5	Optimising Queries		152
17.6	Debugging SQL		153

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Oracle course manuals