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;
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 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;
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;
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 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;
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 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
- Use not in and the corresponding column has not null constraint
- 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);
CREATE TABLE table_name ( column_a data_type, column_b data_type, column_c data_type );
CREATE TABLE universities( university_shortname text, university text, university_city text );
ALTER TABLE table_name ADD COLUMN column_name data_type;
ALTER TABLE professors ADD COLUMN university_shortname text;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE affiliations RENAME COLUMN organisation TO organization;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE affiliations DROP COLUMN university_shortname;
INSERT INTO ... SELECT DISTINCT ... FROM ...;
INSERT INTO professors SELECT DISTINCT firstname, lastname, university_shortname FROM university_professors;
DROP TABLE table_name;
DROP TABLE university_professors;
ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type_new;
ALTER TABLE professors ALTER COLUMN firstname TYPE varchar(64);
SELECT CAST(some_column AS data_type) FROM table;
SELECT transaction_date, amount + CAST(fee AS integer) AS net_amount FROM transactions;
CREATE TABLE table_name ( column_name UNIQUE );
ALTER TABLE table_name ADD CONSTRAINT some_name UNIQUE(column_name);
ALTER TABLE organizations ADD CONSTRAINT organization_unq UNIQUE(organization);
ALTER TABLE table_name ADD CONSTRAINT some_name PRIMARY KEY (column_name)
ALTER TABLE universities ADD CONSTRAINT university_pk PRIMARY KEY(id);
ALTER TABLE professors ADD COLUMN id serial; # Add a new field of type serial ALTER TABLE professors ADD CONSTRAINT professors_pkey PRIMARY KEY (id);
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);
ALTER TABLE a ADD CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES b (id);
ALTER TABLE professors ADD CONSTRAINT professors_fkey FOREIGN KEY (university_id) REFERENCES universities (id);