![Structure Query Language
[SELECT] - [FROM] - [WHERE] - [GROUP BY] - [HAVING] - [ORDER BY]
- SELECT FirstName, LastName FROM Employee
- SELECT * FROM Employee
- SELECT FirstName FROM Employee ORDER BY FirstName
- SELECT FirstName FROM Employee WHERE Department=HR
- SELECT FIrstName
FROM Employee
WHERE Department=HR
ORDER BY FirstName](https://image.slidesharecdn.com/gjqwtutqe6olvfd28ng6-signature-6a6d293003c50605b327d34d34a365ab6aead5cfa0d44849a7d99692013fbd52-poli-181228072812/85/Database101-9-320.jpg)
Database101
- 1. Database for Everyone Piti Champeethong (A.K.A Lutfy) FB: fb.com/digitaloldschooljourney
- 2. Agenda Morning Session Afternoon Session - Normalization 犖犖劇賢犖犖萎犖 犖÷元 犖犖萎犖犖犖迦 - Workshop design database - Import data - SQL101 - Trigger - Stored Procedure - User Defined Function - Workshop query report - Conclusion
- 3. Normalization 1NF - Eliminate Repeating Groups (Make a separate table for each set of related attributes, and give each table a primary key) 2NF - A relation is in second normal form if it is in 1NF and every non key attribute is fully functionally dependent on the primary key. 3NF - A relation is in second normal form if it is in 2NF and Every non-prime attribute of R is non-transitively dependent on every key of R (Relation Table)
- 4. 1NF (First Normal Form)
- 5. 2NF (Second Normal Form)
- 6. 3NF (Second Normal Form)
- 8. Import data - Setup extension from https://bit.ly/2CoUtgT - Convert excel or any sources to CSV - Import CSV by extension into database
- 9. Structure Query Language [SELECT] - [FROM] - [WHERE] - [GROUP BY] - [HAVING] - [ORDER BY] - SELECT FirstName, LastName FROM Employee - SELECT * FROM Employee - SELECT FirstName FROM Employee ORDER BY FirstName - SELECT FirstName FROM Employee WHERE Department=HR - SELECT FIrstName FROM Employee WHERE Department=HR ORDER BY FirstName
- 10. JOIN Operator SELECT FirstName, DepartmentName FROM Employee INNER JOIN Department ON EmployeeId.DepId = Department.DepId SELECT FirstName, DepartmentName FROM Employee INNER JOIN Department ON EmployeeId.DepId = Department.DepId INNER JOIN ControlManager ON ControlManager.MgrId = Employee.MgrId
- 11. INNER JOIN sLeft table Right table
- 12. INNER JOIN Ex:
- 13. INNER JOIN SQL: SELECT 犖犖劇犖犖犖犖園犖犖迦, 犢犖犖犖 FROM 犖犖犖÷弦犖ム犖犖園犖犖迦 as emp INNER JOIN 犖犖犖÷弦犖ム犖犖犖 as dep ON emp.犖犖犖園肩犢犖犖犖 = dep.犖犖犖園肩犢犖犖犖
- 14. LEFT|RIGHT JOIN s Right tableLeft table Left table Right table
- 15. LEFT JOIN Ex:
- 16. LEFT JOIN SQL: SELECT 犖犖犖園肩犖犖犖園犖犖迦, 犖犖劇犖犖犖犖園犖犖迦, 犖о険犖犖犖朽犖ム顕犖犖迦 FROM 犖犖犖÷弦犖ム犖犖園犖犖迦 as emp LEFT JOIN 犖犖犖÷弦犖ム犖迦牽犖ム顕犖犖迦 as leave ON emp.犖犖犖園肩犖犖犖園犖犖迦 = leave.犖犖犖園肩犖犖犖園犖犖迦
- 17. Query Processor 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE or WITH ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. ORDER BY 11. TOP
- 18. Aggregation 1. COUNT - 犖犖園犖犢犖迦犖о犢犖犖о犖園犖犖犖÷ (records) 2. MIN - 犖犖迦犖迦犖朽犖犢犢犖迦犖朽犖犖伍 (minimize) 3. MAX - 犖犖迦犖迦検犖迦犖犖朽犖犖伍 (maximize) 4. SUM - 犖犖迦犖ム牽犖о検 (summation) 5. AVG - 犖犖迦犖迦犖犖ム元犢犖 (average)
- 19. Trigger 犖犖迦牽犢犖犖朽権犖犖犢犖迦肩犖園犖 T-SQL 犢犖犖劇犖犖犖ｏ犖迦犖犖犖犖犖園犖犢犖犢犖犖犖犖犖迦牽犖犢犖 犖犖迦犢犖犖犖迦牽犖迦犖犖犖÷弦犖 犢犖犖犢犖迦犖迦犖犖迦犖犖犖迦犢犖犖犖犖園犢犖犖÷険犖犖 Use-Case: 犢犖÷厳犢犖犖犖刻犢犖犖ム犖犖犖÷弦犖ム犖犢 犢犖犖犖園犖ム賢犖犖犖犖÷弦犖ム犖朽犖犖項犖ム犢犖犖ワ犖迦犖園犖 犢犖犢犖犢犖犢犖э犖犖朽犖犖迦牽犖迦
- 20. Stored Procedure 犖犖迦牽犢犖犖朽権犖犖犢犖迦肩犖園犖 T-SQL 犢犖犖劇犖犖犖ｏ犖迦犖犖犖犖犖園犖犢犖犢犖犢犖犖迦犖э犢犖 犖犢犖迦見犖犖園犖犖園犖犖迦牽犖犖犖÷弦犖ム犖迦犢 犢犖犖劇犖犖犖迦犖犖犖犖迦牽犢犖犖朽権犖犖÷顕犢犖犢犖犖￥ Use-Case: 犖犖ｏ犖迦犖犖犖犖犖園犖犢犖犖劇犖犢犖犖犢犖迦見犖犖園犢犖犖巌犖 犢犖犢犖 犖ム 犢犖犖朽権犖犖犖犖÷弦犖 犖犖迦犢 犖犖迦犖犖迦牽犖迦犢犖犖劇犖犖ム network traffic 犢犖ム鍵犖犖園犖犖犖犖犖犖迦牽 犖犖犖о犖犖犖犖犖犖÷弦犖
- 21. Without SP DB Parser Algibrizing Optimizer Execution Send query string Receive results 犖犖伍犖犖犖園犖犖犖朽犖÷元犖犖迦牽 request 犖犖萎犖犖犖犖園犖犢犖迦肩犖園犖 4 step 犢犖 DB
- 22. With SP DB Parser Algibrizing Optimizer Execution Send SP Receive results 犖犢犖迦犖犖犖謹犖犖犖犖 Execution 犖犖犖迦犢犖犖朽権犖 犢犖犖劇犖犖犖犖迦犖犖犖犖犖ｏ犖迦 SP 犢犖犖÷元犖犖迦牽犖犖犖о犖犖犖 3 犖犖園犖犖犖犖犢犖犖犢犖犢犖ワ犖
- 23. Reference - www.dofactory.com - www.datacamp.com - https://docs.microsoft.com/en-us/sql/azure-data-s tudio/
- 24. Our sponsors