SQL, or Structured Query Language, is used for managing and manipulating databases. Created by Raymond Boyce and Donald Chamberlin in the 1970s, it remains a popular tool to manage databases for both small and large datasets.
This guide walks through the basics of SQL and provides various use cases. Specifically, this guide covers how to:
- Select Data
- Manipulate Data
- Join Tables
- Use Aggregate Functions
- Alter Tables
Selecting Data
SQL query language is written in all caps. The rest of a SQL request consists of variables including the table name, columns, and data.
SELECT * FROM my_table;
The previous example is a simple SQL query. This query selects all data from my_table. The asterisk (*) means all.
SELECT last_name FROM my_table WHERE last_name=’A’;
This query will select only data from the last_name column where the last_name starts with the letter A. The equals (=) is case sensitive.
SELECT last_name FROM my_table WHERE last_name LIKE ‘a%’;
This example is similar to the previous one, but the LIKE condition is case insensitive. Note that LIKE and % are used for string queries.
How to use LIKE and %
Insert the modulo (%) at the beginning of the sequence to select all data that ends with the character(s)
LIKE %nd
Insert the modulo at the end of the sequence to select all data that starts with the sequence
LIKE ba%
Insert the modulo between the sequence to find all data with that sequence of letters
LIKE %an%
Inserting Data
Sometimes you need to not only find data on a table, but add to the table as well.
Suppose we work for a small company and manage their database. So far, the company records look like this:
Table name: some_company
| id | first_name | last_name | DOB |
|---|---|---|---|
| 1 | Tom | Frankson | 1979-05-05 |
| 2 | Gabby | Perth | 1985-11-10 |
We are asked to add a new employee, Allison Richards, to the database.
The INSERT INTO command inserts data into some_company. We need to indicate which columns the data will be inserted into by putting parentheses around these columns. The VALUES keyword is preceded by the data we wish to insert into the table.
INSERT INTO some_company(first_name, last_name, DOB) VALUES(‘Allison’, ‘Richards’, 1990-01-01);
After executing the former command, the table now looks like this:
| id | first_name | last_name | DOB |
|---|---|---|---|
| 1 | Tom | Frankson | 1979-05-05 |
| 2 | Gabby | Perth | 1985-11-10 |
| 3 | Allison | Richards | 1990-01-01 |
Order matters!
Order matters between the column names and values. For instance, consider this example:
INSERT INTO some_company(first_name, last_name, DOB) VALUES(‘Richards’, ‘Allison’, 1990-01-01);
The resulting table will look like this:
| id | first_name | last_name | DOB |
|---|---|---|---|
| 1 | Tom | Frankson | 1979-05-05 |
| 2 | Gabby | Perth | 1985-11-10 |
| 3 | Richards | Allison | 1990-01-01 |
In this example, this person’s name was incorrectly entered into the database as Richards Allison. Oops!
Updating Data
To change data in a table, use the Update command followed by SET.
UPDATE some_company SET first_name = ‘Allison’, last_name = ‘Richards’ WHERE first_name = ‘Richards’, last_name = ‘Allison’;
The table is back to normal:
| id | first_name | last_name | DOB |
|---|---|---|---|
| 1 | Tom | Frankson | 1979-05-05 |
| 2 | Gabby | Perth | 1985-11-10 |
| 3 | Allison | Richards | 1990-01-01 |
In this example, the SET keyword is followed by the column names we wish to change. Each column name is set to the new data. The WHERE constraint indicates which data we wish to change. We did not need to update her birthdate, as that information was already accurate.
Deleting Data
Sometimes, it’s necessary to delete data. In this case, Allison has left Some Company and our records should update accordingly. To delete data from a table, specify which data should be deleted using the WHERE constraint.
DELETE FROM some_company WHERE first_name=’Allison’, last_name=’Richards’;
The table will reflect this change:
| id | first_name | last_name | DOB |
|---|---|---|---|
| 1 | Tom | Frankson | 1979-05-05 |
| 2 | Gabby | Perth | 1985-11-10 |
The DELETE FROM command deleted all of Allison’s records from the database.
Take care when using this command.
| id | first_name | last_name | DOB |
|---|---|---|---|
Executing
DELETE FROM my_company;
will clear out the entire table besides the headings!
Joining Tables
Perhaps you have two tables you’re working with. You want to join the data between the two in order to analyze the results.
There are different types of JOIN clauses available.
INNER JOIN
Sometimes referred to as simply JOIN, INNER JOIN combines rows when there is a match in both tables.
LEFT JOIN
Combines all rows from the left table with matching rows from the right table.
RIGHT JOIN
Combines all rows from the right table with matching rows from the left table.
You have switched careers from Some Company to Some School. The students in your small class have completed two math quizzes so far.
Table: unit_1_quiz
| id | first_name | last_name | Class | Quiz | Score |
|---|---|---|---|---|---|
| 1 | Bryce | Aquino | Math | Unit 1 | 9.5 |
| 2 | Garret | Batista | Math | Unit 1 | 6.0 |
| 3 | Madison | Chan | Math | Unit 1 | 8.7 |
Table: unit_2_quiz
| id | first_name | last_name | Class | Quiz | Score |
|---|---|---|---|---|---|
| 1 | Bryce | Aquino | Math | Unit 2 | 9.7 |
| 2 | Garret | Batista | Math | Unit 2 | 5.8 |
| 3 | Madison | Chan | Math | Unit 2 | 9.3 |
You want to combine the results of these two tables to record your students' averages.
SELECT first_name, last_name, score FROM unit_1_quiz JOIN unit_2_quiz ON unit_1_quiz.id = unit_2_quiz.id
Start off by selecting the columns you'd like to see in the table. Then, using the JOIN keyword, select the other table you'd like to join. Next, specify how the tables should be joined. Tables should be joined through mutual data. In the example above, we've joined the data using the Id of the students.
Aggregate Functions
We still need to find the average, however. There is an easy way to do this that doesn't involve combining tables.
Select id, average_score = AVG(score) FROM (
SELECT id, score FROM unit_1_quiz
UNION ALL
SELECT id, score FROM unit_2_quiz
) AS subquery
GROUP BY id
First, we select id and create a new column called average score. Average score is set to the aggregated average of the score columns on both of our tables.
The UNION ALL keyword combines two or more SELECT commands. Using UNION ALL, we SELECT the id and score columns of our unit_1_quiz table and our unit_2_quiz table. This results in a subquery (a query within our main query).
We then group the results by the student's ids.
| id | average_score |
|---|---|
| 1 | 9.6 |
| 2 | 5.9 |
| 3 | 9 |
The resulting table returns the averaged scores.
Altering Tables
SQL allows us to create tables, alter existing ones, and even destroy tables.
Create Tables
In order to create new tables, we need to specify the data types for each column. In this scenario, our class is set to go on a field trip. We need to create a table listing the student's id, their first and last name, the class they're in, the amount they paid, and if they turned in their permission form.
Let's break down this information into data types!
- First name: string
- Last name: string
- Class: string
- Amount paid: float (a few dollars and some change)
- Permission form signed: boolean
Use the CREATE TABLE command to create the table with its corresponding data types. Keep in mind that every table should be instantiated with a primary key value such as an I.D. number.
CREATE TABLE field_trip (
id INTEGER PRIMARY KEY,
last_name TEXT,
first_name TEXT,
class TEXT,
amount_paid FLOAT,
permission BOOLEAN
);
Alter Table
We can alter the table's name and columns using the ALTER keyword.
Change table name
We want to be more detailed about the information in our field_trip table. We're going to change the name to iceskating_field_trip.
ALTER TABLE field_trip
RENAME TO iceskating_field_trip;
This command will rename the table to iceskating_field_trip.
Adding columns and deleting columns
Turns out our iceskating_field_trip table should include shoe size information as well. We also do not need the class they're in as they are the only class going on this field trip.
To add a column, use ALTER TABLE and ADD followed by the column's name and datatype.
ALTER TABLE iceskating_field_trip
ADD shoe_size FLOAT
We can delete the superfluous column with ALTER TABLE and DROP followed by the column's name.
ALTER TABLE iceskating_field_trip
DROP class
Keep in mind if you're using a database like SQLite this feature may not be supported.
Dropping Tables
We've reached the end of the school year and we need to clean out our database for next school year. You can run the command DROP TABLE to completely destroy an existing table (schema included).
DROP TABLE iceskating_field_trip
This will delete all records. Be sure this is something you really want to do.
Summary
We've covered the basic essence of SQL from selecting and manipulating data, to joining tables and using aggregate functions to creating and altering tables. There is more to SQL than what this guide covers.
If you want more practice with SQL, I recommend these sources:
- SQLBolt: Interactive SQL lessons and exercises.
- NTCHosting: A brief history of SQL
Happy SQL-ing!
Top comments (0)