Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:05 [2023/04/05 15:57] 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 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> | ||
| + | |||
| + | <note> | ||
| The data can also be retrieved from one or more tables and there can be more complex clauses. | The data can also be retrieved from one or more tables and there can be more complex clauses. | ||
| - | <spoiler Click learn more> | + | <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 277: | 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 435: | 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 441: | 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> | ||
