SQL Server Study Hub

Day 1: Fundamentals & Table Operations

1: Introduction
2: Database: create, alter, drop
3: Table: Create, Insert, Select, Update, Delete, Drop
4: Key Constraints: Primary, Foreign, Unique, Default, Check, Cascading referential Integrity

Day 1: Practice

Task 1: Create DB Library_record, toggle Single/Multi-user mode.
Task 2: Create Student_record & Book_record tables with PK, FK, Unique, Check, and Default constraints.
Task 3: Alter tables (add/drop columns) and test cascading referential integrity.

Day 2: Querying Data with SELECT

Topics covered: Retrieving data (SELECT, DISTINCT), filtering records (WHERE, IN, BETWEEN, Operators), pattern matching (LIKE & Wildcards), and sorting/limiting results (ORDER BY, TOP).

Read Day 2 Content

Day 2: Practice Questions

Retrieve specific columns and unique records from your tables.
Filter data using multiple conditions (AND, OR, IN, BETWEEN).
Search for specific text patterns using the LIKE operator and wildcards.
Sort results and retrieve the TOP N highest-priced items.

View Full Exercise

Day 3: Functions & Data Grouping

Topics covered: Built-in SQL functions (Aggregate, Math, String), summarizing data using the GROUP BY clause, and understanding the critical difference between WHERE and HAVING.

Read Day 3 Content

Day 3: Practice Questions

Calculate MIN, MAX, AVG, and SUM from a dataset.
Format text strings using UPPER, LOWER, and remove spaces with TRIM.
Group data by categories to generate summary reports.
Filter aggregated reports using the HAVING clause.

View Exercises

Day 4: Combining Data (UNION)

Topics covered: Combining multiple queries vertically using Set Operators. Understanding the performance and behavioral differences between UNION and UNION ALL, and the strict rules for column matching.

Read Day 4 Content

Day 4: Practice Questions

Stack data from regional tables into a single master report.
Identify and fix syntax errors in broken UNION queries.
Apply global sorting rules to combined datasets.

View Exercises

Day 5: Connecting Tables (JOINS)

Topics covered: Normalization basics and combining tables using INNER JOIN, LEFT/RIGHT/FULL OUTER JOINS, CROSS JOIN, and advanced Self Joins.

Read Day 5 Content

Day 5: Practice Questions

Retrieve matching records across multiple related tables.
Find "orphaned" records (e.g., Customers with no Orders) using LEFT JOIN.
Generate Cartesian products for product variations.
Write a Self Join to map a corporate hierarchy.

View Exercises

Day 6: Virtual Tables (Views)

Topics covered: Creating Views to save complex query logic. Using Views as a security layer to enforce Column-level security, Row-level filtering, and data aggregation.

Read Day 6 Content

Day 6: Practice Questions

Wrap complex JOIN statements into a simplified View.
Implement Column-level security by hiding sensitive employee data.
Implement Row-level security by restricting data to specific regions.
Create an aggregated View to hide individual transactions.

View Exercises

Day 7: Stored Procedures (Part 1)

Topics covered: Transitioning from queries to programmable SQL. Creating basic Stored Procedures, executing them, and making them dynamic using Input Parameters to handle complex business logic.

Read Day 7 Content

Day 7: Practice Questions

Wrap basic SELECT statements into a reusable Stored Procedure.
Execute, modify (ALTER), and delete (DROP) a procedure.
Create procedures that accept a single Input Parameter.
Create procedures that accept multiple parameters to execute UPDATE statements securely.

View Exercises