DMV - All Lectures SQL.pptxpComplete SQL

this has whole SQL lectures

1. Standard Query Language SQL Lecture 1

2. Course Expectations What do you expect from this course? Prior Exposure to SQL? My Experience with SQL.

3. Course Expectations

4. Database USER DBMS DATABASE

5. SQL and Databases SQL is the language for generating, manipulating, and retrieving data from a relational database. Types?

6. Table, Tuples, Fields, Schema, Primary Key, Foreign Key Why not Excel? SQL (Sequel) Vs. MySQL

7. Relational Databases

8. SQL Statements

9. Creating Table and Databases Dropping Table and Databases

10. Data Types

11. Material to Cover Databases (Their control and administration) Select statement Selection of Database from toolbar or with command HR Data exploration: How do we start Entity Relationship Diagram (ERD) Relationship type: Many, One Joining Criteria Importing the HR tables: Countries and Customers Use of underscore instead of space

12. One to One: You and your ERP Many to One: You all studying from me One to Many: Me teaching you all Many to Many: Other students studying from same professor that you have studied from. Relationship of Customers to Country?

13. Standard Query Language SQL Lecture 3

14. ERD Diagram A customer complains about an employee, how to find the data of the employee? Why not Country Name as PK and why Country ID?

15. Star Schema

16. Snowflake Schema

17. Topics to Cover in Lecture 2 Text vs Numbers Comments in SQL: #, --, /* Not equal to sign Between command '[ acs ]%’ '[! acs ]%’ '[a-f]%

18. Topics to Cover in Lecture 3 Importing Date columns as text Color coding of SQL: Gray = Operator, Pink = Function YEAR, MONTH Temporary Table: INTO #hktemp, INTO ##hktemp Copying the data to Excel

19. Topics to Cover in Lecture 4 Refresher of Group By Function Distinct feature in Excel – Not Done Flow of Processing SQL commands ‘Having’ does not depend on Select statement Distinct feature in Excel Count Distinct *

20. Topics to Cover in Lecture 5 ORDER BY 2 or more columns ORDER BY on String

21. Inner Join

22. Left Join

23. Right Join

24. Full Outer Join

25. Null/Not Null

26. Null/Not Null Select * from customer where city is NULL; Is not same as Select * from customer where city = ‘NULL’; lets say id <> 2, then it won’t pull up the records with id = null. You have to specifically build that into condition that id <> 2 and id is null.

27. SQL Functions MODULUS or %: Checking the Remainder: select distinct city from station where ID%2 = 0; For Even: Either do MOD(ID, 2) = 0 or ID%2 = 0. For Odd: Either do MOD(ID, 2) = 0 or ID%2 = 1. POWER(X, 2): To raise x to the power (exponent) of 2.

28. SQL Functions

29. SQL Functions TRIM([characters FROM ]string): removes leading spaces or the characters you specify. LTRIM, RTRIM. Window Functions: Over() to open a window: If no column is passed to over(), it is going create one window for all the records, will iterate over all rows. Select *, max( salaray ) over() as max_salary from employee: this will show max salary among all the employees.

30. SQL Functions ROW_NUMBER() to apply row numbers in a table. Specification in the Over() clause will tell us how to apply the row number. Example: Select a.CITY , a.CITY_LENGTH FROM (SELECT CITY, LENGTH(CITY) AS CITY_LENGTH, Row_number () over(order by LENGTH(CITY) asc , city asc ) as short_length_rn , Row_number () over(order by LENGTH(CITY) desc, city asc ) as long_length_rn FROM STATION) as a where a.short_length_rn = 1 or a.long_length_rn = 1 order by 1 asc ;

31. SQL Functions DateAdd Datediff Left Right GetDate