1. Create a query that lists all customers. Include their first name, last name,
ID: 3915880 • Letter: 1
Question
1. Create a query that lists all customers. Include their first name, last name, address, and home phone. The query should sort the list alphabetically by customer’s last name, then sort by customer’s first name. So Smith, Jane would come before Smith, Joe in the list.
2. Create a query that allows the receptionist to look up a customer’s contact information by last name. Use a parameter to allow the user to enter a specific name when prompted. For example, when the query runs, a window will pop up asking what name they want to search for. When the query executes, it should include all of the customer information for that last name and should be sorted by first name (it’s already sorted by last name since everyone in the list should have the same last name!)
3. Create a query that lists all visits including the customer’s name and ID, the name and species of the pet, the employees who worked with the animal, and the diagnosis. The query should user parameters and ask for the beginning date and end date of query. For example, the user should be able to specify that they want all visits between Jan 1, 2015 and March 31, 2015 or from June 1, 2015 to June 8, 2015. The user should be prompted for these parameters and the query should sort them by date.
4. The vet’s office likes to send out birthday cards to pets. Create a list that pulls the customer’s address and pet’s name for any pet that has a birthday in a certain month. This should also use a prompt where the user can enter the month. HINT: If the office manager types in 12 for the month, you will need to find every pet born in December. Your date is probably stored in one field so you will either need to do a “like” in the query builder. If you’re doing SQL, you’ll need to do a little research on the web to get at that month after you’ve done the join.
5. The office manager wants to make sure that employee time is being used efficiently. Create a query that will count the number of visits each employee worked on. HINT: You will need to join the employee table to the employee/visit join table. In this query, you will group by employee’s last name and employee’s first name and then count the employee ID field.
6. The vet’s office needs to start thinking about getting rid of older files. You need to create a tool that identifies all customers that have not brought in their pet(s) in the last two years. Create this query and include customer name and home phone number and date of last visit. This is a little more complicated than it looks so here are a few hints….you will need to do two queries to accomplish this. First query, you will need to use the MAX and GROUP BY options discussed in chapter 10 to figure out the last visit date for all customers. Save that query. Then do another query joining that query you just finished to your customer table. This second query will set the parameter for records older than two years using that last visit date and you can grab the name and phone number there also.
Explanation / Answer
Q1) SELECT FIRST_NAME, LAST_NAME, ADDRESS, PHONE FROM CUSTOMER ORDER BY LAST_NAME ASC, FIRST_NAME ASC;
Q2) SELECT PHONE_NO1, PHONE_NO2, EMAIL_ADDRESS1, EMAIL_ADDRESS2, FAX FROM CUSTOMER WHERE LAST_NAME = '&L_NAME' ORDER BY FIRST_NAME ASC;
Q3) SELECT FIRST_NAME || LAST_NAME AS 'FULL_NAME', CUST_ID, PET_NAME, PET_SPECIES FROM CUSTOMER WHERE VISIT_DATE BETWEEN TO_DATE('&START_DATE','DD-MON-YYYY') AND TO_DATE('&END_DATE','DD-MON-YYYY') ORDER BY VISIT_DATE ASC;
Q4) SELECT LOCALITY, BUILDING, STREET, STATE_CODE, COUNTRY_CODE, ZIPCODE, PET_NAME FROM CUSTOMER WHERE TO_CHAR(PET_DOB,'mm') = '&month';
Q5) SELECT COUNT(EMP_ID), FIRST_NAME, LAST_NAME FROM EMPLOYEE JOIN VISITS ON EMPLOYEE.EMP_ID = VISITS.EMP_ID GROUP BY (FIRST_NAME, LAST_NAME);
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.