Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:06 [2023/04/12 16:31] alexandru.predescu |
ewis:laboratoare:06 [2023/04/12 18:01] (current) alexandru.predescu [Exercises] |
||
|---|---|---|---|
| Line 497: | Line 497: | ||
| == 1. Databases (5p) == | == 1. Databases (5p) == | ||
| - | <note important> | + | **Task 1.** Run the example queries presented in the lab (based on the company database example). |
| - | For this part you only need MySQL Workbench and Server installed on your computer. | + | |
| - | * Pick **5** exercises (ex. 0 is required) of your choice and write the SQL query that solves the task. | + | |
| - | * Upload your results on Moodle as screenshots or CSV files for grading. | + | |
| - | </note> | + | |
| - | **0.** Run the example queries presented in the lab (that are related to the company database example). | + | **Task 2.** Solve 5 of the following queries. Present the SQL and results. |
| - | **1.** Using aggregate functions, write an SQL query to calculate the sum of all salaries in the company database. | + | - Using aggregate functions, write an SQL query to calculate the sum of all salaries in the company database. |
| - | + | - Using aggregate functions, write an SQL query to calculate the average salary in the company database. | |
| - | **2.** Using aggregate functions, write an SQL query to calculate the average salary in the company database. | + | - Write an SQL query to return a list of employees with the following information about them: |
| - | + | * the name | |
| - | **3.** Write an SQL query to return a list of employees with the following information about them: | + | * the name of their department |
| - | + | * the size of their team (number of employees within the same department) | |
| - | * the name | + | - Write an SQL query to return a list of employees with the following information about them: |
| - | * the name of their department | + | * the name |
| - | * the size of their team (number of employees within the same department) | + | * the name of their position |
| - | + | * the min/average/max salary of the employees on the same position | |
| - | **4.** Write an SQL query to return a list of employees with the following information about them: | + | - Write an SQL query to return a list of employees with the following information about them: |
| - | + | * the name in uppercase | |
| - | * the name | + | * the location of their department |
| - | * the name of their position | + | - Write an SQL query to return a list of employees (name, salary) having a higher salary than the average salary within the company. |
| - | * the min/average/max salary of the employees on the same position | + | - Write an SQL query to return a list of employees (name, salary) having a higher salary than their manager. |
| - | + | - Write an SQL query to return the employees (name, salary) having the highest salary within each department. | |
| - | **5.** Write an SQL query to return a list of employees with the following information about them: | + | - Write an SQL query to return the managers and the number of employees in their teams. |
| - | + | - Write an SQL query to list all employees and the year they were hired (hint: use the scalar function DATE_FORMAT(hire_date,'%Y')) | |
| - | * the name in uppercase | + | |
| - | * the location of their department | + | |
| - | + | ||
| - | **6.** Write an SQL query to return a list of employees (name, salary) having a higher salary than the average salary within the company. | + | |
| - | + | ||
| - | **7.** Write an SQL query to return a list of employees (name, salary) having a higher salary than their manager. | + | |
| - | + | ||
| - | **8.** Write an SQL query to return the employees (name, salary) having the highest salary within each department. | + | |
| - | + | ||
| - | **9.** Write an SQL query to return the managers and the number of employees in their teams. | + | |
| - | + | ||
| - | **10.** Write an SQL query to list all employees and the year they were hired (hint: use the scalar function DATE_FORMAT(hire_date,'%Y')) | + | |
| == 2. MySQL-Python. Charts (5p) == | == 2. MySQL-Python. Charts (5p) == | ||
| Line 541: | Line 525: | ||
| For this part you also need Python installed on your computer. | For this part you also need Python installed on your computer. | ||
| * Check out the requirements section (at the end of the page) to install the required tools and setup working with Python on your computer. | * Check out the requirements section (at the end of the page) to install the required tools and setup working with Python on your computer. | ||
| - | * Upload your results on Moodle as image files or CSV files for grading. | ||
| </note> | </note> | ||
| - | **1.** Run the example code (**lab.py**) and check out the generated CSV file that contains the data about the employees in the example database. | + | Run the example code (**lab.py**) and check out the generated CSV file that contains the data about the employees in the example database. |
| + | * Change the example by using the //ex// variable (1/2/3) | ||
| - | **2.** Run the example code (**plots.py**) to display the results of the functions as charts. Save the figures as image files. | + | Run the example code (**plots.py**) to display the results of the functions as charts. |
| - | * Change the mode by using the //ex// variable (1/2) | + | * Change the example by using the //ex// variable (1/2) |
| - | * Save the figures as shown in the image below | + | |
| - | + | ||
| - | {{:ewis:laboratoare:save_figure.png?400|}} | + | |
| - | + | ||
| - | **3.** The example code (**lab.py**) contains multiples examples. Run //example2// function to list all salaries in the company and plot them on a chart. Save the figures as image files. | + | |
| - | * Change the mode by using the //ex// variable (1/2/3) | + | |
| - | * Save the figures as shown in the image below | + | |
| - | + | ||
| - | {{:ewis:laboratoare:save_figure.png?400|}} | + | |
| - | **4.** Based on the examples, create a function to display the results for one of the selected queries as CSV file. | + | **Task 3.** Based on the examples, create a function to display the results for one of the selected queries in CSV format. |
| Hint: | Hint: | ||
| Line 564: | Line 539: | ||
| * Change the SQL query provided to the database module (''db.run_query("SELECT * FROM employee")'') | * Change the SQL query provided to the database module (''db.run_query("SELECT * FROM employee")'') | ||
| - | **5.** Based on the examples, create a function to display the results for one of the selected queries as a plot. You may plot a single column, using either plot, stem, barchart or histogram. | + | **Task 4.** Based on the examples, create a function to display the results for one of the selected queries as a plot. You may plot a single column, using either plot, stem, barchart or histogram. |
| + | * Save the figures as shown in the image below | ||
| - | Hint: | + | {{:ewis:laboratoare:save_figure.png?400|}} |
| + | |||
| + | Hints: | ||
| * Check how the example in **lab.py** works (see ex. 3) | * Check how the example in **lab.py** works (see ex. 3) | ||
| * Change the SQL query provided to the database module (''db.run_query("SELECT * FROM employee")'') | * Change the SQL query provided to the database module (''db.run_query("SELECT * FROM employee")'') | ||
| Line 573: | Line 551: | ||
| == 3. Online evaluation == | == 3. Online evaluation == | ||
| - | * <del>Upload all results (csv files, images) as a zip archive on Moodle.</del> | ||
| - | * <del>Add a readme file (readme.txt) to the archive where you describe your results</del> | ||
| * Include the results and discussion in a pdf as either text or screenshots along with the task description. | * Include the results and discussion in a pdf as either text or screenshots along with the task description. | ||
| - | |||
| <note important> | <note important> | ||
