1 of 25

Hello World! � ~SQLite intro

CSE 414, Autumn '24

Section 1 - CB/DB

9/26/24

2 of 25

Your TA

Anna Kuznetsova - avk5@uw.edu

Computer Science, Entrepreneurship
6th quarter TA
Outside of school I enjoy spending time with my cats, playing Hogwarts Legacy, cooking, and reading

OHs:

Monday 1:30-3:00pm Allen 3nd Floor Breakout

3 of 25

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
Something fun you did over summer break
Favorite form of potato

We’ll go around and see what style of potato is most popular!

4 of 25

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?

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!

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

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!