Aims:

• Learn and Practice Basic SQL.
• Backup/Export your SQL Database.

Assessment:

This practical contains 4 parts totaling 35 points and contributes 3.5% to your final course grade.

• Parts 1 & 2 are worth 12 points total.

You will need to complete all of these parts to receive full marks.

• Part 3 is worth a maximum of 23 points

You only need to complete 23 points worth of tasks in this part to achieve full marks and may do so using any combination of the tasks.

You need to complete Task 2.3 before attempting Part 3.

Before you begin

Create and checkout a new folder in your SVN repository at https://versioncontrol.adelaide.edu.au/svn/aXXXXXXX/2020/s1/wdc/prac6 . Be sure to add all your .sql files for this exercise to your new prac6 folder.

Part 1 - Setting up MySQL

Task 1.1 (2 points)

Let's get started with MySQL.

At the MySQL prompt, run the command SHOW DATABASES; and copy the output to a text file called task1-1.txt in your prac6 folder.

Guide

1. If you are not using the CS50 IDE, ignore this guide and contact your coursecoordinator.
(7 points)
2. Use the start_sql command to start the RDBMS

• The first-time start can take quite a long time.
• You should get the message SQL Server Running once it's ready to go.

3. Connect to the RDBMS using the mysql --host=127.0.0.1 command.

You will be presented with a mysql prompt

4. At the mysql prompt, run the command SHOW DATABASES; and copy the output to a text file called task1-1.txt in your prac6 folder.

Don't forget the semicolon ; at the end of each command.

5. Disconnect from the RDBMS by using the exit command.

6. Use the stop_sql command to stop the RDBMS when finished.

Part 2 - Creating and Populating a Database
Task 2.1 (7 points)
Create a database named enrolment .
Create and populate the following 3 tables:
Students

student_id	given_name	family_name	program
a1111111	Fang	Li	BE(Hons)(Soft)
a1111112	Jane	Brown	BE(Hons)(Soft)
a1111113	Bob	Smith	BCompSc
a1111114	Wei	Zhang	BCompSc

Subjects

subject_code	subject	faculty
COMP SCI 1102	Object Oriented Programming	ECMS
COMP SCI 2207	Web and Database Computing	ECMS
COMP SCI 2000	Computer Systems	ECMS
PHIL 2039	Philosophy of Mind	Arts

Enrolments

student_id	subject_code	mark
a1111111	COMP SCI 1102	62
a1111111	COMP SCI 2000	80
a1111112	COMP SCI 1102	55
a1111112	COMP SCI 2207	80
a1111113	PHIL 2039	65
a1111113	COMP SCI 1102	46
a1111114	COMP SCI 2207	67
a1111114	COMP SCI 2000	49

Note: This task will be marked once you've completed Task 2.2

Guide

Refer to the notes and live demos in Week 5, Lecture 14.

Backing Up your Database

As you work in groups or in different environments, you may be using different SQL servers or your existing database may become corrupted. Currently this would mean that you would need to manually recreate any changes made to your database and data it holds. Oh No!

To avoid these issues, you should back up your database to a file whenever you make changes to it.

Task 2.2
Backup your enrolment database to a .sql file called task2.sql .
Add task2.sql to your prac6 folder.
Guide
If you haven't already quit from your mysql client program, do so now using the exit command.

To back up one or more databases to a file, we use the mysqldump command, which looks like this:
mysqldump --host=127.0.0.1 --databases db_name_1 db_name_2 > file.sql
We can make a backup of our enrolment database by running the following:
mysqldump --host=127.0.0.1 --databases enrolment > task2.sql
This saves the contents of the enrolment database to the file task2.sql
Delete your enrolment database using the DROP DATABASE SQL command
(https://www.w3schools.com/sql/sql_drop_db.asp) .
Download this task3 SQL file.
Load the enrolment database from the task3.sql file.
List the tables in the newly loaded database.
Connect to the RDBMS, and open the enrolment database.
At the MySQL prompt, run the command SHOW TABLES; and copy the output to a text file
called task2-3.txt in your prac6 folder.
Guide
To restore a database backup, use the command:
mysql --host=127.0.0.1 < file.sql
Where file.sql is the path to your database backup file.

Part 3 - Querying your enrolment Database

We've got our database and filled it with some data, so now let's start working with it.
You need to have completed Part 2, Task 2.3 before attempting this part.
You may use any combination of the tasks in this Part to achieve the maximum of 23 points.

Task 3.1

List all subjects

Write a SQL query to list the details of all subjects

The query should output all the information of each subject.

Use a text editor to save the query in a text file named query3-1.sql
Save the query itself, not its output.

Don't forget the Semicolon ; at the end of the query.

Task 3.2

List all students' ids and programs

Write a SQL query to list the ids and programs of all students
The query should output ONLY the id of each student and the program they belong to.
Use a text editor to save the query in a text file named query3-2.sql
Save the query itself, not its output.
Don't forget the Semicolon ; at the end of the query.

Task 3.3

Who's studying Computer Science?
Write a SQL query to list the names of all students in the BCompSc Program.
The query should only list the names (given, family) of these students, no other
information.
Use a text editor to save the query in a text file named query3-3.sql
Save the query itself, not its output.
Don't forget the Semicolon ; at the end of the query.

Task 3.4

Who's enrolled in Web and Database Computing?
Write a SQL query to list the names of all students enrolled in Web and Database Computing.
The query should only list the names (given, family) of these students, no other information.
Use a text editor to save the query in a text file named query3-4.sql
Save the query itself, not its output.

Don't forget the Semicolon ; at the end of the query.

Task 3.5

Who's struggling with Computer Systems?

Write a SQL query to list the names and scores of all students currently failing (score less than 50) Computer Systems.
The query should only list the names and scores of these students, no other information.
Save the query in a file named query3-5.sql

Task 3.6

Bob Smith (a1111113) passed his Object Oriented Programming (COMP SCI 1102) Supp!
Write a SQL query to update Bob's score in Object Oriented Programming to 50.
Save the query in a file named query3-6.sql

Task 3.7

The provided database contains additional information about the course coordinator of each
subject (not included in Part 2)
Write a SQL query to list the details (attributes and their data types) of the table containing
the course coordinator information.
Save the query in a file named query3-7.sql
Tips
See this page (https://dev.mysql.com/doc/refman/8.0/en/getting-information.html) for details
on getting information about databases.

Task 3.8

Jane Brown (a1111112) is leaving the University (I'm very sad about this, Jane).
Write a SQL query to remove Jane from the subjects she is enrolled in.
Write a SQL query to remove Jane from the database.
Save both queries in a file named query3-8.sql , each on a new line.
Don't forget the Semicolon ; at the end of each query.

Task 3.9

Who have been Bob Smith's (a1111113) teachers in the ECMS faculty?
Write a SQL query to list the id and name (given, family) of each of Bob's course
coordinator.
Only list the coordinators of Subjects from the ECMS faculty.
See Task 3.7 for details on course-coordinators.
Save the query in a file named query3-9.sql

Task 3.10

Who's taking broadening electives?
5/2/2020 Practical Exercise 6
https://myuni.adelaide.edu.au/courses/54385/assignments/159491 7/7
Write a SQL query that, for each student taking one or more subjects outside of the ECMS
faculty, lists the student's name (given, family), the student's program/degree, the name of the subject, and which faculty the subject is run by.
Save the query in a file named query3-10.sql

Tips

See this SQL Inner Join (https://www.w3schools.com/sql/sql_join_inner.asp) page for how to do multiple inner joins. Note the extra parentheses.
You're Done!
Be sure to submit any .sql files you haven't already submitted.