DB1 Practice Guide

Part 1: Foundational Topics (SQL)

Objective: Gain an understanding of the relational model, fundamental data retrieval techniques for datasets.

Topics Covered: - Relational Model - Basic SELECT Statement - Set Operators

Resources: - Introduction - Basic SQL Statements - Set Operators

Objective: Develop proficiency in working with data types and manipulating individual rows of data.

Topics Covered: - Data Types - Character Functions - Number Functions - Date Functions

Resources: - Data Types - SQL Functions - Character Functions - Number Functions - Date Functions

Objective: Master advanced SQL functions and techniques for summarizing and analyzing grouped data.

Topics Covered: - Conversion Functions - Other Functions - Conditional Expressions - Group Functions - GROUP BY

Resources: - Conversion Functions - Other Functions - Conditional Expressions - Group Functions - Group By

Objective: Learn to combine data from multiple sources, construct complex queries, and utilize Common Table Expressions (CTEs).

Topics Covered: - Joins - Subqueries - Common Table Expressions (WITH)

Resources: - Joins - Subqueries - WITH

Objective: Explore the theoretical foundations of relational databases and their operations.

Topics Covered: - Relational Algebra

Resources: - Relational Algebra

Objective: Acquire skills to modify data, define database structures, and create reusable virtual tables.

Topics Covered: - DML (Data Manipulation Language) - DDL (Data Definition Language) - Views

Resources: - DML and DDL - Views

Objective: Evaluate understanding and application of foundational SQL concepts.

Objective: Gain an introduction to procedural programming within the database environment.

Topics Covered: - PL/SQL Basics

Resources: - PL/SQL Basics

Objective: Learn to process multi-row result sets and utilize in-memory data structures effectively.

Topics Covered: - Cursors - Collections

Resources: - Cursors and Collections

Objective: Develop techniques to handle runtime errors gracefully and ensure robust code execution.

Topics Covered: - Exception Handling

Resources: - Exceptions

Objective: Explore advanced querying techniques for hierarchical data structures.

Topics Covered: - Recursion

Resources: - Recursion

Objective: Assess comprehension and application of advanced SQL and PL/SQL concepts.

Objective: Provide an opportunity to revisit and demonstrate mastery of key topics.