# Overview Structured Query Language (SQL) is the most popular language used for handling and interacting with data that is stored in a structured manner, such as with a supporting schema. ## Summary Characteristics | Open Source | Paradigm | Typing System | Compilation | D vs. I | | ----------- | -------- | ------------- | ----------- | --------------- | | | | | | [[Declarative]] | ## Documentation Link(s) ## Pro and Cons Summary ### Pros ### Cons # Key Characteristics ## Command Types - DDL - Data Definition Language - Commands to create and modify database structure - `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE` - DQL - Operators - Functions - Data Types - DML - Data Manipulation Language - Commands to manage data within tables - `SELECT`, `INSERT`, `UPDATE`, `DELETE` - DCL - Data Control Language - Commands to control access permissions - `GRANT`, `REVOKE` - TCL - Transaction Control Language - Commands to manage transactions - `BEGIN`, `COMMIT`, `ROLLBACK` ## Key Features ### Variable and Data Type Declarations ### Control Flow Structures #### Recursion Complicated recursive queries can be created using common table expressions (CTEs) (`WITH RECURSIVE`). Here is an example: > [!Coding Example]- > Finding the names of people who emigrated from the US to Europe > ```SQL > WITH RECURSIVE > -- in_usa is the set of vertex IDs of all locations within the United States > in_usa(vertex_id) AS ( > SELECT vertex_id FROM vertices WHERE properties->>'name' = 'United States' > UNION > SELECT edges.tail_vertex FROM edges > JOIN in_usa ON edges.head_vertex = in_usa.vertex_id > WHERE edges.label = 'within' > ), > -- in_europe is the set of vertex IDs of all locations within Europein_europe(vertex_id) > AS (SELECT vertex_id FROM vertices WHERE properties->>'name' = 'Europe' > UNION > SELECT edges.tail_vertex FROM edges > JOIN in_europe ON edges.head_vertex = in_europe.vertex_id > WHERE edges.label = 'within' > ), > -- born_in_usa is the set of vertex IDs of all people born in the US > born_in_usa(vertex_id) AS ( > SELECT edges.tail_vertex FROM edges > JOIN in_usa ON edges.head_vertex = in_usa.vertex_id > WHERE edges.label = 'born_in' > ), > -- lives_in_europe is the set of vertex IDs of all people living in Europe > lives_in_europe(vertex_id) AS ( > SELECT edges.tail_vertex FROM edges > JOIN in_europe ON edges.head_vertex = in_europe.vertex_id > WHERE edges.label = 'lives_in' > ) > SELECT vertices.properties->>'name' > FROM vertices > -- join to find those people who were both born in the US *and* live in Europe > JOIN born_in_usa ON vertices.vertex_id = born_in_usa.vertex_id > JOIN lives_in_europe ON vertices.vertex_id = lives_in_europe.vertex_id; > ``` > 1. First find the vertex whose name property has the value "United States", and > make it the first element of the set of vertices in_usa. > 2. Follow all incoming within edges from vertices in the set in_usa, and add them > to the same set, until all incoming within edges have been visited. > 3. Do the same starting with the vertex whose name property has the value > "Europe", and build up the set of vertices in_europe. > 4. For each of the vertices in the set in_usa, follow incoming born_in edges to find > people who were born in some place within the United States. > 5. Similarly, for each of the vertices in the set in_europe, follow incoming lives_in > edges to find people who live in Europe. > 6. Finally, intersect the set of people born in the USA with the set of people living in Europe, by joining them. ### Error Handling Mechanisms # Use Cases # Performance ## Memory Management ## [[Concurrency and Parallelism]] Support # Ecosystem and Integration ## [[Integrated Development Environments]] (IDEs) ## Package Managers and Dependency Management # Interoperability # Documentation Links ## Standard Libraries and Modules ## Reference #### Working Notes #### Sources