SQL: The Forgotten Younger Sibling of a Coding Bootcamp

In the job search process, it is very easy to focus on studying Object Oriented Languages for technical exams. However, writing to my audience candidly, I was rejected from an opportunity last week due to my lacking knowledge of the Structured Query Language (SQL). During the bootcamp, we glazed over SQL as we can automate this process by leveraging powerful frameworks, such as Rails; taking for granted what is going on “under the hood.”

*With such a statement, I would like to provide some color that the bootcamp did give students learning material regarding SQL and I am taking it upon myself to reexamine and build upon it. I am incredibly grateful to my bootcamp for teaching me how to build robust full stack web applications in 15 weeks.*

To demonstrate my desire to learn and grow as a junior developer, this week’s article will be an introduction to SQL that will develop in the following weeks as I continue my studies.

Source: https://codematters.online/wp-content/uploads/2018/11/SQLServer2019.jpg

SQL is a language designed for managing data in relational databases; in other words, its sole purpose is to “talk” to the database for data storage, access, and manipulation. Therefore it’s considered to be a “special purpose” or “domain specific” programming language. SQL is compatible with many different databases such as MySQL, Postgres, and SQLite; I will be using SQLite. While there are some minor differences, the fundamentals remain the same. SQL is invoked by writing declarative statements, often referred to as queries. There are many clauses that are used to write statements; some of the most common clauses include:

  • SELECT: extracts data from the database

Note: Clauses are uppercased and each query must end with a semicolon. In a table, there is a column that includes a unique identifier (or primary key) that is used to identify a specific record. In relational databases, columns also contain foreign keys in order to denote relationships with other tables (e.g., in the dogs table below, it could contain a foreign key relating each dog to a specific owner from an owners table).

Query Syntax

// * means all; to narrow the extraction, specify a column nameSELECT * FROM table_name;
SELECT column_name FROM table_name;
UPDATE table_name SET column_name = new_value WHERE column_name = existing_value;DELETE column_name FROM table_name;
DELETE FROM table_name WHERE column_name = existing_value;
INSERT INTO table_name(column_name_1, column_name_2, column_name_3, ...) VALUES (value_1, value_2, value_3, ...);CREATE TABLE table_name (
column_1_name data_type,
column_2_name data_type,
column_3_name data_type,
...
);
ALTER TABLE table_name ADD COLUMN column_name data_type;DROP TABLE table_name;CREATE INDEX index_name ON table_name(column_name); DROP INDEX index_name ON table_name;

Examples:

SELECT * FROM dogs;
SELECT name FROM dogs;
UPDATE dogs SET age = 3 WHERE id = 1;
UPDATE dogs SET name = "Sid the Kid" WHERE name = "Sidney";
DELETE age FROM dogs;
DELETE FROM dogs WHERE id = 1;
INSERT INTO dogs(id, name, age, owner_id) VALUES(1, "Sidney", 2, 5);CREATE TABLE dogs (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
owner_id INTEGER
);
ALTER TABLE dogs ADD COLUMN breed TEXT;DROP TABLE dogs;CREATE INDEX idx_first_name ON dogs(name);DROP INDEX idx_first_name ON dogs;

Note: You can format the output of your SELECT statements by running the following commands and then executing your SELECT statement:

  • .headers on: outputs the name of each column

For example, using the first command will transform the following output:

1|Sidney|3|1|German Shepherd//id   name   age   owner_id   breed
---- ---- ---- ---- ----
1 Sidney 3 1 German Shepherd

Query Constraints

Constraints can be added to specify the information that can be contained in a column; this is done when creating a new table. A constraint instructs the database to reject data that does not meet its condition. Some constraints include:

  • PRIMARY KEY: specifies the id column used to identify a row.

For example:

CREATE TABLE dogs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
age INTEGER NOT NULL,
breed TEXT DEFAULT "Mixed",
owner_id INTEGER
);

Basic Queries / Keywords

  1. ORDER BY: allows us to order the table rows returned by a SELECT statement.
SELECT column_name FROM table_name ORDER BY column_name ASC/DESC;SELECT * FROM dogs ORDER BY age ASC;

2. LIMIT: used to determine the number of records you want returned from a dataset in conjunction with ORDER BY to select extreme values from a database table.

// ORDER BY displays the dogs from oldest to youngest, while LIMIT restricts the number of records to 1 - outputting the oldest dogSELECT column_name FROM table_name ORDER BY column_name DEC LIMIT num;SELECT * FROM dogs ORDER BY age DEC LIMIT 1;

3. BETWEEN: allows us to select all records between certain parameters.

SELECT column_name FROM table_name WHERE column_name BETWEEN value_1 AND value_2;SELECT name FROM dogs WHERE age BETWEEN 1 AND 3;

4. IS NULL: used to add or search data with missing values.

// if an animal shelter needs to input a dog at intake, but the dog doesn't have a name yetINSERT INTO dogs (name, age, breed) VALUES (NULL, 4, "Belgian Shepherd");// can also SELECT dogs with NULL valuesSELECT * FROM dogs WHERE name IS NULL;

5. IS NOT NULL: used to search for data with a specified value.

SELECT * FROM dogs WHERE name IS NOT NULL;

6. COUNT: is an aggregate function that operates on groups of records. COUNT will count the number of records that meet a specific condition. Other aggregate functions, for example, will retrieve the minimum and maximum values of a column, sum the values of a column, and get the average of a column’s values.

SELECT COUNT column_name FROM table_name WHERE column_name = some_value;SELECT COUNT owner_id FROM dogs WHERE owner_id = 1;

7. GROUP BY: is another aggregate function that will group output by a specified column.

SELECT COUNT column_name FROM table_name WHERE column_name = some_value;SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name;SELECT breed, COUNT(breed) FROM dogs GROUP BY breed;// this example will return: breed   owner_id   COUNT(breed)
---- ---- ----
GSD 1 1

8. AS: allows columns or tables to be renamed using an alias.

// the following statement would rename the age column to years oldSELECT age AS years_old FROM dogs;

9. DISTINCT: selects only unique values.

SELECT DISTINCT name FROM dogs;

10. WHERE: selects data based on certain conditions (utilizing the following operators: =, <, >).

SELECT * FROM dogs WHERE name = "Sidney";SELECT * FROM dogs WHERE age > 12;

11. LIKE: compares similar values.

// in the following statement, the underscore and percentage act as a placeholder; returning records where the name of a dog has three letters and ends in "at" regardless of the first letter (e.g., rat, cat, bat, hat - strange dog names, great example). SELECT * FROM dogs WHERE name LIKE "_at";SELECT * FROM dogs WHERE name LIKE "%at";

12. AND: allows us to search for multiple criteria. A record must meet both.

SELECT * FROM dogs WHERE age BETWEEN 3 AND 5 AND breed = "German Shepherd";

13. OR: allows us to search for multiple criteria. A record must meet at least one.

SELECT * FROM dogs WHERE age BETWEEN 3 AND 5 OR breed = "German Shepherd";

Note: Now that we’re familiar with SELECT, there is an alternate syntax:

SELECT table_name.column_name FROM table_name;

SELECT
dogs.name FROM dogs;
// this syntax is useful if we wanted to get the names of animals from both the cats and dogs tablesSELECT dogs.name, cats.name FROM dogs, cats;

Graduate of Flatiron School’s Software Engineering Bootcamp as of 12/4/2020. Actively job searching in New York City.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store