SQL Query Interview Questions and Answers Database

SQL stands for Structured Query Language. It is a programming language used to access a relational database. Using SQL, we can create or modify databases, schemas, tables, or table entries. There are plenty of SQL server providers that have adopted SQL as the standard language for dealing with their databases.

A table organizes data in rows and columns similar to a spreadsheet. All the data in a database is contained within one or more table objects. To create a table, we must first select a database or create one if not already present.

To create a database –

CREATE DATABASE sql_demo;

To select the database –

USE sql_demo;

To create a table, we need to use the CREATE TABLE syntax which defines the columns, their datatypes and constraints for the table.

Following code demonstrates how to create a user table with the required columns –

CREATE TABLE user (
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    email_id VARCHAR(100) PRIMARY KEY,
    contact INT,
    created_on DATETIME
);

An SQL table consists of rows and columns. Each row is known as a record and each column is known as a field. Each row or record represents a unique entry in the table whereas each column represents specific information for the respective record.

DBMS stands for Database Management System and RDBMS stands for Relational Database Management System. The major differences between the two are –

1. In DBMS data is stored in file format whereas, in RDBMS, data is stored in a tabular manner.
2. For large data requirements, RDBMS is preferred. DBMS can take care of only small data quantity.
3. RDBMS supports distributed databases and multiple users. DBMS supports only single users.
4. Due to this, the software and hardware requirements are more in RDBMS as compared to DBMS.
5. RDBMS is usually preferred due to its multiple layers of security while handling data. DBMS has a low-security profile.
6. Data linkage is possible in RDBMS but not in DBMS.

Expect to come across this popular question in SQL query interview questions and answers.

The major difference between SQL and NoSQL is the type of data that they store. SQL offers a schematic way to store organized and structured data, which is in tabular format, whereas NoSQL can store unstructured data like documents, key-value pairs, graphs, etc. SQL can be used to access data within a SQL database. Examples of SQL databases are MySQL, PostgreSQL, etc., and NoSQL databases are JSON, GraphQL, etc.

Individual database server providers offer a different set of datatypes but there are some common datatype formats in SQL. These datatypes can be found in almost all databases. The list of these commonly used datatypes is given below – 

SQL queries are built using SQL clauses which help to retrieve the data from the database based on some filter conditions, data subsets, ordering of data, etc. 

An entity is an independent real-world object that can be distinguished from other entities. For example, in an organization, employees, projects, salaries, etc can be considered entities.  

Relationships are the links that can be created between these entities. For example, employees can be associated with the projects and salary entities within the same database.

This is a most asked SQL Query interview questions.

There are four categories in which the SQL commands can be categorized in, namely –

1. Data Definition Language (DDL)
   DDL comprises commands like CREATE, ALTER, DROP, TRUNCATE, etc which are used to define the data structure within the database.
2. Data Manipulation Language (DML)
   DML comprises commands like SELECT, INSERT, UPDATE, DELETE, etc which are used to manipulate existing data present in the database schema.
3. Data Control Language (DCL)
   DCL takes care of the permissions related to the database objects, functions, and users. The two SQL commands GRANT and REVOKE are a part of DCL.
4. Transaction Control Language (TCL)
   TCL comprises commands like COMMIT, ROLLBACK, etc which are used to deal with the transactions that occur within the database.

An SQL server consists of one or more databases. Each of these databases consists of a schema (schema and database are synonyms in some databases) and tables within this schema. In order to create a database in SQL, we can use the following command –

CREATE DATABASE knowledgehut_db;

The command will throw an error if a database name “knowledgehut_db” is already present. To avoid such clashes, we can modify the query to –

CREATE DATABASE IF NOT EXISTS knowledgehut_db;

This will create the database only if it does not exist on the server.

A common question in SQL Query questions for interview, don't miss this one.

Aggregate functions in SQL are functions capable of aggregating data over a series of values and returning a single value as the result. The five common aggregate functions often used are e MIN(), MAX(), SUM(), AVG() and COUNT() which returns the minimum value, maximum value, sum of the values, average of the values, and the number of data points from a series of data. 

Following query returns the average salary of the employees in an organization – 

SELECT AVG(salary) FROM employees; 

Following query returns the maximum percentage acquired by a student in a class – 

SELECT MAX(percentage) FROM students WHERE class=‘X’; 

Each record in a SQL table can be uniquely identified using a column or a set of columns. These columns are referred to as PRIMARY KEY. For example, an employee ID is unique for each employee within an organization, therefore, an employee ID can act as a primary key for a table which contains information about the employees. Another example can be a table which contains information about the different television models from different brands. We know that a given brand is unlikely to have two different television models with the same model ID but two different brands can have the same model ID within their portfolio. Therefore, in such cases, we can refer to multiple columns for the primary key. Here, television brand and model ID both together can act as the primary key. Each record in the table can be uniquely identified with the help of a combination of these two fields.

The NOT NULL constraint is a column-level constraint that is used to enforce the nullable rule in a table field. A field with a NOT NULL constraint cannot have empty or null records. If attempted to insert a null record, it will throw an error.

The PRIMARY KEY column can uniquely identify a row in a table whereas UNIQUE KEY column will not contain any repeated values but might contain NULL values. Therefore, while both PRIMARY KEY and UNIQUE KEY constraints allow non-repeating values, PRIMARY KEY does not allow NULL values.

Column aliases are used to provide the columns with a temporary name. It does not affect the original table but is used in the resultant records for reference. This makes the column names readable and easier to interpret. We can mention the aliases in inverted commas right after their column declaration with an ‘AS’ keyword in the SELECT clause like – 

SELECT 
MIN(salary) AS “Minimum Salary” 
MAX(salary) AS “Maximum Salary” 
AVG(salary) AS “Average Salary” 
FROM 
employees; 

The IN operator is used to specify a condition where a field value is checked if it is present in a set of values. The condition is set to true is the value is found in the set, else false. 

For example, if we want to get the records for the employees whose location is either Mumbai, Bangalore or Pune, we can use – 

SELECT *  
FROM employees  
WHERE location IN (‘Mumbai’, ‘Bangalore’, ‘Pune’); 

The BETWEEN operator is used to check if a value is present in a range of values where both the range values are inclusive. The value and the range can be numbers, dates, or even text. The condition is set to true if the value lies between the range, else false. 

For example, if we want to check the names of the student who have scored between 80 and 90 then we can use – 

SELECT name 
FROM student 
WHERE SCORE BETWEEN 80 AND 90; 

This a tricky SQL Queries for an interview, be prepared to answer this one.

ALTER and SET commands can be used to modify an existing entry in the database table. For example, if we wish to replace the work location of the employees residing in Mumbai to Pune, then we can use the query – 

ALTER TABLE employees 
SET base_location = ‘Pune’ 
WHERE location = ‘Mumbai’; 

WHERE clause is used to filter records based on some condition. These conditions make use of logical operators. Some of the illustrated examples are – 

SELECT *
FROM employees
WHERE salary > 850000; 

Returns the records from the employees table where employee salary is greater than 850000. 

SELECT * 
FROM students 
WHERE name LIKE ‘%E’; 

Returns the records from the students' table where student's name ends with the letter ‘E’. 

In order to extract the first X records of any query we can make use of the LIMIT clause. It is the last clause that gets executed in the SQL order of execution. An example of usage is – 

SELECT * 
FROM employees 
ORDER BY employee_id DESC 
LIMIT 5; 

The query will return the first 5 records from the employee's table when the records are ordered in descending order of their employee IDs. 

Consider an employees table consisting of only 3 fields on the order name, location, and salary, then a record can be entered in the employee's table using the INSERT command. The 2 ways we can do this are – 

a. Method 1 – Mention column values in the right order 

INSERT INTO employees 
VALUES (‘John Doe’, ‘Indore’, 1000000); 

b. Method 2 – Mention both the column names and column. Here the order is referred from the query itself. 

INSERT INTO employees (name, location, salary) 
VALUES (‘John Doe’, ‘Indore’, 1000000); 

We can get unique records from the column with the help of DISTINCT. Use of DISTINCT in our query will return only the unique set of records, which means any duplicate entry will be shown only once. For example, to get only the unique locations of all the employees in an organization, we can use –

SELECT DISTINCT location FROM employees;

ORDER BY clause sorts the results in ascending or descending order. By default, it sorts the results in ascending order, or the ASC keyword can also be provided explicitly. The use of the DESC keyword will order the records in descending order. The syntax is given by – 

ORDER BY [column name] ASC|DESC

We can also sort the records with respect to multiple columns, for example sorting the records by name in ascending order and marks in descending order – 

SELECT * 
FROM students 
ORDER BY name ASC, score DESC;

IFNULL() is a function used in MySQL servers which takes in two parameters, one being the field name and the other parameter is the value to return in case of null entries. Not null values are returned as it is, while the null values are represented by the value provided in the second parameter. For example, null values from the gender column can be replaced with the text ‘Prefer not to say’ in the result using the following query – 

SELECT IFNULL(gender, “Prefer not to say”) 
FROM employees;

Similar functionality is present in other database servers but with different function names. Oracle servers have NVL(), SQL servers have ISNULL() and COALESCE() in PostgreSQL. 

The SELECT clause of SQL is used to provide the columns that need to be present in the results. We can either mention the individual column names or use ‘*’ if we want all the columns to be a part of the result. 

-- To output all the column 
SELECT * FROM employees LIMIT 5; 
-- To output the fields name, salary and location 
SELECT name, location, salary FROM employees; 

The sum of a column with real values can be calculated using the SUM() aggregate function. For example, the query that provides the sum of all the scores of class X students can be given by – 

SELECT SUM(score) FROM students WHERE class=‘X’;

The average value of a column with real values can be calculated using the AVG() aggregate function. For example, the query that provides the average salary of the employees in an organization can be given by – 

SELECT AVG(salary) FROM employees; 

The minimum and maximum values of a column with real values can be calculated using the MIN() and MAX() aggregate functions respectively. 

For example, the minimum and maximum salary of the employees in an organization is given by – 

SELECT 
MIN(salary) AS ‘Minimum’, 
MAX(salary) AS ‘Maximum’
AS employees; 

SELECT * 
FROM students 
WHERE name LIKE ‘A%’; 

Returns the records from the student's table where the student's name starts with the letter ‘A’. 

SELECT COUNT(DISTINCT location) 
FROM employees; 

Returns the count of the number of unique locations associated with the employees of the organization. 

A view in SQL is a fictitious table built from the results of a SQL statement. Like a true table, a view also has rows and columns. A view contains fields from one or more actual database tables. A view can be extended with SQL statements and functions to present data as though it were drawn from a single table. The CREATE VIEW statement is used to create a SQL view. 

Syntax: 

CREATE VIEW view_name AS 
SELECT column_1, column_2, ... , column_N 
FROM table_name 
WHERE condition; 

This is a frequently asked SQL Query interview questions for experienced professionals.

A foreign key, or a reference key, in SQL is used to link two tables in a database. The linkage is between one column from the first table to the primary key column present in another table. This helps to identify any value present in the column of the first table to the primary key column of another table maintaining the referential integrity. For example, table `students` consist of a field named, `course` which is a foreign key referencing to the primary key field `course_name` present in the `courses` table. The schema structure for this can be created using – 

CREATE TABLE courses ( 
course_name VARCHAR(100) PRIMARY KEY, 
duration INT, 
language VARCHAR(50) 
); 
CREATE TABLE students ( 
student_id INT PRIMARY KEY, 
name VARCHAR(50), 
course VARCHAR(100), 
FOREIGN KEY (course) REFERENCES courses (course_name) 
); 

When two or more tables are to be linked together to create resultant data, we need to make use of joins. Using joins ensures that the resultant data matches the right record from one table to another. For example, if a database contains two tables, namely, student and scores, then we would like to join these tables by taking the student ID column as a reference (assuming student ID is present in both tables). 

The different types of joins are – 

1. INNER JOIN 

Inner join matches one or more columns of a table with that of one or more tables and considers only those records from the tables which have the column values in common. For example, consider the student and scores table. When we apply inner join on both these tables using the student_id column, we get 3 records in the resultant table with student_id STU001, STU002 and STU003 since these IDs are common in both tables. 

SELECT
s.Student_ID AS Student_ID, 
First_Name, 
Last_Name, 
Class 
Marks, 
Grade 
FROM Student s JOIN Scores e ON s.Student_ID = e.Student_ID; 

2. CROSS JOIN 

Cross-join in SQL returns the cartesian product of the two tables. It joins each record from one table with the records in the other table. For example, consider table garment and table colours which are joined using a CROSS join. The resultant of the join will look like this – 

3. FULL OUTER JOIN 

FULL OUTER JOIN in SQL considers all the records from both the tables, i.e., both matching and non-matching ones. Where the match is not found the other table entries are provided with null values. 

SELECT
s.Student_ID AS Student_ID_X, 
e.Student_ID AS Student_ID_Y 
FROM Student s FULL OUTER JOIN Scores e ON s.Student_ID = e.Student_ID; 

4. LEFT OUTER JOIN OR LEFT JOIN 

Even if there are no matches in the right table, the SQL LEFT JOIN returns all rows from the left table. For the records in the left table where the match exists in the right table, the records are pulled otherwise the values in the data columns from the right table are represented as null for non-matching records. For example, the student_id STU001, STU002, and STU003 are common in both tables, therefore we can see them in the result along with STU005 id since it is present in the left table, that is, Students table. 

SELECT
s.Student_ID AS Student_ID, 
First_Name, 
Last_Name, 
Class 
Marks, 
Grade 
FROM Student s LEFT JOIN Scores e ON s.Student_ID = e.Student_ID; 

5. RIGHT OUTER JOIN 

The SQL RIGHT JOIN returns all rows from the left table, even if there are no matches in the right table. For the records in the right table where the match exists in the left table, the records are pulled otherwise the values in the data columns from the left table are represented as null for non-matching records. For example, the student_id STU001, STU002, and STU003 are common in both tables, therefore we can see them in the result along with STU010 id since it is present in the right table, that is, Students table. 

SELECT
s.Student_ID AS Student_ID, 
First_Name, 
Last_Name, 
Class 
Marks, 
Grade 
FROM Student s RIGHT JOIN Scores e ON s.Student_ID = e.Student_ID; 

An index is a disk-based structure linked to a table or view that facilitates quicker row retrieval. 

A table or view's table or view's columns are used to create keys in an index. These keys are kept in a structure (B-tree) that enables SQL Server to quickly locate the row or rows that correspond to the key values. The users cannot see the indexes, they are merely used to speed up queries. The CREATE INDEX statement is used to create indexes in tables. 

Syntax: 

CREATE INDEX index_name 
ON table_name (column_1, column_2, ... , column_N); 

For indexing purposes, NULL values in a column are considered duplicate values if they are repeated more than once. Indexing is not possible if you have duplicate entries in a column. Therefore, creating a unique index in a column with NULL values is only possible if there is only one NULL value present in the respective field. 

It's no surprise that this one pops up often in SQL Query interview questions for experienced.

A subquery is an additional query within an existing query. A subquery can be used in the SELECT, FROM, WHERE, and HAVING clauses and should be contained in parentheses. Subqueries in WHERE and HAVING clauses are classified into the following: 

1. Independent subquery 

The inner and outer queries are independent of one another in an independent subquery. Independent subqueries are further categorized into single-row and multiple-row types based on the number of returned rows. For example, the below query first executes the parenthesis to collect the average salary among the employees and then this value is further used to inspect the results of the outer query where the salary of an employee is greater than this average value. 

SELECT 
Emp_id, 
Salary, 
Department 
FROM Employee e1 
WHERE Salary ( 
SELECT AVG(Salary) 
FROM Employee e2); 

2. Correlated subquery 

A correlated subquery is a form of query where the execution of the inner query is dependent upon the outer query. An attribute from one of the tables in the outer query is used in the inner query. For each chosen record from the outer query, the inner query is run recursively. In the below example, the Department column of the outer sub-query is used inside the inner sub-query. Therefore, both the inner and outer queries are dependent or correlated. 

SELECT 
Emp_id, 
Salary, 
Department 
FROM Employee e1 
WHERE Salary ( 
SELECT AVG(Salary) 
FROM Employee
WHERE e1.Department = e2.Department); 

The practice of normalization improves data integrity in the table by removing redundant data and duplication. Additionally, normalization aids in database data organization. Setting the data into tabular form and removing duplicate data from relational tables involves several steps. n order to make sure that restrictions on database integrity effectively carry out their obligations, normalization arranges the columns and tables of a database. It is a methodical method of breaking down tables to eliminate redundant data and undesired traits like Insertion, Update, and Deletion anomalies. The different normal forms are: 

• 1st Normal Form (1NF) 
• 2nd Normal Form (2NF) 
• 3rd Normal Form (3NF) 
• Boyce CoddNormal Form (BCNF) 

When assessing relational databases and application architectures, database professionals typically check for the ACID (Atomicity Consistency Isolation Durability) principle. All four of these qualities must be present for a database to be regarded as robust. 

• Atomicity in SQL ensures that while running a block of SQL code, either all logical statements (CREATE, INSERT, ALTER, DELETE, etc.) are executed successfully or they are rolled back. 
• Consistency ensures that whatever operation is being performed in a database, it will not leave the database in an incomplete state. 
• Isolation applies to individual transactions. It ensures that one transaction does not access the results of the other transactions unless its execution is not completed successfully. 
• Durability ensures that the database will maintain track of pending changes so that committed transactions won't be lost and the server can recover from an abnormal termination. 

A trigger is a SQL process that starts doing an action if an object experiences an event such as an INSERT, DELETE, or UPDATE. The DBMS stores and maintains triggers. DBMS automatically fires a trigger as a result of a data alteration to the related table; a trigger cannot be called or executed directly. Triggers are primarily used to ensure improved performance for defining rules in a database and easier maintenance, as the triggers can be reused by each of the applications that use the database. 

The syntax for trigger in MySQL is – 

CREATE [ OR ALTER ] TRIGGER $trigger_name 
[ BEFORE | AFTER ] { INSERT | UPDATE | DELETE } 
ON $table_name FOR EACH ROW 
{ $trigger_body } 

For example, let us consider a ‘Sales’ table where we have the following columns: 

• Sale_ID 
• Timestamp 
• Product_ID 
• Quantity 
• Unit_Cost 
• Total_Cost 

For every entry that is made, we will add a SQL Trigger which will calculate the value for the ‘Total_Cost’ column using the ‘Quantity’ and ‘Unit_Cost’ columns and then update in the ‘Sales’ table. The SQL trigger is given by – 

CREATE TRIGGER trg_product_cost 
BEFORE INSERT 
ON Sales FOR EACH ROW 
SET Sales.Total_Cost = Sales.Quantity * Sales.Unit_Cost; 

Now every time an INSERT SQL statement is being run on the server, it will calculate the total cost for the respective entry and fill the value in the table along with other fields. 

SQL Injection is a technique that exposes your database to vulnerabilities by hackers. It is a way in which hackers try to run a SQL statement like ALTER, DELETE, TRUNCATE, etc that might cause your database to be compromised. The most common way of doing it is by placing a malicious SQL statement instead of a user input variable that the original SQL considers. There are different ways of protecting SQL injection within your code by the following standard practices to avoid such attacks. SQLAlchemy in Python is known to handle such malicious SQL injection attacks using SQL parameters.

1. One-to-One Relationship
   The relationship between two tables where each record form one table is associated with a single record from the other table.
   For example, an `employee_id` from the employee table will be associated to a single salary record in the salary table. 
2. One-to-Many Relationship
   The relationship between two tables where each record from one table is associated with more than one record from the other table.
   For example, an `employee_id` from the employee table can be associated to more than one `project_id` from the project table. 
3. Many-to-One Relationship
   The relationship between two tables where multiple records from one table is associated with a single record from the other table.
   For example, more than one `project_id` from the project table can be associated to a single `employee_id` from the employee table. 
4. Many-to-Many Relationship
   The relationship between two tables where multiple records from one table is associated with multiple records from the other table.
   For example, an `employee_id` from the employee table can register for multiple `event_id` from the events table and similarly one `event_id` can be registered by more than one `employee_id` from the employee table. 
5. Self-Referencing Relationship
   The relationship of a table with itself.
   For example, an employee table that contains information about the managers of each employee.

GRANT and REVOKE commands in SQL are used to enforce security in the database where multiple users are involved. The commands are a part of the Data Control Language and can provide only the required set of permissions to a user for running a query. 

For example, 

GRANT SELECT ON employee TO temp_user; 

The above command grants `temp_user` the permission to run SELECT queries on the employee table. 

REVOKE SELECT ON employee TO temp_user; 

The above command revokes the granted permission to `temp_user` to run SELECT queries on the employee table. 

GRANT ALL PRIVILEGES ON organization.* TO temp_user; 

The above command provides `temp_user` with the permission to run any query, modify or alter the database, or even add tables and records for all the tables present in the organization database. 

The GROUP BY clause is used in the SELECT statement. The aggregate functions that generate summary values for each of those sets are most frequently used with this clause. GROUP BY clause-containing queries are referred to as grouped queries because they only return a single result for each grouped item. One or more columns may be used to organize the groups. For instance, the GROUP BY query will be used to calculate the total salary for each department or to count the number of employees in each department. In the SELECT query, you must use aggregate functions like MAX(), MIN(), COUNT(), SUM(), AVG(), etc. For example, to get the department-wise average salary of employees where the average salary is more than 5 lakh, we can use –

SELECT 
Dept_ID, AVG(Salary) 
FROM employees 
GROUP BY Dept_ID 
HAVING AVG(Salary) > 500000; 

DELETE FROM $table_name 
WHERE row_id NOT IN ( 
SELECT MAX(row_id) AS max_row_id 
FROM $table_name 
GROUP BY $column_1, $column_2, … $column_n 
); 

The query will keep only the latest records in the table which represents the unique record based on the columns provided in the GROUP BY clause. 

We can achieve this using window partition functions. In MySQL, we can write the following query:

SELECT *
FROM (
    SELECT
        *,
        RANK() OVER (PARTITION BY Subject, ORDER BY Score DESC) rn
        FROM Student
    ) t
WHERE rn = 1;

While applying a filter to a string column, wildcards can be useful. They are used along with the LIKE operator. Wildcards help to substitute one or more characters in a string. We can define a string format using patterns, for example, email ID. They can be considered similar to regular expressions used in other programming languages. Following are some of the common wildcards used – 

In SQL, we can prepare code that may be saved and reused repeatedly, known as a stored procedure. So, if you frequently develop SQL queries, save them as stored procedures and just call them to run them. Additionally, you can send parameters to a stored procedure, allowing it to act based on the value(s) of the passed parameters. The syntax to create a SQL procedure is – 

CREATE PROCEDURE $procedure_name 
AS 
{ sql statements } 
GO; 

To execute or run the procedure – 

EXEC $procedure_name; 

For example, the following SQL stored procedure, ShowDepartmentWiseEmployees, takes in to account a department and displays the employee details belonging to only the respective department. 

CREATE PROCEDURE 
ShowDepartmentWiseEmployees @Department VARCHAR(128) 
AS 
SELECT * FROM Employees WHERE Department = @Department 
GO; 

After creating this procedure, we can use the following query to get the desired output – 

EXEC ShowDepartmentWiseEmployees @Department = ‘Human Resource’; 

The SELECT INTO statement is used to copy data from one table to another. The syntax is given by – 

SELECT * 
INTO $schema_name.$table_name 
FROM $schema_name.$table_name 
WHERE condition(s); 

SELECT t1.*
FROM Product t1  
    INNER JOIN (
        SELECT
            Category, MAX(Price) Max_Price
        FROM Product
        GROUP BY Category
    ) t2 ON t1.Category = t2.Category AND
    t1.Price = t2.Ma_Price;

The purpose of DDL language is to define the data structure of the database which includes – 

1. Create database, schema or table. 
2. Alter the tables and table fields. 
3. Drop the database, schema, table, and table fields. 
4. Truncate a table. 

The purpose of DML language is to perform any manipulation in the database which includes – 

1. Select the data from the database. 
2. Insert new records in the table. 
3. Update any existing record in the table. 
4. Delete the table records. 

The purpose of DCL language is to control user permissions in a database like – 

1. Grant permission to a user to access the database or revoke permission of an already existing user. 
2. Grant user-restricted permission like read-only, read and write by allowing the user to run only specific SQL commands on the database. 
3. Revoke any existing set of rules or permission granted to a user. 

The purpose of TCL language is to manage transactions in a database where COMMIT is used to permanently save changes made by the current transaction and ROLLBACK to undo the changes made by the current transaction. For example, the usage of TCL commands can be noticed in digital payments where the payee can reverse a transaction.

DELETE and TRUNCATE commands are used to delete records from a table without altering the table schema. 

• DELETE command can be used with the WHERE clause to mention a condition or filter while deleting these records. With TRUNCATE, all the records in the respective table get deleted. Filters cannot be provided with the TRUNCATE command as it does not allow the use of the WHERE clause.  
• Due to this behaviour, the DELETE command is a part of the Data Manipulation Language (DML) and the TRUNCATE command is categorized as Data Definition Language (DDL). 
• DELETE command maintains an entry for each record in the transaction log due to which it can be rolled back and is slower. In contrast, the TRUNCATE command maintains only the page deallocations in the transaction log which makes it a faster operation as compared to deleting and not possible to rollback. 
• For a database user to use the DELETE command requires to DELETE permission on the table whereas to use the TRUNCATE command requires ALTER permission on the table. 

Constraints in SQL are used to enforce some set of rules for the table fields and records while creating a table. This set of rules helps in automated data validation and avoids irrelevant data from being stored in these tables. These constraints can be categorised into column-level constraints and table-level constraints. The column-level constraints enforce the set of rules on a single column whereas the table-level constraints apply to the entire table. 

Some of the most used constraints are PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, AUTO_INCREMENT, etc. 

A NOT NULL constraint would prevent null entries in a certain column. For example, the birth date field in birth records cannot be null. A column can only contain values that fall under the scope of a CHECK constraint. A student percentage field, for instance, may only accept values in a specified range. If a column's value appears in another table row, a UNIQUE constraint will throw an error. This ensures that none of the values in a column are repeated. For example, we might not want to see any repeated value for the mobile number field in a table. 

CREATE TABLE knowledgehut_blogs ( 
blog_id TEXT PRIMARY KEY 
blog_title TEXT NOT NULL 
page_id INT UNIQUE 
); 

In the above example, the “knowledgehut_blogs” table consists of a PRIMARY KEY constraint to uniquely identify a record, a NOT NULL constraint to avoid blank entries for blog titles, and a UNIQUE constraint to avoid duplication of webpage IDs. 

Both WHERE and HAVING clauses can contain search conditions to further filter the rows selected. The major difference between both of them is that HAVING can be used with aggregate functions like AVG, SUM, MIN, MAX, etc. whereas WHERE does not support them. Also, the HAVING clause requires the GROUP BY clause to be present (due to aggregation) to work. WHERE condition is used to filter individual records based on one or more conditions.

String manipulation functions are useful to alter values in the fields containing string datatypes. Some of the common string manipulation functions in SQL and their examples are – 

1. CONCAT(string1, string2, … stringN) 

Used to concatenate two or more string values. 

SELECT CONCAT("Welcome ", "to ", "KnowledgeHut"); 

Output: Welcome to KnowledgeHut 

2. SUBSTR(string, start, length) 

Used to select a substring starting at the nth position in string up to a length x. 

SELECT SUBSTR('Welcome to KnowledgeHut', 12, 12); 

Output: KnowledgeHut 

3. LENGTH(string) 

Calculates the length of the string. 

SELECT LENGTH('KnowledgeHut'); 

Output: 12 

4. INSTR(search string, search term) 

Returns the index position of the specified character or word. 

SELECT INSTR('Welcome to KnowledgeHut', 'KnowledgeHut'); 

Output: 12 

5. LPAD(string, length, character) 

Adds padding to the left side of the string to fill up the required blank spaces up to the specified length of the string. 

SELECT LPAD('98345', 10, 'X'); 

Output: XXXXX98345 

6. RPAD(string, length, character) 

Adds padding to the right side of the string to fill up the required blank spaces to the specified length of the string. 

SELECT RPAD('98345', 10, 'X'); 

Output: 98345XXXXX 

7. REPLACE(string, replace, replace_with) 

Replaces the occurrence of a string of characters with a specified value. 

SELECT REPLACE('Welcome to KnowledgeHut', 'KnowledgeHut', 'upGrad KnowledgeHut'); 

Output: Welcome to upGrad KnowledgeHut 

CASE statements in SQL are similar to using conditional statements in other programming languages. It considers more than one condition and defines the action that needs to be performed when a condition is met. The syntax for the SQL case statements is –

CASE
    WHEN [condition_1] THEN [result_1]
    WHEN [condition_2] THEN [result_2]
    ...
    WHEN [condition_N] THEN [result_N]
    ELSE [result_default]
END;

Consider you are working with a student results table and grade the students based on their percentage, we can follow the below CASE statement –

SELECT
    Student_ID,
    Student_Name,
    Percentage,
CASE
    WHEN Percentage > 90 THEN ‘A’
    WHEN Percentage > 75 THEN ‘B’
    WHEN Percentage > 50 THEN ‘C’
    WHEN Percentage > 35 THEN ‘D’
    ELSE ‘Fail’
END AS Grade
FROM Results;