Trại hè công nghệ

20 - MySQL Exploratory Data Analysis - Full Project https: // youtu.be / QYd - RtK58VQ?si = 9Dv - oEFz9nI99aNO TOTAL: 32 . 43 -- EDA -- Here we are jsut going to explore the data and find trends or patterns or anything interesting like outliers -- normally when you start the EDA process you have some idea of what you're looking for -- with this info we are just going to look around and see what we find! SELECT * FROM world_layoffs.layoffs_staging2; -- EASIER QUERIES SELECT MAX (total_laid_off) FROM world_layoffs.layoffs_staging2; -- Looking at Percentage to see how big these layoffs were SELECT MAX (percentage_laid_off),   MIN (percentage_laid_off) FROM world_layoffs.layoffs_staging2 WHERE  percentage_laid_off IS NOT NULL ; -- Which companies had 1 which is basically 100 percent of they company laid off SELECT * FROM world_layoffs.layoffs_staging2 WHERE  percentage_laid_off = 1 ; -- these are mostly startups it looks like who all went out of business durin...

19 - Data Cleaning in MySQL 19 - Data Cleaning in MySQL https: // youtu.be / 4UltKCnnnTA?si = vccggkAZ_aNDGFcq TOTAL: 51 : 10 NOW : 06 : 30 -- SQL Project - Data Cleaning -- https://www.kaggle.com/datasets/swaptr/layoffs-2022 => lấy file CSV từ link này (link local: D:\LEARNING\KHÁC\BI_DEVELOPER\SQL\ALEX_THE_ANALYSIS\01-MYSQL\layoffs.csv) -- tạo mới 01 schema tên world_layoffs -> nhấp phải lên mục Table - chọn Table Data Import Wizard - Create New...Drop if Exist - Next SELECT * FROM world_layoffs.layoffs; -- first thing we want to do is create a staging table. This is the one we will work in and clean the data. We want a table with the raw data in case something happens -- Tạo 01 table rỗng giống hệt world_layoffs CREATE TABLE world_layoffs .layoffs_staging LIKE world_layoffs.layoffs; -- Copy ALL data world_layoffs vào table mới tạo -> thao tác này để backup world_layoffs INSERT layoffs_staging SELECT * FROM world_layoffs.layoffs; -- MỤC TIÊU BÀI 19: -- now ...

18 - Triggers and Events in MySQL https: // youtu.be / QMUZ5HfWMRc?si = YPQSMBwAuGVQOL6s TOTAL: 14 . 41 -- Triggers -- a Trigger is a block of code that executes automatically executes when an event takes place in a table. -- for example we have these 2 tables, invoice and payments - when a client makes a payment we want it to update the invoice field "total paid" -- to reflect that the client has indeed paid their invoice SELECT * FROM employee_salary; SELECT * FROM employee_demographics; -- so really when we get a new row or data is inserted into the payments table we want a trigger to update the correct invoice -- with the amount that was paid -- so let's write this out USE parks_and_recreation; DELIMITER $$ CREATE TRIGGER employee_insert2     -- we can also do BEFORE, but for this lesson we have to do after     AFTER INSERT ON employee_salary     -- now this means this trigger gets activated for each row that is inserted. Some sql databs...

17 - Stored Procedures in MySQL https: // youtu.be / 7vnxpcqmqNQ?si = GilD7Nk_jVSYaUmj TOTAL: 12 . 36 -- Moi Procedure phai RUN no moi ton tai de CALL duoc -- SHOW PROCEDURE STATUS; => kiem tra procedure nao tao hay chua -- So let's look at how we can create a stored procedure -- First let's just write a super simple query SELECT * FROM employee_salary WHERE salary >= 60000 ; -- Now let's put this into a stored procedure. CREATE PROCEDURE large_salaries() SELECT * FROM employee_salary WHERE salary >= 60000 ; -- Now if we run this it will work and create the stored procedure -- we can click refresh and see that it is there -- notice it did not give us an output, that's because we -- If we want to call it and use it we can call it by saying: CALL large_salaries(); -- as you can see it ran the query inside the stored procedure we created -- Now how we have written is not actually best practice.alter -- Usually when writing a stored procedure you don...

16 - Temp Tables in MySQL https: // youtu.be / uEk07jXdKOo?si = jCd2G7sQ5MrN11KK TOTAL: 7 . 46 -- Using Temporary Tables -- Temporary tables are tables that are only visible to the session that created them. -- They can be used to store intermediate results for complex queries or to manipulate data before inserting it into a permanent table. -- Bang TAM nay khong ton tai trong database nhung van co the viet cau query thao tac duoc (tam chi tren file SQL khac - mien la dung tat APP) -- There's 2 ways to create temp tables: -- 1. This is the less commonly used way - which is to build it exactly like a real table and insert data into it CREATE TEMPORARY TABLE temp_table (first_name varchar ( 50 ), last_name varchar ( 50 ), favorite_movie varchar ( 100 ) ); -- if we execute this it gets created and we can actualyl query it. SELECT * FROM temp_table; -- notice that if we refresh out tables it isn't there. It isn't an actual table. It's just a table in memory. -- now o...

15 - CTEs in MySQL https: // youtu.be / UC7uvOqcUTs?si = PIvc9QJLMEF8oT3i TOTAL: 10 . 31 MINS -- Using Common Table Expressions (CTE) -- A CTE allows you to define a subquery block that can be referenced within the main query. -- It is particularly useful for recursive queries or queries that require referencing a higher level -- this is something we will look at in the next lesson/ -- Let's take a look at the basics of writing a CTE: -- First, CTEs start using a "With" Keyword. Now we get to name this CTE anything we want -- Then we say as and within the parenthesis we build our subquery/table we want WITH CTE_Example AS ( SELECT gender, SUM (salary), MIN (salary), MAX (salary), COUNT (salary), AVG (salary) FROM employee_demographics dem JOIN employee_salary sal     ON dem.employee_id = sal.employee_id GROUP BY gender ) -- directly after using it we can query the CTE SELECT * FROM CTE_Example; -- Now if I come down here, it won't work because it's n...

  14 - Window Functions in MySQL https: // youtu.be / 7NBt0V8ebGk?si = mMdFDPdVIjphTG9X TOTAL: 13 . 28 -- Window Functions -- windows functions are really powerful and are somewhat like a group by - except they don't roll everything up into 1 row when grouping. -- windows functions allow us to look at a partition or a group, but they each keep their own unique rows in the output -- we will also look at things like Row Numbers, rank, and dense rank SELECT * FROM employee_demographics; -- first let's look at group by SELECT gender, ROUND ( AVG (salary), 1 ) FROM employee_demographics dem JOIN employee_salary sal     ON dem.employee_id = sal.employee_id GROUP BY gender ; -- now let's try doing something similar with a window function SELECT dem.employee_id, dem.first_name, gender, salary, AVG (salary) OVER () FROM employee_demographics dem JOIN employee_salary sal     ON dem.employee_id = sal.employee_id ; -- now we can add any columns and it work...

  13 - Subqueries in MySQL https: // youtu.be / Vj6RqA_X - IE?si = 8hSBKjhytr_zGOD5 TOTAL: 11 --  Subqueries -- So subqueries are queries within queries. Let's see how this looks. SELECT * FROM employee_demographics; -- Now let's say we wanted to look at employees who actually work in the Parks and Rec Department, we could join tables together or we could use a subquery -- We can do that like this: SELECT * FROM employee_demographics WHERE employee_id IN             ( SELECT employee_id                 FROM employee_salary                 WHERE dept_id = 1 );                 -- So we are using that subquery in the where statement and if we just highlight the subquery and run it it's basically a list we are selecting from in the outer query SELECT * FROM employee_demographics WHERE employee_id IN     ...

  --TOI UU MYSQL --https://youtu.be/TslBGnENTFw?si=XGlzoT8zz8qSkIlz NOW : 23 : 02 -- MỤC LỤC -- 01 - Cài đặt -- 02 - Kiến trúc -- 03 - Làm việc vs SQL -- 04 - Tối ưu -- 05 - Tối ưu tham số -- 06 - Sao lưu --00:00 -> 04:00 --Giới thiệu --04:28 --Từ khóa "MySQL Community Server"-> dev.mysql.com/downloads/mysql/ --Bản LTS - Long Time Support -> làm việc vs Oracle db thì tạo tài khoản còn k chọn No Thanks... --Custom cài trên ổ D -> bảo mật hơn??? --Data Directory -> vào thư mục khi nãy vừa cài trên ổ D - tạo thư mục data --khai báo pass cho root -> tạo thêm 01 user dbadmin --cài xong vào services.msc kiểm tra xem có MySQL8x đang Running là OK --08:45 -> 10:50 --Cấu trúc files của MYSQL trên ổ cứng --bin -> chứa file thực thi của MySQL --path thư mục bin này chính xác nằm trong services.msc - MySQL8x --data -> chứa dữ liệu db --chạy màn hình cmd(terminal k dung duoc) -> vào thư mục bin (C:\Program Files\MySQL\MySQL Server 8.0\bin\) gõ lệnh --mysql.ex...

  12 - Case Statements in MySQL https: // youtu.be / RYIiOG4LsvQ?si = EHM4eyQZ - y91MbeD TOTAL: 8 . 53 -- Case Statements -- IF Else theo kieu SQL -- A Case Statement allows you to add logic to your Select Statement, sort of like an if else statement in other programming languages or even things like Excel SELECT * FROM employee_demographics; SELECT first_name, last_name, CASE     WHEN age <= 30 THEN 'Young' END FROM employee_demographics; -- SELECT first_name, last_name, CASE     WHEN age <= 30 THEN 'Young'     WHEN age BETWEEN 31 AND 50 THEN 'Old'     WHEN age >= 50 THEN "On Death's Door" END FROM employee_demographics; -- Poor Jerry -- Now we don't just have to do simple labels like we did, we can also perform calculations -- Let's look at giving bonuses to employees SELECT * FROM employee_salary; -- Pawnee Council sent out a memo of their bonus and pay increase structure so we need to follow it -...