Course Hero Logo

A8.sql - /* Hey guys - we're going to spend some more time...

Course Hero uses AI to attempt to automatically extract content from documents to surface to you and others so you can study better, e.g., in search results, to enrich docs, and more. This preview shows page 1 - 3 out of 7 pages.

/* Hey guys -- we're going to spend some more time with Joins and sub-queries.I know you're excited. Don't fear, we'll get some Unions in,but first, more practice *//*Assumptions/Standards1 - Dates -- MM/DD/YYYY unless otherwise specified.2 - Names -- unless otherwise specified, if a problem asks for astudent/instructor name, we want to see the first name and last name columns.2a - alphabetic listing -- means sort it by last name, then first name3 - Start each assignment by pasting the contents of the assignment text intoyour .sql file -- write your SQL statements AFTER the problem statement comment.4 - My preferred contact is: [email protected]5 - I've added a line at the top that says "set echo on;" -- please leave it in6 - If you are asked to sort on a column, don't use DESC unless you arespecifically asked to do so.*/------------------------------------------------------------------------------------------------leave this next line in your scriptset echo on;/*1. List the names of instructors who have not used projects (PJ) as a basisfor grading in their courses. Arrange by last name.*/--names of instructors(first and last name ordered by last name)--where project type hasn't been used to grade the course.--write the code to pull all instructors--next write the one that doesn't pull the pj--then combine themselect distinct--sct.instructor_idi.first_name,i.last_namefromsection sctjoin instructor ion (i.instructor_id =sct.instructor_id)wheresct.section_id NOT IN (selectsection_idfrom gradewhere grade_type_code = 'PJ')order byi.last_name;/*2. Determine the number of students who received abelow average grade on the final exam (FI) in section 86.*/select
trunc(avg(g.numeric_grade),2) as average,--g.grade_type_code,s.last_namefromstudent s, grade g, enrollment ewhereg.student_id = e.student_idand e.student_id = s.student_idand e.section_id = 86and g.grade_type_code = 'FI'and numeric_grade < (selecttrunc(avg(numeric_grade),2)fromgrade)group bye.student_id, s.last_nameorder bys.last_name;--list the number..../*3. List the city and state that have the highest number of students enrolled.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 7 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Fall
Professor
Hilton
Tags
The Final, Personal name, Family name

Newly uploaded documents

Show More

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture