PRACTICE LAB 5.docx - ANGELA GARDINER Q1 List all...

This preview shows page 1 - 3 out of 6 pages.

ANGELA GARDINER Q1. List all instructors. Show salutation in all lower cases, last name in all upper cases and phone number in the format of '(xxx) xxx-xxxx'. (HINT: use LOWER, UPPER and SUBSTR function) select lower(salutation),
Q2. List all students (display student_id, first name, last name, street address, zipcode, city, and state) who live in New York, NY. Sort results by last name, and then first name, in descending order.
Where z.city = 'New York' and z.state = 'NY' ORDER BY s.Last_Name DESC, S.first_name DESC; Q3. Display the city in which each instructor lives. List first name, last name, zip, city, and state. Display "N/A" for zip, city, and state if an instructor does not have a zipcode. (HINT: left join INSTRUCTOR and ZIPCODE; use NVL function) Select I.First_Name, I.Last_Name, NVL (z.zip, 'N/A') AS Zip, NVL (z.city, 'N/A') as City, NVL (z.state, 'N/A') AS State from Instructor I Left Join Zipcode Z on I.zip = Z.zip

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture