SQL Cheat Sheet -1-
Sql or Structured Query Language, it allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in pratically all technologies that process data.
SAMPLE DATA
QUERYING SINGLE TABLE
- Fetch all columns from the country table:
- Fetch id and name columns from the city table:
- Fetch city names sorted by the rating column in the default Ascending order:
- Fetch city names sorted by the rating column in the Descending order:
USE OF “AS”
With the AS expression, we can temporarily give short names to long and difficult to use table or field names
COLUMNS
TABLES
FILTERING THE OUTPUT
COMPARISON OPERATORS
- Fetch names of cities that have a rating above 3:
- Fetch names of cities that are neither Berlin nor Madrid:
TEXT OPERATORS
- Fetch names of cities that start with a ‘P’ or end with an ‘s’:
- Fetch names of cities that start with any letter followed by ‘ublin’ (like Dublin in Ireland or Lublin in Poland):
OTHER OPERATORS
- Fetch names of cities that have a population between 500K and 5M:
- Fetch names of cities that don’t miss a rating value:
- Fetch name of cities that are in countries with IDs 1,4,7 or 8:
GROUP BY
Group By, groups together rows that have the same values in columns
FUNCTIONS
- avg() : average value for rows within the group
- count() : count of values for rows within the group
- max() : max value within the group
- min() : min value within the group
- sum() : sum of values within the group
EXAMPLE QUERIES
- Find out the number of cities:
- Find out the number of cities with non-null ratings:
- Find out the number of distinctive country values:
- DISTINCT: The Distinct statement allows the repetitive data in certain columns of the table to be retrieved as one.
- Find out the smallest and the greatest country populations:
- Find out the total population of cities in respective countries:
- Find out the average rating for cities in respective countries if the average is above 3.0:
SUBQUERIES
A subquery is a query that is nested inside another query, or inside another
subquery. There are different types of subqueries.
SINGLE VALUE
- Finds cities with the same rating as Paris:
MULTİPLE VALUES
- Finds cities in countries that have a population above 20M:
CORRELATED
A correlated subquery refers to the tables introduced in the outer query.
A correlated subquery depends on the outer query. It cannot be run
independently from the outer query.
- Finds cities with a population greater than the average population in the country:
- Finds countries that have at least one city:
- Displays Deutsch cyclists together with Deutsch skaters:
UNION: we can combine the fields we choose in our two table and use them as if they are a single table. When combining fields in two tables with union, repetitive records are taken once. If repetitive records are required, unıon all should be used.
INTERSECT: It will list the records found in both sets A and B.
- Displays DE cyclists who are also DE skaters at the same time:
EXCEPT: returns only the rows that appear in the first result set but do not appear in the second result set.
- Displays DE cyclists unless they are also DE skaters at the same time:
QUERYING MULTIPLE TABLES
INNER JOİN :The INNER JOIN statement is used to join tables with a common value.
LEFT JOİN: It is used to combine all rows in the first selected table and matching rows in the second selected table.
If the values in the first table do not match the values in the second table, it gets null.
RİGHT JOİN: It is used to merge all rows from the second selected table and the matching rows from the first selected table.
Returns null if there is a value in the second table that does not match the first table
FULL JOİN: (or explicitly FULL OUTER JOIN) returns all rows from both tables — if there’s no matching row in the second table, NULLs are returned.
NATURAL JOİN: will join tables by all columns with the same name.
