Microsoft Access Database Assignment: SQL Queries and Solutions

This document presents a comprehensive solution to a Microsoft Access database assignment. It includes the creation of two tables, PET_OWNER and PET, with appropriate data types and constraints. The solution provides SQL INSERT statements to populate the tables with sample data and demonstrates various SELECT queries to retrieve specific information. These queries include filtering data based on conditions (e.g., PetType='Cat'), ordering results (e.g., by OwnerLastName), using functions (e.g., LCASE, UCASE), counting records, and handling NULL values. The assignment covers fundamental database operations and query techniques in Microsoft Access.

Running head: MICROSOFT ACCESS DATABASE
Microsoft Access Database
Name of the Student
Name of the University
Author’s Note

1
MICROSOFT ACCESS DATABASE
Answer to Question 1:
CREATE TABLE PET_OWNER( OwnerID INT NOT NULL PRIMARY KEY,
OwnerLastName varchar (50),
OwnerFirstName varchar (50),
OwnerPhone varchar (15),
OwnerEmail varchar (255));
Answer to Question 2:
INSERT INTO PET_OWNER
VALUES (1, 'Downs', 'Marsha', '555-537-8765', '[e-mail]');
INSERT INTO PET_OWNER
VALUES (2, 'James', 'Richard', '555-537-7654', '[e-mail]');
INSERT INTO PET_OWNER
VALUES (3, 'Frier', 'Liz', '555-537-6543', '[e-mail]');
INSERT INTO PET_OWNER ( OwnerID, OwnerLastName, OwnerFirstName,
OwnerEmail )
VALUES (4, 'Trent', 'Miles', '[e-mail]');
Answer to Question 3:
CREATE TABLE PET(
PetID INT NOT NULL PRIMARY KEY,
PetName varchar (50),

2
MICROSOFT ACCESS DATABASE
PetType varchar (50),
PetBreed varchar (30),
PetDOB varchar (15),
OwnerId int NOT NULL,
FOREIGN KEY (OwnerID) REFERENCES PET_OWNER(OwnerID));
Answer to Question 4:
INSERT INTO PET
VALUES (1, 'King', 'Dog', 'std.Poddle', '27-feb-2011', 1);
INSERT INTO PET
VALUES (2, 'Teddy', 'Cat', 'Cashmere', '01-feb-2012', 2);
INSERT INTO PET
VALUES (3, 'Fido', 'Dog', 'Std.Poodle', '17-Jul-2010', 1);
INSERT INTO PET
VALUES (4, 'Aj', 'Dog', 'Collie Mix', '05-May-2011', 3);
INSERT INTO PET
VALUES (5, 'Cedro', 'Cat', 'Unknown', '06-Jun-2009', 2);
INSERT INTO PET
VALUES (6, 'Wooley', 'Cat', 'Unknown', 'NULL', 2);
INSERT INTO PET
VALUES (7, 'Buster', 'Dog', 'Border Collie', '11-Dec-2008', 4);

3
MICROSOFT ACCESS DATABASE
Answer to Question 5:
SELECT PetType, PetBreed
FROM PET;
Answer to Question 6:
SELECT PetBreed, PetDOB
FROM PET
WHERE PetType='Cat';
Answer to Question 7:
SELECT OwnerFirstName, OwnerLastName, OwnerEmail
FROM pet_owner
ORDER BY OwnerLastName;
Answer to Question 8:
SELECT LCASE (OwnerFirstName), UCASE (OwnerLastName)
FROM pet_owner;
Answer to Question 9:
SELECT COUNT (*)
FROM PET;
Answer to Question 10:
SELECT OwnerFirstName, OwnerLastName, OwnerEmail
FROM PET_OWNER

4
MICROSOFT ACCESS DATABASE
WHERE OwnerPhone is NULL;
Answer to Question 11:
SELECT COUNT([PetBreed]) AS NumberOfBreeds
FROM (SELECT DISTINCT PetBreed FROM PET) AS [%$##@_Alias];
Answer to Question 12:
SELECT PetName
FROM PET
WHERE PetType='Dog';

5
MICROSOFT ACCESS DATABASE
Bibliography
Brown, C. C., Pan, D., & Wiersma, G. (2015). Advanced Data Analysis: From Excel
PivotTables to Microsoft Access.
Halsey, M. N. (2017). Learn to Use Microsoft Access 2016. Silver City Publications &
Training, LLC.