Intro to SQL

Objectives

• Introduce Databases
• Introduce SQL
• Create Databases
• Create, Alter and Delete Tables
• Introduce and Perform CRUD Actions on Our Database Tables
• Introduce and Implement Join Clauses

Databases

Why Use a Database?

• Thus far, whenever we terminate our applications, all our data disappears
• We need a way to save our information so it can be accessed later
• With a database, a computer can store our information and provide it to our application every time it starts up
• A database will have a specific query language you can use to perform basic and complex tasks on your data

Relational Databases

• We will be focusing on relational databases which use SQL
• Relational Databases are set up as tables where each row represents a single entity
• You can think of a table like a spreadsheet
• Relational Databases allow us to set up relationships between a row in one table with a row (or many rows) in another table (or many other tables)
• More on this later!

​

SQL

What is SQL?

• Structured Query Language is a Domain Specific Language used to manage data in a database
• By “domain specific language” we mean that sql is used for a specific purpose - communicating with our database
• We will be using SQLite which is a database management system that uses SQL
• The language we are using to manage/query data is SQL
• The database management system (program that interacts with our database) is sqlite

Using SQLite

• There are two ways that we can interact with our database
• From the SQLite command line terminal
• By executing sql files
• You can compare these options (sort of) to entering irb or running a ruby file
• Everytime we interact with our database, we use the sqlite3 command

​

Useful sqlite3 commands

• .tables
• .schema
• .headers on
• .mode column

Database vs. Tables

• Our database can contain many tables
• Our tables can contain many pieces of information (data)
• A table alone is not a database, but it is a part of a database
• We cannot have a table without a database

Creating a Database

• The first step is to actually create our database from the command line
• With sqlite, our data just lives in a file on our computer
• There are two ways to set up our database
• sqlite3 name_of_database.db
• touch name_of_database.db
• Note: the file extension actually does not matter but it is convention to use .db or .sqlite

Database Tables

Creating Tables

• To create a table, we use the CREATE TABLE command
• There are two ways that we can create a table
• From the sqlite3 terminal for our database
• By executing a file in our text editor
• Again, the extension of our files do not matter, but it is convention to use .sql

Altering Tables

• To create a table, we use the ALTER TABLE command
• There are two ways that we can alter a table
• From the sqlite3 terminal for our database
• By executing a file in our text editor
• Again, the extension of our files do not matter, but it is convention to use .sql

Deleting Tables

• To create a table, we use the DROP TABLE command
• There are two ways that we can drop a table
• From the sqlite3 terminal for our database
• By executing a file in our text editor
• Again, the extension of our files do not matter, but it is convention to use .sql

Dealing with Data

CRUD

• There are 4 actions we can take on our data:
• Create
• Read
• Update
• Delete
• We can create a new row in our tables by inserting data
• We can read the data we have in our tables
• We can update the data in our tables
• We can delete data from our tables

SQL CRUD

• Create
• INSERT INTO [tableName] (attr1, attr2) VALUES (value1, value2);
• INSERT INTO states (name, population) VALUES (“New York”, 19);
• Read
• SELECT [what we want to select] FROM [tableName] WHERE attr [conditional] "attr_value";
• SELECT * FROM states WHERE name = "New York";
• Update
• UPDATE [tableName] SET [columnName] = [new value] WHERE [columnName] = [value];
• UPDATE states SET population = 20 WHERE name = “New York”;
• Delete
• DELETE FROM [tableName] WHERE [columnName] = [value];
• DELETE FROM states WHERE name = “New York”;

​

Basic SQL Queries

• A sql query is a statement that retrieves data from our database
• When we make a query we are executing the READ part of CRUD
• Every sql query begins with SELECT and requires us to specify FROM which table we want the data
• SELECT [what we want to select] FROM [tableName] WHERE attr [conditional] "attr_value";

​

​

​

Query Modifiers

• We can use query modifiers to further specify what data we want
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• BETWEEN
• IS NULL/IS NOT NULL

​

Aggregate Functions

• Aggregate Functions are sql statements that perform calculations on data we query from the database
• AVG
• SUM
• COUNT
• MIN
• MAX
• These functions take in arguments of column names on which to perform the calculation
• SELECT SUM(population) FROM states

Joins

• A sql join clause is a way to combine rows from two or more tables, based on a common column between them
• An inner join will return all rows when there is at least one match in BOTH tables

SELECT column_name(s)

FROM first_table

INNER JOIN second_table

ON first_table.column_name = second_table.column_name;

• A left outer join will return all rows from the left table, and the matched rows from the right table

SELECT column_name(s)

FROM first_table

LEFT OUTER JOIN second_table

ON first_table.column_name = second_table.column_name;

Resources

• https://docs.microsoft.com/en-us/dotnet/architecture/cloud-native/relational-vs-nosql-data
• https://aws.amazon.com/relational-database/#:~:text=A%20relational%20database%20is%20a,be%20represented%20in%20the%20database.
• https://www.javatpoint.com/sql-vs-sqlite#:~:text=SQL%20is%20a%20standard%20which,have%20a%20separate%20server%20process.
• http://zetcode.com/db/sqlite/
• https://www.techopedia.com/definition/24361/database-management-systems-dbms#:~:text=A%20database%20management%20system%20(DBMS)%20is%20a%20software%20package%20designed,record%20structure%20and%20file%20structure.
• https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
• https://www.digitalocean.com/community/tutorials/understanding-sql-and-nosql-databases-and-different-database-models
• ​

Resources

• https://sqlite.org/cli.html
• https://learnsql.com/blog/sql-order-of-operations/#:~:text=Six%20Operations%20to%20Order%3A%20SELECT,developer%20to%20know%20this%20order.