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]
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. [2]
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. [3]
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.
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)
Numeric
INTEGER
REAL
INT(M)
FLOAT(M,D)
String
TEXT
BLOB
VARCHAR(M)
TEXT
BLOB
Date and Time
Does not have a storage class set aside for storing dates and/or times
DATE
DATETIME
TIMESTAMP
TIME
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
Connect
$ sqlite3
$ mysql
Prompt
sqlite>
sqlite>
…> ;
sqlite>
mysql>
mysql>
-> ;
mysql>
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
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
Change mode
sqlite> .mode
current output mode: list
sqlite> .mode ?
Error: mode should be one of: ascii box column csv html insert json line list markdown qbox quote table tabs tcl
sqlite> .mode markdown
Exit
sqlite> .exit
mysql> exit
Labs and Exercises
Lab: Analyzing the MIMIC-IV Database Demo with SQLite
Exercise: Analyzing the MIMIC-IV Database Demo with SQLite
Resources
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
Last updated