Trại hè công nghệ

  11 - String Functions in MySQL https: // youtu.be / KRXSJb9ql1Y?si = QMf4ZqloHE7EBei6 TOTAL: 11 . 54 -- Now let's look at string functions. These help us change and look at strings differently. SELECT * FROM bakery.customers; -- Length will give us the length of each value SELECT LENGTH ( 'sky' ); -- Now we can see the length of each name SELECT first_name, LENGTH (first_name) FROM employee_demographics; -- Upper will change all the string characters to upper case SELECT UPPER ( 'sky' ); SELECT first_name, UPPER (first_name) FROM employee_demographics; -- lower will change all the string characters to lower case SELECT LOWER ( 'sky' ); SELECT first_name, LOWER (first_name) FROM employee_demographics; -- Now if you have values that have white space on the front or end, we can get rid of that white space using TRIM SELECT TRIM ( 'sky'   ); -- Now if we have white space in the middle it doesn't work SELECT LTRIM ( '     I    ...

  10 - Unions in MySQL https: // youtu.be / iTQW_nDp938?si = iEg72oljtiK1F9yl TOTAL: 7 . 22 MINS -- UNIONS -- A union is how you can combine rows together- not columns like we have been doing with joins where one column is put next to another -- Joins allow you to combine the rows of data -- Now you should keep it the same kind of data otherwise if you start mixing tips with first_names it would be really confusing, but you can do it. -- Let's try it out and use Union to bring together some random data, then we will look at an actual use case -- UNION -> nối 02 câu SELECT SELECT first_name, last_name FROM employee_demographics UNION SELECT occupation, salary FROM employee_salary; -- So you can see we basically combined the data together, but not side by side in different columns, but one on top of the other in the same columns -- This obviously is not good since you're mixing data, but it can be done if you want. SELECT first_name, last_name FROM employee_demograph...

  09 - Joins in MySQL https: // youtu.be / lXQzD09BOH0?si = tVTMVvKD5hyB2ELD TOTAL: 17 . 11 MINS -- Joins -- joins allow you to combine 2 tables together (or more) if they have a common column. -- doesn't mean they need the same column name, but the data in it are the same and can be used to join the tables together -- there are several joins we will look at today, inner joins, outer joins, and self joins -- here are the first 2 tables - let's see what columns and data in the rows we have in common that we can join on SELECT * FROM employee_demographics; SELECT * FROM employee_salary; -- let's start with an inner join -- inner joins return rows that are the same in both columns -- 03:35 -- since we have the same columns we need to specify which table they're coming from SELECT * FROM employee_demographics JOIN employee_salary     ON employee_demographics.employee_id = employee_salary.employee_id; -- notice Ron Swanson isn't in the results? This is because...

08 - Limit + Aliasing in MySQL https: // youtu.be / ZnAydTqCtFU?si = z_qUXNIqoEizJn4 - TOTAL: 3 . 56 MINS -- LIMIT and ALIASING -- Limit is just going to specify how many rows you want in the output SELECT * FROM employee_demographics LIMIT 3 ; -- if we change something like the order or use a group by it would change the output SELECT * FROM employee_demographics ORDER BY first_name LIMIT 3 ; -- now there is an additional paramater in limit which we can access using a comma that specifies the starting place -- DẤU PHẨY là xác định ĐIỂM BẮT ĐẦU LIMIT SELECT * FROM employee_demographics ORDER BY first_name; SELECT * FROM employee_demographics ORDER BY first_name LIMIT 3 , 2 ; -- this now says start at position 3 and take 2 rows after that -- this is not used a lot in my opinion -- you could us it if you wanted to select the third oldest person by doing this: SELECT * FROM employee_demographics ORDER BY age desc ; -- we can see it's Donna - let's try to selec...

  https: // youtu.be / dCNjUOc1cBY?si = 98wPr8SDYuRBXsGn TOTAL: 3 . 45 mins -- Having vs Where -- Both were created to filter rows of data, but they filter 2 separate things -- Where is going to filters rows based off columns of data -- Having is going to filter rows based off aggregated columns when grouped SELECT gender, AVG (age) FROM employee_demographics GROUP BY gender ; -- let's try to filter on the avg age using where -- đã nhóm = GROUP BY rồi là không dùng WHERE để lọc => đó là lí do HAVING ra đời -- HAVING -> lọc trên kết quả đã nhóm SELECT gender, AVG (age) FROM employee_demographics WHERE AVG (age) > 40 GROUP BY gender ; -- this doesn't work because of order of operations. On the backend Where comes before the group by. So you can't filter on data that hasn't been grouped yet -- this is why Having was created SELECT gender, AVG (age) FROM employee_demographics GROUP BY gender HAVING AVG (age) > 40 ; SELECT gender, AVG (age) as AVG...

https: // youtu.be / zgYqUP_PhQo?si = UxhPuDkIcGB8n5v5 TOTAL: 10 . 45 mins -- Group By -- When you use the GROUP BY clause in a MySQL query, it groups together rows that have the same values in the specified column or columns. -- GROUP BY is going to allow us to group rows that have the same data and run aggregate functions on them SELECT * FROM employee_demographics; -- when you use group by  you have to have the same columns you're grouping on in the group by statement SELECT gender FROM employee_demographics GROUP BY gender ; SELECT first_name FROM employee_demographics GROUP BY gender ; SELECT occupation FROM employee_salary GROUP BY occupation ; -- notice there is only one office manager row -- when we group by 2 columns we now have a row for both occupation and salary because salary is different SELECT occupation, salary FROM employee_salary GROUP BY occupation, salary ; 02 : 22 -- now the most useful reason we use group by is so we can perform out aggregate fu...

  https: // youtu.be / MARn_mssG4A?si = BWZz_BJREcBsmbrv Total: 12 . 26 mins -- WHERE Clause: -- ------------- -- The WHERE clause is used to filter records (rows of data) -- It's going to extract only those records that fulfill a specified condition. --  So basically if we say "Where name is = 'Alex' - only rows were the name = 'Alex' will return --  So this is only effecting the rows, not the columns -- Let's take a look at how this looks SELECT * FROM employee_salary WHERE salary > 50000 ; SELECT * FROM employee_salary WHERE salary >= 50000 ; SELECT * FROM employee_demographics WHERE gender = 'Female' ; -- We can also return rows that do have not "Scranton" SELECT * FROM employee_demographics WHERE gender != 'Female' ; -- We can use WHERE clause with date value also SELECT * FROM employee_demographics WHERE birth_date > '1985-01-01' ; -- Here '1990-01-01' is the default data formate ...