#Qview qd700 cheat sheet full

The combined queries must return the same number of columns and compatible data types. Set operations are used to combine the results of two or more queries into a single result. This query finds countries that have at least one city: WHERE population > ( SELECT AVG(population) This query finds cities with a population greater than the average population in the country: SELECT * It cannot be run independently from the outer query. A correlated subquery depends on the outer query. This query finds cities in countries that have a population above 20M: SELECT nameĪ correlated subquery refers to the tables introduced in the outer query. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY. This query finds cities with the same rating as Paris: SELECT nameĪ subquery can also return multiple columns or multiple rows. It can be used with comparison operators =,, or >=. The simplest subquery returns exactly one column and exactly one row.

sum(expr) − sum of values within the groupįind out the number of cities: SELECT COUNT(*) FROM city įind out the number of cities with non-null ratings: SELECT COUNT(rating ) FROM city įind out the number of distinctive country values: SELECT COUNT(DISTINCT country_id ) FROM city įind out the smallest and the greatest country populations: SELECT MIN(population ), MAX(population ) FROM country įind out the total population of cities in respective countries: SELECT country_id, SUM(population ) FROM cityįind out the average rating for cities in respective countries if the average is above 3.0: SELECT country_id, AVG(rating ) FROM cityĪ subquery is a query that is nested inside another query, or inside another subquery.

min(expr) − minimum value within the group.

max(expr) − maximum value within the group.

count(expr) − count of values for rows within the group.

avg(expr) − average value for rows within the group.

It computes summaries (aggregates) for each unique combination of values. GROUP BY groups together rows that have the same values in specified columns. NATURAL JOIN is very rarely used in practice. NATURAL JOIN used these columns to match rows:Ĭity.id, city.name, country.id, country.name. NATURAL JOIN will join tables by all columns with the same name. SELECT city.name, country.nameĬROSS JOIN returns all possible combinations of rows from both tables.

#Qview qd700 cheat sheet full

SELECT city.name, country.nameįULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned. If there's no matching row, NULLs are returned as values from the left table. RIGHT JOIN returns all rows from the right table with corresponding rows from the left table.

If there's no matching row, NULLs are returned as values from the second table. LEFT JOIN returns all rows from the left table with corresponding rows from the right table. JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables. 'ublin' (like Dublin in Ireland or Lublin in Poland):įetch names of cities that have a population between 500K and 5M:įetch names of cities that don't miss a rating value:įetch names of cities that are in countries with IDs 1, 4, 7, or 8: SAMPLE DATA QUERYING SINGLE TABLEįetch all columns from the country table: SELECT *įetch id and name columns from the city table: SELECT id, nameįetch city names sorted by the rating column in the default ASCending order: SELECT nameįetch city names sorted by the rating column in the DESCending order: SELECT nameĪliases Columns SELECT name AS city_name FROM city įROM city AS ci JOIN country AS co ON ci.country_id = co.id įILTERING THE OUTPUT COMPARISON OPERATORSįetch names of cities that have a rating above 3:įetch names of cities that are neither Berlin nor Madrid:įetch names of cities that start with a 'P' or end with an 's':įetch names of cities that start with any letter followed by It is used in practically all technologies that process data. Today, SQL is a universal language of data. It allows you to select specific data and to build complex reports. SQL, or Structured Query Language, is a language to talk to databases.