SQL 101

The SQL 101 course is designed for someone aspiring to build a career as data engineer, analyst, or a data scientist. This course covers fundamental SQL concepts, including querying, database design, and data manipulation. learning to extract insights and support data-driven decision-making.

By the end of the course, you will be writing complex queries, optimizing database performance, and using SQL to solve practical problems.

Recommened Programs

• DBeaver Community
• DuckDB

You may also use any other database engine you feel comfortable with however the create database statement may need to be adjusted. It maybe also be that some functions are not in your chosen engine or are named differently.

Setup

To participate please fork the repo and follow one of the setup documents. please save your queries here. Also while going through the corse if you find an issue or think of an improvement please log an issue here

• DuckDB
• SQL Server
• MySQL
• Postgres

Grades

Grades have been added to help break your journy into helpful sections. Each section will cover different skill levels and will help you demonstrate your ability to other people.

Grade 1: Introduction to SQL and Databases

• Overview of SQL: What is SQL and its importance.

• Relational Databases: Basic concepts of relational databases, tables, rows, and columns.
• Simple Queries: Using SELECT to retrieve data from a single table.
• Filtering Data: Using WHERE clause to filter results.

Grade 2: Intermediate Data Retrieval

• Sorting Data: Using ORDER BY to sort query results.

• Limiting Results: Using LIMIT (or TOP in some SQL dialects) to restrict the number of returned rows.
• Basic Functions: Introduction to SQL functions like COUNT, SUM, AVG, MIN, and MAX.
• Aliasing: Using AS to rename columns in the result set.

Grade 3: Advanced Data Retrieval

• Joins: Understanding and using different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).

• Aggregate Functions: Grouping data with GROUP BY and filtering groups with HAVING.
• Subqueries: Writing and using subqueries in SELECT, FROM, WHERE, and HAVING clauses.
• Set Operations: Using UNION, INTERSECT, and EXCEPT.

Grade 4: Data Manipulation

• Inserting Data: Using INSERT to add new records to a table.

• Updating Data: Using UPDATE to modify existing records.
• Deleting Data: Using DELETE to remove records.

Grade 5: Database Design and Data Definition

• Creating Tables: Using CREATE TABLE to define new tables.

• Altering Tables: Using ALTER TABLE to modify existing tables (e.g., adding, dropping columns).
• Dropping Tables: Using DROP TABLE to delete tables.
• Constraints: Understanding and implementing primary keys, foreign keys, unique constraints, and check constraints.

Grade 6: Advanced SQL Functions and Expressions

• String Functions: Using functions like CONCAT, SUBSTRING, LENGTH, TRIM, etc.

• Date and Time Functions: Using functions like NOW, DATEADD, DATEDIFF, FORMAT, etc.
• Mathematical Functions: Using functions like ROUND, ABS, CEIL, FLOOR, etc.
• Case Statements: Using CASE for conditional logic within queries.

Grade 7: Performance Tuning and Optimization

• Indexes: Understanding the importance of indexes and how to create them.

• Query Optermization: Tips and techniques for writing efficient queries.
• Execution Plans: Reading and interpreting execution plans to optimize query performance.
• Temporary Tables and CTEs: Using temporary tables and Common Table Expressions (CTEs) to simplify complex queries.

Grade 8: Transactions and Security

• Transactions: Using BEGIN TRANSACTION, COMMIT, and ROLLBACK to manage transactions.

• Locking and Concurrency: Understanding locking mechanisms and how to handle concurrency.
• Windows Functions: Using ROWNUMBER, RANK, and NTILE.

Extra

In this section we will cover technoligy specific topics such as reading CSVs in DuckDB and variable in SQL Server (TSQL).

• Cursors: Using CURSOR in SQL Server to loop through data.