Mastering SQL Commands: A Comprehensive Guide
Building the Foundation: Data Definition Language (DDL)
When it comes to defining the database structure or schema, Data Definition Language (DDL) commands are the way to go. These commands are essential for creating, modifying, and deleting database tables. Let’s dive into some key DDL commands with practical examples.
Creating Tables with CREATE
The CREATE TABLE command is used to create a new table in the database. For instance, the following SQL command creates a new table named Products with three columns: product_id (integer type), name (string type up to 100 characters), and price (decimal type for storing prices).
Modifying Tables with ALTER TABLE
The ALTER TABLE command is used to modify the structure of an existing table, such as adding, deleting, or renaming columns. For example, the following SQL command adds a column named email to the Customers table.
Deleting Tables with DROP TABLE
The DROP TABLE command is used to delete the specified table in our database. For instance, the following SQL command will delete the table named Orders.
Manipulating Data with Data Manipulation Language (DML)
Data Manipulation Language (DML) commands focus on handling data within the database, including most SQL statements. Let’s explore some essential DML commands with simple examples.
Inserting Data with INSERT INTO
The INSERT INTO statement is used to insert new rows into a database table. For example, the following SQL command inserts a new row into the Customers table with the given values.
Updating Data with UPDATE
The UPDATE statement is used to edit an existing row in a database table. For instance, the following command updates the age field of the customer with customer_id 5 to 29.
Deleting Data with DELETE
The DELETE statement is used to delete row(s) from a database table. For example, the following SQL command deletes all rows from the Customers table where the country is UAE.
Retrieving Data with Data Query Language (DQL)
Data Query Language (DQL) is used for querying and retrieving data from a database. It allows us to specify the exact data we want to see from one or more tables based on given conditions.
Selecting Data with SELECT
The SELECT statement is used to select (retrieve) data from a database table. For example, the following SQL command retrieves all rows from the Customers table where the country is UK.
Controlling Access with Data Control Language (DCL)
Data Control Language (DCL) commands include GRANT and REVOKE, which are used to control access to the database.
Granting Permissions with GRANT
The GRANT statement gives users access privileges to the database. For instance, the following command grants SELECT and UPDATE permissions on the Customers table to user1.
Revoking Permissions with REVOKE
The REVOKE statement withdraws access privileges given by the GRANT statement. For example, the following SQL query revokes SELECT permission on the Customers table from user1.
Managing Transactions with Transaction Control Language (TCL)
In SQL, TCL commands manage changes affecting the database.
Committing Changes with COMMIT
The COMMIT command is used for saving the changes made in the database. For example, the following SQL command updates the country of the customer with customer_id 4 and commits the transaction.
Rolling Back Changes with ROLLBACK
The ROLLBACK command is used to undo the transactions that have not been saved in the database. For instance, the following query deletes all records from the Orders table but then rolls back the transaction.