Answer the questions below by using the given file . A database called INSS300.accdb is
Linked. This file should be downloaded, renamed so your name is in the title somewhere, and edited. Please make sure you submit that single file. Do not export queries into separate subfiles, these will be unreadable to me.
Note, PK = primary key field, FK = foreign key field.
Also note that phone numbers are saved as text fields not numbers – this is because we want to keep leading zeros and do not want to do arithmetic on them.
Using this database, write SQL statements for the questions given below.
Answers should be saved inside the Access file as separate queries.
Go to the Create menu
Choose Query design
on the new QueryDesign tab that appears (it will be colored red), select the left most button to get to SQL mode. This is where you can enter you SQL statement.
Test your statement with the Run ! button
use the View button on the left hand side to change the view back to SQL. Repeat until satisfied with your answer.
the query will automatically appear in the left menu under Queries – make sure to save it / rename it with an appropriate name (Questions 1 etc).
The database has 3 tables: Customers, Orders, and Shippers, with the following fields:
Customers
CustomerID: integer (PK)
Company: short text
LastName: short text
FirstName: short text
JobTitle: short text
BusinessPhone: short text
FaxNumber: short text
Address: long text
City: short text
State: short text
Zipcode: short text
State: short text
Country: short text
Orders
OrderID: integer (PK)
ShipperID: integer (FK)
CustomerID: integer (FK)
OrderDate: date
ShipDate: date
ShipName: text
ShipAddress: text
ShipCity: text
ShipZIP: text
ShipState: text
Payment Type: text
ShippingFee: currency
Shippers
ShipperID: integer (PK)
ShipperName: text
ShipperAccountNo: integer
Questions:
Write a query that returns the first and last name of every customer who lives in Portland
Write a query that returns the first and last name of every customer who placed an order that shipped after January 1st, 2019.
Write a query that returns all of the order details of any order that was paid for with Visa
Write a query that returns the order number and order date of every order that was shipped via UPS.
Write a query that returns the order number of any order placed by Christina Lee where she paid via Check
Write a query that returns the details of every customer, listed in alphabetical order by City.
Write a query that updates Dean Collin’s address to match the format of the other addresses (revised question).
Write a query that returns the lowest shipping fee of any order in Orders.
Write an SQL statement that creates a new table, Company, with the following fields: CompanyID (pk), CompanyName, and Country. Add three rows of data to each table, and ensure they are connected through keys.
Write an SQL statement that creates a new table, Product, with the following fields: ProductID (pk), CompanyID (fk) ProductName, and Price.
https://ubalt.instructure.com/courses/5436/files/645274?wrap=1
