SQL

Question 1

What are SQL dialects? Give some examples.

Answer 1

The various versions of SQL, are called SQL dialects. All the flavors of SQL have a very similar syntax and vary insignificantly only in additional functionality. Some examples are Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle, and MongoDB.

Question 2

What are the main applications of SQL?

Answer 2

Using SQL, we can:

• create, delete, and update tables in a database
  access,
• manipulate, and modify data in a table
• retrieve and summarize the necessary information from a table or several tables
• add or remove certain rows or columns from a table
• integrate with other programming languages, such as Python or R, so we can use their combined power.

Question 3

List examples of the types of SQL commands (or SQL subsets)

Answer 3

• Data Definition Language (DDL) – to define and modify the structure of a database. (CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN)
• Data Manipulation Language (DML) – to access, manipulate, and modify data in a database. (UPDATE, DELETE, and INSERT)
• Data Control Language (DCL) – to control user access to the data in the database and give or revoke privileges to a specific user or a group of users. (GRANT and REVOKE)
• Transaction Control Language (TCL) – to control transactions in a database. (COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT)
• Data Query Language (DQL) – to perform queries on the data in a database to retrieve the necessary information from it. (SELECT)

Question 4

What is a constraint, provide examples

Answer 4

A set of conditions defining the type of data that can be input into each column of a table. Constraints ensure data integrity in a table and block undesired actions.

• DEFAULT – provides a default value for a column.
• UNIQUE – allows only unique values.
• NOT NULL – allows only non-null values.
• PRIMARY KEY – allows only unique and strictly non-null values (NOT NULL and UNIQUE).
• FOREIGN KEY – provides shared keys between two and more tables.

Question 5

What is a join and what are the different types? How else can you combine data from two tables?

Answer 5

A clause used to combine and retrieve records from two or multiple tables. SQL tables can be joined based on the relationship between the columns of those tables.

• (INNER) JOIN – returns only those records that satisfy a defined join condition in both (or all) tables. It’s a default SQL join.
• LEFT (OUTER) JOIN – returns all records from the left table and those records from the right table that satisfy a defined join condition.
• RIGHT (OUTER) JOIN – returns all records from the right table and those records from the left table that satisfy a defined join condition.
• FULL (OUTER) JOIN – returns all records from both (or all) tables. It can be considered as a combination of left and right joins.

Can also use UNION to combine data from two tables

Question 6

What’s the basic syntax for updating a table?

Answer 6

UPDATE table_name
SET col_1 = value_1, column_2 = value_2
WHERE condition;

Question 7

How to get the count of records in a table?

Answer 7

Using the COUNT() aggregate function with the asterisk passed as its argument: SELECT COUNT(*) FROM table_name;.

Question 8

How to select common records from two tables?

Answer 8

Using the INTERSECT statement:

SELECT * FROM table_1
INTERSECT
SELECT * FROM table_1;

NOTE: both select statements must return same number of columns

Question 9

What are some common aggregate functions?

Answer 9

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

Question 10

What are some common scalar functions?

Answer 10

LEN() (in other SQL flavors – LENGTH()) – returns the length of a string, including the blank spaces
UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper case
LCASE() (in other SQL flavors – 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)
MID() (in other SQL flavors – SUBSTR()) – extracts a substring from a string
ROUND() – returns the numerical value rounded to a specified number of decimals
NOW() – returns the current date and time

Question 11

What are some common string manipulation functions?

Answer 11

CONCAT() – joins two or more string values appending the 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
REPLACE() – replaces all occurrences of a defined substring in a provided string with another substring
INSTR() – returns the numeric position of a defined substring in a provided string
LPAD() and RPAD() – return the padding of the left-side/right-side character for right-justified/left-justified value
TRIM() – removes all the defined characters, as well as white spaces, from the left, right, or both ends of a provided string

Question 12

What is the difference between a primary key and a unique key?

Answer 12

While both types of keys ensure unique values in a column of a table, the first one identifies uniquely each record of the table, and the second one prevents duplicates in that column.

Question 13

What is the order of appearance of the common statements in the SELECT query?

Answer 13

SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Question 14

What’s a CASE statement and what’s the syntax?

Answer 14

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;

Question 15

How to select all even or all odd records in a table?

Answer 15

SELECT * FROM table_name
WHERE ID_column % 2 = 0;

Question 16

Find the 6th highest value in a column of a table.

Answer 16

SELECT * FROM table_name
ORDER BY column_name DESC
LIMIT 1
OFFSET 5;

Question 17

How to find the values in a text column of a table that start with a certain letter?

Answer 17

SELECT * FROM table_name
WHERE surname LIKE ‘A%’;

Question 18

Select 5 random rows from a table

Answer 18

SELECT * FROM your_table
ORDER BY RANDOM()
LIMIT 5;

NOTE: This will be very slow on a large table

Question 19

what’s a CTE in sql?

What’s the basic syntax to create one? 

Answer 19

A Common Table Expression (CTE) in SQL is a temporary result set that can be referred to within the context of a SELECT, INSERT, UPDATE, or DELETE statement. CTEs provide a way to break down complex queries into simpler, more manageable parts. They are defined using the WITH clause and can be referenced within the query.

The basic syntax is:
WITH cte_name (column1, column2, …) AS (
– CTE query definition
SELECT column1, column2, …
FROM your_table
WHERE conditions
)
– Main query using the CTE
SELECT *
FROM cte_name;

Question 20

***subquery examples

Question 21

Given table Products (productId, name, supplierId) :

Find the percentage of the products supplied by each supplier.

Answer 21

SELECT SupplierID,
count() * 100.0 / (SELECT count() from Products) as “Supplier Percentage”
FROM Products
GROUP BY SupplierID

Question 22

***percent change overtime

Question 23

What is guaranteed to be returned from the following query if there is a NULL value in races.winner_id?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races)

Answer 23

Nothing. If there is a NULL in the subquery nothing will be returned even if there are some other matches. Instead use:

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races WHERE winner_id IS NOT NULL)

Question 24

Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query:

Select * From Emp, Dept

Answer 24

The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.

Question 25

Given table test_a(id numeric);
And table test_b(id numeric);

Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.

Answer 25

select * from test_a
except
select * from test_b;

Question 26

Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

Answer 26

SELECT TOP (1) Salary FROM
(
SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary

OR

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 9;

Question 27

Given, tables:

users (user_id, username)
And
training_details (user_training_id user_id training_id training_date)

Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.

Answer 27

SELECT
u.user_id,
username,
training_id,
training_date,
count( user_training_id ) AS count
FROM users u JOIN training_details t ON t.user_id = u.user_id
GROUP BY u.user_id,
username,
training_id,
training_date
HAVING count( user_training_id ) > 1
ORDER BY training_date DESC;

Question 28

What does ACID stand for and what do ACID properties do? 

Answer 28

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Question 29

Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?

Answer 29

SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id

Question 30

What’s the equivalent of the NVL function in post gres

Answer 30

The NVL function is used to compare a list of values in a list and return the first non-null value. The equivalent function in post grass is coalesce()

Question 31

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

Answer 31

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}

Question 32

What is the difference between the WHERE and HAVING clauses?

Answer 32

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Question 33

Given a table Employee having columns empName and empId, what will be the result of the SQL query below?

Answer 33

“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.

Question 34

What will be the output of the below query, given an Employee table having 10 records?

Answer 34

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

Question 35

Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print ‘Fizz’ for a numeric value or ‘Buzz’ for alphabetical value for all values in that column.

Example:

[‘d’, ‘x’, ‘T’, 8, ‘a’, 9, 6, 2, ‘V’]

…should output:

[‘Buzz’, ‘Buzz’, ‘Buzz’, ‘Fizz’, ‘Buzz’,’Fizz’, ‘Fizz’, ‘Fizz’, ‘Buzz’]

Answer 35

SELECT col, case when upper(col) = lower(col) then ‘Fizz’ else ‘Buzz’ end as FizzBuzz from table;

Question 36

Write an SQL query to display the text CAPONE as:

C
A
P
O
N
E

Answer 36

Declare @a nvarchar(100)=’capone’;
Declare @length INT;
Declare @i INT=1;
SET @lenght=LEN(@a)
while @i<=@length
BEGIN
print(substring(@a,@i,1));
set @i=@i+1;
END

Question 37

Can we insert a row for identity column implicitly?

Answer 37

Yes, SET IDENTITY_INSERT TABLE1 ON

INSERT INTO TABLE1 (ID,NAME)
SELECT ID,NAME FROM TEMPTB1

SET IDENTITY_INSERT OFF

Question 38

Given this table:

Testdb=# Select * FROM “Test”.”EMP”;

1
2
3
4
5
(5 rows)
What will be the output of below snippet?

Select SUM(1) FROM “Test”.”EMP”;
Select SUM(2) FROM “Test”.”EMP”;
Select SUM(3) FROM “Test”.”EMP”;

Answer 38

5
10
15

Question 39

Table is as follows:

ID C1 C2 C3
1 Red Yellow Blue
2 NULL Red Green
3 Yellow NULL Violet

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR.

Answer 39

SELECT * FROM table
WHERE ‘Yellow’ IN (C1, C2, C3)

Question 40

Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.

Col1 Col2
1 0
0 1
0 1
0 1
1 0
0 1
1 0
1 0

Answer 40

update table set col2 = case when col1 = 1 then 0 else 1 end

Or if the type is numeric:

update table set col2 = 1 - col1

Question 41

How do you get the last id without the max function?

Answer 41

select id from table order by id desc limit 1

Question 42

What is the difference between IN and EXISTS?

Answer 42

IN:

Works on List result set
Doesn’t work on subqueries resulting in Virtual tables with multiple columns
Compares every value in the result list
Performance is comparatively SLOW for larger resultset of subquery

EXISTS:

Works on Virtual tables
Is used with co-related queries
Exits comparison when match is found
Performance is comparatively FAST for larger resultset of subquery

Question 43

How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?

Answer 43

Declare @N int
set @N = 5;
WITH CTE AS
(
SELECT Name, Salary, EmpID, RN = ROW_NUMBER()
OVER (ORDER BY Salary DESC)
FROM Employee
)
SELECT Name, Salary, EmpID
FROM CTE
WHERE RN = @N

Question 44

Given the following table named A:
x
——
2
-2
4
-4
-3
0
2

Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x.

Answer 44

SELECT SUM (
CASE WHEN x>0 THEN x
ELSE 0 END ) sum_pos,
SUM (
CASE WHEN x<0 THEN x
ELSE 0 END ) sum_neg
FROM a;

Question 45

Given the table mass_table:

weight
5.67
34.567
365.253
34
Write a query that produces the output:

weight kg gms
5.67 5 67
34.567 34 567
365.253 365 253
34 34 0

Answer 45

SELECT
weight,
TRUNC (weight) as kg,
NVL(SUBSTR(weight - TRUNC(weight), 2), 0) as gms

FROM mass_table;

Question 46

33.
Consider the Employee table below.

Emp_Id Emp_name Salary Manager_Id
10 Anil 50000 18
11 Vikas 75000 16
12 Nisha 40000 18
13 Nidhi 60000 17
14 Priya 80000 18
15 Mohit 45000 18
16 Rajesh 90000 –
17 Raman 55000 16
18 Santosh 65000 17

Write a query to generate below output:

Manager_Id Manager Average_Salary_Under_Manager
16 Rajesh 65000
17 Raman 62500
18 Santosh 53750

Answer 46

SELECT b.emp_id as “Manager_Id”,
b.emp_name as “Manager”,
avg(a.salary) as “Average_Salary_Under_Manager”
FROM Employee a,
Employee b
WHERE a.manager_id = b.emp_id
GROUP BY b.emp_id, b.emp_name
ORDER BY b.emp_id;

Question 47

How do you copy data from one table to another table ?

Answer 47

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

Question 48

Find the SQL statement below that is equal to the following: SELECT name FROM customer WHERE state = ‘VA’;

SELECT name IN customer WHERE state IN (‘VA’);
SELECT name IN customer WHERE state = ‘VA’;
SELECT name IN customer WHERE state = ‘V’;
SELECT name FROM customer WHERE state IN (‘VA’);

Answer 48

SELECT name FROM customer WHERE state IN (‘VA’);

Question 49

Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to update the values to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.

Answer 49

UPDATE TBL
SET Nmbr =
CASE when Nmbr = 0 then Nmbr+2
ELSE Nmbr+3
END ;

Question 50

How do you get the Nth-highest salary from the Employee table without a subquery or CTE?

Answer 50

SELECT salary from Employee order by salary DESC LIMIT 2,1

This will give the third-highest salary from the Employee table. Accordingly we can find out Nth salary using LIMIT (N-1),1.

Question 51

How to find duplicate name and email fields in a user table ?

Answer 51

SELECT name, email, COUNT()
FROM users
GROUP BY name, email
HAVING COUNT() > 1

Question 52

Given tables:
Work (work_id, work_name, artist_id, work_country)
Artist (artist_id, artist_name)

Select each artist’s name and Id and the number of different countries their work is displayed in

Answer 52

SELECt distinct count(w.artist_id)
over (partition by w.artist_id) artist_country_count ,
w.artist_id,
a.artist_name

FROM work w
JOIN artist a on a.artist_id = w.artist_id
GROUP BY w.work_country, a.artist_name, w.artist_id
ORDER BY artist_country_count desc

Question 53

Given tables:
work (workd_id, work_title, museum_id, style)
musuem (museum_id, museum_name)

Select museums with the highest number of Cubist style works

Answer 53

SELECT
count(w.work_id) work_count,
w.museum_id,
m.name

FROM work w
join museum m on w.museum_id = m.museum_id
WHERE style = ‘Cubism’
GROUP BY w.museum_id, m.name
m.country
ORDER BY work_count Desc

Question 54

Given tables:
work (workd_id, work_title, museum_id, style)
musuem (museum_id, museum_name)

Select museums with the highest proportion/percent of Cubist style works

Answer 54

SELECT
w.museum_id,
m.name,
AVG(CASE WHEN style = ‘Cubism’ then 1 else 0 end) percent_cubist

FROM work w
JOIN museum m on w.museum_id = m.museum_id
GROUP BY w.museum_id, m.name
ORDER BY percent_cubist desc

Question 55

Given tables:
work (workd_id, work_title, museum_id, style)
musuem (museum_id, museum_name, country)

Which work styles are the most displayed in museums in the USA?

Answer 55

SELECT
count(w.work_id) work_count,
w.style

FROM work w
join museum m on w.museum_id = m.museum_id
WHERE m.country=’USA’
GROUP BY w.style
ORDER BY work_count desc

Question 56

What are the differences between CTEs and temp tables?

Answer 56

CTEs are typically held in memory, which means that they can be accessed and manipulated quickly. Temporary tables, on the other hand, are usually written to disk, which can cause a performance overhead. Usage : CTEs are usually used for smaller data sets that require more complex manipulation.

Question 57

What is a Windows function and why would you want to use it?

Answer 57

A type of function that performs a calculation across a specified range of rows related to the current row within the result set. Unlike aggregate functions, which operate on the entire result set, window functions focus on a “window” or subset of rows defined by an OVER clause.

The basic syntax of a window function is as follows:

window_function(column) OVER (PARTITION BY partition_column ORDER BY order_column)

Window functions are powerful for performing calculations that involve data across different rows, such as running totals, moving averages, and ranking. They are especially useful when you need to perform computations within specific groups or partitions of your data.

Question 58

Provide an example of common windows functions and their syntax

Answer 58

SUM(column) OVER (PARTITION BY partition_column ORDER BY order_column)

AVG(): Calculates the average of a column within the specified window.

ROW_NUMBER(): Assigns a unique number to each row within the specified window

COUNT() : used to calculate the number of rows within a specified window or partition.

Question 59

Given the following tables:
products (productid, categoryid, discounted)
categories(categoryid, categoryname)

write a query that selects the categories with the highest percentages of discounted products

Answer 59

SELECT
c.categoryid, c.categoryname,
(COUNT() FILTER (WHERE discontinued = true) * 100.0 /
COUNT()) AS discontinued_percentage
FROM
products p join categories c on p.categoryid = c.categoryid
GROUP BY
c.categoryid, c.categoryname
ORDER BY discontinued_percentage DESC

Question 60

What is the purpose of the COALESCE function?

Answer 60

to return the first non-null expression among its arguments.