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

1 of 26

Hello World! � ~SQLite intro

CSE 414, Autumn '24

Section 1

9/26/24

2 of 26

David Cao (he/him)

4th year CS
1st time TAing CSE 414 (took it autumn 2022)
Finished my first half marathon in summer
Office Hours: Tuesday 11:30 - 12:30 @ Allen 3th floor breakout

3 of 26

Derek Zhu (he/him)

4th year CS & ACMS major, Biology minor
1st time TAing CSE 414 (took it autumn 22)
interned at amazon this summer, didnt get pipped
Office Hours: Wednesday 2:30-3:30pm @ Gates 151

4 of 26

Ice Breakers

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

Your name
What you're looking forward to learning about in CSE 414
Something fun you did over summer break

5 of 26

Announcements

Let us know if you're unable to access Gradescope or Ed
HW 1 due <Friday October 4th>

Intro to SQL and sqlite3
Submitted via Gradescope
Visit OH or post on Ed if you're stuck!
Office hours start on Monday, exact time TBD

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 -

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!

.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

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!