Basic SQL
Assignment 3 BASIC SQL
Total points: 40
This assignment should be completed individually. For each problem, submit your SQL statements and a screen shot of the SQL results in a single pdf file. Submit the file via eLearning.
Load the database script file provided with the assignment. Before you attempt to write any SQL queries, familiarize yourself with the database structure and data. I have provided the ERD and sample data for this database.
Write queries to address each of the problems. Submit both the SQL statements and the screen shot of the outputs from Oracle. (2 points for the screen shots). Be sure your id is included in your screen shots!!!
Each problem is 5 points. Write the query in the space provided AND provide a screen shot with the query and the query results. Your login name must be visible in the screen shot.
Sample output for each question:
- Query: select * from aircraft
Screenshot: 2-login information, 3 – query, 4 – results
- What grades did students get in the Intro. To Statistics class? Display the different grades, list them only once. Display the grade, the crs_code and crs_descript. Display them in alphabetical order.
Query:
- In a single query, display the max, min and average GPA for all CIS students. Use column aliases for each column heading.
Query:
- John Smith (324299) would like a copy of his current schedule. List all the classes he is enrolled in. Display the crs code, day and time, room code, and professor first and last name.
Query:
- List all the classes and the professors (include those classes that don’t have a professor assigned). Display the course code, dept code, course description, and professor number. (Challenge – also display the professor’s last name.+2 pts)
Query:
- Provide a list of all the employees who have birthdays in October or November (emp_dob). Display all columns. BONUS: display the results in order by month. (+2 pts)
Query:
- List all the classes that have the word ‘Database’ in the course description. If the course is offered include the class section and class day and time.
Query:
- Administration wants a list of all the students who are between 2.0 and 3.0. They want to list the student information, department information and the number of pts the student needs to get to a 3.0. In your results list the student last and first name, the student dept code, dept name, school code, current gpa, and the pts needed for a 3.0 (display the column as “Pts. Needed for 3.0”). Sort the results in alphabetical order by last then first name.
Query:
- List all the pre-requisite courses for QM-362. Display the course code, the pre-requisite course code, and the course description for the pre requisite course.
Query:
