[solved]-Write 10 Sql Statements Student Schema Statements Run Error Free Valid Submit Two Separate Q39058361

Write 10 SQL statements against the STUDENT schema. Yourstatements should run error-free and should be valid. Submit twoseparate files: one plain text file (.txt or .sql file) with yourstatements only; and the other document (doc/docx/pdf) include bothyour statements and your query results (copy and paste text orscreen shots). The instructor and TA should be able to run yourplain text source file as script and generate the same output asshown in your result document.

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)

Q2. List all students (display student_id, first name, lastname, street address, zipcode, city, and state) who live in NewYork, NY. Sort results by last name, and then first name, indescending order.

Q3. Display the city in which each instructor lives. List firstname, last name, zip, city, and state. Display “N/A” for zip, city,and state if an instructor does not have a zipcode. (HINT: leftjoin INSTRUCTOR and ZIPCODE; use NVL function)

Q4. Show the number of instructors who live in NY state and hasa street number of 518. (HINT: use string functions such as SUBSTRand INSTR)

Q5. Display the lowest, highest, and average numeric grade ofProject grade type. (HINT: use the MIN, MAX, and AVG function; joinGRADE and GRADE_TYPE)

— LEFT JOIN, DATE

Q6. List all students (display student_id, first name, lastname, and registration date) who registered on or before 2/15/2007but have not enrolled in any course. (HINT: left outer join STUDENTand ENROLLMENT; use the TO_DATE function)

— COUNT WITH 3-TABLE JOIN

Q7. Find how many students are enrolled in sections taught byTodd Smythe. (HINT: join INSTRUCTOR, SECTION, and ENROLLMENT)

— 4-TABLE JOIN

Q8. Show all students who are taking, or have taken the course”Advanced Java Programming course”. List student name, enrollmentdate, and section location. (HINT: join COURSE, SECTION,ENROLLMENT, and STUDENT)

— 4-TABLE JOIN

Q9. List the students who have received any numeric grade scoreof at least 95 in an Advanced Java Programming course. Show studentname, the grade type code, and the numeric grade.

— 4-TABLE JOIN WITH DISTINCT

Q10. List the instructors who teach Advanced Java Programming(having a section with students enrolled), without duplication.Show instructor name and course name. (HINT: use the DISTINCTkeyword)

Expert Answer

---

Answer to Write 10 SQL statements against the STUDENT schema. Your statements should run error-free and should be valid. Submit tw… . . .