SQL: Beginners First Dive Into Data Analysis II

Introduction

In the last part of the blog series you have taken the basic understanding of the SQL, its history and some options you have for SQL. Lets's learn about the working of SQL. Thinking about the working of SQL, the most basic question arises in our mind is How can we start working with SQL or From where the journey of SQL start. So let's dive deeper and get the hang of working with SQL.

Data Types in SQL

Before learning more about SQL working let's first understand about the Data Types. SQL supports a wide variety of Data Types. These are as follows:

1. Integer or int: Used for whole numbers (positive or negative) without decimal places. Example: id INT, Age INTEGAR.
2. Varchar or char: Used for variable-length and fixed-length character strings. This takes the number of chararcters as argument. Example: city CHAR(50), name VARCHAR(50).
3. Float or Double: Used for floating-point numbers (numbers with decimal places). Example: price FLOAT, salary DOUBLE.
4. Date: Used for date type variables. Example: birthdate DATE.
5. Time: Used for time type variables. Example: login_time TIME.
6. Datetime or Timestamp: Used for date and time type variables. Example: created_at DATETIME, updated_at TIMESTAMP.
7. Boolean or Bool: Used for boolean or true/false values. Example: is_active BOOLEAN, has_permit BOOL.
8. Numeric or Decimal: Used for exact numeric values with a specified precision and scale. This takes precision and scale as arguments. Example: price NUMERIC(10, 2), rating DECIMAL(3, 1).
9. Binary Large Object: Used for storing binary data, such as images or documents. Example: image BLOB.

Data Definintion Language (DDL)

The working of SQL is categorized how it works such as defining the schema, creating databases, querying the data out of databases as per our need, using different methods on queryed data and much more. The first and starting point is Data Definition Language (DDL). Data Definition Language (DDL) deals with the definition and management of database structures. It includes commands for creating, altering, and deleting database objects such as tables, indexes, and schemas. DDL statements enable users to define the structure of a database, specifying how data is stored, organized, and related to other data within the database. The following are the main methods under Data Definition Language.

Create: Used to create a new database object, such as a table.

1CREATE TABLE employees (
2   employee_id INT PRIMARY KEY,
3   first_name VARCHAR(50),
4   last_name VARCHAR(50),
5   hire_date DATE);

Here we are creating a SQL Table named employees, which has four columns named as employee_id, first_name, last_name, and hire_date.

Alter: Used to modify the structure of an existing database object, such as adding or dropping columns.

1ALTER TABLE employees ADD COLUMN department_id INT;

We have created employees table and it has four columns, and now we have addded one new column named department_id.

Truncate: Used to remove all rows from a table, but retains the table structure for future use.

1TRUNCATE TABLE employees;

After using `Truncate` method we have cleared all the rows from the employees table.

Comment: Used to add comments to the data dictionary, providing additional information about a database object.

1COMMENT ON TABLE employees IS 'Table storing employee information';

We have successfully used Comment method and added a new comment.

Rename: Used to rename an existing database object, such as a table.

1ALTER TABLE employees
2RENAME TO staff;

Here we are using two methods one is Alter and other is Rename. After using it we have renamed our employees table into staff.

Drop: Used to delete an existing database object, such as a table or an index.

1DROP TABLE employees;

This Drop method will completely delete the table.

Data Query Language (DQL)

Data Query Laguage (DQL) statements are used for performing queries on the data. The purpose of the DQL Command is to get some schema relation based on the query passed to it. We can define DQL as follows it is a component of SQL statement that allows getting data from the database. It includes the SELECT statement. This command allows getting the data out of the database to perform operations with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program.

1SELECT employee_id from employees;

That's how we can retrieve information out of the database using SELECT statement.

Conclusion

Let's just finish this part of the SQL series here. We have understood how to start create, alter, truncate, drop and rename table or tables using DDL and even how to query some data out of the table using DQL. In the next part we will move forward and take our time to get the understanding of other methods of SQL.

Till then enjoy and stay tuned for next part

Thanks