Master class on SQL ( Beginner to Advance ).pptx

it's about the SQL and its Fundamental for the Beginer. to advance

1. Master class on SQL Dr. Abhilash Ponnam

2. What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

3. What Can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database

4. SQL works with databases Tables are housed inside databases Using queries, summary data can be obtained by analysing data spread across multiple tables when tables are joined.

5. SELECT * from lo where Region = "North" SELECT * from lo where Region = "North" and Segment = "Home Office" SELECT CustomerName , Country, Region from LO SELECT DISTINCT Country from lo where Region = "North" select Country, count(Country) from lo group by Country select Country, count(Country) from lo group by Country order by count(Country) desc select Country, count(Country) from lo group by Country order by count(Country) desc LIMIT 5 select SubCategory , avg(Discount) from ob group by SubCategory order by avg(discount) desc limit 3 create table loob as select * from lo join ob where lo.OrderID = ob.OrderID select Country, sum( CumProfit ) from LOOB group by Country order by sum( CumProfit ) asc limit 3 select Country, sum( CumProfit ) from LOOB group by Country having sum( CumProfit ) < 0 select Country, sum( CumProfit ) as total_profit from LOOB group by Country having total_profit <= 0 order by sum( CumProfit ) asc create table segprof as select Country, Segment, sum( cumprofit ) as segment_profit from loob group by Country

6. create table segprof as select Country, Segment, sum( cumprofit ) as segment_profit from loob group by Country, Segment select * from segprof order by Country, segment_profit desc create table segrank as select row_number () over (PARTITION by Country order by segment_profit desc) as rankcol , * from segprof select * from segrank where rankcol = 1

7. WITH RankedSegProf AS ( SELECT ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Segment_Profit DESC) AS Serial_Number , * FROM SegProf ) SELECT * FROM RankedSegProf WHERE Serial_Number = 1

8. CREATE TABLE LOOB AS SELECT * FROM LO JOIN OB WHERE LO.OrderID = OB.OrderID SELECT Country, Category, sum( CumProfit ) as tot_prof , avg(Discount) as avg_disc from loob GROUP by Country, Category order by tot_prof asc SELECT Country, Category, sum( CumProfit ) as tot_prof , avg(Discount) as avg_disc from loob GROUP by Country, Category having tot_prof <=0 order by tot_prof asc SELECT Country, Category, sum( CumProfit ) as tot_prof , avg(Discount) as avg_disc from loob GROUP by Country, Category having tot_prof <=0 order by tot_prof asc select Country, substr (Country,1,3) as cc, sum( CumProfit ) as tot_prof from loob group by Country

9. Thank you for your time