Databases were a bit of a mystery when I came across them. I had to learn the syntax, and they were intimidating at first. But they're pretty simple! SQL is a vital tool for anyone interested in data because it helps to create and access data stored in databases. In this article, we'll explain what a database is and how it works, then walk through how to use SQL (the standard query language for databases) to make your database queries.
What is a database?
When you're looking to get started with SQL, you've got a lot of choices. The first thing you should know is that there are 2 types of databases:
Relational databases
Non-relational databases
Relational databases are composed of tables—like your bank account or your grocery list—that contain rows and columns. Each row contains information about one thing (say, a customer), and each column holds information about how many times that particular piece of data appears in the table. For example, a table might contain an actor's name and identification number. The columns would be actor_id, first_name, and last_name.
Non-relational databases are made up of files (like excel files) instead of tables. They don't have rows and columns, but they do have metadata tags that describe what each piece of data is supposed to represent.
What is SQL?
SQL stands for Structured Query Language. It's a language you can use to access your database by giving it instructions about what information you want to retrieve or change. You can use SQL queries to perform complex searches on large amounts of data quickly and easily! It is a programming language that queries and updates data in relational databases. It's based on the Structured English Query Language (also known as SQL) developed by IBM in 1979.
You can use SQL to create tables in your database and add information to them. You can also use SQL to perform CRUD operations—create, read, update, and delete—on your database tables.
The SQL language has 3 main clauses:
SELECT
INSERT
UPDATE
The SELECT clause retrieves the data from a database or table. The INSERT clause inserts new rows of data into an existing table or a new table. The UPDATE clause updates existing rows in a current table or updates specific attributes of the row in a new table.
How to use SQL
To start querying on SQL, you must create a workspace to write your queries by clicking on New Query.
1. Create a database. You can do this by opening the following SQL prompt in your
favorite text editor and typing:
CREATE DATABASE [database name];
This is an example of database creation in the SQL language. Dummydb is the name of the database that we are creating.
USE “database name”;
This command would open the database so that you can create your table.
2. Create a table in the database. This can be done using the following command:
CREATE TABLE “table name”
(
ID INT IDENTITY PRIMARY KEY,
First_Name varchar(20),
Last_Name varchar(30),
salary INT
);
This command would create a table called Employees and its columns. Here is a breakdown of the code:
ID INT IDENTITY PRIMARY KEY means the ID column is an integer (number) and a primary key.
First_Name varchar(20) means the First_Name column is a variable character with only 20 letters.
The same goes for the Last_Name column but it can contain up to 30 letters.
The Salary column is also an integer.
3. View the table. After creating your table, you can view the table you created by writing the following query:
SELECT *
FROM dbo.Employees;
This is a preview of your table.
Now you're ready to begin using your brand-new database! The next thing is to begin creating records in your database. The next blog post in the SQL series is about the INSERT INTO and UPDATE clauses which would help modify and maintain your databases efficiently.