SQL Basic and its operations for Beginners

Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. Databases can be found in almost all software applications. SQL is the standard language to query a database. This SQL tutorial for beginners will teach you database design. Also, it teaches you basic to advanced SQL.

The following areas we will cover in this blog

Key elements of a database

1. Relational Keys

2. Select Queries

3. Joins

4. Data Definition Language (DDL)

5. Data Manipulation Language (DML)

A database usually takes the key areas of a business and breaks them into structured tables. Each table will store information on a key area:

Product table - A table that brings all your product information together e.g. product name, product colour, product description e.g.

Orders table – This table keeps a record of each order your company has processed, this table will refer to a lot of key area in the business e.g. who made the sale, who was sold to, what was sold and when.

1.  Relational keys:

With each part of the business having its own table, we need to know how a table is structured and how they relate to each other:

• Primary Key

Tables within a database are structured for a purpose so that each table removes the need for duplicate values. For example, a product table should not have duplicates of the same product.

A primary key is assigned to each unique row within a table. This creates an efficient structure when you need to find related information. The product table could have a product ID unique to each product.To create a table with primary key, write query as

                        “create table products 
                                 (product_id int primary key,
                                 product_name varchar(200),
                                  product_color varchar(100));”

And the table look like below with product_id has primary key

• Foreign Key

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.

Look at the following two tables:

Persons Table

PersonID	Lastname	Firstname	Age
1	Hanson	Ola	40
2	Marie	Nik	35

Orders Table

OrderID	OrderNumber	PersonID
3	345346	3
1	456788	2
4	564734	1

• Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

• The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

• The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

• The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

2. Select queries

Understanding the structure of a table allows us to write queries that access the information we need form each table.

Imagine our orders table had all the company’s transactions over ten years and you wanted to know what was sold on 1st December 2017. If you were to get this information from the table it could take a long time scrolling to find the right lines. However, with a SQL Query we can get the computer to present the information we need.

SQL Query example:

SELECT column1, column2, columnN FROM table_name
WHERE Condition;

This query would show us the results of every order placed on this day. For a select statement we must assign which fields we want to retrieve, and then the table the fields come from. We can

then add any filters for the data allowing us to get answers to specific queries quickly.

3.Joins

Now we know how each table is structured (Primary Keys), how tables are linked (Foreign Keys) and how to write select queries. We can now look at the ways in which we can retrieve data from multiple tables simultaneously.

A join is how we connect two or more tables together. This allows us to summarise data that would normally be stored in different tables into one result set. This is useful when analysing data for report creation.

What is a join?

We saw earlier that our product table and order table have the Product ID field in common. We could then use this ID to join the two tables e.g. 

SELECT Order_ID, Order_Date, Product_Name, Product_Type, Product_Colour 
FROM Orders JOIN Product ON Orders.Product_ID = Product.Product_ID 

As you can see, the results would allow access to the information from the two separate tables.

4. Data Definition Language (DDL)

  Data Definition Language (DDL) is a vocabulary used in Transact-SQL (T-SQL) to create, edit and delete tables. 

CREATE: We use this each time we create a table, for example:

CREATE TABLE Product (
     Product ID int,
     Product Name Varchar(255),
     Product Type Varchar (255),
     Product Colour Varchar (255),
);

This script would create the table Product with the fields set out as the example we used above.

“int” and “Varchar” after the field name designate the data type of the field. “int” shows the field will store an integer, “Varchar” that the field will store text (or a string)

DROP: We use this to delete a table, including its structure and data. For example:

DROP TABLE Product;

This would delete the Product table and any data contained within it.

TRUNCATE: We use truncate to remove the data stored in a table but retain the table structure, for example:

TRUNCATE TABLE Product;

This would delete all data stored in the Product table, however, the table would remain with each field as set up originally.

ALTER: We can use this to change the structure of a table. For example, we can remove a field:

ALTER TABLE Product
DROP COLUMN Product Colour

This would remove the column the table and all data stored within it. We can also add additional columns to an existing table:

ALTER TABLE Product
ADD Product Description Varchar (255)

5.  Data Manipulation Language(DML)

Data Manipulation Language (DML) is a vocabulary used to retrieve and work with data stored within the database.

The most frequently used DMLs are

SELECT: We have already used this DML to specify which records we want to retrieve for reviewing.

SELECT Order Date, Order ID, Product ID FROM Orders Where Order Date = “1 Dec 2017”

This would retrieve the data in each row stored in the fields Order Date, Order ID and Product ID.

DELETE: This is the opposite to the select statement, where the data in each row we specify is removed from the table

           DELETE FROM Product WHERE Order Date = “1 Dec 2017”

This statement would remove each row from the database that matched the details of the WHERE clause. All data in the associated fields i.e. Product ID would also be deleted.

UPDATE: We can use this DML to correct or update a value within a table

e.g.

UPDATE Product SET Product Name = “Normal Kettle”, Product Colour = “Green” WHERE Product ID = 1

This statement would select rows where the Product ID is 1, and then update the existing values within fields Product Name and Product Colour.

INSERT: We can use the insert DML to add missing values to our tables

e.g.

INSERT INTO Product (Product ID, Product Name, Product Type, Product Colour) VALUES (13, “Stylish Cheese Grater”, 3, “Blue”)

This statement specifies the fields we wish to insert and the values to be added. You do not have to specify all fields, however any field not selected will be defaulted to a null value.

ADVANCED TOPICS IN SQL

• Common table expressions (CTEs).

• Recursive CTEs.

• Temporary functions.

• Self joins.

• Date-time manipulation.

• View

1. Common Table Expressions (CTEs)

If you’ve ever wanted to query a query, that’s when common table expressions (CTEs) come into play. CTEs essentially create a temporary table. Using CTEs is a great way to modularize and break down your code just like you would break an essay down into several paragraphs.

Basic Syntax:

WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )
 

To view the CTE result we use a Select query with the CTE expression name.

Select[Column1,Column2,Column3…..]from expression_name

or

Select *from expression_name

CTEs also allow you to do more advanced techniques like creating recursive tables.

2. Recursive CTEs

A recursive CTE is a CTE that references itself, just like a recursive function in Python. Recursive CTEs are especially useful when querying hierarchical data like organization charts, file systems, a graph of links between web pages, and so on.

A recursive CTE has three parts:

• The anchor member, which is an initial query that returns the base result of the CTE.

• The recursive member is a recursive query that references the CTE. This is UNION ALLed with the anchor member

• A termination condition that stops the recursive member.

Syntax

WITH RECURSIVE cte_name AS (
    cte_query_definition (the anchor member)
 
    UNION ALL
 
    cte_query_definition (the recursive member)
)
 
 
SELECT *
FROM   cte_name;

3. Temporary Functions

Knowing how to write temporary functions is important for several reasons:

It allows you to break code down into smaller chunks. It’s useful for writing cleaner code. It prevents repetition and allows you to reuse code, similar to using functions in Python.

Consider the following example:

SELECT name
       , CASE WHEN tenure < 1 THEN "analyst"
              WHEN tenure BETWEEN 1 and 3 THEN "associate"
              WHEN tenure BETWEEN 3 and 5 THEN "senior"
              WHEN tenure > 5 THEN "vp"
              ELSE "n/a"
         END AS seniority 
FROM employees

4. Self Joins

An SQL self-join joins a table with itself. You might think that such an action serves no purpose, but you’d be surprised at how common this is. In many real-life settings, data is stored in one large table rather than many smaller tables. In such cases, self-joins may be required to solve unique problems.

Let’s look at an example

 SELECT
    a.Name as Employee
FROM
    Employee as a
    JOIN Employee as b on a.ManagerID = b.Id
WHERE a.Salary > b.Salary

5. Date-Time Manipulation

You should definitely expect some sort of SQL questions that involve date-time data. For example, you may be required to group data by months or convert a variable format from DD-MM-YYYY to simply the month.

Some functions you should know are the following:

EXTRACT
DATEDIFF
DATE_ADD, DATE_SUB
DATE_TRUNC

6.View

A View in SQL is simply a virtual table created based on a result set of another SQL statement. Views were introduced to reduce the complexity of multiple tables and deliver data in a simple manner. Views help us maintain data integrity and provide security to the data, thus acting as a security mechanism.

Creating a View in SQL

The Views in SQL are created with the CREATE VIEW syntax.

Following is the basic syntax to create a VIEW in SQL:

CREATE VIEW view_name AS  
SELECT column1, column2...column N 
FROM table1, table2...table N 
WHERE condition;

SELECT  * FROM [view_name];

Learned a lot of things in one go? Don’t worry, go through the examples, scenarios to clear your concept well. Most of the concepts were aided by examples to convey the idea lucidly.

I hope this article elaborately describes basic concepts in SQL with proper codes for better understanding.