101 Practice SQL Questions: Basic to Advanced

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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 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.

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.

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

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.

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.

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.

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.

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 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.

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.

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.

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.