1 of 28

Hello World! � ~SQLite intro

1

CSE 344

Section 1

2 of 28

TA Intro:

2

3 of 28

TA Intro: [name]

[Intro]
Fun Fact:

3

4 of 28

Ice Breakers

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

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

4

5 of 28

Announcements

Let us know if you're unable to access Gradescope or Ed
HW 1 due Tuesday Apr 8 @ 10:00pm

Intro to SQL and sqlite3
Link is posted on course calendar; submit on Gradescope
Multiple parts: SQL coding, Java coding, some reading
Visit SH or post on Ed if you're stuck!

Questions?

5

6 of 28

Review: Database and DBMS

What is a database?

What is a DBMS?

6

7 of 28

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

8 of 28

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

9 of 28

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

10 of 28

SQLite: Special Commands

.help - list other . commands

.headers 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

11 of 28

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

11

CREATE TABLE tableName (colName INT, ...);

12 of 28

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

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

12

INSERT INTO tableName VALUES(value1, ...);

13 of 28

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName FROM tableName;

13

SELECT columnName FROM tableName;

14 of 28

SQLite: Basic SQL Statements

UPDATE: updates data in table

[ex] UPDATE tableName

SET ….

WHERE [condition];

14

UPDATE tableName

SET ...

WHERE [condition];

15 of 28

SQLite: Basic SQL Statements

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE [condition];

15

DELETE FROM tableName

WHERE [condition];

16 of 28

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, e.g. on attu)

[ex] ALTER TABLE oldName RENAME TO newName;

16

ALTER TABLE tableName

ADD COLUMN columnName columnDataType;

ALTER TABLE tableName DROP COLUMN columnName;

ALTER TABLE oldName RENAME TO newName;

17 of 28

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

17

Answer: Create another table!

18 of 28

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

19 of 28

SQLite: Special Operators

DATE operator: lets you work with dates and times; declare as varchar

[ex] SELECT * FROM tableName WHERE dateColumn ='YYYY-MM-DD';

SELECT * FROM tableName WHERE dateColumn < DATE('now', '-1

month');

Other operators: LENGTH(string), SUBSTR(string, start index, end index), etc.

19

SELECT * FROM tableName

WHERE dateColumn = ‘YYYY-MM-DD’;

SELECT * FROM tableName

WHERE dateColumn < DATE(‘now’, ‘-1 month’);

20 of 28

More SQL (For Reference)

WHERE clause - filter records
ORDER BY clause - sort records (default ascending order)
DISTINCT clause - keep unique values
AND, OR operator - filter records based on more than one condition
Relational operators: =, >, >=, <, <=
AS - give an alias name to a table or a column

20

21 of 28

SQLite Installation

21

.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 28

SQLite Installation (Linux/Mac)

Linux - Open a terminal, then run the command:

sudo apt-get install sqlite3

Mac - SQLite ships with macOS, so no additional setup is needed

22

23 of 28

SQLite Installation (Windows)

HIGHLY recommended to use attu instead, as SQLite is already installed there.

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

24 of 28

Attu Setup Tips:

You can use VSCode to connect to Attu:

Visual GUI!
Extensions!

Download the “Remote SSH” extension in VSCode and use it to connect to Attu.

Don’t want to type your password in everytime you go to connect, setup SSH-based connection: https://code.visualstudio.com/docs/remote/troubleshooting#_ssh-tips

24

25 of 28

Connect to attu through VSCode

25

26 of 28

Running SQLite

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

sqlite3

Windows -

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

26

27 of 28

Saving your Database

Running sqlite3 opens an in-memory database, which is erased after exiting.

Save into persistent db file using .save [database name].db

Alternatively, open SQLite using sqlite3 [database name].db

Reopen your db next time using sqlite3 [database name].db

27

28 of 28

SQL Demo!

28

Note: you can use the demo sql file for this

— OLD —

.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