Essential SQL Commands for Data Science
Tags: database, sql, datascience, python
Published: 2023-03-13 19:24:51

Structured Query Language (SQL) is a programming language designed for managing and manipulating relational databases.
A database on the other hand is a collection of data that is organized in a manner that facilitates ease of access, as well as efficient management and updating.
A database is made up of tables that store relevant information.
The language is used by data analysts and data scientists to extract insights from large datasets.
SQL is a powerful tool that can be used to perform a wide variety of data manipulation tasks including : filtering, sorting, grouping and aggregating data.
A table stores and displays data in a structured format consisting of columns and rows that are similar to those seen in Excel spreadsheets.

SQL can:
- Insert, update or delete records in a database.
- Create new databases, tables, triggers and views.
- Retrieve data from a database.


## Basic SQL Commands
I shall be demonstrating this commands using mysql terminal.

## 1. SHOW 
The show statement displays information contained in the database and its tables

    SHOW DATABASES;


![showDatabase](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ckgunceondh6cwrdnsgu.png)
This command (SHOW DATABASES) lists the databases managed by the server.

    SHOW TABLES;

![showTables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jw6ixkaja7g56to77pj0.png)

    SHOW COLUMNS FROM table_names;

![showColumn](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kea21guemf3642acely3.png)
This commands shows the columns in the table_names table.
It displays the :
- Field : The column name
- Type : The data type of the values stored in the column
- Null : If the column is null 
- Key : It the column is the `Primary Key`
- Default : The default value if null
- Extra : may contain additional information that is available about a given column

## USE command
It is used (no pun intended) to specify which database to be ***used*** if there are multiple of them.

    USE demo;

![useDatabase](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fj4zgg3pv319n14zi7mx.png)
There are six databases managed by my server. By the the help of ***USE*** we specify that we want to use the demo database.

## 2. SELECT Statement
It is used to retrieve data from one or more tables in a database.
The select statement can be used to filter, sort and group data using different functions which we'll cover as we progress.
Here's the syntax of SQL SELECT statement:

    SELECT column_list
    FROM table_name;

- column_list : includes one or more columns which data is retrieved
- table-name : it's the name of the table from which the information is retrieved

A ***query*** may retrieve information from selected columns or from all columns in the table.
To create a simple ***SELECT*** statement, specify the name(s) of the column(s) you need from the table.

    SELECT adm_no FROM table_names;

![selectCol](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sxjomstur5nhwafijo7m.png)
From the above statement we SELECT the values in the adm_no column in the table_names table. This means we have specified from which column we want it's values selected. We have just selected from just one column.

    SELECT adm_no, Maths FROM table_name;


![selCols](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fpcpohq0xwxukn7ye8c8.png)
We can specify more columns to be queried. In the above statement we have queried from two columns (adm_no and Maths).
To ***SELECT*** from specific multiple columns you use a comma (,) to add the name of the column you want queried.

    SELECT * FROM table_name;

![selAll](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqlgt2jjjm5z3fwaeaab.png)
We use an asterisk (*) if we want to query/fetch from all the columns in a table.

***Multiple Queries***
SQL allows to run multiple queries or commands at the same time.

    SELECT * FROM pets;
    SELECT * FROM table_name;

The above statements retrieves all the columns and the rows in the pets and table_name tables;

 ***The DISTINCT Keyword***
In situations where you have multiple duplicate records in a table you may want to retrieve only unique records, instead of fetching the Duplicates.
***Syntax***

    SELECT DISTINCT col_name1, col_name2
    FROM table_name;

Example

    SELECT DISTINCT * FROM pets;

![selectDistinct](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/igzqgjzu2jttqexoh21z.png)
We fetch all the columns and rows that are distinct from the pets table;

## 3. CREATE Database
The <em>*** CREATE DATABASE***</em> statement is used to create a new SQL database;

    CREATE DATABASE employees;

![create_db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zwl3zyb3jvc9cemzrho4.png)
From the above commands we've created a database called employees;

we can now <em> ***USE***</em> the database and now create it's tables;

## 4. CREATE TABLE
The <em>***CREATE TABLE***</em> statement is used to create a new database.

    CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    );

That is the syntax of creating a table.
Let's create a new table

    CREATE TABLE employees (
    emp_id VARCHAR(50),
    firstName VARCHAR(50),
    lastName VARCHAR(50),
    department VARCHAR(50));

![createTable](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/048q94cwkp5pimn8dre4.png)
We've created a table called ***employees***
It has the following columns:

- emp_id : of type varchar
- firstName : of type varchar
- lastName : of type varchar
- department : of type varchar

Now that we have created a new table we want to insert values to the table:

## INSERT INTO
The <em> INSERT INTO </em> is used to insert new records in a table.

***syntax***

    INSERT INTO table_name
    VALUES (value1, value2, ...)

Here's an example inserting values to the ***employees*** table.

    INSERT INTO employees
    VALUES ('IT_210', 'John', 'Doe', 'IT');

![insertInto](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v56qzf3l2bb23squsmyb.png)
We have insert values to the employees table.
Note we have supplied values for every column in the employees table.

What if we do not want to supply a value for every column/field of the table?
We then have to supply a list of the fields we want to supply values for

    INSERT INTO employees (emp_id, firstName, lastName)
    VALUES ('mn_210', 'Lucky', 'Lard');

![insertInto_list](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oscg0yttmvna34malats.png)
Here we've supplied a list of the list of fields we want to supply values for: ***emp_id, firstName,lastName***

What if we want to update the values already contained in the database? 
Maybe we passed the wrong department or name for an employee. Well use the update statement

## 5. UPDATE statement
The <em>UPDATE</em> statement is used to modify the existing records in a table.
***syntax***

    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;

Let's update the lastName of the customer with id mn_210

    UPDATE employees
    SET lastName = "Angie"
    WHERE emp_id = 'mn_210';

![updateValues](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pnvtwb4xq6yrzh1aeg7c.png)
We've updated the name from Lard to Angie.
You can also update many records by use of a comma as shown in the syntax definition

## 6. WHERE statement
It is used to filter data based on a specified condition.
We provide the conditions that have to be met before returning the data using the ***WHERE*** clause.
It is used to filter data in a way.
The ***WHERE*** clause is not only used in <em>SELECT</em> statements, but also in <em>UPDATE</em> and <em>DELETE</em> etc.

    SELECT * FROM employees
    WHERE emp_id = 'mk_23';

![where1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cc43jw7gvr6z0rrf0gzg.png)
Here we've specified that we want to retrieve record that for employee with id ***mk_23***
In this case the query will fetch on row since only one employee has the id since every id is unique
<em>Example2</em>


    SELECT * FROM employees
    WHERE department = 'IT';

![where2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9tiut0hiq5rp2srdn5cb.png)
Here we fetch all the records having the ***department*** as <em>IT</em>
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/se8v11xnk8fvh9i6elxn.png)
For this case it returns two reco.rds.

We will see more of <em>WHERE</em> as we progress with other statements, commands etc.

## 7. DELETE
The <em> DELETE </em> statement is used to delete existing records in a table.
<em>Syntax</em>

    DELETE FROM table_name WHERE condition;

Let's delete the records for Lucky

    DELETE FROM employees
    WHERE emp_id = 'mn_210';

![del1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l3fey2oioyo8g5juj357.png)
Here we have delete the record that has the ***emp_id*** with the value ***mn_210*** which is refers to Lucky Angie.
When we retrieve all the records we can see that the record has been deleted.

We can also delete all records at once.
***<em>Syntax</em> *** 

    DELETE FROM table_name;

<em>Example</em>

    DELETE FROM pets;

![delete all records](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zkttlj3d6cdvrma81tpf.png)
This format is used to delete every record from a table.
This however does not delete the table.


## 8. ORDER BY
The keyword is used to sort the result-set in ascending or descending order.
It sorts the records i descending order, use the ***DESC** keyword.
***Syntax***

    SELECT col_1, col_2, ...
    FROM table_name
    ORDER BY col_1, col_2, ... ASC|DESC

Let's explore the keyword using the employees database and employees table.

    SELECT *
    FROM employees
    ORDER BY emp_id;

![orderBy1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2q2jveomcys4pxlypjrg.png)
Here we've ordered the records of the employees table using the <em>emp_id</em> column in ascending order

    SELECT * FROM employees
    ORDER BY firstName, lastName;

![order 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qz6si95ansi3lbavt7g2.png)
Here we've order the records with the ***firstName*** and ***lastName***. In an instance where the firstName is similar in two or more records the records will be ordered in respect to the ***lastName***

## 10. GROUP BY
The <em>GROUP BY </em> statement groups row that have the same values into summary rows.
It is often used with aggregate functions
- COUNT()
- MAX()
- SUM()
- AVG()

***Syntax***

    SELECT col_name(s)
    FROM table_name
    WHERE condition
    GROUP BY col_name(s)
    ORDER BY col_name(s)

Let's view various instances of ***GROUP BY*** statement

    SELECT COUNT(department), department FROM employees
    GROUP BY department;

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e2uf8ept1vqrjnljkxz5.png)
Here we count the number of employees in each department