Categories of SQL command to know for Data Analysis

SQL Command

Relational databases can be managed and manipulated using the programming language SQL (Structured Query Language). Here are some crucial SQL command categories to be aware of for data analysis:

Data Definition Language (DDL)

Table of Contents

Data Definition Language (DDL) modifies the structure of the table by adding, removing, or changing tables, among other things. All DDL commands are automatically committed, which permanently saves all database modifications. The following commands are included in DDL:

Create

This command builds a new table and has a predefined syntax. The CREATE statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE employees: This part of the statement indicates that you are creating a new table named “employees.”

(id INT PRIMARY KEY,): This line defines the first column of the table, named “id.”

INT specifies that it is an integer data type, meaning it will store whole numbers.
PRIMARY KEY indicates that the “id” column will be the primary key for the table. A primary key is a unique identifier for each record in the table.

(name VARCHAR(100),): This line defines the second column of the table, named “name.”

VARCHAR(100) specifies that it is a variable character field, capable of storing up to 100 characters. This column is typically used for storing names or textual information.

(salary DECIMAL(10, 2)): This line defines the third column of the table, named “salary.”

DECIMAL(10, 2) specifies a decimal data type with a total of 10 digits, where 2 digits are reserved for the decimal part. This is commonly used for storing monetary values.

In summary, the SQL statement is creating a table named “employees” with three columns: “id” for unique identification, “name” for storing names, and “salary” for storing decimal values such as salaries. The “id” column is designated as the primary key, ensuring each record in the table has a unique identifier.

Alter

An ALTER command modifies an existing database table. This command can add additional columns, drop existing columns, and even change the data type of columns involved in a database table. An ALTER command syntax is:

ALTER object type object name parameters;

For example:

ALTER TABLE employees
ADD department VARCHAR(50);

In this example, we added a new column called “department” to the employees table, capable of storing up to 50 characters.

Drop

A DROP command is used to delete objects such as a table, index, or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it. Drop statement syntax is:

DROP object type object name;

For example:

DROP TABLE employees;

In this example, we’re deleting the Employee table.

Truncate

Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later. Truncate statement syntax is:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE employees;

Comment

This statement allows you to add comments to a table or column, which can be useful for documentation purposes. For example:

COMMENT ON COLUMN employees.salary IS 'The salary of the employee';

What are the DDL commands?

Create

This command builds a new table and has a predefined syntax. The CREATE statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE employees: This part of the statement indicates that you are creating a new table named “employees.”

(id INT PRIMARY KEY,): This line defines the first column of the table, named “id.”

INT specifies that it is an integer data type, meaning it will store whole numbers.
PRIMARY KEY indicates that the “id” column will be the primary key for the table. A primary key is a unique identifier for each record in the table.

(name VARCHAR(100),): This line defines the second column of the table, named “name.”

VARCHAR(100) specifies that it is a variable character field, capable of storing up to 100 characters. This column is typically used for storing names or textual information.

(salary DECIMAL(10, 2)): This line defines the third column of the table, named “salary.”

DECIMAL(10, 2) specifies a decimal data type with a total of 10 digits, where 2 digits are reserved for the decimal part. This is commonly used for storing monetary values.

Alter

ALTER object type object name parameters;

For example:

ALTER TABLE employees
ADD department VARCHAR(50);

In this example, we added a new column called “department” to the employees table, capable of storing up to 50 characters.

Drop

DROP object type object name;

For example:

DROP TABLE employees;

In this example, we’re deleting the Employee table.

Truncate

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE employees;

Comment

This statement allows you to add comments to a table or column, which can be useful for documentation purposes. For example:

COMMENT ON COLUMN employees.salary IS 'The salary of the employee';

Data Query Language (DQL)

Data Query Language (DQL) is used to fetch data from the database. A group of SQL commands known as DQL are used to extract data from databases. The DQL command that is used the most is SELECT. Here are a few DQL command examples:

Create Table

This command creates a new table with a predefined syntax. The CREATE TABLE statement syntax is:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE employees: This part of the statement indicates that you are creating a new table named “employees.”

(id INT PRIMARY KEY,): This line defines the first column of the table, named “id.”

INT specifies that it is an integer data type, meaning it will store whole numbers.
PRIMARY KEY indicates that the “id” column will be the primary key for the table. A primary key is a unique identifier for each record in the table.

(name VARCHAR(100),): This line defines the second column of the table, named “name.”

VARCHAR(100) specifies that it is a variable character field, capable of storing up to 100 characters. This column is typically used for storing names or textual information.

(salary DECIMAL(10, 2)): This line defines the third column of the table, named “salary.”

DECIMAL(10, 2) specifies a decimal data type with a total of 10 digits, where 2 digits are reserved for the decimal part. This is commonly used for storing monetary values.

Insert Data

This command inserts data into the newly created table. The INSERT INTO statement syntax is:

INSERT INTO employees (id, name, salary) VALUES
    (1, 'John Doe', 50000.00),
    (2, 'Jane Smith', 60000.00),
    (3, 'Michael Johnson', 55000.00),
    (4, 'Emily Williams', 62000.00),
    (5, 'Robert Brown', 48000.00),
    (6, 'Lisa Davis', 53000.00),
    (7, 'William Wilson', 58000.00),
    (8, 'Linda Taylor', 51000.00),
    (9, 'James Anderson', 54000.00),
    (10, 'Mary Martinez', 57000.00);

This command will insert data into the “employees” table.

Retrieve All Data

This query retrieves all rows and columns from the “employees” table. The SELECT statement syntax is:

SELECT * FROM employees;

Retrieve Specific Columns

This query retrieves only the “name” and “salary” columns from the “employees” table. The SELECT statement syntax is:

SELECT name, salary FROM employees;

Filter Data

This query retrieves rows from the “employees” table where the salary is greater than 55000. The SELECT statement syntax is:

SELECT * FROM employees WHERE salary > 55000;

DML Commands

The database can be changed by using DML commands. It is in charge of making any kind of database modifications. DML commands cannot permanently save all database changes since they are not auto-committed. They are rollbackable. The four primary DML commands in SQL are as follows:

INSERT Command

The INSERT command is used to add new rows to the table. The INSERT INTO statement syntax is:

INSERT INTO employees (id, name, salary) VALUES (11, 'Alex Johnson', 59000.00);

This command will insert a new employee into the “employees” table.

UPDATE Command

The UPDATE command is used to modify existing data. For example:

-- Update John Doe's salary to $55000
UPDATE employees SET salary = 55000.00 WHERE name = 'John Doe';

This command updates the salary of John Doe to $55000.00.

Another example to update multiple rows at once:

-- Increase salary by 10% for employees with salary less than 55000
UPDATE employees SET salary = salary * 1.10 WHERE salary < 55000;

DELETE Command

The DELETE command is used to remove rows from the table. For example:

-- Delete the employee with ID 5
DELETE FROM employees WHERE id = 5;

This command deletes the employee with ID 5 from the "employees" table.

Another example to delete rows based on a condition:

-- Delete employees with a salary less than 50000
DELETE FROM employees WHERE salary < 50000;

INSERT Multiple Rows

You can insert multiple rows at once using the INSERT INTO statement. For example:

INSERT INTO employees (id, name, salary) 
VALUES
    (12, 'Sarah Brown', 58000.00),
    (13, 'Thomas Miller', 53000.00);

DDL and DML Commands

DDL (Data Definition Language) Commands

DDL commands are used to define and manage database structures. These commands help in creating, modifying, or deleting database objects like tables, indexes, and views. They do not manipulate the actual data but deal with the structure of the database itself.

Common DDL Commands

Some of the commonly used DDL commands include:

CREATE: Used to create a new table, view, or other database objects.
ALTER: Used to modify an existing database object, such as adding or deleting a column in a table.
DROP: Used to delete a database object, such as a table or view.
TRUNCATE: Removes all records from a table, but the structure remains intact for future use.

Example of a DDL Command

-- CREATE a new table
CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Grade VARCHAR(10)
);

DML (Data Manipulation Language) Commands

DML commands are used for manipulating data within tables. Unlike DDL commands, DML commands work with the data contained in the tables and are used to retrieve, insert, update, or delete data.

Common DML Commands

Some of the commonly used DML commands include:

SELECT: Used to query and retrieve data from one or more tables.
INSERT: Adds new rows of data into a table.
UPDATE: Modifies existing records in a table.
DELETE: Removes records from a table based on a specified condition.

Example of a DML Command

-- SELECT query to retrieve all data from the Students table
SELECT * FROM Students;

-- INSERT a new student record
INSERT INTO Students (ID, Name, Age, Grade)
VALUES (1, 'John Doe', 20, 'A');

-- UPDATE an existing record
UPDATE Students
SET Grade = 'B'
WHERE ID = 1;

-- DELETE a student record
DELETE FROM Students
WHERE ID = 1;

Comparing DML and DDL Commands in SQL

This table provides an overview of the differences between Data Manipulation Language (DML) and Data Definition Language (DDL) commands in SQL. Understanding these differences is crucial for effective database management.

Feature	DML	DDL
Purpose	Manipulates data within existing tables	Defines the structure of the database
Syntax	Primarily uses INSERT, UPDATE, DELETE, and SELECT	Primarily uses CREATE, ALTER, and DROP
Impact	Affects data within tables	Affects the overall database structure
Examples	INSERT INTO customers (customer_id, name) VALUES (1, ‘John Doe’);	CREATE TABLE customers (customer_id INT, name VARCHAR(50));

Data Control Language (DCL)

To control access to database objects, use these instructions. GRANT and REVOKE are a couple of examples of commands in this group. DCL (Data Control Language) commands are used to control access to data within a database. They are primarily concerned with granting or revoking permissions and privileges to users. Here’s how you can use DCL commands on the “employees” table and dataset:

GRANT

A person or group of users can be granted special rights with this command. The GRANT command has the following syntax:

-- Grant SELECT privilege on the employees table to a user or role
GRANT SELECT ON employees TO username_or_role;

REVOKE

This command is used to remove privileges from users or roles. The REVOKE command has the following syntax:

-- Revoke SELECT privilege on the employees table from a user or role
REVOKE SELECT ON employees FROM username_or_role;

DCL commands are used to manage permissions, access control, and security within the database. Keep in mind that the specific syntax and implementation might vary depending on the database system you are using.

Transaction Control Language (TCL)

The management of transactions, which are collections of database activities that are handled as a single unit of work, is done using these commands. The commands COMMIT and ROLLBACK are examples of this class. Transaction Control Language (TCL) consists of a set of SQL commands that control the transactions in a database. Transactions are sequences of one or more SQL statements that are executed as a single unit of work. TCL commands allow you to manage the changes made to a database during transactions. Here are the main TCL commands:

COMMIT

This command is used to permanently save changes made in the current transaction to the database. Once committed, the changes become permanent and visible to other transactions. The COMMIT command syntax is:

COMMIT;

ROLLBACK

This command is used to undo changes made in the current transaction. It reverts the database to the state it was in before the transaction began. The ROLLBACK command syntax is:

ROLLBACK;

SAVEPOINT

A savepoint marks a specific point within a transaction to which you can later roll back. It allows you to selectively undo parts of a transaction while leaving other parts intact. The SAVEPOINT command syntax is:

SAVEPOINT sp1;

ROLLBACK TO SAVEPOINT

This command undoes changes made after a specific savepoint within a transaction. The ROLLBACK TO SAVEPOINT command syntax is:

ROLLBACK TO SAVEPOINT sp1;

Transaction Control Language (TCL)

COMMIT

COMMIT;

ROLLBACK

This command is used to undo changes made in the current transaction. It reverts the database to the state it was in before the transaction began. The ROLLBACK command syntax is:

ROLLBACK;

SAVEPOINT

SAVEPOINT sp1;

ROLLBACK TO SAVEPOINT

This command undoes changes made after a specific savepoint within a transaction. The ROLLBACK TO SAVEPOINT command syntax is:

ROLLBACK TO SAVEPOINT sp1;

Comparing DCL and TCL Commands in SQL

This table provides an overview of the differences between Data Control Language (DCL) and Transaction Control Language (TCL) commands in SQL. Understanding these differences is crucial for effective database management and security.

Feature	DCL	TCL
Purpose	Controls access to the database.	Manages transactions within the database.
Syntax	Primarily uses GRANT and REVOKE.	Primarily uses COMMIT, ROLLBACK, and SAVEPOINT.
Impact	Affects user permissions and access control.	Affects the state of the data by managing transaction boundaries.
Examples	GRANT SELECT ON employees TO user;	COMMIT; ROLLBACK; SAVEPOINT sp1;

SQL Command Categories and Their Purposes

This table provides an overview of the different SQL command categories, their purposes, and examples of each. Understanding these categories is essential for effective database management.

Category	Purpose	Examples
Data Definition Language (DDL)	Defines the database structure.	CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE
Data Manipulation Language (DML)	Manipulates data within tables.	INSERT, UPDATE, DELETE, SELECT
Data Control Language (DCL)	Controls access to the database.	GRANT, REVOKE
Transaction Control Language (TCL)	Manages transactions.	COMMIT, ROLLBACK, SAVEPOINT

What is the purpose of the SQL SELECT statement in data analysis?

Answer: The SQL SELECT statement is used to retrieve data from one or more tables. It allows you to specify which columns to retrieve and apply filtering conditions using the WHERE clause.

How can you filter rows from a table using SQL?

Answer: You can filter rows from a table using the WHERE clause in conjunction with the SELECT statement. The WHERE clause allows you to specify conditions that the rows must meet to be included in the result set.

What is the significance of the SQL GROUP BY clause?

Answer: The GROUP BY clause is used to group rows with similar values in specified columns. It’s often used in combination with aggregate functions to perform calculations on grouped data.

Explain the difference between the INNER JOIN and LEFT JOIN SQL commands.

Answer: The INNER JOIN returns only the matching rows from both tables based on the specified condition. On the other hand, the LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there’s no match, NULL values are returned for the right table’s columns.

How can you calculate the average of a specific column in a SQL table?

Answer: You can calculate the average of a specific column using the AVG aggregate function in combination with the SELECT statement and optionally the GROUP BY clause.

Question 6: What is the purpose of the SQL INSERT statement?

Answer: The SQL INSERT statement is used to add new rows into a table. It allows you to specify the values for each column in the new row.

Explain the concept of a primary key in SQL.

Answer: A primary key is a column or a set of columns that uniquely identify each row in a table. It ensures data integrity by preventing duplicate or null values in the key columns.

How does the SQL ORDER BY clause work?

Answer: The ORDER BY clause is used to sort the result set of a query based on one or more columns. You can specify the sorting order (ascending or descending) for each column.

What is the purpose of the SQL ALTER TABLE statement?

The SQL ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns, changing data types, or adding constraints.

How can you use a subquery in SQL for data analysis?

A subquery is a nested query that can be used within another query. It can be employed to retrieve data that will be used for filtering, calculations, or comparisons in the outer query. Subqueries are enclosed in parentheses and can appear in various parts of a SQL statement, such as the WHERE clause or the FROM clause.

Call to Action: Take Your SQL Skills to the Next Level

Explore Additional SQL Tutorials and Real-World Practice

Now that you’ve mastered the LIKE operator and wildcards, it’s time to expand your knowledge and practice with more advanced SQL topics. The best way to improve your skills is through hands-on experience. Explore the following tutorials and practice on real-world datasets to take your SQL expertise to the next level:

These resources will guide you through complex SQL concepts and offer practical tips for real-world applications. Keep learning and refining your skills, and don’t forget to practice on real datasets to solidify your knowledge.

Vista Academy Master Program in Data Analytics

Vista Academy’s Master Program in Data Analytics equips you with advanced skills in data analysis, machine learning, and visualization. With practical experience in tools like Python, SQL, Tableau, and Power BI, this program prepares you for high-demand roles in data science and analytics.

Learn More & Enroll Now

Call Now: 9411778145

Address: Vista Academy, 316/336, Park Rd, Laxman Chowk, Dehradun, Uttarakhand 248001

Vista Academy Master Program in Data Science

Vista Academy’s Master Program in Data Science offers in-depth training in advanced topics such as machine learning, artificial intelligence, big data analytics, and predictive modeling. Gain hands-on experience with Python, R, SQL, and TensorFlow to build a strong foundation for your career in data science.

Learn More & Enroll Now

Call Now: 9411778145

Address: Vista Academy, 316/336, Park Rd, Laxman Chowk, Dehradun, Uttarakhand 248001