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 websitearrow-up-right. [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 websitearrow-up-right. [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 websitearrow-up-right.

  • 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

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

Uses Date And Time Functionsarrow-up-right

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

Command
SQLite
MySQL

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

Last updated