Oracle Database 12c: Basic SQL

Course details

Mastering the SQL language is an essential skill for any Oracle professional and is the first step in becoming a true Oracle expert. As with any database platform, Oracle has its own take on the SQL language that builds on top of industry standards. In this course, Oracle instructor and consultant David Yahalom covers the basics required to code with SQL in an Oracle Database 12c environment. Learn how to select and filter data, and manipulate data using functions, including SUBSTR, UPPER, MIN, MAX, AVG, SUM, and more. Then find out how to combine data from two or more tables in a single statement with joins. Discover how to modify data with DML and DDL commands like DELETE and UPDATE, and group data with aggregate functions. David also provides an overview of advanced concepts, such as constraints and subqueries.

Note: This training course is appropriate for beginning database developers with no prior experience with SQL or Oracle.

Learning objectives
Using Oracle SQL*Developer, the free development environment
Selecting data from tables
Filtering data
Using functions to manipulate data
Joining data from tables
Creating tables
Updating values
Truncating data
Grouping and ordering result sets
Working with primary keys
Checking constraints

Concepts

0. Introduction

01 - Welcome
02 - What you need to know

1. Introduction to SQL

03 - What is SQL
04 - Database tables
05 - Overview of table data types
06 - SQL query structure

2. Preparing the Course Environment

07 - Downloading the course VM

3. Using Oracle SQL Developer

08 - Introduction to Oracle SQL Developer
09 - Creating a connection to the database
10 - Managing existing connections
11 - Exploring database objects
12 - Filtering specific objects
13 - Using SQL Developer tabs
14 - Viewing query output
15 - Case sensitivity of SQL commands
16 - Viewing SQL execution history
17 - Query output as grid or script

4. Basic SELECT Queries

18 - Writing your first SELECT query
19 - Selecting only specific columns
20 - Using WHERE to filter specific rows
21 - Using multiple WHERE conditions
22 - Using WHERE to filter partial strings
23 - Using WHERE to filter numeric ranges
24 - Using WHERE IN and NOT IN for strings
25 - Using WHERE IN and NOT IN for numbers
26 - Using WHERE to filter dates
27 - Using WHERE to filter exact strings
28 - Using DISTINCT to eliminate duplicates
29 - Performing basic numeric calculations
30 - Using column aliases
31 - Describe the structure of a table
32 - Using ORDER BY

5. String, Number, and Date Functions

33 - Introduction to functions
34 - Using SUBSTRING, LENGTH, and CONCAT
35 - Using LENGTH
36 - Using CONCAT
37 - Using LOWER and UPPER
38 - Using INSTR
39 - Using RPAD and LPAD
40 - Using TO CHAR to convert numbers
41 - Using TO CHAR to format DATE values
42 - Using TO CHAR to filter DATE values
43 - The special Oracle DUAL table
44 - Using ROUND
45 - Using TRUNC
46 - Using SYSDATE
47 - Using SYSTIMESTAMP
48 - Using MONTHS BETWEEN
49 - Using ADD MONTHS

6. Joining Table Data

50 - Introduction to SQL joins
51 - Joining data from tables
52 - INNER JOIN
53 - LEFT JOIN
54 - RIGHT JOIN
55 - FULL OUTER JOIN
56 - Joins with table aliases
57 - Oracle join syntax vs. ANSI syntax

7. Manipulating Data

58 - Introduction to DML and DDL commands
59 - Creating a simple table
60 - Inserting a new row to a table
61 - COMMIT and ROLLBACK
62 - Introduction to transactions
63 - Deleting rows for a table
64 - Updating values
65 - Multiple-statement transactions
66 - Truncating all data from a table
67 - Adding and dropping columns from a table

8. Grouping Data

68 - Introduction to aggregate functions
69 - Using aggregate functions
70 - Using GROUP BY
71 - Using GROUP BY with MAX, MIN, and AVG
72 - Using HAVING

9. Advanced Topics

73 - Primary keys
74 - NOT NULL constraints
75 - Check constraints
76 - Foreign key constraints
77 - Subqueries

Conclusion

78 - Next steps