Short Notes on SQL Basics
SQL stands for Structured Query Language. This is designed to manage the data stored in relational databases. Relational database organizes the data in one or more tables with column and rows. The example is, data stored in excel sheets in the form of rows and columns. All data stored in relational database is a certain type of datatype such as integer, text (string), date and real (a decimal format).
Let’s see some of the basic commands of SQL and their usage.
Table of Contents
- CREATE
- INSERT
- SELECT
- SELECT DISTINCT
- COUNT
- WHERE
- WHERE with logical operators
- BETWEEN
- ORDER BY
- GROUP BY
- HAVING
CREATE
CREATE
statement is used to create a table. The syntax is given below.
CREATE TABLE
is a clause used to create a table. cricket_players
is a name of table. There are 3 parameters are used, those are. name: TEXT
, age: INTEGER
, runs_scored: INTEGER
.
INSERT
INSERT
statement is used to add the records in the table. The syntax is shown below.
INSERT INTO
is a clause used to insert the specific row and column into the table. Here Sachin
is name variable, 48
is age variable and 15,000
are run_scored variable.
SELECT
SELECT
statement is used to fetch data from database. The syntax is shown below.
SELECT
clause selects the name
column from cricket_players
table. You can put some more column names beside name
like shown below.
As you can see above SELECT
clause selects two columns from cricket_players table those are name and age.
SELECT DISTINCT
Sometimes, it happens that, a column contains multiple repeated values or say duplicate values. It may happen that, two cricket players may have scored the same runs. If you want to select distinct values in the table then you can use SELECT DISTINCT
clause. The syntax is shown below.
COUNT
If you want to count the number of players or the number of rows, then you can use, COUNT
statement as shown below.
SELECT COUNT
clause with asterisk mark in the bracket shows the number of records or number rows in the table.
Filtering the table
WHERE
WHERE
statement allows you to filter text and numeric values in a table. The following operators can be used for conditional filtering.
=
equal
<>
not equal
<
less than
>
greater than
<=
less than or equal to
>=
greater than or equal to
Now let’s see an example,
As shown in the above code snippet, it filters out the particular movie i.e. Sholey
from bollywood_films
table.
WHERE with logical operators
You can combine queries with logical operators such as AND
or OR
Let’s see an example,
In the above example, all the films directed by David D
and released after year 2010 were filtered out.
BETWEEN
BETWEEN
statement provides shorthand for filtering values within specific range. This can be used in the following way.
With the combination of WHERE
BETWEEN
and AND
we can filter out the films released between the year 2000 and 2010. Please note that, BETWEEN
is inclusive.
Sorting and grouping the table
ORDER BY
This is a keyword in SQL used to sort the table either by ascending order or descending order. By default it sorts in ascending order.
In the above code snippet, the title
column has been selected from bollywood_films
table and ordered it in descending order by release_year
.
GROUP BY
Sometimes you may need aggregate results, for example, you may want to count the number of films released each year by its count.
The above code snippet, the release_year column has been selected from the bollywood_films
table and grouped by release_year
and counts number of films in that year.
HAVING
The GROUP BY
clause cannot be coupled withWHERE
clause therefore to put the condition HAVING
clause is being used.
The code snippet only shows those years in which 5 films were
In the next article let’s see joining data in SQL