IVY TECH COMMUNITY COLLEGE

DBMS110 M06 Lab Assignment (19 questions; 40 points total)

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:

1. Write a simple query to view the data inserted in student table created for the Academic Database.
2. Write a query to retrieve the marks obtained by the student for each test attempted.
3. The faculty of the different Departments realized that the marks entered in test 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.
4. Display the First Name and Email Address as "The email address of is ".
5. Display the department name and the HOD of the department from the Department table.
6. Display the distinct Department ID from the Course table.
7. Display the course details for the Spring Session (Session ID 100).
8. Display the details of the students who have scored more than 93.
9. Display the course details for departments 20 and 30.
10. Display the details of students whose first name begins with the letter "M".
11. Display the details of students who have opted for courses 190 or 193.
12. Display the course details offered by department 30 for the Fall Session (Session ID 200).

SELECT * FROM Student

SELECT grade FROM Student

SELECT 7+grade FROM student;

SELECT CONCAT(" The email address of ",Firstname, " is ", email_id) AS data FROM Students;

SELECT Department_name,HOD_name FROM Department

SELECT DISTINCT Department_Id FROM Course;

SELECT * FROM Course WHERE Session_Id=100;

SELECT * FROM Student WHERE mark>93

SELECT * FROM Course WHERE Department=20 AND Department=30;

SELECT * FROM Student WHERE FirstName LIKE 'M%';

SELECT * FROM Student WHERE Course WHERE Course=190 OR Course=193

SELECT * FROM Course WHERE Department = 30 AND 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:

13. Display the name of courses offered and the name of each session it is offered in.
14. Display the name of courses offered in the Fall session.
15. Display the course details and students who have enrolled for those courses.
16. Display the course details and students who have enrolled for those courses for department 30.
17. 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, TEST_ID

FROM COURSE_NAME d JOIN TEST_RESULT a

USING (a.COURSE_ID)

18. 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, TEST_ID

FROM COURSE d JOIN TEST_RESULT a

ON a.COURSE_ID

BETWEEN 190 AND 195

19. Explain what output would be generated when the given statement is executed. Include your answers in a comment in your script.

SELECT *

FROM TEST_RESULT

CROSS JOIN TEST_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 labelled accordingly.

FACULTY STUDENT_COURSE STUDENT STUDENT_ATTENDANCE PK * Faculty_ID FACULTY_COURSE PK FK * Student_ID PK * Student_ID PK FK *Student_ID PK Faculty_Name FK *Facuty_D PK FK * Course_ID First_Name No_of_Working_Days First_Name PK FK *Course_ID Student Reg_Year No_Of_Days_Off Last_Name FK Parent_ID . Eligibility_For_Exams COURSE DEPARTMENT PARENT_INFORMATION PK * Course_ID PK * Department_ID FACUTLY_LOGIN PK * Parent ID * Course_Name Department_Name PK FK * Faculty_ID Father_Name FK Department_ID HOD PK *Login_Date Mother_Name FK Session_ID PK * Login_Time ACADEMIC_SESSION Details PK EXAM_RESULT * Session_ID PK FK *Student D Session_Name PK FK * Course_ID FK *Exam_ID PK EXAM_DETAIL EXAM_TYPE Grade PK *Exam_ID PK *Exam_Type FK Exam_Type Exam_Name . Start_Date Exam_Desc Name