The SELECT command is the most commonly used SQL command by Data Scientists. It helps to retrieve/extract data from one or more tables in a database.
You learnt how to use the CREATE, INSERT and SELECT commands in my previous article
Read: Get started with SQL.
You created a song table, inserted some records and viewed the table using the SELECT command.
In this post, you'll learn more about the SELECT command. Before then, let's see how the ALTER, UPDATE and DELETE commands are used.
What are Conditions?
Conditions are expressions that evaluate to boolean values(TRUE or FALSE). We'll use them with the WHERE clause in this post.
WHERE Clause
The WHERE clause is used to filter records from a table. Only the rows that satisfy the WHERE condition would be included in the result set.
Let's say we want a record from the songs table but we don't know the title, we only know it belongs to the "Trap" genre. That's enough clue for this use case.
Open your DB browser and click on the 'Open Database' button at the top taskbar
Navigate to the directory where you saved your database from the last session('Documents' in my case). Click on the database file('mydatabase' in my case) and click open.
Now, click the 'Execute SQL' button and enter the code as shown below, highlight and run with 'Ctrl' + 'Enter'.
SELECT * FROM songs WHERE genre="Trap";
There it is! Toosie Slide is the song's title.
Let's analyze the code:
SELECT * FROM songs selects all the columns in the songs table
WHERE is a clause
genre = "Trap" is a condition
The statement filters records that satisfy the condition. You shall see more of the WHERE clause soon.
ALTER Command
This is used to modify an existing table. Let's use the ALTER command to add an 'album' column to the 'songs' table.
Enter the code as shown below, highlight and run with 'Ctrl' + 'Enter'.
ALTER TABLE songs ADD COLUMN album TEXT; SELECT * FROM songs;
ALTER TABLE songs points to the table, songs, we want to modify
ADD COLUMN album TEXT adds an album column which should hold TEXT values.
As you can see, a new column has been added to the table but filled with null values. SQLite does not allow us to specify a default value in the ALTER statement(another drawback of SQLite).
What is a NULL value?
A NULL value simply signifies the absence of a value i.e. It is a missing value.
UPDATE Command
This allows us to modify a row in a table. Let's use it to fill in the Null values in the songs table.
You'll be filling 'Unknown' for songs that don't have albums.
To fill all missing values in the album column, you'll run 5 UPDATE statements and a SELECT statement to view the updated table. I'll advice you to highlight and run a statement at a time. This would help you debug easily incase you run into an error.
UPDATE songs SET album = "Unknown" WHERE title = "Joro"; UPDATE songs SET album = "Me Against the World" WHERE title = "Dear Mama"; UPDATE songs SET album = "Vida" WHERE title = "Despacito"; UPDATE songs SET album = "Dark Lane Demo Tapes" WHERE title = "Toosie Slide"; UPDATE songs SET album = "Unknown" WHERE title = "Try me"; SELECT * FROM songs;
UPDATE songs selects the table to modify
SET album selects which column to modify
WHERE title filters which row to modify
We ran this code with different values for all five rows and the songs table was updated as shown above.
DELETE Command
This is used to remove rows from a table.
Let's remove the the rows that have Unknown albums and view the table.
DELETE FROM songs WHERE album = "Unknown"; SELECT * FROM songs;
DELETE FROM songs selects the table
WHERE album = "Unknown"; specifies the condition to be met for row deletion
Click on 'Write Changes' on the taskbar to commit these changes to your database.
Congratulations, you've just modified your SQL Database and Table. From now on, we'll be working with pre-existing databases.
Download this sample database here, unzip and move the database file to the directory where you stored your first database. I stored mine in documents, so I'll move it there. The database file is named 'chinook'.
Click on 'Open Database' from the taskbar and open the 'chinook' database. The schema table on the right pane should be updated with new tables(13 precisely).
Clear the queries in your editor. You should have a clean interface like this.
Should we run SELECT commands to view the tables?
No. This is a new database we know nothing about. DB browser provides a feature to help us inspect the nature of the tables in the database. It can already be seen on the right pane that this database contains 13 tables.
Click on 'Browse Data' on the taskbar.
Click on the Table drop-down list and select the 'invoices' table.
As seen, the 'invoices' table contains the details for tracks purchased by customers. But there are many columns. How do we view selected columns from the table?
SELECT Command on Specified Columns
The code below will help us view specified columns from the 'invoices' table.
Click on the 'Execute SQL' whenever you want to run queries.
For this part, let's view the InvoiceDate, BillingCountry and Total columns.
SELECT InvoiceDate, BillingCountry, Total FROM invoices;
When the '*' symbol is used after the SELECT command, it selects all columns from the table.
We can specify columns after the SELECT command. As seen above, we specified 3 columns and separated them with comma(,).
SELECT Command with the WHERE Clause
We can view selected rows after selecting few columns from the table by using the WHERE command. Only rows which satisfy the WHERE condition will be displayed.
Let's add another column, BillingCity, to the previous query.
We want to filter out orders that were billed from Berlin(Do you think Berlin was deleted too early from the Money Heist Series?).
SELECT InvoiceDate, BillingCity, BillingCountry, Total FROM invoices WHERE BillingCity="Berlin";
Let's do more. We want to filter out orders that were billed from either Berlin, Helsinki, Lisbon or Stockholm(Do you think Monica had the Stockholm syndrome?).
SELECT InvoiceDate, BillingCity, BillingCountry, Total FROM invoices WHERE BillingCity="Berlin" OR BillingCity="Helsinki" OR BillingCity="Lisbon" OR BillingCity="Stockholm";
The result includes all records from the aforementioned billing cities by using the OR operator.
The OR operator filters records that satisfy any of the given condition specified by the WHERE clause.
We also have the AND operator. This filters the records that satisfy all the given conditions in the WHERE clause.
Let's filter records that were billed in Brazil and have a Total greater than 1.
SELECT InvoiceDate, BillingCity, BillingCountry, Total FROM invoices WHERE BillingCountry="Brazil" AND Total>1;
There it is. We have only records that were billed from Brazil and have a Total greater than 1.
The SELECT command can be used to view various subsets of a table. In future articles, we'll learn how to use these results to answer questions.
You do not need to write changes to the database as we have not altered anything. We only viewed results.
Let's keep things organized against the next episode. Create a new folder in the directory where you stored your database file(Documents in my case). I'll name my folder 'MyDataCourse_SQL'.
Move 'mydatabase' and 'chinook' to this folder
Here's what my folder looks like.
Few Things to Note
- We enclose text values in quotes and not INTEGERS. 1 is different from "1". Ensure you use the column names just as they appear in the table. "Billingcity" is different from "BillingCity"
- Don't forget the semi-colon(;) at the end of your queries.
- Try to run one query at a time. This helps you to debug easily.
Summary
The ALTER command is used to modify an existing table.
The UPDATE command is used to modify rows in an existing table.
The DELETE command is used to remove rows from an existing table.
The WHERE clause is used to filter results from a table.
No comments: