By Charles McDowell
Comptia Certified Linux Networking Professional.
Cisco Certified Networking Technician.
Microsoft Technology Associate Database Administration
“Is it ”SELECT * WHERE a=b FROM c” or ”SELECT WHERE a=b FROM c ON *” ?”
Hello, my name is Charles McDowell and today I will be going over the fundamentals of Structured Query Language. SQL or Structured Query Language is based on the English language. In my opinion it is very easy to learn at first but difficult to master. Especially once you get to joins, aggregation, and subqueries. Here’s an example of what those look like:
SELECT users.firstname || ' ' || users.lastname
AS "Full Name"
FROM Accounts
JOIN Users U
ON Accounts.userid=Users.Userid
JOIN Products
ON Products.productid=Accounts.productid
WHERE Products.productid IN (SELECT productid
FROM Products
WHERE price>(SELECT avg(price)
FROM Products))
GROUP BY Users.firstname, Users.lastname;
This may look confusing as a new comer, or even an intermediate developer looking at SQL for the first time.
Through this blog post, I hope to give a fresh perspective on learning SQL.
I’m focusing on PostgreSQL syntax in this post, although SQL syntax is very similar across databases. So some of these would work on MySQL, or other SQL databases.
The Three Main Keywords
There are many keywords used in SQL, SELECT, FROM, and WHERE will be used most of the time. These key words are the most fundamental way of querying a database.
Show me the data
So, here’s a quick over view of what’s in the database we’ll be referencing:
We have a Health Care Software Business, with Products, Accounts and Users.
Our “products” table has information about the productid, productname, price, date of availability, and version available. The product id in this a case is a Primary Key because it’s unique value with the sole purpose of referencing a specific row of data on this table.
Our “Users” table only has the first and last name of all registered users.
The “Accounts” table has information on the Products bought by the users. The productid column refers to the id of the product in the “products” table that was sold, and the userid column corresponds to the member in the “users” table that borrowed the book. The productid, and userid in this case by the way is known as foreign keys. They are refencing the primary key of other tables, making a parent child relationship between the two tables and making the data relational. We also have the dates when the products were sold, and when they are expected to be updated to later versions.
Let’s Practice
For our first query : let’s get the names and ids of all products under 30$.
Our query would be:
SELECT productid AS "id", productname
FROM products
WHERE Price < 30;
Which would give us :
id ProductName
32 Calorie Calculator
13 Fitness Time Tracker
Simple enough. See if you can dissect the query to really understand what’s happening.
FROM where does the data come?
Obvious I know, but really matters when we start doing joins and subqueries. FROM points our query to its table, a collection of data organized in columns and rows. This table can exists currently, or a table which we generate through joins or subqueries.
WHERE should the data be restricted?
WHERE, acts to filter out the rows based on a condition that we set. In this case the only rows we want to consider are those where the value of the Price column is below 30$.
SELECT what you want.
Now that we have the rows we wanted from the table that we wanted, what data do we care about? In this case we wanted the productname and id of the product, so that’s what we SELECT. We can also rename the columns we want to show with an alias or AS.
Stay tuned for part 2 next week.