Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:06 [2023/04/12 17:59] alexandru.predescu [Exercises] |
ewis:laboratoare:06 [2023/04/12 18:01] (current) alexandru.predescu [Exercises] |
||
|---|---|---|---|
| Line 501: | Line 501: | ||
| **Task 2.** Solve 5 of the following queries. Present the SQL and results. | **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) == | ||
