Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:05 [2023/04/05 15:59] alexandru.predescu |
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 264: | Line 264: | ||
| ORDER BY salary DESC; | ORDER BY salary DESC; | ||
| </code> | </code> | ||
| + | |||
| + | |||
| + | <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 | ||
| + | * ''HAVING'' - selects from the groups defined by the ''GROUP BY'' clause | ||
| + | * ''ORDER BY'' - specifies the order in which to return the rows | ||
| + | * ''AS'' - provides an alias which can be used for tables or columns | ||
| + | |||
| + | <code sql> | ||
| + | SELECT [* | {column_name_1, column_name_2, ..., aggregate_function(expression)}] | ||
| + | FROM table_name as table_alias | ||
| + | [WHERE conditions] | ||
| + | [GROUP BY column_name_1] | ||
| + | [ORDER BY column_name_1 [ASC | DESC]] | ||
| + | </code> | ||
| + | |||
| + | Example: | ||
| + | |||
| + | <code sql> | ||
| + | -- 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; | ||
| + | |||
| + | -- 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; | ||
| + | </code> | ||
| + | </spoiler> | ||
| + | |||
| + | </note> | ||
| + | |||
| + | |||
| === UPDATE (modifying data) === | === UPDATE (modifying data) === | ||
| Line 318: | Line 355: | ||
| <note tip> | <note tip> | ||
| Because we declared the FOREIGN KEY constraints with the CASCADE option for UPDATE and DELETE, the employees will also be removed when removing the department. This is useful to keep the data consistent. Take for example the case where we remove a position (from the //positions// table). It doesn't make sense to keep the employees for the position that we want to remove, so this would be a good use case for the CASCADE constraint, as we don't have to worry about inconsistencies in the database. The other options are: RESTRICT, SET NULL, NO ACTION. | Because we declared the FOREIGN KEY constraints with the CASCADE option for UPDATE and DELETE, the employees will also be removed when removing the department. This is useful to keep the data consistent. Take for example the case where we remove a position (from the //positions// table). It doesn't make sense to keep the employees for the position that we want to remove, so this would be a good use case for the CASCADE constraint, as we don't have to worry about inconsistencies in the database. The other options are: RESTRICT, SET NULL, NO ACTION. | ||
| - | </note> | ||
| - | |||
| - | |||
| - | <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 | ||
| - | * ''HAVING'' - selects from the groups defined by the ''GROUP BY'' clause | ||
| - | * ''ORDER BY'' - specifies the order in which to return the rows | ||
| - | * ''AS'' - provides an alias which can be used for tables or columns | ||
| - | |||
| - | <code sql> | ||
| - | SELECT [* | {column_name_1, column_name_2, ..., aggregate_function(expression)}] | ||
| - | FROM table_name as table_alias | ||
| - | [WHERE conditions] | ||
| - | [GROUP BY column_name_1] | ||
| - | [ORDER BY column_name_1 [ASC | DESC]] | ||
| - | </code> | ||
| - | |||
| - | Example: | ||
| - | |||
| - | <code sql> | ||
| - | -- 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; | ||
| - | |||
| - | -- 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; | ||
| - | </code> | ||
| - | </spoiler> | ||
| - | |||
| </note> | </note> | ||
| Line 438: | Line 440: | ||
| ** T0 (1p)** Evaluate the example database (case study) and describe the relationships between the following tables: albums, songs, artists, categories. | ** T0 (1p)** Evaluate the example database (case study) and describe the relationships between the following tables: albums, songs, artists, categories. | ||
| - | ** 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). 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. |
| ** 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. | ** 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. | ||
| Line 444: | Line 446: | ||
| ** 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. | ** 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> | + | <spoiler Click to view> |
| <code sql> | <code sql> | ||
