Oracle Database 12c: Basic SQL

Oracle Database 12c: Basic SQL
Oracle Database 12c: Basic SQL
English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 3h 12m | 404 MB

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.

Topics include:

  • 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
Table of Contents

Introduction
1 Welcome
2 What you need to know

Introduction to SQL
3 What is SQL
4 Database tables
5 Overview of table data types
6 SQL query structure

Preparing the Course Environment
7 Downloading the course VM

Using Oracle SQL Developer
8 Introduction to Oracle SQL Developer
9 Creating a connection to the database
10 Managing existing connections
11 Exploring database objects
12 Filteringspecific 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

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

String, Number, and Date Functions
33 Introduction to functions
34 Using SUBSTRING, LENGTH, and CONCAT
35 Using LENGTH
36 Using CONTACT
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

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

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 Multiplestatement transactions
66 Adding and dropping columns from a table
67 Truncating all data from a table

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

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

Conclusion
78 Next steps