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

1 of 27

Hello World! � ~SQLite intro

CSE 414 Winter 2022

Section 1 AD/BD

2022-01-06

2 of 27

Aaditya Desai

Senior in Electrical Engineering
Enjoy playing and watching sports, especially soccer

3 of 27

Snigdha Goel

Senior in CSE - Data Science
Fourth time TA for 414
Enjoy photography!

4 of 27

Introductions

What is your name, year, major?
What are you looking forward to the *most* in 2022?

5 of 27

Announcements

Make sure you have access to our Ed discussion page and Canvas and Gradescope!
Check out the class website (https://sites.google.com/cs.washington.edu/cse414-22wi/ )
HW1 released!
Reach out if you have any questions/concerns :)

6 of 27

DB Review & Using SQLite

7 of 27

Review: Database and DBMS

What is a database?

What is a DBMS?

8 of 27

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

9 of 27

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.

10 of 27

11 of 27

12 of 27

SQLite: What is it?

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

13 of 27

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

14 of 27

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

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

15 of 27

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

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

[ex] INSERT INTO tableName

VALUES (a1, b1, c1, …), (a2, b2, c2, …), … ;

16 of 27

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

17 of 27

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET tableName.attributeA = “abc”

WHERE [condition];

18 of 27

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

19 of 27

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 27

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 27

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 27

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 27

SQLite Installation (con’t)

Windows -

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

24 of 27

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 27

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 27

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');

Add REFERENCES Payroll

Include sqlite3 setup, JOIN, DATES, ‘dot’ commands, different ways to INSERT, rigid typing parameters (and why you should avoid them)

SELECT * FROM Payroll;

.show

.headers on

.mode col

SELECT P.Name, P.UserID

FROM Payroll AS P

WHERE P.Job = ‘TA’;

SELECT P.Name, R.Car

FROM Payroll AS P JOIN Regist AS R

ON P.UserID = R.UserID;

What type of join is this?

SELECT P.Name, R.Car

FROM Payroll AS P, Regist AS R

WHERE P.UserID = R.UserID;

What type of join is this?

SELECT *

FROM Payroll AS P, Regist AS R;

How many rows will I get from this query? Why?

SELECT P.Name, R.Car

FROM Payroll AS P LEFT OUTER JOIN Regist AS R

ON P.UserID = R.UserID;

What would happen if we did a right outer join?

Find all people who drive a Civic and Pinto?

DOESN’T WORK

SELECT P.Name, R.Car

FROM Payroll AS P, Regist AS R

WHERE P.UserID = R.UserID AND

R.Car = ‘Civic’ AND

R.Car = ‘Pinto’;

WORKS

SELECT P.Name, R1.Car

FROM Payroll AS P, Regist AS R1, Regist AS R2

WHERE P.UserID = R1.UserID AND

P.UserID = R2.UserID AND

R1.Car = ‘Civic’ AND

R2.Car = ‘Pinto’;

27 of 27

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!