101 Practice SQL Questions: Basic to Advanced

Learn to explore the Adventure Works database using system views in Microsoft SQL Server Management Studio, focusing on schemas, tables, columns, and data types before questions one through ten.

Count and categorize object types in the Adventure Works database using the system objects table, grouping by type description and ordering results by descending counts.

Count schemas, tables, and columns in Adventure Works using system tables and information schema while excluding views, list them by schema, table, and column names for Part A and B.

Explain how to manage check constraints in SQL by creating an edited Adventure Works database, querying system views, constructing a constraint table, and classifying constraints as column or table level.

Examine foreign key to primary key relationships in the Adventure Works database, focusing on the currency rate ID linking currency rate and cells order header tables using a build script.

Learn to create and populate a table named table relationships in the edited adventure works database, count and identify duplicate foreign keys, and count distinct keys including business entity ID.

Join system default constraints to system tables, schemas, and columns to reveal 152 defaults, showing constraint names, tables, columns, and their definitions and default values.

Learn to write scripts that find columns containing rate and tables containing history using system tables and like queries in question seven of the 101 practice sql questions.

Explore data type usage in the Adventure Works database using information_schema.columns to count by type, then categorize into character, numeric, date, or null with a case statement.

Use the information_schema view_column_usage to list each view and count distinct tables, grouping by view name and ordering by table count, highlighting inner and left outer joins in Adventure Works.

Explore the Adventure Works database by constructing a script to return table names, column names, and values where class = 1, then retrieve definitions for the person table columns.

Master practice sql reasoning by counting employees, filtering active records, and counting distinct job titles through joins of employee and person tables, using conditions on person type for sales roles.

Identify the CEO Ken Sanchez from the employee and person tables, concatenate first and last names, find the hire date, and list direct reports with names and titles.

Learn to join the person and employee tables and use the employee department history to determine John Evans's job title (production technician) and department (production).

Explore sql practice on the purchasing vendor table to identify the highest credit rating, cast ratings to decimals, classify vendors as preferred or not, and count active vendors.

Question 15 demonstrates how to compute ages with date diff, cast to decimal, format to one decimal, and apply ceiling and floor to round ages, then average by organizational level.

Analyze the Adventure Works product table to count sold products, identify those actively sold, and classify items as purchased or made in-house using the make flag.

Join the product and sales order detail tables on product id, classify items by make flag using a case statement, and compute currency-formatted sums, counts, and average line totals.

Analyze the production transaction history and archive, union data, cast datetime to date, and label types as work order, sales order, or purchase order for first and last dates.

Question 19 builds on question 18 by computing the min and max order dates from the sales order header, converting to date datatype, and confirming alignment with the transaction history.

Identify tables and dates in Adventure Works, format and compare first and last dates across purchase order header and work order, and reconcile with transaction history and sales order headers.

Experience joining country and state data with tax rates in Adventure Works, using left joins to preserve rows, reveal duplicates, and analyze tax rates in Alberta, Ontario, and Quebec.

This lecture explains counting individual retail customers by country in Adventure Works, preserving granularity with distinct business entity IDs, and using a dynamic inner query for percent of total.

Explains computing percent of total by using a local float variable as the denominator, derived from the inner query and total retail customers, and compares correct and incorrect approaches.

Explore calculating the difference between subtotal and total due using the sales order header, and verify line totals in the detail by unit price, discounts, and quantity.

Compute net revenue by subtracting the standard cost from the list price. Identify the highest margins by sorting product margins in descending order.

Explore grouping Mountain 100 bikes by product model ID and joining product and sales tables to analyze start dates, end dates, and special offers.

Explore question 27 of 101 practice sql questions: determine if product model 19 is still in stock by analyzing sell end date, discontinuation status, and inventory through joins and aggregation.

Explore question 28 by extracting distinct cell reasons, counting orders per reason through table joins, and ranking reasons to reveal price as the most common purchase driver.

Learn to count ids by the number of cells reasons using a cte and left joins, and compare join types to categorize ids with zero to three reasons.

Identify customers who left a review by joining the product review table with product, sales order, and person data to trace purchases and review timing.

Discover how to locate Ken Sanchez's email by joining person and email address tables and updating it by ID. Explore open transactions, rollbacks, and commit statements in upcoming questions.

Correct Ken Sanchez's email within a begin transaction, verify open transactions, and, if needed, rollback to restore the original email before committing the final update in the Adventure Works database.

Wrap an update in a transaction with begin transaction and a rowcount check to commit or rollback; test with selects to safely update one email for business entity id 1.

Learn to rank employees by hire date using the rank function, create a seniority column, and compute days, months, and years employed with date diff.

Learn to use temp tables, case statements, and aggregated queries to bucket employees by years employed, count groups, and compare average vacation and sick hours.

Learn to build a regional sales report by joining territory, header, and customer data, sum total due, concatenate names, and rank top customers per region with row_number.

Show top 25 customers per region using a temp table and row_num; roll up to region level to average total due. Remove format to avoid text when averaging.

Compute total freight from the sales order header, calculate average freight per sales id, freight by year, and a running total by year using a subquery and over clause.

Explain question 39 by extending question 38 to count completed months per year and compute average freight per completed month using a running total. Project 2014 with a linear estimate.

Learn to analyze freight costs by month with ship date, including year and month breakdowns, a monthly average rate, and a year-to-date running total using partition by year.

Build a SQL report listing sales order ID (31,465 rows) with customer name, spelled-out person type using case statements, salesperson or 'no salesperson', order date, and quantity, using joins.

Demonstrates building a sentence in the sales order header using a common table expression, then updating 31,000 rows by concatenating fields, casting types, and formatting dates for a usable comment.

Use inner queries to count salespeople meeting their year-to-date quota and to count those with year-to-date sales above the average, as shown with the salesperson table.

Create, drop, and execute a stored procedure named cells report year to date to compute commission as sales year to date times commission percent, with currency and percent formatting and aliases.

Demonstrates adding a single parameter to a stored procedure to filter results by a business entity id. The video shows executing with a specific id and dropping the procedure.

Counts vendor credit ratings, groups by credit rating, and clarifies results with a case statement before comparing to the choose function; analyzes the preferred vendor status.

Explore how case, choose, and if yield the same results for counting vendors by approved versus not approved based on credit rating, highlighting case readability.

Add a credit rating description column to purchasing.vendor with varchar(100), populate it using a case/choose expression via an inner join on business_entity_id, then update and drop the column.

Explore using information_schema.columns to determine the vendor name data type, verify a user defined data type alias, and count user defined data types in Adventure Works as groundwork for question 50.

Learn how to create and use a user defined data type named status as tinyint not null, alter a table column to this type, and drop it when unused.

Explain how to estimate next year's storage for Adventure Works by calculating per-table space from allocation units, joining system partitions and tables to get table name, rows, and kilobyte usage.

Explore calculating storage needs by flagging tables with under 10% unused space, handling divide-by-zero, casting to decimal, and exporting results to a shared view for easy access.

Derive a per day kilobyte value from current usage, project the next year's storage over 365 days, and update the view to reflect the projected kilobytes.

Learn to compute the average total due on July 4th for Adventure Works orders by filtering July 4th dates, summing totals, and dividing by distinct dates using datepart.

Transform the average July 4th total into per year totals, identify peak year, add day of week with date name, and store results in a temp table for SQL analysis.

Explore calculating average total due by day of week across the database using temp tables, grouping by weekday, and comparing July 4th sales to weekday averages with a case-based flag.

Learn to drop temp tables safely in automated daily workflows by using 'drop table if exists' and an object_id check to avoid errors. The video explains two practical approaches.

Learn to use the pivot function to recreate a year-by-year sales view from Adventure Works data, pivoting fiscal years 2012–2014, and format subtotals as currency.

Learn to create a 2013 sales by salesperson table using a subquery and pivot, displaying road, mountain, and touring bikes totals formatted as currency and ordered by their combined total.

Master pivot and unpivot in SQL to transform survey data from rows to columns by survey id, question, and response for clear visuals.

Master building a sales report by joining salesperson, person, and territory tables, using a left join for managers without territory, formatting year-to-date as currency, and ordering by descending year-to-date.

Rank salespeople by year-to-date sales using over and rank functions, then partition by territory to compare top performers within regions, and compute percent ranks for cross-compare.

Add four columns using lag and lead functions to a sales report, returning prior year-to-date values and business entity IDs, with null handling and currency formatting.

Create a view named Cells to house a script using lag and lead functions for a sales report, then alter the view to adjust lag while preserving base tables.

Explain how to identify employees who appear multiple times in the employee department history view by using group by and having, and show duplicates arise from department changes.

Learn to practice percent_rank by joining the employee view to pay history, resolve duplicates, and use row_number partitioned by business entity ID to return each employee’s most recent active rate.

Practice the percent_rank function with an over window, format results as a percent named percentile, and order by descending percentile to identify the highest pay rate.

In the question 68 video explanation, learn to compute department percentile for each employee using percent_rank with an over window partitioned by department and ordered by rate.

Explore quartiles and percentiles in sql using the dn tile over window function, applied to the whole company and by department, with practical ordering notes.

Count distinct order dates, calculate days between first and last using date diff, and illustrate a while loop to build a date calendar and find three missing dates.

Create usernames by stripping Adventure Works domain from emails, verify uniqueness; build a password from first name's two letters, last name's two letters, and first five of the new id.

Learn to compute username length with len, apply case when to pad usernames, and perform update, truncate, and drop table operations in a sql workflow using the adventure works database.

Join a chain of tables to build a 290-row employee report for holiday gifts. Include full name, job title, country, state, city, postal code, and address lines.

Convert all weights to a single unit using a case statement and a conversion factor, then query the product table to find the heaviest product in the adventure work database.

Demonstrates building a sql workflow to compute product inventory by joining product and inventory tables, aggregating quantity, applying case when logic, and creating flags with a temp table.

Demonstrates duplicating a view into a table, filtering by non-null first purchase date, and adding full name and age columns with joins and date calculations.

Explain how to fetch distinct marital status values, update them with a case statement, and fix truncation errors by altering column size and updating gender (M/F) in Adventure Works.

Pull the average age by yearly income and order by income. Then identify the highest average total purchases year to date by income, education, and marital status in Adventure Works.

analyze scrap data from the work order and scrap reason tables by joining on scrap reason id, group by scrap reason, and roll up to reveal paint process failed causes.

Fix three errors in the SQL script: add an else to the case statement, include a group by, and switch to a left join to get all six person types.

Calculate days between order date and ship date with date diff, then count unique sales order ids to assess shipments before or after seven days.

Declare local variables for the top sales person and current year, then identify the highest sales year to date and show the top ten products by subtotal via joins.

This video explains how to use declared variables and a rank window function to find the third best salesperson's top business entity ID, updating only the declare statement.

Count unique credit card numbers in the sales schema credit card table and show valid versus expired cards by person type using the person credit card linkage.

Review question 84 to identify 3149 expired cards and learn inner and left joins, coalesce for full names, and expiration date formatting to year-month-day.

Explore question 86 by cross joining departments and shifts to generate department–shift pairs, then use a subquery to filter valid combinations and format start and end times.

Use row_number with a left join on price history and an inner query to count each product's price changes, partitioned by product id and ordered by start date.

Demonstrate a multi-table join in the Adventure Works database to identify shipments damaged by a Beverly Hills trucking route, filtering ship date March–April 2014 and state, with 38 results.

Create a SQL report listing all products with documentation using left joins and a temp table, handling no file on record and computing average revisions by owner.

rebuild a 290-row employee table by department group and shift using SQL joins, with day, evening, and night counts, and a roll up producing a grand total row.