Answer Happy

Have to write SQL queries.
MUTT'S GROOMING AND BOARDING CUSTOMER (CUST NO, CUST_FNAME, CUST_LNAME, CUST_ADDR, CUST_CITY, CUST_STATE, CUST_ZIP, CUST_PHONE) SERVICES (SERV NO, SERV_NAME, SERV_DESC, SERV_COST) CERTIFICATION (CERT NO, CERT_NAME, CERT_DESCR) SUITE (SUITE NO, SUITE_TYPE, SUITE_DESCR, SUITE_CAPACITY) VACCINATION (VAC_NO, VAC_NAME) PET (PET NO, PET_NAME, PET_DOB, PET_GENDER, PET_TYPE, CUST_NO) FK CUST_NO → CUSTOMER STAFF (STAFF NO, STAFF_FNAME, STAFF_LNAME, STAFF_ADDR, STAFF_CITY, STAFF_STATE, STAFF_ZIP, STAFF_SSN, DATE_HIRED, STAFF_SALARY, LOC_NO) FK LOC_NO → LOCATION LOCATION (LOC NO, LOC_ADDR, LOC_CITY, LOC_STATE, LOC_ZIP, LOC_PHONE, LOC_MANAGER) FK LOC_MANAGER → STAFF APPOINTMENT (APP NO, LOC_NO, PET_NO, APP_DATE, APP_TIME, STAFF_NO) FK LOC_NO → LOCATION PET_NO → PET FK STAFF_NO STAFF FK APP_SERV (APP NO, SERV NO) FK APP_NO → APPOINTMENT FK SERV_NO → SERVICE STAFF_CERT (STAFF NO, CERT NO, DATE) FK STAFF_NO STAFF FK CERT_NO → CERTIFICATION RESERVATION (RES NO, LOC_NO, START_DATE, END_DATE, SUITE_NO, ROOM_NO, NUM_PETS) LOC_NO → LOCATION FK SUITE_NO → SUITE RES_PET (RES NO, PET NO) FK RES_NO → RESERVATION FK PET_NO → PET FK
MUTT'S GROOMING AND BOARDING FK PET_VAC (VAC NO, PET NO, DATE) FK VAC_NO → VACCINATION PET_NO → PET BILLING (BILLING ID, CUST_NO, RES_NO, BILLING_DATE, BILL_COST) FK CUST_NO → CUSTOMER FK RES_NO → RESERVATION FK PROVIDER_ID → PROVIDER
MUTT'S GROOMING AND BOARDING SAMPLE QUERY Unless otherwise indicated, the ID number and the name should be shown whenever a person or name is requested in the output. If a specific date or person is requested, assume a variable will be passed to the query for comparison (you can make up the name of the variable). Assume that a current system date and time is available for use in a query: DATE() or TIME(). USE THE FOLLOWING FORMAT FOR YOUR QUERIES: 1) Type the query to be satisfied as stated in QUERIES REQUIRED. 2) Then show the VIEW you would create to satisfy this query. EXAMPLE: 1. List the customers in alphabetical order by customer number, name, address and phone. CREATE VIEW CustomerList (Cust#, First, Last, Street, City, State, Zip, Phone) AS SELECT FROM CUSTOMER ORDER BY cust_lname, cust_fname NOTES ON THE EXAMPLE: 1) Customerlist You should choose a unique, appropriate name for each view. 2) (Number, Name, ...) You may rename the columns but you do not have to. There must be a one-to-one correspondence between the columns listed here and the ones shown in the SELECT statement. 3) LIBRARY Table names are usually shown in upper case to differentiate them from column names.
MUTT'S GROOMING AND BOARDING Reminder: THE REPORT MUST BE TYPED QUERIES REQUIRED The completed system must be able to satisfy the following queries: A. For each CUSTOMER: 1) List the customers in alphabetical order by customer number, name, address and phone. 2) List all customers who have a pet scheduled for a grooming appointment. List customer number, name, appointment date, time, and pet name. B. For each PET: 1) List the pet's name, type, gender, DOB, and owner's first and last name along with their telephone number 2) List the vaccination record on file showing the pet's number, name, vaccination name and date administered. c. D. 3) List all pets on file that have never had a reservation for boarding. List the pet's number, name, customer first and last name and telephone number. For each LOCATION: 1) List the location number, address, manager's name, and the location's telephone number. 2) List the staff along with their first and last names and the current certifications each has on file. 3) List all appointments with the appointment number, pet's name, date, time, and staff member assigned to the appointment. 4) List the total salary for the staff at each location. APPOINTMENTS: 1) For each appointment, list the appointment date, time, pet's name, owner's name, phone number and the staff member assigned. 2) For each day, list the appointments by staff number, name and time. 3) List the services that do not appear in any appointments 4) For each appointment list the appointment number, customer's name and total cost. RESERVATIONS: 1) For each reservation, list the location, pet's number and name, the start and end dates, and the type of suite requested. 2) List all pets along with their number and name who have upcoming appointments and reservations. List the date of the appointment and the start date of the reservation. 3) List all reservations that have been billed, along with the reservation number, the customer's name, the billing date and the bill cost. E.