1 of 26

Hello World! � ~SQLite intro

CSE 414, Fall '25

Section 1 - CD

09/25/25

2 of 26

Your TAs

Moses Lurbur

BS in CS and Econ
ex Amazon and NOAA
CS PhD
Outside of school: getting outside! 🏔️🌊🌳

OHs:

Wednesday 3:30-4:30pm Allen 5nd Floor Breakout

3 of 26

Your TAs

Maria Protogerou

AMATH DS and Business
Athens, GR -> Baltimore, MD -> Portland, OR -> Seattle, WA

OHs:

Wednesday 1:00pm - 2:00pm, Gates Center 150

4 of 26

Ice Breakers

Let's get to know each other!

Small groups of 4-6ish
Please share…

Your name and major
What you're looking forward to learning about in CSE 414
Favorite memory from summer break
Largest mammal you’ve ever seen

5 of 26

Announcements

Let us know if you're unable to access Gradescope or Ed
HW 1 due Friday, October 3rd 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 times

Questions?

6 of 26

Review: Database and DBMS

What is a database?

What is a DBMS?

7 of 26

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

8 of 26

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.

9 of 26

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

10 of 26

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 26

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

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

12 of 26

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

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

13 of 26

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

14 of 26

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET ….

WHERE [condition];

15 of 26

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

16 of 26

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;

17 of 26

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!

18 of 26

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

19 of 26

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.

20 of 26

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

21 of 26

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

22 of 26

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

23 of 26

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

24 of 26

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]

25 of 26

SQL Demo!

-- 1 Creating tables

create table Company

(cname varchar(20) primary key,

country varchar(20),

no_employees int,

for_profit char(1));

insert into Company values ('GizmoWorks', 'USA', 20000,'y');

insert into Company values ('Canon', 'Japan', 50000,'y');

insert into Company values ('Hitachi', 'Japan', 30000,'y');

insert into Company values('Charity', 'Canada', 500,'n');

select * from Company;

-- Making sure SQL Lite shows us the data in a nicer format

-- These commands are specific to SQLite!

.header on

.mode column

.nullvalue NULL

-- Comment: upper/lower case; name conflicts

-- -- Company, company, COMPANY = all the same

-- -- Company(cname, country), Person(pname, country) = repeated 'country' OK

-- -- Company(cname, country), Person(pname, company) = the attribute 'company' not ok

-- Null values: whenever we don't know the value, we can set it to NULL

insert into Company values('MobileWorks', 'China', null, null);

select * from Company;

-- Deleting tuples from the database:

delete from Company where cname = 'Hitachi';

select * from Company;

delete from Company where for_profit = 'n';

-- what happens here??

select * from Company;

-- cname is a key and SQL will ensure that it is unique:

insert into Company values('Canon', 'Japan', null, null);

-- Error: UNIQUE constraint failed

-- Alerts: sql lite is REALLY light and accepts some erroneous commands

-- Alert 1: sqlite allows a key to be null

insert into Company values(NULL, 'Somewhere', 0, 'n');

select * from Company;

-- this is dangerous, since we cannot uniquely identify the tuple

-- better delete it before we get into trouble

delete from Company where country = 'Somewhere';

select * from Company;

-- Discussion in class:

-- tables are NOT ordered. They represent sets or bags.

-- tables do NOT prescribe how they should be implemented: PHYSICAL DATA INDEPENDENCE!

-- tables are FLAT (all attributes are base types)

------------------------------------------

-- 2 Altering a table in SQL

-- Add/Drop attribute(s)

-- let's drop the for_profit attribute

-- Note: In 2021, SQLite3 added support for dropping an attribute as part of its version 3.35.5 release

-- (https://www.sqlite.org/releaselog/3_35_5.html). As such, this command does work

-- if you have a SQLite3 version after 3.35.5, but doesn't work for any version prior.

ALTER TABLE Company DROP for_profit;

ALTER TABLE Company ADD ceo varchar(20);

select * from Company;

UPDATE Company SET ceo='Brown' WHERE cname = 'Canon';

SELECT * FROM Company;

-- A peek at the physical implementation:

-- What happens when you alter a table ? Consider row-wise and column-wise.

------------------------------------------

-- 3 Multiple Tables, and Keys/Foreign-Keys

-- Now alter Company to add the products that they manufacture.

-- Problem: can't add an attribute that is a LIST OF PRODUCTS. What should we do??

-- Create a separate table Product, with a foreign key to the company:

create table Product

(pname varchar(20) primary key,

price float,

category varchar(20),

manufacturer varchar(20) references Company);

-- Alert 2: sqlite does NOT enforce foreign keys by default. To enable

-- foreign keys use the following command. The command will have no

-- effect if your version of SQLite was not compiled with foreign keys

-- enabled. Do not worry about it.

PRAGMA foreign_keys=ON;

insert into Product values('Gizmo', 19.99, 'gadget', 'GizmoWorks');

insert into Product values('PowerGizmo', 29.99, 'gadget', 'GizmoWorks');

insert into Product values('SingleTouch', 149.99, 'photography', 'Canon');

insert into Product values('MultiTouch', 199.99, 'photography', 'MobileWorks');

insert into Product values('SuperGizmo', 49.99, 'gadget', 'MobileWorks');

-- now it enforces foreign kyes:

insert into Product values('Hoverboard', 299.99, 'entertainment', 'NewCompany');

-- Error: FOREIGN KEY constraint failed

26 of 26

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!