There are 19 questions listed below. Based on the content in the M06 Readings, answer each question. After completing, save, and execute your scripts in Oracle Application Express. Upload a copy of each of your scripts to IvyLearn.
- M06_LastNameFirstName_Queries
- M06_LastNameFirstName_Joins
*Note: For your statements to work, you will need to execute the scripts provided with this assignment. To help with identifying tables that are needed, the ERD of the Academic Database has been included on the very last page of this activity.
(2 points each) Create the SQL statements for displaying the results for each of the following scenarios in the Academic Database. Save these statements in a script called M06_LastNameFirstName_ Queries (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Based on data in the Academic Database, complete the following:
- Write a simple query to view the data inserted in the student table created for the Academic Database.
- Write a query to retrieve the marks obtained by the student for each exam attempted.
- The faculty of the different Departments realized that the marks entered in Exam Results were showing a decreased value of 7 marks for each entry. Display the marks by adding 7 points to the grades obtained by each student.
- Display the First Name and Email Address as “The email address of <FIRST_NAME> is
<EMAIL_ADDR>”. - Display the department name and the HOD of the department from the Department table.
- Display the distinct Department ID from the Course table.
- Display the course details for the Spring Session (Session ID 100).
- Display the details of the students who have scored more than 93.
- Display the course details for departments 20 and 30.
- Display the details of students whose first name begins with the letter “M”.
- Display the details of students who have opted for courses 190 or 193.
- Display the course details offered by department 30 for the Fall Session (Session ID 200).
(2 points each) Create the SQL statements for displaying the results for each of the following scenarios in the Academic Database. Save these statements in a script called M06_LastNameFirstName_Joins (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. To help with identifying tables that are needed to do joins, the ERD of the Academic Database has been included on the very last page of this activity. Based on the data given (shown below) for the Academic Database, complete the following:
- Display the name of courses offered and the name of each session it is offered in.
- Display the name of courses offered in the Fall session.
- Display the course details and students who have enrolled for those courses.
- Display the course details and students who have enrolled for those courses for department 30.
- Will the given statement execute successfully? If not, what must be changed? Explain your answer in a comment in your script. Make the appropriate changes and include in the script.
SELECT COURSE_NAME, GRADE, EXAM_ID
FROM COURSE_NAME d JOIN EXAM_RESULT a
USING (a.COURSE_ID)
- Explain what output would be generated when the given statement is executed. Include your answers in a comment in your script.
SELECT COURSE_NAME, GRADE, EXAM_ID
FROM COURSE d JOIN EXAM_RESULT a
ON a.COURSE_ID
BETWEEN 190 AND 195
- Explain what output would be generated when the given statement is executed. Include your answers in a comment in your script.
SELECT *
FROM EXAM_RESULT
CROSS JOIN EXAM_TYPE
(2 points) In the script created above, include the following comments:
- At the beginning of the script include your name, the date, and the name of the script and a summary of what the script will do.
- Throughout the script, include comments to describe briefly what each section of script will do.
For fields that are marked as *, these are required/mandatory fields.
For fields that are marked as °, these are optional fields.
Primary key and foreign key fields have been labeled accordingly.
[wpecpp name=”DBMS110 ” price=”40.00″ align=”center”]
