Database Assignment

Please include the questions with your answers. Answer in complete sentences where applicable.

1. Does all standard SQL work in Microsoft Access? Explain.

No. certain expressions like certain Join Expressions are not supported by Microsoft Access even though they are supported by other vendors like ANSI SQL. For instance, the following expression leads to an error “Join Expressions not supported”

select * from (table1 inner join table2 on table1.city = table2.code)

inner join table3 on table3.col1 = 5 and table3.col2 = 'Hello'

2. List and describe the four basic SQL data types.

Character strings – these refer to fixed-length and variable strings with varying lengths of characters. It is defined as CHARACTER VARYING(n) where n represents a number identifying the allocation

Numeric strings – are stored in fields that are defined as some type of number, typically refered to as NUMBER, INTEGER, REAL, DECIIMAL, and so on.

Date and time values – are used to keep track of information concerning dates and time. This is supported as DATETIME datatypes that includes; DATE, TIME, INTERVAL, TIMELSTAMP.

3. List and describe five SQL built-in functions.

In SQL functions are kewords that manipulate values within columns for output purposes. Types of functions in SQL include;

a. Aggregate functions – functions that are used to provide summarization information for a SQL statement, such as counts, totals, and averages. i.e COUNT, SUM, MAX, MIN, AVG

b. Group functions – are used in arranging data in groups

c. Character functions – used to represent strings in SQL in formats alternate to how they are stored in the table. i.e TRANCATE, CONVERT and POSITION

d. Date and Time functions – used to manioulate the representation of date and time data.i.e Current date and Time Zone

The best way to learn SQL is by actually using it. In the following problems, we will use SQL to create, populate, and query a small database. Use SQL in Microsoft Access to complete the problems. Save all queries as instructed in the problem. Submit the database file (save as Pet_Database.accdb and KEEP THIS FILE for use with a later assignment) with all your queries in addition to the Word document containing the questions and answers for numbers 1, 2, and 3.

Use the following information for problems 4 – 10:

Tables:

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID)

**Note: OwnerID is italicized to indicate the Foreign Key**

Data:

4. Write an SQL CREATE TABLE statement to create the PET_OWNER table, with OwnerID as a surrogate key. Save as CreatePetOwner.

CREATE TABLE PET_OWNER (

OwnerID INT PRIMARY KEY,

OwnerLastName VARCHAR(20),

OwnerFirstName VARCHAR(20),

OwnerPhone CHAR(12),

OwnerEmail VARCHAR(40))

5. Write a set of SQL INSERT statements to populate the PET_OWNER table with the data given above. Save as PopulatePetOwner.

INSERT INTO PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) VALUES (1,'Downs', 'Marsha', '555-537-8765', '[email protected]');

INSERT INTO PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) VALUES (2, 'James', 'Richard', '555-537-7654', '[email protected]');

INSERT INTO PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerEmail) VALUES (4, 'Trent', 'Miles', '[email protected]');

INSERT INTO PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) VALUES (3, 'Frier', 'Liz', '555-537-6543', '[email protected]');

6. Write an SQL CREATE TABLE statement to create the PET table, with PetID as a surrogate key. Save as CreatePet.

CREATE TABLE PET(

PetID INT PRIMARY KEY ,

PetName VARCHAR(10),

PetType VARCHAR(10),

PetBreed VARCHAR(15),

PetDOB DATE,

OwnerID INT REFERENCES PET_OWNER);

7. Write a set of SQL INSERT statements to populate the PET table with the data given above. Save as PopulatePet.

INSERT INTO PET (PetID,PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (1,'King', 'Dog', 'Std. Poodle','27-Feb-11', 1);

INSERT INTO PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (2, 'Teddy', 'Cat', 'Cashmere', '01-Feb-12', 2);

INSERT INTO PET (PetID,PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (3,'Fido', 'Dog', 'Std. Poodle', '17-Jul-10', 1);

INSERT INTO PET (PetID,PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (4,'AJ', 'Dog', 'Coolie Mix', '05-May-11', 3);

INSERT INTO PET (PetID,PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (5,'Cedro', 'Cat', 'Unknown', '06-May-09', 2);

INSERT INTO PET (PetID,PetName, PetType, PetBreed, PetDOB, OwnerID) VALUES (7,'Buster', 'Dog', 'Border Coolie', '11-Dec-08', 4);

8. Write an SQL statement to display the breed and type of all pets. Save as AllBreeds.

9. Write an SQL statement to display the breed, and DOB of all pets having the type Cat. Save as Cats.

SELECT PetBreed, PetDOB

FROM PET

WHERE PetType='Cat';

10. Write an SQL statement to display the first name, last name, and email of all owners, sorted in alphabetical order by last name. Save as AlphaOwners.

SELECT OwnerFastName,OwnerLastName, OwnerEmail

FROM PET_OWNER

ORDER BY OwnerLastName ASC;

11. Write an SQL statement to display all the owners’ names, with the first name in all lower case and the last name in all upper case. Save as UpperLower.

SELECT LCASE(OwnerFastName),UCASE(OwnerLastName)

FROM PET_OWNER

ORDER BY OwnerLastName ASC;

12. Write an SQL statement to display the total number of pets. Save as TotalPets.

SELECT Count(PET.PetID) AS Total

FROM PET;

13. Write an SQL statement to display the last name, first name and email of any owner who has a NULL value for OwnerPhone. (Note: there should be one owner who has a NULL value for OwnerPhone.) Save as PhoneNull.

SELECT PET_OWNER.OwnerLastName, PET_OWNER.OwnerFirstName, PET_OWNER.OwnerEmail

FROM PET_OWNER

WHERE (((PET_OWNER.OwnerPhone)="isNull"));

14. Write an SQL statement to count the number of distinct breeds. Save as NumberOfBreeds.

SELECT PET.PetBreed, Count(PET.PetID) AS TotalNumber

FROM PET

GROUP BY PET.PetBreed;

15. Write an SQL statement to display the names of all the dogs. Save as Dogs.

SELECT PET.PetName, PET.PetType

FROM PET

WHERE (((PET.PetType)="Dog"));