Microsoft Access SQL: SQL from Absolute Beginners To Expert

Master simple select statements by pulling all columns with select * from a specific table, and learn to end queries with semicolons; also practice selecting only needed columns.

Select distinct returns unique values from a table, such as distinct business_phone or job_title, and note that null values are included in the results, thus duplicates are removed.

Learn to use switch with select in Microsoft Access by mapping test expressions to values, using aliases in queries to classify order dates into current, future, or previous orders.

Master the where clause to filter records in Microsoft Access SQL, retrieving Seattle for a given company, and learn that the select list need not include every filtered column.

Master Microsoft Access SQL comparison operators, including equal to, not equal to, greater than, greater than or equal to, less than, and less than or equal to.

Explore how to apply arithmetic operators in Microsoft Access SQL, including plus, minus, multiplication, and division, within select statements and workloads, using reorder level and target level as examples.

Explore how to use logical operators in Access SQL, including and, or, and not, to filter orders by date, ship state, and payment type.

Learn how to use the concatenation operator to join strings with ampersand or plus, add spaces between names, and understand how null values affect results.

Identify null values in Access SQL using the is nil operator, test missing data with order_id is null, and view results in data sheet view.

Learn how to use the between and not between operators in Microsoft Access SQL to filter records within a date range, handle timestamp values, and ensure inclusive ranges in queries.

Master sql 'in' and 'not in' operators to filter records by a fixed value set, using invoices and orders examples to select ships to Las Vegas, New York, or Portland.

Learn to use the like operator in Microsoft Access SQL to filter records by patterns with wildcards, such as starting with s, containing sales, or ending with manager.

Learn the like operator with character classes in square brackets to match a single character, filtering ship state starts with N, C, or W and ends with V or E.

Master how to match characters in Microsoft Access SQL using square bracket ranges with hyphen notation, and star or hash symbols with Like operator for product codes and supplier names.

Use the negation character inside square brackets with an exclamation mark to exclude letters in a like pattern. For example, filter suppliers not starting with certain letters.

Learn how to use the question mark with the like operator to match a single character in sql, and compare it with the star wildcard.

Use the order by clause to sort query results either ascending or descending, with ascending as default, and apply multi-column sorting in the specified column order.

Sort by column number with order by n, where n is the nth select clause column. Default sorting is ascending; prefer using column names for readability to prevent surprises.

Use the top keyword in Microsoft Access SQL to limit results, with examples like top four orders and top 5%, and sort by order date or order id.

Learn how the group by clause groups records by selected columns, often with aggregate functions, and ensure all selected columns appear in group by unless using aggregates.

Learn to use the group by clause with sum to compute total shipping fees per order date in Microsoft Access SQL, including where and order by syntax.

Master essential string functions in SQL, including lowercase, left, right, trim, ltrim, rtrim, length (alien), mid (made), replace, and compare (steer comp).

Explore SQL string functions to change case and extract fixed-length substrings, with examples converting company names to lowercase or uppercase and using left or right nine-character cuts.

Learn to remove leading, trailing, and both-side spaces with ltrim, rtrim, and trim in Access sql, and convert numeric ids to strings with the str function.

Learn to compare strings, measure their length, extract substrings with mid, and replace text in Microsoft Access SQL through practical examples with names and company strings.

Explore numerical functions in Microsoft Access SQL, including sum, average, count, integer value, max, and min, with examples of how each aggregates data in a table.

Explore numerical aggregate functions in Microsoft Access SQL, including average, max, min, and count, with group by and order by using shipping fee examples.

Use the date function in Microsoft Access SQL to fetch the current system date, with examples in select, insert, delete, update, and procedure, and alias as current system date.

Explore how to use dateadd to add years, quarters, months, days, hours, minutes, and seconds to a date field, demonstrated on an invoice date column.

learn to subtract dates in microsoft access sql using the date diff function to measure differences in years, quarters, or months.

Learn to extract parts of a date in Microsoft Access SQL using the date part function, returning integers for year, month, week, hour, minutes, seconds, and quarters.

Extract day from a date field with the day function in Access SQL. Use Day([order date]) or DatePart on the orders table and run the query to view the day.

Extract hour, minute, and second values from date fields with minute and second functions in Microsoft Access SQL, using an invoices table example to demonstrate practical queries.

Learn to retrieve the current system date and time with the now and time functions in SQL, and contrast with the date function that returns only the date.

Extract year and month from date fields using year and month functions in Access SQL. Practice extracting month from invoice date and year from employ date in sample tables.

Learn how the date value function in Microsoft Access SQL extracts a date from strings and converts formats like 921 and 21/9/2020 into date values.

Learn to format dates using the format function in Microsoft Access SQL, applying short date, long date, and custom patterns to fields like ship date in the artists table.

Use isdate function in Microsoft Access SQL to check whether an expression can convert to a date, returning minus one for true and zero for false, with examples from orders.

Learn to use the is null function in Microsoft Access SQL to identify null values (true = minus one), distinguish them from blanks, and verify nulls in order date.

Use the isNumeric function in Microsoft Access SQL to check if an expression is numeric, returning minus one for true and zero for false for currency values.

Learn to use the current user function in Microsoft Access SQL to obtain the current database username and verify the logged-in user, such as admin.

Learn how the having clause filters aggregated results after group by, using sums from an aggregate function by order date. See its relation to where and order by.

Explore data types in Microsoft Access, including counter as autonomous field for primary key, text and long text, byte, number, numeric, date time, currency, binary, bit, timestamp, hyperlink, and attachment.

Learn ms access naming conventions to prevent errors: avoid spaces and special characters, use consistent camel or title case, and prefixes for tables, queries, forms, macros, and modules, with peaky.

Create a simple table in Microsoft Access using the create table statement with columns like store id (auto number), store name, phone, email, address, city, state, and zipcode.

Learn to create a table with constraints to enforce data validity and accuracy, applying normal, unique, primary key, and foreign key rules at column or table level using standard syntax.

Understand how the not null constraint enforces non-null values on table columns, with an example using a PBL Stores table to keep store ID and store name non-null.

Discover how the unique constraint enforces unique values in a column, with create table syntax, column definitions, and named constraints such as store uk.

Learn how a primary key uniquely identifies records, enforces not null values, and supports entity integrity by linking to foreign keys, including composite primary keys across multiple columns.

Master how a foreign key in a child table links to a parent table's primary key, and declare foreign keys using the syntax foreign key (cols) references parent table (cols).

Create a parent table items with item_id as primary key, item_quantity, and item_load_status as yes/no, then link a child table product_details through foreign key to items to establish one-to-many relationship.

Understand how a foreign key in the child table references a primary-key column, and why that column must be unique; resolve with a unique constraint or unique index.

Discover how a unique index enforces no duplicate values in a column, and learn the syntax to create one on a table using a specified index name and columns.

Learn how to verify a unique index in Access by using a query to create it and checking the table design view shows yes with no duplicates.

Create an index on table items two using the item id column, then compare with a unique index to see duplicates are allowed.

Learn to insert records in Microsoft Access SQL by using the insert into statement, listing the table, columns, and values, and note that an auto-number field auto-increments when omitted.

Insert records into a constrained table by respecting not null and primary key requirements, and observe how the priority field is assigned for valid rows.

Insert the parent items record first to satisfy the foreign key constraint. Then insert the corresponding product details record, allowing Access to auto-assign the product id.

Learn to update an existing record in Microsoft Access SQL using the update statement, set clauses, and optional where conditions; update specific rows or all records across tables.

learn how to use a subcommittee within a main query to obtain the second maximum unit price from order details.

Learn to use subqueries in Microsoft Access SQL to filter order details by unit price greater than the average list price from the products table, returning order IDs.