Learn advanced SQL, starting with SQL basics, data types, JSON/XML, and spatial data, and covering advanced techniques for dynamic and efficient database management.

Learning Advanced Concepts of SQL using MySQL - I.png

docker.tar.gz

MySQL Server

MySQL-Server-Code-Widget

MySQL-Server-SPA-Widget

MySQL-with-my-config-SPA-widget

MySQL Server (with Timezones)

MySQL-with-my-config-SPA-widget-copy

Dive into MySQL, a powerful database system for developers handling complex data structures. Learning MySQL equips you with skills for diverse applications, from web development to advanced data analysis, enhancing your ability to manage and manipulate data efficiently.

This course starts with MySQL Shell basics, leading to exploring data types and mastery of SQL statements. You will learn about user-defined variables and table expressions and delve into JSON and XML data handling. The course also covers spatial data management and advanced techniques like recursive queries and window functions. Each module builds upon the last, ensuring a comprehensive understanding of MySQL’s capabilities.

After completing this course, you will have a solid grasp of MySQL and be ready to solve intricate data problems. This expertise opens doors to advanced database roles and opportunities, elevating your career in the tech industry.

Mastering Advanced SQL Techniques with MySQL

A point is a one-dimensional type of geometry.

A line is a two-dimensional type of geometry.

A polygon is a two-dimensional type of geometry.

According to the OpenGIS model, a `LinearRing` is a type of `Polygon`.

**(Select all that apply.)** Which statement(s) is correct? 

(Select all that apply.) Which statement(s) is correct?

`ST_Intersection` and `COUNT(ParkZone.zone)`

The following SQL statement determines the IDs of e-scooters that are parked but do not reside inside a designated parking zone. Which answer correctly fills the two blanks (`<BLANK>`) in the SQL statement in the order of their appearance?

```sql
SELECT Scooter.id AS `ID`
FROM Scooter
 JOIN ParkZone
 ON NOT <BLANK>(ParkZone.zone, Scooter.location)
GROUP BY Scooter.id
HAVING <BLANK> = (SELECT COUNT(id) FROM ParkZone);
```

The following SQL statement determines the IDs of e-scooters that are parked but do not reside inside a designated parking zone. Which answer correctly fills the two blanks (<code>&lt;BLANK&gt;</code>) in the SQL statement in the order of their appearance?
<pre><code class="language-sql">SELECT Scooter.id AS `ID`
FROM Scooter
 JOIN ParkZone
 ON NOT &lt;BLANK&gt;(ParkZone.zone, Scooter.location)
GROUP BY Scooter.id
HAVING &lt;BLANK&gt; = (SELECT COUNT(id) FROM ParkZone);
</code></pre>

**(Select all that apply.)** Which types of spatial entities does MySQL support? 

(Select all that apply.) Which types of spatial entities does MySQL support?

`SELECT ST_AsGeoJSON(ST_GeomFromGeoJSON(@geojson), 4, 4)`

`SELECT ST_AsText(ST_AsGeoJSON(ST_GeomFromGeoJSON(@geojson), 8, 4))`

`SELECT ST_AsGeoJSON(ST_GeomFromGeoJSON(@geojson), 8, 4)`

`SELECT ST_AsGeoJSON(ST_GeomFromGeoJSON(@geojson), 8, 5)`

Suppose that `@geojson` is a user-defined variable that stores a GeoJSON document. Which SQL statement below produces the following output?

```json
{
  "crs": {
    "type":"name",
    "properties": {
      "name":"urn:ogc:def:crs:EPSG::3857"
    }
  },
  "type":"GeometryCollection",
  "geometries": [
    {
      "type":"Point",
      "coordinates": [
        1489139.3750614,
        6894445.78475996
      ]
    }
  ]
}
```

Suppose that <code>@geojson</code> is a user-defined variable that stores a GeoJSON document. Which SQL statement below produces the following output?
<pre><code class="language-json">{
 &quot;crs&quot;: {
 &quot;type&quot;:&quot;name&quot;,
 &quot;properties&quot;: {
 &quot;name&quot;:&quot;urn:ogc:def:crs:EPSG::3857&quot;
 }
 },
 &quot;type&quot;:&quot;GeometryCollection&quot;,
 &quot;geometries&quot;: [
 {
 &quot;type&quot;:&quot;Point&quot;,
 &quot;coordinates&quot;: [
 1489139.3750614,
 6894445.78475996
 ]
 }
 ]
}
</code></pre>

The function `ST_Longitude()` only applies to geographic spatial reference systems (SRS), but the provided argument is embedded in the cartesian SRS 3587. Hence, the function’s call produces an error.

What is the result of executing the following SQL statement?

```sql
SELECT ST_Longitude(ST_PointFromText('POINT(13.379429 52.516561)', 3587));
```

What is the result of executing the following SQL statement?
<pre><code class="language-sql">SELECT ST_Longitude(ST_PointFromText('POINT(13.379429 52.516561)', 3587));
</code></pre>

Test your knowledge of spatial data in MySQL.

Getting Started

Data Types

SQL Statements

Flow Control Functions

String Functions

Date and Time Functions

Aggregate Functions

Window Functions

Recursion

JSON and XML

Spatial Data

Mastering Advanced SQL Techniques with MySQL

Conclusion

Quiz: Spatial Data