Advance Sql For Data Analytics

What is SQL?

SQL stands for Structured Query Language, which performs various functions on the database. With the help of SQL, we can manage, store, and retrieve data in databases.

Some functions that SQL can perform are:

  • SQL can execute queries in a database
  • It can retrieve data from a databaseSQL can insert, update and delete records in a database
  • It can also create new database
  • It can create Tables in a Database, etc.

However, the increase in the volume of data leads to a rise in the demand for qualified analysts who know intermediate to advanced level of SQL.

 

Some concepts of Advanced SQL

  1. Common Table Expressions (CTEs):  These are useful when we need to query a query.
  2. Pivot Data:  This is another advanced concept of SQL where it allows the user to pivot data.
  3. Self Joins: Self Joins help to join a table to itself. It helps in cases where all the data is given in a single table.
  4. Ranking of Data:  It ranks the data in a table or dataset per the given condition.
  5. Delta Values: Yet another essential advanced concept of SQL, used to compare two values from different periods.

So, these are some concepts of advanced SQL for data analytics and their uses.

You may also like to read: The Ultimate Guide on SQL

 

Basic Concepts of DBMS

Basic DBMS concepts include Schema, Constraints, Metadata, etc., but let us first understand what DBMS is.

Database Management System (commonly termed DBMS) is a computer-based record-keeping system. The primary purpose is to store and serve the data to multiple applications simultaneously.

Some Basic Concepts of DBMS are:

  1. Schema: Schema is the basic design of the database.
  2. Constraints: These are the restrictions on the type of data.
  3. Metadata: Metadata is the data about the data
  4. Query: Queries are used to access data from the database.

 

Connecting To Data Source

One effective method to establish a connection to a data source in a database management system (DBMS) such as SQL Server, Oracle, MySQL, etc., is by utilizing the SQL Server Import and Export Wizard. This tool facilitates the seamless transfer of dataset files from a source location to a designated destination. To illustrate, let’s consider an example of connecting to a data source in an SQL server.

The process for connecting to a data source in DBMS consists of the following steps:

  1. Open the Microsoft SQL Server Management Studio
  2. Click on the Databases option
  3. Select the database to which you want to add the source
  4. Give a right-click and select Task
  5. Click on Import Data
  6. Select the data source as per your file
  7. Specify the options that need to be specifie
  8. Click on Finish

 

Types of SQL Commands

SQL Commands are essential instructions presented in the form of SQL code to execute various tasks and queries on databases. These commands enable us to create tables, modify their structure, add data, and define user permissions, among other functionalities.

However, these are broadly divided into four major SQL commands, which are:

  1. DDL – Data Definition Language
  2. DQL – Data Query Language
  3. DML – Data Manipulation Language
  4. DCL – Data Control Language

Let us now look at each of the four major SQL commands individually.

You may also like to Read: List of SQL Commands

DATA DEFINITION LANGUAGE (DDL)

DDL in DBMS is used widely to create, alter, and delete the data in the databases. Any changes made in DDL are permanently saved as the commands of DDL are auto-committed.

DDL consists of the following commands:

  • CREATE: This clause or command is used to create databases or a table in the database.
  • ALTER: This command changes or alters the design of the database either by modifying an existing attribute or by adding a new one.
  • DROP: As the name says, it is used to delete the dataset stored in the database.

 

DATA QUERY LANGUAGE (DQL)

DQL in DBMS serves the purpose of querying tables or datasets in a database. It utilizes the SELECT command to retrieve data from the table, often supported by the WHERE clause to establish specific conditions. DQL is primarily employed to obtain schema relationships by executing queries against the database.

The syntax for the SELECT command is as follows:

SELECT expressions   

FROM TABLES   

WHERE conditions;

 

DATA MANIPULATION LANGUAGE (DML)

DML in DBMS deals with the anipulation of tables or objects of databases. The commands of DML are not auto-committed, so any changes made in the database are not saved permanently.

It consists of the following commands:

  • INSERT: This command is used to insert data into the tables.
  • UPDATE: It is used to update or modify the value of a column in a table.
  • DELETE: It is used to delete data from the tables.

DATA CONTROL LANGUAGE (DCL)

DCL in DBMS gives or removes permissions or authorities from a database user.

It mainly consists of two commands which are:

  • GRANT: This command provides access or authority to the user.
  • REVOKE: This command takes back the access or authority from the user.

How to Create A Database in SQL?

We all know that a database is the collection of organised information used for analysis, and one of the first steps of working in any system is creating a database in SQL.

The steps involved in the process of creating a database in an SQL server are as follows:

  1. Open Run
  2. Open MS SQL Server Management Studio
  3. Click on New Query
  4. Now use the SQL Command CREATE DATABASE
  5. Write the name of the database that you want.
  6. Click on Execute

Now, a new database is created under the Database Tab in the Management Studio.

 

Creating, Updating & Deleting Tables

For creating, updating and deleting tables and schema in SQL, we will follow the steps given below:

1. CREATING A TABLE

CREATE TABLE command is used to create a new table in the database. The syntax for creating the table is:

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

2. UPDATING A TABLE

UPDATE TABLE command is used to update the existing records of the table. The syntax for updating the table is:

UPDATE table_name 

SET column1 = value1, column2 = value2, … 

WHERE condition;

3. DELETING A TABLE

DELETE statement is used to delete the records of the table. The syntax for deleting the table is:

DELETE FROM table_name WHERE condition;

So, these are the steps for creating, updating and deleting tables and schema in SQL.

 

Setup Relationship in Table

Setting up relationships in tables in SQL server helps in minimising data redundancy and hence increasing efficiency. The process of setting up relationships in the table in SQL server is:

  1. Open SQL Server Management Studio
  2. Select two tables, one on the primary key side and the other on the foreign key side
  3. Right-click on the foreign-key side table and select Design
  4. From the Table Designer menu, select Relationships
  5. In the foreign key relationship box, select Add
  6. Select the type the relationship
  7. Click OK

So, now the relationship is set between the tables in the SQL server.

 

Types of Relationships

There are majorly three types of relationships in SQL:

  1. One-to-One Relatio
  2. One-to-Many Relation
  3. Many-to-Many Relation

Now, let us look at the three types of relationships in SQL individually:

  1. One-to-One Relation: In one-to-one relation, the relationship is established between two tables where the single row or record of one table is related to a single record of the second table and vice-versa.
  2. One-to-Many Relation: This is the most commonly used relationship in SQL. In this relationship, a single row of one table can be related to multiple rows of the second table, but the single row of the second table can be related to only one row of the first table.
  3. Many-to-Many Relation: In this relation, a single row or record of one table can be related to as many records of the second table and vice-versa.

SQL Constraints, Keys, Operators & Wildcard

  • SQL CONSTRAINS: These are the restrictions that limit the data type that can go in a table. For example, some frequently used SQL constraints are NOT NULL, UNIQUE, PRIMARY, FOREIGN, and DEFAULT.
  • SQL KEYS: SQL Keys are the attributes or columns that help identify a row in a table. The major SQL keys are PRIMARY, FOREIGN, UNIQUE, SUPER, CANDIDATE, ALTERNATE & COMPOSITE KEY.
  • SQL OPERATORS: SQL operators are primarily used to perform operations such as addition, subtraction, or comparison. Various SQL operators include Arithmetic, Logical, Comparison, and Negation.
  • WILDCARDS IN SQL: Wildcards in SQL are the character used to substitute one or more symbols in a string.

SQL Quaries

SQL Queries are the instructions or commands given to a database to extract data. The significant SQL queries are:

  1. SELECT: The SELECT query is used to select data from the database.
  2. FROM: The FROM query contains the list of databases from which a database is selected.
  3. TABLES: TABLES in SQL contain the data in the form of records (rows) and fields (columns).
  4. WHERE: The WHERE clause establishes a condition, and data is returned only when a certain condition is met.
  5. HAVING: The HAVING clause is used when the WHERE condition can’t be used with aggregate functions.
  6. GROUP BY: The GROUP BY query groups all the rows that have the same value. It is often used with aggregate functions such as COUNT, MAX, MIN, etc.
  7. ORDER BY: The ORDER BY clause orders the data in ascending or descending order.

What is SQL Sub Queries And TCL Commands?

SQL SUB-QUERIES: SQL Sub queries are also called nested queries, as the results of outer queries depend on the results of the inner query.

TCL COMMANDS: TCL Commands in SQL stands for Transaction Control Language Commands. This language deals with the management of transactions made by DML commands. Since changes via DML are temporary, TCL is used to store the data in DBMS permanently.

Major TCL Commands in SQL are COMMIT, ROLLBACK, and SAVEPOINT.

  • COMMIT command saves the data permanently
  • ROLLBACK command restores the data saved to the last Savepoint.
  • SAVEPOINT command temporarily saves the data so one can roll back to it if needed.

What are Joins?

JOINS in SQL are the commands to join rows or data from two or more tables based on a common column.

There are majorly four types of Joins in SQL:

INNER JOIN: The INNER JOIN returns the records that have the same values in both tables.

LEFT JOIN: The LEFT JOIN returns all the records from the left table and the matching records from the right table. The result is 0 from the right side if there is no matching.

RIGHT JOIN: The RIGHT JOIN returns all the records from the right table and the matching records from the left table. If there is no matching, the result is 0 from the left side.

OUTER JOIN: The OUTER JOIN is also called FULL JOIN. The OUTER JOIN returns all the records when there is a match in left or right table records.

 

How to Export Data From SQL?

Exporting data from an SQL server to Excel means transferring the database from an external source or destination to an internal source. The steps involved in Exporting data from SQL server to Excel are:

  1. Open the SQL Server Management Studio
  2. Select a Table and right-click on it
  3. Click on the Tasks option, then click on Export Data
  4. The wizard box opens up, so fill in the specific details like Data Source, Server name, Destination, etc.
  5. Click on Finish
  6. Open the file in the destination folder

Thus, we can see the data is exported from the SQL server to Excel.