Code

/*1. Find the second highest*/
select 
*
from [Name Data Combined]

where salary = 
              (
                  select max(salary) from 
                  [Name Data Combined]
                  where salary <>
                                   (
                                  SELECT max(Salary)
                                  from
                                  [Name Data Combined]
                                   )
               )
;


/*2. how much is the average weight for red andd blue group and select the group which has average salary equal to 80000 ?*/
select 
avg(weight) as AvgWeight, avg(salary) as AvgSalary, color

from [Name Data Combined]

group by color
having avg(salary)>80000
;


/*3. select the person with maximum weight in group 'Blue' ( using group by) */

/*this one cannot not show the colour*/
select 
max(weight)
from [Name Data Combined]
where color='blue'
;

/*show colour but must use group by*/
select 
max(weight), color
from [Name Data Combined]
where color='blue'
group by color
;

/*same as above, but slightly inefficient*/
select 
max(weight), color
from [Name Data Combined]
group by color
having color='blue'
;


/*4. select all people which has letter 'a' in the name */
select 
*
from [Name Data Combined]
where name Like "*a*"
;

/* ANOTHER SET OF PRACTICE */

/*1. Select the name,capital, population (in billions with '$' in starting and 'bn' at end) and continent*/
SELECT 
a.Name, b.Capital, "$" & " " & [c.Population]/1000000000 & 'bn' AS BN, c.[Growth rate], c.Continent
FROM 
([Table 1] AS a LEFT JOIN [Table 2] AS b ON a.ID1=b.ID2) 
LEFT JOIN [Table 3] AS c ON a.ID1=c.ID3
;


/*2. find the max in each continent */
SELECT 
Continent, max(BN)
FROM Answer1Join
GROUP BY Continent
;

/*2b. find the 2nd max population of all */
SELECT 
*
FROM  Answer1Join
WHERE BN = 
(

SELECT max(BN) as 2Max
FROM Answer1Join
WHERE BN <>

    (SELECT
      max(BN)
      from Answer1Join
     );

)
;


/*3. What will be each country's population in 2019 ?*/
SELECT 
Population * (1+[Growth rate]) AS 2019Pop, 
*
FROM AllFieldsJoin;


/*4. Find the list of countries which do not have the capitals listed
SELECT 
*
FROM AllFieldsJoin
WHERE Capital is NULL;


/*5. Count how many times each country is listed*/
SELECT 
Name, Count(Name) AS [Count]
FROM [Table 4]
GROUP BY Name;


/*6. select the countries which are above the average population of all the countries*/
SELECT 
*
FROM AllFieldsJoin
WHERE Population > (
                      Select 
                      Avg(Population) As Average
                      From AllFieldsJoin
                      ;
                    )
;


/*7. select the distinct rows and distinct ids from table 5 */
SELECT 
DISTINCT ID2, Capital
FROM [Table 5]
GROUP BY ID2, Capital;


/*8. Create two column, Name and Managed By. */
SELECT 
a.Name AS Name, b.Name AS [Manage by]
FROM [Table 6] AS a LEFT JOIN [Table 6] AS b ON a.ManagerID=b.Employeeid;


/*9. Union table 1 and 4 and see the difference */
/*UNION, removed duplicates*/
SELECT
*
From 
[Table 1] UNION 
SELECT 
* 
FROM
[Table 4];

/*UNION ALL, kept duplicates*/
SELECT
*
From 
[Table 1] UNION ALL 
SELECT 
* 
FROM
[Table 4];


/*10. Create a new column 'CalField' where is country is from Asia, then population *1.5, 
any other continent population devided by 1.5 */
SELECT 
IIF(Continent = 'Asia', Population*1.5, Population/1.5) AS CalField, 
*
FROM [Table 3];


/* 11. Create a new column with number of days between Date and date2*/
SELECT 
Date2-Date AS Day_diff, 
*
FROM [Table 7];


/* 12. Extract month, Day and year from Date column*/
SELECT 
year(Date) AS YearBorn, 
month(Date) AS MonthBorn, 
day(Date) AS DayBorn, 
*
FROM [Table 7];


/* 13. Create a new column called 'Name' which should be like G. Singh. Use column 'First Name' and 'Last Name', and also Fullname*/
SELECT 
(MID([First Name], 1,1) & '.' & [Last Name]) AS Name, 
*
FROM [Table 7];


/* 14. Create a new column PeriodDate where 19890514 becomes 05-14-1989.*/
SELECT 
MID(Periodid,5,2) & '-' & MID(Periodid,7,2) & '-' & MID(Periodid,1,4) AS PeriodDate, 
*
FROM [Table 7];

SELECT 
FORMAT(PeriodDate, "long date") AS FormatedDate, 
*
FROM Answer14;

/* 15. Select First Name*/
SELECT 
Instr([Full Name], ' ') AS SpacePosition, Left([Full Name], SpacePosition-1), 
*
FROM [Table 7];