Microsoft Access Database

This article provides step-by-step solutions to questions related to creating tables, inserting values, and running queries in Microsoft Access Database. The article also includes a bibliography for further reading.

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.