/* There 2 type of nested queries: 1) Corelated: Can't execute the subquery induvidually 2) Non-Corelated: Can execute the subquery indivisually */ USE MyDB; GO SELECT [Name] AS [State with most population in Malaysia] FROM [State] WHERE [Population]=(SELECT MAX([Population]) AS [Most Population of state in Malaysia] FROM [State] WHERE [Country]='my'); SELECT c.Name FROM [Country] AS c WHERE (SELECT COUNT(*) FROM [State] AS s WHERE s.Country = c.Code) > 20; SELECT c.Name, (SELECT COUNT(*) FROM [State] AS s WHERE s.Country = c.Code) AS [No of States] FROM [Country] AS c; SELECT c.Name, COUNT(s.[ID]) AS [No of States] FROM [State] s JOIN [Country] c ON s.Country=c.Code GROUP BY c.Name; /* 1) Find out who is the oldest employee 2) Find out all the supervisors */