Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:06 [2023/04/12 17:55] alexandru.predescu [Exercises] |
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). | + | |
| - | + | ||
| - | **1.** Using aggregate functions, write an SQL query to calculate the sum of all salaries in the company database. | + | |
| - | + | ||
| - | **2.** Using aggregate functions, write an SQL query to calculate the average salary in the company database. | + | |
| - | + | ||
| - | **3.** Write an SQL query to return a list of employees with the following information about them: | + | |
| - | + | ||
| - | * the name | + | |
| - | * the name of their department | + | |
| - | * the size of their team (number of employees within the same department) | + | |
| - | + | ||
| - | **4.** Write an SQL query to return a list of employees with the following information about them: | + | |
| - | + | ||
| - | * the name | + | |
| - | * the name of their position | + | |
| - | * the min/average/max salary of the employees on the same position | + | |
| - | + | ||
| - | **5.** Write an SQL query to return a list of employees with the following information about them: | + | |
| - | + | ||
| - | * 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. | + | **Task 2.** Solve 5 of the following queries. Present the SQL and results. |
| - | **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')) | + | - 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. | ||
| + | - Write an SQL query to return a list of employees with the following information about them: | ||
| + | * the name | ||
| + | * the name of their department | ||
| + | * the size of their team (number of employees within the same department) | ||
| + | - Write an SQL query to return a list of employees with the following information about them: | ||
| + | * the name | ||
| + | * the name of their position | ||
| + | * the min/average/max salary of the employees on the same position | ||
| + | - Write an SQL query to return a list of employees with the following information about them: | ||
| + | * the name in uppercase | ||
| + | * the location of their department | ||
| + | - Write an SQL query to return a list of employees (name, salary) having a higher salary than the average salary within the company. | ||
| + | - 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. | ||
| + | - 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')) | ||
| == 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> | ||
| Line 550: | Line 533: | ||
| * Change the example by using the //ex// variable (1/2) | * Change the example by using the //ex// variable (1/2) | ||
| - | **1.** Based on the examples, create a function to display the results for one of the selected queries in CSV format. | + | **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 556: | 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")'') | ||
| - | **2.** 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 | * Save the figures as shown in the image below | ||
