SQL Interview Questions.

Important questions that are frequently asked during interviews.

1. State the difference between Where and Having in SQL

2. How is Drop different from Truncate ?

The Drop command is used to completely remove a table or a database management system(DBMS).
Be extremely cautious when using DROP as it irreversibly deletes the table or database and its contents.

3.Find the lowest salary for each department .

To find the lowest value in a table u can use the (MIN) aggregate function in SQL

SELECT dept,min(salary) as lowest_salary 
FROM employees
GROUP by dept;

4. Write an SQL query to fetch the unique values of the department and print their length.

First, u get the unique values of the department from the table of employees

SELECT DISTINCT dept,length(dept) as dept_length
FROM employees

5. What is the use of the DATEDIFF function in SQL?

The DATEDIFF function can be useful in various scenarios, such as calculating the age of a person, finding the duration between two events, or determining the time between two timestamps.
The DATEDIFF function returns the number of days between two date, datetime or timestamp values.
General syntax

DATEDIFF(datepart,startdate, enddate) 

Where;
date part specifies the unit of time to use for the
calculation(eg day, month, year)
start date - the starting date,
end date - the ending date,

commonly used dateparts include;
day - Difference in days, month - Difference in months,
year - Difference in years, Hour -Difference in hours
minute - Difference in minutes, second Difference in seconds.

6. Display the details of the employees for all the departments except marketing.

Here we will give a condition using the where clause,
SELECT *from Employees
WHERE dept<> 'marketing'

7. Find the employee with the 3rd Highest salary from the table.

SELECT *FROM (select * FROM EMPLOYEES ORED BY SALARY des limit 3) as Toder by salary 

8. Write the SQL query to fetch all the duplicate rows in the table.

When data is duplicated, it can lead to inaccurate results in analyses, skewed reports, and ultimately, misinformed business decisions.
Duplicates in SQL databases are a crucial concern, particularly for quality control, rationality checks, and data validation.

Using the** GROUP BY** and** HAVING** clauses we can show the duplicates in the table.
The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions.

9. Given a string how will you extract four characters starting from the second position?

SUBSTRING() is a function that enables us to extract subparts of strings, which are known as substrings.
general syntax includes;

SUBSTRING(string, start, length)

The string parameter is used to specify the element we want to extract the characters.
The start parameter is used to define the starting position of the string. If it is a positive number, the function starts from the beginning of the string, and if it is a negative number, the function starts from the end of the string.

SELECT SUBSTRING(your_column_name, 2, 4) AS extracted_chars
FROM your_table_name;

10. Write an SQL query to print one row twice in the results
from the table.

We can achieve the following using SQL UNION. - the union operator is used to combine the result-set or more select statements.

• Every SELECT statement within UNION must have the same number of columns.

• The columns must also have similar data types.

• The columns in every SELECT statement must also be in the same order.

• It does not remove duplicates.

11. What is a check constraint in SQL?

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

12. Find the top 5 countries with the highest freight charges.
This question has many ways u can handle it;
Basic syntax

SELECT TOP 5* FROM table_name

SELECT TOP 5* FROM table_name
ORDER BY ColumnName;

• This query orders the result set by the specified column (ColumnName). The default order is ascending. If you want descending order, you can use ORDER BY ColumnName DESC.

-

Filtering with the WHERE clause:

You can combine TOP with the WHERE clause to retrieve the top 5 records that meet certain conditions:

SELECT TOP 5 * FROM YourTableName
WHERE SomeColumn = 'SomeValue'
ORDER BY AnotherColumn;

Using Variables:

You can use variables to make your query more dynamic. For example, if you want to find the top N records, you can declare a variable and use it in the TOP clause:

DECLARE @TopCount INT = 5;
SELECT TOP (@TopCount) * FROM YourTableName;

Handling Ties:

If your ordering column has the possibility of having ties (i.e., multiple rows with the same value), you might want to add additional columns to the ORDER BY clause to break ties:

SELECT TOP 5 * FROM YourTableName
ORDER BY Column1, Column2;

Using TOP without an ORDER BY clause can result in arbitrary results because SQL Server does not guarantee a specific order if one is not specified. Always include an ORDER BY clause if you need a specific order.