Database Assignment: ITM 6400

Instructions

This assignment should be done in pairs. Only one partner needs to submit the files at blackboard.

Due date: October 23, 2018

Marks: 15%

In this assignment, you have to run a series of queries as mentioned below. Prior to executing the queries, you have to create the database. For this purpose, you need to create a pet.sql file that will contain the SQL statements for table creation and data insertion.

The pet schema (database) is mentioned in the following figure (next page). Please note, that you do not have to create pet_3 table for this assignment.To insert data into the database, refer to the petdata.xls file. Once the pet.sql file is executed, the pet.db file should be created.

Deliverables

In blackboard, you need to submit the pet.sql file and a word file that contains all the SQL statements for the 15 queries. pet.sql file should contain the create statements to create the 3 tables and the insert statements to insert data in to the tables. When the pet.sql file is executed then the pet.db with all data must be created. You will use SQLite to verify that the pet.db is created when the pet.sql file is executed. Once the pet.db file is created then you can run all the queries. You need not submit the results of the queries.

Write the sql statements for the following queries:

1.Write a SQL statement to display the breed and type of all pets.

2.Write a SQL statement to display the breed, type, and DOB of all pets having the type Dog.

3.Write an SQL statement to display the PetBreed column of PET.

4.Write a SQL statement to display the PetBreed column of PET. Do not show duplicates.

5.Write a SQL statement to display the breed, type, and DOB for all pets having the type Dog and the breed Std. Poodle.

6.Write a SQL statement to display the name, breed, and type for all pets that are not of type Bird, Dog, or Fish.

7.Write a SQL statement to display the last name, first name and email of all owners who have an email address ending with somewhere.com. Assume that email account names can be any number of characters.

8.Write an SQL statement to display the last name, first name and email of any owner who has a NULL value for OwnerPhone.

9.Write an SQL statement to display the name and breed of all pets sorted by PetName.

10.Write a SQL statement to display the name and breed of all pets sorted by PetBreed in ascending order and by PetName in descending order within PetBreed.

11.Write an SQL statement to count the number of pets.

12.Write an SQL statement to count the number of distinct breeds.

13.Write an SQL statement to display the last name, first name, and email of any owners of cats.

14.Write an SQL statement to display the Pet ID, breed, and type for all pets having a four-character name starting with K.

15.Display the last name, first name, and email of any owner of a pet that has an AverageLifeExpectancy value greater than 15

[DB 0: Insert Draw Page Layout Formulas Review E , g [WWM'J'V XE petdata View Developer 39 Wrap Text v v A v H v v 0 '7 3 .... '9 'B I H 1 6' 1 3 $ A, no Ezrgagzz' ma; 3:." . w m? E15 $' , t A B C D E F G H | l L M N Q R S T 1 PetlD PetName PetType PetBreed PetDOB OwnerlD 2 1 Teddy Cat Cashmere 2/1/12 2 3 2 Fido Dog Std.Pood|e 7/17/10 1 4 3 A] Dog CollleMlx 5/5/11 3 5 4 Cedru Cat Unknown 6/6/09 2 6 S Waoley Cat Unknown 2 7 6 Buster Dog Eordercullle 12/11/08 4 8 9 10 11 12 13 14 15 I I 16 17 18 19 20 21 22 23 24 25 { p ' pet pet_owner breed + Ready __) + 100%