Data Manipulation Language (DML) commands in SQL are used to manage data records in database tables, but it is not used to change the database structure. INSERT, UPDATE, and DELETE are the most commonly used DML commands. SELECT statement is also considered as a part of DML commands.
INSERT: It is used to insert new data records or rows in the database table.
Syntax:
Example:
Here, we inserted a record in the employees’ table in four respective columns.
UPDATE: It is used to set the new value to the particular record of a field or column.
Syntax:
Example:
Here, we updated the salary of employees table, which has employee_id 101.
DELETE: It is used to remove one or more rows from the database table.
Syntax:
Example:
Here, we deleted the data of employee_id 101 from the employees’ table.
In DELETE, we use the WHERE condition.
SELECT: It is used to fetch selected fields or columns from a database table.
The SELECT statement is used to retrieve the data from the database and display it. The syntax of a SELECT statement includes multiple clauses, such as SELECT, FROM, WHERE, GROUP BY, HAVING, and more.
Parameters used in the above syntax are:
column_name: Column's name that needs to be retrieved or chosen for the result.
table_name: Database table's name from which the results must be retrieved.
filter_condition: Condition used to filter records for the outcome.
group_filter_condition: Condition used to filter the groups.
The FROM clause always executes first, it goes into the database and searches for the table and after that, it puts that table under execution.
After this WHERE clause starts the execution, it filters the table based on the condition given.WHERE clause executes row by row.
After the WHERE clause GROUP BY clause starts the execution. It groups the record based on the column passed. It executes row by row. The GROUP BY clause creates a group, so the clause executing after the GROUP BY clause executes group by group.
The HAVING clause starts execution after the GROUP BY clause. It filters the group based on the group filter condition. It executes group by group.
SELECT clause executes last.
To select all the columns present in the table, * is used.
Example:
Output:
Description:
Using ' * ' is like saying, “Give me everything from this table without naming each thing one by one.” It's a way to fetch all the data in a table in one go.
It gives all columns and records in the output.
A statement that gives us a result is known as an expression.
An expression consists of two types:
1. OPERAND
2. OPERATORS (+, -, *, /)
OPERANDS consists of two types:
1. Column name
2. Literals (Direct Value)
Literals are of three types:
1. Number Literals
2. Character Literal
3. Date Literal
Character Literal and Date should always be enclosed within single quotes.
E.g.
'SOME WORD' or '15-AUG-47'
Note: SQL commands/Syntax is not case sensitive, but all the character literals are case sensitive, i.e. ‘SMITH’, 'Smith' & 'smith' would all be different entities.
Alias is an Alternative name given to a column or an expression. In the result table.
An alias can be used with or without using the 'AS' keyword.
Alias name should be a single word or a String enclosed in double quotes.
Alias is not mandatory but is recommended to be provided.
Q. Write a query to display the name, and salary of the employee along with their annual salary from the emp table.
Output:
Description:
Here, we gave ANUUAL_SAL as an Alias to the SAL*12 column name by using the AS keyword.
So, in the result table, we will get NAME and ANUUAL_SAL as column names, i.e. SAL*12 is replaced with ANUUAL_SAL.
To remove repeated or duplicated values in a result table, we can use the DISTINCT clause.
For this clause, we have to pass column_name or an expression as an argument.
When using the DISTINCT clause, it should always be the first argument in the SELECT clause.
We can also pass multiple columns for the DISTINCT clause.
It effectively removes any combination of duplicates from the specified columns.
Q. Write a query to display the salary of employees without any duplication.
Description:
If we passed only SELECT SAL then, in output, we will get all the values including duplicate and repeated also.
EMP table:
But, Here we pass the DISTINCT clause in the SELECT statement for the SAL*12 columns.
So, the output will get the following values without repetition or duplication.
Output:
Where clause is used to filter the records.
For the WHERE clause, we can pass the filter condition as an argument.
WHERE clause executes row by row.
WHERE clause executes after the execution of the FROM clause.
We can pass multiple conditions for the WHERE clause by using logical operators.
Q. Write a query to display the names of employees getting salaries less than 2000 in department no 10.
Description:
Here, we want the name of the employee whose salary is less than 2000 and who belongs to department 10.
So, we passed the NAME in the SELECT clause and filter conditions as SAL < 2000 AND DEPTNO= 10 in the WHERE clause.
On these filter conditions, we will get the final output.
We use the GROUP BY clause to group the records.
For the GROUP BY clause, we can pass a column name or an expression as an argument.
It executes row by row.
We can write GROUP BY expressions along with multi-row functions in the SELECT clause.
Any column name or expression that is written in the GROUP BY Clause should be present in the SELECT statement.
Any column name or expression that is written in the GROUP BY Clause is known as Group-by-expression.
After the execution of the GROUP BY clause, it creates groups, and if any clause executes after Group by clause it executes group by group.
Q. Display the total salary of all departments.
Output:
Description:
Here, we want total salaries per department.
We used the sum(sal) aggregate function for finding total salaries in the SELECT clause.
We want salaries per department so we passed DEPTNO in the GROUP BY clause because the GROUP BY clause groups the records based on DEPTNO.
To filter the groups HAVING clause is used.
We can pass multi-row function conditions in the HAVING clause.
It executes group by group.
If you're using the HAVING clause it should be used after the GROUP BY clause. It cannot be used without the GROUP BY clause.
Q. Write a query to display department no and the number of clerks working in each department if there are at least 2 clerks.
Description:
Here, we want a deptno and a number of clerks working in each department if there are at least 2 clerks.0
So, we passed JOB = 'CLERK' in the WHERE clause for filtering the data based on the above condition.
We want groups based on DEPTNO to calculate no of clerks working in each department. That's why we passed DEPTNO in the GROUP BY clause.
COUNT (*) >= 2 is passed in the HAVING clause to filter the groups.
Q. Write a query to display department no and total salary needed to pay all emp in each department if there are at least 4 emp in each department.
Q. Write a query to display the number of employees earning a salary of more than 1200 in each job and the total salary needed to pay employees of each job must exceed 3800.
Q. Display job-wise the highest salary only if the highest salary is more than Rs1500.
Output:
Description:
Here, we have to create a group based on the JOB to find job-wise highest salaries.
MAX (SAL) > 1500 is passed in the HAVING clause to filter the group.
Operators in SQL
SQL operators are used to perform operations on data stored in database tables. They can filter, manipulate, and perform calculations on data in SQL queries.
Operators are classified into:
Arithmetic Operators ( +, -, *, / )
Relational Operators ( >, <, >=, <=, =, < > or != - not equals to)
Logical Operators ( NOT, AND, OR )
Special Operators ( IN, LIKE, BETWEEN, IS)
Special operators are useful for handling patterns and sets of values.
1) IN operator – it is used for evaluating multiple values.
Q. List the employees in departments 10 & 20.
Output:
Description:
Here, the IN operator works like it finds DEPTNO in 10 OR 20.
So, it gives all the records of the table where DEPTNO is 10 OR 20.
Q. List all the clerks and analysts.
Output:
Description:
Here, the IN operator works like it finds JOB in ‘CLERK’ or ‘ANALYST’.
So, it gives all the records of the table where jobs are ‘CLERK’ or ‘ANALYST’.
2) LIKE operator – It is used for pattern matching.
% (percentage) matches 0 or n characters.
_ (underscore) matches exactly one character.
Q. List all the employees whose name starts with S.
Output:
Description:
Here, the LIKE operator works like it matches the letter ‘S’ at the first position.
So, it gives all the records of the table where the letter ‘S’ is in the first position.
Q. List the employees whose names have the letter L as the 2nd character.
Output:
Description:
Here, the LIKE operator works like it matches the letter ‘L’ at the second position.
So, it gives all the records of the table where the letter ‘L’ is in the second position.
3) BETWEEN operator – It is used for searching based on a range of values.
Q. List the employees whose salary is between 2000 and 3000.
Output:
Description:
Here, the BETWEEN operator works like it is searching the values between the range 2000 to 3000.
So, it gives all the records of the table whose salary is between 2000 to 3000.
4) IS operator – This operator is used to compare with nulls.
Q. List all the employees whose commission is null.
Output:
Description:
Here, the IS operator works like it gives the values by comparing them with null.
So, it gives all the records of the table whose commission is null.
Logical operators allow us to create specific filters and conditions for our data.
1) AND operator – It returns true if both conditions are true.
Q. List all the salesmen in department 30.
Output:
Description:
Here, the AND operator works as it searches in the table for JOB as a ‘Salesman’ and DEPTNO in 30.
Both conditions are required to be true for the final output.
So, it gives all the records of the table whose JOB = ‘Salesman’ AND DEPTNO = 30.
2) OR operator – OR operators return true if any one of the conditions is true.
Q. List all the employees whose name starts with S or A.
Output:
Description:
Here, the OR operator works as it searches in the table for a name that has an ‘S’ letter at the first position or an ‘A’ letter at the first position.
Any one of the conditions is required to be true for the final output.
So, it gives all the records of the table that match the pattern like ENAME LIKE ‘S%’ OR ENAME LIKE ‘A%’.
3) NOT operator – It acts like negation.
Q. List all the employees except those who are working in departments 10 & 20.
Output:
Description:
Here, the NOT operator works like negation.
It gives all the records of the table whose deptno is not present in 10 or 20.
To conclude, mastering the use of SELECT statements in SQL is essential for extracting insightful data. The FROM clause tells your database where to look for the data you want to work with. The WHERE clause is like a data filter. It helps you find and select the specific data you want. When you want to organize data into neat groups, think of GROUP BY as your handy organizer. It makes your data easier to understand. HAVING is like a super-filter. It helps you narrow down your grouped data. With the guidance and resources provided in this guide, you can confidently optimize your SQL queries, regardless of your experience level.
About time, you dive in and optimize your SQL SELECT statements with confidence!
