-
Module 1: Introduction to querying Oracle
14 Lessons-
StartModule introduction
-
StartWhat is Oracle Database ?
-
StartOracle database editions
-
StartWhat is SQL ?
-
StartWhat is PL/SQL ?
-
StartTypes of SQL statements
-
StartWhat are databases ?
-
StartWhat are pluggable databases ?
-
StartWhat is sqlplus ?
-
StartWhat is SQL Developer ?
-
StartConnecting to Oracle
-
StartFinding help and documentation
-
StartLab 1: Introduction to querying Oracle
-
StartQuiz 1: Introduction to querying Oracle
-
-
Module 2: Querying a single table
16 Lessons-
StartModule introduction
-
StartWhat are tables?
-
StartFinding out what's in a table
-
StartWhat are schemas?
-
StartWhat is Popkorn Kraze ?
-
StartSELECT statements
-
StartUsing aliases for columns and tables
-
StartQuoted identifiers
-
StartUsing 2-part names
-
StartORDER BY clause
-
StartLImiting rows with OFFSET and FETCH
-
StartRemoving duplicates with DISTINCT
-
StartQuerying literals, expressions and functions
-
StartLab 2: Querying a single table
-
StartLab 2: Answers
-
StartQuiz 2: Querying a single table
-
-
Module 3: Filtering the rows to return
14 Lessons-
StartModule introduction
-
StartFiltering output with the WHERE clause
-
StartFinding patterns with LIKE
-
StartLogical operations with AND, OR, and NOT
-
StartWorking with logical values
-
StartRanges of values with the BETWEEN operator
-
StartChecking lists of values by using the IN operator
-
StartLimiting output rows with FETCH
-
StartUsing FETCH WITH TIES
-
StartWorking with NULL values
-
StartWhat are user-defined functions?
-
StartLab 3: Filtering the rows to return
-
StartLab 3: Answers
-
StartQuiz 3: Filtering the rows to return
-
-
Module 4: Working with queries
9 Lessons-
StartModule introduction
-
StartUsing statement terminators
-
StartWorking with scripts in SQL Developer
-
StartUsing query history and worksheets
-
StartCopying and exporting query results
-
StartCommitting or rolling back changes
-
StartCommenting code
-
StartFormatting your scripts for readability
-
StartQuiz 4: Working with queries
-
-
Module 5: Querying numbers
11 Lessons-
StartModule introduction
-
StartWhat are data types?
-
StartExact numbers
-
StartIdentity and generated numeric values
-
StartApproximate numbers
-
StartNumeric operators
-
StartInteger and decimal division
-
StartCommon numeric functions
-
StartLab 5: Querying numbers
-
StartLab 5: Answers
-
StartQuiz 5: Querying numbers
-
-
Module 6: Querying strings
15 Lessons-
StartModule introduction
-
StartString data types
-
StartLiteral string values
-
StartString operators
-
StartTrimming strings with LTRIM, RTRIM, and TRIM
-
StartChanging case with UPPER, LOWER, and INITCAP
-
StartExtracting parts of strings with SUBSTR
-
StartDetermining the length of strings
-
StartReplacing substrings
-
StartPadding and repeating characters
-
StartFinding substrings with INSTR
-
StartReplacing characters with TRANSLATE
-
StartLab 6: Querying strings
-
StartLab 6: Answers
-
StartQuiz 6: Querying strings
-
-
Module 7: Querying dates and times
17 Lessons-
StartModule introduction
-
StartDate and time data types
-
StartLiteral date and time values
-
StartCurrent date and time
-
StartExtracting components from date and time values
-
StartAdding and subtracting date and time values
-
StartWorking with intervals
-
StartCalculating date and time differences
-
StartCalculating ages
-
StartGet string names for date and time parts
-
StartCalculating beginning and end of month
-
StartConstructing dates and times from components
-
StartChanging time zone offsets
-
StartFormatting date and time values
-
StartLab 7: Querying dates and times
-
StartLab 7: Answers
-
StartQuiz 7: Querying dates and times
-
-
Module 8: Converting between data types
11 Lessons-
StartModule introduction
-
StartReplacing NULL values with COALESCE
-
StartReturning NULL When Equal with NULLIF
-
StartComparing nullable values
-
StartImplicit vs explicit conversions
-
StartConverting data types with CAST
-
StartConverting strings to dates and numbers
-
StartOther built-in data types
-
StartLab 8: Converting between data types
-
StartLab 8: Answers
-
StartQuiz 8: Converting between data types
-
-
Module 9: Aggregating data
12 Lessons-
StartModule introduction
-
StartCounting rows and columns with COUNT
-
StartSummarizing data with SUM, AVG, MIN, MAX
-
StartSummarizing data in sections with GROUP BY
-
StartFiltering returned groups with HAVING
-
StartUnderstanding logical query execution order
-
StartNumbering rows with window functions
-
StartPartitioning ranking and numbering with PARTITION BY
-
StartOther categories of aggregates
-
StartLab 9: Aggregating data
-
StartLab 9: Answers
-
StartQuiz 9: Aggregating data
-
-
Module 10: Implementing logic in scripts
15 Lessons-
StartModule introduction
-
StartUsing conditional expressions with CASE
-
StartSimplifying conditionals with GREATEST, LEAST
-
StartSQL vs PL/SQL
-
StartOutputting messages
-
StartPL/SQL blocks
-
StartDefining Variables and Assigning Values
-
StartAdding conditional logic with IF, ELSE
-
StartCreating loops with WHILE
-
StartCreating loops with FOR
-
StartAdvanced loop options
-
StartCreating loops with LOOP
-
StartLab 10: Implementing logic in scripts
-
StartLab 10: Answers
-
StartQuiz 10: Implementing logic in scripts
-
-
Module 11: Querying multiple tables
14 Lessons-
StartModule introduction
-
StartWhat is a primary key?
-
StartWhat is a foreign key?
-
StartUsing CROSS JOIN
-
StartUsing INNER JOIN
-
StartUsing modern join syntax
-
StartUsing LEFT OUTER JOIN
-
StartOther OUTER join types
-
StartJoining more than two tables
-
StartJoining a table to itself (self join)
-
StartJoins without equality (non-equi joins)
-
StartLab 11: Querying multiple tables
-
StartLab 11: Answers
-
StartQuiz 11: Querying multiple tables
-
-
Module 12: Applying set operations to tables
10 Lessons-
StartModule introduction
-
StartRules for set operations
-
StartUsing UNION and UNION ALL
-
StartExcluding data with MINUS
-
StartFinding common data with INTERSECT
-
StartSelecting from set-returning functions
-
StartImplementing lateral joins with APPLY
-
StartLab 12: Applying set operations to tables
-
StartLab 12: Answers
-
StartQuiz 12: Applying set operations to tables
-
-
Module 13: Using subqueries
12 Lessons-
StartModule introduction
-
StartUsing subqueries
-
StartUsing scalar subqueries
-
StartUsing lists from subqueries
-
StartChecking for existence with EXISTS subqueries
-
StartUsing set-returning subqueries
-
StartUsing VALUES row constructors
-
StartSimplifying queries with common table expressions (CTEs)
-
StartUsing multiple CTEs in a single query
-
StartLab 13: Using subqueries
-
StartLab 13: Answers
-
StartQuiz 13: Using subqueries
-
-
Module 14: Working with stored procedures and dictionary views
11 Lessons-
StartModule introduction
-
StartWhat is a stored procedure?
-
StartStored procedures vs functions
-
StartExecuting stored procedures
-
StartUsing anonymous PL/SQL blocks
-
StartExecuting dynamic SQL statements
-
StartQuerying the data dictionary
-
StartQuerying system functions
-
StartLab 14: Working with stored procedures and dictionary views
-
StartLab 14: Answers
-
StartQuiz 14: Working with stored procedures and dictionary views
-
-
Module 15: Modifying data
17 Lessons-
StartModule introduction
-
StartInserting data into a table
-
StartInserting multiple rows at once with VALUES
-
StartWhat are default constraints?
-
StartWhat are identity columns?
-
StartIdentifying the last value inserted
-
StartWhat is a check constraint?
-
StartWhat is a unique constraint?
-
StartDeleting rows from a table
-
StartInserting rows with data from another table
-
StartSelecting rows into a new table
-
StartTruncating a table vs deleting all rows
-
StartUpdating data in a table
-
StartMerging data into a table
-
StartLab 15: Modifying data
-
StartLab 15: Answers
-
StartQuiz 15: Modifying data
-
Writing SQL Queries for Oracle®
Creating reports, analytics, or applications? And need to get data out of Oracle? Learn to write SQL queries like a pro !
Course Summary
Do you need to learn how to write SQL queries for Oracle?
- You know that the information that you need is stored in an Oracle database
- You need to find some data to extract it
- You need to create reports with reporting tools
- You are using analytic tools like Power BI, Tableau, QlikView, Excel, or Access and need to get data from Oracle
- You want to learn to write queries properly, using commercial coding standards
- You are new to writing queries, or you are self-taught and want to make sure you are doing it correctly
We encourage you to complete the practical exercises. We have tried to make getting started with this as easy as possible.
