“If you plan to do evil, you will be lost; if you plan to do good, you will receive unfailing love and faithfulness.”
Main Source: (1) Practical SQL: A Beginner’s Guide To StoryTelling With Data by Anthony Debarros (2nd Edition). (2) The PostgreSQL Documentation.
However, this post HAS variations to the above-mentioned text.
PURPOSE
This post will focus on the SELECT keyword for PostgreSQL. The previous post focused on adding row’s of data to our administration table.
The SELECT keyword retrieves data from rows & columns in our database.
A SELECT statement can retrieve all of the data from tables in our database. The SELECT statement can also filter our data into certain criteria that we choose.
SELECT SYNTAX
This SELECT statement will retrieve data from every column & row in the table we fill in.
SELECT * FROM table_name_here;Let’s break this code down further:
SELECT * FROM table_name_here;(1) SELECT
SELECT is a reserved keyword in PostgreSQL. The SELECT keyword will retrieve rows from a table or view.
(2) *
The * -asterick will represent all the choices that a given value could possibly have.
In this particular statement, it will query our table to select all the column names.
The *asterick could be replaced with a column name, which would then only SELECT data from that column name.
That would look like this: SELECT column_name FROM table_name_here;
(3) FROM
The FROM “clause specifies one or more source tables” for the SELECT reserved keyword.
If multiple sources are specified, a Cross-Join will result from all the sources. However, it is common to add qualification conditions like a WHERE clause. The WHERE clause will restrict the cross-join into a smaller subset.
(4) table_name_here
This is where you would insert your own table name.
Later in this post, we will use the administration table name.
(5) ;
Always remember to finish your SQL statements with a ; – semi-colon.
“Do they not go astray who devise evil? But mercy and truth belong to those who devise good.”
Let’s try a SELECT statement with our Administration table.
Here is the code:
SELECT * FROM administration; Your output should look similar to this:
TWO ALTERNATIVES
Alternative #1: View “All Rows” w/ pgAdmin
Right-click on the administration table in the pgAdmin object tree.
Choose “View/Edit data — All Rows”.
Alternative #2: TABLE table_name_here;
Use this SQL statement:
SELECT with a SUBSET
In large datasets, you may want to limit the columns your SELECT statement fetches.
Here is how this can be done:
SELECT column_one, column_two, column_three, column_four FROM table_name_here;Notice that the column names are separated by a COMMA ( , ).
Let’s try this out:
SELECT id, first_name, last_name, school_name, date_of_hire FROM administration;Here is our output –
***Notice the missing columns: mid_int_name & salary_current.
!!!TIP!!!
The output will follow the order of the column names listed in your SQL statement.
For instance:
SELECT school_name, first_name, id, last_name, date_of_hire FROM administration;Will produce this outcome:
The next post, Post #7 will focus on: Clauses to use with SELECT.
“Haven’t you noticed how evil schemers always wander astray?
But kindness and truth come to those who make plans to be pure in all their ways.”
Leave a Reply