Get Started with SQL: A Beginner’s Guide to Querying Databases

SQL Query

SQL is used to interact with relational databases. Data is retrieved and filtered from databases using queries.

Through SQL, specified data can be requested and obtained from a database through a process known as Querying.

We’ll use the data behind The Economic Guide To Picking A College Major to demonstrate the concepts of SQL Querying.

SELECT

SELECT is the most fundamental query in SQL.

We retrieve data from a database using the SELECT query.

SELECT *
  FROM table_name;

For example:

SELECT * 
  FROM recent_grads;

In the above query:

1. SELECT * selects all the columns
2. FROM recent_grads identifies the table we want to select from.

NOTE: Whereas we can write the two lines of code in one line, SQL convention dictates that the query should be written in two separate lines with the second line indented.

SELECTING SPECIFIC COLUMNS

We can also retrieve data from specific columns using the SELECT statement.

SELECT column1, column2
  FROM table_name;

For example:

SELECT Total, Major 
  FROM recent_grads;

The above query returns the ‘Total’ and ‘Major’ columns from the ‘recent_grads’ table .

LIMIT

The LIMIT query restricts the number of rows that a SELECT column can return.

LIMIT is ideal when working with large databases as it makes queries run faster while also saving on-screen space.

SELECT column1 
  FROM table_name
LIMIT number_of_rows;

For example:

SELECT * 
  FROM recent_grads 
LIMIT 5;

The above query returns the first 5 rows from the ‘recent_grads’ table.

WHERE

The WHERE query filters a SELECT statement’s data based on given criteria.

The WHERE keyword is used to specify the criteria which the data must satisfy in order for it to be returned.

SELECT column1 
  FROM table_name 
  WHERE criteria;

For example:

SELECT Major
  FROM recent_grads 
  WHERE Total < 1000;

The above query will return the ‘Major’ column from the ‘recent_grads’ table where the total number is less than 1000.

The comparison operators that can be used alongside the WHERE clause include the following:

1. Less than (<)
2. Greater than (>)
3. Less than or equal to (≤)
4. Greater than or equal to (≥)
5. Equal to (=)
6. Not equal to (!=) or (<>)

MULTIPLE FILTER CRITERIA

We can combine multiple filter conditions using Logical Operators.

SELECT Major 
  FROM recent_grads 
  WHERE Major_category = "Engineering" AND ShareWomen < 0.5;

The above query will return the ‘Major’ column from the ‘recent_grads’ table whose Major is Engineering and with a less proportion of women than men.

Some of the most common used logical operators include AND, OR as well as NOT.

ORDER BY

The ORDER BY query sorts a SELECT statement’s data in ascending or descending order based on one or more columns.

SELECT column1
  FROM table_name
  ORDER BY column_name [ASC|DESC];

For example:

SELECT Major, Major_category, Unemployed
  FROM  recent_grads
  ORDER BY Unemployed DESC;

The above query will retrieve the ‘Major’, ‘Major_category’ and ‘Unemployed’ columns from the ‘recent_grads’ table and order them by the number of ‘Unemployed’ in descending order.