Tiramisu 298 7 inches
Introduction to Information Systems
Learning Outcomes
Lab 07: Database using Microsoft Access II
Copyright By PowCoder代写 加微信 powcoder
1. Experience how to create and modify tables for data storage using SQL.
2. Experience how to create and modify queries for data retrieval using SQL.
Introduction
SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS). It is particularly useful in handling structured data where there are relations between different entities/variable of the data.
SQL: A language for “interacting” with database – Add/Update/Delete certain records in database – Select certain records in database
In this laboratory, you will learn some key SQL commands: INSERT, UPDATE, DELETE and SELECT.
1. INSERT Command
INSERT INTO table (field1, field2, …) VALUES (value1, value2, …)
Example: Add new product record in ProductInfo table
1. Open the file lab07_SQL_DB.accdb which can be downloaded from CANVAS.
2. There is one table named ProductInfo. Double click ProductInfo to review the table content.
3. We would like to add a new product record in the ProductInfo table using SQL.
4. Click CreateQuery Design, a new Query1 will appear.
5. Close the Show Table window.
6. Click View SQL View
7. Details of the new product:
PRO_ID PRO_Name PRO_Price PRO_Size PRO_Weight
100041 6
100041 6
23587653 New 23587638 Territories
Introduction to Information Systems
8. Copy the below coding under the SQL View
INSERT INTO ProductInfo VALUES (300015, “Tiramisu”, 298, “7 inches”, “1.9 lbs”)
9. Click Run button and you will see the below warning:
10. Click Yes and Refresh All, you will see the new record shown in the ProductInfo table.
Task 1: Insert new record for new Member
Details of the new Member:
MEM_ID MEM_Last_Name MEM_First_Name MEM_Month MEM_Contact_No
How can we use INSERT command to add new member to MemberInfo table?
2. UPDATE Command
UPDATE table SET newvalue WHERE criteria
Example: Update member record in MemberInfo table
1. Click ViewSQL View
2. Details of the member need to update:
MEM_ID MEM_Last_Name MEM_First_Name MEM_Month MEM_Contact_No
3. Copy the below coding under the SQL view
UPDATE MemberInfo SET MEM_Contact_No = 23587638 WHERE MEM_ID = 100041
4. Click Run button and you will see the below warning:
MEM_Regions
MEM_Regions
100041 6
* = all fields
Introduction to Information Systems
5. Click Yes and you will see the updated record shown in the MemberInfo table.
Task 2: Update product price for each product in ProductInfo table
As the cost of raw materials increased a lot, we would like to increase the selling price by $15 for each product. How can we use UPDATE command to update new selling price in ProductInfo table?
3. Delete Command
DELETE field FROM table WHERE criteria
Example: Delete member record in MemberInfo table
1. Click ViewSQL View
2. Details of the member need to delete:
MEM_ID MEM_Last_Name MEM_First_Name MEM_Month MEM_Contact_No MEM_Regions
3. Copy the below coding under the SQL view
DELETE * FROM MemberInfo WHERE MEM_ID = 100041
4. Click Run button and you will see the below warning:
5. Click Yes and you will see the member record is deleted in the MemberInfo table.
4. SELECT Command
SELECT field FROM table WHERE criteria
Example: Find out which members have a contact number starting with 9
1. Click View SQL View
2. Copy the below coding under the SQL view
SELECT * FROM MemberInfo WHERE MEM_Contact_No Like “9*” OR
SELECT MEM_Last_Name, MEM_First_Name, MEM_Contact_No FROM MemberInfo WHERE MEM_Contact_No Like “9*”
SELECT MEM_Last_Name, MEM_First_Name, PRO_Name, PRO_Price, PUR_Quantity FROM MemberInfo, ProductInfo, PurchaseRecord
WHERE PRO_ID = PUR_PRO_ID AND PUR_MEM_ID = MEM_ID
AND PRO_Price > 200 AND PUR_Quantity > 1
FUNCTION = {COUNT, MAX, MIN, AVG, SUM}
Introduction to Information Systems
3. Click Run button and you will see the below result:
Example: Retrieve Purchase Information (Member Names, Product Name, Price and Quantity) of those who had purchase product that is priced over 200 and purchase amount greater than 1
1. Click View SQL View
2. Copy the below coding under the SQL view
3. Click Run button and you will see the expected result.
5. SELECT Command (Aggregate)
SELECT FUNCTION(field) FROM table WHERE criteria [GROUP BY field]
Example: Find out how many members who born in March
1. Click View SQL View
2. Copy the below coding under the SQL view
SELECT COUNT(*) FROM MemberInfo WHERE MEM_MONTH = 3
3. You may revise the SQL so that it has a meaningful field name for COUNT.
SELECT COUNT(*) AS MarchMember FROM MemberInfo WHERE MEM_MONTH = 3
Example: Find out the distribution of Birthday Month for all Member
1. Click ViewSQL View
2. Copy the below coding under the SQL view
SELECT MEM_Month, COUNT(*) AS NoOfMember FROM MemberInfo GROUP BY MEM_Month
What will happen if you run the below SQL? ERROR WARNING MESSAGE
SELECT MEM_Month, COUNT(*) AS NoOfMember FROM MemberInfo
SELECT MEM_ID, MAX(PRO_Price) AS MaxPrice
FROM MemberInfo, ProductInfo, PurchaseRecord
WHERE PRO_ID = PUR_PRO_ID AND PUR_MEM_ID = MEM_ID GROUP BY MEM_ID
SELECT ________, ___________, _________, ________, ____________ FROM _____________________________________
WHERE ____________________________________
AND DatePart(“m”, PUR_Date) = 2
SELECT ________, ___________, _________, ________, ____________ FROM _____________________________________
WHERE ____________________________________
AND ________________________
AND DatePart(“m”, PUR_Date) = _______
Introduction to Information Systems
Example: Find out the most expensive product for each Member has bought
1. Click ViewSQL View
2. Copy the below coding under the SQL view
Task 3: Find out the distribution of Regions for all Member
You may fill in the blanks below:
SELECT ___________ FROM ___________ GROUP BY __________
Task 4: Retrieve Purchase Record (Member Name, Purchase Date, Product ID, Quantity) of those who bought product in February
You may fill in the blanks below:
Task 5: Retrieve which customer has bought product in their Birthday Month
You may fill in the blanks below:
Expected Result:
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com