Basic MS- Access SQL Commands that we have studied so far:
Refer to the tutorial for basic SQL commands here
Refer to the tutorial for Relationships and Insert into commands here:
Importing a .csv and .xls file into MS- Access
How to change the data types of columns in MS-Access Tables.
Queries:
Lets say we import the following table into MS- Access:
Below are the type of queries we did in class along with description for each.
Query Explanation
SELECT * FROM InventorySchool; Displays all rows for all columns:
SELECT ITEM FROM InventorySchool; Displays all rows only for Item column
SELECT PRICE, CAMPUS, displays three columns from
CATEGORY FROM InventorySchool; InventorySchool table
SELECT PRICE AS COST FROM Displays all rows only for Price column
InventorySchool; from InventorySchool table. In the
output, Price column will be named as
COST
SOME OTHER QUERIES THAT WE HAVE DONE SO FAR ARE ON
WHERE CLAUSE (FILTERING ROWS)
Query Explanation
SELECT * FROM InventorySchool Displays all rows and all columns but
WHERE PRICE>3; only with price values more than 3
SELECT ITEM FROM InventorySchool Displays only rows for Item column
WHERE QuantityInStock <50; where the quantityInStock is less than
50
SELECT ITEM, CATEGORY FROM Displays only item and category column
InventorySchool with rows that only are in Southeast or
WHERE CAMPUS=’Southeast’ or Sports campuses.
CAMPUS= ‘Sports’;
Please read tutorial for more
AGGREGATE FUNCTIONS
Query Explanation
SELECT SUM(Price) AS TotalPrice Displays the sum total of all the values
FROM InventorySchool; in the price column,. The result is
labeled as ‘TotalPrice’
SELECT MAX(QunatityInStock) AS Displays the maximum value of
MaxQuantity QuantityInStock, the result is labled as
FROM InventorySchool; MaxQuantity
SELECT MIN(Price) AS MinPrice Displays the minimum value of Price
FROM InventorySchool; column and the result is labled as
MinPrice
SELECT AVG(Price) AS AvgPrice Displays the Average value of the Price
FROM InventorySchool; column and the result is labled as
AvgPrice
SELECT COUNT(*) As Displays the total number of rows where
TotalPriceMoreThan20 the value of price column is more than
FROM InventorySchool 20
WHERE Price>20;
Using the orderBy
Order By is used to sort the data in a table.
Query Explanation
SELECT * Displays entire table but in descending
FROM InventorySchool order of the item names
ORDER BY Item;
SELECT Category, price Displays category and price columns
FROM InventorySchool only with rows that have price value
WHERE price<25 <25. Data is sorted in ascending order
ORDER BY category;; of the category column
Selecting unique names from a particular column
The DISTINCT keyword is used to show only unique and non repeated values from
a column.
Query Explanation
SELECT DISTINCT Campus Shows only the unique and non
FROM InventorySchool; repeated values from the campus
column of the InventorySchool table
SELECT DISTINCT StorageLocation Shows only the unique and non
FROM InventorySchool; repeated values from the
StorageLocation column of the
InventorySchool table
INSERTING INTO TABLE
Consider the following table in MS Access:
Query Explanation
INSERT INTO Colleges (ID,Name, Inserts a row of data into the table
Location) VALUES (‘9’, ‘Manipal Colleges.
University’, ‘Karnataka’);
