SQL Practice Repository

Introduction

Welcome to my SQL Practice Repository! This is where I documented and shared SQL scripts and exercises covering beginner ,intermediate and Advanced -level concepts. I practiced and improved my SQL skills through hands-on examples, learning different ways to manipulate and query data.

Topics Covered

Beginner Level

• Created Tables: Defined table structures using CREATE TABLE, specified data types, and set constraints like PRIMARY KEY and FOREIGN KEY.

• Used SELECT Statements: Retrieved data from tables using SELECT, chose specific columns, and applied expressions.

• Applied WHERE Clause: Filtered results using conditions with operators like =, >, <, AND, OR, and NOT.

• Used LIKE Operator: Applied wildcard matching (%, _) to find patterns in data.

• Grouped Data with GROUP BY: Grouped rows based on column values and applied aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX().

• Sorted Data with ORDER BY: Ordered query results using ASC or DESC.

• Filtered Groups with HAVING Clause: Applied conditions on grouped results.

• Limited Results with LIMIT Clause: Controlled the number of rows returned in a query.

• Used Aliasing: Renamed columns or tables using AS for better readability.

Intermediate Level

• Implemented CASE Statements: Introduced conditional logic inside queries.

• Performed Joins: Combined data from multiple tables:

  • INNER JOIN: Retrieved matching records.
  • LEFT JOIN: Returned all records from the left table.
  • RIGHT JOIN: Returned all records from the right table.
  • FULL JOIN: Retrieved all records from both tables.

• Used Subqueries: Executed queries within queries to fetch specific results.

• Worked with Window Functions: Performed calculations across rows:

  • ROW_NUMBER(): Assigned a unique number to each row.
  • RANK() and DENSE_RANK(): Assigned rank values to rows.
  • LAG() and LEAD(): Accessed previous or next row values in a dataset.

• Used String Functions: Manipulated text data using CONCAT(), SUBSTRING(), TRIM(), UPPER(), and LOWER().

• Combined Queries with Unions: Merged results from multiple queries:

  • UNION: Combined distinct records.
  • UNION ALL: Combined all records, including duplicates.

Advanced Level

• Used Common Table Expressions (CTEs): Created temporary result sets with WITH to improve query readability and reusability.

• Implemented Stored Procedures: Wrote reusable SQL procedures using CREATE PROCEDURE to execute logic with input parameters.

• Worked with Temporary Tables: Created temporary datasets using CREATE TEMPORARY TABLE, which existed only for the session.

• Created Triggers: Automated actions using CREATE TRIGGER to execute SQL statements before or after data changes (INSERT, UPDATE, DELETE).

• Scheduled Events: Used CREATE EVENT to automate tasks like backups, periodic updates, and data cleanup at scheduled intervals.