Basic extraction from data tables

CREATING TABLES

CREATE TABLE SpaceMonster(`Account Balance` INT);
INSERT INTO SpaceMonster VALUES (42);
SELECT `Account Balance` FROM SpaceMonster

CREATE TABLE employee(
  employee_id INTEGER PRIMARY KEY,
  first_name VARCHAR(10),
  dept_code VARCHAR(10),
  manager_id INTEGER REFERENCES employee);
  
INSERT INTO employee VALUES (1,'Robin','Eng',NULL);
INSERT INTO employee VALUES (2,'Jon','SoC',1);
INSERT INTO employee VALUES (3,'Andrew','SoC',2);
INSERT INTO employee VALUES (4,'Alison','SoC',2);

SELECT

SELECT * FROM world

SELECT name, continent, population FROM world
ORDER BY name
GROUP BY region

SELECT population FROM world AS title
  WHERE name = 'Germany'
  
SELECT name, population FROM world
  WHERE name IN ('Sweden', 'Norway')
  
SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 300000
  
SELECT name FROM world
  WHERE name LIKE '_B%'   
  AND population > 0
  
SELECT name FROM world
  WHERE name NOT LIKE '_B%'     
  
INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;       // inserting rows from another table
  
SELECT name FROM world
  WHERE population >
     (SELECT population FROM world    // list countries where pop > Romania's
      WHERE name='Romania')
  
ROUND(population, 1)
LENGTH(name)
CONCAT(region, name)
UNION                      // combining rows from multiple datasets

SQL Commands

SELECT, SUM, COUNT, JOIN, ETC

Data table name = 'world'
Row name = different countries
Column name = 'name' and 'population'