The database class at OTech was fun. So, here I decided to post some of the solutions to the problems presented during the course. These are all my original solutions to the problems. I am not including the database design at this time.
Problem: List the names (first and last) of all instructors.
SELECT FIRST_NAME, LAST_NAME
FROM INSTRUCTOR;Problem: Provide a list of distinct course numbers that are being taught as sections.
SELECT DISTINCT COURSE_NO
FROM SECTION;Problem: List the names of Instructors with a first name starting with “T”.
SELECT FIRST_NAME, LAST_NAME
FROM INSTRUCTOR
WHERE FIRST_NAME LIKE 'T%';Problem: List the full name, phone, and employer for all students with the last name of “Miller”.
SELECT FIRST_NAME, lAST_NAME, PHONE, EMPLOYER
FROM STUDENT
WHERE LAST_NAME = 'Miller';
OR
SELECT FIRST_NAME, lAST_NAME, PHONE, EMPLOYER
FROM STUDENT
WHERE LAST_NAME LIKE 'Miller';Problem: List the course number and course description of all courses that have a prerequisite of course 20.
SELECT COURSE_NO, DESCRIPTION
FROM COURSE
WHERE PREREQUISITE = 20;Problem: List the course number, description and cost for all 200 level courses costing less than $1100.
SELECT COURSE_NO, DESCRIPTION, COST
FROM COURSE
WHERE (COURSE_NO BETWEEN 200 AND 300) AND (COST < 1100);Problem: List the course number and section id for all 100 level courses taught in room L509 or L214.
SELECT COURSE_NO, SECTION_ID
FROM SECTION
WHERE (COURSE_NO BETWEEN 100 AND 200) AND ((LOCATION = 'L509') OR (LOCATION = 'L214'));
OR
SELECT COURSE_NO, SECTION_ID
FROM SECTION
WHERE (COURSE_NO BETWEEN 100 AND 200) AND (LOCATION IN ('L214', 'L509'));Problem: List the course number and section id for classes with a capacity of 15 or 20 (use the IN clause). Order the list by course number and section id.
SELECT COURSE_NO, SECTION_ID
FROM SECTION
WHERE CAPACITY IN (15, 20)
ORDER BY COURSE_NO, SECTION_ID;Problem: List the student id and grade for all of the quiz scores (QZ) in section id 147. Arrange the list by student id and grade. (from table grade).
SELECT STUDENT_ID, NUMERIC_GRADE
FROM GRADE
WHERE (GRADE_TYPE_CODE = 'QZ') AND (SECTION_ID = 147)
ORDER BY STUDENT_ID, NUMERIC_GRADE;Problem: List the course number and description for all courses with a prerequisite, arranged on course description.
SELECT COURSE_NO, DESCRIPTION
FROM COURSE
WHERE PREREQUISITE IS NOT NULL
ORDER BY DESCRIPTION;Problem: Provide an alphabetical list of the full name and phone number of all students that work for ‘Baxxon Corp.’ (the full name should be displayed as one column with an alias of ‘Student Name’)
SELECT CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Student Name", PHONE
FROM STUDENT
WHERE EMPLOYER = 'Baxxon Corp.'
ORDER BY "Student Name";Problem: Provide a list of student employers that are corporations (have “Corp.” in their name). List each employer only once and arrange the list alphabetical order.
SELECT DISTINCT EMPLOYER
FROM STUDENT
WHERE EMPLOYER LIKE '%Corp.%'
ORDER BY EMPLOYER;Problem: Provide an alphabetical list of students in area code 203. List student name in the format , . ( Example, LOCKE, J. ) followed by the phone number.
SELECT (CONCAT(LAST_NAME, (CONCAT(', ', CONCAT(SUBSTR(FIRST_NAME, 1, 1), '.'))))) AS "Student Name", PHONE
FROM STUDENT
WHERE PHONE LIKE '203%'
ORDER BY "Student Name";Problem: List the name and address of all students without a phone number.
SELECT (CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME)) AS "Student Name", CONCAT(STREET_ADDRESS, CONCAT(', ', ZIP)) AS "Address"
FROM STUDENT
WHERE PHONE IS NULL
ORDER BY "Student Name";Problem: Provide a list of zip codes for New York, NY.
SELECT DISTINCT ZIP
FROM ZIPCODE
WHERE (CITY = 'New York') AND (STATE = 'NY')
ORDER BY ZIP;Problem: List the course number and location for all courses taught in building ‘M’.
SELECT COURSE_NO, LOCATION
FROM SECTION
WHERE LOCATION LIKE 'M%';Problem: Provide a list of cities, state abbreviations and full state names from the zip code table for MA, CT and WV. (You’ll need to use the CASE expression).
SELECT DISTINCT CITY, STATE,
CASE
WHEN STATE = 'CT' THEN 'Connecticut'
WHEN STATE = 'MA' THEN 'Massachusetts'
WHEN STATE = 'WV' THEN 'West Virginia'
END AS "State Full Name"
FROM ZIPCODE
WHERE STATE IN ('CT', 'MA', 'WV')
ORDER BY STATE, CITY;Problem: Create a listing containing single column address (salutation, first name, last name, address, zip) as ‘Instructor Address’ for each instructor in zip code 10025.
SELECT (CONCAT(SALUTATION, CONCAT(' ', CONCAT(FIRST_NAME, CONCAT(' ', CONCAT(LAST_NAME, CONCAT(' ', CONCAT(STREET_ADDRESS, CONCAT(' ', ZIP))))))))) AS "Instructor Address"
FROM INSTRUCTOR
WHERE ZIP = 10025;Problem: List the student id and final exam score for each student in section 87. List the scores from highest to lowest.
SELECT STUDENT_ID, NUMERIC_GRADE AS "Final Exam Score"
FROM GRADE
WHERE (SECTION_ID = 87) AND (GRADE_TYPE_CODE = 'FI')
ORDER BY NUMERIC_GRADE DESC;Problem: List the student ID, final exam (grade type code FI) score and exam result (‘PASS’ or ‘FAIL’) for all students in section 103. A final score of 85 or higher is required to pass.
SELECT STUDENT_ID, NUMERIC_GRADE AS "Final Exam Score",
CASE
WHEN NUMERIC_GRADE >= 85 THEN 'Passed'
WHEN NUMERIC_GRADE < 85 THEN 'Fail'
END AS "Pass/Fail"
FROM GRADE
WHERE (SECTION_ID = 103) AND (GRADE_TYPE_CODE = 'FI');Problem: List the first name, last name and phone number for all students that registered on 2/23/2007. Arrange the list in order of last name and first name.
SELECT FIRST_NAME, LAST_NAME, PHONE
FROM STUDENT
WHERE REGISTRATION_DATE = '23-Feb-2007'
ORDER BY LAST_NAME, FIRST_NAME;Problem: List course number, section ID and start date for all sections located in L214. Arrange by start date.
SELECT COURSE_NO, SECTION_ID, START_DATE_TIME
FROM SECTION
WHERE LOCATION = 'L214'
ORDER BY START_DATE_TIME;Problem: List the course number, section ID, start date and instructor ID for all courses with a start date in April 2007. Arrange the list by course number and section number.
SELECT COURSE_NO, SECTION_ID, START_DATE_TIME, INSTRUCTOR_ID
FROM SECTION
WHERE START_DATE_TIME LIKE '%APR-07'
ORDER BY COURSE_NO, SECTION_ID;Problem: List Student ID, Section ID and final grade for all students with a final grade that enrolled in January 2007.
SELECT STUDENT_ID, SECTION_ID, FINAL_GRADE
FROM ENROLLMENT
WHERE (FINAL_GRADE IS NOT NULL) AND (ENROLL_DATE LIKE '%JAN-07');Problem: Create a query using the Oracle Dual Table (pg 137) that returns the date of the end of the semester for courses that begin on January 3, 2011. The semester is 105 days long.
SELECT (TO_DATE('03-JAN-2011', 'DD-MON-YYYY') + 105)
FROM DUAL;Problem: Provide a list of course numbers and locations for sections being taught in the odd numbered rooms located in building M.
SELECT COURSE_NO, LOCATION
FROM SECTION
WHERE (LOCATION LIKE 'M%') AND (MOD((LTRIM(LOCATION, 'M')),2) <> 0);Problem: Provide a list of Students in zip code 11214 that registered more than 2 days after their student record was created.
SELECT STUDENT_ID, SALUTATION, FIRST_NAME, LAST_NAME
FROM STUDENT
WHERE (ZIP = '11214') AND ((REGISTRATION_DATE - CREATED_DATE) > 2);Problem: Create a list of student names and the number of years since they registered (2 decimal places). Sort the list on the number of years from highest to lowest.
SELECT SALUTATION, FIRST_NAME, LAST_NAME, TRUNC((MONTHS_BETWEEN(SYSDATE, REGISTRATION_DATE)/12),2) AS YEARS
FROM STUDENT
ORDER BY YEARS DESC;Problem: Create a list of starting times for all course sections. Eliminate duplicates. Show only the time of day as “Start Time”.
SELECT DISTINCT COURSE_NO, TO_CHAR(START_DATE_TIME, 'HH24:MI') AS "Start Time"
FROM SECTION;Problem: List the Student ID and Section ID for all students who enrolled at 10:18am.
SELECT STUDENT_ID, SECTION_ID
FROM ENROLLMENT
WHERE TO_CHAR(ENROLL_DATE, 'HH24:MI') = '10:18';Problem: Create a query that returns the average cost for all courses. Round to two places.
SELECT ROUND(AVG(COST),2) AS "Average Cost"
FROM COURSE;Problem: Create a query that returns the total number of Students that registered during February 2007. Alias the column as “February_Registrations”.
SELECT COUNT(STUDENT_ID) AS "February Registrations"
FROM STUDENT
WHERE REGISTRATION_DATE LIKE '%FEB-07';Problem: Create a query that returns the average, highest and lowest final exam scores for Section 147.
SELECT AVG(NUMERIC_GRADE) AS "Average", MAX (NUMERIC_GRADE) AS "Highest Grade", MIN(NUMERIC_GRADE) AS "Lowest Grade"
FROM GRADE;
OR
SELECT ROUND(AVG(NUMERIC_GRADE),2) AS "Average", MAX (NUMERIC_GRADE) AS "Highest Grade", MIN(NUMERIC_GRADE) AS "Lowest Grade"
FROM GRADE;Problem: List the city, state and number of zip codes for all cities with more than one zip code. Arrange by state and city.
SELECT COUNT(ZIP), CITY, STATE
FROM ZIPCODE
GROUP BY CITY, STATE
HAVING COUNT(ZIP) > 1
ORDER BY COUNT(ZIP) DESC;Problem: Provide a list of Sections and the number of students enrolled in those sections for students who enrolled on 2/21/2007. Sort the output from highest to lowest on the number of students enrolled.
SELECT SECTION_ID, ENROLL_DATE, COUNT(STUDENT_ID) AS "Number of Students"
FROM ENROLLMENT
WHERE ENROLL_DATE LIKE '21-FEB-07'
GROUP BY SECTION_ID, ENROLL_DATE
ORDER BY COUNT(STUDENT_ID) DESC;Problem: Create a query listing the Student ID, Section ID and average grade for all students in section 86. Sort your list on the student ID and display all of the average grades as a number with four decimal places.
SELECT STUDENT_ID, SECTION_ID, ROUND(AVG(NUMERIC_GRADE),4)
FROM GRADE
WHERE SECTION_ID = 86
GROUP BY STUDENT_ID, SECTION_ID
ORDER BY STUDENT_ID;Problem: Create a query to determine the number of sections that student ID 250 is enrolled in. Your output should contain the student ID and the number of sections enrolled.
SELECT STUDENT_ID, COUNT(SECTION_ID) AS "Number of Sections"
FROM GRADE
WHERE STUDENT_ID = 250
GROUP BY STUDENT_ID;Problem: List the section ID and lowest quiz score for all sections where the low score is less than a B (less than 80).
SELECT SECTION_ID, MIN(NUMERIC_GRADE)
FROM GRADE
WHERE GRADE_TYPE_CODE = 'QZ' AND NUMERIC_GRADE < 80
GROUP BY SECTION_ID
ORDER BY MIN(NUMERIC_GRADE);Problem: List the names of employers who employ more than five students. Your output should contain the employer name and the number of student employees.
SELECT EMPLOYER, COUNT(STUDENT_ID) AS "Number of Students"
FROM STUDENT
GROUP BY EMPLOYER
HAVING COUNT(STUDENT_ID) > 5;Problem: List the section ID, number of participation grades and the lowest participation grade for all sections that have more than 15 participation grades.
SELECT SECTION_ID, COUNT(GRADE_TYPE_CODE) AS "Number of Participation Grades", MIN(NUMERIC_GRADE) AS "Lowest Grade"
FROM GRADE
WHERE GRADE_TYPE_CODE = 'PA'
GROUP BY SECTION_ID
HAVING COUNT(GRADE_TYPE_CODE) > 15
ORDER BY "Number of Participation Grades" DESC;Problem: List the course number, description and cost for all courses taught by Charles Lowry. Arrange the list in order of course number.
SELECT SECTION.COURSE_NO, COURSE.DESCRIPTION, COURSE.COST
FROM SECTION, COURSE, INSTRUCTOR
WHERE SECTION.COURSE_NO = COURSE.COURSE_NO AND SECTION.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID AND
INSTRUCTOR.FIRST_NAME = 'Charles' AND INSTRUCTOR.LAST_NAME = 'Lowry'
ORDER BY SECTION.COURSE_NO;Problem: List the full name and phone number for students that live in Newark, NJ.
SELECT CONCAT(CONCAT(STUDENT.FIRST_NAME, ' '), STUDENT.LAST_NAME) AS "Student Name", STUDENT.PHONE
FROM STUDENT, ZIPCODE
WHERE STUDENT.ZIP = ZIPCODE.ZIP AND ZIPCODE.CITY LIKE 'Newark'
ORDER BY "Student Name";Problem: Provide a listing of courses that include projects as a part of their grade.
SELECT DISTINCT(SECTION.COURSE_NO)
FROM GRADE, SECTION
WHERE GRADE.SECTION_ID = SECTION.SECTION_ID AND GRADE.GRADE_TYPE_CODE = 'PJ'
ORDER BY SECTION.COURSE_NO;Problem: List the grade type code, description and number per section of all grades in course 144. Arrange by description.
SELECT GRADE.GRADE_TYPE_CODE, GRADE_TYPE.DESCRIPTION, COUNT(SECTION.SECTION_ID) AS "Number of Grades"
FROM GRADE, GRADE_TYPE, SECTION
WHERE GRADE.GRADE_TYPE_CODE = GRADE_TYPE.GRADE_TYPE_CODE AND GRADE.SECTION_ID = SECTION.SECTION_ID AND SECTION.COURSE_NO = 145
GROUP BY GRADE.GRADE_TYPE_CODE, GRADE_TYPE.DESCRIPTION
ORDER BY GRADE_TYPE.DESCRIPTION;Problem: Provide a list of students (student ID and name) that have an overall grade average of 89 or higher. The name should be one column, last name then first and sorted on last name plus first name.
SELECT CONCAT(CONCAT(STUDENT.LAST_NAME, ', '), STUDENT.FIRST_NAME) AS "Student Name", ROUND(AVG(GRADE.NUMERIC_GRADE),2) AS "Average Grade"
FROM STUDENT, GRADE
WHERE STUDENT.STUDENT_ID = GRADE.STUDENT_ID
GROUP BY STUDENT.LAST_NAME, STUDENT.FIRST_NAME
HAVING AVG(GRADE.NUMERIC_GRADE) > 89
ORDER BY STUDENT.LAST_NAME, STUDENT.FIRST_NAME;Problem: List the course number and number of students enrolled in courses that don’t have a prerequisite. Sort the list by course number.
SELECT COURSE.COURSE_NO, COUNT(ENROLLMENT.STUDENT_ID) AS "Number of Students Enrolled"
FROM COURSE, ENROLLMENT, SECTION
WHERE SECTION.COURSE_NO = COURSE.COURSE_NO AND SECTION.SECTION_ID = ENROLLMENT.SECTION_ID AND COURSE.PREREQUISITE IS NULL
GROUP BY COURSE.COURSE_NO;Problem: List the names and address (including city and state) for all faculty who have taught less than 10 course sections.
SELECT INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME, INSTRUCTOR.STREET_ADDRESS, ZIPCODE.CITY, ZIPCODE.STATE, INSTRUCTOR.ZIP, COUNT(SECTION.COURSE_NO)
FROM INSTRUCTOR, SECTION, ZIPCODE
WHERE INSTRUCTOR.INSTRUCTOR_ID = SECTION.INSTRUCTOR_ID AND INSTRUCTOR.ZIP = ZIPCODE.ZIP
GROUP BY INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME, INSTRUCTOR.STREET_ADDRESS, ZIPCODE.CITY, ZIPCODE.STATE, INSTRUCTOR.ZIP
HAVING COUNT(SECTION.COURSE_NO) < 10;Problem: List the single highest grade on the final exam that was given to a student in course 145.
SELECT MAX(GRADE.NUMERIC_GRADE) AS "High Score for Course 145"
FROM GRADE, GRADE_TYPE, SECTION
WHERE GRADE.GRADE_TYPE_CODE = GRADE_TYPE.GRADE_TYPE_CODE AND GRADE.SECTION_ID = SECTION.SECTION_ID AND GRADE_TYPE.DESCRIPTION = 'Final' AND SECTION.COURSE_NO = 145;Problem: For all 200 level courses (200-299), list the course number, prerequisite course number and prerequisite course description. Sort by course number.
SELECT C1.COURSE_NO, C1.PREREQUISITE AS "Prerequisite Course Number", C2.DESCRIPTION AS "Prerequisite Description"
FROM COURSE C1 JOIN COURSE C2
ON C1.PREREQUISITE = C2.COURSE_NO
WHERE (C1.COURSE_NO BETWEEN 200 AND 299) AND (C1.PREREQUISITE IS NOT NULL)
ORDER BY C1.COURSE_NO;Problem: Provide an alphabetic list of students (full name and phone number) from Flushing, NY who enrolled prior to 10:20am on February 2, 2007.
SELECT DISTINCT(CONCAT(CONCAT(STUDENT.FIRST_NAME, ' '), STUDENT.LAST_NAME)) AS "Student Name", STUDENT.PHONE
FROM STUDENT, ZIPCODE, ENROLLMENT
WHERE (STUDENT.ZIP = ZIPCODE.ZIP) AND (ZIPCODE.CITY = 'Flushing') AND (ZIPCODE.STATE = 'NY') AND (ENROLLMENT.ENROLL_DATE <= TO_DATE('02-FEB-2007 10:20:00', 'dd-mon-yyyy HH24:MI:SS'))
ORDER BY "Student Name";Problem: List the student ID of the student that has enrolled in the most sections of 100 level courses (100 – 199).
SELECT STUDENT_ID
FROM ENROLLMENT
HAVING COUNT(STUDENT_ID) = (
SELECT MAX(COUNT(STUDENT_ID))
FROM ENROLLMENT
WHERE SECTION_ID BETWEEN 100 AND 199
GROUP BY STUDENT_ID
)
GROUP BY STUDENT_ID;Problem: Provide the student_id and name of the student(s) that scored highest on the final exam (FI) in course 230 section 99.
SELECT STUDENT_ID, FIRST_NAME, LAST_NAME
FROM STUDENT
WHERE STUDENT_ID = (
SELECT STUDENT_ID FROM (
SELECT * FROM (
SELECT GRADE.STUDENT_ID, GRADE.NUMERIC_GRADE
FROM GRADE, SECTION
WHERE (GRADE.SECTION_ID = SECTION.SECTION_ID) AND (GRADE.SECTION_ID = 99) AND (SECTION.COURSE_NO = 230) AND (GRADE_TYPE_CODE = 'FI')
ORDER BY GRADE.NUMERIC_GRADE DESC
)
WHERE ROWNUM=1
));
OR
SELECT STUDENT.LAST_NAME, STUDENT.FIRST_NAME, GRADE.STUDENT_ID, GRADE.NUMERIC_GRADE
FROM GRADE, SECTION, STUDENT
WHERE STUDENT.STUDENT_ID = GRADE.STUDENT_ID AND SECTION.SECTION_ID = GRADE.SECTION_ID
AND GRADE.NUMERIC_GRADE = (SELECT MAX(NUMERIC_GRADE) FROM GRADE)
AND GRADE.GRADE_TYPE_CODE = 'FI' AND GRADE.SECTION_ID = 99
AND SECTION.COURSE_NO = 230;Problem: List the name and phone number of instructors who have never taught a course section.
SELECT INSTRUCTOR_ID, FIRST_NAME, LAST_NAME
FROM INSTRUCTOR
WHERE INSTRUCTOR_ID NOT IN (
SELECT INSTRUCTOR_ID
FROM SECTION)
ORDER BY INSTRUCTOR_ID;Problem: Generate an alphabetic listing containing the last names and final exam grade (FI) of students who scored above average on the final exam for section 89.
SELECT STUDENT.LAST_NAME, GRADE.NUMERIC_GRADE
FROM STUDENT, GRADE
WHERE (STUDENT.STUDENT_ID = GRADE.STUDENT_ID)
AND (GRADE.SECTION_ID = 89)
AND (GRADE.GRADE_TYPE_CODE = 'FI')
AND (GRADE.NUMERIC_GRADE > (
SELECT AVG(NUMERIC_GRADE)
FROM GRADE))
ORDER BY STUDENT.LAST_NAME;Problem: List the course number and course description of the course with the highest number of enrolled students.
SELECT COURSE_NO, DESCRIPTION
FROM COURSE
WHERE COURSE_NO = (
SELECT COURSE_NO
FROM SECTION
WHERE SECTION_ID IN (
SELECT DISTINCT SECTION_ID
FROM ENROLLMENT
)
GROUP BY COURSE_NO
HAVING COUNT(COURSE_NO) = (
SELECT MAX(COUNT(COURSE_NO))
FROM SECTION
WHERE SECTION_ID IN (
SELECT DISTINCT SECTION_ID
FROM ENROLLMENT
)
GROUP BY COURSE_NO
));Problem: List course number and course description for all courses that don’t have at least one 9:30AM section. Sort by course number.
SELECT COURSE_NO, DESCRIPTION
FROM COURSE
WHERE COURSE_NO NOT IN (
SELECT COURSE_NO
FROM SECTION
WHERE TO_CHAR(START_DATE_TIME, 'HH:MI') = '09:30'
)
ORDER BY COURSE_NO;Problem: List the student_id and last name of the students who received an above average grade on Quiz #3 in section 120.
SELECT STUDENT.STUDENT_ID, STUDENT.LAST_NAME
FROM GRADE, STUDENT
WHERE GRADE.STUDENT_ID = STUDENT.STUDENT_ID
AND GRADE.GRADE_TYPE_CODE = 'QZ'
AND GRADE.GRADE_CODE_OCCURRENCE = 3
AND GRADE.SECTION_ID = 120
AND GRADE.NUMERIC_GRADE > (
SELECT AVG(NUMERIC_GRADE)
FROM GRADE
WHERE GRADE_TYPE_CODE = 'QZ'
AND GRADE_CODE_OCCURRENCE = 3
AND SECTION_ID = 120)
ORDER BY STUDENT.STUDENT_ID;Problem: List the student id, last name and the final exam grade for students in section 142.
SELECT STUDENT.STUDENT_ID, STUDENT.LAST_NAME, GRADE.NUMERIC_GRADE
FROM GRADE, STUDENT
WHERE STUDENT.STUDENT_ID = GRADE.STUDENT_ID
AND STUDENT.STUDENT_ID IN (
SELECT ENROLLMENT.STUDENT_ID
FROM ENROLLMENT, SECTION
WHERE ENROLLMENT.SECTION_ID = SECTION.SECTION_ID
)
AND GRADE.SECTION_ID = 142
AND GRADE.GRADE_TYPE_CODE = 'FI';Problem: List the names of students who have taken both the Systems Analysis class and the Project Management class.
SELECT FIRST_NAME, LAST_NAME
FROM STUDENT
WHERE STUDENT_ID IN (
SELECT DISTINCT STUDENT_ID
FROM ENROLLMENT
WHERE SECTION_ID IN (
SELECT SECTION_ID
FROM SECTION
WHERE COURSE_NO IN (
SELECT COURSE_NO
FROM COURSE
WHERE DESCRIPTION = 'Systems Analysis' OR DESCRIPTION = 'Project Management')))
ORDER BY LAST_NAME;Problem: List the instructor name, course number and course description of the Java courses that have been taught by the Instructor that has taught the most Java courses.
SELECT DISTINCT COURSE_NO, INSTRUCTOR_ID
FROM SECTION
WHERE COURSE_NO IN (
SELECT COURSE_NO
FROM COURSE
WHERE DESCRIPTION LIKE '%Java%'
)
ORDER BY INSTRUCTOR_ID, COURSE_NO;Problem: List the names of instructors who have not used projects (PJ) as a basis for grading in their courses.
SELECT FIRST_NAME, LAST_NAME
FROM INSTRUCTOR
WHERE INSTRUCTOR_ID NOT IN (
SELECT SECTION.INSTRUCTOR_ID
FROM SECTION, GRADE
WHERE SECTION.SECTION_ID = GRADE.SECTION_ID
AND GRADE.GRADE_TYPE_CODE = 'PJ'
)
ORDER BY LAST_NAME;Problem: Determine the number of students who received a below average grade on the final exam (FI) in section 86.
SELECT COUNT(*) AS "Number of Students"
FROM GRADE
WHERE SECTION_ID = 86
AND GRADE_TYPE_CODE = 'FI'
AND NUMERIC_GRADE < (
SELECT AVG(NUMERIC_GRADE)
FROM GRADE
WHERE GRADE_TYPE_CODE = 'FI'
AND SECTION_ID = 86
);Problem: List the city and state that have the highest number of students enrolled.
SELECT CITY, STATE
FROM ZIPCODE
WHERE ZIP = (
SELECT ZIP
FROM STUDENT
WHERE STUDENT_ID IN (
SELECT DISTINCT STUDENT_ID
FROM ENROLLMENT
)
GROUP BY ZIP
HAVING COUNT(ZIP) = (
SELECT MAX(COUNT(ZIP))
FROM STUDENT
WHERE STUDENT_ID IN (
SELECT DISTINCT STUDENT_ID
FROM ENROLLMENT
)
GROUP BY ZIP
));Problem: Provide the student_id, name and final exam grade of the student(s) that scored highest on the final exam (FI) in course section 81.
SELECT STUDENT.STUDENT_ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME, GRADE.NUMERIC_GRADE
FROM STUDENT, GRADE
WHERE STUDENT.STUDENT_ID = GRADE.STUDENT_ID
AND STUDENT.STUDENT_ID = (
SELECT STUDENT_ID
FROM GRADE
WHERE NUMERIC_GRADE = (
SELECT MAX(NUMERIC_GRADE)
FROM GRADE
WHERE SECTION_ID = 81 AND GRADE_TYPE_CODE = 'FI'
)
AND SECTION_ID = 81
AND GRADE_TYPE_CODE = 'FI'
)
AND GRADE.SECTION_ID = 81
AND GRADE_TYPE_CODE = 'FI';Problem: List the student id and name of students who have taken the same course more than once .
SELECT STUDENT_ID, FIRST_NAME, LAST_NAME
FROM STUDENT
WHERE STUDENT_ID IN (
SELECT ENROLLMENT.STUDENT_ID
FROM SECTION, ENROLLMENT
WHERE SECTION.SECTION_ID = ENROLLMENT.SECTION_ID
HAVING COUNT(SECTION.COURSE_NO) > 1
GROUP BY ENROLLMENT.STUDENT_ID, SECTION.COURSE_NO
)
ORDER BY STUDENT_ID, LAST_NAME;Problem: Provide a list of students that have enrolled in a course that allowed them an opportunity to drop a lowest score.
SELECT FIRST_NAME, LAST_NAME
FROM STUDENT
WHERE STUDENT_ID IN (
SELECT ENROLLMENT.STUDENT_ID
FROM ENROLLMENT, SECTION
WHERE ENROLLMENT.SECTION_ID = SECTION.SECTION_ID
AND ENROLLMENT.SECTION_ID IN (
SELECT SECTION_ID
FROM GRADE_TYPE_WEIGHT
WHERE DROP_LOWEST = 'Y'
)
)
ORDER BY LAST_NAME;Problem: List the names and phone numbers of instructors who teach students that live in same zipcode as the instructor.
SELECT INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME, INSTRUCTOR.PHONE
FROM INSTRUCTOR, STUDENT, SECTION, ENROLLMENT
WHERE INSTRUCTOR.ZIP = STUDENT.ZIP
AND INSTRUCTOR.INSTRUCTOR_ID = SECTION.INSTRUCTOR_ID
AND STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID
AND ENROLLMENT.SECTION_ID = SECTION.SECTION_ID
ORDER BY INSTRUCTOR.LAST_NAME;Problem: Provide a list of names and cities of students and instructors that live in zipcode 10025. Identify each person’s role as either “Student” or “Instructor”. Sort the list by role, last name and first name.
(SELECT STUDENT.FIRST_NAME AS "First Name", STUDENT.LAST_NAME as "Last Name", 'Student' AS ROLE, ZIPCODE.CITY
FROM STUDENT, ZIPCODE
WHERE STUDENT.ZIP = ZIPCODE.ZIP AND ZIPCODE.ZIP = 10025)
UNION
(SELECT INSTRUCTOR.FIRST_NAME AS "First Name", INSTRUCTOR.LAST_NAME as "Last Name", 'Instructor' AS ROLE, ZIPCODE.CITY
FROM INSTRUCTOR, ZIPCODE
WHERE INSTRUCTOR.ZIP = ZIPCODE.ZIP AND ZIPCODE.ZIP = 10025)
ORDER BY ROLE, "Last Name", "First Name";Problem: Create a query that lists location, number of sections taught in that location and number of students enrolled in courses at that location. Sort by location.
SELECT LOCATION, COUNT(COURSES) AS COURSES, COUNT(STUDENTS) AS STUDENTS
FROM (
(SELECT LOCATION, COUNT(COURSE_NO) AS COURSES, NULL AS STUDENTS
FROM SECTION
GROUP BY LOCATION, COURSE_NO)
UNION ALL
(SELECT SECTION.LOCATION AS LOCATION, NULL AS COURSES, COUNT(ENROLLMENT.SECTION_ID) AS STUDENTS
FROM ENROLLMENT, SECTION
WHERE ENROLLMENT.SECTION_ID = SECTION.SECTION_ID
GROUP BY SECTION.LOCATION, ENROLLMENT.SECTION_ID))
GROUP BY LOCATION
ORDER BY LOCATION;Problem: Create a query that shows all of the individual grades for student 112 in section 95 and also the average of those grades. The individual grades should come first with the average at the bottom. List the grade type code and numeric grade. The average row should have a caption of, “Average for student 112”.
SELECT GRADE_TYPE_CODE, NUMERIC_GRADE
FROM GRADE
WHERE STUDENT_ID = 112
AND SECTION_ID = 95
UNION ALL
SELECT 'Average for Student 112', ROUND(AVG(NUMERIC_GRADE), 2)
FROM GRADE
WHERE STUDENT_ID = 112
AND SECTION_ID = 95;Problem: Create an alphabetic listing of all instructors and the number of sections that they have taught. The number of sections can be null for instructors that have not taught any sections.
SELECT INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME, COUNT(SECTION.SECTION_ID) AS "Sections Taught"
FROM INSTRUCTOR
FULL JOIN SECTION ON INSTRUCTOR.INSTRUCTOR_ID = SECTION.INSTRUCTOR_ID
GROUP BY INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME
ORDER BY INSTRUCTOR.LAST_NAME, INSTRUCTOR.FIRST_NAME;Problem: List the course number and description of courses wherein students have received grades for every one of the possible grade types. Order by Course Number.
SELECT DISTINCT COURSE.COURSE_NO, COURSE.DESCRIPTION
FROM COURSE
LEFT JOIN SECTION
ON COURSE.COURSE_NO = SECTION.COURSE_NO
WHERE SECTION.SECTION_ID IN (
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'FI'
UNION
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'HM'
UNION
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'MI'
UNION
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'PA'
UNION
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'PJ'
UNION
SELECT GRADE.SECTION_ID
FROM GRADE
LEFT JOIN SECTION
ON GRADE.SECTION_ID = SECTION.SECTION_ID
WHERE GRADE.GRADE_TYPE_CODE = 'QZ'
)
ORDER BY COURSE_NO;Problem: List all of the zip codes in Astoria, NY and the number of students from each of the zip codes. Sort by zip code. Show 0 for zip codes with no students enrolled.
SELECT ZIPCODE.ZIP, COUNT(STUDENT.STUDENT_ID)
FROM STUDENT
FULL JOIN ZIPCODE
ON STUDENT.ZIP = ZIPCODE.ZIP
WHERE ZIPCODE.CITY = 'Astoria' AND ZIPCODE.STATE = 'NY'
GROUP BY ZIPCODE.ZIP
ORDER BY ZIPCODE.ZIP;Problem: List course number and description all of the Java courses and the total number of enrollments for each course. Arrange by course number. Show zero for courses with no students enrolled.
SELECT COURSE.COURSE_NO, COURSE.DESCRIPTION, COUNT(SECTION.COURSE_NO) AS "Number of Enrollments"
FROM COURSE
FULL JOIN SECTION
ON COURSE.COURSE_NO = SECTION.COURSE_NO
FULL JOIN ENROLLMENT
ON SECTION.SECTION_ID = ENROLLMENT.SECTION_ID
WHERE COURSE.DESCRIPTION LIKE '%Java%'
GROUP BY SECTION.COURSE_NO, COURSE.COURSE_NO, COURSE.DESCRIPTION
ORDER BY COURSE.COURSE_NO;Problem: List student ID and Name of all of the students in area code 212 along with the number of enrollments each has. If the students has not enrolled in any courses, display the word “none” in the enrollments column. Alphabetize the list on last name and first name.
SELECT STUDENT.STUDENT_ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME, (CASE WHEN COUNT(ENROLLMENT.SECTION_ID) > 0 THEN TO_CHAR(COUNT(ENROLLMENT.SECTION_ID)) ELSE 'None' END) AS "Enrollments"
FROM STUDENT
FULL JOIN ENROLLMENT
ON STUDENT.STUDENT_ID = ENROLLMENT.STUDENT_ID
WHERE STUDENT.PHONE LIKE '212%'
GROUP BY STUDENT.STUDENT_ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME
ORDER BY STUDENT.LAST_NAME, STUDENT.FIRST_NAME;Problem: Insert a new instructor: Mr. John Locke with an ID of 815. His address is 2342 Oceanic Way, Bayonne, NJ, 07002. He doesn’t have a phone number.
INSERT INTO INSTRUCTOR (
INSTRUCTOR_ID,
SALUTATION,
FIRST_NAME,
LAST_NAME,
STREET_ADDRESS,
ZIP,
CREATED_BY,
CREATED_DATE,
MODIFIED_BY,
MODIFIED_DATE
) VALUES (
815,
'Mr.',
'John',
'Locke',
'2342 Oceanic Way',
07002,
'Frank Olson',
SYSDATE,
'Frank Olson',
SYSDATE
);Problem: Create a new section ID of 48 for Mr. Locke. He’ll be teaching section 4 of the Project Management Course (142) beginning on September 22, 2011 at 8:15am in Room L211. The capacity is 15 students.
INSERT INTO SECTION (
SECTION_ID,
COURSE_NO,
SECTION_NO,
START_DATE_TIME,
LOCATION,
INSTRUCTOR_ID,
CAPACITY,
CREATED_BY,
CREATED_DATE,
MODIFIED_BY,
MODIFIED_DATE
) VALUES (
48,
142,
4,
TO_DATE('2011/09/22 09:15:00', 'yyyy/mm/dd hh24:mi:ss'),
'L211L',
815,
15,
'Frank Olson',
SYSDATE,
'Frank Olson',
SYSDATE
);Problem: Enroll students 375, 137, 266 and 382 in the course. Their enrollment date should be the current date. (Use ONE INSERT statement).
INSERT ALL
INTO ENROLLMENT (STUDENT_ID, SECTION_ID, ENROLL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES (375, 48, SYSDATE, 'Frank Olson', SYSDATE, 'Frank Olson', SYSDATE)
INTO ENROLLMENT (STUDENT_ID, SECTION_ID, ENROLL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES (137, 48, SYSDATE, 'Frank Olson', SYSDATE, 'Frank Olson', SYSDATE)
INTO ENROLLMENT (STUDENT_ID, SECTION_ID, ENROLL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES (266, 48, SYSDATE, 'Frank Olson', SYSDATE, 'Frank Olson', SYSDATE)
INTO ENROLLMENT (STUDENT_ID, SECTION_ID, ENROLL_DATE, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
VALUES (362, 48, SYSDATE, 'Frank Olson', SYSDATE, 'Frank Olson', SYSDATE)
SELECT * FROM DUAL;Problem: Remove the enrollment for student 147 from section 120. Note the error. You cannot delete the enrollment record because there are foreign keys in the Grade table that depend on the Enrollment table records, thus creating an integrity constraint violation. First remove all grades for student 147, section 120 and then remove remove the enrollment for student 147, section 120. (Use two DELETE statements).
DELETE FROM GRADE
WHERE STUDENT_ID = 147 AND SECTION_ID = 120;
DELETE FROM ENROLLMENT
WHERE STUDENT_ID = 147 AND SECTION_ID = 120;Problem: Use the same procedure to remove the enrollment record for student 180 in section 119.
DELETE FROM GRADE
WHERE STUDENT_ID = 180 AND SECTION_ID = 119;
DELETE FROM ENROLLMENT
WHERE STUDENT_ID = 180 AND SECTION_ID = 119;Problem: Change Mr. Locke’s (ID 815) phone number to be 4815162342.
UPDATE INSTRUCTOR
SET PHONE = '4815162342'
WHERE INSTRUCTOR_ID = 815;Problem: Change the grade on the first homework (HM) assignment for each student in section 119 to 100.
UPDATE GRADE
SET NUMERIC_GRADE = 100
WHERE GRADE_TYPE_CODE = 'HM'
AND GRADE_CODE_OCCURRENCE = 1
AND SECTION_ID = 119;Problem: Increase the final exam (FI) score for each student in section 119 by 10 percent.
UPDATE GRADE
SET NUMERIC_GRADE = NUMERIC_GRADE * 1.1
WHERE GRADE_TYPE_CODE = 'FI'
AND SECTION_ID = 119;Problem: For each section of the Project Management course, list the section ID, location and number of students enrolled. Sort by section ID.
SELECT SECTION.SECTION_ID, SECTION.LOCATION, COUNT(ENROLLMENT.STUDENT_ID)
FROM SECTION
JOIN ENROLLMENT
ON SECTION.SECTION_ID = ENROLLMENT.SECTION_ID
WHERE SECTION.COURSE_NO IN (
SELECT COURSE_NO
FROM COURSE
WHERE DESCRIPTION LIKE '%Project Management%'
)
GROUP BY SECTION.SECTION_ID, SECTION.LOCATION
ORDER BY SECTION.SECTION_ID;Problem: Provide an alphabetic list of instructor names and phone numbers for instructors that teach the Project Managment course.
SELECT INSTRUCTOR.FIRST_NAME, INSTRUCTOR.LAST_NAME, INSTRUCTOR.PHONE
FROM INSTRUCTOR
JOIN SECTION
ON INSTRUCTOR.INSTRUCTOR_ID = SECTION.INSTRUCTOR_ID
WHERE SECTION.SECTION_ID IN (
SELECT SECTION.SECTION_ID
FROM SECTION
JOIN COURSE
ON SECTION.COURSE_NO = COURSE.COURSE_NO
WHERE COURSE.DESCRIPTION LIKE '%Project Management%'
)
ORDER BY INSTRUCTOR.LAST_NAME;Problem: Provide a list of students in section 119 along with their average numeric grade. Include the student id, student name and average grade. Format the average to two places. Sort by student ID.
SELECT SECTION.INSTRUCTOR_ID
FROM SECTION
JOIN ENROLLMENT
ON SECTION.SECTION_ID = ENROLLMENT.SECTION_ID
WHERE SECTION.LOCATION = 'L211'
HAVING COUNT(ENROLLMENT.STUDENT_ID) >= 3
GROUP BY SECTION.INSTRUCTOR_ID, ENROLLMENT.SECTION_ID
ORDER BY SECTION.INSTRUCTOR_ID;Problem: Create a list of instructors that have taught only the Project Management course (142). List the instructor ID, instructor name (salutation, first name, last name) and phone number.
SELECT INSTRUCTOR_ID, CONCAT(CONCAT(CONCAT(CONCAT(SALUTATION, ' '), FIRST_NAME), ' '), LAST_NAME) AS "Instructor Name", PHONE
FROM INSTRUCTOR
WHERE INSTRUCTOR_ID IN (
SELECT SECTION.INSTRUCTOR_ID
FROM SECTION
JOIN COURSE
ON SECTION.COURSE_NO = COURSE.COURSE_NO
WHERE COURSE.DESCRIPTION LIKE '%Project Management%'
MINUS
SELECT SECTION.INSTRUCTOR_ID
FROM SECTION
JOIN COURSE
ON SECTION.COURSE_NO = COURSE.COURSE_NO
WHERE COURSE.DESCRIPTION NOT LIKE '%Project Management%'
)
ORDER BY LAST_NAME;Problem: List student names, section id and course number for students that have not yet received any grades in any section.
SELECT STUDENT.FIRST_NAME, STUDENT.LAST_NAME, SECTION.SECTION_ID, SECTION.COURSE_NO
FROM STUDENT, SECTION
WHERE STUDENT.STUDENT_ID = SECTION.SECTION_ID
AND NOT EXISTS (
SELECT '*'
FROM GRADE
WHERE GRADE.STUDENT_ID = STUDENT.STUDENT_ID
)
ORDER BY STUDENT.LAST_NAME, STUDENT.FIRST_NAME, SECTION.SECTION_ID, SECTION.COURSE_NO;Problem: List each unique section start time and the number of courses (not sections) that have been taught at that time. Sort by start time.
SELECT SECTION_NO, TO_CHAR(START_DATE_TIME, 'MON/DD/YYYY HH:MI') AS "Start Time", COUNT(COURSE_NO)
FROM SECTION
GROUP BY SECTION_NO, START_DATE_TIME
ORDER BY SECTION_NO, START_DATE_TIME;