1 of 24

Quiz Section 1 �~SQLite intro

2 of 24

Meet Your TAs

Kushagra Bhatia

* please call me Kush

​

  • MS student at UW ECE majoring in NLP and ML
  • Love watching & playing tennis
  • Was TA for CSE 344 - Intro to Data Management, CSE 180 - Intro to Data Science and EE 565 - Computer Networks
  • kb111198@cs.washington.edu
  • Office Hours : TBD will be posted on course website

Hongyi Ji

​

  • BS Junior major in CS, previously in math
  • Have two cats
  • hji14@cs.washington.edu
  • Office Hours : TBD will be posted on course website

​

3 of 24

Resources

  • Course Website:

https://sites.google.com/cs.washington.edu/cse414-22au/

​

  • Ed Discussion:

https://edstem.org/us/courses/29763/discussion/

​

  • Gradescope:

https://www.gradescope.com/

4 of 24

Review: Database and DBMS

  • What is a database?

​

  • What is a DBMS?

​

4

5 of 24

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

5

6 of 24

What is SQL (Structured Query Language) ?

  • SQL is a language designed for managing data held in a relational database management system (RDBMS)
  • Relational Database:  a type of database that uses a structure that allows us to identify and access data in relation to another piece of data in the database.
  • A piece of data in relational database is called “table”.
  • Example of Universities and Students table.
  • RDBMS: Program that allows efficient management of relational data
  • What can it do?
    • Data insert, delete, query, schema creation, etc.
  • https://www.codecademy.com/article/what-is-rdbms-sql

6

7 of 24

SQLite: What is it?

  • C library that implements a relational data management system (RDBMS)
  • sqlite3: a standalone program that can run programs and manage an SQLite database

​

7

8 of 24

SQLite Installation

8

9 of 24

SQLite Installation

Linux - Open a terminal, then run the command:

sudo apt-get install sqlite3

Mac -

  1. Download Homebrew: instructions @ https://brew.sh/
  2. Open a terminal, then run the command:

brew install sqlite3

9

10 of 24

SQLite Installation (con’t)

Windows -

  1. Go to https://www.sqlite.org/download.html and download the third option down under “Precompiled Binaries for Windows”
  2. Extract files into directory of your choice
  3. Add that directory to the environment variable “path”

​

Video walkthrough: https://www.youtube.com/watch?v=XA3w8tQnYCA&t=2s

​

10

11 of 24

Running SQLite

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

sqlite3 [database]

where “database” is the name of the database

Windows -

  1. In cmd, go to directory where you extracted sqlite3.exe files
  2. Run the command: sqlite3 [database]

11

12 of 24

SQLite: Basic SQL Statements

CREATE TABLE: creates a new table

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

​

​

12

13 of 24

SQLite: Basic SQL Statements

Primary Key constraint

A primary key is a field / set of fields in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields.

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

[ex] CREATE TABLE tableName (columnName int, ... , PRIMARY KEY (col1, col2,..));

​

​

​

13

14 of 24

SQLite: Basic SQL Statements

INSERT INTO: inserts new data into table

[ex] INSERT INTO tableName VALUES (value1, …); *Insert value for each column

[ex] INSERT INTO table (column1,column2 ,..) VALUES( value1,...); *Insert value for specific columns

[ex] INSERT INTO table (column1,column2 ,..) VALUES ( value1,...), (value1’,..), (value1’’,...);

*Insert multiple rows

​

14

15 of 24

SQLite: Basic SQL Statements

SELECT: gets existing data from table

[ex] SELECT columnName,.. FROM tableName;

[ex] SELECT * FROM tableName;

[ex] SELECT columnName,.. FROM tableName WHERE rowFilterRule;

​

15

16 of 24

Commands for making changes

UPDATE: updates data in table

[ex] UPDATE tableName

SET newValueForColumn(s)

WHERE conditionToSearchRow(s);

16

17 of 24

Commands for making changes

DELETE: deletes data in table

[ex] DELETE FROM tableName

WHERE conditionToSearchRow(s);

​

DROP: drops a table from the database

[ex] DROP TABLE tableName;

17

18 of 24

Commands for Altering a table

ALTER: can have different applications

[ex] ALTER TABLE existing_table RENAME TO new_table;

[ex] ALTER TABLE table_name ADD/DROP COLUMN column_name;

[ex] ALTER TABLE table_name RENAME COLUMN current_name TO new_name;

18

19 of 24

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

20 of 24

Multiple Tables

  • A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
  • The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column

It has to be one of the values contained in the parent table’s primary key.�

20

21 of 24

More SQL

  • 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 - gives an alias name to a table or a column
  • Relational operators: =, >, >=, <, <=

21

22 of 24

SQLite: Special Commands

.help - list other . Commands

.header on/off - show/hide column headers in query results

.databases- shows the database we are in along with the permissions

.tables- shows list of tables

.open- open a database

.mode [mode type] - change how to separate the columns in each row/tuple (for better formatting)

Mode type examples: csv, tabs, line, column

.show - lists all display options

​

22

23 of 24

Google Chrome SQLite demo

https://stackoverflow.com/questions/8936878/where-does-chrome-save-its-sqlite-database-to

​

Did you know Google Chrome stores some things in a SQLite database file?

Explore on your computer!

24 of 24

24

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!