USE MyDB;
GO

CREATE TABLE [TblOdd] (
    [Name] 		varchar(64) NOT NULL,
    [Value] 	int NOT NULL,
    PRIMARY KEY (Value)
);
GO

INSERT INTO TblOdd Values('One',1);
INSERT INTO TblOdd Values('Three',3);
INSERT INTO TblOdd Values('Five',5);
INSERT INTO TblOdd Values('Seven',7);
INSERT INTO TblOdd Values('Nine',9);

SELECT * FROM TblOdd;
GO


CREATE TABLE [TblEven] (
    [Name] 		varchar(64) NOT NULL,
    [Value] 	int NOT NULL,
    PRIMARY KEY (Value)
);
GO

INSERT INTO TblEven Values('Two',2);
INSERT INTO TblEven Values('Four',4);
INSERT INTO TblEven Values('Six',6);
INSERT INTO TblEven Values('Eight',8);
INSERT INTO TblEven Values('Ten',10);

SELECT * FROM TblEven;
GO


CREATE TABLE [TblPrime] (
    [Name] 		varchar(64) NOT NULL,
    [Value] 	int NOT NULL,
    PRIMARY KEY (Value)
);
GO

INSERT INTO TblPrime Values('Two',2);
INSERT INTO TblPrime Values('Three',3);
INSERT INTO TblPrime Values('Five',5);
INSERT INTO TblPrime Values('Seven',7);

SELECT * FROM TblPrime;
GO


SELECT o.*,p.* FROM TblOdd o
INNER JOIN TblPrime p ON o.Value = p.Value;

SELECT o.*,p.* FROM TblOdd o
LEFT JOIN TblPrime p ON o.Value = p.Value;

SELECT o.*,p.* FROM TblOdd o
RIGHT JOIN TblPrime p ON o.Value = p.Value;

SELECT o.*,p.* FROM TblOdd o
FULL JOIN TblPrime p ON o.Value = p.Value;

SELECT o.*,p.* FROM TblOdd o
CROSS JOIN TblPrime p;

SELECT * FROM TblOdd
UNION
SELECT * FROM TblPrime;

SELECT * FROM TblOdd
UNION ALL
SELECT * FROM TblPrime;

--Inner Join
SELECT c.Name, Count(s.[ID]) [No of States] FROM State s 
JOIN Country c ON s.Country=c.Code
GROUP BY c.Name;

SELECT c.Name, Count(s.[ID]) [No of Big States] FROM State s 
JOIN Country c ON s.Country=c.Code
WHERE s.Population>2000000
GROUP BY c.Name;

SELECT c.Name, Count(s.[ID]) [No of States for Big Countries] FROM State s 
JOIN Country c ON s.Country=c.Code
GROUP BY c.Name
HAVING Count(s.[ID])>20;

SELECT c.Name, Count(s.[ID]) [No of States for Big Countries] FROM State s 
JOIN Country c ON s.Country=c.Code
GROUP BY c.Name
HAVING Count(s.[ID])>20
ORDER BY [No of States for Big Countries] DESC;