1 of 22

CSE 344: Section 1

SQL and SQLite

January 6th, 2022

2 of 22

Administrivia

  • HW1 is due on 1/10 at 11:59 pm. Please start soon.
  • We have tons of office hours today, tomorrow, and monday.
  • Refer to canvas and the schedule page on the course website and reach out on Ed if you have any other questions

​

2

3 of 22

Review: Database and DBMS

  • What is a database?

​

  • What is a DBMS?

​

  • What is a Data Model?

​

3

4 of 22

Review: Database and DBMS

  • What is a database?
  • Collection of organized files containing related data persisting over a long period of time
  • What is a DBMS?
  • Program that allows for efficient management of large databases
  • What is a Data Model?
    • It is how we can talk about data conceptually without having to think about implementation.

​

4

5 of 22

SQL (Structured Query Language)

  • Language designed for managing data held in a relational database management system (RDBMS)

​

  • Declarative query language

​

  • What can it do?
    • Data insert, delete, query, schema creation, etc.

5

6 of 22

SQLite: What is it?

  • C library that implements a relational data management system (RDBMS)

​

  • sqlite3: a standalone program that can run programs and manage an SQLite database

​

6

7 of 22

SQLite Installation

Linux -

  • Already installed on attu
  • Open a terminal, then run the command if not using attu:

sudo apt-get install sqlite3

Mac -

  1. Download Homebrew: instructions @ https://brew.sh/
  2. Open a terminal, then run the command:

brew install sqlite3

7

8 of 22

SQLite Installation (con’t)

Windows -

  1. Go to https://www.sqlite.org/download.html and download the third option down (sqlite-tools-win32-x86-3360000.zip) under “Precompiled Binaries for Windows”
  2. Extract files into directory of your choice
  3. Add that directory to the environment variable “path”
  4. More information -> How to Install and Use SQLite on Windows

​

8

9 of 22

Running SQLite

Linux/Mac - Open a terminal, then run the command:

sqlite3 [database]

where “database” is the name of the database

Windows -

  1. In cmd, go to directory where you extracted sqlite3.exe files
  2. Run the command: sqlite3 [database]

9

10 of 22

Recommended Text Editors/IDEs

  • For a simple, lightweight experience we would recommend using atom or sublime
    • Colors help you notice if you are coding properly in SQL
  • If you want to use an IDE (will be more helpful later in the quarter), we recommend using VSCode. Also useful, when connecting via ssh. Reach out on ED if you need help with this.

11 of 22

11

Questions on installation �or running SQLite? �Post on Ed Board or visit us during OH!

12 of 22

SQLite: Special Commands

.help - list other . commands

.header on/off - show/hide column headers in query results

.mode [mode type] - change how to separate the columns in each row/tuple (for better formatting)

Mode type examples: csv, tabs, line

.show - lists all display options

12

13 of 22

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

[ex] CREATE TABLE tableName (columnName int, ... );

13

14 of 22

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

[ex single tuple] INSERT INTO tableName VALUES (value1, …);

[ex multiple tuples] INSERT INTO tableName (columnList, …)

VALUES

(value1a, …)

(value2a, …)

…;

14

15 of 22

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

15

16 of 22

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET ….

WHERE [condition];

16

17 of 22

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

17

18 of 22

SQLite: Special Operators

DATE operator: lets you work with dates and times; declare as varchar (see hw1 documentation)

[ex] SELECT * FROM tableName WHERE dateColumn ='YYYY-MM-DD';

SELECT * FROM tableName WHERE dateColumn < DATE('now', '-1

month');

Other operators: LIKE, LENGTH(string), SUBSTR(string, start index, end index), etc.

18

19 of 22

More SQL (For Reference)

  • WHERE clause - filter records
  • AND, OR operator - filter records based on more than one condition
  • LIKE operator - used in a WHERE clause to search�for a specified pattern in a column
  • AS - give an alias name to a table or a column
  • Relational operators: =, >, >=, <, <=

19

20 of 22

SQL Demo!

20

21 of 22

Google Chrome SQLite demo

https://stackoverflow.com/questions/8936878/where-does-chrome-save-its-sqlite-database-to

​

Did you know Google Chrome stores some things in a SQLite database file?

Explore on your computer!

22 of 22

22

Didn’t understand everything? That’s okay! This was just a preview.

​

SQL basics will be explained further in lecture before your homework is due.

​

*Post on Ed or come to OH with questions!