sql-fundamentals_skill

Overview

This skill teaches core SQL fundamentals for reading, writing, and shaping relational data. It covers SELECT, INSERT, UPDATE, DELETE and DDL commands like CREATE, ALTER, DROP. You will also learn filtering, sorting, aggregations, joins, basic window functions, NULL handling, and common SQL patterns. The focus is practical queries and simple optimization tips.

How this skill works

The skill walks through hands-on examples and common patterns, showing exact SQL statements for each concept. It inspects query structure, explains when to use each clause, and highlights common pitfalls like using SELECT * or missing indexes. Practical performance tips and sample problems help you apply concepts to real schemas.

When to use it

• When you need to read or modify relational data with SELECT/INSERT/UPDATE/DELETE
• When designing or changing schemas using CREATE, ALTER, DROP
• When aggregating or grouping results with GROUP BY and HAVING
• When combining tables using INNER, LEFT, or multi-table JOINs
• When you need basic window functions or subqueries for row-level calculations

Best practices

• Specify columns instead of using SELECT * to reduce IO and avoid surprises
• Add indexes on frequently filtered or joined columns but monitor write impact
• Filter rows early and push predicates before expensive joins where possible
• Use LIMIT for sampling large tables and avoid heavy full-table scans during development
• Prefer explicit JOINs and clear ON conditions to avoid accidental cartesian products

Example use cases

• Write a SELECT with WHERE and ORDER BY to extract top-paid employees
• Create and alter tables with appropriate data types and foreign keys
• Aggregate sales by month with GROUP BY and DATE functions for reporting
• Find duplicates or missing relations with DISTINCT, GROUP BY, and LEFT JOIN
• Compute row rankings and running totals with ROW_NUMBER and SUM window functions