Introduction to Oracle SQL

Instructor

• Philip Burton from filecats.co.uk
• Creator of several best-selling courses on Udemy.

Course Overview

• Introduction to Oracle SQL, the most widely used database engine.
• Important for many jobs.
• Course duration: 1 hour.

Course Content

1. Installation

   • Oracle Express Edition: Free installation on your computer.
   • Oracle SQL Developer: Tool to write SQL code.
   • Alternative to installation is available.

2. SQL Select Statements

   • Learn to write SQL select statements.
   • Use of six principal clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
   • Importance of remembering the order for job interviews.
   • Practice activity included.

3. Oracle Database Versions

   • Validated against 11g Release 2 and 12c Release 1.
   • Latest version at the time is 18c, with 19c under development.
   • Explanation of versioning (e.g., 10g, 11g, 12c) and their significance.

4. Installation Details

   • Recommended version: 18c Express Edition.
   • Limitations of Express Edition: 2 CPUs, 12GB user data, 2GB RAM.
   • Installation steps for 18c and 11g (for 32-bit systems).

5. Setting Up SQL Developer

   • Installation of SQL Developer 19.1.
   • Connection setup between SQL Developer and Oracle Database.
   • Use of SYSDBA role for full database access.

6. Common Issues

   • Troubleshooting connection issues with SQL Developer.
   • Importance of ensuring Oracle TNS Listener is running.

SQL Concepts

1. Select Statement

   • Basic syntax: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
   • Use of SELECT * and specific columns.
   • Renaming columns with AS.
   • Importance of semicolons to end statements.

2. Clauses Overview

   • SELECT: Choose columns.
   • FROM: Specify table.
   • WHERE: Filter rows.
   • GROUP BY: Aggregate rows.
   • HAVING: Filter groups.
   • ORDER BY: Sort result set.

3. Tips and Tricks

   • Drag and drop table/column names in SQL Developer to save time.
   • Using comments in SQL (single and multi-line).
   • Keyboard shortcuts in SQL Developer.

4. Practice Activities

   • Reinforce learning through real exercises.
   • Practice using different clauses and their order.*

Further Learning

• Explore Oracle University and Oracle Database SQL Certified Associate (IZO-071).
• Recommended textbook: OCA Oracle Database SQL Exam Guide.
• Free resources available on Oracle's documentation site.

Conclusion

• Review of installation and key SQL concepts.
• Encouragement to continue learning and exploring SQL.