Differences
This shows you the differences between two versions of the page.
|
ewis:laboratoare:05 [2023/04/05 15:27] 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 351: | Line 357: | ||
| </note> | </note> | ||
| - | ==== Case Study ==== | + | ==== Case Study - SoundCloud ==== |
| - | This section presents some example database schemas and discussion about the way they are designed and the relationships between table, to better understand the design concepts. | + | /* This section presents some example database schemas and discussion about the way they are designed and the relationships between table, to better understand the design concepts. |
| === Blog === | === Blog === | ||
| Line 397: | Line 403: | ||
| === SoundCloud === | === SoundCloud === | ||
| + | */ | ||
| - | In this example, the schema describes a song database with songs, albums, bands and artists. | + | Here is an example database schema that describes a song database with songs, albums, bands and artists. We are interested in a discussion about the way it is designed and the relationships between tables, to better understand the design concepts. |
| * Each song may be a part of an album (//one-to-many// relationship between //songs// and //albums//), and may belong to a category (//one-to-many// relationship between //songs// and //categories//) | * Each song may be a part of an album (//one-to-many// relationship between //songs// and //albums//), and may belong to a category (//one-to-many// relationship between //songs// and //categories//) | ||
| Line 431: | Line 438: | ||
| */ | */ | ||
| - | ** 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 your setup. | + | ** T0 (1p)** Evaluate the example database (case study) and describe the relationships between the following tables: albums, songs, artists, categories. |
| - | ** Task 2 (2p)** Run the SQL script ({{:ewis:laboratoare:company_db.txt|Company Database File}}) to add data into the database | + | |
| - | ** 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. | + | |
| - | + | ||
| - | ==== Resources ==== | + | |
| - | {{:ewis:laboratoare:setup.pdf|MySQL Local Setup Tutorial}} | + | ** 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. |
| - | {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} | + | ** 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. |
| - | {{:ewis:laboratoare:company_db.txt|Company Database File}} | + | ** 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. |
| - | [[https://ladvien.com/data-analytics-mysql-localhost-setup/|Setup a Local MySQL Database]] | + | <spoiler Click to view> |
| - | + | ||
| - | [[https://www.w3schools.com/sql/default.asp|SQL Tutorial]] | + | |
| - | + | ||
| - | [[https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php|SQL exercises on employee Database]] | + | |
| - | + | ||
| - | ==== Example SQL queries ==== | + | |
| <code sql> | <code sql> | ||
| Line 508: | Line 505: | ||
| INNER JOIN position as pos | INNER JOIN position as pos | ||
| ON pos.id = emp.position_id; | ON pos.id = emp.position_id; | ||
| - | |||
| </code> | </code> | ||
| + | |||
| + | </spoiler> | ||
| + | |||
| + | ==== Resources ==== | ||
| + | |||
| + | {{:ewis:laboratoare:setup.pdf|MySQL Local Setup Tutorial}} | ||
| + | |||
| + | {{:ewis:laboratoare:workbench.pdf|MySQL Workbench Tutorial}} | ||
| + | |||
| + | {{:ewis:laboratoare:company_db.txt|Company Database File}} | ||
| + | |||
| + | [[https://ladvien.com/data-analytics-mysql-localhost-setup/|Setup a Local MySQL Database]] | ||
| + | |||
| + | [[https://www.w3schools.com/sql/default.asp|SQL Tutorial]] | ||
| + | |||
| + | [[https://www.w3resource.com/sql-exercises/employee-database-exercise/index.php|SQL exercises on employee Database]] | ||
| + | |||
| + | |||
