SQL - (Structured Query Language)
Pratibha Kumari J.
Chief Digital Officer @ DataThick | Results-driven Chief Digital Officer
SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases
SQL allows users to perform a wide range of operations on a database, including inserting, updating, and deleting data, as well as selecting and retrieving data for analysis and reporting. It also includes powerful tools for joining tables
Some common SQL commands include:
SQL is a popular and widely used language in the field of data analysis and database management. It is used by a variety of software applications, from simple desktop tools to complex enterprise systems, and is supported by most modern database management systems, including MySQL, Oracle, and Microsoft SQL Server.
Some additional key features and concepts of SQL:
Overall, SQL is a powerful and flexible language that is widely used in the field of database management and data analysis. Its rich set of features and ability to work with large datasets make it a valuable tool for businesses and organizations of all sizes.
SQL Commands List
Here is a list of some commonly used SQL commands:
This is not an exhaustive list, as SQL has many other commands and variations depending on the specific database management system being used.
Some additional SQL commands that can be useful in database management and data analysis:
Again, these are just a few examples of the many SQL commands and variations that are available for managing and analyzing relational databases.
DDL - Data Definition Language
DDL stands for Data Definition Language, and it refers to the subset of SQL commands that are used to define and manage the structure of a database. Here are some common DDL commands:
These commands are used to define and manage the schema of a database, which includes the tables, columns, constraints, indexes, and other objects that make up the database structure. The schema defines the relationships between different entities in the database, and it determines how data is stored and organized within the database.
It's important to note that DDL commands are typically used by database administrators and developers, rather than end-users. End-users typically interact with the database using DML (Data Manipulation Language) commands, which are used to insert, update, delete, and retrieve data from the database.
DML - Data Manipulation Language
DML stands for Data Manipulation Language, and it refers to the subset of SQL commands that are used to manipulate the data stored in a database. Here are some common DML commands:
These commands are used by end-users to interact with the data stored in a database. The SELECT command is used to retrieve data from one or more tables, and it can be used to filter, sort, and aggregate the data based on specified criteria. The INSERT command is used to add new data to a table, and it requires that the data being inserted matches the structure of the table. The UPDATE command is used to modify existing data in a table based on specified criteria, and it can be used to change the values in one or more columns of the table. The DELETE command is used to remove data from a table based on specified criteria, and it permanently deletes the data from the table.
It's important to note that DML commands can have a significant impact on the data stored in a database, and they should be used carefully to avoid unintended consequences or data loss. Additionally, DML commands can be used in combination with transaction management commands such as COMMIT and ROLLBACK to ensure data consistency and integrity.
DCL - Data Control Language
DCL stands for Data Control Language, and it refers to the subset of SQL commands that are used to control access to a database and manage user permissions. Here are some common DCL commands:
These commands are used by database administrators to manage user permissions and control access to the database. The GRANT command is used to give specific permissions to a user or role, such as the ability to read, write, or execute stored procedures on a specific table. The REVOKE command is used to remove previously granted permissions from a user or role, while the DENY command is used to explicitly deny specific permissions to a user or role.
The CREATE USER command is used to create a new user account in the database, while the ALTER USER command is used to modify the properties of an existing user account. The DROP USER command is used to delete an existing user account from the database.
It's important to note that DCL commands can have a significant impact on the security and integrity of a database, and they should be used carefully to ensure that users have the appropriate permissions to access and modify data in the database. Additionally, database administrators should follow best practices for managing user accounts, such as enforcing strong passwords and limiting the number of users with administrative privileges.
DQL - Data Query Language
DQL stands for Data Query Language, and it refers to the subset of SQL commands that are used to retrieve data from a database. The most common DQL command is SELECT, which is used to retrieve data from one or more tables based on specified criteria.
Here are some common DQL commands:
These commands are used by end-users to retrieve data from a database, and they can be combined in various ways to perform complex queries and generate reports.
It's important to note that DQL commands can have a significant impact on the performance of a database, especially when used with large datasets or complex queries. Therefore, it's important to optimize queries and use appropriate indexing and partitioning strategies to improve query performance.
SQL Constraints
SQL constraints are rules that are defined on a table to restrict the type of data that can be inserted, updated, or deleted from the table. Constraints help ensure data integrity and consistency, and they are used to enforce business rules and prevent errors and data inconsistencies.
Here are some common SQL constraints:
NOT NULL constraint: This constraint specifies that a column cannot contain NULL values, which are used to represent unknown or missing data.
UNIQUE constraint: This constraint specifies that a column or a combination of columns must contain unique values, which are used to prevent duplicate data from being inserted into the table.
PRIMARY KEY constraint: This constraint specifies that a column or a combination of columns must contain unique values and cannot contain NULL values, which are used to uniquely identify each record in the table.
FOREIGN KEY constraint: This constraint specifies a relationship between two tables, where the values in a column of one table must match the values in a column of another table. This is used to enforce referential integrity between related tables.
CHECK constraint: This constraint specifies a condition that must be true for each row in the table, which is used to ensure that only valid data is inserted into the table.
DEFAULT constraint: This constraint specifies a default value that is used when a value is not provided for a column during an insert operation.
Constraints can be defined when a table is created using the CREATE TABLE statement, or they can be added later using the ALTER TABLE statement. Constraints can also be enabled or disabled using the ENABLE or DISABLE keywords.
It's important to note that constraints can impact the performance of a database, especially when used with large tables or complex queries. Therefore, it's important to carefully design and optimize constraints to ensure that they improve data integrity without negatively impacting performance.
Database Normalization
Database normalization is the process of organizing a database in a way that reduces data redundancy and ensures data integrity. The goal of normalization is to minimize data duplication, prevent inconsistencies, and simplify queries and updates.
Normalization involves breaking down a large table into smaller tables and creating relationships between them based on their attributes. This is done by applying a series of rules or normal forms that define how tables should be structured.
There are several normal forms, including:
Normalization is an iterative process that involves identifying dependencies, creating new tables, and defining relationships between them. By following these rules, normalization helps to eliminate data redundancy, reduce data anomalies, and improve the performance and maintainability of a database. However, it's important to note that normalization can also lead to more complex queries and joins, which can impact performance. Therefore, it's important to strike a balance between normalization and performance optimization.
SQL Operators
SQL operators are used to perform operations on values and expressions in SQL queries. Here are some common SQL operators:
SQL operators can be used in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, and other clauses of SQL queries to perform various operations and comparisons on data.
SQL Expressions
SQL expressions are combinations of values, operators, and functions that evaluate to a single value. SQL expressions can be used in SELECT, WHERE, GROUP BY, HAVING, and other clauses of SQL queries to manipulate and filter data. Here are some common SQL expressions:
SQL expressions can be used to filter, aggregate, and transform data in a wide variety of ways, making them a powerful tool for data manipulation and analysis.
SQL Joins
SQL joins are used to combine rows from two or more tables based on a related column between them. SQL joins allow you to extract and combine data from multiple tables in a single query. Here are the most common types of SQL joins:
Inner join: An inner join returns only the rows that have matching values in both tables based on the specified join condition. The syntax for an inner join is:
SELECT column1, column2, ...
FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Left join: A left join returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, the result contains NULL values for the right table columns. The syntax for a left join is:
SELECT column1, column2, ...
FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
Right join: A right join returns all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, the result contains NULL values for the left table columns. The syntax for a right join is:
SELECT column1, column2, ...
FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
Full outer join: A full outer join returns all the rows from both tables and combines them based on the specified join condition. If there are no matching rows in either table, the result contains NULL values for the columns of the table without a matching row. The syntax for a full outer join varies depending on the database system.
SQL joins can be used to combine data from different tables based on a related column, making it easy to extract and analyze information from complex databases.
SQL Sub Queries
SQL subqueries, also known as nested queries, are queries that are nested inside another query. Subqueries are used to retrieve data from one table based on the values in another table, or to perform complex calculations on a subset of data. Here are some common types of SQL subqueries:
Recommended by LinkedIn
Scalar subquery: A scalar subquery returns a single value that can be used in a larger query. For example:
SELECT name, age, (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
This query calculates the average salary of all employees and includes it as a column in the result set.
Correlated subquery: A correlated subquery uses values from the outer query in the inner query to filter the results. For example:
SELECT name, age
FROM employees e
WHERE age > (SELECT AVG(age)
FROM employees WHERE department = e.department);
This query retrieves the name and age of employees whose age is greater than the average age of employees in their department.
Subquery with IN operator: A subquery with the IN operator returns a list of values that match a condition in the outer query. For example:
SELECT name, age
FROM employees
WHERE department IN
(SELECT department FROM departments
WHERE location = 'New York');
This query retrieves the name and age of employees who work in a department located in New York.
SQL subqueries can be used to perform complex calculations, filter data based on multiple conditions, and extract data from multiple tables in a single query. However, subqueries can be slow and resource-intensive, so it is important to use them judiciously and optimize their performance as much as possible.
SQL Useful Functions
SQL offers a variety of built-in functions that can be used to manipulate and analyze data in a database. Here are some of the most commonly used SQL functions:
Aggregate functions: Aggregate functions allow you to perform calculations on groups of rows in a table. The most common aggregate functions are:
String functions: String functions allow you to manipulate and analyze character strings. The most common string functions are:
Date functions: Date functions allow you to manipulate and analyze dates and times. The most common date functions are:
Math functions: Math functions allow you to perform mathematical calculations on numeric data. The most common math functions are:
These are just a few of the many functions available in SQL. Using SQL functions can help simplify complex queries and perform advanced data analysis in a database.
SQL Views
SQL views are virtual tables that are created by selecting data from one or more existing tables in a database. A view does not actually store data itself, but rather is a saved query that can be referenced like a table. Views can be used to simplify complex queries, provide a more intuitive interface for data access, and enforce security and access controls on database objects.
To create a view in SQL, you use the CREATE VIEW statement followed by a SELECT statement that defines the view's data. For example, the following query creates a view called "employee_names" that includes the names and job titles of all employees:
CREATE VIEW employee_names
AS
SELECT first_name, last_name, job_title
FROM employees;
Once the view is created, it can be used in the same way as a table. For example, you can query the view to retrieve a list of employee names and job titles:
SELECT first_name, last_name, job_title
FROM employee_names;
Views can also be used to simplify complex joins and subqueries. For example, the following query creates a view called "employee_salaries" that calculates the average salary of each job title:
CREATE VIEW employee_salaries
AS
SELECT job_title, AVG(salary) AS avg_salary
FROM employees GROUP BY job_title;
Once the view is created, you can use it to retrieve the average salary for a specific job title:
SELECT avg_salary
FROM employee_salaries
WHERE job_title = 'Manager';
Views can be modified and dropped like any other database object. However, it's important to note that changes made to the underlying tables will affect the data returned by the view. Therefore, it's important to test views thoroughly and update them as necessary to ensure that they continue to provide accurate and up-to-date information.
SQL Procedure
In SQL, a stored procedure is a group of SQL statements that are stored in the database and can be executed on demand. Stored procedures can be used to perform complex data processing, automate common tasks, and enforce business rules and data integrity.
To create a stored procedure in SQL, you use the CREATE PROCEDURE statement, followed by the procedure name, input parameters (if any), and the SQL statements that make up the procedure. Here's an example of a simple stored procedure that returns a list of all employees:
CREATE PROCEDURE get_all_employees
AS
BEGIN
SELECT * FROM employees;
END
Once the stored procedure is created, you can execute it using the EXECUTE statement:
EXECUTE get_all_employees;
Stored procedures can also accept input parameters, which can be used to filter or modify the results returned by the procedure. For example, the following stored procedure accepts a job title as an input parameter and returns a list of all employees with that job title:
CREATE PROCEDURE get_employees_by_job_title
@job_title VARCHAR(50)
AS
BEGIN
SELECT *
FROM
employees
WHERE job_title = @job_title; END
To execute this stored procedure, you pass in the job title as a parameter:
EXECUTE get_employees_by_job_title 'Manager';
Stored procedures can also be used to update, insert, or delete data from the database. For example, the following stored procedure accepts an employee ID and a new salary as input parameters and updates the employee's salary in the database:
CREATE PROCEDURE update_employee_salary
@employee_id INT,
@new_salary MONEY
AS
BEGIN
UPDATE employees
SET salary = @new_salary
WHERE employee_id = @employee_id; END
To execute this stored procedure, you pass in the employee ID and the new salary as parameters:
EXECUTE update_employee_salary 123, 50000.00;
Stored procedures are a powerful feature of SQL that can help streamline database operations and improve performance. However, it's important to use them judiciously and test them thoroughly to ensure that they are reliable and secure.
SQL Trigger
In SQL, a trigger is a special type of stored procedure that is automatically executed in response to certain events or actions, such as a data modification or a database operation. Triggers can be used to enforce data integrity, validate data changes, and automate business processes.
To create a trigger in SQL, you use the CREATE TRIGGER statement, followed by the trigger name, the event that will activate the trigger (such as an INSERT, UPDATE, or DELETE operation), and the SQL statements that make up the trigger. Here's an example of a simple trigger that logs all INSERT operations on a table:
CREATE TRIGGER log_inserts
ON employees
FOR INSERT
AS
BEGIN
INSERT INTO employee_logs (employee_id, log_message)
SELECT employee_id, 'New employee added' FROM inserted;
END
This trigger is activated whenever a new record is inserted into the "employees" table. It then inserts a new record into the "employee_logs" table, which contains the employee ID and a log message indicating that a new employee was added.
Triggers can also be used to enforce data integrity by rejecting invalid data changes. For example, the following trigger checks whether a new employee's salary is within a certain range and rolls back the transaction if it is not:
CREATE TRIGGER check_salary_range
ON employees
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS ( SELECT * FROM inserted WHERE salary < 25000 OR salary > 100000 )
BEGIN
ROLLBACK TRANSACTION;
PRINT 'Salary must be between $25,000 and $100,000';
END
END
This trigger is activated whenever a new record is inserted or updated in the "employees" table. It then checks whether the new salary is within the allowed range and rolls back the transaction and prints an error message if it is not.
Triggers can be a powerful tool for automating database operations and enforcing business rules. However, they can also have a significant impact on performance and should be used judiciously. It's important to test triggers thoroughly and ensure that they do not interfere with other database operations or cause unexpected behavior.
How to optimize SQL Query?
Optimizing SQL queries can improve the performance and efficiency of your database operations. Here are some tips for optimizing SQL queries:
By following these tips, you can optimize your SQL queries and improve the performance of your database operations. It's important to test your queries thoroughly and make sure they return accurate results.
SQL VS NOSQL
SQL and NoSQL are two types of databases that differ in their structure, data model, and scalability. Here are some key differences between SQL and NoSQL databases:
SQL databases are often used for applications that require complex queries, strong data consistency, and a fixed schema, such as financial systems, transaction processing, and content management systems. NoSQL databases are often used for applications that require flexible data models, high scalability, and real-time data processing, such as social media platforms, e-commerce, and Internet of Things (IoT) applications.
Overall, the choice between SQL and NoSQL databases depends on the specific needs of the application, including the data structure, performance requirements, scalability needs, and consistency requirements.
SQL SUMMARY
SQL (Structured Query Language) is a standard language used to manage relational databases. It allows users to create, modify, and manipulate databases and tables, as well as query data and retrieve results.
Some of the key components of SQL include:
SQL also includes a range of operators, expressions, functions, and keywords that can be used to customize queries and manipulate data.
SQL databases can be optimized for performance by using techniques such as indexing, minimizing subqueries, avoiding SELECT * statements, and using temporary tables.
There are two main types of databases: SQL and NoSQL. SQL databases are relational, with a defined schema and structured data. NoSQL databases are non-relational, with flexible data models and unstructured or semi-structured data.
The choice between SQL and NoSQL databases depends on the specific needs of the application, including the data structure, performance requirements, scalability needs, and consistency requirements.
.NET Developer | Jr. Application Developer
1yVery useful
Data Scientist | Well Intervention Engineer | Web Developer
1yThank you!
Sales Associate at American Airlines
1yThanks for sharing
Data Scientist 🧑💻 | Python🐍 | SQL 📊 | ML🖥|Tableau📈| Buildspace
1yThank you for such a holistic article on SQL. It is definitely equivalent to an entire SQL course summed up in 30 minutes. Thank you for bringing out the nuances of the language👍