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

1 of 25

Hello World! � ~SQLite intro

CSE 414, Autumn '26

Section 1

10/01/26

2 of 25

Announcements

Let us know if you're unable to access Gradescope or Ed
HW 1 due 9th at 11:59pm

Intro to SQL and sqlite3
Submitted via Gradescope
Visit OH or post on Ed if you're stuck!
Office hours start next week, see course website for more details

Questions?

3 of 25

Your TAs

Amelia Li

CS + Informatics
Interests: My cats, boba, badminton

Office Hours: Probably Wed 2:30-3:30

Sylvia Che

CS + professional sales program
Interests: origami, games, volleyball

Office Hours: probably tue 1230-130

4 of 25

Ice Breakers

Let's get to know each other! Please share...

Your name & major
What you're looking forward to learning about in CSE 414 & why you took this class
Something fun you did over summer break

5 of 25

Review: Database and DBMS

What is a database?

What is a DBMS?

6 of 25

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

7 of 25

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.

8 of 25

SQLite: What is it?

C library that implements a relational database management system (RDBMS)
sqlite3: a standalone program that can run programs and manage a SQLite database
Here and here are links to helpful documentation

9 of 25

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

10 of 25

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

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

11 of 25

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

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

12 of 25

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

13 of 25

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET ….

WHERE [condition];

14 of 25

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

15 of 25

SQLite: Basic SQL Statements

ALTER: modify an existing table’s attributes/characteristics

[ex] ALTER TABLE tableName

ADD COLUMN columnName columnDatatype;

[ex] ALTER TABLE tableName DROP COLUMN columnName;

(Note: SQLite does not support dropping an attribute for versions prior to 3.35.5)

[ex] ALTER TABLE oldName RENAME TO newName;

16 of 25

What if we want to add more data?

We have a table regarding Companies. What if we want to also add the products they manufacture? How can we add this information?

Hint: Tables have to be FLAT in SQL

Answer: Create another table!

17 of 25

SQL Foreign Keys

A column (or a collection of columns) in one table that refers to the Primary Key of another table

Used to establish a link between two tables
Requirement: When declared, foreign key values must also be in the primary key values of the linked table

18 of 25

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.

19 of 25

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

20 of 25

SQLite Installation

.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

21 of 25

SQLite Installation

Linux - Open a terminal, then run the command:

sudo apt-get install sqlite3

Mac -

Check if installed: sqlite3 --version
Download Homebrew: instructions @ https://brew.sh/
Open a terminal, then run the command:

brew install sqlite3

22 of 25

SQLite Installation (con’t)

Windows -

Go to https://www.sqlite.org/download.html and download the third option down under “Precompiled Binaries for Windows”
Extract files into directory of your choice
Add that directory to the environment variable “path”

Video walkthrough: https://www.youtube.com/watch?v=XA3w8tQnYCA&t=2s

23 of 25

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]

24 of 25

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

25 of 25

Didn’t understand everything or having trouble with SQLite install? 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!