What is SQL?
SQL is short for Structured Query Language. You can pronounce it as S - Q - L or as Sequel. It is a standard computer language used to communicate with relational database management systems.
General Uses
To query databases(i.e. to read or retrieve data stored in a database).
To write, insert, modify and update data in a database.
Data Scientist Uses
To retrieve data.
To create tables or test environment for analysis.
To combine multiple sources/tables together.
To write complex queries for analysis.
This guide is going to be divided into two parts or series. After these guides you'll be ready to write nice and clean queries in SQL. Let's continue.
SQL is more or less a translator. So, your SQL syntax will depend on what type of Database Management System you're using. In other words, each DBMS has it's own syntax. Examples of Relational DBMS are MySQL, PostgreSQL, Microsoft SQL Server, SQLite etc. So, for this guide and the next, I'll be using SQLite. To understand more about RDBMS and Relational Database Models, I'll be adding some links below that you can check out on your own. With all that said, let's dive right in!
In this guide, I'll be touching the following:
Retrieving Data
Creating Tables
Basic Filtering methods
Sorting
Math Operations, and
Aggregate Functions
Retrieving Data
No SQL query can exist without a SELECT clause. You're going to be using it in any and every query that you write. The SELECT clause is used to specify a column that is to be retrieved from a database. Along with the SELECT clause comes the FROM clause which is used to specify the table where your column is to be retrieved from. e.g.
SELECT column_name
FROM table_name;
You could also retrieve multiple columns from a table. To do this, just specify the columns by their name separating each column name by a comma.
SELECT column_name1, column_name2, column_name3
FROM table_name;
You can even retrieve all of the columns. You do this using the '*' wild card.
SELECT *
FROM table_name;
Also, where the number of results to be retrieved is large, you can specify the number you want to be retrieved using the LIMIT word.
SELECT *
FROM table_name
LIMIT 5;
LIMIT 5 implies that you want your SQL query to retrieve only the first five results from that table.
Creating Tables
To create tables in SQL, you use the CREATE TABLE clause. This statement is made up of the table name, the column names, the column definitions and the data types of the columns.
CREATE TABLE My_courses
(
course_name char(20) NOT NULL,
course_id char(5) PRIMARY KEY,
instructor char(20) NOT NULL,
fees decimal(3,2) NOT NULL,
DESC varchar(750) NULL);
From the above query, the name of the table has been specified with the CREATE TABLE statement. Also, the column names(course_name, course_id...), their definition and data types have been specified as well. Every column is either NULL or NOT NULL. Note that primary keys cannot be empty; they must have a value.
You can also add data to a table. What's the need of creating table if not to hold some sort of data or information? You can do this using the INSERT INTO clause.
INSERT INTO My_courses
(course_name, course_id, instructor, fees, DESC)
VALUES ('SQL', '405', 'Gladens', 80.00, NULL)
Basic Filtering Methods
Filtering in SQL is done majorly to retrieve a specific or a particular type of result from a database. The WHERE clause is used for filtering in SQL. Along with the WHERE clause comes some operators which are: =(Equal to), <>(Not equal), <(Less than), >(Greater than), >=(Greater or equal to), <=(Less than or equal to), BETWEEN, IS NULL.
For example, if you want to retrieve information about the course with course name 'SQL' from the table, you'll write the query as follows:
SELECT *
FROM My_courses
WHERE course_name = 'SQL';
You may want to retrieve all the information about the courses except a particular course for example the course 'SQL'. You'll write your query as follows:
SELECT *
FROM My_courses
WHERE course_name <> 'SQL';
You can also filter on a single value. If you want to retrieve information about all the courses where the fees is greater than or equal to 75.00, you'll write the query as follows:
SELECT *
FROM My_courses
WHERE fees >= 75.00
You can even filter within a range of values i.e.
SELECT *
FROM My_courses
WHERE fees BETWEEN 20.00 AND 80.00
The above query will retrieve all courses where the fees is between the range 20.00 and 80.00.
Sorting
When retrieving information from your database you may want your results to be returned in some form of order. You do this using the ORDER BY clause. This clause can take single or multiple columns. You only need to add a comma after each column name when using multiple columns. You can sort either by the column position or the actual name of the column. You can also sort by a column that is not retrieved in your query. Take note that the ORDER BY clause must be the last clause in your query. For example:
SELECT course_name, fees
FROM My_courses
ORDER BY 2
OR
SELECT course_name, fees
FROM My_courses
ORDER BY course_id
You can also sort by direction i.e. in ascending or descending order. All you have to do is specify the order by either ASC or DESC for ascending and descending orders respectively. You must take note that the ASC and DESC words applies only to the column name they directly follow. This is important to note especially when sorting or ordering by multiple columns.
Math Operations
You can apply math operations(+, -, *, /) to columns in your database tables. All you need to do is specify how they should be applied.
Aggregate Functions
These are SQL built-in functions to simplify analysis. They are: AVG() for averaging, COUNT() to count values in a table or column, MIN() which returns the minimum value, MAX() which returns the maximum value and SUM() to sum up values in a table or column. For example:
- Average value of fees
SELECT AVG(fees)
FROM My_courses
- Total number of courses
SELECT COUNT(*)
FROM My_courses
- Course with the lowest fee
SELECT MIN(fees)
FROM My_courses
- Course with the highest fee
SELECT MAX(fees)
FROM My_courses
- Total fee of all courses
SELECT SUM(fees)
FROM My_courses
To understand SQL, you need a basic knowledge of Relational Database Models and ER Diagrams.
You can check out the following links for it:
Apart from the above links, there are a ton of articles that you can find on the internet to explain Relational Database models and ER Diagrams.
In the next and final part of this series, I'll talk about Subqueries, the different types of Joins and touch some other areas that will propel you into using SQL with confidence.
Thanks for reading!