Structured Query Language (SQL) Database Management

Introduction to SQL

SQL Stands for Structured Query Language its helps to manage databases. It helps in creating data, operating on it, and retrieving it.

Types of database are Relational and Non-relational databases. Relational Database store the data in Structured data format in  tables. Non-relational database store the data in Unstructured data format in JSON files.

Note : "SQL is Relational Database".

What is CRUD? Why we need this?

These four operations form the foundation of all database interactions. Understanding CRUD will enable you to perform the essential tasks needed to manage data effectively. With CRUD operations, you can

C-Create | R-Read | U-Update | D-Delete

• Create new records by adding new data to the database.

• Read data by retrieving and viewing data stored in the database.

• Update existing records by modifying data already present in the database.

• Delete records by removing data that is no longer needed.

  
  

DDL - Data Definition Language:

DDL changes the structure of the table like creating a table, deleting a table, altering a table.

DDL are auto-committed, it permanently save all the changes in the database.

• CREATE It is used to create a new table in the database.

• DROP  It is used to delete both the structure and record stored in the table.

• ALTER  It is used to alter the structure of the database.

• TRUNCATE  It is used to delete all the rows from the table and free the space containing the table.

DML - Data Manipulation Language:

DML commands are used to modify the database. It is responsible for all the changes in the database.

DML commands are not auto-committed. It can't permanently save all the changes in the database. They can be rollback.

• SELECT - It used to select the data from the database and we can view it.

• INSERT - It is used to insert the data into the table.

• UPDATE - It is used to update the data in into the table.

• DELETE - It is used to  delete the data into the table.

TCL - Transaction Control Language:

• COMMIT means that the changes made in the current transaction are made permanent

• ROLLBACK statement cancels all modifications made by the current transaction.

Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

DROP vs DELTE vs TRUNCATE

This is the most Confusion Part in SQL because every comment looks like same but this all comments have different behavior.

SQL used in Sailpoint

SailPoint use SQL  to interact with various data sources, primarily for account aggregation, provisioning, and entitlement management. The specific SQL usage depends on the connector type (e.g., JDBC, SQL Server, SQL Loader) and the desired operations.

Account Aggregation:

• Retrieving user account information from databases.

• Joining multiple tables to gather comprehensive data.

• Filtering results based on specific criteria.

Provisioning:

• Creating, modifying, or deleting user accounts in target systems.

• Updating account attributes based on user information.

Entitlement Management:

• Assigning and revoking permissions or roles to users.

• Synchronizing entitlements between SailPoint and target systems.

• SQL in Different Connectors.

Types of connector

JDBC Connector:

• Provides flexibility for interacting with various databases using standard SQL.

• Requires defining account and entitlement queries.

• Supports stored procedures for complex operations.

SQL Server Connector:

• Optimized for Microsoft SQL Server.

• Offers additional features like password management and schema attributes.

SQL Loader Connector:

• Primarily for bulk data loading from flat files into SQL databases.

• Limited SQL usage compared to other connectors.

Conclusion

SQL (Structured Query Language) is a powerful and essential tool for managing and manipulating relational databases. It provides a standardized approach to creating, retrieving, updating, and deleting data, known as CRUD operations. Understanding SQL is fundamental for working with relational databases, as it facilitates the efficient organization and management of structured data.

Stay tuned to our blog to see more posts about Sailpoint products implementation and its related updates.