HAVING statement

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions.
The HAVING clause allows us to filter the grouped groups of data.

SELECT release_year
FROM films
GROUP BY release_year
HAVING COUNT(title) > 200;

USING statement

Connect two tables in SQL. If the two fields connected are equal, you can use USING to equal ON.

SELECT c.name AS country, continent, l.name AS language, official
FROM countries AS c
INNER JOIN languages AS l
USING(code);

SELECT c.name AS country, continent, l.name AS language, official
FROM countries AS c
INNER JOIN languages AS l
ON c.code = l.code;

SELECT INTO statement

SELECT INTO statement selects data from one table and inserts it into another.
The SELECT INTO statement is often used to create a backup copy of a table or to archive records.

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

SELECT country_code, size,
    CASE WHEN size > 50000000 THEN 'large'
        WHEN size > 1000000 THEN 'medium'
        ELSE 'small' END
        AS popsize_group
INTO pop_plus
FROM populations
WHERE year = 2015;

UNION and UNION ALL operators

The UNION operator is used to combine the result sets of two or more SELECT statements.

Note that the SELECT statement within a UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

Note: by default, the UNION operator selects different values. If duplicate values are allowed, use UNION ALL.

SELECT code, year
  FROM economies
	UNION ALL
SELECT country_code, year
  FROM populations
ORDER BY code, year;

INTERSECT statement

Similar to the UNION instruction, INTERSECT processes the results of two SQL statements. The difference is that UNION is basically an OR (if the value exists in the first OR second sentence, it will be selected), while INTERSECT is more like AND (the value will be selected only if it exists in the first AND second sentences). UNION is UNION, AND INTERSECT is intersection.

SELECT code, year
  FROM economies
	INTERSECT
SELECT country_code, year
  FROM populations
ORDER BY code, year;

EXCEPT statement

EXCEPT returns the difference between the two result sets (that is, from the left query, all non repetitive values not found by the right query are returned).

SELECT name
  FROM cities
	EXCEPT
SELECT capital
  FROM countries
ORDER BY name;

CROSS JOIN

If there is no WHERE condition clause, it will return Cartesian product of two connected tables, and the number of rows returned is equal to the product of the number of rows of two tables;

SELECT c.name AS city, l.name AS language
FROM cities AS c        
  CROSS JOIN languages AS l
WHERE c.name LIKE 'Hyder%';

Semi join and anti join

Semi join usually appears in sql that uses exists or in. The so-called semi join is to return the records of the first table when there are one or more matching records in the second table when two tables are associated. The difference between semi join and common join is that the records in the first table can only be returned once at most;

SELECT DISTINCT name
FROM languages
WHERE code IN
  (SELECT code
   FROM countries
   WHERE region = 'Middle East')
ORDER BY name;

Anti join is the opposite of semi join, that is, when no matching record is found in the second table, the record in the first table will be returned;

It is used when not exists/not in is used. There is a difference between the two when dealing with null values

When to choose anti join

• 1. Use not in and the corresponding column has not null constraint
• 1. not exists, anti join is not guaranteed to be used every time

SELECT *
  FROM countries
  WHERE continent = 'Oceania'
  	AND code NOT in
  	(SELECT code
  	 FROM currencies);

Add, change, change, delete (a series of operations of a table)

1. new tables

CREATE TABLE table_name (
 column_a data_type,
 column_b data_type,
 column_c data_type
);

For example:

CREATE TABLE universities(
 university_shortname text,
 university text,
 university_city text
);

2. New columns in existing tables

ALTER TABLE table_name
ADD COLUMN column_name data_type;

For example:

ALTER TABLE professors
ADD COLUMN university_shortname text;

3. Rename column name

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

For example:

ALTER TABLE affiliations
RENAME COLUMN organisation TO organization;

4. delete columns

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE affiliations
DROP COLUMN university_shortname;

5. Migrate data to a new table

INSERT INTO ... 
SELECT DISTINCT ... 
FROM ...;

For example:

INSERT INTO professors 
SELECT DISTINCT firstname, lastname, university_shortname 
FROM university_professors;

6. delete table

DROP TABLE table_name;

For example:

DROP TABLE university_professors;

7. Change data type

ALTER TABLE table_name
ALTER COLUMN column_name
TYPE data_type_new;

For example:

ALTER TABLE professors
ALTER COLUMN firstname
TYPE varchar(64);

Data type cast

SELECT CAST(some_column AS data_type)
FROM table;

For example:

SELECT transaction_date, amount + CAST(fee AS integer) AS net_amount 
FROM transactions;

Add unique constraint

Add table:

CREATE TABLE table_name (
 column_name UNIQUE
);

Existing tables:

ALTER TABLE table_name
ADD CONSTRAINT some_name UNIQUE(column_name);

For example:

ALTER TABLE organizations
ADD CONSTRAINT organization_unq UNIQUE(organization);

Add primary key constraint

ALTER TABLE table_name
ADD CONSTRAINT some_name PRIMARY KEY (column_name)

For example:

ALTER TABLE universities
ADD CONSTRAINT university_pk PRIMARY KEY(id);

Create an auto increment primary key

ALTER TABLE professors 
ADD COLUMN id serial; # Add a new field of type serial

ALTER TABLE professors 
ADD CONSTRAINT professors_pkey PRIMARY KEY (id);

Merge fields to create a new primary key

ALTER TABLE cars
ADD COLUMN id varchar(128);

UPDATE cars
SET id = CONCAT(make, model);

ALTER TABLE cars
ADD CONSTRAINT id_pk PRIMARY KEY(id);

Create foreign keys

ALTER TABLE a 
ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);

For example:

ALTER TABLE professors 
ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES universities (id);