Welcome to the Coding compiler. We bring your attention to the article with the screaming title of “Getting Started SQL in 20 Minutes”, Of course, you will not master all SQL in 20 minutes, but you will get a good start.
SQL Fundamentals
Every self-respecting web developer should know SQL. Although it already exists since the 70s of the last century, it is still very widely used, and without it it will be difficult to create something serious. Most full-stack frameworks can work with SQL. Among them: ActiveRecord , Doctrine , Hibernate and many others. Despite this, sometimes you have to “mess your hands” and go into real SQL.
That’s why we have prepared a short introduction in which we will go through the fundamental things in SQL. We strongly recommend that you try all the examples below on your own, because, as you know, a theory is nothing without practice. Well, let’s get started!
SQL Getting Started Guide
Let’s start learning SQL with code examples
SQL Create Table
To create a table in SQL, use the CREATE TABLE statement. It takes as parameters all the columns that we want to enter, as well as their data types.
Let’s create a label called “Months”, which will have three columns:
- id – in other words, the ordinal number of the month (integer type or int)
- name – the name of the month (string or varchar (10) (10 characters – maximum string length))
- days – the number of days in a particular month (integer type or int)
The code will look like this:
| 1 | CREATE TABLE months (id int, name varchar(10), days int); |
Also, when tables are created, it is customary to add a so-called primary key. This is a column whose values are unique. Most often, the primary key column is id, but in our case it may be the name, since the names of all months are unique. For more detailed information we suggest to follow this link .
SQL Data Insert
Now let’s add a couple of months to our label. This can be done using the INSERT command. There are two different ways to use INSERT:
The first method does not imply the indication of column names, but only accepts the values in the order in which they are listed in the table.
INSERT INTO months VALUES (1,'January',31);
The first method is shorter than the second, but if in the future we want to add additional columns, all previous queries will not work. To solve this problem, use the second method. Its essence is that before entering the data we specify the names of the columns.
INSERT INTO months (id,name,days) VALUES (2,'February',29);
In case we do not specify one of the columns, NULL or the default value will be written in its place, but this is another story.
SQL Select
This query is used if we need to show the data in the table. Probably the simplest example of using SELECT would be the following query:
SELECT * FROM characters
The result of this query will be a table with all the data in the characters table. The asterisk (*) sign means that we want to show all columns from the table without exceptions. Since the database is usually more than one table, we need to specify the name of the table, the data from which we want to see. We can do this using the FROM keyword.
When you need only some columns from the table, you can specify their names separated by commas instead of an asterisk.
SELECT name, weapon FROM characters
SQL WAlso sometimes we need to sort the displayed data. For this we use the ORDER BY “column name”. ORDER BY has two modifiers: ASC (ascending) (default) and DESC (descending).
SELECT name, weapon FROM "characters" ORDER BY name DESC
here
Now we know how to show only specific columns, but what if we want to include in the output only some specific lines? For this we use WHERE. This keyword allows us to filter data by a specific condition.
In the next query, we will display only those characters who use a pistol as a weapon.
SELECT * FROM charactersWHERE weapon = 'pistol';
SQL And / OR
Conditions in WHERE can be written using logical operators (AND / OR) and mathematical comparison operators (=, <,>, <=,> =, <>).
For example, we have a tablet in which data about 4 best-selling music albums of all times are recorded. Let’s derive only those whose genre is rock, and sales were less than 50 million copies.
SELECT * FROM albums WHERE genre = 'rock' AND sales_in_millions <= 50 ORDER BY released
SQL In / Between / Like
Conditions in the WHERE can be written using a few more commands, which are:
- IN – compares the value in the column with several possible values and returns true if the value matches at least one value.
- BETWEEN – Checks if a value is in a certain range
- LIKE – searches by pattern
For example, we can make a request to display data about albums in the pop or soul genre:
SELECT * FROM albums WHERE genre IN ('pop','soul');
If we want to display all the albums that were released between 1975 and 1985, we can use the following entry:
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
Also, if we want to display all the albums with the letter ‘R’ in the title, we can use the following entry:
SELECT * FROM albums WHERE album LIKE '%R%';
The% sign means any sequence of characters (0 characters are also counted as a sequence).
If we want to display all the albums, the first letter in the title of which is ‘R’, the recording will change slightly:
SELECT * FROM albums WHERE album LIKE 'R%';
SQL also has inversion. For an example, try writing the NOT yourself before any logical expression in the condition (NOT BETWEEN, and so on).
SQL Functions
SQL has full built-in functions for performing various operations. We will show you only the most frequently used ones:
- COUNT () – returns the number of rows
- SUM () – returns the sum of all fields with numeric values in them
- AVG () – returns the average value among the lines.
- MIN () / MAX () – returns the minimum / maximum value among the lines
To display the year of the oldest album, the following query can be used in the table:
SELECT MAX(released) FROM albums;
Please note that if you write a query in which you, for example, need to display the name and average value of something, then you will get an error on the output.
Suppose you write such a query:
SELECT name, avg(age) FROM students;
To avoid an error, you should add the following line:
GROUP BY name
The reason for this is that the avg (age) record is aggregated, and you need to group the values by name.
SQL Nested Select
In the previous steps, we learned how to do simple calculations with data. If we want to use the result of these calculations, then often we need to use the so-called nested queries. Suppose we need to bring the artist, the album and the year of the oldest album in the table.
You can output these columns using the following query:
SELECT artist, album, released FROM albums;
We also know how to get the earliest year available:
SELECT MIN(released) FROM album;
You can combine these queries in WHERE:
SELECT artist,album,released FROM albums WHERE released = ( SELECT MIN(released) FROM albums);
SQL Joining Tables
In complex databases, most often we have several related tables. For example, we have two tables: about video games and about developers.
There is a developer_id column in the video_games table, in this case it is the so-called foreign_key. To make it easier to understand, developer_id is the link between the two tables.
If we want to display all the information about the game, including information about its developer, we need to connect the second table. To do this, you can use INNER JOIN:
SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;
This is probably the easiest example of using JOIN. There are several more options for its use. For more detailed information we suggest to follow this link .
SQL Aliases
If you take a look at the previous example, you will notice that there are two columns that are named the same: “name”. Often this can be confusing. The solution to this problem are pseudonyms. They, by the way, help to make the name of the column more beautiful or more understandable if necessary.
To assign an alias to a column, you can use the AS keyword:
SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;
SQL Update
Often we need to change the data in the table. In SQL, this is done using UPDATE.
Using UPDATE includes:
- select the table in which the field we want to change is located
- write new value
- use WHERE to denote a specific place in the table
Suppose we have a table with the highest rated TV shows of all time. However, we have a problem: “Game of Thrones” is designated as a comedy and we definitely need to change this:
UPDATE tv_series SET genre = 'drama' WHERE name = 'Game of Thrones';
SQL Deleting Records From a Table
Deleting a record from a table via SQL is a very simple operation. All that is needed is to designate what we want to delete.
Note: Make sure you use WHERE when deleting a record from a table. Otherwise, you delete all the records from the table, unwittingly.
DELETE FROM tv_series WHERE id = 4;
SQL Deleting Tables
If we want to delete all data from the table, but leave the table itself, we should use the TRUNCATE command:
TRUNCATE TABLE table_name;
In case we want to delete the table itself, then we should use the DROP command:
DROP TABLE table_name;
Conclusion
Here in this blog we have completed the getting started SQL tutorial. Of course, this is not a comprehensive guide but it will help you to jump start the SQL, but you need to study a lot to master the SQL development. All the best for your future learning.
Related SQL Server DBA Tutorials And Interview Questions
SQL Server DBA Tutorial – Part 1
SQL Server DBA Tutorial – Part 2.
SQL Server DBA Tutorial – Part 3
SQL Server DBA Tutorial- Part 4
SQL Server Interview Questions