SQL for Data Analysis - with SQL Server

Apply criteria with the where clause to filter rows in select queries, using numeric and text criteria with single quotes and combining conditions like salary flag equals one.

Explore how to filter records using not equal to, handle null values with is null and is not null, and combine criteria with and/or.

Learn to filter data using multiple criteria with and, or, and in SQL Server. Build concise queries using where clauses to combine conditions and efficiently handle large data sets.

Master how to combine and and or in SQL queries with parentheses. This clarifies order of evaluation and shows salary flag equals one or job title equals senior tool designer.

Explore range-based where clauses to filter numerical values using greater than, less than, and between, including inclusive endpoints; apply to sales quota and total due examples, including open-ended ranges.

Learn to query text with patterns using wildcards in SQL Server, including the percent sign and bracket ranges, to flexibly match names and other fields with like and not like.

Sort query results with the order by clause to bring the largest totals to the top in descending order. Reference columns by alias or position and sort by multiple columns.

Extract unique values with select distinct for a single column or multiple columns. Use order by to reveal duplicates and confirm unique combinations.

Explore SQL text functions like left, right, len, and replace to derive fields such as area codes from phone numbers and empower flexible where criteria.

Learn how to safely concatenate first, middle, and last names using the concat with separator function in SQL Server, which gracefully handles null values and scales to many fields.

Learn time-saving SSMS tips, including autocomplete and tab completion, to quickly insert functions and derive columns like first name length, boosting daily coding efficiency.

Learn to wield nested text functions in SQL Server, using len, left, and right to derive usernames from emails and pad with leading zeros, and use replace to clean punctuation.

Learn date math in SQL to add or subtract time with dateadd and to measure elapsed days with datediff, using getdate for dynamic ranges like last seven days.

Explore core sql data types, including text, numeric, and date, and learn to cast between them with cast, covering implicit conversions and casting date time to date.

Learn to format data in SQL Server with the format function to render numbers as percentages, currency, and dates, using format codes and culture arguments; note it returns a string.

Use the isnull function to replace nulls with friendly fallbacks, such as 'no title' or zero in sales quotas, and prepare for advanced output with the case statement.

Master the mighty case statement in SQL Server to transform data, create derived fields, bucketize text and date values, and control logic with when, then, and else.

Explore stacking rows with union to combine orders from customer and vendor tables into a single dataset, and contrast with joining columns from different tables for integrated analysis.

Explore why data normalization in SQL reduces redundancy by splitting information into players and teams tables, using primary keys and foreign keys to establish one-to-many relationships in relational databases.

Discover how to join related tables in SQL Server by aliasing tables and using on to link business entity ids between sales and person tables.

Explore how to extend sql joins by adding multiple tables, linking on territory and city IDs, filtering with where clauses, and understanding inner versus outer joins in practical data analysis.

Learn how to combine inner and outer joins in a single query, choosing join types strategically, handling nulls, and applying criteria to left joins to preserve non-matches.

Explore entity relationship diagrams to visualize the structure of relational databases and the relationships between tables, including one-to-many joins and key guidance, with practical Adventure Works examples.

Format SQL code for readability by breaking lines, indenting blocks, and capitalizing keywords; use web-based formatters like poor sql to tailor style while weighing time costs.

Develop the ability to extract data from a relational database with surgical precision, apply sophisticated transformations for non-technical audiences, and build a foundation for artificial intelligence in tech.

Learn to use aggregate functions like count, sum, min, max, and avg. Compare count(*) and count(column), handle nulls, group by, and use number * 1.0 to avoid integer division.

Group by produces one row per unique value combination, enabling aggregate calculations like count and sum on fields such as job title and gender.

Apply the having clause after group by to filter aggregated groups, such as total sales by product, and distinguish where from having for correct SQL query ordering.

Discover how to use the string_agg function in SQL Server 2017+ to concatenate multiple subcategories into a single string per category, with a delimiter, using a join and group by.

Master precise prompt design to guide ai tools like ChatGPT and Claude in sql. Craft clear prompts that specify data, column names, constraints, and output structure for accurate results.

Apply few-shot prompting to teach with example input-output pairs and create a reusable task template. See how unpivoting in SQL Server transforms wide amount columns into rows for analysis.

Be bold and experimental with AI, mastering prompt engineering through iterative questioning, fresh conversations, and trying different assistants to improve responses for data analysis tasks.

Structure prompts with XML style tags to create clear, hierarchical delimiters that separate context, data, instructions, and output format for better AI responses.

Balance concise prompts with room for AI insight, starting with broad questions to analyze monthly sales data in sql server without overfitting your requests.

Explore the role reversal prompting strategy, where the AI asks one clarifying question at a time to craft a monthly revenue by product category SQL query in Adventureworks.