Statements

Question 1

Difference between “Union” and “Union All”?

Answer 1

Difference is that Union does Distinct clause in the background while Union All does not perform distinct and gives all records merged.

Question 2

Structure of basic Union Query

Answer 2

SELECT Column From Table1
UNION
SELECT Column From Table2;

Question 3

Structure of basic Union All Query

Answer 3

Select Column From Table1
UNION ALL
Select Column From Table2;

Question 4

What conditions must be satisfied for Union query?

Answer 4

• No of columns must match
• Order of columns should be same
• Data types should match

Question 5

Types of Joins

Answer 5

• Inner
• Left Outer
• Right Outer
• Full

Question 6

Define Inner Join

Answer 6

Intersection of two joining tables. So only the records that strictly satisfy the condition will be shown.

Question 7

Define Left Outer Join

Answer 7

All records in left table and the ones that satisfy the condition in right table. The rows of right for which the condition is satisfied will be merged with left. For the records in left which don’t satisfy condition the columns of right table will have NULL value

Question 8

Define Right Outer Join

Answer 8

All records in right table and the ones that satisfy the condition in left table. The rows of left for which the condition is satisfied will be merged with right. For the records in right which don’t satisfy condition the columns of left table will have NULL value

Question 9

Define Full Join

Answer 9

All records of both tables are shown. Those for which condition matches are merged together. POTENTIALLY HUGE RECORDS ARE RETURNED FROM FULL JOINS.

Question 10

Structure of Inner Join

Answer 10

SELECT Table1.Column1, Table2.Column1 FROM Table1 INNER JOIN Table2 ON Table1.MagicalCol1 = Table2.MagicalCol1

Question 11

Structure of Left Join

Answer 11

SELECT Table1.Column1, Table2.Column1 FROM Table1 LEFT JOIN Table2 ON Table1.MagicalCol1 = Table2.MagicalCol1

Question 12

Structure of Right Join

Answer 12

SELECT Table1.Column1, Table2.Column1 FROM Table1 RIGHT JOIN Table2 ON Table1.MagicalCol1 = Table2.MagicalCol1

Question 13

Structure of Full Join

Answer 13

SELECT Table1.Column1, Table2.Column1 FROM Table1 FULL JOIN Table2 ON Table1.MagicalCol1 = Table2.MagicalCol1

Question 14

Joining three tables

Answer 14

SELECT Table1.Column1, Table2.Column2, Table3.Column3 From ((Table1 INNER JOIN Table2 ON Table1.MagicColumn = Table2.MagicColumn) INNER JOIN Table3 ON Table1.MagicColumn = Table3.MagicColumn);

Question 15

What is alias?

Answer 15

Alias as name suggests provides temporary name to columns or tables.
Two types
- Column alias
- Table alias

Question 16

What is table alias?

Answer 16

Table alias is given to make query shorter when dealing with multiple tables.

Question 17

What is column alias and when to use it?

Answer 17

Giving temporary name to column for better readability.

Question 18

Structure of table alias

Answer 18

SELECT A.Column, A.Column2 from Table1 as A;

SELECT A.Column, B.Column from Table1 A, Table2 B WHERE A.Column = B.Column;

Question 19

Structure of Column Alias

Answer 19

SELECT Column1 as ReadableCol, Column2 as [Readable Col] FROM Table1;

Question 20

Structure of concatenating multiple column values in one

Answer 20

SELECT Column1, Column2 + ‘, ‘ + Column3 + ‘, ‘ + Column4 AS Alias FROM Table1;

Question 21

Structure of selecting records and adding a column with static value with Alias.

Answer 21

Select ‘Customers’ as Type, Customers.CustomerName as Name FROM Customers
UNION
Select ‘Suppliers’ as Type, Suppliers.SupplierName as Name FROM Suppliers;

Question 22

Structure of Group By query

Answer 22

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Question 23

Countries wise count of customers with countries having more customers first

Answer 23

SELECT COUNT(CustomerId), Country FROM Customers GROUP BY Country ORDER BY Count(CustomerId) DESC;

Question 24

List number of orders shipped by each shipper in descending sorted order of no of orders

Answer 24

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Question 25

Why was HAVING clause added in SQL?

Answer 25

Having Clause was added to SQL because WHERE clause could not be used with aggregate functions. Suppose needed to apply condition to COUNT, SUM, AVG, MIN, MAX etc

Question 26

Structure of Having clause query

Answer 26

``` SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); ```

Question 27

List number of customers in each country. Only include countries with more than 5 customers.

Answer 27

Need group by and aggregation function count and Having clause. Select Country, Count(CustomerId) AS Cnt FROM Customers GROUP BY Country HAVING Count(CustomerId) > 5 ORDER BY Cnt DESC;

Question 28

List of employees ordered more than 10 orders

Answer 28

SELECT Employees.LastName, COUNT(Orders.OrderId) FROM Employees LEFT JOIN Orders ON Employees.EmployeeId = Orders.EmployeeId GROUP BY Orders.EmployeeId HAVING COUNT(Orders.OrderId) > 10;

Question 29

List if the employees "Davolio" or "Fuller" have registered more than 25 orders

Answer 29

SELECT Employees.LastName, COUNT(Orders.OrderId) AS NumberOfOrders FROM Employees LEFT JOIN Orders ON Employees.EmployeeId = Orders.EmployeeId WHERE Employees.LastName = 'Davolio' OR Employees.LastName = 'Fuller' GROUP BY LastName HAVING COUNT(Orders.OrderId) > 25;

Question 30

What is self join?

Answer 30

Joining on same table with different alias. | A self JOIN is a regular join, but the table is joined with itself.

Question 31

Structure of Self Join

Answer 31

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; Here T1 and T2 are aliases of same Table

Question 32

SQL statement matches customers that are from the same city

Answer 32

Need self join. SELECT A.CustomerName AS Customer1, B.CustomerName AS Customer2 FROM Customers A, Customers B WHERE A.CustomerId <> B.CustomerId AND A.City = B.City ORDER BY A.City;

Question 33

Need for EXISTS operator

Answer 33

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns true if the subquery returns one or more records.

Question 34

Structure of EXISTS operator

Answer 34

SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

Question 35

Returns TRUE and lists the suppliers with a product price less than 20

Answer 35

With Subquery SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20); ---------------------------------------------------------------------------------- Without Subquery SELECT DISTINCT Suppliers.SupplierName FROM Products INNER JOIN Suppliers ON Products.SupplierId = Suppliers.SupplierId WHERE Products.Price < 20;

Question 36

Need for ANY and ALL operators

Answer 36

The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition.

Question 37

Structure of ANY operator

Answer 37

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

Question 38

Structure of ALL operator

Answer 38

SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

Question 39

List the product names if it finds ANY records in the OrderDetails table that quantity = 10

Answer 39

Using IN operator SELECT DISTINCT Products.ProductName FROM Products WHERE Products.ProductId IN ( SELECT ProductId FROM OrderDetails WHERE Quantity = 10 ); ------------------------------- Using ANY operator SELECT Products.ProductName FROM Products WHERE Products.ProductId = ANY ( SELECT ProductId From OrderDetails WHERE Quantity = 10 );

Question 40

Lists the product names if ALL the records in the OrderDetails table has quantity = 10.

Answer 40

SELECT Products.ProductName FROM Products WHERE Products.ProductId = ALL ( SELECT ProductId From OrderDetails WHERE Quantity = 10 );

Question 41

Structure of ANY operator

Answer 41

SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

Question 42

Show orders with quantity and textual form relative to 30. Like greater than 30, equal to 30 and less than 30

Answer 42

SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END AS QuantityText FROM OrderDetails;

Question 43

List the product names if it finds ANY records in the OrderDetails table that quantity = 10

Answer 43

Using IN operator SELECT DISTINCT Products.ProductName FROM Products WHERE Products.ProductId IN ( SELECT ProductId FROM OrderDetails WHERE Quantity = 10 ); ------------------------------- Using ANY operator SELECT Products.ProductName FROM Products WHERE Products.ProductId = ANY ( SELECT ProductId From OrderDetails WHERE Quantity = 10 );

Question 44

Lists the product names if ALL the records in the OrderDetails table has quantity = 10.

Answer 44

SELECT Products.ProductName FROM Products WHERE Products.ProductId = ALL ( SELECT ProductId From OrderDetails WHERE Quantity = 10 );

Question 45

Structure of SQL Case statement

Answer 45

``` CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END; The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. ``` If there is no ELSE part and no conditions are true, it returns NULL.

Question 46

Show orders with quantity and textual form relative to 30. Like greater than 30, equal to 30 and less than 30

Answer 46

SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END AS QuantityText FROM OrderDetails;

Question 47

Order the customers by City. However, if City is NULL, then order by Country:

Answer 47

``` SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END); ```

Question 48

Structure of WITH clause

Answer 48

TBA

Question 49

Important gotcha about GROUP BY Clause. When showing cloumn and doing group by

Answer 49

When showing column X, Y, Aggregate(Z) etc in SELECT clause and Grouping By then we must give X and Y in the group by clause. Without it it won't work.

Question 50

What does ROUND function do?

Answer 50

ROUND (number) ROUND returns n rounded to integer places to the right of the decimal point. If you omit integer , then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.

Question 51

Which function is used to find median of list of numbers?

Answer 51

MEDIAN(column) is used to find median.

Question 52

Which function is used to truncate decimal to fixed number of digits?

Answer 52

TRUNC(column, noOfDigits) function is used to truncate digits after decimal place.