Master SQL Basics in 7 Days! | Learn SQL Server (SSMS)

Set up your SQL environment by creating, using, and deleting databases, data types, and manage tables with creating, dropping, inserting data, and handling nulls, primary keys, defaults, and unique constraints.

Connect to your on premise server and create databases with Object Explorer or using the create database syntax, practicing with practice db and practice db two.

Learn how to drop databases in SQL Server using Object Explorer and drop database syntax, ensure connections are closed, and understand common permission considerations.

Create and connect to a database in SSMS, using the 'use' syntax to switch to the practice DB, and comment out unused code to avoid accidental drops.

Explore core SQL Server data types such as int, varchar, decimal, and date, and learn how choosing the right type improves data accuracy, storage efficiency, and performance.

Create your table named employee with columns department name (varchar 100), birth date (date), and salary (decimal(10,2)); use shift alt down for edits, run with F5, and note dbo schema.

Learn to drop a table in SQL Server using both the object explorer and the drop table syntax, and use IntelliSense tips plus refreshing the local cache.

Master inserting data into an employee table with insert into and values, inspect results with select star from, and explore text, int, and decimal data types.

Insert rows to show null values as missing information in a database table, and enforce data integrity with birth date not null.

Learn how to enforce unique values in SQL Server by applying a unique constraint on the employee ID, preventing duplicate keys and enabling distinct records even when other fields match.

Reveal how primary keys combine unique and not null to provide a unique identifier for employee IDs, and demonstrate defining, naming, and enforcing this constraint in SQL Server.

Drop the table, create a new table with not null birth date, a primary key on employee id, and apply a default constraint to salary to zero.

Day three teaches querying data with select statements, where clauses, and order by, building on day two’s create, drop, and insert syntax, and covering aggregate functions, grouping, and advanced filtering.

Learn to query data with the select statement, limit rows with top n, and specify columns instead of using star, illustrated through practicing with a recreated employee table.

Explore how the distinct function returns unique values in a select query using name, birth date, and id examples to show when rows are collapsed.

Explore the four arithmetic operators—addition, subtraction, multiplication, and division—and see how they compute quarterly and monthly salaries from the employee table.

Master SQL basics by using alias names with the as syntax to rename computed columns like addition, subtraction, multiplication, and monthly or quarterly salary for clearer query output.

Discover how the where clause filters data in SQL Server using operators =, >, <, >=, <=, and <>. See examples with department and salary to return specific rows.

Learn to use and, or, in, not in, and between in where clauses of SQL to filter data, with examples on department, salary, birth dates, and handling null values.

Explore like and not like in the where clause, using percent and underscore wildcards to match starts with, ends with, contains patterns, with null and is not null considerations.

Sort query results with order by, choosing ascending or descending for names and salaries. Identify the SQL written order: select, from, where, then order by.

Apply the count aggregate function to tally all rows, count non-null values, and count distinct names with aliases. Explore how nulls affect counts and preview sum, min, max, and average.

Learn how group by changes data granularity by counting per employee name. See the SQL order of operations with where, group by, and order by, including column numbers.

Explore aggregate functions by calculating total salary, minimum, and maximum values, and observe how grouping by department changes granularity in SQL.

Learn how the having clause filters data after a group by and aggregation, using aggregates like sum, min, max, and average, and how it differs from the where clause.

Learn to use case statements to add conditional logic in SQL queries, creating high, medium, and low salary groups in select, where, order by, and having clauses.

Update and delete data safely, distinguish delete from truncate, and alter table structures; create tables with select into, add conditions, and use inner queries, temporary tables, and common table expressions.

Update data in SQL by crafting update statements with set clauses, use where clauses to target specific rows, and modify multiple rows or columns, such as salary and department.

Learn how to truncate a table to remove all rows without deleting the table, preserving the structure, including columns, primary key, and default constraints.

Learn how to delete data in SQL Server using delete from, including deleting a specific row with a where clause, and compare it with truncate table behavior.

Learn how to alter a table in sql server to add, drop, and modify columns, adjust constraints, and manage primary and unique keys.

Learn how to create a new table with select into from existing data, aggregate department salaries, and count employees, using inserts, aliases, and null handling in SQL Server.

Discover inner queries (subqueries), temporary tables, and common table expressions (CTEs) to break complex SQL into manageable steps, with practical SSMS examples and a focus on readability.

Learn how to use temporary tables in SQL Server to store intermediate results, compare temporary tables with inner queries, and decide when to drop or reuse them.

Explore common table expressions (CTEs) to break complex SQL into readable chunks, compare them with inner queries and temporary tables, and use the with syntax for efficient querying.

Build the Summit Sporting Goods database in SSMS, mapping tables and relationships, and master joins—inner, left, right, and full outer—to query a real-world data model.

Explore how the Summit Sporting Goods database powers data driven decision making, using six tables: transactions, products, stores, employees, customers, and a date table—to uncover trends and optimize operations.

Examine the Summit Sporting Goods data dictionary covering six tables, five dimensions and a fact table, with column data types and definitions, including flags such as refund and membership discount.

Master relational databases by mapping entities to tables, primary and foreign keys, and star schemas. Build a Summit Sporting Goods database with a central fact table and dimension tables.

Create the summit sporting goods database and five dimension tables—dim store, dim product, dim customer, dim employee, and dim date—using drop-if-exists checks, not-null columns, and primary keys.

Create the summit fact table linked to five dimension tables via date_id, store_id, employee_id, customer_id, and product_id, then load in batches and use a refund flag to adjust revenue.

Master foreign keys to connect data across tables by linking to primary keys and enabling one to one or one to many relationships between fact and dimension tables for integrity.

Create and enforce relationships in the summit database by adding primary and foreign keys to the fact table and dimensions in SSMS, visualize with an ERD, and prepare for joins.

Troubleshoot loading the summit sporting goods database by restoring from a backup file in SQL Server Management Studio, confirming primary and foreign keys, and creating an entity relationship diagram.

Learn how to join data tables in SQL by combining rows from two tables using inner, left, right, and full outer joins, with examples from customers and orders.

Explore inner joins in SQL Server using the Summit Sporting Goods database, joining the fact transactions to dim employee, alias tables, and group by job title to analyze gross revenue.

Learn how to use left joins in SQL Server, mirroring inner join syntax, and verify results by comparing counts to reveal null job titles for online orders.

Understand how a right join returns all rows from the right table (dim employee) and matches revenue from the left table, highlighting nulls for non-sales roles.

Master full outer joins while reviewing inner, left, and right joins across dim employee and fact transaction data, noting nulls and online revenue.

Explore SQL functions to manipulate, format, and analyze data, including string, null handling, numeric conversion, date calculations, and rollout function for multi-level summaries in the Summit Sporting Goods database.

Use the concat function to combine two or more strings in SQL, such as city and state with a comma and a space, and alias the result as location.

Master the replace function in SQL Server to substitute a specific string with another. See practical examples on literals and table columns, including replacing not applicable with online.

Master the is null function to replace null values with a chosen replacement, using simple syntax on expressions or columns such as email, producing no value or no email provided.

Learn to use the coalesce function in SQL to return the first non-null value from multiple expressions, with practical examples of email columns, table alterations, and conditional updates.

Learn how the nullif function compares two expressions and returns null when they are equal, otherwise returns the first expression, with practical sql examples.

Explore the round function in SQL Server to round or truncate numeric values to a set of decimals, using round(numeric_expression, length, function) with 0 for round and 1 for truncate.

Master how cast and convert change data types in SQL server output, compare their syntax, and format numbers and dates using style options in result sets.

Explore how to format numbers, currencies, percentages, and dates with the SQL Server format function, apply custom two-decimal styles, and use optional culture settings to tailor output.

Apply the upper and lower functions to convert a first name to uppercase and a last name to lowercase, using dim_customer and aliasing as uppercase and lowercase.

Learn how to use the left and right string functions in SQL, with syntax, examples, and practical applications like building initials and usernames from names.

Master the trim function to remove leading and trailing spaces and specific characters in SQL Server queries. Learn syntax with expressions and examples, including removing dashes, and compare to Excel.

Explore the Len function in SQL Server, learning how Len(expression) returns the number of characters in a string excluding trailing spaces, with an example using 'hello' that yields 5.

Learn how to use the date add function to add or subtract intervals to dates, and compute a refund date by adding 90 days to a purchase date.

Master the date diff function in sql to compute differences between two dates in days, months, or years, using start and end dates with practical examples.

Learn how the roll up operator extends group by to create subtotals and grand totals across region and store hierarchies, handling nulls with case logic.

Practice ten practice SQL questions on day seven to solidify your understanding using the Summit Sporting Goods database, review the quizzes, and prepare for the final exam.

Practice counting rows and unique transactions in the fact transaction table using count and count(distinct), apply column aliases, format results with comma separators, and filter out refunded records.

Count total employees, filter current ones with the current flag, then use a case statement to classify part-time versus full-time and identify the job title with the most staff.

Add a full name column to the dim customer table, populate by concatenating first name, middle initial, and last name with spaces, then update and drop the column.

Explain counting distinct products from the product column, display unique counts by category using group by, and include a rollup grand total (315) to summarize results.

Learn to compute net revenue from price minus cost, apply member discounts, sum results, and format as currency, then determine a 90-day return date with dateadd, convert, and format.

Learn how to use update statements to reformat categories, rename soccer to football for international markets, and concatenate first and last names with a space using sql.

Learn to identify the store with the most gross revenue, format it as currency, and show online store revenue by month and week using ssms joins and grouping.

Explain the distinct count of employees who purchased and the online store's gross revenue by state using dim customer and dim store with a fact table, applying left joins.

Identify the current chief marketing officer from the dim employee table, add a season column to dim product, and implement a case-based update mapping each category to a season.

Learn to query a customer database by joining dim and fact tables, filter online purchases, and use group by and having to identify college students who spent over 1000.