SQL

Question 1

only returns data that is distinct — in other words, if there are duplicate records, it will return only one copy of each.

Answer 1

SELECT DISTINCT

Question 2

SELECT DISTINCT

Answer 2

only returns data that is distinct — in other words, if there are duplicate records, it will return only one copy of each.

Question 3

only returns the top x number or percent from a table.

Answer 3

SELECT TOP 50

Question 4

SELECT TOP 50

Answer 4

only returns the top x number or percent from a table.

Question 5

COUNT()

Answer 5

returns the number of rows in a set

SELECT COUNT(*) AS total_records
FROM axelar.gov.fact_staking;

Question 6

returns the number of rows in a set

Answer 6

COUNT()

SELECT COUNT(*) AS total_records
FROM axelar.gov.fact_staking;

Question 7

SQL Aggregate Functions

Answer 7

An aggregate function is a function that performs a calculation on a set of values, and returns a single value.

The most commonly used SQL aggregate functions are:

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column

Question 8

INNER JOIN

Answer 8

An INNER JOIN is a type of join operation in SQL that combines rows from two or more tables based on a related column between them. It returns only the rows where there is a match between the columns specified in the join condition.

Sure, let’s say we have two tables: employees and departments. Each employee belongs to a department, and there’s a common column between them, let’s call it department_id.

Here’s how you would perform an INNER JOIN using these tables:

SELECT
employees.name,
departments.department_name
FROM
employees
INNER JOIN
departments
ON
employees.department_id = departments.department_id;

Question 9

Sure, let’s say we have two tables: employees and departments. Each employee belongs to a department, and there’s a common column between them, let’s call it department_id. How do you query all the employees that are assigned to a department (any department)?

Answer 9

Through “inner join”

SELECT
employees.name,
departments.department_name
FROM
employees
INNER JOIN
departments
ON
employees.department_id = departments.department_id;

Question 10

If you simply want to combine every row from one dataset with every row from another dataset, you can use a _________. This will create a Cartesian product of the two datasets.

Answer 10

CROSS JOIN

SELECT *
FROM table1
CROSS JOIN table2;

Question 11

CROSS JOIN

Answer 11

Combines every row from one dataset with every row from another dataset.

Question 12

RIGHT JOIN

Answer 12

The SQL RIGHT JOIN statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from the right table.

Let’s consider a scenario where we have two tables: one containing information about employees and another containing their performance reviews. Now, suppose we want to retrieve all employee names along with their performance reviews on a specific date. We’ll use a RIGHT JOIN to ensure that we get all the performance reviews for each employee, even if there’s no review for a specific date.

“Employees” table:
|—-|———–|————–|
| 1 | Alice | Sales |
| 2 | Bob | Marketing |
| 3 | Charlie | Finance |
| 4 | Diana | HR |
| 5 | Eve | Operations |

“Perf_Rev” table:
| ID | Employee_ID | Review_Date | Rating |
|—-|————-|————-|——–|
| 1 | 1 | 2024-04-30 | 4 |
| 2 | 2 | 2024-04-30 | 3 |
| 3 | 1 | 2024-05-01 | 5 |
| 4 | 3 | 2024-05-01 | 4 |
| 5 | 4 | 2024-05-01 | 5 |

SELECT Employees.Name, Per_Rev.Review_Date, Per_Rev.Rating
FROM Employees
RIGHT JOIN Performance_Reviews Per_Rev ON Employees.ID = Per_Rev.Employee_ID
WHERE Per_Rev.Review_Date = ‘2024-05-01’;

| ID | Name | Department |

Name | Review_Date | Rating |
|————-|——————–|————|
| Alice | 2024-05-01 | 5 |
| Bob | NULL | NULL |
| Charlie | 2024-05-01 | 4 |
| Diana | 2024-05-01 | 5 |
| Eve | NULL | NULL |

Question 13

Let’s consider a scenario where we have two tables: one containing information about employees and another containing their performance reviews. Now, suppose we want to retrieve all employee names along with their performance reviews on a specific date. We’ll use a _______ to ensure that we get all the performance reviews for each employee, even if there’s no review for a specific date.

Answer 13

RIGHT JOIN