-- Simple select queries

-- Get all of the columns from the table, but only the first 10 rows
SELECT *
FROM cmt11_mod
LIMIT 10
;

-- Only selected columns
SELECT persid, agegpt, marstat1t
FROM cmt11_mod
LIMIT 10
;

-- '*' only means all columns, you cannot use it like a wildcard
SELECT re*
FROM censusmicroteaching11ew
LIMIT 10
;


SELECT agegpt
     , region
	 , Region_Name
	 , sex
	 , Gender
FROM  cmt11_mod
;



-- Using the Where clause to filter the rows being returned

-- The Where clause

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Gender = 'Female'
;

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt > 5
;

-- More complex WHERE clause

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE  Gender = 'Female' AND region < 3 AND ( agegpt = 1 OR agegpt >= 6)
;

--- breakdown

-- Gender = 'Female' - 289172 rows returned
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE  Gender = 'Female'
;

--  region < 3  - 97785 rows returned
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE  region < 3 
;

-- agegpt = 1  - 106832 rows returned
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt = 1 
;

-- agegpt >= 6  - 158147 rows returned
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt >= 6
;

-- full query - 23929 rows returned
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE  Gender = 'Female' AND region < 3 AND ( agegpt = 1 OR agegpt >= 6)
;


-- Using BETWEEN and IN operators


SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt > 2 AND agegpt <= 6
;


SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt BETWEEN 3 AND 6
;

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE agegpt IN (3,4,5,6)
;


-- Exercise

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Region_Code IN ("E12000001", "E12000002")
  AND econac1t BETWEEN 5 and 9
  AND agegpt > 6
;

-- using Region_Code
SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Region_Name Like  "%North%"

;

-- using Region_Name
SELECT agegpt, region, Region_Name, sex, Gender, length(Region_Name), length(trim(Region_Name))
FROM  cmt11_mod
WHERE Region_Name like  "North East%"

;

-- sort

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Region_Code IN ("E12000001", "E12000002")
  AND econac1t BETWEEN 5 and 9
  AND agegpt > 6
ORDER BY agegpt
;

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Region_Code IN ("E12000001", "E12000002")
  AND econac1t BETWEEN 5 and 9
  AND agegpt > 6
ORDER BY agegpt DESC
;

SELECT agegpt, region, Region_Name, sex, Gender
FROM  cmt11_mod
WHERE Region_Code IN ("E12000001", "E12000002")
  AND econac1t BETWEEN 5 and 9
  AND agegpt > 6
ORDER BY agegpt, Region_Code DESC
;


-- Aggregation

select count(*), agegpt
from cmt11_mod
group by agegpt
;



select region, count(*)
from cmt11_mod
group by region;



-- Creating table with SQL

CREATE TABLE Sex(
   ID INT ,
   Gender TEXT        
 );  

INSERT INTO sex (ID, Gender)  
VALUES (1, 'Male');

-- Another way
INSERT INTO sex 
VALUES (2, 'Female');

CREATE TABLE Occupations(
   ID INT ,
   Occupation TEXT        
 ); 
 
INSERT INTO Occupations 
VALUES (1, 'Managers, Directors and Senior Officials') 
, (2, 'Professional Occupations')  
, (3, 'Associate Professional and Technical Occupations')  
, (4, 'Administrative and Secretarial Occupations')  
, (5, 'Skilled Trades Occupations')  
, (6, 'Caring, Leisure and Other Service Occupations')  
, (7, 'Sales and Customer Service Occupations')  
, (8, 'Process, Plant and Machine Operatives')  
, (9, 'Elementary Occupations') 
, (-9, 'No code required (under 16/never worked/student/pupil living away)') 
;

-- Joining tables  & Creating a new table from the result

CREATE TABLE cmt11_mod as 
Select a.*, b.Occupation, c.Region_Code, c.Region_Name, d.Gender
from censusmicroteaching11ew as a
join Occupations as b ON a.occupat1t = b.ID
join Regions as c on a.region = c.ID
join Sex as d on a.sex = d.ID
;