FB

Want to learn and improve your SQL and database skills?

Everything you need to learn, practice, and improve your SQL and database development...

Short focused courses, practice questions, sample data and scripts, beginner-to-advanced roadmap, for multiple database vendors...

All immediately available - even if you think you don’t have much time to learn.

You want to learn SQL and database skills, but...

  • You don’t have time to watch countless hard-to-follow YouTube videos or read lots of blog posts that only show part of a concept.
  • You find it hard working with messy or hard-to-understand queries.
  • It’s hard to find the right solution to get the result you want from your query
  • You don’t know what SQL features exist that could help you write your queries
  • You’ve got a slow query and don’t know how to improve it
  • You know the concepts but want to put your knowledge to the test
  • You don’t know how to get set up with a database and get started

I’ve been there.

I’ve felt every one of these when learning and working with SQL.

Let me tell you a little story about a developer named John.

John picked up a new user story to work on. It involved creating an order history page, and after reading the story it seemed a little complicated.

He talked to another one of his teammates to find out more about the area of the system to work on.

His initial thoughts were that he needed to write a new query to get the data from the database, hook it up to some existing modules in his code, and get it to display on a new screen.

He opened up his IDE, which was SSMS. Browsing through the tables and running some quick queries, he got a basic understanding on what tables were needed and what was used. He checked the application code to check which tables were being used and worked out the best way to get started was to write the SQL query.

The query he would need to write was a bit more than just a simple Select from a table. It needed quite a few joins, with different types. He wrote a simple query to start and checked the results.

John slowly made more changes to the query, bringing in more tables, adding WHERE clauses to filter data, checking the results as he went.

For one particular column, he wasn’t sure how to get the right data to show.

Fortunately, he knew a few ways to do this. He tried a subquery, but then realised that a couple of joins may work better for the column. John knew this could be an option because he had learned about what a subquery was and how it worked, saving him hours of time doing research for this particular issue.

He added the joins and logic to his query and ran it.

The results looked good to him: it looked like it was the correct data.

But the only way to know for sure was to run some tests on it. He checked a few customers and orders individually and it all looked good!

The performance of the query was pretty good too, because he knew what features of SQL should be avoided and what can help with writing a query that performs well.

John was satisfied.

This process of writing this query did have its complex moments, but he was grateful he had a broad understanding of the SQL language and knew how to find the data he needed.

What if you could know how to write queries to get the results you need, that perform well, and you knew the features of SQL you wanted to use?

Introducing...

Database Star Academy

The online training platform for software developers who want to improve their database development and SQL skills.

Here's what you get:

Video Courses

30 high-quality video courses from getting started with SQL to advanced topics

Questions and Assignments

Practice questions and assignments to improve your skills

Works on Many Databases

Content that works on many databases so it’s relevant no matter which database you use

PDF Guides

Many PDF guides to enhance your skills, including checklists and cheat sheets

SQL Scripts

Download the SQL scripts to follow along with each course

Case Studies

Show the steps on how to solve a problem

Timesaver PDFs

Low on time? There are several PDFs that show you how to use different tools easier

Badges, Achievements, Leaderboard

Earn points and badges from various activities, and see how you compare to other members on a Top 10 Leaderboard.

Join Database Star Academy

Instance 1

Monthly

Pay the same amount each month

 

$

19

/month

  • Immediate access
  • Access to all content
  • Certificates of Completion
  • Practice questions, PDFs, and more
Join Now

Annual

Pay one amount, once per year

 

$

190

/year

  • Immediate access
  • Access to all content
  • Certificates of Completion
  • Practice questions, PDFs, and more
JOIN Now

All prices in USD.

Why Join Database Star Academy?

  • Make the most of your learning time with short, focused courses - no fluff or timewasting
  • Learn from someone with experience and passion with databases and SQL
  • Track your progress with lesson and course completion saved to your account
  • Easily cancel online whenever you like - no need for a phone call or to pay additional fees
  • Follow along with courses with access to the SQL scripts used in each course
  • Save time within your SQL IDE so you spend less time fiddling with the tool and more time solving real business problems with your queries
  • Demonstrate your completion of a course with Course Completion Certificates
  • Get better with your skills by taking practice exercises
  • Get started easily with step-by-step instructions to set up the database and create a simple table.
  • Apply the concepts to the database you’re working on with courses that apply to Oracle, SQL Server, MySQL, and PostgreSQL.
  • Refer back to the course material with the accompanying PDF guides for courses

Courses in Database Star Academy

These are the 33 courses available in Database Star Academy, with more being added regularly.

Introduction to SQL

Learn the basics of Oracle SQL including setting up your own database and running SQL queries.

12 videos, 73 minutes

  1. 1
    Introduction
  2. 2
    Download and Install PostgreSQL
  3. 3
    Download and Install MySQL
  4. 4
    Download and Install SQL Server
  5. 5
    Download and Install Oracle Express
  6. 6
    Create your first table
  7. 7
    Add data to a table
  8. 8
    View data in a table
  9. 9
    View some records in a table
  10. 10
    Update data in a table
  11. 11
    Delete data from a table
  12. 12
    Conclusion

Creating a Second Table

Learn how to create a second table, relate it to the first, and write a SELECT query to get data from both tables.

  1. 1
    Introduction (02:01)
  2. 2
    Setup scripts (03:43)
  3. 3
    Capture company name (04:04)
  4. 4
    Update company name (04:39)
  5. 5
    Create a company table (04:55)
  6. 6
    Relating data (02:17)
  7. 7
    Add a new column (05:33)
  8. 8
    Update the identifying values (02:43)
  9. 9
    Add primary key (02:44)
  10. 10
    Using a foreign key (03:12)
  11. 11
    Add a foreign key (05:03)
  12. 12
    Select with join (06:52)
  13. 13
    Select with an outer join (04:43)
  14. 14
    Ordering results (02:55)
  15. 15
    Conclusion (00:58)

Improve Your Queries and Data

Learn how to improve the quality of your queries and data integrity with several SQL features

  1. 1
    Introduction (01:58)
  2. 2
    Setup scripts (05:06)
  3. 3
    Add a third table (04:49)
  4. 4
    Table aliases (04:33)
  5. 5
    Select from three tables (03:38)
  6. 6
    Column aliases (05:16)
  7. 7
    Bind variables (06:08)
  8. 8
    Check Constraints (05:56)
  9. 9
    Not Null Constraint (04:06)
  10. 10
    Unique Constraint (03:58)
  11. 11
    Conclusion (01:05)

Select Mastery Level 1

Use the sample database to write Select queries to analyse data, while learning about grouping and functions.

  1. 1
    Introduction (02:00)
  2. 2
    Setup scripts (04:24)
  3. 3
    Our requirements (02:03)
  4. 4
    Show all orders (05:59)
  5. 5
    Show number of orders (04:10)
  6. 6
    Grouping the results (05:56)
  7. 7
    Show order value (04:56)
  8. 8
    Next report requirements (01:02)
  9. 9
    Show order details (06:16)
  10. 10
    Group by person (03:07)
  11. 11
    Limit customers (02:57)
  12. 12
    Conclusion (02:00)

Select Mastery Level 2

Use the sample database to demonstrate more features of the Select query, such as subqueries and views.

  1. 1
    Introduction (01:54)
  2. 2
    Setup scripts (03:49)
  3. 3
    Our requirement (01:25)
  4. 4
    Show orders (04:22)
  5. 5
    Find latest order (03:00)
  6. 6
    What is a subquery? (02:36)
  7. 7
    Write outer query (03:06)
  8. 8
    Create a view (05:22)
  9. 9
    Conclusion (01:20)

Select Mastery Level 3

Use the sample database to demonstrate more features of the Select query, including row limiting, subqueries, and more functions.

  1. 1
    Introduction (02:01)
  2. 2
    Setup scripts (03:44)
  3. 3
    Our requirements (01:34)
  4. 4
    Select companies by order value (05:03)
  5. 5
    Operators (04:11)
  6. 6
    Lookup table (06:19)
  7. 7
    Select top 5 (03:42)
  8. 8
    Simplify query with a subquery (02:54)
  9. 9
    Conclusion (01:17)

Bulk Insert

Learn how to insert many rows at once using a variety of techniques, the Truncate statement, and auto-increment features.

  1. 1
    Introduction (01:49)
  2. 2
    Our requirement (01:57)
  3. 3
    Insert individual rows (04:43)
  4. 4
    Remove data (02:58)
  5. 5
    Insert multiple rows (05:39)
  6. 6
    Auto increment (07:08)
  7. 7
    Disable foreign keys (04:10)
  8. 8
    Transactions (04:42)
  9. 9
    Importing from a file (03:24)
  10. 10
    Conclusion (01:13)

Soft Delete

Learn what a soft delete is, why you would use it, and how to implement it.

  1. 1
    Introduction (01:07)
  2. 2
    Our database design (03:06)
  3. 3
    What is a soft delete? (02:45)
  4. 4
    Altering the table (04:34)
  5. 5
    Delete using a trigger (06:22)
  6. 6
    Delete using a stored procedure (05:24)
  7. 7
    Selecting from the table (03:41)
  8. 8
    Inserting records (01:37)
  9. 9
    Conclusion (01:13)

Sample CSV to Normalised Database

Learn how to import some CSV files into a database and transform the design and tables into a better, normalised design.

  1. 1
    Introduction (01:49)
  2. 2
    Import a CSV File in MySQL Workbench (03:12)
  3. 3
    Import a CSV File in Oracle SQL Developer (02:51)
  4. 4
    Import a CSV File in SSMS (05:07)
  5. 5
    Import a CSV File in DBeaver (03:16)
  6. 6
    Query the Tables (04:08)
  7. 7
    Create Our Next Table (07:18)
  8. 8
    Extract Title Type (04:33)
  9. 9
    Extract Age Certification (06:04)
  10. 10
    Extract Genres (12:38)
  11. 11
    Extract Production Countries (03:23)
  12. 12
    Final Design and Select Queries (02:45)
  13. 13
    Conclusion and Next Steps (01:31)

Relational Database Design

Learn how to design an effective and efficient database, including the process of normalisation.

  1. 1
    Welcome (01:52)
  2. 2
    What is a database? (02:53)
  3. 3
    Different types of databases (01:22)
  4. 4
    Benefits of relational databases (07:07)
  5. 5
    What is data modeling and the design process (01:44)
  6. 6
    Determine the goal of the database (02:33)
  7. 7
    Consider the current system (02:05)
  8. 8
    Gathering requirements of the database (02:48)
  9. 9
    Finding exceptions to the rules (04:35)
  10. 10
    Identifying entities (07:11)
  11. 11
    Defining the attributes (07:47)
  12. 12
    Normalisation (01:40)
  13. 13
    First normal form (01:34)
  14. 14
    First normal form: student (05:18)
  15. 15
    First normal form: subject (02:05)
  16. 16
    First normal form: teacher (02:25)
  17. 17
    First normal form: university (02:45)
  18. 18
    Relationships (02:41)
  19. 19
    Second normal form (01:39)
  20. 20
    Second normal form: student and subject (09:53)
  21. 21
    Second normal form: teacher (02:22)
  22. 22
    Second normal form: university (03:22)
  23. 23
    Third normal form (01:08)
  24. 24
    Third normal form: student and subject (06:50)
  25. 25
    Third normal form: teacher and university (01:48)
  26. 26
    Third normal form completed (02:43)
  27. 27
    Data types and precision (03:52)
  28. 28
    Integrity constraints (01:29)
  29. 29
    Naming conventions (01:46)
  30. 30
    Lookup tables (01:38)
  31. 31
    Auditing tables (02:40)
  32. 32
    Implementation and next steps (01:20)

Advanced Database Modeling

Learn a range of advanced tips and strategies for modeling different scenarios, and overcoming common issues found when designing databases.

  1. 1
    Introduction (01:48)
  2. 2
    Multiple values in one column (05:46)
  3. 3
    Alternatives in one column (04:33)
  4. 4
    Entity Attribute Value (08:16)
  5. 5
    New tables for each group (04:45)
  6. 6
    New columns for each similar value (05:40)
  7. 7
    Storing calculated fields (04:20)
  8. 8
    Use a soft delete (04:36)
  9. 9
    Audit tables (05:44)
  10. 10
    Names (03:32)
  11. 11
    Addresses (03:55)
  12. 12
    Phone numbers (01:58)
  13. 13
    Currency (03:39)
  14. 14
    Email address (02:54)
  15. 15
    IP address (02:28)
  16. 16
    Use VARCHAR instead of CHAR (03:59)
  17. 17
    Primary Key Constraints with duplicates (05:24)
  18. 18
    Lookup tables and Check Constraints (03:53)
  19. 19
    Polymorphic association (03:40)
  20. 20
    Not Null (02:38)
  21. 21
    Conclusion (01:01)

Case Study: Database Design 1

This is a case study that shows you how to design a database for a social media website.

  1. 1
    Introduction (01:40)
  2. 2
    Our requirement (01:59)
  3. 3
    The first table (04:07)
  4. 4
    Posts table (03:54)
  5. 5
    Comments on posts (03:44)
  6. 6
    Likes on posts (03:41)
  7. 7
    Add a photos table (02:53)
  8. 8
    Comments on photos (03:06)
  9. 9
    Likes on photos (02:09)
  10. 10
    Modifying our design (06:21)
  11. 11
    Add videos table (03:00)
  12. 12
    Replying to comments (03:44)
  13. 13
    Our final design (01:50)

Oracle PL/SQL Level 1

Learn the basics of Oracle's procedural language (PL/SQL) by writing a simple Hello World program, using variables, conditions, and loops. 

  1. 1
    Introduction (01:51)
  2. 2
    Your first program (05:26)
  3. 3
    Variables (04:56)
  4. 4
    Number variables (05:17)
  5. 5
    Modify variables (02:57)
  6. 6
    Constants (05:48)
  7. 7
    Conditions (06:18)
  8. 8
    Loops (05:57)
  9. 9
    Conclusion (01:08)

Oracle PL/SQL Level 2

Learn how to create procedures and functions, and how to work with exceptions.

  1. 1
    Introduction (01:05)
  2. 2
    Create a simple stored procedure (05:10)
  3. 3
    Parameters (06:13)
  4. 4
    Output parameters (04:36)
  5. 5
    Functions (04:42)
  6. 6
    Exceptions (05:44)
  7. 7
    Raising exceptions (03:22)
  8. 8
    Conclusion (01:13)

Oracle PL/SQL Level 3

Learn how to use Select, Insert, Update, and Delete statements in PL/SQL, and learn about cursors.

  1. 1
    Introduction (01:11)
  2. 2
    Inserting data (04:01)
  3. 3
    Updating data (04:57)
  4. 4
    Deleting data (02:21)
  5. 5
    Selecting data (03:32)
  6. 6
    Explicit cursors (03:51)
  7. 7
    Handling multiple rows with loops and cursors (03:42)
  8. 8
    Selecting multiple columns (03:19)
  9. 9
    Conclusion (01:10)

Oracle Triggers

This course teaches you what a trigger is, why to use one, and includes many examples.

  1. 1
    Welcome (01:14)
  2. 2
    What is a trigger? (04:39)
  3. 3
    Before Insert trigger (09:10)
  4. 4
    After Insert trigger (05:39)
  5. 5
    Instead Of trigger (06:00)
  6. 6
    Edit a trigger (03:27)
  7. 7
    Drop a trigger (01:11)
  8. 8
    Enable and disable a trigger (02:11)
  9. 9
    Before Update trigger (06:06)
  10. 10
    After Update of a Row trigger (02:52)
  11. 11
    After Update of a Value trigger (03:02)
  12. 12
    Before Delete trigger (03:25)
  13. 13
    After Delete trigger (02:10)
  14. 14
    Before Insert statement-level trigger (02:18)
  15. 15
    Database Logon trigger (02:47)
  16. 16
    Compound trigger (04:32)
  17. 17
    Compound trigger: complex constraint (06:09)
  18. 18
    New and old values (02:37)
  19. 19
    Debugging a trigger (07:32)
  20. 20
    Call a stored procedure from a trigger (03:43)
  21. 21
    Restrictions, privileges, and tips (02:27)
  22. 22
    Mutating table error (06:48)
  23. 23
    Viewing information about triggers (03:24)
  24. 24
    Conclusion (00:39)

Create a Bank Database

This course will explain the concepts of transactions, auto incrementing, and check constraints.

  1. 1
    Introduction (01:59)
  2. 2
    Load the sample database (02:13)
  3. 3
    Add a transaction record (05:40)
  4. 4
    Generate unique IDs (10:18)
  5. 5
    Data quality with Check Constraints (06:39)
  6. 6
    Using a transaction (10:44)
  7. 7
    Multiple sessions (08:22)
  8. 8
    Conclusion (00:50)

Views in SQL Server

This course teaches you about views in SQL Server: what they are, how to create them, and when to use them.

  1. 1
    Introduction (01:11)
  2. 2
    What is a view? (02:27)
  3. 3
    Setting up sample data (03:11)
  4. 4
    Create a view (04:40)
  5. 5
    Create a view with multiple tables (02:34)
  6. 6
    Inserting data in a view (02:40)
  7. 7
    Updating data in a view (02:27)
  8. 8
    Non-updatable views (05:27)
  9. 9
    Rename and drop views (02:45)
  10. 10
    Find information about a view (03:16)
  11. 11
    Indexed views (05:01)
  12. 12
    Conclusion (00:36)

Stored Procedures in SQL Server

In this course, you'll learn all about stored procedures in SQL Server: what they are, why to use them, how to create them, and a range of features that are available.

  1. 1
    Introduction (01:46)
  2. 2
    Stored procedure basics (02:32)
  3. 3
    Create a stored procedure (05:41)
  4. 4
    Drop a stored procedure (01:21)
  5. 5
    Modify a stored procedure (03:13)
  6. 6
    Set Nocount On (02:07)
  7. 7
    Parameters (07:29)
  8. 8
    Text Parameters (02:41)
  9. 9
    Optional Parameters (04:37)
  10. 10
    Variables (03:23)
  11. 11
    Storing results in variables (03:41)
  12. 12
    Output parameters (04:43)
  13. 13
    Inserting data (03:03)
  14. 14
    Updating data (02:48)
  15. 15
    Deleting data (01:30)
  16. 16
    If and Else (06:35)
  17. 17
    While loop (03:41)
  18. 18
    Error handling with Try Catch (04:39)
  19. 19
    Throwing errors (05:21)
  20. 20
    Raising errors (04:15)
  21. 21
    Conclusion (00:37)

CTEs in SQL Server

In this course, you'll learn about CTEs (Common Table Expressions) and how to use them in SQL Server to improve your queries.

  1. 1
    Introduction (01:19)
  2. 2
    What is a CTE? (05:17)
  3. 3
    Benefits of CTEs (01:03)
  4. 4
    Two CTEs in one query (05:05)
  5. 5
    CTE in Create View (01:56)
  6. 6
    CTE in Create Table as Select (03:42)
  7. 7
    CTE in Update Statement (02:29)
  8. 8
    What is a recursive CTE? (01:04)
  9. 9
    Recursive CTE example (05:12)
  10. 10
    Indenting results of recursive queries (04:19)
  11. 11
    Preventing infinite loops (02:45)
  12. 12
    Conclusion (00:46)

Introduction to Data Warehousing

This course explains what data warehousing and ETL is, and shows you how to create and populate a data warehouse.

  1. 1
    Welcome (01:19)
  2. 2
    What is a data warehouse? (03:17)
  3. 3
    Transaction vs analytical databases (01:59)
  4. 4
    What is business intelligence? (02:16)
  5. 5
    Our sample database (00:44)
  6. 6
    Setting up SQL Server (07:26)
  7. 7
    Setting up the sample database (03:30)
  8. 8
    Overall process (02:30)
  9. 9
    Dimensional modeling (04:17)
  10. 10
    Current AdventureWorks ERD (03:55)
  11. 11
    Create the fact table (09:00)
  12. 12
    Create the dimension tables (18:46)
  13. 13
    ETL (01:51)
  14. 14
    Download and install Visual Studio (04:42)
  15. 15
    Create the project and first step (11:39)
  16. 16
    Get data from multiple tables (13:00)
  17. 17
    Populate the customer dimension (11:57)
  18. 18
    Populate the salesperson dimension (07:21)
  19. 19
    Populate the address dimension (02:37)
  20. 20
    Populate the date dimension (04:50)
  21. 21
    Populate the fact table (14:46)
  22. 22
    Populate the second fact table (04:45)
  23. 23
    Summary of steps (03:12)
  24. 24
    Run SQL queries (03:55)
  25. 25
    Conclusion (00:54)

Write Faster SQL

This course shows you how you can improve the performance of your SQL queries. You'll learn how to find slow areas, many strategies to improve the queries, and how to measure the results.

  1. 1
    Welcome (02:10)
  2. 2
    Start with your own experience (05:29)
  3. 3
    Ask other people (03:23)
  4. 4
    Look at the data dictionary (09:25)
  5. 5
    Look at other tools (04:34)
  6. 6
    Choose a query (07:10)
  7. 7
    Measure your query (05:20)
  8. 8
    Analyse execution plan (06:36)
  9. 9
    Create indexes (10:00)
  10. 10
    Remove Distinct (05:11)
  11. 11
    Remove calculated fields from Where and Joins (04:57)
  12. 12
    Remove unneded tables and columns (04:21)
  13. 13
    Use Union All instead of Union (03:14)
  14. 14
    Use Exists (03:42)
  15. 15
    Avoid wildcard matching (03:48)
  16. 16
    Use bind variables (05:47)
  17. 17
    Change Union to Case (05:28)
  18. 18
    Use a temp table (05:47)
  19. 19
    Partition data (09:08)
  20. 20
    Change correlated subqueries to joins (03:39)
  21. 21
    Avoid cursors (05:01)
  22. 22
    Compare the data (06:02)
  23. 23
    Compare the performance (04:35)
  24. 24
    Deploy the changes (02:01)
  25. 25
    Making further changes (01:38)

Understanding Complex Queries

In this course, you'll learn a step-by-step process to understand complex SQL queries, and several tips on how to improve readability and formatting of the query to make it easier to understand.

  1. 1
    Introduction (01:44)
  2. 2
    Open your query (04:59)
  3. 3
    Auto format (04:34)
  4. 4
    Manual format (07:57)
  5. 5
    Diagram the tables (04:15)
  6. 6
    Add columns to the diagram (05:20)
  7. 7
    Add filtering columns to the diagram (04:56)
  8. 8
    Extra information and review (03:44)
  9. 9
    Run a small part of the query (03:49)
  10. 10
    Add comments (05:03)
  11. 11
    Add aliases (04:58)
  12. 12
    Run more of the query (02:31)
  13. 13
    Improve joins (03:19)
  14. 14
    Remove unnecessary keywords (05:15)
  15. 15
    Convert to CTE (02:44)
  16. 16
    Subquery to join (03:14)
  17. 17
    Conclusion (00:53)

Case Study: Show a Page of Records

This course is a case study that shows you step-by-step how to write a query that shows a page of records. You'll learn about pagination, row limiting, subqueries, grouping, string aggregation, and more.

  1. 1
    Introduction (01:30)
  2. 2
    Our sample database (02:48)
  3. 3
    Show the books (03:07)
  4. 4
    Get the number of sales (06:32)
  5. 5
    Add the publisher (03:07)
  6. 6
    Add row limiting and pagination (08:08)
  7. 7
    Show authors (04:35)
  8. 8
    Find authors for books (04:41)
  9. 9
    Add to main query (05:41)

Complex Queries 1: Sales Report

This course will show you step-by-step how to write a complex query. We’ll use several SQL features (advanced grouping, aggregate functions, subqueries) to write a sales report on a book database.

  1. 1
    Introduction (01:36)
  2. 2
    Our sample database (02:48)
  3. 3
    Our requirement (01:29)
  4. 4
    Show all orders (02:44)
  5. 5
    Count orders by date (04:24)
  6. 6
    Count book sales (03:04)
  7. 7
    Count sales by year (04:05)
  8. 8
    Show an overall total (04:22)
  9. 9
    Count and sum sales by year (03:56)
  10. 10
    Count and sum sales by year and month (04:03)
  11. 11
    Clarify data and exclude some orders (06:14)

Complex Queries 2: Shipping Report

This course demonstrates several SQL features to help build a report on shipping destinations for online orders. You’ll learn how to use grouping, window functions, and analysing data to see if the query is correct.

  1. 1
    Introduction (01:34)
  2. 2
    Our sample database (02:48)
  3. 3
    Our requirement (01:56)
  4. 4
    Show city and country (03:22)
  5. 5
    Show order count (05:49)
  6. 6
    Show customer count (07:17)
  7. 7
    Missing cities (03:37)
  8. 8
    Order details (03:34)
  9. 9
    Include customer details (02:23)
  10. 10
    Include shiping method (02:07)
  11. 11
    Add order value (02:07)
  12. 12
    Add order count (03:09)
  13. 13
    Final queries (02:02)

Certification: Oracle Certified Associate (1Z0-071)

This course is a study guide for the Oracle SQL Certified Associate (1Z0-071) exam).
Watch the videos and take the practice exam at the end of the course to help you study for this certification.

  1. 1
    Course intro (02:58)
  2. 2
    Exam information (05:30)
  3. 3
    Exam tips (03:34)
  4. 4
    Setting up the sample database (03:53)
  5. 5
    Explain the relationship between a database and SQL (03:03)
  6. 6
    Identify the connection between an ERD and a database using SQL SELECT statements (05:43)
  7. 7
    Describe the purpose of DML (02:27)
  8. 8
    Build a SELECT statement to retrieve data from an Oracle Database table (12:22)
  9. 9
    Use the ORDER BY clause to sort SQL query results (06:46)
  10. 10
    Limit the rows that are retrieved by a query (12:11)
  11. 11
    Use ampersand substitution to restrict and sort output at runtime (06:21)
  12. 12
    Describe the different types of joins and their features (06:30)
  13. 13
    Use SELECT statements to access data from more than one table using equijoins and nonequijoins (10:58)
  14. 14
    Join a table to itself by using a self-join (06:01)
  15. 15
    View data that generally does not meet a join condition by using outer joins (03:10)
  16. 16
    Use various types of functions available in SQL (01:34)
  17. 17
    Use character, number, and date functions in SELECT statements (10:26)
  18. 18
    Use analytical functions in SELECT statements (13:00)
  19. 19
    Use conversion functions (01:47)
  20. 20
    Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions (11:46)
  21. 21
    Apply general functions and conditional expressions in a SELECT statement (07:03)
  22. 22
    Describe the use of group functions (05:56)
  23. 23
    Group data by using the GROUP BY clause (03:51)
  24. 24
    Include or exclude grouped rows by using the HAVING clause (02:25)
  25. 25
    Use a set operator to combine multiple queries into a single query (06:52)
  26. 26
    Control the order of rows returned (01:58)
  27. 27
    Define subqueries (01:08)
  28. 28
    Describe the types of problems subqueries can solve (05:11)
  29. 29
    Describe the types of subqueries (01:28)
  30. 30
    Use single-row and multiple-row subqueries (06:33)
  31. 31
    Use correlated subqueries (03:12)
  32. 32
    Update and delete rows using correlated subqueries (07:19)
  33. 33
    Use the EXISTS and NOT EXISTS operators (04:02)
  34. 34
    Use the WITH clause (03:34)
  35. 35
    Insert rows into a table (08:50)
  36. 36
    Update rows in a table (05:00)
  37. 37
    Delete rows from a table (06:14)
  38. 38
    Describe the features of multitable INSERTs (08:18)
  39. 39
    Control transactions (10:40)
  40. 40
    Merge rows in a table (05:42)
  41. 41
    Describe the purpose of DDL (01:56)
  42. 42
    Describe data types that are available for columns (01:21)
  43. 43
    Create a simple table (11:28)
  44. 44
    Create constraints for tables (06:45)
  45. 45
    Describe how schema objects work (01:45)
  46. 46
    Query various data dictionary views (04:06)
  47. 47
    Create and maintain indexes including invisible indexes and multiple indexes on the same columns (05:03)
  48. 48
    Drop columns and set column UNUSED (04:08)
  49. 49
    Perform flashback operations (04:41)
  50. 50
    Create and use external tables (04:20)
  51. 51
    Create simple and complex views with visible/invisible columns (06:18)
  52. 52
    Create, maintain and use sequences (03:52)
  53. 53
    Differentiate system privileges from object privileges (03:10)
  54. 54
    Grant privileges on tables and on a user (03:21)
  55. 55
    Distinguish between privileges and roles (01:17)

SQL Developer Jumpstart

This course will help you get started quickly with Oracle SQL Developer.

  1. 1
    Welcome (04:20)
  2. 2
    Download SQL Developer (04:21)
  3. 3
    Download and install Oracle Express (05:50)
  4. 4
    Tour of SQL Developer (05:31)
  5. 5
    Connections (11:03)
  6. 6
    Database Object Browser (06:39)
  7. 7
    Preferences (20:46)
  8. 8
    SQL files (08:40)
  9. 9
    Running queries (07:46)
  10. 10
    Query builder (05:22)
  11. 11
    Importing data into a table (05:20)
  12. 12
    Exporting tables and database (05:52)
  13. 13
    Exporting results (05:16)
  14. 14
    Functions (04:40)
  15. 15
    Procedures (03:41)
  16. 16
    Debugging (06:01)
  17. 17
    Code navigation (03:20)
  18. 18
    View menu (08:13)
  19. 19
    Data modeler overview (01:46)
  20. 20
    Versioning (11:11)
  21. 21
    Reports (05:26)
  22. 22
    Tools menu (02:35)
  23. 23
    Window menu (02:12)
  24. 24
    Features and extensions (03:35)
  25. 25
    Conclusion (01:06)

Introduction to SQL*Plus

Learn the basics of SQL*Plus, Oracle's command-line tool for working with Oracle databases.

  1. 1
    What is SQL*Plus? (01:14)
  2. 2
    Open SQL*Plus and connect to a database (06:06)
  3. 3
    Creating a table (05:07)
  4. 4
    Copy and paste text (02:12)
  5. 5
    Insert data into a table (03:12)
  6. 6
    Selecting data (02:26)
  7. 7
    Describe statement (01:09)
  8. 8
    Formatting columns (08:30)
  9. 9
    Formatting data (02:34)
  10. 10
    Command line formatting (01:58)
  11. 11
    Send query results to a file (01:52)
  12. 12
    Create HTML reports (02:44)
  13. 13
    Run an SQL file (03:25)
  14. 14
    Performance in SQL*Plus (05:29)
  15. 15
    Setting defaults using the login file (02:39)
  16. 16
    Run operating system commands (01:17)
  17. 17
    Run PL/SQL blocks (02:39)
  18. 18
    Conclusion (00:33)

Introduction to Toad for Oracle

Learn how to get started with Toad for Oracle and a demonstration of many of its features.

  1. 1
    What is Toad? (03:00)
  2. 2
    Download Toad (03:28)
  3. 3
    Install Toad (02:39)
  4. 4
    Quick tour (02:07)
  5. 5
    Create a database connection (04:47)
  6. 6
    Edit, import, export, and auto connect for connections (03:25)
  7. 7
    Run SQL statements (02:34)
  8. 8
    Navigator and multiple SQL statements (04:01)
  9. 9
    Working with files (02:56)
  10. 10
    Export query results (04:14)
  11. 11
    Working with desktops (00:50)
  12. 12
    Jump search (01:53)
  13. 13
    Find and replace (02:50)
  14. 14
    Find in files (01:54)
  15. 15
    Using bookmarks (02:24)
  16. 16
    Explain plans (05:10)
  17. 17
    Changing schema (01:16)
  18. 18
    Schema browser (02:58)
  19. 19
    Filtering the schema browser (02:30)
  20. 20
    Database browser (01:06)
  21. 21
    Session browser (03:09)
  22. 22
    Working with the data grid (01:40)
  23. 23
    Searching the data grid (01:35)
  24. 24
    Filtering in the data grid (02:34)
  25. 25
    Editing data sets (03:51)
  26. 26
    Insert and delete rows in the data grid (01:40)
  27. 27
    Describe objects (01:47)
  28. 28
    Object search (02:44)
  29. 29
    Create, alter, and drop objects (03:28)
  30. 30
    Create, modify, and view object privileges (02:20)
  31. 31
    Using the object palette (02:51)
  32. 32
    Copy data to another table (02:09)
  33. 33
    Compare data wizard (03:02)
  34. 34
    Compare objects (02:04)
  35. 35
    Compare schemas (02:15)
  36. 36
    Compare files (03:37)
  37. 37
    Exporting data (01:24)
  38. 38
    Importing data (03:43)
  39. 39
    Code completion templates (02:30)
  40. 40
    Code snippets (01:49)
  41. 41
    Using the code formatter (02:24)
  42. 42
    Configuring the code formatter (01:57)
  43. 43
    Improving the view of your code (02:40)
  44. 44
    Strip code feature (01:27)
  45. 45
    Make code feature (02:11)
  46. 46
    SQL recall (02:25)
  47. 47
    Import or export saved SQL (02:27)
  48. 48
    Load database objects (01:00)
  49. 49
    Create a new object (01:50)
  50. 50
    Extract procedure (01:59)
  51. 51
    DBMS output (02:18)
  52. 52
    Setting up the debugger (01:43)
  53. 53
    Start debugging (02:06)
  54. 54
    Debug results and the call stack (01:54)
  55. 55
    Breakpoints (02:24)
  56. 56
    Watches (02:01)
  57. 57
    Smart watches (01:39)
  58. 58
    Workspaces (02:01)
  59. 59
    Shortcut keys (01:27)
  60. 60
    Working with projects (03:12)
  61. 61
    Conclusion (00:43)

Join Database Star Academy

Here's what you get:

  • 33 high-quality video courses
  • 40 practice questions
  • 1 assignment (larger task with several steps to practice your skills)
  • Courses and content for Oracle, SQL Server, MySQL, and PostgreSQL
  • Immediate access to all content
  • Certificates of Completion for finishing a course
  • Quizzes on courses where applicable
  • SQL scripts available for each course
  • PDF versions of some courses to use as a reference
  • Badges, points, and leaderboard system to encourage you to complete courses
  • Timesaver guides for SSMS, MySQL Workbench, and SQL Developer, to save time using these IDEs
  • PDF checklists, flowcharts, and cheat sheets
  • 5 sample data sets to load into your database + questions to answer by writing SQL queries
  • A reference page of SQL Topics with links to content, to save you time when learning a concept
Instance 1

Monthly

Pay the same amount each month

 

$

19

/month

Join Now

Annual

Pay one amount, once per year

 

$

190

/year

JOIN Now

All prices in USD.

What our customers are saying:

His explanations are so clear

Ben has simplified sub-queries for me, regex, and many other topics. I feel slow when reading a lot of explanations off the internet, but his explanations are so clear, they make these topics easy to understand.

The "SQL Interview Questions & Answers", which I nearly didn't read, was full of answers I had on how to use CTE, DBMS-Output, temp tables. And then I put them all in a folder that are easy to refer back to.

On top if it all, I don't have to sift through a 10 minute you tube to find the 10 seconds of information I'm looking for. I also get emails from Database Star on topics I didn't even knew I had questions on - like how to manage your time, and book recommendations like "Clean Code" by Robert C. Martin.

Nancy T  //  Database Star Academy member

Videos are simple, informative, and easy to understand

I think I am one of the guys who joined you almost from the beginning of the launch of your academy (correct me if I am wrong). And, this is the first time in my IT career I have ever subscribed to any site such as yours.

After being with you for some time, I now feel that I have not made any mistake by subscribing to your site. I have learnt a lot by following your site which cannot be described in words. Examples given after each topic are very useful. The videos are simple, informative and easy to understand. There is also a lot of general content which is very useful in developing the subject.

I am extremely grateful to you for that. I am also happy to see you coming up with different flavors of SQL (SQL Server, MySQL & Postgress SQL). It would be easy for a person to refer to one single site for everything. In fact, I am looking forward to detailed stuff related to Oracle DBA and other related topics. Hope, it is on the way.

Korlahalli S  //  Database Star Academy member

Progression is well designed

The brevity of lessons are more manageable and the progression is well designed.

Merri H  //  Database Star Academy member

Good breakdown by difficulties

A large content, well structured. High quality video and sound. Good breakdown of difficulties by a succession of videos.

Santiano  //  Database Star Academy member

You are responsive and you are listening!

Before I bought the course I mailed you with few questions. AND YOU ANSWERED. Trust me, this is a rare thing...with you answering, I knew you are not a bot.


Your courses are short and full of practical knowledge + you are responsive and you are listening!

Michal Z  //  Database Star Academy member

Everything is in one place

Your explanations are so clear and concise that you don’t need to go on and on and on as I have heard others do.


I do like that everything is in one place and I’m not trying to go to five or six different courses or YouTube videos for one topic.

Donna M //  Database Star Academy member

The finest, most lucid, considerate learning source that I have ever experienced

I have been in the IT industry now, for over 40 years. In the late 1990's, I begin to gravitate specifically to Oracle products. In my pursuits of capturing a reliable and coherent source for specific Oracle based knowledge, I have kissed a many frogs.

I say this loud and without anyone persuading me, that Database Star is the finest, most lucid, considerate learning source that I have ever experienced. The gentleman who produces their products (Ben Brumm), actually listens to your input.

He is personable, bright, responsive and provides extremely helpful materials that deal with real world scenarios. I have several people that I peronally mentor and I always refer them to Database Star.

Michael I  //  Database Star Academy member

I have gotten a great many new ideas and have done refresher type reviews on many subject presented in the course of a year.

Its a great tool, and gives me the opportunity to see how other do the things I do.

James W  //  Database Star Academy member

If somebody want to learn postgreSQL DB from the "ground" that is the great pace to start.

Sandor V  //  Database Star Academy member

I highly recommend Database Star for those who want to make their future career in data analytics or related fields to learn the basic concepts and foundations.

The curriculum is well organized and easy to understand. I found Database Star while I was searching documents to improve my database and SQL skills. Ben used to send me different links and resources even before I became a member of Database Star.

Abebe T //  Database Star Academy member

Can't go wrong with a Database Star Academy membership - excellent resource for all things to do with databases - all the big vendors are covered e.g. Oracle, SQL Server, Postgres, MySQL.

Database Star Academy covers everything from database-creation i.e. normalization and modelling through to data warehousing. I've used as much as I have time for so far, and really enjoy that I can refresh existing skills and learn-up new skills in my career with databases.

The section on different IDE's is also very useful - gets you up and running very quickly with the most popular tools for interacting with the databases mentioned above.

There's so much to learn about databases and so many applications for databases that its a constantly-growing area - I feel that every penny spent on Database Star Academy membership is a good investment as I continue learning & growing in my career as a Database Developer. Thanks Ben!

Yehoshua H //  Database Star Academy member

The video courses in Database Star Academy cover a wide range of practical topics in major databases, and I haven't seen any other online course provide the same. There are some topics like database design which are ignored by many SQL courses, but are discussed thoroughly in this course and the referenced pages in the Database Star blog.

Also some very relevant practical topics such as populating the normalized tables from an initial raw table is something you can rarely find in any other database course. It was described with different scenarios in this course.

Overall the courses in this series helped me a lot improve not only my DB query skills, but also on the design and implementation skills.

As a learner who has tried several different online courses, I believe the insight you get from the course series in Database Star Academy is very unique and valuable. The nice organization of courses into several categories helps a learner with any level of database knowledge to quickly find the material most appropriate for them.

I highly recommend the Database Star Academy courses. The potential knowledge you can quickly get from these courses and apply them to your projects is not comparable to other courses online.

Mohammad R //  Database Star Academy member

100%
MONEY BACK GUARANTEE

100% Satisfaction Guarantee

The Database Star Academy comes with my no-questions-asked, 30-day money-back guarantee.


You are fully protected by our 100% Satisfaction-Guarantee. If you are not satisfied with your membership, then let me know within the first 30 days of purchasing, and I will provide you with a full and prompt refund.


I'm confident that you'll get some value for your membership within the first 30 days!

Ben Brumm  //  Course Teacher

I've been working with databases and SQL for many years, after completing my computer science degree in 2007, majoring in Oracle databases. I've been a software consultant ever since.

I've also obtained the Oracle SQL Expert certification.

I created this membership to teach SQL and database skills to many people. I want you to be able to improve your knowledge and skills with SQL and databases.

Frequently Asked Questions

Why should I join your membership?

I've been teaching SQL and databases to coworkers and on my blog for years. I'm also a certified Oracle SQL Expert and passionate about writing good SQL.

The membership site is focused on SQL, so you'll be getting your knowledge straight from someone who knows it, rather than a broad "software development" membership.

Can I cancel my membership?

You can cancel at any time - simply click the Cancel button inside the membership site, or in your PayPal account. Or contact me.

What happens after I buy?

You'll get an email with details on how to register for the course on Database Star.

Once you register, you'll be able to log in to an area of the site that has a list of all the modules, videos, and downloadable material. You'll then be charged every month.

Can I sign up, download all the material, then cancel?

While I would prefer that you didn't do that, there's nothing stopping you. It would take you a while to go through and download the hundreds of videos though.

This is for you if...

You want to get better at working with databases
You don't have a lot of time to study and improve your skills and want to know the right thing to study.
You want to solve problems you're having on your database
You want to make your queries run faster
You want to understand how certain SQL features work

Why Enrol in Database Star Academy?

You'll get immediate access to all materials.
You'll learn from someone who has experience with databases and SQL.
You'll get focused education, not broad education like you get in college.
You can cancel at any time.
You don't have to pay thousands to travel or enrol in an in-person training class

One more thing...

The Database Star Academy is what I'm most proud of on my website. I truly enjoy helping and teaching others to improve their skills and solve problems.

If you're interested in learning and improving your SQL and database skills, I encourage you to enrol in Database Star Academy today.

Instance 1

Monthly

Pay the same amount each month

 

$

19

/month

  • Immediate access
  • Access to all content
  • Certificates of Completion
  • Practice questions, PDFs, and more
Join Now

Annual

Pay one amount, once per year

 

$

190

/year

  • Immediate access
  • Access to all content
  • Certificates of Completion
  • Practice questions, PDFs, and more
JOIN Now

© 2023, Elevated Online Services Pty Ltd

Terms and Conditions | Privacy | Contact