Q1. What is a primary key?

Primary keys are unique values used to identify records. Think of this as a
unique identification card such as an Aadhar Card, or PAN Card of the
data. It cannot have null values and must have a unique value just like
your PAN Card number, or Aadhar Number. Only one primary key is
allowed in one table (one Aadhar, or PAN Card per person). It can have
one or more fields making the primary key a composite key.

Q2. Explain the types of SQL joins.

There are four different types of SQL Joins: (Inner) Join: An inner join is
used to retrieve the records that have matching values in tables involved
in the join. It combines rows from two tables based on a related column
and returns only the matching record. Inner Join is mostly used to join
queries.
1SELECT * FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B;

Left (Outer) Join: The use of left join is to retrieve all the records or rows
from the left and the matched ones from the right.
1SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col;

Right (Outer) Join: The use of Right join is to retrieve all the records or
rows from the right and the matched ones from the left.
1SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col;

Full (Outer) Join: The use of Full join is to retrieve the records that have a
match either in the left table or the right table.
1SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col;

Q3. What is an index?

The indexes in a database are like the index in a book. They are the ones
to assist you in getting the info at a fast pace. For example, take a library
where there is no catalogue, you would have to take a long time to find a
book. The index is a tool that allows you to find books faster. Also, in the
case of SQL, the index creation is the reason of the faster searches by
means of the index which is the roadmap to the relevant data.

Syntax:
1CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

Q4. Explain the types of indexes.

Primary Index: Think of it as the main key to a building. It is unique and

provides entrance into all rooms. In the same way that a primary index in
a database uniquely identifies each row and speeds up searches.

Secondary Index: Imagine this like one departmental index in a

noticeably big library. Whereas the primary index leads you to the book,
the secondary index helps narrow down your search within specific
sections, so you find it quicker.

Clustered Index: Picture this as organizing books on shelves with

alphabetical titles. Consequently, it organizes data physically in a table
that matches the sequence of the index thereby facilitating easy retrieval
just like finding them on shelves well arranged.

Non-Clustered Index: Just like card catalogues, a clustered index sorts

out data on the shelf while a non-clustered index gives another lookup for
some information that can be narrowed down further for ease in
searching.

Q5. What are SQL operators?

SQL operators are the special keywords or characters that perform

specific operations. They are also used in SQL queries. These operators
can be used within the WHERE clause of SQL commands. Based on the
specified condition, SQL operators filter the data.

The SQL operators can be categorized into the following types:

 Arithmetic Operators:For mathematical operations on

numerical data
o addition (+)
o subtraction (-)
o multiplication (*)
o division (/)
o remainder/modulus (%)
 Logical Operators: For evaluating the expressions and
returning results in True or False
o ALL
o AND
o ANY
o ISNULL
o EXISTS
o BETWEEN
o IN
o LIKE
o NOT
o OR
o UNIQUE
 Comparison Operators: For comparisons of two values
and checking whether they are the same or not
o equal to (=)
o not equal to (!= or <>)
o less than (<),
o greater than (>;)
o less than or equal to (&<=)
o greater than or equal to (>=)
 o not less than (!<)
o not greater than (!>)
 Bitwise Operators: For bit manipulations between two
expressions of integer type. It first performs the
conversion of integers into binary bits and then applied
operators
o AND (& symbol)
o OR (|, ^)
o NOT (~)
 Compound Operators: For operations on a variable
before setting the variable’s result to the operation’s
result
o Add equals (+=)
o subtract equals (-=)
o multiply equals (*=)
o divide equals (/=)
o modulo equals (%=)
 String Operators: For concatenation and pattern
matching of strings
o + (String concatenation)
o += (String concatenation assignment)
o % (Wildcard)
o [] (Character(s) matches)
o [^] (Character(s) not to match)
o _ (Wildcard match one character)

 Q6. What is a data warehouse?

 A data warehouse is a large store of accumulated data, from a wide
range of sources, within an organization. The data helps drive
business decisions.
 Q7. How would you find the second highest
salary from the following table?
 

 Code:
1select * from employee
2select max(e_salary) from employee
3where e_salary not in (select max(e_salary)
4from employee)

 Output:

Q8. What do you understand about a character

manipulation function?

Character manipulation functions are used for the manipulation of

character data types.
Some of the character manipulation functions are as follows:

UPPER: It returns the string in uppercase.

Syntax:
1UPPER(‘string’)

Example:
1SELECT UPPER(‘demo string’) from String;

Output:
1DEMO STRING
LOWER: It returns the string in lowercase.

Syntax:
1LOWER(‘STRING’)

Example:
1SELECT LOWER (‘DEMO STRING’) from String

Output:
1demo string

INITCAP: It converts the first letter of the string to uppercase and retains
others in lowercase.

Syntax:
1Initcap(‘sTRING’)

Example:
1SELECT Initcap(‘dATASET’) from String

Output:
1Dataset

CONCAT: It is used to concatenate two strings.

Syntax:
1CONCAT(‘str1’,’str2’)

Example:
1SELECT CONCAT(‘Data’,’Science’) from String

Output:
1Data Science

LENGTH: It is used to get the length of a string.

Syntax:
1LENGTH(‘String’)

Example:
 1SELECT LENGTH(‘Hello World’) from String
1Output: 11

Q9. Which command is used to find out the SQL

Server version?

The following command is used to identify the version of SQL Server:

1Select SERVERPROPERTY('productversion')

Q10. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null
value.

Syntax:
1COALESCE(val1,val2,val3,……,nth val)

Example:
1SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output:
11

Q11. Explain Inner Join with an example.

Inner Join gives us those records that have matching values in two tables.

Let us assume that we have two tables: Table A and Table B. When we
apply Inner Join to these two tables, we will get only records common to
both Table A and Table B.

Syntax:
1SELECT columns
2FROM table1
3INNER JOIN table2
4ON table1.column_x=table2.column_y;
Example:
1select * from employee
2select * from department

Output:

Now, we will apply Inner Join to both these tables, where the e_dept
column in the employee table is equal to the d_name column of the
department table.

Syntax:
1select employee.e_name, employee.e_dept, department.d_name,
2department.d_location from employee inner join department
3on
4employee.e_dept=department.d_name

Output:

After applying Inner Join, we have only those records where the
departments match in both tables. As we can see, the matched
departments are Support, Analytics, and Sales.

Q12. What are the types of views in SQL?

In SQL, the views are classified into four types. They are the following:
  Simple View: It is a view based on a single table and does not
have a GROUP BY clause or other features.
 Complex View: It is a view built from several tables and
includes a GROUP BY clause as well as functions.
 Inline View: It is a view built on a subquery in the FROM clause,
which provides a temporary table and simplifies a complicated
query.
 Materialized View: It is a view that saves both the definition
and the details. It builds data replicas by physically preserving
them.

 Q13. What is the difference between Union

and Union All operators?
 The union operator is used to combine the result set of two or more
select statements. For example, the first select statement returns
the fish shown in Image A, and the second statement returns the
fish shown in Image B. The Union operator will then return the result
of the two select statements as shown in Image A U B. If there is a
record present in both tables, then we will get only one of them in
the final result.


 Syntax:
1SELECT column_list FROM table1

 Union:
1SELECT column_list FROM table2
  Now, we will execute it in the SQL Server.
 These are the two tables in which we will use the Union operator.


1select * from student_details1

 Union:
1select * from student_details2

 Output:

 The Union All operator gives all the records from both tables
including the duplicates.


 Let us implement it in the SQL Server.

 Syntax:
1select * from student_details1
  Union All:
1select * from student_details2

 Output:

Q14. Can you identify the employee who has

the third-highest salary from the given
employee table (with salary-related data)?

Consider the following employee table. In the table, Sabid has the third-
highest salary (60,000).
Name Salary
Tarun 70,000
Sabid 60,000
Adarsh 30,000
Vaibhav 80,000

Below is a simple query to find out which employee who has the third-
highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are
used to obtain the increasing integer value by imposing the ORDER BY
clause in the SELECT statement, based on the ordering of the rows. The
ORDER BY clause is necessary when the RANK, DENSE RANK, or ROW
NUMBER functions are used. On the other hand, the PARTITION BY clause
is optional.
WITH CTE AS
1(
2SELECT Name, Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC)
3FROM EMPLOYEE
4)
5SELECT Name, Salary FROM CTE WHERE RN =3
Q15. How would you find the second-highest
salary in a table?

There are several ways to find the second highest salary in a table.

1. Using the ORDER BY, LIMIT, and OFFSET Clauses:

1SELECT distinct(salary) from table_name ORDER BY salary DESC LIMIT 1 OFFSET 1;

2. Using Subquery:

1
2
3SELECT MAX(salary) AS salary
4FROM table_name WHERE salary <> (SELECT MAX(salary)
5FROM table_name);
6Where,
7table_name: your table name
8salary: salary column present in your table

Q16. What is an effective way to prevent SQL

injection in your queries?

The effective way to prevent SQL injection attacks is through input

validation and parameterized queries, which include prepared statements.
The developer can sanitize all the inputs, not only just the web form input,
because the application code should never use the input directly.

Q17. What is the significance of an index in a

database, and how do you use it?

Database indexing helps the DBMS find the specific rows in a table very
quickly. The most common database index is the B-tree Index. A B-tree
index is a situation where the data has few distinct values, such as name,
dates or state codes.
Q18. How will you optimize a slow-moving SQL
query? What are some of the optimization
techniques?

We can optimize a slow-moving SQL query by using indexing in the DBMS

to find the specific rows in a table very quickly.

There are several optimization techniques:

1.
1. Indexing
2. Using Distinct
3. Having and Where clauses
4. Avoiding correlated subqueries
5. Limit
6. Column statistics

Q19. What do you know about the stuff()

function?

The stuff() function deletes a part of the string and then inserts another
part into the string, starting at a specified position.

Syntax:
1STUFF(String1, Position, Length, String2)

Here, String1 is the one that will be overwritten. The position indicates the
starting location for overwriting the string. Length is the length of the
substitute string, and String2 is the string that will overwrite String1.

Example:
1select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:
1Python Tutorial

Q20. What is a stored procedure? Give an

example.

A stored procedure is a prepared SQL code that can be saved and reused.
In other words, we can consider a stored procedure to be a function
consisting of many SQL statements to access the database system. We
can consolidate several SQL statements into a stored procedure and
execute them whenever and wherever required.

A stored procedure can be used as a means of modular programming, i.e.,

we can create a stored procedure once, store it, and call it multiple times
as required. This also supports faster execution when compared to
executing multiple queries.

Syntax:
1CREATE PROCEDURE procedure_name
2AS
3Sql_statement
4GO;
5To execute we will use this:
6EXEC procedure_name

Example:

We are going to create a stored procedure that will help us extract the
age of the employees.
1create procedure employee_age
2as
3select e_age from employee
4go

Now, we will execute it.

1exec employee_age

Output:
Q21. What is the use of the INTERSECT
operator?

The INTERSECT operator helps combine two select statements and returns
only those records that are common to both select statements. After we
get Table A and Table B over here, and if we apply the INTERSECT
operator on these two tables, then we will get only those records that are
common to the result of the select statements of these two tables.

Syntax:
1SELECT column_list FROM table1
2INTERSECT
3SELECT column_list FROM table2

Now, let us take a look at an example of the INTERSECT operator.

1select * from student_details1
2select * from student_details1
Output:

1select * from student_details1

2intersect
3select * from student_details2

Output:

Q22. Describe how to delete duplicate rows

using a single statement but without any table
creation.

Let us create an employee table where the column names are ID, NAME,
DEPARTMENT, and EMAIL. Below are the SQL scripts for generating the
sample data:
1CREATE TABLE EMPLOYEE
2(
3ID INT,
4NAME Varchar(100),
5DEPARTMENT INT,
6EMAIL Varchar(100)
7)
8INSERT INTO EMPLOYEE VALUES (1,'Tarun',101,'[email protected]')
INSERT INTO EMPLOYEE VALUES (2,'Sabid',102,'[email protected]')
9
INSERT INTO EMPLOYEE VALUES (3,'Adarsh',103,'[email protected]')
1INSERT INTO EMPLOYEE VALUES (4,'Vaibhav',104,'[email protected]')
0–These are the duplicate rows:
1INSERT INTO EMPLOYEE VALUES (5,'Tarun',101,'[email protected]')
1INSERT INTO EMPLOYEE VALUES (6,'Sabid',102,'[email protected]')
1
2
 1
3
1
4

We can see the duplicate rows in the above table.

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name
1AND e1.id > e2.id

The SQL query above will delete the rows, where the name fields are
duplicated, and it will retain only those unique rows in which the names
are unique and the ID fields are the lowest, i.e., the rows with IDs 5 and 6
are deleted, while the rows with IDs 1 and 2 are retained.

Q23. What is the difference between the

DELETE and TRUNCATE commands?

 DELETE:This query is used to delete or remove one or more

existing tables.
 TRUNCATE:This statement deletes all the data inside a table.
The differences between DELETE and TRUNCATE commands are the
following:

 TRUNCATE is a DDL command, and DELETE is a DML command.

 With TRUNCATE, we cannot really execute and trigger, while
with DELETE, we can accomplish a trigger.
 If a table is referenced by foreign key constraints, then
TRUNCATE will not work. So, if we have a foreign key, we have
to use the DELETE command.

The syntax for the DELETE command is as follows:

1DELETE FROM table_name
2[WHERE condition];

Example:
1select * from stu

Output:

1delete from stu where s_name=’Bob’

Output:
The syntax for the TRUNCATE command:
1TRUNCATE TABLE
2Table_name;

Example:
1select * from stu1

Output:

1truncate table stu1

Output:

This deletes all the records from a table.

Q24. What is the difference between the DROP

and TRUNCATE commands?

If a table is dropped, all things associated with that table are dropped as
well. This includes the relationships defined on the table with other tables,
access privileges, and grants that the table has, as well as the integrity
checks and constraints.

To create and use the table again in its original form, all the elements
associated with the table need to be redefined.

However, if a table is truncated, there are no such problems as mentioned

above. The table retains its original structure.
Q25. What are some common clauses used with
SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most
commonly used clauses with SELECT queries are as follows:

 FROM
The FROM clause defines the tables and views from which data
can be interpreted. The tables and views listed must exist at the
time the question is given.
 WHERE
The WHERE clause defines the parameters that are used to limit
the contents of the results table. You can test for basic
relationships or for relationships between a column and a series
of columns using subselects.
 GROUP BY
The GROUP BY clause is commonly used for aggregate functions
to produce a single outcome row for each set of unique values in
a set of columns or expressions.
 ORDER BY
The ORDER BY clause helps in choosing the columns on which
the table’s result should be sorted.
 HAVING
The HAVING clause filters the results of the GROUP BY clause by
using an aggregate function.

Q26. What is the difference between BETWEEN

and IN operators in SQL?
The BETWEEN operator is employed to identify rows that fall within a
specified range of values, encompassing numerical, textual, or date
values. It returns the count of values that exist between the two defined
boundaries.

On the other hand, the IN operator serves as a condition operator utilized

for searching values within a predetermined range. When multiple values
are available for selection, the IN operator is utilized.

Check out how to use IN and BETWEEN Operators in SQL with

examples.

Q27. What is the difference between HAVING

and WHERE clauses?

The main difference between the ‘HAVING’ and ‘WHERE’ clauses in SQL is
that the ‘WHERE’ clause operates on individual rows of data, while the
‘HAVING’ clause is used to filter aggregated data. The ‘WHERE’
clause cannot be used with aggregate functions, whereas the ‘HAVING’
clause specifically filters results based on aggregate conditions.

Let us consider the employee table below.

Name Department Salary
Tarun Production 50,000
Tarun Testing 60,000
Sabid Marketing 70,000
Adarsh Production 80,000
Vaibhav Testing 90,000

The following will select the data on a row-by-row basis:

1SELECT Name, Salary FROM Employee WHERE Salary >=50000

Output:

Name Salary
Tarun 50,000
Tarun 60,000
Sabid 70,000
Adarsh 80,000
Vaibhav 90,000

The HAVING clause, on the other hand, operates on the aggregated

results.

SELECT Department, SUM(Salary) AS total

1FROM Employee GROUP BY Department

Output:

Department Total
Marketing 70,000
Production 130,000
Testing 150,000

Now, let us see the output when we apply HAVING to the above query.

SELECT Department, SUM(Salary) AS total FROM Employee

1GROUP BY Department HAVING SUM(Salary)>70000

Output:

Department Total
Production 130,000
Testing 150,000

Q28. How will you update the prices in a

product column by increasing 5% of the prices
in each row?

We can update the prices of the product columns by using the UPDATE
method, which is part of the DML.
1UPDATE table_name SET price = price*1.05;
Where,
table_name: your table name
price: price column present in your table

With this code, we can easily modify the price of each product by 5%.

Q29. How will you calculate the average price of

products in each category?

To calculate the average price of products in each category, we can use

the aggregate function (AVG) with the price column and group it by the
category column.
SELECT category, AVG(price) as average_price
1FROM table_name GROUP BY category;

Where,
table_name: table name
category: category column in your table
price: price column in your table

Q30. How will you calculate the total sales in

each category of a product sales table?

To calculate the total sales in each category of a product sales table, we

can use the aggregate function (SUM) with the sales amount column and
group it by the category column.
SELECT category, SUM(sales_amount) as total_sales
1FROM sales GROUP BY category;

Where,
sales: table name
category: category column in your table
sales_amount: sales_amount column in your table
Q31. How will you find the IDs or details where
there have been no entries in terms of sales?

To find the IDs or details where there have been no entries in terms of
sales, we can use the LEFT JOIN or NOT EXISTS clause.

Assume we have two tables: ‘product’ with product details and ‘sales’ with
sales data.

Left Joins:
SELECT p.product_id, p.product_name FROM product p
1LEFT JOIN sales s on p.product_id = s.product_id WHERE s.product_id is NULL;

Where,
p.product_id: product id in the product table
p.product_name: product name in the product table
s.product_id: product id in the sales table

Here, the WHERE s.product_id is NULL condition helps us filter out the
rows where a match in the sales table is not found.

Not Exists:
SELECT p.product_id, p.product_name FROM products p
1WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id );
2
3Where,
4p.product_id: product id in the product table
5p.product_name: product name in the product table
6s.product_id: product id in the sales table

Q32. Suppose there is a database where

information about the employees in various
verticals is stored. Your task is to find the
average salary of each vertical and the highest
salary among the lot.
To find the average salary of each vertical and the highest salary among
the employees, we can use the group by clause along with the aggregate
functions (AVG and MAX).
SELECT vertical, AVG(salary) as average_salary, MAX(salary) as highest_salary
FROM
1employees GROUP BY vertical;

Where,
vertical: column that you want to group
salary: column in the table
employees: table name

Q33. Write an SQL query to find the month-on-

month sales of a specific product in a store.

To calculate the month-on-month sales of a specific product in a store, we

can use a combination of date functions and aggregate functions.
SELECT EXTRACT(YEAR_MONTH FROM sale_date) AS year_month, SUM(quantity_sold)
AS total_sales FROM sales
WHERE product_id = 'your_product_id'
1GROUP BY year_month ORDER BY year_month;

Where,
sale_date: date of the sales column
quantity_sold: number of quantity sold column
sales: table name
product_id: Id of the product column
your_product_id: pass the product ID for which you want to check.

Q34. Suppose in an organization, employees

are mapped under managers. Write a SQL query
that will fetch you the managers and employees
working under them.
To fetch the managers and employees working under them, we can use a
self-join to fetch the managers and the employees working under them.
SELECT M.manager_id AS manager_id, M.manager_name AS manager_name,
E.employee_id AS employee_id, E.employee_name AS employee_name
FROM
employees E JOIN employees M ON E.manager_id = M.employee_id
1ORDER BY M.manager_id, E.employee_id;

Where,
manager_id: manager id column
manager_name: manager name column
employee_id: employee id column
employee_name: employee name column

Q35. In a store inventory, your task is to fetch

the total quantity of the top product purchased
by the customers.

To fetch the total quantity of the top product purchased by the customers,
we can use a group by clause along with the limit in descending order.
SELECT product_id, SUM(quantity_purchased) AS total_quantity_purchased FROM
1 purchases GROUP BY product_id ORDER BY total_quantity_purchased DESC LIMIT 1;

Where,
product_id: product id column
quantity_purchased: column having the no. of the quantity purchased
purchases: table name

Q36. What is SQL JOINS?

The SQL JOIN component joins rows from one or more tables in a relational
database. Create sets that can be stored in tabular form or used routinely.
JOIN is to combine columns from one table or multiple tables using the
same value.
There are different types of JOINS in SQL, which are the following:

 INNER JOIN – An INNER JOIN is used to return records of the

same value in two tables.
 LEFT JOIN – LEFT JOIN is used to join all the rows in the left
table with matching rows in the right table.
 RIGHT JOIN – RIGHT JOIN is used to join all the rows in the right
table with the corresponding rows in the left table.
 FULL JOIN – A FULL JOIN is used to return all records from two
tables if there are matching records in each table.
 SELF JOIN – A SELF JOIN is a join used to join a table to itself.
SELF JOINS treats one table as two tables.
 CARTESIAN JOIN – CARTESIAN Integral is used to multiply the
number of rows in the first table by the number of rows in the
second table. It is also called CROSS JOIN.

Q37. What is the difference between INNER JOIN

and SELF JOIN?

The most important difference between INNER and SELF JOIN is:

 INNER JOIN is used to return the records which are present in

both tables. Whereas, in SELF JOIN, a table is joined to itself.
 A SELF JOIN is a type of INNER JOIN.

Q38. State the difference between the RIGHT

JOIN and the LEFT JOIN.

 Both LEFT JOIN and RIGHT JOIN do the same thing: they return the
result of a query that contains all the records in the table. The only
difference is that the left view shows all the records in the left table
of the query, and the right view shows all the records in the right
table.
Q39. Is SELF JOIN an INNER JOIN or OUTER JOIN?

 The SELF JOIN can be an INNER JOIN, OUTER JOIN, or can also be
CROSS JOIN. Tables are automatically linked based on columns that
contain duplicate data in multiple rows.

Q39. What is the difference between FULL JOIN

and CARTESIAN JOIN?

 The combination of the LEFT and the RIGHT OUTER JOIN is called a
FULL JOIN.
 If the ON condition cannot be satisfied, it returns all rows in both
tables that match the WHERE clause with a NULL value.
 Whereas, a CARTESIAN or CROSS JOIN creates a cross-product
between the two tables. For all rows, it returns a possible sequence.

Q40. What is NATURAL JOIN?

 A NATURAL JOIN is used to create an absolute JOIN clause based on

common attribute values in two tables. Shared variables are
variables that are named in both tables. NATURAL JOINS do not need
any equivalence operator like EQUI JOIN.
 Example: In the below two tables the Customer and Shop tables
have a common field ‘CustID’. So, in the case of NATURAL JOINS, we
don’t need to mention the On clause over the ‘CustNo’ field of both
tables.
CustI Name Area name
D
890 AVI CP
678 NIO MG

 Customer table
ShopID SName CustID
23 ABY 678
31 BCY 890
  Shop table


Q41. What is an EQUI JOIN?

 An EQUI JOIN is a type of join operation in a database that combines

rows from two or more tables based on a matching condition using
the equality operator (=). It is used to retrieve data where values in
specified columns are equal.
 Here is an example of the syntax for an EQUI JOIN:
1SELECT column_name(s)
2FROM table1
3
4JOIN table2
5
6ON table1.column_name = table2.column_name;

 Know the most common methods for executing function in

sql by exploring our blog on how to run function in SQL!

Q42. Can you join a table to itself in SQL?

 Yes, in SQL, it is possible to join a table to itself, which is known as a

self-join.
 By using table aliases, you can treat the same table as two separate
entities and perform a join operation on them based on specified
conditions. Self-joins are used when you need to retrieve
information by comparing rows within the same table.
 Wish to get certified in SQL! Learn SQL from top SQL experts
and excel in your career with intellipaat’s SQL certification
course.
Q43. Create tables- Customer detail and
Product detail.

Fig. 1.1
Fig. 1.2

So, based on these two tables, let’s look into some of the
questions related to SQL JOINS and queries.

Q44. Get customer name and product name

order by first name from
1SELECT a.first_name,b.Product_name
2FROM [customer] A
3INNER JOIN [product] B
4ON A.customer_id = B.customer_id
5ORDER BY a.first_name

Q45. Get customer name, and product name

order by firstname from
1SELECT a.first_name, b.Product_name FROM [customer] A
2LEFT OUTER JOIN [Product] B
3ON A.customer_id = B.customer_id
4ORDER BY a.first_name
Q46. Get the Customer name and product name
order by firstname from
1SELECT a.First_Name, ISNULL(b.Product_name,'-No Project Assigned')
2FROM customer A LEFT OUTER JOIN product B
3ON A.customer_id = B.customer_id ORDER BY a.first_name

Check the Difference between MongoDB and MySQL

through MongoDB vs MySQL blog.

Q47. Get all product names even if they have

not matched any Customer id, in the left table,
order by firstname from
1SELECT a.first_name,b.Product_name
2FROM [customer] A RIGHT OUTER JOIN [product] B
3ON a.customer_id = b.customer_id ORDER BY a.first_name

Learn the basics of SQL through SQL Tutorial.

Q48. Get the complete record(Customer name,
product name) from both
tables([CustomerDetail],[ProductDetail]), if no
match is found in any table then show NULL.
1SELECT a.first_name,b.Product_name FROM [customer] A
2FULL OUTER JOIN [product] B
3ON a.customer_id = b.customer_id
4ORDER BY a.first_name

Q49. Write a query to find out the Customer

name who has not been assigned any product,
and display
1SELECT a.first_name, ISNULL(b.Product_name,'-No Project Assigned') AS [Product]
2FROM [customer] A
3LEFT OUTER JOIN [product] B
4ON a.customer_id = b.customer_id
5WHERE b.Product_name IS NULL

Q50. Write a query to find out the product

name which is not assigned to any
employee( tables:- [CustomerDetail],
[ProductDetail]).
1SELECT b.Product_name FROM [customer] A
2RIGHT OUTER JOIN [product] B
3ON a.customer_id = b.customer_id
4WHERE a.first_name IS NULL
Q51. Write down the query to fetch
CustomerName & Product who has assigned
more than one project.
Select c.customer_id, c.first_name, p.Product_name from [customer] c
1INNER JOIN [product] P
2ON c.customer_id = p.customer_id
3WHERE c.customer_id IN
4(SELECT customer_id FROM [product] GROUP BY customer_id
5HAVING COUNT(*)>1 )

*The output will not come as there is no duplicate record in the product
table.

Intellipaat also provides SQL course in Bangalore. Enroll now!

Q52. Write down the query to fetch

ProductName on which more than one customer
is working along with CustomerName.
Select P.Product_name, c.first_name from product P
1INNER JOIN customer c
2on p.customer_id = c.customer_id

where P.Product_name in(select Product_name from product group by

Product_name having COUNT(1)>1)

*The output will not come as there is no duplicate record in the product
table.

Q53. Why use the HAVING clause in MySQL?

Use HAVING to filter data after grouping it with GROUP BY. It works like
WHERE but applies to groups, not individual rows.
Q54. How do you find the difference in time
between the two dates?

Use TIMESTAMPDIFF to calculate the time gap. For example, SELECT

TIMESTAMPDIFF(DAY, ‘2020-01-01’, ‘2020-01-31’); tells you the days
between dates.

Q55. In PL/SQL, what are the differences

between Stored Procedure and Stored
Function?

The key differences between stored procedure and stored

function are:

 Returning the value in a stored procedure is optional, while

returning the value in a stored function is required.
 A stored procedure can have both input and output parameters,
while a stored function can only have either an input parameter
or an output parameter.
 Exception handling is possible in a stored procedure, whereas it
is not possible in a stored function.

Q56. What aggregate functions do you know?

 AVG() – returns the average value

 SUM() – returns the sum of values
 MIN() – returns the minimum value
 MAX() – returns the maximum value
 COUNT() – returns the number of rows, including those with null values
 FIRST() – returns the first value from a column
 LAST()– returns the last value from a column

Q57. What scalar functions do you know?

  LEN()(in other SQL flavors – LENGTH()) – returns the length of a
string, including the blank spaces
 (in other SQL flavors –
UCASE() UPPER()) – returns a string
converted to the upper case
 (in other SQL flavors –
LCASE() LOWER()) – returns a string
converted to the lower case
 INITCAP()
– returns a string converted to the title case (i.e.,
each word of the string starts from a capital letter)
 (in other SQL flavors –
MID() SUBSTR()) – extracts a substring
from a string
 – returns the numerical value rounded to a specified
ROUND()
number of decimals
 NOW() – returns the current date and time

Q58. What are case manipulation functions? Give some

examples.
Case manipulation functions represent a subset of character functions,
and they're used to change the case of the text data. With these
functions, we can convert the data into the upper, lower, or title case.

 (in other SQL flavors –

UCASE() UPPER()) – returns a string
converted to the upper case
 (in other SQL flavors –
LCASE() LOWER()) – returns a string
converted to the lower case
 – returns a string converted to the title case (i.e.,
INITCAP()
each word of the string starts from a capital letter)

Q59. What are character manipulation functions? Give

some examples.
Character manipulation functions represent a subset of character
functions, and they're used to modify the text data.

 – joins two or more string values appending the

CONCAT()
second string to the end of the first one
 SUBSTR()– returns a part of a string satisfying the provided
start and end points
 LENGTH() (in other SQL flavors – LEN()) – returns the length of a
string, including the blank spaces
  – replaces all occurrences of a defined substring in a
REPLACE()
provided string with another substring
 – returns the numeric position of a defined substring in
INSTR()
a provided string
 LPAD()and RPAD() – return the padding of the left-side/right-
side character for right-justified/left-justified value
 – removes all the defined characters, as well as white
TRIM()
spaces, from the left, right, or both ends of a provided string

Q60. What set operators do you know?

 UNION– returns the records obtained by at least one of two

queries (excluding duplicates)
 UNION ALL– returns the records obtained by at least one of two
queries (including duplicates)
 INTERSECT – returns the records obtained by both queries
 EXCEPT(called MINUS in MySQL and Oracle) – returns only the
records obtained by the first query but not the second one

Q61. What is the order of appearance of the common

statements in the SELECT query?
 SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER
BY – LIMIT

Q62. In which order the interpreter executes the common

statements in the SELECT query?
 FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER
BY – LIMIT

Q63. What is a view, and why use it?

 A virtual table containing a subset of data retrieved from one or
more database tables (or other views). Views take very little space,
simplify complex queries, limit access to the data for security
reasons, enable data independence, and summarize data from
multiple tables.

Q64. Can we create a view based on another view?

  Yes. This is also known as nested views. However, we should avoid
nesting multiple views since the code becomes difficult to read and
debug.

Q65. Can we still use a view if the original table is

deleted?
 No. Any views based on that table will become invalid after deleting
the base table. If we try to use such a view anyway, we'll receive an
error message.

Q66. What is the difference between clustered and non-

clustered indexes?
 While a clustered index defines the physical order of records of
a table and performs data searching based on the key values, a
non-clustered index keeps the order of records that doesn't
match the physical order of the actual data on the disk. A table
can have only one clustered index but many non-clustered ones.

Q67. What is the CASE() function?

 The way to implement the if-then-else logic in SQL. This function
sequentially checks the provided conditions in the WHEN clauses and
returns the value from the corresponding THEN clause when the first
condition is satisfied. If none of the conditions is satisfied, the
function returns the value from the ELSE clause in case it's provided,
otherwise, it returns NULL. The syntax is:
 CASE
 WHEN condition_1 THEN value_1
 WHEN condition_2 THEN value_2
 WHEN condition_3 THEN value_3
 ...
 ELSE value
 END;
 POWERED BY DATALAB

Q68. What is the difference between

the DELETE and TRUNCATE statements?
 DELETE is a reversible DML (Data Manipulation Language) command
used to delete one or more rows from a table based on the
conditions specified in the WHERE clause. Instead, TRUNCATE is an
irreversible DDL (Data Definition Language) command used to
delete all rows from a table. DELETE works slower than TRUNCATE.
Also, we can't use the TRUNCATE statement for a table containing a
foreign key.
Q69. What is the difference between
the DROP and TRUNCATE statements?
 DROP deletes a table from the database completely, including the
table structure and all the associated constraints, relationships with
other tables, and access privileges. TRUNCATE deletes all rows from a
table without affecting the table structure and
constraints. DROP works slower than TRUNCATE. Both are irreversible
DDL (Data Definition Language) commands.

Q70. What is the difference between the HAVING and

WHERE statements?
 The first one works on aggregated data after they are grouped,
while the second one checks each row individually. If both
statements are present in a query, they appear in the following
order: WHERE – GROUP BY – HAVING. The SQL engine interprets them
also in the same order.

Q71. How to find the last id in a table?

Using the MAX() function. Otherwise, in many SQL versions, we can use the
following syntax:

SELECT id

FROM table_name

ORDER BY id DESC

LIMIT 1;

Q72. How to select random rows from a table?

Using the RAND() function in combination with ORDER BY and LIMIT. In some
SQL flavors, such as PostgreSQL, it's called RANDOM(). For example, the
following code will return five random rows from a table in MySQL:

SELECT * FROM table_name

ORDER BY RAND()

LIMIT 5;