Project 1 Introduction to MySQL Solved

Description

As an introduction to mysql, you will import and submit queries against a sample employee database provided with mysql. The database contains about 300,000 employee records with 2.8 million salary entries. The database size is approximately 167 MB, which is not huge, but heavy enough to be non-trivial for testing.



This project will use interview grading. 40% of your grade will be based on the submitted materials. The remaining 60% will come from the interview portion. You must schedule an interview slot with a grader. If you do not schedule or attend a slot the interview portion will be scored zero.



Procedure

In this assignment you will import this database, perform various SQL operations on it and submit the resulting output. You can find the database file on Moodle.



You are responsible for getting access to a mysql environment. You can use the CS virtual machine or any other machine you want that you can install mysql on. It is an exercise for the student to install mysql on whatever machine they choose.



In your virtual machine (or whichever Linux environment you are using), install the sample database into mysql. Follow the instructions on this github page for installation.



https://dev.mysql.com/doc/employee/en/employees-installation.html



It’s a good idea to run the validation script that generates and compares checksums to ensure your installation is correct. Do not download the database files from this site, use those provided on moodle.



Once the database is installed, start a mysql client and execute the following commands:



mysql show databases;



There should be an employees database listed.


mysql use employees;

mysql show tables;

+----------------------+

| Tables_in_employees |

+----------------------+

| current_dept_emp |

| departments |

| dept_emp |

| dept_emp_latest_date |

| dept_manager |

| employees |

| salaries |

| titles |

+----------------------+



You are encouraged to explore the database. Throw some generic queries, e.g. select * from, against various tables to look at their contents. You can use the describe query to explore the tables schemas or refer to the documentation on the mysql website for a description of the schemas.



You will create queries and redirect their output to files to be submitted on Moodle. To redirect the output of your queries to a file, append the following clause to your queries:



INTO OUTFILE <filename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'


You need to develop and execute the following queries:



We want to recognize our newest employees. Create a query that contains all the employees that have been with the company for less than a month. Assume the last employee hired was hired today. Create an output file called new_hires.csv for this query.




Create a view called high_salaries of our high salary employees. A view can be created as follows:


mysql create view <name as <query;



Define a high salary to be any employee making greater than $120,000.00 annually. This view should appear as a table in show tables; when you are finished. Once created, this view can be used like any other table. After creating the view, execute the query: select * from high_salaries and redirect the output to high_salaries.csv.







Create a view called high_salary_names that is a list of the first and last names of our high salary employees. The list should contain no duplicates and be in sorted order.


After creating the view, execute the query: select * from high_salary_names and redirect the output to high_salary_names.csv.





When you are finished, copy the text of each query you came up with into a file called queries.txt. Then put queries.txt, new_hires.csv, high_salaries.csv, and high_salary_names.csv into a directory and create a tarball. If the files in are in a directory, test, cd to the parent directory and create the tar archive:



~/fwmiller/test/$ cd ..

~/fwmiller/$ tar czvf test.tgz test



This will create a tarball called test.tgz that you should submit to Moodle.