MySQL for business developer - Titouan BENOIT

Software

titouan-benoit
  • MySQL for business developer Titouan BENOIT – CTO at Welp.fr [email protected]
  • MySQL for business developer– Titouan BENOIT Titouan BENOIT – CTO at Welp – [email protected] – [email protected] MySQL for business developer - v1.0 2 Titouan BENOIT mailto:[email protected] mailto:[email protected]
  • Plan MySQL for business developer - v1.0 3 MySQL for business developer– Titouan BENOIT 1. Introduction 2. Query Language 3. Basics – SELECT – WHERE – ORDER BY – DISTINCT – LIMIT & OFFSET 4. Advanced – JOIN – Sub-queries – Math functions – COUNT, SUM, AVG 5. phpMyAdmin 6. Examples/Exercices
  • MYSQL FOR BUSINESS DEVELOPER 1. Introduction 4
  • MySQL is an Open Source Relational Database Management System (RDBMS) – SQL (Structured Query Language) databases – The second most used in the world – Main purpose: storing data In this presentation you will learn to: – Make basics queries to search data into MySQL databases – Extract data from MySQL databases – Use PHPMyAdmin – Compute statistics on data (count, average, …) 1. Introduction MySQL for business developer - v1.0 5 MySQL for business developer– Titouan BENOIT
  • 1. Introduction: structure MySQL for business developer - v1.0 6 MySQL for business developer– Titouan BENOIT MySQL Server Databases Welp OHA … Tables needs propositions users … id title author_id … 1 Mon titre1 5 … 2 Mon titre2 84 … 3 Mon titre3 6 … … … … … data Columns Rows
  • MYSQL FOR BUSINESS DEVELOPER 2. Query Language 7
  • SQL (Structured Query Language) With SQL we can: – Insert data – Make queries (select data) – Update data – Delete data – Create and modify schema (tables) – Manage data access control 2. Query Language MySQL for business developer - v1.0 8 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 3. Basics 9
  • SELECT * FROM database.table; – Select all colums from table in the database – Tips: • USE database; SELECT * FROM table SELECT column1, column2, … FROM table Alias: SELECT user.email FROM fos_user AS user Example: – SELECT id, email, first_name, last_name FROM welp_db.fos_user 3. Basics: SELECT MySQL for business developer - v1.0 10 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table WHERE condition; – Select all colums from table in the database where condition is true SELECT first_name, evaluation_average FROM fos_user WHERE evaluation_average = 5 – Select users where their average is equal to 5 Comparison operators Logical Operators 3. Basics: WHERE MySQL for business developer - v1.0 11 MySQL for business developer– Titouan BENOIT Operator Description = equal < less than greater than >= greater than or equal to or != different, not equal Equal (also to NULL value) Operator Symbol Description AND && AND OR || OR XOR Exclusive OR NOT ! NOT Example:
  • SELECT * FROM table ORDER BY column1; – Select all colums from table and order by column1 descending order (default behavior) SELECT * FROM table ORDER BY column1 DESC; – Descending order SELECT * FROM table ORDER BY column1 ASC; – Ascending order SELECT * FROM table ORDER BY column1, column2, …; – Multicolumn order – Note: the order of column after ORDER BY statement is important. The order will be done on the column1 and then column2, … 3. Basics: ORDER BY MySQL for business developer - v1.0 12 MySQL for business developer– Titouan BENOIT
  • SELECT DISTINCT column FROM table; – Return only different rows (one by ‘column’ occurrences) Example: – SELECT DISTINCT species FROM animal; – If I have 300 dogs, 400 cats and 250 elephants, this query will return: • Dog • Cat • Elephant – SELECT DISTINCT first_name FROM fos_user ORDER BY first_name ASC 3. Basics: DISTINCT MySQL for business developer - v1.0 13 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table LIMIT 0,10; – Return 10 rows from the beginning (row number 0). – How to use limit: LIMIT [offset, ]number_of_lines – Note: the LIMIT statement must be at the end Other syntaxe: – LIMIT number_of_lines [OFFSET offset]; Examples 3. Basics: LIMIT & OFFSET MySQL for business developer - v1.0 14 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 4. Advanced 15
  • INNER JOIN – SELECT need.title, need.category_id, category.id, category.name FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at LIMIT 10; – Note: inner join does not select rows where the relation is null (here where need has no category for example) 4. Advanced: JOIN MySQL for business developer - v1.0 16 MySQL for business developer– Titouan BENOIT Need table Category table need category INNER JOIN
  • LEFT JOIN – SELECT user.first_name, media.id AS mediaID, media.name AS avatar FROM fos_user AS user LEFT JOIN media ON user.avatar_id = media.id ORDER BY user.created_at DESC LIMIT 10, 10 4. Advanced: JOIN MySQL for business developer - v1.0 17 MySQL for business developer– Titouan BENOIT User table Media table media LEFT JOIN
  • RIGHT JOIN – SELECT category.id as categoryID, category.name AS categoryName, event.id AS eventID, event.title AS eventTitle FROM event RIGHT JOIN category ON event.category_id = category.id 4. Advanced: JOIN MySQL for business developer - v1.0 18 MySQL for business developer– Titouan BENOIT Category table Event table Difference with LEFT JOIN This category has no event This event has no category RIGHT JOIN LEFT JOIN
  • Sub-queries – In FROM statement: • SELECT goodEvaluation.comment, goodEvaluation.evaluation, goodEvaluation.evaluator_id FROM ( SELECT * FROM evaluation WHERE evaluation.evaluation = 5 ) as goodEvaluation WHERE goodEvaluation.evaluator_id = 19 – In WHERE clause: • SELECT id, title, category_id FROM need WHERE category_id IN ( SELECT id FROM category WHERE name IN ('Actions à plusieurs', 'Visites de courtoisie') ); 4. Advanced: sub-queries MySQL for business developer - v1.0 19 MySQL for business developer– Titouan BENOIT The sub-query:
  • Math functions – CEIL(n) or CEILING(n): Return the smallest integer value not less than the argument – FLOOR(n): Return the largest integer value not greater than the argument – ROUND(n, d): Round the argument (d number of decimal) – TRUNCATE(n, d): truncate the argument (d number of decimal) – POW(n, e) or POWER(n, e): n^e – SQRT(n): square root of the argument – MOD(n, div): modulo of the division n/div – RAND(): return a random value between 0 and 1 – SIGN(): return -1 for 0 and negative values and 1 for positive value – ABS(): absolute value – PI() – COS() – SIN() 4. Advanced: Math functions MySQL for business developer - v1.0 20 MySQL for business developer– Titouan BENOIT
  • Aggregate functions: – COUNT(), SUM(), AVG(), MAX(), MIN() SELECT COUNT(*) as nbNeed FROM need WHERE need.category_id = 1 SELECT AVG(fos_user.evaluation_average) as avgEvaluation FROM fos_user GROUP BY & HAVING – SELECT u.id, u.first_name, COUNT(n.author_id) need_count FROM fos_user u LEFT JOIN need n ON n.author_id = u.id GROUP BY u.id HAVING need_count > 0 ORDER BY need_count DESC 4. Advanced: COUNT, SUM, AVG MySQL for business developer - v1.0 21 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 5. phpMyAdmin 22
  • Databases 5. phpMyAdmin MySQL for business developer - v1.0 23 MySQL for business developer– Titouan BENOIT
  • Tables 5. phpMyAdmin MySQL for business developer - v1.0 24 MySQL for business developer– Titouan BENOIT
  • Table structure 5. phpMyAdmin MySQL for business developer - v1.0 25 MySQL for business developer– Titouan BENOIT
  • Browse data 5. phpMyAdmin MySQL for business developer - v1.0 26 MySQL for business developer– Titouan BENOIT
  • SQL query 5. phpMyAdmin MySQL for business developer - v1.0 27 MySQL for business developer– Titouan BENOIT
  • Export data 5. phpMyAdmin MySQL for business developer - v1.0 28 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 6. Examples/Exercices 29
  • Requirements: – Connect to Welp phpMyAdmin – Login to prod with the read only user 1 slide with the problem at stake and the next slide the query Good luck & have fun! 6. Examples/Exercices MySQL for business developer - v1.0 30 MySQL for business developer– Titouan BENOIT
  • 1°/ Retrieve the id, title and the category_id of the 10 last created_at need 6. Examples/Exercices MySQL for business developer - v1.0 31 MySQL for business developer– Titouan BENOIT
  • 1°/ SOLUTION SELECT id, title, category_id, created_at FROM need ORDER BY created_at DESC LIMIT 10 6. Examples/Exercices MySQL for business developer - v1.0 32 MySQL for business developer– Titouan BENOIT
  • 2°/ Retrieve the id, first_name and the rank of fos_user which has a rank more than or equal to 2 and less than 5 and sort by rank (descending) 6. Examples/Exercices MySQL for business developer - v1.0 33 MySQL for business developer– Titouan BENOIT
  • 2°/ SOLUTION SELECT id, first_name, rank FROM fos_user WHERE rank >= 2 AND rank < 5 ORDER BY rank DESC 6. Examples/Exercices MySQL for business developer - v1.0 34 MySQL for business developer– Titouan BENOIT
  • 3°/ Retrieve all the distinct place_locality as city of the need and sort them by alphabetical order 6. Examples/Exercices MySQL for business developer - v1.0 35 MySQL for business developer– Titouan BENOIT
  • 3°/ SOLUTION SELECT DISTINCT(place_locality) as city FROM need ORDER BY city ASC 6. Examples/Exercices MySQL for business developer - v1.0 36 MySQL for business developer– Titouan BENOIT
  • 4°/ Retrieve the 12 last created need with their category. Display the need id, title and the category id and name. 6. Examples/Exercices MySQL for business developer - v1.0 37 MySQL for business developer– Titouan BENOIT
  • 4°/ SOLUTION SELECT need.id as needID, need.title as needTitle, need.category_id, category.id as categoryID, category.name as categoryName FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at DESC LIMIT 12 6. Examples/Exercices MySQL for business developer - v1.0 38 MySQL for business developer– Titouan BENOIT
  • 5°/ Retrieve fos_user id, first_name, last_name and for each user display their number of welpActions. Hint: use GROUP BY user.id 6. Examples/Exercices MySQL for business developer - v1.0 39 MySQL for business developer– Titouan BENOIT
  • 5°/ SOLUTION SELECT user.id, user.first_name, user.last_name, COUNT(welp_action.user_id) as WelpActions FROM fos_user as user LEFT JOIN welp_action ON user.id = welp_action.user_id GROUP BY user.id ORDER BY WelpActions DESC 6. Examples/Exercices MySQL for business developer - v1.0 40 MySQL for business developer– Titouan BENOIT
  • MySQL for business developer– Titouan BENOIT MySQL for business developer – https://en.wikipedia.org/wiki/MySQL – https://en.wikipedia.org/wiki/SQL – http://dev.mysql.com/ – https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql – http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html – http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html – http://dev.mysql.com/doc/refman/5.7/en/join.html – https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html – http://dev.mysql.com/doc/refman/5.7/en/subqueries.html – http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html MySQL for business developer - v1.0 41 SOURCES https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/SQL https://en.wikipedia.org/wiki/SQL http://dev.mysql.com/ http://dev.mysql.com/ https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/join.html http://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html
Please download to view
41
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Description
Text
  • MySQL for business developer Titouan BENOIT – CTO at Welp.fr [email protected]
  • MySQL for business developer– Titouan BENOIT Titouan BENOIT – CTO at Welp – [email protected] – [email protected] MySQL for business developer - v1.0 2 Titouan BENOIT mailto:[email protected] mailto:[email protected]
  • Plan MySQL for business developer - v1.0 3 MySQL for business developer– Titouan BENOIT 1. Introduction 2. Query Language 3. Basics – SELECT – WHERE – ORDER BY – DISTINCT – LIMIT & OFFSET 4. Advanced – JOIN – Sub-queries – Math functions – COUNT, SUM, AVG 5. phpMyAdmin 6. Examples/Exercices
  • MYSQL FOR BUSINESS DEVELOPER 1. Introduction 4
  • MySQL is an Open Source Relational Database Management System (RDBMS) – SQL (Structured Query Language) databases – The second most used in the world – Main purpose: storing data In this presentation you will learn to: – Make basics queries to search data into MySQL databases – Extract data from MySQL databases – Use PHPMyAdmin – Compute statistics on data (count, average, …) 1. Introduction MySQL for business developer - v1.0 5 MySQL for business developer– Titouan BENOIT
  • 1. Introduction: structure MySQL for business developer - v1.0 6 MySQL for business developer– Titouan BENOIT MySQL Server Databases Welp OHA … Tables needs propositions users … id title author_id … 1 Mon titre1 5 … 2 Mon titre2 84 … 3 Mon titre3 6 … … … … … data Columns Rows
  • MYSQL FOR BUSINESS DEVELOPER 2. Query Language 7
  • SQL (Structured Query Language) With SQL we can: – Insert data – Make queries (select data) – Update data – Delete data – Create and modify schema (tables) – Manage data access control 2. Query Language MySQL for business developer - v1.0 8 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 3. Basics 9
  • SELECT * FROM database.table; – Select all colums from table in the database – Tips: • USE database; SELECT * FROM table SELECT column1, column2, … FROM table Alias: SELECT user.email FROM fos_user AS user Example: – SELECT id, email, first_name, last_name FROM welp_db.fos_user 3. Basics: SELECT MySQL for business developer - v1.0 10 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table WHERE condition; – Select all colums from table in the database where condition is true SELECT first_name, evaluation_average FROM fos_user WHERE evaluation_average = 5 – Select users where their average is equal to 5 Comparison operators Logical Operators 3. Basics: WHERE MySQL for business developer - v1.0 11 MySQL for business developer– Titouan BENOIT Operator Description = equal < less than greater than >= greater than or equal to or != different, not equal Equal (also to NULL value) Operator Symbol Description AND && AND OR || OR XOR Exclusive OR NOT ! NOT Example:
  • SELECT * FROM table ORDER BY column1; – Select all colums from table and order by column1 descending order (default behavior) SELECT * FROM table ORDER BY column1 DESC; – Descending order SELECT * FROM table ORDER BY column1 ASC; – Ascending order SELECT * FROM table ORDER BY column1, column2, …; – Multicolumn order – Note: the order of column after ORDER BY statement is important. The order will be done on the column1 and then column2, … 3. Basics: ORDER BY MySQL for business developer - v1.0 12 MySQL for business developer– Titouan BENOIT
  • SELECT DISTINCT column FROM table; – Return only different rows (one by ‘column’ occurrences) Example: – SELECT DISTINCT species FROM animal; – If I have 300 dogs, 400 cats and 250 elephants, this query will return: • Dog • Cat • Elephant – SELECT DISTINCT first_name FROM fos_user ORDER BY first_name ASC 3. Basics: DISTINCT MySQL for business developer - v1.0 13 MySQL for business developer– Titouan BENOIT
  • SELECT * FROM table LIMIT 0,10; – Return 10 rows from the beginning (row number 0). – How to use limit: LIMIT [offset, ]number_of_lines – Note: the LIMIT statement must be at the end Other syntaxe: – LIMIT number_of_lines [OFFSET offset]; Examples 3. Basics: LIMIT & OFFSET MySQL for business developer - v1.0 14 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 4. Advanced 15
  • INNER JOIN – SELECT need.title, need.category_id, category.id, category.name FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at LIMIT 10; – Note: inner join does not select rows where the relation is null (here where need has no category for example) 4. Advanced: JOIN MySQL for business developer - v1.0 16 MySQL for business developer– Titouan BENOIT Need table Category table need category INNER JOIN
  • LEFT JOIN – SELECT user.first_name, media.id AS mediaID, media.name AS avatar FROM fos_user AS user LEFT JOIN media ON user.avatar_id = media.id ORDER BY user.created_at DESC LIMIT 10, 10 4. Advanced: JOIN MySQL for business developer - v1.0 17 MySQL for business developer– Titouan BENOIT User table Media table media LEFT JOIN
  • RIGHT JOIN – SELECT category.id as categoryID, category.name AS categoryName, event.id AS eventID, event.title AS eventTitle FROM event RIGHT JOIN category ON event.category_id = category.id 4. Advanced: JOIN MySQL for business developer - v1.0 18 MySQL for business developer– Titouan BENOIT Category table Event table Difference with LEFT JOIN This category has no event This event has no category RIGHT JOIN LEFT JOIN
  • Sub-queries – In FROM statement: • SELECT goodEvaluation.comment, goodEvaluation.evaluation, goodEvaluation.evaluator_id FROM ( SELECT * FROM evaluation WHERE evaluation.evaluation = 5 ) as goodEvaluation WHERE goodEvaluation.evaluator_id = 19 – In WHERE clause: • SELECT id, title, category_id FROM need WHERE category_id IN ( SELECT id FROM category WHERE name IN ('Actions à plusieurs', 'Visites de courtoisie') ); 4. Advanced: sub-queries MySQL for business developer - v1.0 19 MySQL for business developer– Titouan BENOIT The sub-query:
  • Math functions – CEIL(n) or CEILING(n): Return the smallest integer value not less than the argument – FLOOR(n): Return the largest integer value not greater than the argument – ROUND(n, d): Round the argument (d number of decimal) – TRUNCATE(n, d): truncate the argument (d number of decimal) – POW(n, e) or POWER(n, e): n^e – SQRT(n): square root of the argument – MOD(n, div): modulo of the division n/div – RAND(): return a random value between 0 and 1 – SIGN(): return -1 for 0 and negative values and 1 for positive value – ABS(): absolute value – PI() – COS() – SIN() 4. Advanced: Math functions MySQL for business developer - v1.0 20 MySQL for business developer– Titouan BENOIT
  • Aggregate functions: – COUNT(), SUM(), AVG(), MAX(), MIN() SELECT COUNT(*) as nbNeed FROM need WHERE need.category_id = 1 SELECT AVG(fos_user.evaluation_average) as avgEvaluation FROM fos_user GROUP BY & HAVING – SELECT u.id, u.first_name, COUNT(n.author_id) need_count FROM fos_user u LEFT JOIN need n ON n.author_id = u.id GROUP BY u.id HAVING need_count > 0 ORDER BY need_count DESC 4. Advanced: COUNT, SUM, AVG MySQL for business developer - v1.0 21 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 5. phpMyAdmin 22
  • Databases 5. phpMyAdmin MySQL for business developer - v1.0 23 MySQL for business developer– Titouan BENOIT
  • Tables 5. phpMyAdmin MySQL for business developer - v1.0 24 MySQL for business developer– Titouan BENOIT
  • Table structure 5. phpMyAdmin MySQL for business developer - v1.0 25 MySQL for business developer– Titouan BENOIT
  • Browse data 5. phpMyAdmin MySQL for business developer - v1.0 26 MySQL for business developer– Titouan BENOIT
  • SQL query 5. phpMyAdmin MySQL for business developer - v1.0 27 MySQL for business developer– Titouan BENOIT
  • Export data 5. phpMyAdmin MySQL for business developer - v1.0 28 MySQL for business developer– Titouan BENOIT
  • MYSQL FOR BUSINESS DEVELOPER 6. Examples/Exercices 29
  • Requirements: – Connect to Welp phpMyAdmin – Login to prod with the read only user 1 slide with the problem at stake and the next slide the query Good luck & have fun! 6. Examples/Exercices MySQL for business developer - v1.0 30 MySQL for business developer– Titouan BENOIT
  • 1°/ Retrieve the id, title and the category_id of the 10 last created_at need 6. Examples/Exercices MySQL for business developer - v1.0 31 MySQL for business developer– Titouan BENOIT
  • 1°/ SOLUTION SELECT id, title, category_id, created_at FROM need ORDER BY created_at DESC LIMIT 10 6. Examples/Exercices MySQL for business developer - v1.0 32 MySQL for business developer– Titouan BENOIT
  • 2°/ Retrieve the id, first_name and the rank of fos_user which has a rank more than or equal to 2 and less than 5 and sort by rank (descending) 6. Examples/Exercices MySQL for business developer - v1.0 33 MySQL for business developer– Titouan BENOIT
  • 2°/ SOLUTION SELECT id, first_name, rank FROM fos_user WHERE rank >= 2 AND rank < 5 ORDER BY rank DESC 6. Examples/Exercices MySQL for business developer - v1.0 34 MySQL for business developer– Titouan BENOIT
  • 3°/ Retrieve all the distinct place_locality as city of the need and sort them by alphabetical order 6. Examples/Exercices MySQL for business developer - v1.0 35 MySQL for business developer– Titouan BENOIT
  • 3°/ SOLUTION SELECT DISTINCT(place_locality) as city FROM need ORDER BY city ASC 6. Examples/Exercices MySQL for business developer - v1.0 36 MySQL for business developer– Titouan BENOIT
  • 4°/ Retrieve the 12 last created need with their category. Display the need id, title and the category id and name. 6. Examples/Exercices MySQL for business developer - v1.0 37 MySQL for business developer– Titouan BENOIT
  • 4°/ SOLUTION SELECT need.id as needID, need.title as needTitle, need.category_id, category.id as categoryID, category.name as categoryName FROM need INNER JOIN category ON need.category_id = category.id ORDER BY need.created_at DESC LIMIT 12 6. Examples/Exercices MySQL for business developer - v1.0 38 MySQL for business developer– Titouan BENOIT
  • 5°/ Retrieve fos_user id, first_name, last_name and for each user display their number of welpActions. Hint: use GROUP BY user.id 6. Examples/Exercices MySQL for business developer - v1.0 39 MySQL for business developer– Titouan BENOIT
  • 5°/ SOLUTION SELECT user.id, user.first_name, user.last_name, COUNT(welp_action.user_id) as WelpActions FROM fos_user as user LEFT JOIN welp_action ON user.id = welp_action.user_id GROUP BY user.id ORDER BY WelpActions DESC 6. Examples/Exercices MySQL for business developer - v1.0 40 MySQL for business developer– Titouan BENOIT
  • MySQL for business developer– Titouan BENOIT MySQL for business developer – https://en.wikipedia.org/wiki/MySQL – https://en.wikipedia.org/wiki/SQL – http://dev.mysql.com/ – https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql – http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html – http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html – http://dev.mysql.com/doc/refman/5.7/en/join.html – https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html – http://dev.mysql.com/doc/refman/5.7/en/subqueries.html – http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html MySQL for business developer - v1.0 41 SOURCES https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/MySQL https://en.wikipedia.org/wiki/SQL https://en.wikipedia.org/wiki/SQL http://dev.mysql.com/ http://dev.mysql.com/ https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql https://openclassrooms.com/courses/administrez-vos-bases-de-donnees-avec-mysql http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/logical-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html http://dev.mysql.com/doc/refman/5.7/en/join.html http://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/subqueries.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html
Comments
Top