Write 10 SQL statements against the STUDENT schema (Login in
Write 10 SQL statements against the STUDENT schema (Login information can be provided). Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown 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, 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.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)Q4. Show the number of instructors who live in NY state and has a street number of 518. (HINT: use string functions such as SUBSTR and INSTR)Q5. Display the lowest, highest, and average numeric grade of Project grade type. (HINT: use the MIN, MAX, and AVG function; join GRADE and GRADE_TYPE)– LEFT JOIN, DATEQ6. List all students (display student_id, first name, last name, and registration date) who registered on or before 2/15/2007 but have not enrolled in any course. (HINT: left outer join STUDENT and ENROLLMENT; use the TO_DATE function)– COUNT WITH 3-TABLE JOINQ7. Find how many students are enrolled in sections taught by Todd Smythe. (HINT: join INSTRUCTOR, SECTION, and ENROLLMENT)– 4-TABLE JOINQ8. Show all students who are taking, or have taken the course ‘Advanced Java Programming course’. List student name, enrollment date, and section location. (HINT: join COURSE, SECTION, ENROLLMENT, and STUDENT)– 4-TABLE JOINQ9. List the students who have received any numeric grade score of at least 95 in an Advanced Java Programming course. Show student name, the grade type code, and the numeric grade.– 4-TABLE JOIN WITH DISTINCTQ10. 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 DISTINCT keyword)Learning Resourceshttps://www.w3schools.com/sql/sql_join.asphttps://docs.oracle.com/database/121/SQLRF/functions.htm#SQLRF006
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.