USE MyDB;
GO

SELECT * FROM [State];

SELECT [ID],[Name],[LocalName] FROM [State];


--Filter Records
SELECT * FROM [State]
WHERE [Population]<1000000;

SELECT * FROM [State]
WHERE [Country]='my';

SELECT * FROM [State]
WHERE [Name] LIKE '%ng';

SELECT * FROM [State]
WHERE [Name] LIKE '%ng%';

SELECT * FROM [State]
WHERE [Name] LIKE '%ng__';

SELECT * FROM [State]
WHERE [Name] LIKE '%n[a-g]%';

SELECT * FROM [State]
WHERE [Country]='my' AND [Population]<1000000; 

SELECT * FROM [State]
WHERE [Country]<>'my' AND [Population]<1000000; 

SELECT * FROM [State]
WHERE [Country]='my' OR [Population]<1000000; 

SELECT * FROM [State]
WHERE NOT ([Country]='my' OR [Population]<1000000); 


--Sorting Records
SELECT * FROM [State]
ORDER BY [Name];

SELECT * FROM [State]
ORDER BY [Name] DESC;

SELECT * FROM [State]
ORDER BY [Country], [Population] DESC;

SELECT * FROM [State]
ORDER BY 2, 5 DESC;

SELECT * FROM [State]
WHERE [Country]='my'
ORDER BY [Name];

SELECT TOP 3 * FROM [State]
WHERE [Country]='my'
ORDER BY [Name];


--Offset and Skip
SELECT * FROM [State]
WHERE [Country]='my'
ORDER BY [Name]
OFFSET 2 ROWS;

SELECT * FROM [State]
WHERE [Country]='my'
ORDER BY [Name]
OFFSET 2 ROWS
FETCH NEXT 5 ROWS ONLY;

--Using Functions
SELECT COUNT(*) AS [Number of state for Malaysia]
FROM [State]
WHERE [Country]='my';

SELECT SUM([Population]) AS [Population of Malaysia]
FROM [State]
WHERE [Country]='my';

SELECT AVG([Population]) AS [Average Population per state of Malaysia]
FROM [State]
WHERE [Country]='my';

--Custom Column
SELECT [Name], [Population], IIF([Population]>2000000,'Yes','No') AS [Big State]
FROM [State];

SELECT [Population],CAST([Population]*100.0/(SELECT SUM([Population]) FROM [State] WHERE [Country]='my') AS DECIMAL(18,2)) AS '%'
FROM [State]
WHERE [Country]='my';

SELECT 
    [Population],
    CAST([Population]*100.0 / SUM([Population]) OVER() AS DECIMAL(18,2)) AS '%'
FROM [State]
WHERE [Country]='my';

--Using Group By
SELECT [Country],COUNT(*) AS [No of States]
FROM [State]
GROUP BY [Country];

SELECT [Country],COUNT(*) AS [No of States]
FROM [State]
GROUP BY [Country]
HAVING COUNT(*)>20;

SELECT [Country],COUNT(*) AS [No of States]
FROM [State]
WHERE Name LIKE '%ng%'
GROUP BY [Country];

SELECT [Country],COUNT(*) AS [No of States]
FROM [State]
WHERE Name LIKE '%ng%'
GROUP BY [Country]
HAVING COUNT(*)>10;