Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:05 [2023/04/05 15:31] alexandru.predescu [Example SQL queries] |
ewis:laboratoare:05 [2023/04/05 16:10] (current) alexandru.predescu |
||
|---|---|---|---|
| Line 47: | Line 47: | ||
| * Relationships can exist between tables such as: //one-to-one//, //one-to-many//, //many-to-many// and //self-referencing//. | * Relationships can exist between tables such as: //one-to-one//, //one-to-many//, //many-to-many// and //self-referencing//. | ||
| - | {{:ewis:laboratoare:table_data_draw.png?600|Table Data}} | + | {{:ewis:laboratoare:table_data_draw.png?500|Table Data}} |
| {{:ewis:laboratoare:table_definition_draw.png?600|Table Definition}} | {{:ewis:laboratoare:table_definition_draw.png?600|Table Definition}} | ||
| Line 238: | Line 238: | ||
| [WHERE conditions] | [WHERE conditions] | ||
| </code> | </code> | ||
| - | |||
| Syntax description: | Syntax description: | ||
| Line 246: | Line 245: | ||
| * column_name_k - the name of the column k | * column_name_k - the name of the column k | ||
| * conditions - some additional conditions for data to be retrieved | * conditions - some additional conditions for data to be retrieved | ||
| + | | ||
| + | == Examples == | ||
| + | |||
| + | The following examples present some SELECT queries that you can run to get data from the //company// database. | ||
| + | |||
| + | <note>To run the examples, you should add some data into the database. You can use the following script: {{:ewis:laboratoare:company_db.txt|Company Database File}} to make it easier to get started.</note> | ||
| + | |||
| + | <code sql> | ||
| + | -- List all employees from the department with id 1 -- | ||
| + | SELECT * FROM employee | ||
| + | WHERE department_id = 1; | ||
| + | </code> | ||
| + | |||
| + | <code sql> | ||
| + | -- List the employees name and salary. Show only those having the salary between 5000 and 7000 EUR and order them descending by salary -- | ||
| + | SELECT name, salary FROM employee | ||
| + | WHERE salary between 5000 and 7000 | ||
| + | ORDER BY salary DESC; | ||
| + | </code> | ||
| + | |||
| - | <spoiler The data can also be retrieved from one or more tables and there can be more complex clauses (Click to show)> | + | <note> |
| + | The data can also be retrieved from one or more tables and there can be more complex clauses. | ||
| + | <spoiler Click to learn more> | ||
| * ''GROUP BY'' - groups rows sharing a property so that an aggregate function can be applied to each group | * ''GROUP BY'' - groups rows sharing a property so that an aggregate function can be applied to each group | ||
| Line 276: | Line 297: | ||
| </code> | </code> | ||
| </spoiler> | </spoiler> | ||
| - | | ||
| - | == Examples == | ||
| - | The following examples present some SELECT queries that you can run to get data from the //company// database. | + | </note> |
| - | <note>To run the examples, you should add some data into the database. You can use the following script: {{:ewis:laboratoare:company_db.txt|Company Database File}} to make it easier to get started.</note> | ||
| - | <code sql> | ||
| - | -- List all employees from the department with id 1 -- | ||
| - | SELECT * FROM employee | ||
| - | WHERE department_id = 1; | ||
| - | </code> | ||
| - | |||
| - | <code sql> | ||
| - | -- List the employees name and salary. Show only those having the salary between 5000 and 7000 EUR and order them descending by salary -- | ||
| - | SELECT name, salary FROM employee | ||
| - | WHERE salary between 5000 and 7000 | ||
| - | ORDER BY salary DESC; | ||
| - | </code> | ||
| === UPDATE (modifying data) === | === UPDATE (modifying data) === | ||
| Line 432: | Line 438: | ||
| */ | */ | ||
| - | ** Task 0 (1p)** Evaluate the example database (case study) and describe the relationships between tables. | + | ** T0 (1p)** Evaluate the example database (case study) and describe the relationships between the following tables: albums, songs, artists, categories. |
| - | ** Task 1 (5p)** Create the example database in MySQL Workbench. See {{:ewis:laboratoare:setup.pdf|MySQL Local Setup Tutorial}} for installing MySQL on your computer and {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} for working with MySQL Workbench and creating the example database (//company// database). Take a screenshot to validate this step. | + | ** T1 (5p)** Create the example database in MySQL Workbench. See {{:ewis:laboratoare:setup.pdf|MySQL Local Setup Tutorial}} for installing MySQL on your computer and {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} for working with MySQL Workbench and creating the example database (//company// database). You can check the example presented in the DDL - CREATE TABLE section. Take a screenshot to validate this step. |
| - | ** Task 2 (2p)** Run the SQL script ({{:ewis:laboratoare:company_db.txt|Company Database File}}) to add data into the database. Take a screenshot to validate this step. | + | ** T2 (2p)** Run the SQL script ({{:ewis:laboratoare:company_db.txt|Company Database File}}) to add data into the database. Take a screenshot to validate this step. |
| - | ** Task 3 (2p)** Run 5 of the SQL queries (your choice) from the {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} and save the results for each query as screenshots or CSV files. | + | ** T3 (2p)** Run 5 of the SQL queries (your choice) from the {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} (as shown below) and save the results for each query as screenshots or CSV files. |
| + | |||
| + | <spoiler Click to view> | ||
| + | |||
| + | <code sql> | ||
| + | -- Q1. Write a query in SQL to display all the information of the employees -- | ||
| + | SELECT * FROM employee; | ||
| + | |||
| + | -- Q2. List the employees name and salary -- | ||
| + | -- Show only those having the salary between 5000 and 7000 EUR and order them descending by salary -- | ||
| + | SELECT name, salary FROM employee | ||
| + | WHERE salary BETWEEN 5000 AND 7000 | ||
| + | ORDER BY salary DESC; | ||
| + | |||
| + | -- Q3. List the employees name and the name of their department -- | ||
| + | SELECT emp.name as employee, dept.name as department FROM employee as emp | ||
| + | INNER JOIN department as dept | ||
| + | ON dept.id = emp.department_id; | ||
| + | |||
| + | -- Q4. Get the number of employees in the company -- | ||
| + | SELECT COUNT(id) as employee_count FROM employee; | ||
| + | |||
| + | -- Q5. Get the number of employees FROM department id 4 -- | ||
| + | SELECT department_id, COUNT(id) as employee_count FROM employee | ||
| + | WHERE department_id = 4 | ||
| + | GROUP BY department_id; | ||
| + | |||
| + | -- Q6. Get the number of employees for each department -- | ||
| + | SELECT department_id, COUNT(id) as employee_count FROM employee | ||
| + | GROUP BY department_id; | ||
| + | |||
| + | -- Q7. Get the average salary for each department -- | ||
| + | SELECT department_id, AVG(salary) as average_salary FROM employee | ||
| + | GROUP BY department_id; | ||
| + | |||
| + | -- Q8. Get the number of analysts FROM department id 3 -- | ||
| + | SELECT department_id, position_id, COUNT(id) as employee_count FROM employee | ||
| + | WHERE department_id = 3 AND position_id = 3; | ||
| + | |||
| + | -- Q9. Get the number of analysts for each department -- | ||
| + | SELECT department_id, position_id, COUNT(id) as employee_count FROM employee | ||
| + | WHERE position_id = 3 | ||
| + | GROUP BY department_id; | ||
| + | |||
| + | -- Q10. List the employees name and the name of their manager -- | ||
| + | SELECT manager.name as manager_name, emp.name as employee_name FROM employee as emp | ||
| + | INNER JOIN employee as manager ON emp.manager_id = manager.id | ||
| + | ORDER BY manager_name ASC; | ||
| + | |||
| + | -- Q11. List the employees name, salary, the name of their position -- | ||
| + | SELECT emp.name as employee, pos.name as position, emp.salary FROM employee as emp | ||
| + | INNER JOIN position as pos | ||
| + | ON pos.id = emp.position_id; | ||
| + | |||
| + | -- Q12. List the employees name, salary, the name of their department and position -- | ||
| + | SELECT emp.name as employee, dept.name as department, pos.name as position, emp.salary | ||
| + | FROM employee as emp | ||
| + | INNER JOIN department as dept | ||
| + | ON dept.id = emp.department_id | ||
| + | INNER JOIN position as pos | ||
| + | ON pos.id = emp.position_id; | ||
| + | |||
| + | </code> | ||
| + | |||
| + | </spoiler> | ||
| ==== Resources ==== | ==== Resources ==== | ||
