This shows you the differences between two versions of the page.
ewis:laboratoare:05 [2023/04/05 15:56] alexandru.predescu [Task] |
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 434: | 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 440: | 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> |