Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > Statements > Flashcards

Statements Flashcards

1
Q

Difference between “Union” and “Union All”?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Structure of basic Union Query

A

SELECT Column From Table1
UNION
SELECT Column From Table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Structure of basic Union All Query

A

Select Column From Table1
UNION ALL
Select Column From Table2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What conditions must be satisfied for Union query?

A
  • No of columns must match
  • Order of columns should be same
  • Data types should match
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Types of Joins

A
  • Inner
  • Left Outer
  • Right Outer
  • Full
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define Inner Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Define Left Outer Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Define Right Outer Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Define Full Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Structure of Inner Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Structure of Left Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Structure of Right Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Structure of Full Join

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Joining three tables

A

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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is alias?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is table alias?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is column alias and when to use it?

A

Giving temporary name to column for better readability.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Structure of table alias

A

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Structure of Column Alias

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Structure of concatenating multiple column values in one

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

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

22
Q

Structure of Group By query

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

Countries wise count of customers with countries having more customers first

A

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

24
Q

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

A

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

25
Q

Why was HAVING clause added in SQL?

A

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

26
Q

Structure of Having clause query

A 
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
27
Q

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

A

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;

28
Q

List of employees ordered more than 10 orders

A

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;

29
Q

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

A

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;

30
Q

What is self join?

A

Joining on same table with different alias.

A self JOIN is a regular join, but the table is joined with itself.

31
Q

Structure of Self Join

A

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Here T1 and T2 are aliases of same Table

32
Q

SQL statement matches customers that are from the same city

A

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;

33
Q

Need for EXISTS operator

A

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.

34
Q

Structure of EXISTS operator

A

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

35
Q

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

A

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;

36
Q

Need for ANY and ALL operators

A

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.

37
Q

Structure of ANY operator

A

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

38
Q

Structure of ALL operator

A

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

39
Q

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

A

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
);

40
Q

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

A

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

41
Q

Structure of ANY operator

A

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

42
Q

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

A

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;

43
Q

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

A

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
);

44
Q

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

A

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

45
Q

Structure of SQL Case statement

A 
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.

46
Q

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

A

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;

47
Q

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

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

Structure of WITH clause

A

TBA

49
Q

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

A

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.

50
Q

What does ROUND function do?

A

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.

51
Q

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

A

MEDIAN(column) is used to find median.

52
Q

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

A

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

SQL (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions