MySQL Syntax

Posted on | In , , |

SQL Resources

Return a list of India’s UNESCO heritage sites

  • AS — aliasing of column names
  • LEFT JOIN — returns all records in table X that have a matching record in table Y as well as records in table X that have no matching record in table Y
  • TRIM(value) — removes leading as well as whitespace in a string

Example

1
2
3
4
5
6
7
8
9
10
SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`, hsc.category_name AS `category`
FROM heritage_site hs
LEFT JOIN heritage_site_jurisdiction hsj
  ON hs.heritage_site_id = hsj.heritage_site_id
LEFT JOIN country_area ca
  ON hsj.country_area_id = ca.country_area_id
LEFT JOIN heritage_site_category hsc
  ON hs.heritage_site_category_id = hsc.category_id
WHERE TRIM(ca.country_area_name) = 'India'
ORDER BY hs.site_name;

Return a count of Indian UNESCO Heritage Sites by heritage site category

  • COUNT(*) — count of the number of heritage sites returned by category
  • COUNT(DISTINCT hs.heritage_site_id) — filters out duplicate rows produced by a heritage site located in more than one country / area
  • GROUP BY — follows the WHERE clause and groups the result set by region, subregion, country / area, category
1
2
3
4
5
6
7
8
9
10
11
SELECT ca.country_area_name AS `country / area`, hsc.category_name AS `category`, COUNT(*) AS `heritage site count`
FROM heritage_site hs
  LEFT JOIN heritage_site_jurisdiction hsj
    ON hs.heritage_site_id = hsj.heritage_site_id
  LEFT JOIN country_area ca
    ON hsj.country_area_id = ca.country_area_id
  LEFT JOIN heritage_site_category hsc
    ON hs.heritage_site_category_id = hsc.category_id
WHERE TRIM(ca.country_area_name) = 'India'
GROUP BY ca.country_area_name, hsc.category_name
ORDER BY hsc.category_name;

[caption]

The ca.country_area_name has to be included in the GROUP BY list, since MySQL implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Reference)

Return a list of heritage sites that includes the word ‘Lake’ in the name

  • INNER JOIN — returns all records in table X that have a matching record in table Y. Replacing each INNER JOIN with a LEFT JOIN returns the same results

  • AND — requires that all conditions separated by AND return true

  • INSTR(string, substring) — returns the first occurrence of the substring in the string value

  • %\[value\]% — use of wildcards in the search string

1
2
3
4
5
6
7
8
9
10
11
SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`,
       hsc.category_name AS `category`
  FROM heritage_site hs
       INNER JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       INNER JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
       INNER JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
WHERE hsc.category_id = 2 AND hs.site_name LIKE '%Lake%'
ORDER BY ca.country_area_name, hs.site_name;
1
2
3
4
5
6
7
8
9
10
11
SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`,
       hsc.category_name AS `category`
  FROM heritage_site hs
       INNER JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       INNER JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
       INNER JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
WHERE hsc.category_id = 2 AND hs.site_name LIKE '%Lake%'
ORDER BY ca.country_area_name, hs.site_name;

Return list of castles, fortifications and walled cities and towns inscribed as UNESCO heritage sites

  • OR — an operator that requires that any condition separated by OR return true

  • REGEX — leverage regular expressions as a filter (e.g., akin to the regexp '/([a-zA-Z0-9])Castle([a-zA-Z0-9])/g').

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`, SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`,
       hsc.category_name AS `category`
  FROM heritage_site hs
       LEFT JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       LEFT JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       LEFT JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
 WHERE hsc.category_name = 'Cultural' AND hs.site_name REGEXP 'Castle|Citadel|Fort|Wall'
 ORDER BY ca.country_area_name, hs.site_name;hsc.category_name AS `category`
  FROM heritage_site hs
       LEFT JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       LEFT JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       LEFT JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
 WHERE hsc.category_name = 'Cultural' AND (hs.site_name LIKE '%Castle%'
       OR hs.site_name LIKE '%Citadel%'
       OR hs.site_name LIKE '%Fort%'
       OR hs.site_name LIKE '%Wall%')
 ORDER BY ca.country_area_name, hs.site_name;
1
2
3
4
5
6
7
8
9
10
SELECT ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`, hsc.category_name AS `category`
  FROM heritage_site hs
       LEFT JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       LEFT JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       LEFT JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
 WHERE hsc.category_name = 'Cultural' AND hs.site_name REGEXP 'Castle|Citadel|Fort|Wall'
 ORDER BY ca.country_area_name, hs.site_name;

Return British, French, German and Dutch UNESCO heritage sites inscribed between 2010-2018

  • REPLACE(value, replace, replace with) — cosmetic; substitutes the “UK” acronym for the official country name.

  • IN(value1, value2, ...) — an operator that permits multiple values to be referenced in the WHERE clause.

  • BETWEEN — — an operator that selects values within a given range, including the begin and end values.

1
2
3
4
5
6
7
8
9
10
SELECT REPLACE(ca.country_area_name, 'United Kingdom of Great Britain and Northern Ireland', 'UK') AS `country / area`, hs.site_name AS `heritage site`, hsc.category_name AS `category`, hs.date_inscribed AS `date inscribed`
  FROM heritage_site hs
       INNER JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       INNER JOIN heritage_site_category hsc
              ON hs.heritage_site_category_id = hsc.category_id
       INNER JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
WHERE ca.iso_alpha3_code IN ('DEU', 'FRA', 'GBR', 'NLD') AND hs.date_inscribed BETWEEN 2010 AND 2018
ORDER BY hs.date_inscribed DESC, ca.country_area_name, hs.site_name;

Return a list of heritage site counts by region and subregion

WHERE != — clause condition that excludes Antarctica from the search results

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT r.region_name AS `region`, sr.sub_region_name AS `subregion`, COUNT(DISTINCT hs.heritage_site_id) AS `heritage sites`
  FROM heritage_site hs
       INNER JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       INNER JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       INNER JOIN location l
              ON ca.location_id = l.location_id
       INNER JOIN region r
              ON l.region_id = r.region_id
       INNER JOIN sub_region sr
              ON l.sub_region_id = sr.sub_region_id
WHERE TRIM(ca.country_area_name) != 'Antarctica'
GROUP BY r.region_name, sr.sub_region_name
ORDER BY r.region_name, sr.sub_region_name;

Return the largest UNESCO heritage site by area (hectares) in the Caribbean

  • MAX(value) — returns the maximum value, in this case, area (hectares).

  • \G — terminator that informs MySQL to print the result set in vertical output form.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT r.region_name AS `region`, sr.sub_region_name AS `subregion`,  
       ir.intermediate_region_name AS `intermediate region`,
       ca.country_area_name AS `country / area`, hs.site_name AS `heritage site`,
       hs.area_hectares AS `area (hectares)`
  FROM heritage_site hs
       LEFT JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       LEFT JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       LEFT JOIN location l
              ON ca.location_id = l.location_id
       LEFT JOIN region r
              ON l.region_id = r.region_id
       LEFT JOIN sub_region sr
              ON l.sub_region_id = sr.sub_region_id
       LEFT JOIN intermediate_region ir
              ON l.intermediate_region_id = ir.intermediate_region_id
 WHERE TRIM(ir.intermediate_region_name) = 'Caribbean'
       AND hs.area_hectares = (SELECT MAX(hs1.area_hectares)
                                 FROM heritage_site hs1
                                      LEFT JOIN heritage_site_jurisdiction hsj1
                                             ON hs1.heritage_site_id = hsj1.heritage_site_id
                                      LEFT JOIN country_area ca1
                                             ON hsj1.country_area_id = ca1.country_area_id
                                      LEFT JOIN location l1
                                             ON ca1.location_id = l1.location_id
                                      LEFT JOIN intermediate_region ir1
                                             ON l1.intermediate_region_id = ir1.intermediate_region_id
                                WHERE TRIM(ir1.intermediate_region_name) = 'Caribbean')\G

Return the total area (in hectares) per region that have been protected by the UNESCO heritage site designation

  • SUM() — aggregate function that, in combination with the GROUP BY, provides the total area of heritage sites located in a given region.

  • CAST(value AS datatype) — casts area_hectares (a DOUBLE) as a DECIMAL datatype in order to ensure that the ORDER BY returns the result set in descending order (FLOAT and DOUBLE lack of precision can confound proper ordering).

  • ROUND(number, decimals) — rounds a number to a specified number of decimal places (in this case zero places).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT r.region_name AS 'region',
       ROUND(CAST(SUM(hs.area_hectares) AS DECIMAL(10,1))) AS `area (hectares)`
  FROM heritage_site hs
       LEFT JOIN heritage_site_jurisdiction hsj
              ON hs.heritage_site_id = hsj.heritage_site_id
       LEFT JOIN country_area ca
              ON hsj.country_area_id = ca.country_area_id
       LEFT JOIN location l
              ON ca.location_id = l.location_id
       LEFT JOIN region r
              ON l.region_id = r.region_id
 WHERE TRIM(ca.country_area_name) != 'Antarctica'
 GROUP BY r.region_name
 ORDER BY `area (hectares)` DESC;

Return a list of heritage sites, if any, that span regional boundaries

  • HAVING — acts as a filter on the GROUP BY clause, eliminating rows that do not meet the condition specified
  • GROUP_CONCAT() — entirely decorative but handy nonetheless since it augments the raw counts with a list of the regions associated with each site.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT hs.site_name AS `heritage site`,
          GROUP_CONCAT(DISTINCT r.region_name ORDER BY r.region_name SEPARATOR ', ') AS `regions`,
          COUNT(DISTINCT r.region_id) as `region count`
     FROM heritage_site hs
          INNER JOIN heritage_site_jurisdiction hsj
                  ON hs.heritage_site_id = hsj.heritage_site_id
          INNER JOIN country_area ca
                  ON hsj.country_area_id = ca.country_area_id
          INNER JOIN location l
                  ON ca.location_id = l.location_id
          INNER JOIN region r
                  ON l.region_id = r.region_id
    GROUP BY hs.site_name
   HAVING COUNT(DISTINCT r.region_id) > 1
    ORDER BY `region count` DESC;