Chapter $8$ Homework

Use the MSU Human Resources database you created to do Chapter $7$ homework. If you don$$t have it$,$ you can make the database using $$MSU$\_$HR$\_$MySQL$$ script provided. Before answering the questions, be familiar with the database structure by referring to the database tables $($MSU Human resources Database Tables$)$ and database model $($ERD for MSU Human Resources Database$)$ documents.

Submission:

$$ Your answer document MUST contain SQL statement and query results for each question.

$$ Copy and paste SQL statements and their results to Microsoft Word or PDF file and submit the Word or PDF file

o Put question #$,$ too.

$$ I strongly recommend you start this homework earlier since it will take time to do$.$

$$ There will be NO extension or make$-$up given.

Note:

$$ You MUST use the SQL commands used in the textbook and the field $($column$)$ names in the tables provided; otherwise, you will NOT get any credits for the question.

$$ When you join tables, you must use the $$Join on$$ syntax or $$old$-$style$$ join which is covered in Chapter $7.$ You may read pages $275-276$ if you don$$t know what is $$old$-$style$$ join.

o Do Not use $$Natural Join$,$Inner Join$,$ or $$Join Using$$ syntax

$$ For downloading, installation, and tutorial information on MySQL$,$ refer to the $$Chapter $7$ Materials and Resources$$ folder in the Course Materials section.

Answer the following questions.

$$ Use the SQL statement asked. Otherwise, you will have zero $(0)$ points for the question.

$$ Use the SQL commands used in the textbook, otherwise, you will not get any credits for the question.

$1.$ Change the length of the department name column in the Departments table to $50$ characters. Show the result using the $$describe$$ command.

$2.$ Change the department name in the Departments table to $$Information Technology$$ for the department name entitled $$IT$.$ Show the result using the $$select $*$ command.

$3.$ Add a location to the Locations table. You choose the data for the new location. Make sure that you need to use the correct data type when entering the data. Show the result using the $$select $*$ command.

$4.$ Delete the new location you added in #$3.$ Show the result using the $$select $*$ command.

$5.$ Change the column name $$state$\_$province$$ to $$state$$ in the Locations table. Show the result using the $$describe$$ command. Make sure to use the same data type and data size as the existing one.

$6.$ Create a new table structure named $$regions$$ with $$region$\_$id$$ and $$region$\_$name$$ fields. The $$region$\_$id$$ is the primary key of the table. Show the result using the $$show tables$$ command.

$7.$ Drop the new table you created in Question #$6.$ Show the result using the $$show tables$$ command.

$8.$ Create a new table from existing tables. The new table will contain the following data: department id and department name, and the street address, city, and state data of the department location. Note: You have to use a SELECT statement in creating and entering data. Show the result using the $$select $*$ command.

$9.$ Create an index based on the field $($attribute$)$state$$ in the new table you created in question #$8.$ You don$$t need to show the results.

$10.$ Create a view containing the following data: department name and the city and state data of the department location. Show the result using the $$show tables$$ and $$select $*$ commands. The $$show tables$$ command displays the list of tables and views in the database. The $$select $*$ command displays the contents of the view.

$11.$ Delete the state column from the new table you created in question #$8.$ Show the result using the $$describe$$ command.

$12.$Create a database named $$MSU$.$ Show the result using $$show databases$$ command.