# Structured Query Language (SQL)

## Introduction

Structured Query Language (SQL) is a standard language for storing, manipulating and retrieving data in databases. There are multiple versions of SQL, including MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, and Postgres. There are some syntactical differences between versions, but each operates largely in the same way, supporting at least the same major commands. [\[1\]](#references)

## SQLite

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. For more information, refer to the [SQLite website](https://www.sqlite.org/). [\[2\]](#references)

* Open-source RDBMS (Relational Database Management System)
* Written in C programming language
* Server-less database (“embedded database”)
* Small, fast, self-contained, high-reliability, full-featured, SQL database engine
* Suitable for smaller databases

## MySQL

MySQL is an open-source relational SQL database management system. For more information, refer to the [MySQL website](https://www.mysql.com/). [\[3\]](#references)

* Open-source RDBMS
* Written in C and C++ programming languages
* Requires server-client architecture 
* User management system allows for multiple access
* Suitable for bigger databases and scalability

## PostgreSQL

PostgreSQL is a free and open-source relational SQL database management system. For more information, refer to the [PostgreSQL website](https://www.postgresql.org/).

* Open-source RDBMS
* Better suited for enterprise-level applications, particularly those involving more frequent updates
* More challenging to learn

## SQL Table Basics

* Specify fields in each table and data types for each field 
* Specify fields that should NOT have NULL values
* Define any constraints such as PRIMARY KEY that uniquely identifies each record or FOREIGN KEY that points to a primary key in another table

Create INDEX to improve performance (can be done when table is created or afterwards)

| Data Type     | SQLite                                                                                                                                                                       | MySQL                                                 |
| ------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------- |
| Numeric       | <p>INTEGER</p><p>REAL</p>                                                                                                                                                    | <p>INT(M)</p><p>FLOAT(M,D)</p>                        |
| String        | <p>TEXT</p><p>BLOB</p>                                                                                                                                                       | <p>VARCHAR(M)</p><p>TEXT</p><p>BLOB</p>               |
| Date and Time | <p>Does not have a storage class set aside for storing dates and/or times<br></p><p>Uses <a href="https://www.sqlite.org/lang_datefunc.html">Date And Time Functions</a></p> | <p>DATE</p><p>DATETIME</p><p>TIMESTAMP</p><p>TIME</p> |

## SQL Query Basics

* Queries end with a semicolon (;)
  * Multiple queries in same line separated by ;
  * Single query spanning multiple lines (prompt changes until ; is entered)
* Case sensitive (e.g., table1 ≠ TABLE1)
* Query results returned in a tabular format
  * Column names in first row
  * Results in subsequent rows
  * Total number of rows and execution time

| Command           | SQLite                                                                                                                                                                                                                         | MySQL                                                    |
| ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------- |
| Connect           | $ sqlite3                                                                                                                                                                                                                      | $ mysql                                                  |
| Prompt            | <p>sqlite></p><p>sqlite> </p><p>    …> ;</p><p>sqlite> </p>                                                                                                                                                                    | <p>mysql></p><p>mysql> </p><p>    -> ;</p><p>mysql> </p> |
| Cancel Command    | To get back to sqlite> prompt, close any open quotes or end with “;”                                                                                                                                                           | Type Control-C to cancel a command                       |
| Clear Screen      | Control-L                                                                                                                                                                                                                      | Control-L                                                |
| Get Help          | sqlite> .help                                                                                                                                                                                                                  | <p><br></p>                                              |
| List Databases    | sqlite> .databases                                                                                                                                                                                                             | mysql> SHOW DATABASES;                                   |
| Open Database     | sqlite> .open FILE                                                                                                                                                                                                             | mysql> USE \<DB\_NAME>;                                  |
| List Tables       | sqlite> .tables                                                                                                                                                                                                                | mysql> SHOW TABLES;                                      |
| Show Table Schema | sqlite> .schema TABLE                                                                                                                                                                                                          | mysql> DESCRIBE \<TABLE\_NAME>;                          |
| Import Data       | sqlite> .import FILE TABLE                                                                                                                                                                                                     | <p><br></p>                                              |
| Change mode       | <p>sqlite> .mode</p><p>current output mode: list</p><p>sqlite> .mode ?</p><p>Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl</p><p>sqlite> .mode markdown</p> | <p><br></p>                                              |
| Exit              | sqlite> .exit                                                                                                                                                                                                                  | mysql> exit                                              |

## Labs and Exercises

* <mark style="background-color:red;">Lab: Analyzing the MIMIC-IV Database Demo with SQLite</mark>
* <mark style="background-color:red;">Exercise: Analyzing the MIMIC-IV Database Demo with SQLite</mark>

## Resources

* [SQL Tutorial](https://www.w3resource.com/sql/tutorials.php)
* [SQL Bolt: Learn SQL with Simple, Interactive Exercises](https://sqlbolt.com/)
* [SQL Hospital Database - Exercises, Practice, Solution](https://www.w3resource.com/sql-exercises/hospital-database-exercise/index.php)
* [SQLite Website](https://www.sqlite.org/)
* [MySQL Website](https://www.mysql.com/)
* [PostgreSQL Website](https://www.postgresql.org/)

## References

* \[1] Amazon Web Services. (n.d.). What is SQL? Retrieved April 30, 2024, from <https://aws.amazon.com/what-is/sql/#:~:text=Structured%20query%20language%20(SQL)%20is,relationships%20between%20the%20data%20values>
* \[2] SQLite. (n.d.). Retrieved April 30, 2024, from <https://www.sqlite.org/>
* \[3] Wikipedia contributors. (n.d.). MySQL. In Wikipedia. Retrieved April 30, 2024, <https://en.wikipedia.org/wiki/MySQL>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.bcbi.brown.edu/codiac-for-health/computing/structured-query-language-sql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.