[Solved] SDEV350 Week2

In this lab, you will demonstrate you are comfortable navigating the AWS Oracle RDS environment and creating basic Schema objects using SQL developer or similar tool. This lab is a review of basic SQL.

You must connect to the AWS Educate Oracle RDS environment and complete these task to earn credit for this lab.

Lab Requirements

1. Write and test a set of SQL statements that will drop the following tables:
   1. Engineers
   2. Faculty
   3. Classes
   4. ClassEnrollments

Be sure to use the exact table names as listed.

Note: Since you probably have not yet created there tables, you will receive an error which is Okay and expected.

2. Write and test a set of SQL statements that will create the following tables:
   1. Engineers

Primary Key: EID

Columns: Lastname, Firstname, Email, Graddate

1. Faculty

Primary Key: FID

Columns: Lastname, Firstname, Email, Hiredate

1. Classes

Primary Key: CID

Columns: Subject (e.g. SDEV), Catalognbr (e.g. 350), Title (e.g Database Security ) d. ClassEnrollments

Primary Key: EnID

Foreign Keys: CID (from Classes), FID (from Faculty), EID (from Engineers)

Be sure table and column names exactly match the requirements.

3. Write and test a set of SQL statements that will insert the following quantity of records into each table
   1. 15 Engineers
   2. 3 Faculty
   3. 3 Classes
   4. 15 ClassEnrollments

Your Primary Key IDs should always start with 1 and increment by 1. You can use an Oracle sequence but it is not required. Just hardcoding the Primary Key ID is Okay for this exercise.

4. Write and test a set of SQL statements that will select all records from each The output should display the records in descending order by Primary key.

5. Write and test a set of SQL statements that will Update records with the following specifications
   1. Update the Lastname of one faculty in the Faculty table to be Friendship.
   2. Update the Firstname of one engineer in the Engineers table to be Amadeus.
   3. Update the Subject of one class in the Classes table to be IOT Cyber.

You may need to use these requirements as you design your insert statements for step 3.

6. Write and test a SQL statement that will Delete the ClassEnrollments record with the lowest EnID

7. Write and test a SQL statement that creates a view joining the required tables such that a user can retrieve the Engineers Lastname and Firstname, the Faculty Lastname and Email and the Classess Subject and Title for each Course enrollment.