A database is an organized collection of data. Databases are a useful means of storing, managing, and retrieving data quickly. They consist of sets of records contained within one or more tables, with each table identified by the table name. This unit will teach you how to use simple SQL database commands to manage and retrieve data.
The most common command to interact with a database is the SELECT statement, utilized to query (i.e., request) data from the database. A statement is a complete piece of code that can run independently, and is comprised of clauses. The most basic SELECT statement has the format of:
SELECT *
FROM <tablename>;
This basic SELECT statement is comprised of two clauses that must occur in that specific order. The SELECT clause indicates the columns to list in the specific order that they will appear. In this case, the * is a unique wildcard that stands for ALL columns. Using this means that all the columns are selected to be displayed back to the user in the order that they appear in the table names that are listed. The second clause is the FROM clause that lists the table names that will be returned. For now, we will only focus on one table, but we will address multiple table queries later on in the course.
The data that is returned from a SELECT statement is stored in a result table called a result-set.
Keep in mind that SQL keywords are not case sensitive, meaning that select is the same as Select or SELECT. Using uppercase keywords is a best practice to visually separate out the keywords from the table or column names. However, it is not required.
Throughout this course, you will use a free and open-source relational database management system called Postgres to practice running queries. To access the Postgres database environment, open the following link in another browser window or tab: https://postgres.sophia.org. If you enter the Postgres database environment, you will see a list of database tables on the left side under the Schema Browser (album, artist, customer, employee, genre, invoice, etc.) Under each table name is a list of columns of data and their data types.
Using the Postgres database environment for this course, you can query the customer table by entering in:
SELECT *
FROM customer;
Then click on the run/play button at the top right.
This will execute the query and return the result set from the query. You should see that there are 59 rows of data. Each row is a unique record from the customer table. Each column name is listed, along with the data associated with it.
This is a useful way to be able to see all of the data in a single table at once.
[MUSIC PLAYING] Let's take a look at the SQL Query interface. On the left side, there is a list of tables. And under each table name is a list of columns and data types.
This is the schema for the database. The largest area is used to enter your own SQL queries. You can fill this with one of the examples by selecting it from the dropdown menu at the top.
Once you have entered a query, you can run it by clicking the Play button to the upper right. After running a query, you can view the results at the bottom of the screen.
You can reset the database at any time by reloading the web page. This will change everything back to its starting state. You should write down your SQL queries in another location before running them, either a text document on your computer or on a piece of paper. This way, you won't lose any progress you have already made if you need to reset.
[MUSIC PLAYING]
Source: Vincent Tran
[MUSIC PLAYING] A SELECT statement is the basic statement in order query data from a table. For example, we have the customer table that's located here. In order to query from the table, we would specify the SELECT statement. Then we specify the columns that's going to be selected. We can utilize the star as a wildcard to include all columns.
Then we include the FROM clause. And then we list the table names associated with what we're trying to query from. Here, we can go ahead and choose customer. Click on Run, and the result set should return all of the rows associated with the customer itself.
Now, we can also filter the data a little bit further, that we'll get into in future tutorials, utilizing the WHERE clause. For example, if we're trying to find the WHERE clause and identify the customers that have the customer_id equal to 2, this would filter the data set. So prior to running this, we'll see that there's 59 rows. However, once we run this, it'll filter the data, only return the ones that fit that criteria, and return a single row, because we only have one row that has a customer_id that's equal to 2.
[MUSIC PLAYING]
The third main clause of the SELECT statement is the WHERE clause. The WHERE clause is used to filter records, and only returns those rows/records when they fulfill a specific condition. There are many ways to filter data using the WHERE clause, which can be used not only in SELECT statements, but also in other statements like the UPDATE and DELETE statements. We will cover these other statements in later lessons.
Source: Authored by Vincent Tran
