Data is absolutely everywhere. When you visited this web page, you generated data. When you took a note of your to-dos for the day in your reminders, you generated data.
All of this data needs to go somewhere! That’s where databases come in. Databases store information in a structured way so that it is accessible by computer programs. Programmers use a tool called Structured Query Language (SQL) to access and manipulate databases.
In this guide, we’re going to provide a brief overview of SQL to help you level up your skills! Without further ado, let’s get started!
What is SQL?
SQL was invented in the early 1970s by the IBM researchers Donald Chamberlin and Raymond Boyce. The technology may be old but it's still a crucial part of interacting with databases.
SQL is used to retrieve data from databases, insert records into a database, update a database, and to control the permissions in a database.
SQL is used with Relational Database Management Systems, or RDBMS. These are databases which are broken up into tables. Each table consists of a list of columns and rows. Tables can be linked together by creating primary and foreign keys between the tables.
Data scientists, database administrators, data analysts, business analysts and back-end web developers are several of the many types of technologists who use databases in their work.
Inserting Data into a Database
Create a Table
The CREATE TABLE statement creates a new table in a database. You can use this command to specify what columns should appear in a database and the types of those columns.
Insert a Record
The INSERT INTO statement inserts a new row into a database. Depending on the structure of a table, this statement can be used to insert some or all the values into a record.
Selecting Data from a Database
The SELECT statement selects data from one or more tables. It can be used to retrieve all the rows in a table or only rows that meet a set of conditions. You can filter out what columns will be returned by the SELECT statement.
The WHERE statement allows you to select a record based on a condition.
For instance, you could select all of the names in an Employees table that start with the letter “B”. You could select all the people in an Employees table who have worked for a business for at least four years.
The ORDER BY clause sorts data by a particular column name. It can be used to sort data in either ascending or descending order.
You could sort a list of employees in ascending order based on the day they were hired. You could sort a list of coffee products by their prices in descending order.
SQL databases can contain a lot of records. The LIMIT clause allows you to limit how many rows are returned by a query. For instance, you could return only the first five responses from a particular query.
Query Values Using Between
The BETWEEN operator allows you to select records where a column value is within a particular range. For instance, you could use BETWEEN to retrieve a list of employees who work at a business and who earn between $20,000 and $30,000. You could use BETWEEN to retrieve a list of all the coffees at a cafe which cost between $2.00 and $2.50.
Updating a Database
Updating a database allows you to modify the information you have already stored, and you can do so by doing the following:
The UPDATE statement amends the contents of a table. This statement will change all the values in a database that meet a particular condition or set of conditions. You can use a WHERE statement to select which values should be updated.
The DELETE statement removes one or more rows from a table. By default, this command deletes all the records in a table. The DELETE statement is usually used with a WHERE statement so that only particular records are deleted.
We’ve only just begun talking about what features SQL has to offer. Given how widely used the technology is, it should not surprise you that there is more to learn. If you have already mastered the topics we’ve covered, here are a few more topics to research:
- Alter the structure of a table
- Add a column to a table
- How to use unions
- How to use aggregate functions
- How to write subqueries
You’re already on your way to becoming a master of SQL. The more time you invest in practicing SQL, the better you’ll get at working with databases!
About the author
James Gallagher is a writer at Career Karma where he focuses on coding tutorials and technical articles. (twitter)