DATAPATASHALA A COMPLETE GUIDE TO TOP SQL COMMANDS datapatashala_official © learndatapatashalain lin’ datapatashala ®& [email protected]INTRODUCTION TO SQL Today the universal language for managing relational database is SQL. It's both powerful and relatively easy to learn. However, SQL can get tricky as your data structures become more complex. But you can be a pro at it with some practice. So, here are essential commands that you’ll use frequently. This doc doesn’t contains all the commands but includes few of the essential commands: e DDL (Data Definition Language), e DML (Data Manipulation Language), e DQL (Data Query Language), and e DCL (Data Control Language). @ datapatashala_official @ learndatapatashalain lin’ datapatashala ®% [email protected]Data Definition Language (DDL) DDL is a subset of SQL primarily used for defining and managing the structure of a database. DDL commands are non-transactional, meaning they immediately affect the database structure. * Key DDL commands include CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, CREATE VIEW, and DROP VIEW. CREATE TABLE Command © Syntax: CREATE TABLE table_name (columnnamet datatype, columnname2 datatype, ...); © Purpose: Creates a new table with specified columns and data types. ¢ Example: CREATE TABLE employees (id INT, name VARCHAR(50), salary DECIMAL); @ datapatashala_official @ learndatapatashalain lin’ datapatashala ®% [email protected]This SQL command creates a table named "employees" with three columns: id (INT) integer column to store unique employee A variable-length character name (VARCHAR(S®)) column for employee names with a maximum length of 50 characters. A decimal number column to store salary (DECIMAL) . employee salaries. ALTER TABLE Command e Syntax: ALTER TABLE table_name ADD|MODIFY| DROP column_name datatype; e ADD: e Example: ALTER TABLE employees ADD department VARCHAR(30) ; @ datapatashala_official @ learndatapatashalain lin’ datapatashala ®% [email protected]¢ This command adds a new column named "department" to the "employees" table with a data type of VARCHAR(30). e MODIFY: ¢ Example: ALTER TABLE employees MODIFY salary DECIMAL(10, 2); e In this example, the command modifies the data type of the "salary" column in the "employees" table. It changes the data type to DECIMAL with a precision of 10 and a scale of 2. e@ DROP: e Example: ALTER TABLE employees DROP department; e@ The DROP command removes the "department" column from the "employees" table. DROP TABLE Command @ Syntax: DROP TABLE table_name; e Purpose: Deletes an entire table and all its data. e Example: DROP TABLE employees; @ datapatashala_official @ learndatapatashalain lin datapatashala ®% [email protected]The purpose of this command is to remove the entire "employees" table, including all the data it contains. This action permanently deletes the table and all the records (rows) stored in it. CREATE INDEX Command e Syntax: CREATE INDEX index_name ON table_name (column1, column2, ...); © Purpose: This command is used to create an index on one or more columns within a database table, improving the speed of data retrieval operations. e@ Example: CREATE INDEX idx_name ON employees (id); The provided example creates an index named "idx_name" on the "employees" table, specifically on the "id" column. This indexing will optimize the retrieval of data based on the "id" column. DROP INDEX Command e Syntax: DROP INDEX index_name; @ datapatashala_official @ learndatapatashalain lin’ datapatashala = [email protected]Data Manipulation Language (DML) DML, or Data Manipulation Language, is a subset of SQL used to manage and manipulate data within a database. SELECT Statement Syntax: SELECT column1, column2 FROM table WHERE condition; e Purpose: Retrieves data from a database. e Example: SELECT first_name, last_name FROM employees; This command selects the first name and last name of employees working in the Sales department. SELECT - Retrieving Data You can select specific columns or use ™' to retrieve all columns. datapatashala_official @ learndatapatashalain lin’ datapatashala ®% [email protected]Filtering is done using the WHERE clause. Example: SELECT product_name, price FROM products WHERE category = 'Electronics'; Here, we retrieve the product name and price from the ‘products’ table for items in the 'Electronics’ category. The WHERE clause filters the results to include only Electronics products. Example: SELECT * FROM products; In this example, the SELECT statement is used with *, as the column list. When you use *, it's a shorthand way of selecting all columns from the specified table, in this case, the products table. INSERT Statement Syntax: INSERT INTO table (column1, column2, ...) VALUES (valuel, value2, ...); Purpose: Adds new data to a table. Example: INSERT INTO customers (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]'); @ datapatashala_official @ learndatapatashalain lin’ datapatashala = [email protected]Explanation: This command inserts a new customer, ‘John Doe’, into the ‘customers’ table with the specified first name, last name, and email. INSERT - Adding Multiple Rows You can insert multiple rows in a single statement in the following way. Example: INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 101, '2023-10-18'), (2, 102, '2023-10-19'); Multiple rows can be inserted in a single command. Here, two orders are added to the ‘orders’ table with their respective order IDs, customer IDs, and order dates. UPDATE Statement Syntax: UPDATE table SET column1 = valuel, column2 = value2 WHERE condition; ¢ Purpose: Modifies existing data in a table. @ datapatashala_official @ learndatapatashalain lin’ datapatashala = [email protected]Transaction Control Language (TCL) ¢ Transaction Control Language (TCL) commands are a subset of SQL commands used to manage and control database transactions. @ They include commands like COMMIT, ROLLBACK, and SAVEPOINT, allowing you to control the integrity and consistency of your database. COMMIT Command Syntax: COMMIT; © Purpose: Commits the current transaction, making all changes permanent in the database. e Example: BEGIN; -- Perform SQL operations COMMIT ; @ datapatashala_official @ learndatapatashalain lin’ datapatashala ®% [email protected]In this example, the BEGIN initiates a transaction, and COMMIT makes all changes within that transaction permanent. ROLLBACK Command © Syntax: ROLLBACK; © Purpose: Rolls back the current transaction, undoing all changes made within the transaction. © Example: BEGIN; -- Perform SQL operations ROLLBACK; When ROLLBACK is executed, all changes made within the transaction are undone, and the database returns to its previous state. SAVEPOINT Command Syntax: SAVEPOINT savepoint_name; © Purpose: Creates a savepoint within a transaction to which you can later roll back. datapatashala_official @ learndatapatashalain in datapatashala es [email protected]e Example: BEGIN; -- Perform SQL operations SAVEPOINT spi; -- More SQL operations ROLLBACK TO sp1; SAVEPOINT creates a point in the transaction where you can later roll back, preserving changes made before the savepoint. SET TRANSACTION Command e Syntax: SET TRANSACTION [transaction_properties]; e Purpose: Sets various transaction properties such as isolation level or access mode. e Example: SET TRANSACTION ISOLATION LEVEL SERTALIZABLE; This example sets the isolation level to SERIALIZABLE, which ensures that concurrent transactions do not interfere with the current one, providing high data consistency. datapatashala_official @ learndatapatashalain in datapatashala es [email protected]DATAPATASHALA Hope You Liked The Content Join DataPatashala and become a certified data professional datapatashala_official @ learndatapatashalain in datapatashala ®& [email protected]