Essential SQL Commands for Data Science

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;

This command (SHOW DATABASES) lists the databases managed by the server.

SHOW TABLES;

SHOW COLUMNS FROM table_names;

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;

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;

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;

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;

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;

We fetch all the columns and rows that are distinct from the pets table;

3. CREATE Database

The *** CREATE DATABASE*** statement is used to create a new SQL database;

CREATE DATABASE employees;

From the above commands we've created a database called employees;

we can now USE the database and now create it's tables;

4. CREATE TABLE

The CREATE TABLE 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));

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 INSERT INTO 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');

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');

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 UPDATE 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';

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 SELECT statements, but also in UPDATE and DELETE etc.

SELECT * FROM employees
WHERE emp_id = 'mk_23';

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
Example2

SELECT * FROM employees
WHERE department = 'IT';

Here we fetch all the records having the department as IT

For this case it returns two reco.rds.

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

7. DELETE

The DELETE statement is used to delete existing records in a table.
Syntax

DELETE FROM table_name WHERE condition;

Let's delete the records for Lucky

DELETE FROM employees
WHERE emp_id = 'mn_210';

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.
**Syntax **

DELETE FROM table_name;

Example

DELETE FROM pets;

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;

Here we've ordered the records of the employees table using the emp_id column in ascending order

SELECT * FROM employees
ORDER BY firstName, lastName;

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 GROUP BY 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;

Here we count the number of employees in each department