USING SQL ACCOMPLISH THE TASKS:

Task 1: Write a create script: 1. Using the ERD pictured below, write all the table and constraint creation statements you would need to create the database in a single file: creat.sql Make sure to end each statement with a semicolon (;) The creation scripts we have used in class are a great guide!

2. At the top of the file, write a statement to drop (if the database exists), create and use the appropriate database. Name your database school.

3. Save your script as a .sql file.

Task 2: Execute your creation scripts: 4. Run your database creation scripts on your database server. If you encounter any errors, address the issue and run the script again. This is why we start the script by first dropping the database if it already exists.

Task 3: Insert sample data: 5. Using the images below, add insert statements to your create.sql file.

CLASSES:

ENROLL:

STUDENTS:

Task 4: Access and manipulate the data: 1. Create an SQL file to do hold the queries you would write to solve the following problems.

Make sure to include a comment block at the top of the file including your name, the assignment and course Remember, your file can contain multiple SQL statements so long as you end each statement with a semicolon (;).

2. Write and test queries to solve each of the following problems. When you have a successful query, make sure to copy your SQL statement into your SQL file.

1) Find out what courses Patty Melt and Bilbo Baggins have taken or are taking. Make sure to include the students full name, class name, department, number, section as well as the students grades.

2) Luke Skywalker enrolled in INFO 2430. Insert the needed entry to track this enrollment.

3) A new student, Rory Williams, has decided to enroll in courses. Make sure his student record gets inserted. His phone number is 236-887-4061.

4) Find Rorys assigned stu_id.

5) Enroll Rory in the classes, INFO 1325 and RING 1001 by inserting to the enroll table.

6) Charlie Kelly has decided to drop INFO 2630 and go into business with his friends. Please delete his enrollment. Be careful to preserve any other earned credit!

7) Luke Skywalker earned a B in INFO 2430. Update the appropriate record to store this grade.

8) Find out if we need to close any courses due to low enrollment. Which classes dont have any students enrolled in them? Make sure to include the course name, department, number and section.

9) Get a count of how many classes each student is currently taking. Hint! Current classes are those without a final grade recorded in the enroll table.

\begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|c|}{ students } \\ \hline PK & stu id & int, auto increment \\ & fname & varchar(45) \\ & Iname & varchar(45) \\ area code & char(3) \\ phone & varchar(8) \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline \multicolumn{3}{|c|}{ classes } \\ \hline PK & class_id & int, \\ & auto_increment \\ name & varchar(45) \\ dept & char(4) \\ number & char(4) \\ section & char(2) \\ location & varchar(45) \\ meeting_time & varchar(45) \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline stu_id class_id Brade \\ \hline 000001 & 000002 A \\ \hline 000002 & 000002 \\ \hline 000005 & 000001 D \\ \hline 000005 & 000002 \\ \hline 000006 & 000005 \\ \hline 000003 & 000006 C \\ \hline \end{tabular}