SQL

 

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It is used to insert, update, retrieve and delete data from databases. Some common SQL commands include SELECT, INSERT, UPDATE and DELETE. SQL is used by many popular databases, such as MySQL, SQL Server, and Oracle.

 

 

There are several types of SQL, including:

1.            DDL (Data Definition Language) - These commands are used to define the database schema. Examples include CREATE, ALTER, and DROP.

2.            DML (Data Manipulation Language) - These commands are used to manipulate the data within the database. Examples include SELECT, INSERT, UPDATE, and DELETE.

3.            DCL (Data Control Language) - These commands are used to control access to the data in the database. Examples include GRANT and REVOKE.

4.            DDL (Data Dictionary Language) - These commands are used to manage the database's metadata (data about the data). Examples include DESCRIBE and EXPLAIN.

5.            TCL (Transaction Control Language) - These commands are used to manage the transactions in the database. Examples include COMMIT, ROLLBACK and SET TRANSACTION.

6.            DTL (Data Transformation Language) - These commands are used to transform data in database. Examples include SELECT, UNION, EXCEPT, INTERSECT etc.

It's worth noting that not all SQL database systems support all the types of SQL commands.

 

 

a>   DDL stands for Data Definition Language. It is a subset of SQL commands used to define the structure of a relational database, including creating, altering and deleting database objects such as tables, views, indexes, procedures, and triggers.

Some common examples of DDL commands include:

1.            CREATE - Used to create a new database or its objects (like table, index, function, views, store procedure, etc.)

2.            ALTER - Used to alter the structure of the database, for example to add or drop a column in a table.

3.            DROP - Used to delete objects from the database, for example to delete a table or a view.

4.            TRUNCATE - Used to remove all records from a table, including all spaces allocated for the records are removed.

5.            RENAME - Used to rename an object existing in the database.

DDL statements are used to create and modify the database schema. They are executed during the database design phase and typically don't change the data stored in the database.

It's important to be cautious when using DDL commands, as they can permanently alter or delete important database objects. It's a good practice to backup the database before executing any DDL commands.

 

1.CREATE

                CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    customer_name VARCHAR(255),

    product VARCHAR(255),

    quantity INT,

    order_date DATE

);

 

 

2.            ALTER:

 

ALTER TABLE orders

ADD COLUMN order_total DECIMAL(10,2);

 

3.            DROP:

                                DROP TABLE orders;

 

4. TRUNCATE

TRUNCATE TABLE orders;

 

5.            RENAME:

 

RENAME TABLE orders TO archive_orders;

 

 

 

DML stands for Data Manipulation Language. It is a subset of SQL commands used to manipulate the data within a relational database, including inserting, updating, and deleting data.

Some common examples of DML commands include:

1.            SELECT - Used to retrieve data from one or more tables in the database.

2.            INSERT - Used to insert new data into a table.

3.            UPDATE - Used to modify existing data in a table.

4.            DELETE - Used to delete data from a table.

The SELECT statement is used to query the data from one or more tables, it can also be used to retrieve data from other database objects like views. The INSERT, UPDATE, and DELETE statements are used to modify the data in the tables. These statements are used to manipulate the data stored in the database.

DML statements are executed during the normal operation of a database and typically change the data stored in the database. It's important to be cautious when using DML commands, as they can permanently change or delete important data. It's a good practice to backup the data before executing any DML commands and make use of transactions to ensure the data consistency.

 

1.            SELECT:

SELECT * FROM customers;

 

2.            INSERT:

INSERT INTO customers (first_name, last_name, email)

VALUES ('John', 'Doe', 'johndoe@email.com');

 

3.            UPDATE:

UPDATE customers

SET first_name = 'Jane'

WHERE customer_id = 1;

 

4.            DELETE:

 

DELETE FROM customers

WHERE customer_id = 2;

 

 

DCL stands for Data Control Language. It is a subset of SQL commands used to control access to the data in a relational database, including granting and revoking privileges to users.

Some common examples of DCL commands include:

1.            GRANT - Used to provide specific access privileges to users or roles, for example:

 

GRANT SELECT, INSERT ON orders TO user1;

 

2.            REVOKE - Used to revoke specific access privileges from users or roles, for example:

 

REVOKE UPDATE, DELETE ON orders FROM user1;

 

 

 

TCL stands for Transaction Control Language. It is a subset of SQL commands used to manage the transactions in a relational database, including committing, rolling back, and setting the transaction properties.

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. The purpose of transactions is to ensure that the database remains in a consistent state, even in the event of an error or system failure.

Some common examples of TCL commands include:

1.            COMMIT - Used to save the changes made in a transaction to the database, for example:

 

COMMIT;

This command saves the changes made in the current transaction to the database.

 

2.            ROLLBACK - Used to undo the changes made in a transaction, for example:

ROLLBACK;

 

 

This command undoes the changes made in the current transaction and returns the database to its state before the transaction was started.

3.            SET TRANSACTION - Used to set the properties of a transaction, for example:

 

 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

This command sets the isolation level of the current transaction to "READ COMMITTED", which means that the transaction can only read data that has been committed by other transactions.

TCL statements are used to manage the transactions in the database, they are executed during the normal operation of a database and typically change the state of the transactions. It's important to use TCL commands with caution, as committing or rolling back the wrong changes can affect the consistency of the data.

 

 

DTL (Data Transformation Logic) in SQL refers to the process of transforming and cleaning data in a relational database management system (RDBMS) before it can be used for reporting or analysis. This typically involves the use of SQL commands such as SELECT, JOIN, and GROUP BY to extract, filter, and aggregate data from multiple tables, as well as commands such as UPDATE and DELETE to modify and clean the data. The goal of DTL is to create a "clean" and consistent dataset that can be used for downstream tasks such as data modeling, reporting, and analysis.

It is a process of transforming data from one format to another format and also it can be used to extract, join, filter and aggregate data from multiple tables and also cleaning the data by using commands like update and delete.

DTL is mainly used to make the data ready for data modeling, reporting, and analysis.

 

 

 

•             SELECT is used to query data from one or more tables, and retrieve only the columns and rows that match specific criteria.

•             UNION, EXCEPT and INTERSECT are used to combine the result of two or more SELECT statements. UNION combines the result of two SELECT statements and returns unique rows, EXCEPT returns distinct rows from the first SELECT statement that are not in the second SELECT statement, INTERSECT returns distinct rows that are returned by both SELECT statements.

•             JOIN is used to combine rows from two or more tables based on a related column between them.

•             GROUP BY and HAVING are used to group rows with similar values and filter groups based on a specific criteria.

•             WHERE is used to filter rows based on a specific criteria.

•             ORDER BY is used to sort the result set in ascending or descending order.

These commands are used to extract, filter, join and aggregate data from multiple tables and to make the data ready for reporting, analysis or modeling.