JavaScript isn't enabled in your browser, so this file can't be opened. Enable and reload.

1 of 21

CSE 344: Section 1

SQL and SQLite

September 30th, 2021

2 of 21

Review: Database and DBMS

What is a database?

What is a DBMS?

What is a Data Model?

3 of 21

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 of 21

SQL (Structural 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 of 21

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 of 21

SQLite Installation

Linux - Open a terminal, then run the command:

sudo apt-get install sqlite3

Mac -

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

brew install sqlite3

7 of 21

SQLite Installation (con’t)

Windows -

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”
Extract files into directory of your choice
Add that directory to the environment variable “path”
More information -> How to Install and Use SQLite on Windows

8 of 21

Running SQLite

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

sqlite3 [database]

where “database” is the name of the database

Windows -

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

9 of 21

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

10 of 21

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

11 of 21

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

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

12 of 21

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

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

13 of 21

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

14 of 21

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET ….

WHERE [condition];

15 of 21

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

16 of 21

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.

17 of 21

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: =, >, >=, <, <=

18 of 21

SQL Demo!

.mode column

.header ON

CREATE TABLE Faculty (

UserID INT PRIMARY KEY,

Name VARCHAR(256),

Job VARCHAR(256));

INSERT INTO Faculty

VALUES (1, 'Jack', 'TA');

INSERT INTO Faculty

VALUES (2, 'Allison', 'TA'),

(3, 'Magda', 'Prof'),

(4, 'Dan', 'Prof'),

(5, 'Alvin', 'A. Prof');

SELECT F.Name, F.Job

FROM Faculty AS F

WHERE F.Job = 'TA';

DELETE FROM Faculty

WHERE UserID = 1;

UPDATE Faculty

SET Job = 'Prof'

WHERE UserID = 5;

CREATE TABLE Regist (

UserID INT REFERENCES Faculty,

Car VARCHAR(256));

INSERT INTO Regist

VALUES (3, 'Charger'),

(4, 'Civic'),

(4, 'Pinto');

-- inner join

SELECT F.Name,

R.Car

FROM Faculty AS F

JOIN Regist AS R

ON F.UserID = R.UserID;

-- left (outer) join

SELECT F.Name,

R.Car

FROM Faculty AS F

LEFT JOIN Regist AS R

ON F.UserID = R.UserID;

.nullvalue 'NULL'

-- try the same query again

-- right/full join not supported in my version of sqlite3

19 of 21

Payroll Demo

CREATE TABLE Payroll (

UserID int primary key,

Name text,

Job text,

Salary int

);

INSERT INTO Payroll VALUES

(123, 'Jack', 'TA', 50000),

(345, 'Allison', 'TA', 60000),

(567, 'Magda', 'Prof', 90000),

(789, 'Dan', 'Prof', 100000);

CREATE TABLE Regist(UserID int, Car text);

INSERT INTO Regist VALUES

(123, 'Charger'),

(567, 'Civic'),

(567, 'Pinto');

20 of 21

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!

21 of 21

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!