SQL Examples: A Basic Guide to SQL

SQL stands for Structured Query Language. It is a programming language so if you want to get a grasp of SQL, it is probably a good idea to first master the basics of programming (consider taking programming fundamentals course for non-programmers). Essentially, SQL is designed for managing data held in a relational database management system; it is the de facto standard database query language.

SQL essentially contains two different components: a data definition language and a data manipulation language. The SQL language elements can be subdivided into four different language elements, including clauses, expression, predicates, queries, and statements. Queries are one of the most important aspects of SQL, allowing for the search and retrieval of data from a database based on specific criteria or characteristics.

Overall, SQL can do a variety of different things. The language can execute queries against a database, retrieve data from a database, insert, update, or delete records in a database, create new databases, new tables in a database, or new stored procedures in a database, as well as set permissions on tables, procedures, and views in databases. Basically, you can do anything you might need to do in relation to a database with SQL. It is important to keep in mind that there are several different dialects of SQL, though it is essentially the closest thing to a standard query language that exists. To learn the basics of SQL, consider taking this SQL database for beginners course.

SQL is used very widely in both industry and academia, very often for very large and complex databases. This is in part because SQL supports disturbed databases, or databases that are spread out over several different computer networks. SQL is a set-based, declarative query language, not an imperative language. With that being said, however, there are numerous extensions to SQL that add procedural programming language functionality, known as PSQLs. For example, Oracle is a widely used PSQL. To master Oracle you might want to check out this introduction to Oracle SQL.  And of course, there are a variety of other PSQL examples.

To master SQL you first need to get a grasp of how, exactly, SQL code works. In particular, you will want to have a solid handle on SQL queries, as these are perhaps the most important function that SQL performs. Let’s take a look at some specific SQL query examples in order to understand how they work.

SQL SELECT Statement

Essentially the SQL SELECT statement is used to make a query; it is what is used to choose the data that you want to retrieve from the database to your application, creating a table based on the commands given. This allows you to sort and withdraw data based on a variety of different characteristics. Once you are getting started learning the SQL SELECT statement it is probably best to keep things pretty simple. However, it is important to keep in mind that the SELECT statement has many different options and extensions that provide a great deal of flexibility. Basically, an SQL SELECT statement can be broken down into several distinct element, each beginning with a keyword. These elements include: SELECT, FROM, WHERE, and ORDER BY.

SELECT . . . FROM

The most basic SELECT statement has only two parts. This includes what column you want to select from as well as what table that column comes from. You will want to write the column name immediately following the SELECT statement and if you are retrieving from more than one column you will need to separate each column name with a comma. A basic statement will look like this:

SELECT column name I, column name II, column name III

FROM table name;

WHERE

The WHERE command essentially helps you to filter or to limit data. So in order for a piece of data to be fetched it must meet a certain condition.  For example, let’s say you want to look at the data of all of the expats living in Lima, Peru. You’ve compiled a table called expat with all of the expat’s essential information—columns for their names, their ages, their occupations, their home country, etc. Let’s say that for this specific part of your project you are only interested in examining the ages and occupations of the expats whose home country is Germany. A basic select command would look something like this:

SELECT Age, Occupation

FROM Expat

WHERE HomeCountry = “Germany”;

Or let’s say you want this data for all of the expats in Lima, Peru except those whose home country is Germany. A basic select command would like this:

SELECT Age, Occupation

FROM Expat

WHERE HomeCountry <> “Germany”;

Of course, this is just a basic example. There are many more possibilities than this. If you are working with numerical data, for example, you can also give data ranges when using the WHERE command and you can also specify multiple conditions. The possibilities are numerous.

ORDER BY

Once you have filtered data to meet your needs, you are going to need to order it appropriately or control in which order the rows of your table appear, as well as the organization of the columns. Keep in mind that by default the sort order for a column is ascending (from lowest value to highest value).

To make this more concrete, let’s return to the expat example. Let’s say you want to look at the age and occupation of all the German expats in Lima, Peru and you want to sort the results by the expats age, in a descending order. The query would look something like this:

SELECT Age, Occupation

FROM Expat

WHERE HomeCountry = “Germany”

ORDER BY Age DESC;

In conclusion, the possibilities with SQL are pretty much endless. The trick is to master SQL so you can make the most out of database organization and retrieval. To hone your skills, it is definitely worth it to check out this practical SQL skills guide for beginner to intermediate skill levels.