Objective: Show all columns and rows from a table.
SQL Example 1: Start with the basic example below.
Select *
From T01_VacationList
Objective: Filter rows and show all columns.
SQL Example 2: Filter and show all columns. Also use “V” to write SQL code more compactly.
Select *
From T01_VacationList as V
Where V.Category = ‘Equipment’
Note:
Access 2010 uses single quote ‘ ‘ in SQL code.
Access 2007 uses double quote ” ” in SQL code.
The filter condition is defined by “Where”
Where V.Category = ‘Equipment’
- Select ‘*’ means that all the attributes will be displayed.
- “From” indicates the table which table is used.
- “As” indicates the table is given the shorthand name b
- To execute the query, click the RUN symbol “!” from SQL view.
Objective: Filter rows and show selected columns.
SQL Example 3: Filter and show selected columns.
Select Description, [Associated Price]
From T01_VacationList as V
Where V.Category = ‘Equipment’
Objective: Invert your filter using the “Not” operator.
SQL Example 4: Using “Not” operator.
Select *
From T01_VacationList as V
Where Not( V.Category = ‘Equipment’)
Objective: Use mathematical formulas to add a math driven column.
SQL Example 5: Find the Subtotal.
Select V.*, [Associated Price] * quantity as Subtotal
From T01_VacationList as V
Objective: Use mathematical formulas to add a math driven column AND the sum.
SQL Example 6: Find the Subtotal.
Select sum([Associated Price] * quantity) as Subtotal
From T01_VacationList as V
Try using OR, AND, and ” Like ‘a*’ ” with the “Where” parameter.
Objective: Using the UNION query to consolidate similar tables or queries.
First you will need to create and save two queries named q1 and q1.
SQL Union Example: Create a union of q1 and q2.
Select [ID], [Description], [Category], [Associated Price]
From q1Union Select [ID], [Description], [Category], [Associated Price]
From q2;
Decipher the following query:
SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 01]
UNION SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 02]
UNION SELECT [First Name1] AS [First Name],[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 03]
UNION SELECT [First Name1] AS [First Name] ,[Start Time1] AS [Start Time],[End Time1] AS [End Time],[Start Date], ([End Time]-[Start Time])*24 AS [Hours Available]
FROM [Form Number 04];
Below are the files for the Day 2 learning activities: