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.
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
- UPDATE: updates data from the database
- DELETE: deletes data from the database
- INSERT INTO: inserts new data into the database
- CREATE TABLE: creates a new table within a database
- ALTER TABLE: modifies a table within a database
- DROP TABLE: deletes a table from a database
- CREATE INDEX: creates an index (or search key)
- DROP INDEX: deletes an index
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
- .mode column: shifts into column mode, enabling us to run the next two .width commands
- .width auto: adjusts and normalizes column width
- .width num_1: customizes column width (selecting any number you desire)
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.
- UNIQUE: ensures each value in a column is unique.
- NOT NULL: ensures a value is submitted for each column when a new record is created.
- DEFAULT: creates an assumed value if nothing is specified.
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
- 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;
