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.
SELECT AGENT,
COUNT(*) AS Duplicate_count
FROM ACHL
GROUP BY AGENT
HAVING COUNT (*) >1;
- 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.
SELECT *
FROM world
WHERE name = 'India'
UNION ALL
SELECT *
FROM world
WHERE name = 'India';
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;
13.Explain order of execution of SQL?
Flying White Geese Have Only Little Space Daily. Use this to remember the execution of SQL
FROM - Identify the data source (table or tables).
WHERE - Filter rows by specific conditions.
GROUP BY- Group rows by specified columns - The GROUP BY clause groups rows that have the same values in specified columns into summary rows.
HAVING-The HAVING clause filters groups that result from the GROUP BY operation. This is similar to the WHERE clause but applies to the aggregated results.
SELECT- determines the columns or expressions to be returned in the result set.
DISTINCT- keyword removes duplicate rows from the result set (if specified).
ORDER BY-clause sorts the result set based on specified columns, either in ascending or descending order.
LIMIT- limit the number of rows returned or skip a specified number of rows.
14.For all the employees in the Employees table, show the FirstName, LastName, Title, and BirthDate.Order the results by BirthDate, so we have the oldest employees first. In the output of the query above, showing the Employees in order of BirthDate, we see the time of
the BirthDate field, which we don’t want. Show only the date portion of the BirthDate field.
SELECT FirstName, LastName,Title,BirthDate
CONVERT(DATE, BirthDate) AS BirthDate
FROM Employees
ORDER BY BirthDate;
15.Show the FirstName and LastName columns from the Employees table, and then create a new column called FullName, showing FirstName and LastName joined together in one column, with a space in between.
SELECT
FirstName,
LastName,
FirstName + ' ' + Lastname AS FullName
FROM Employees;
operator is used to concatenate strings in SQL Server.
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.
16.What is the difference between CHAR and VARCHAR data TYPES?
a CHAR is a fixed-length character data type that stores a specific number of characters, while VARCHAR is a variable-length character data type that stores a varying number of characters.
17.What is the difference between a database and a schema?
A database is a collection of related data that is stored and organized. A schema on the hand is a logical container within a database that holds objects like tables,views and procedures.
18.What is the difference between UNION and JOIN ?
Union combines rows from two or more tables vertically,while JOIN combines columns from two or more tables horizontally based on related columns.
19.What is a recursive SQL Query?
A recursive SQL query is query that refers to its own output in order to perform additional operations.It is commonly used for hierarchical or tree-like data structures.
20.What is the purpose of the LIKE operator ?
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.It allows wildcard characters like % (matches any sequence of characters) and _(matches any single character)
21.What is the purpose of the ROW_NUMBER () function?
The ROW_Number () function assigns a unique sequential number to each row within a result set. It is often used for pagination or ranking purposes.
22.Select the year and max snow depth, but only years where the max snow depth is at least 50?
Note We dont use aggregate functions using WHERE CLAUSE.
SELECT year,MAX(snow_depth) AS Max_snow_depth
FROM Station_Data
GROUP BY year
HAVING Max_snow_depth >=50
Top comments (2)
Good work, I had to refresh my interview acumen here. Thank you.
Anytime, buddy.