MySQL Exercise

PART-1 MySQL Download and Installation

1.Go to https://dev.mysql.com/downloads/mysql/ (Links to an external site.)Links to an external site. to download a right copy of MySQL Community Server 5.7 and install it in your computer.

2.As a project, you may need to try and research something during installation until you can run MySQL successfully in your computer. There are many step-by-step installation guide or tutorial in YouTube, which you may find helpful.

3.When the installation is completed, run MySQL Workbench (i.e., the client, similar to Management Studio of SQL Server) and use your root login to connect to your MySQL.

4.Once connected, click on 'Management' tab in Navigator and select the first item on the list, Server Status. Details of your server is displayed on the right panel.

5.To prove you have successfully installed MySQL, please screenshot this window as shown below and save it in 4703_Project with a name 'MySQL_installed'.

PART-2 MySQL Database Scripting

1.When connected to your MySQL, open a new SQL tab.

2.Write a complete script to create a new database (also called 'schema' in MySQL) Bookstore. This database or schema should contain exactly the same pair of tables, Book and Subject, as given in the extended part of EXAM 1 in Canvas. That is, your script must contain SQL DDL statements of CREATE DATABASE and CREATE TABLE(s), plus SQL DML INSERT statements to insert 30 books and 12 subjects into these two tables, respectively. All constraints, including data types, primary keys, not null, and foreign key must be included in your script.

3.You should test your script by executing the entire page as a whole to see if it can create Bookstore database without errors. For this test, use the 3rd icon, the yellow lighting without 'I', to execute the entire script of statements as one batch. If it runs with no errors, you can click 'Schemas' tab in Navigator and right-click on the white space area inside Schemas tab and select 'Refresh All' to view your Bookstore in detail (very similar to how it works in SSMS).

4.When finished, save your script as Create_Bookstore.sql in 4703_Project folder.

PART-3 MySQL Querying

1.Open a new SQL tab when MySQL is connected.

2.Write one SQL statement to solve each of the following queries in Bookstore database.

• Query 1: Display ISBN, Title, and Subject_Code of books that have the same subject code as the book 'The Art of Walt Disney'. The output should be sorted by title from a->z. This query must be solved by using a subquery. No hard coded values are allowed in the statement except the book title 'The Art of Walt Disney'.
• Query 2: Solve Query 1 again by using a table join.
• Query 3: Update the Shelf_Location to KD-2222 for books of a Subject_Code that has the least number of books. For example, if ART and PHL subjects each contains only two books and all other subjects have three or more books, then the Shelf_Location of four books of ART and PHL should be updated. Hint: subquery
• 3.Save the script of all three queries as Query_Bookstore.sql in 4703_Project folder.

3.Save the script of all three queries as Query_Bookstore.sql in 4703_Project folder.