Advance Sql For Data Analytics

  • Home
  • 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. 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.

  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.