Oracle SQL Project in Brief.....

1. There are eight tables in the schema. Write eight queries that each return one row with one column showing how many records each table has. Give the column an alias.

2. Write a query that outputs the following, where the first column shows the name of the table and the second column shows the number of records in that table:

3. Select the first and last names of all the teachers ordered by last name and then by first name.

4. Write a query that outputs the number of people associated with each school. Use aliases where appropriate. Sort by the number of people in descending order. The column headers should be SCHOOL_NAME and NUM_PEOPLE.

5. Write a query that outputs the number of students in each school. Use aliases where appropriate. Sort by the number of students in descending order. The column headers should be SCHOOL_NAME and NUM_STUDENTS.

6. Write a query that gets the full name (in one column with an alias), the address, and the city for all the people from the city of Clinton whose address field has the word "Washington" in it.

7. Write a query that finds the birth date of the oldest student.

8. Write a query that finds the first and last name, city, state, and birth date of the oldest student. Note that this query should stand alone. It should not rely on data retrieved by the previous query.

9. Write a query that returns the following columns:

a. Person's full name.

b. Person's role at the school: principal, teacher, or student.

c. Person's salary (or N/A for students).

d. Person's school name.

10. Create a report showing all the classrooms by writing a query that outputs the following columns ordered by school name, year, semester, and then subject:

a. The Classroom ID.

b. The teacher's full name.

c. The semester.

d. The year.

e. The subject.

f. The school name.

11. Create a view named classroom_students_view from the following query: (2%)

SELECT ps.first_name || ' ' || ps.last_name AS student, cs.grade,

pt.first_name || ' ' || pt.last_name AS teacher,

s.subject, c.semester, c.year

FROM people ps

JOIN students s ON ps.person_id = s.person_id

JOIN classroom_students cs ON s.student_id = cs.student_id

JOIN classrooms c ON c.classroom_id = cs.classroom_id

JOIN subjects s ON s.subject_id = c.subject_id

JOIN teachers t ON t.teacher_id = c.teacher_id

JOIN people pt ON pt.person_id = t.person_id;

12. Using the view you just created, write a query that outputs all the students in Megan Gray's spring, 2021 Science class. It should include the student's name and grade and should be ordered so that the students with the highest grades show up first.

13. Write a query that gets the full name of the teacher who teaches Science at Fayetteville-Manlius School in the spring of 2021.