Run the Zoo database’s “FULL_DATABASE_with_DROP” script to create
the tables and insert data for these questions. All answers should be using SQL statement (scripts).
Questions
1. List the names of the donators who made a donation in December of 2010.
Display all attributes from the zoo table.
Select the bar code number of all products that are of both product types MP and PP.
List all illness that are treated with diet change.
What type of membership is for 'four adults'?
List the product ID and name of the product whose selling price is between 10 and 20.
List all zoo ids and private event types that occurred between April and June of year 2008. Do not have any duplicates in your results.
List all cities where the employees live. DO not include duplicates.
Select everything from the maintenance table where the maintenance title is either 'Janitor' or 'HVAC'. Make sure to use the IN clause.
10. List the warehouse address, city, state, and zip code for the warehouses in either Iowa or Ohio using the IN function.
11. Show the first and last name of all the volunteers in reverse alphabetical order by last name.
12. List the name of sponsor, start date, end date and payment date for those sponsors who made payment 2009. Order the results by sponsor name.
-------------------------------------------------------- -- Drop Tables -------------------------------------------------------- DROP TABLE ANIMAL; DROP TABLE ANIMAL_BREED; DROP TABLE ANIMAL_CHECKUP; DROP TABLE ANIMAL_DIETARY_REQUIREMENTS; DROP TABLE ANIMAL_LIVING_ENVIRONMENT; DROP TABLE ASSOCIATION; DROP TABLE CORPORATE_SPONSOR; DROP TABLE CORPORATE_SPONSOR_PAYMENT; DROP TABLE CUSTOMER_SERVICES; DROP TABLE DEPARTMENT; DROP TABLE DONATOR; DROP TABLE DONATOR_PAYMENT; DROP TABLE EMPLOYEE; DROP TABLE FOOD_CATEGORY; DROP TABLE FOOD_ITEM; DROP TABLE FOOD_ITEM_SUPPLIER; DROP TABLE FOOD_SUPPLIER; DROP TABLE ILLNESS; DROP TABLE ILLNESS_TYPE; DROP TABLE INVENTORY; DROP TABLE KEEPER; DROP TABLE MAINTENANCE; DROP TABLE MANUFACTURED_PRODUCT; DROP TABLE MEDICATION; DROP TABLE MEMBERSHIP_OWNER; DROP TABLE MEMBERSHIP_TYPE; DROP TABLE OFFICE; DROP TABLE PAYMENT; DROP TABLE PRESCRIPTION; DROP TABLE PRIVATE_EVENT; DROP TABLE PRIVATE_EVENT_PAYMENT; DROP TABLE PRODUCT; DROP TABLE PURCHASED_PRODUCT; DROP TABLE RECEIPT_LINE; DROP TABLE RESEARCHER; DROP TABLE STORE; DROP TABLE TEAM; DROP TABLE RECEIPT; DROP TABLE VET; DROP TABLE VOLUNTEER; DROP TABLE VOLUNTEER_DEPT_HISTORY; DROP TABLE WAREHOUSE; DROP TABLE ZOO; -------------------------------------------------------- -- DDL for Table ANIMAL -------------------------------------------------------- CREATE TABLE ANIMAL (ANIM_ID DECIMAL(9,0)PRIMARY KEY, ANIM_NAME VARCHAR(50)NOT NULL, ANIM_GENDER CHAR(11) NOT NULL, ANIM_BREED_ID DECIMAL(9,0) NOT NULL, ANIM_BIRTH_DATE DATE NOT NULL, ANIM_ACQUIRED_FROM VARCHAR(50) NOT NULL, ANIM_ARRIVAL_DATE DATE NOT NULL, ANIM_DEPARTURE_DATE DATE, ANIM_HEIGHT_ON_ARRIVAL VARCHAR(22) NOT NULL, ANIM_WEIGHT_ON_ARRIVAL VARCHAR(22) NOT NULL, ANIM_OTHER_DETAILS VARCHAR(100), ANIM_LIVING_ENVIRONMENT_ID DECIMAL(9,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table ANIMAL_BREED -------------------------------------------------------- CREATE TABLE ANIMAL_BREED (ANIM_BREED_ID DECIMAL(9,0)PRIMARY KEY, ANIM_BREED_NAME VARCHAR(50) NOT NULL, ANIM_BREED_HABITAT VARCHAR(66) NOT NULL, ANIM_BREED_COUNTRIES_OF_ORIGIN VARCHAR(58) NOT NULL, ANIM_BREED_PHOTO_FILENAME VARCHAR(50) NOT NULL, ANIM_BREED_OTHER_DETAILS VARCHAR(106) ) ; -------------------------------------------------------- -- DDL for Table ANIMAL_CHECKUP -------------------------------------------------------- CREATE TABLE ANIMAL_CHECKUP (ANIM_CHECKUP_DATE DATENOT NULL, ANIM_ID DECIMAL(9,0)NOT NULL, ANIM_CHECKUP_HEIGHT VARCHAR(20) NOT NULL, ANIM_CHECKUP_WEIGHT VARCHAR(20) NOT NULL, ANIM_CHECKUP_LENGTH VARCHAR(20) NOT NULL, ANIM_CHECKUP_GENERAL_HEALTH VARCHAR(100) NOT NULL, ANIM_CHECKUP_OTHER_DETAILS VARCHAR(100) , PRIMARY KEY (ANIM_CHECKUP_DATE, ANIM_ID)) ; -------------------------------------------------------- -- DDL for Table ANIMAL_DIETARY_REQUIREMENTS -------------------------------------------------------- CREATE TABLE ANIMAL_DIETARY_REQUIREMENTS (FOOD_ITEM_ID DECIMAL(9,0)NOT NULL, ANIM_ID DECIMAL(9,0)NOT NULL, ANIM_DIETARY_REQUIREMENTS VARCHAR(100) NOT NULL, PRIMARY KEY (FOOD_ITEM_ID, ANIM_ID)) ; -------------------------------------------------------- -- DDL for Table ANIMAL_LIVING_ENVIRONMENT -------------------------------------------------------- CREATE TABLE ANIMAL_LIVING_ENVIRONMENT (ANIM_LIVING_ENVIRONMENT_ID DECIMAL(9,0)PRIMARY KEY, ANIM_LIVING_ENVIRONMENT_TYPE VARCHAR(40) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table ASSOCIATION -------------------------------------------------------- CREATE TABLE ASSOCIATION (ASSOC_ID DECIMAL(3,0)PRIMARY KEY, ASSOC_NAME VARCHAR(60) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table CORPORATE_SPONSOR -------------------------------------------------------- CREATE TABLE CORPORATE_SPONSOR (SPON_ID DECIMAL(3,0) PRIMARY KEY, ZOO_ID DECIMAL(4,0) NOT NULL, SPON_COMPANY_NAME VARCHAR(60) NOT NULL, SPON_DATE_DONATED DATE NOT NULL, SPON_START_DATE DATE NOT NULL, SPON_END_DATE DATE ) ; -------------------------------------------------------- -- DDL for Table CORPORATE_SPONSOR_PAYMENT -------------------------------------------------------- CREATE TABLE CORPORATE_SPONSOR_PAYMENT (SPON_ID DECIMAL(3,0)NOT NULL, PAY_ID DECIMAL(5,0)NOT NULL, SPON_PAY_PAYMENT_DATE DATE NOT NULL, PRIMARY KEY (SPON_ID, PAY_ID)) ; -------------------------------------------------------- -- DDL for Table CUSTOMER_SERVICES -------------------------------------------------------- CREATE TABLE CUSTOMER_SERVICES (EMP_ID DECIMAL(4,0)PRIMARY KEY, CUS_SER_COMMISSION DECIMAL(3,3) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table DEPARTMENT -------------------------------------------------------- CREATE TABLE DEPARTMENT (DEPT_ID DECIMAL(3,0)PRIMARY KEY, DEPT_NAME VARCHAR(100) NOT NULL, ZOO_ID DECIMAL(4,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table DONATOR -------------------------------------------------------- CREATE TABLE DONATOR (DON_ID DECIMAL(3,0)PRIMARY KEY, DON_FIRST_NAME VARCHAR(30) NOT NULL, DON_LAST_NAME VARCHAR(30) NOT NULL, DON_PHONE_NUMBER VARCHAR(16) NOT NULL, DON_DATE_DONATED DATE NOT NULL, ZOO_ID DECIMAL(4,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table DONATOR_PAYMENT -------------------------------------------------------- CREATE TABLE DONATOR_PAYMENT (DON_ID DECIMAL(3,0)NOT NULL, PAY_ID DECIMAL(5,0)NOT NULL, DON_PAY_PAYMENT_DATE DATE NOT NULL, PRIMARY KEY (DON_ID, PAY_ID)); -------------------------------------------------------- -- DDL for Table EMPLOYEE -------------------------------------------------------- CREATE TABLE EMPLOYEE (EMP_ID DECIMAL(4,0)PRIMARY KEY, DEPT_ID DECIMAL(3,0), OFFICE_ID DECIMAL(3,0), ZOO_ID DECIMAL(4,0), EMP_FIRST_NAME VARCHAR(30) NOT NULL, EMP_LAST_NAME VARCHAR(30) NOT NULL, EMP_PHONE_NUMBER DECIMAL(10,0) NOT NULL, EMP_SALARY DECIMAL(6,0) NOT NULL, EMP_SOCIAL_SECURITY_NUMBER DECIMAL(9,0) NOT NULL, EMP_HIRE_DATE DATE NOT NULL, EMP_DEPARTURE_DATE DATE, EMP_TYPE VARCHAR(2), EMP_ADDRESS VARCHAR(50) NOT NULL, EMP_CITY VARCHAR(40) NOT NULL, EMP_ZIP_CODE DECIMAL(5,0) NOT NULL, EMP_STATE VARCHAR(2) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table FOOD_CATEGORY -------------------------------------------------------- CREATE TABLE FOOD_CATEGORY (FOOD_CATEGORY_ID DECIMAL(9,0)PRIMARY KEY, FOOD_CATEGORY_DESCRIPTION VARCHAR(100) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table FOOD_ITEM -------------------------------------------------------- CREATE TABLE FOOD_ITEM (FOOD_ITEM_ID DECIMAL(9,0)PRIMARY KEY, FOOD_ITEM_DESCRIPTION VARCHAR(100) NOT NULL, FOOD_CATEGORY_ID DECIMAL(9,0), FOOD_QOH DECIMAL(9,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table FOOD_ITEM_SUPPLIER -------------------------------------------------------- CREATE TABLE FOOD_ITEM_SUPPLIER (FOOD_ITEM_ID DECIMAL(9,0)NOT NULL, FOOD_SUP_ID DECIMAL(9,0)NOT NULL, FOOD_PRICE DECIMAL(9,2) NOT NULL, PRIMARY KEY (FOOD_ITEM_ID, FOOD_SUP_ID)) ; -------------------------------------------------------- -- DDL for Table FOOD_SUPPLIER -------------------------------------------------------- CREATE TABLE FOOD_SUPPLIER (FOOD_SUP_ID DECIMAL(9,0)PRIMARY KEY, FOOD_SUP_COMPANY_NAME VARCHAR(30) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table ILLNESS -------------------------------------------------------- CREATE TABLE ILLNESS (ANIM_ID DECIMAL(9,0)NOT NULL, ILL_TYPE_ID DECIMAL(9,0)NOT NULL, ILL_START_DATE DATE NOT NULL, ILL_END_DATE DATE , PRIMARY KEY (ANIM_ID, ILL_TYPE_ID)) ; -------------------------------------------------------- -- DDL for Table ILLNESS_TYPE -------------------------------------------------------- CREATE TABLE ILLNESS_TYPE (ILL_TYPE_ID DECIMAL(9,0)PRIMARY KEY, ILL_DESCRIPTION VARCHAR(1000) NOT NULL, ILL_TREATMENT VARCHAR(1000) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table INVENTORY -------------------------------------------------------- CREATE TABLE INVENTORY (PRODUCT_ID DECIMAL(3,0)NOT NULL, WAREHOUSE_ID DECIMAL(2,0)NOT NULL, INVEN_AMOUNT DECIMAL(4,0) NOT NULL, INVEN_DATE_RESTOCKED DATE NOT NULL, INVEN_MAX_STOCK DECIMAL(4,0) NOT NULL, INVEN_REORDER_POINT DECIMAL(3,0) NOT NULL, PRIMARY KEY (PRODUCT_ID, WAREHOUSE_ID)) ; -------------------------------------------------------- -- DDL for Table KEEPER -------------------------------------------------------- CREATE TABLE KEEPER (EMP_ID DECIMAL(4,0)PRIMARY KEY, KEEP_CERTIFIED VARCHAR(3) NOT NULL); -------------------------------------------------------- -- DDL for Table MAINTENANCE -------------------------------------------------------- CREATE TABLE MAINTENANCE (EMP_ID DECIMAL(4,0)PRIMARY KEY, MAINT_TITLE VARCHAR(30) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table MANUFACTURED_PRODUCT -------------------------------------------------------- CREATE TABLE MANUFACTURED_PRODUCT (PRODUCT_ID DECIMAL(3,0)PRIMARY KEY, PRODUCT_START_DATE DATE NOT NULL, PRODUCT_FINISH_DATE DATE NOT NULL ) ; -------------------------------------------------------- -- DDL for Table MEDICATION -------------------------------------------------------- CREATE TABLE MEDICATION (MED_ID DECIMAL(9,0)PRIMARY KEY, MED_TYPE VARCHAR(30) NOT NULL, MED_DIRECTIONS VARCHAR(100) NOT NULL, MED_UNIT_OF_MEASURE VARCHAR(19) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table MEMBERSHIP_OWNER -------------------------------------------------------- CREATE TABLE MEMBERSHIP_OWNER (MEMB_OWNER_ID DECIMAL(3,0) PRIMARY KEY, MEMB_FIRST_NAME VARCHAR(30) NOT NULL, MEMB_LAST_NAME VARCHAR(30) NOT NULL, MEMB_PHONE_NUMBER DECIMAL(10,0)NOT NULL, MEMB_DATE_OF_BIRTH DATE NOT NULL, MEMB_ADDRESS VARCHAR(60) NOT NULL, MEMB_CITY VARCHAR(40) NOT NULL, MEMB_ZIP_CODE DECIMAL(5,0) NOT NULL, MEMB_STATE VARCHAR(20) NOT NULL, MEMB_TYPE_ID DECIMAL(4,0) NOT NULL, ZOO_ID DECIMAL(4,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table MEMBERSHIP_TYPE -------------------------------------------------------- CREATE TABLE MEMBERSHIP_TYPE (MEMB_TYPE_ID DECIMAL(4,0)PRIMARY KEY, MEMB_TYPE_TITLE VARCHAR(30) NOT NULL, MEMB_TYPE_PRICE DECIMAL(5,2) NOT NULL, MEMB_TYPE_DESCRIPTION VARCHAR(255) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table OFFICE -------------------------------------------------------- CREATE TABLE OFFICE (OFFICE_ID DECIMAL(3,0)PRIMARY KEY, OFFICE_PHONE_NUMBER DECIMAL(10,0) NOT NULL, OFFICE_BUILDING VARCHAR(40) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table PAYMENT -------------------------------------------------------- CREATE TABLE PAYMENT (PAY_ID DECIMAL(5,0)PRIMARY KEY, PAY_TYPE VARCHAR(30) NOT NULL, PAY_CREDIT_CARD_NUMBER DECIMAL(16,0), PAY_EXPIRATION_DATE DATE, PAY_CREDIT_CARD_TYPE VARCHAR(30), PAY_AMOUNT DECIMAL(6,0) NOT NULL, PAY_CHECK_NUMBER DECIMAL(5,0) ) ; -------------------------------------------------------- -- DDL for Table PRESCRIPTION -------------------------------------------------------- CREATE TABLE PRESCRIPTION (ANIM_ID DECIMAL(9,0)NOT NULL, MED_ID DECIMAL(9,0)NOT NULL, VET_ID DECIMAL(4,0)NOT NULL, ASSOC_ID DECIMAL(3,0)NOT NULL, PRES_DOSAGE VARCHAR(20) NOT NULL, PRES_DATE_TAKEN DATE NOT NULL, PRIMARY KEY (ANIM_ID, MED_ID, VET_ID, ASSOC_ID)) ; -------------------------------------------------------- -- DDL for Table PRIVATE_EVENT -------------------------------------------------------- CREATE TABLE PRIVATE_EVENT (PRIV_EVEN_ID DECIMAL(3,0)PRIMARY KEY, PRIV_EVEN_DATE DATE NOT NULL, PRIV_EVEN_TYPE VARCHAR(50) NOT NULL, PRIV_EVEN_PRICE DECIMAL(5,0) NOT NULL, ZOO_ID DECIMAL(4,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table PRIVATE_EVENT_PAYMENT -------------------------------------------------------- CREATE TABLE PRIVATE_EVENT_PAYMENT (PRIV_EVEN_ID DECIMAL(3,0)NOT NULL, PAY_ID DECIMAL(5,0)NOT NULL, PRIV_EVEN_PAY_PAYMENT_DATE DATE NOT NULL, PRIMARY KEY (PRIV_EVEN_ID, PAY_ID) ); -------------------------------------------------------- -- DDL for Table PRODUCT -------------------------------------------------------- CREATE TABLE PRODUCT (PRODUCT_ID DECIMAL(3,0)PRIMARY KEY, PRODUCT_NAME VARCHAR(60) NOT NULL, PRODUCT_SUGGESTED_PRICE DECIMAL(5,2) NOT NULL, PRODUCT_BAR_CODE_NUMBER DECIMAL(8,0) NOT NULL, PRODUCT_TYPE VARCHAR(6) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table PURCHASED_PRODUCT -------------------------------------------------------- CREATE TABLE PURCHASED_PRODUCT (PRODUCT_ID DECIMAL(3,0)PRIMARY KEY, PRODUCT_PURCHASE_PRICE DECIMAL(5,2) NOT NULL, PRODUCT_PURCHASE_DATE DATE NOT NULL ) ; -------------------------------------------------------- -- DDL for Table RECEIPT_LINE -------------------------------------------------------- CREATE TABLE RECEIPT_LINE (RECEIPT_LINE_ID DECIMAL(5,0)PRIMARY KEY, PRODUCT_ID DECIMAL(3,0) NOT NULL, SELLING_PRICE DECIMAL(5,2) NOT NULL, QUANTITY DECIMAL(2,0) NOT NULL, RECEIPT_ID DECIMAL(5,0) NOT NULL ) ; -------------------------------------------------------- -- DDL for Table RESEARCHER -------------------------------------------------------- CREATE TABLE RESEARCHER (EMP_ID DECIMAL(4,0)PRIMARY KEY, RES_BONUS DECIMAL(5,0) ) ; -------------------------------------------------------- -- DDL for Table STORE -------------------------------------------------------- CREATE TABLE STORE (STORE_ID DECIMAL(4,0)PRIMARY KEY, STORE_PHONE_NUMBER