Database administration and management is a highly valuable skill in the modern corporate world. It is one of the key concepts taught to computer science students as part of their curriculum.
Get Started
What is a Database
A database is a set of arranged or assembled information stored in an organized manner for users. While databases do exist in a manual form stored in traditional hard copies or registers, we usually talk about databases as computerized collection of data.
The purpose of the database is to carefully store, organize, configure and manage data. This accuracy and consistency of data stored in a database is called “database integrity.”
A DBMS (Database Management Software) is used to store, arrange and organize all such data. In addition to that, DBMS is also responsible for change management, migrations, parallel processing, backups, security, and much more.
There are 5 major different types of DBMSs: relational, NoSQL, object-oriented, hierarchical, and network databases. Most popular type of DBMS used in the world is relational databases, also known as RDBMS.
To be a RDBMS, a database must meet a set of 13 rules known as Codd’s Twelve Rules. These rules define what is required from a database management system in order for it to be considered relational.
RDBMS stores data in tables, within rows and columns. This arrangement allows tables to relate with each other into 1:1 and 1:many relationships. This table-based arrangement makes the data more structured, making it easily accessible for updates and management straightforward.
SQL (Structured Query Language) is used by relational databases. SQL allows CRUD (create, retrieve, update, delete) operations on database tables, as well as other advanced operations.
What is SQL
SQL stands for Structured Query Language. It is a domain-specific programming language that is used by DBMS for directing instructions to data. SQL is the interface used to define, manipulate, and query data in a database.
Querying data can become very complicated and therefore SQL expertise is needed to run more complex commands. SQL has a huge directory of functions and commands, but you can accomplish many tasks using a few common commands.
SQL has been around since the 1970s and continues to be as popular as ever. Over the years many different versions of SQL have been released, from different companies including Oracle, IBM, and Microsoft.
In this article we will discuss the implementation of some of the most highly used SQL commands and functions.
To illustrate these commands, we will be using a sample table of “Movies” for reference. This table contains information for a movie store that offers DVDs to its customers. The records contain some basic information about the movies available in the store including their average user ratings.
Database Table Name: Movies
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 1 | Step Up | Channing Tatum | 6.5 | Music | 12 |
| 2 | Deadpool | Ryan Reynolds | 8.0 | Action | 13 |
| 3 | Interstellar | Anne Hathaway | 8.6 | Sci-Fi | 11 |
| 4 | Edge of Tomorrow | Tom Cruise | 7.9 | Sci-FI | 11 |
| 5 | Titanic | Leonardo DiCaprio | 7.9 | Drama | 10 |
| 6 | The Dark Knight | Christian Bale | 9.0 | Action | 9 |
| 7 | The Conjuring | Patrick Wilson | 7.5 | Horror | 12 |
| 8 | The Proposal | Sandra Bullock | 6.7 | Comedy | 14 |
| 9 | The Equalizer | Denzel Washington | 7.2 | Thriller | 13 |
| 10 | The Shawshank Redemption | Morgan Freeman | 9.3 | Drama | 10 |
Let’s talk about the most commonly used SQL statement:
SELECT and SELECT DISTINCT
The SELECT statement in SQL is used to retrieve data from a table. Let’s take the example table ‘Movies’ which includes multiple columns. In order to view the entire table we would use the command:
SELECT * FROM Movies;
In SQL, * represents ALL. So this statement returns (selects) all the records in the table ‘Movies’.
Another way to use the SELECT statement is by using it to call specific columns from a table. Here’s an example:
SELECT mName, mGenre FROM Movies;
This statement returns you all the records showing just the two columns of movie name and genre (mName and mGenre).
The SELECT DISTINCT statement is used to call unique records in a column. For comparison, a SELECT statement would return all the records in a table in the same order, whereas SELECT DISTINCT only shows all the unique records avoiding any repetitions.
SELECT DISTINCT Genre
From Movies;
This statement would result in all the distinct Genres that your table holds.
Genre
Music
Action
Sci-Fi
Drama
Horror
Comedy
Thriller
Filter
Much like how you apply search filters when shopping online to get a specific product, you can do the same for SQL records. When you apply search filters only the records that return true for the specified conditions will be retrieved.
Where
After you are familiar with SELECT and FROM statements, the WHERE filter or clause is next in line. You use the WHERE command to place a condition for your query. For example:
SELECT * FROM Movies WHERE mGenre = Horror;
This statement would return all the records from the movies table which belong to the horror Genre. This is what the result of the query would look like:
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 7 | The Conjuring | Patrick Wilson | 7.5 | Horror | 12 |
Not, And, Or
The WHERE clause allows you to place a condition in your query but AND, OR, NOT can help you place multiple conditions altogether. These three operators give you three different ways of querying your SQL conditions.
- The AND operator displays results if all the conditions separated by AND are true.
- The OR operator displays results if any of the conditions separated by OR are true.
- NOT is a negation operator that returns a record if the condition is not true.
The following are examples for each of the operators:
AND
SELECT * FROM Movies WHERE mGenre = ‘Horror’ AND mRating > 7
The above statement will return records where both the conditions are true. So all the horror movies with a rating (mRating) of more than 7 will be shown.
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 7 | The Conjuring | Patrick Wilson | 7.5 | Horror | 12 |
OR
SELECT * FROM Movies WHERE mGenre = ‘Action’ AND ( mStarring = ‘Tom Cruise’ OR mStarring = ‘Christian Bale’);
Running this string of code will return all the comedy movies containing either Tom Cruise or Christian Bale starring in it.
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 6 | The Dark Knight | Christian Bale | 9.0 | Action | 9 |
NOT
SELECT * FROM Movies WHERE NOT mGenre = ‘Drama’;
This query will return all Movies in the table that do not belong to the Drama genre.
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 1 | Step Up | Channing Tatum | 6.5 | Music | 12 |
| 2 | Deadpool | Ryan Reynolds | 8.0 | Action | 13 |
| 3 | Interstellar | Anne Hathaway | 8.6 | Sci-Fi | 11 |
| 4 | Edge of Tomorrow | Tom Cruise | 7.9 | Sci-FI | 11 |
| 6 | The Dark Knight | Christian Bale | 9.0 | Action | 9 |
| 7 | The Conjuring | Patrick Wilson | 7.5 | Horror | 12 |
| 8 | The Proposal | Sandra Bullock | 6.7 | Comedy | 14 |
| 9 | The Equalizer | Denzel Washington | 7.2 | Thriller | 13 |
LIKE
LIKE is another operator used with your conditions following the WHERE clause. LIKE is often combined with Wildcards. We’ll talk more about wildcards below. An example of Wildcard is a ‘%’ sign which represents any number of characters starting from 0 to any.
SELECT * FROM Movies WHERE mName LIKE ‘I%’;
This query returns all movies with names starting with ‘I’ followed by any number of characters: Interstellar
Wildcards
Wildcards help you determine different ways to target a string of characters. Following are the Wildcards used in SQL and their functions:
| Wildcard | Function | Example |
|---|---|---|
| % | The % wildcard refers to any number of characters. | S% would call Step Up, San Andreas |
| _ | The _ (underscore) character represents a single character. | St_p will find Step, Stop |
| [ ] | The [ ] (square brackets) refers to any one character within the brackets. | St[eo]p shows Stop and Step but not Stap or Stup |
| ^ | The ^ wildcard is used to call for any character that is NOT in the square brackets. | St[^o]p finds Step but not Stop. |
| - | The - (hyphen) is a wildcard used to call any single character within a range of the alphabet. | St[c-e]p finds Step but does not find Stop. |
In
The IN operator works to help you specify a number of values within the WHERE clause. This operator works quite similarly to the OR operator.
SELECT * FROM Movies WHERE mGenre IN (Action, Thriller, Comedy);
This query returns all the movies that belong to the action, thriller, or comedy genre.
Between
The BETWEEN operator is also used within the WHERE clause. It is used to define a range of numbers, text or dates to specify a selection of data.
SELECT * FROM Movies WHERE mRating BETWEEN 7.5 AND 9;
This query returns a full list of movies that have a rating within the range of 7.5 to 9.
Apply Structure
Order By
As the name suggests, the ORDER BY apply structure is used to sort a set of records. By default, the ORDER BY instruction sorts the records in ascending order. In order to arrange them in descending order you may need to use the keyword ‘DESC.’
The following examples show how to use ORDER BY to arrange the data:
SELECT * FROM Movies Order By mRating;
This query arranges all your movie records in the ascending order for their rating. As a result, you will be shown the lowest rated movie on top.
SELECT * FROM Movies Order By IMDB Rating DESC;
The query above will end up showing you all the movie records in the descending order of the IMDB rating. Consequently, you will notice that the highest rated movie is shown on top.
Group By
The GROUP BY command is used to structure your table in a way that groups a series of rows for the same values. The following example shows how the GROUP BY command can be used in a query:
SELECT COUNT(mID), mGenre FROM Movies GROUP BY mGenre;
This query shows you the number of movies in each genre. For each genre, you will get a count for the movie IDs that belong to it. A resulting table may look like this:
| Count(mID) | mGenre |
|---|---|
| 3 | Action |
| 2 | Comedy |
Math in SQL
This section covers some math related functions in SQL.
Select Top
The SELECT TOP command is used to set a limit on the number of results that the query will show. This is usually used with large amounts of data to reduce the processing time and maintain the performance of the system. An example of this command is show below for your help:
SELECT TOP 4 * FROM Movies Order By mRating;
This is what the results of this query will look like:
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 1 | Step Up | Channing Tatum | 6.5 | Music | 12 |
| 8 | The Proposal | Sandra Bullock | 6.7 | Comedy | 14 |
| 9 | The Equalizer | Denzel Washington | 7.2 | Thriller | 13 |
| 7 | The Conjuring | Patrick Wilson | 7.5 | Horror | 12 |
This query will respond by showing you the first 4 records from the movies table in the ascending order of their rating.
Min and Max
Next up are the MIN and MAX functions in SQL. These functions are used to return the minimum and maximum values of a selected column. Both these functions return a single row as a result.
This is how you can use the two functions:
SELECT MIN(mRating) FROM Movies WHERE mGenre = ‘Thriller’;
This query returns the record that contains the lowest rated thriller movie in your database.
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 1 | Step Up | Channing Tatum | 6.5 | Music | 12 |
SELECT MAX (mRating) FROM Movies WHERE mGenre = ‘Comedy’;
This query returns the record containing the maximum value of rating for a comedy movie.
| mID | mName | mStarring | mRating | mGenre | mPrice |
|---|---|---|---|---|---|
| 10 | The Shawshank Redemption | Morgan Freeman | 9.3 | Drama | 10 |
Count, Average, Sum
The COUNT, AVERAGE, and SUM functions are self explanatory. These three functions share a single row as result. Here’s how they work:
- COUNT is a function that returns the number of values according to the selected criteria.
- The AVERAGE function helps you determine the mean value of any numeric column.
- The SUM function returns the total sum of values in a numeric column.
Count
SELECT COUNT (mID) From Movies;
This query returns the total number of records in the MovieID section.
Count (mID)
10
Average
SELECT AVG(mRating) From Movies WHERE Genre = ‘Sci-Fi’;
This query returns the average rating for all the Sci-Fi movies in your database.
Avg(mRating)
8.25
SUM
SELECT SUM(mPrice)
From Movies
Where mStarring = ‘Leonardo DiCaprio’;
This query returns the total price of all Leonardo DiCaprio Movies in your database.
Maintaining the Data
Data maintenance is a very important aspect of database management. SQL allows you to use commands that can readily help you perform CRUD operations like add, remove, or update data in your database.
Insert
The SQL INSERT INTO command is used to add new records in your database table. To make this query work, you need to specify the column names and the values to be inserted into each column of the table.
INSERT INTO Movies ( mName, mGenre, mStarring, mRating, mPrice) values (Deadpool, Action, Ryan Reynolds, 8.0, $12.99);
As stated, this will include the new record into the table ‘Movies.’
Update
Sometimes you need to make changes to the existing records in your database. This is where the UPDATE function comes into play.
UPDATE Movies SET mRating = 7.7, mPrice = $13.99 WHERE mName = ‘Deadpool’;
This query will update the record of Deadpool by updating its movie rating to 7.7 and price to $13.99.
Delete
The DELETE statement is used to delete a record from your table.
DELETE FROM Movies WHERE mName = ‘Deadpool’;
This command will delete the entire record containing the movie Deadpool from the table Movies.
Working With Multiple Tables
A database can contain more than one table. Sometimes, a database user needs to retrieve information from different tables to query data. Bringing this information together from different tables is called joins. There are different types of joins that a user can use to call the data from different tables. We will be covering examples for joining two tables, but once aware of the concept you can apply these configurations to join multiple tables.
Joins are comparatively more complex SQL queries. It requires careful consideration of the columns from the tables that you are joining. We will be using Venn diagrams to show how each of these joins work.
We will be combining the Movies table with the Orders table to demonstrate joins. The table of the new dataset is shown below:
Orders
| oID | customerName | mID | Price ($) |
|---|---|---|---|
| 123 | Jake | 2 | 13 |
| 124 | Samantha | 4 | 11 |
| 125 | Amy | 5 | 10 |
| 126 | Emma | 8 | 14 |
| 127 | Joe | 7 | 12 |
Inner
An INNER JOIN combines records from two tables for every matching record in a field that is common in both tables.
This is what an inner join looks like for two tables:
A query using INNER JOIN would look like this:
SELECT Orders.oID, Orders.customerName, Movies.mName
FROM Orders
INNER JOIN Movies ON Orders.mID = Movies.mID
This query applies inner join to the tables of Movies and Orders. The first three records in the result will look like this:
| oID | customerName | mName |
|---|---|---|
| 123 | Jake | Deadpool |
| 124 | Samantha | Edge of Tomorrow |
| 125 | Amy | Titanic |
Left and Right
Left and right joins are also referred to as Left Outer Join, or Right Outer Join. The Left Join returns all the columns from the first table in your query, and all the matching columns in the second table of the query. All the distinct columns of the second table are left out.
A Left Join for two tables looks like this:
The Right Outer Join is basically a reciprocation of the Left Outer Join. A Right outer join calls for all the columns of the second table in the query, and leaves out any distinct columns of the first table.
This is what Right Outer Join demonstrates:
The use of Right Outer Join in a query looks like this:
Full
A Full Join or Full Outer Join calls for all the records from both the tables included in a query. It does not leave out any columns or records.
This is what a Full Outer Join looks like:
This is how a Full Outer Join can be used in a query:
SELECT Movies.mName, Orders.customerName
From Movies
FULL OUTER JOIN Order On Movies.mID, Orders.oID;
Order By Orders.customerName;
The first three rows of this query’s results will look like this:
| MovieName | CustomerName |
|---|---|
| Titanic | Amy |
| The Proposal | Emma |
| Deadpool | Jake |
Self
Union
The UNION operator is used to combine the results of two select statements. To make this operator work, you need to ensure that the columns selected have the same data type, and both SELECT statements should contain the same number of columns.
SELECT mName From Movies
UNION
SELECT customerName From Movies
ORDER BY mName;
The result of this query would be:
MovieName
Amy
Deadpool
Emma
Interstellar
Jake
Summary
SQL is a detailed and complex programming language. It comes in handy when you’re performing actions on huge databases. The commands and functions covered in this article are some of the most frequently used, but this is not an exhaustive list.
With the application knowledge of these basic commands, you can work your way towards more complex queries. For example, queries that require you to join multiple commands and functions to get your desired results.
It takes a lot of practice with these commands to get a competitive advantage in this field. A good understanding of this language can make you eligible for jobs like database tester, administrator, analyst, developer, etc.
In order to practice and hone your skills, you can use sample databases from different online sources to practice your queries on.