2. SELECT Query

Question 1

Single line comment in SQL Server

Answer 1

–

Question 2

In an SQL statement, reserved words like
* clauses (SELECT, FROM, WHERE)
* built-in functions (LOWER)
* data types (VARCHAR)

are called _ .

Answer 2

keywords

Keywords form the syntax of SQL.

Question 3

In an SQL statement, identifiers are _

Answer 3

names of column, table or schema.

SELECT name, LOWER(country)
FROM customers
WHERE country = "India"

Here, name, country, customer are identifiers.

Question 4

In an SQL statement, the _ are used inside WHERE clause to compare values.

Answer 4

operators

Question 5

An SQL statement is composed of clauses and each clause is composed of _

Answer 5

keywords, identifiers, operators and string literals.

Question 6

In SQL statement, _ clause is used to filter columns of the table and _ clause is used to filter rows of the table.

Answer 6

1. SELECT
2. WHERE

Question 7

List down some (as many as you can recall) clauses used in a SELECT query

Answer 7

• SELECT
• DISTINCT
• TOP
• FROM
• WHERE
• JOIN
• GROUP BY
• ORDER BY
• HAVING

Question 8

The typical order of execution for major SQL clauses is:
* SELECT
* DISTINCT
* OFFSET / FETCH (pagination) / TOP
* FROM
* WHERE
* JOIN
* GROUP BY
* ORDER BY
* HAVING

Answer 8

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT (applied after SELECT)
8. ORDER BY
9. OFFSET / FETCH (pagination) / TOP

Question 9

SQL command to specify database in advance

Answer 9

USE databasename;

Question 10

In the SQL query:

SELECT
first_name,
score
FROM customers
WHERE country = 'Germany';

the order of execution of clauses is

Answer 10

1. FROM
2. WHERE
3. SELECT

Question 11

select all customers where score is NULL (unknown).

Answer 11

SELECT * from customers
WHERE score IS NULL;

Question 12

Which clause is used to sort the data?

Answer 12

ORDER BY <col-name>

By default, ordering sequence is ascending (ASC).

Question 13

INSERT INTO customers(id,first_name, country, score) 
    VALUES (1, 'Maria', 'Germany', 350),
        (2, 'John', 'USA', 900),
        (3, 'Georg', 'UK', 750),
        (4, 'Martin', 'Germany', 500),
        (5, 'Peter', 'USA', 0),
        (6, 'Sahil', 'India', null),
        (7, 'Jonney', null, 600);

SELECT * 
FROM customers
order by country DESC;

Where will ‘Jonney’ be placed in the rows of result set?

Answer 13

last row

SQL treats NULL as the lowest possible value when sorting.

Question 14

Write query for customers table to retrieve data ordered by ascending countries and within each country ordered by descending scores.

Assume country column has repetitive entries.

Answer 14

SELECT *
FROM customers
ORDER BY
country ASC,
score DESC

This is known as multi-level (or nested) sorting in SQL

Question 15

Write query for customers table to retrieve data ordered by ascending countries and within each country ordered by descending scores.
Ensure null countries are skipped.

Assume country column has repetitive entries.

Answer 15

SELECT *
FROM customers
WHERE country IS NOT NULL
ORDER BY country ASC, score DESC

Question 16

1. SQL places NULL column as the _ row value when sorting the column ascending.
2. SQL places NULL column as the _ row value when sorting the column descending.

(last/first)

We have some null entries in column A and we are sorting (order by) column A

Answer 16

1. first
2. last

SQL treats NULL as the lowest possible value when sorting.

Question 17

1. When calculating avg(), SQL _ NULL values.

(counts/ignores)

2. If score column has entries: 600, NULL, then avg(score) will be _ .
3. If score column has entries: NULL, NULL, then avg(score) will be _ .

Answer 17

1. ignores
2. 600
3. NULL

Question 18

Find avg score and total number of customers for each country.

CREATE TABLE customers (
  id int,
  first_name varchar(15),
  country varchar(15),
  score int
);

Answer 18

select 
country,
avg(coalesce(score, 0)) as avg_score_per_country,
count(*) as count_per_country
from customers
where country is not null
group by country

Question 19

Write query to place NULL values in the end, while sorting on basis of score.

Hint: Maximum score value is 1000

Answer 19

select *, coalesce(score, 1001) as nscore
from customers
order by nscore asc;

Question 20

Retrieve customers with non-null countries,
sorted by country in descending order.

Answer 20

SELECT * 
FROM customers
where country is not null
order by country DESC;

Question 21

Which SQL clause helps aggregate data of a column by another column?

Answer 21

GROUP BY

it conveys the “for each” semantics.

For e.g., find total score for each country

Question 22

Write SQL query to find the total score values for each country.

Answer 22

SELECT country, SUM(score)
FROM customers
GROUP BY country

Question 23

If GROUP BY clause has two columns, then the _ is used as basis of aggregation.

Answer 23

combined value of two columns

Question 24

Find total score and total number of customers for each country.

Answer 24

SELECT SUM(score) AS total-score, COUNT(*) AS total_customers
FROM customers
GROUP BY country;

Question 25

Which SQL clause is used to filter data after aggregation?

Answer 25

HAVING HAVING clause is executed after GROUP BY clause.

Question 26

Write SQL query to find total score values for each country. Avoid null values for countries.

Answer 26

SELECT country, SUM(score) FROM customers WHERE country IS NOT NULL GROUP BY country

Question 27

Write SQL query to find the average score values for each country where average score is more than 449.

Answer 27

SELECT country, AVERAGE(score) AS avg_score FROM customers GROUP BY country HAVING AVERAGE(score) > 449

Question 28

NULLs are treated as _ (equal/unequal) when evaluating distinctness in DISTINCT clause

Answer 28

equal

Question 29

Retrieve top 3 customers with highest scores.

Answer 29

SELECT TOP(3) * FROM customers ORDER BY scores DESC

Question 30

Any comparison operator like <, =, > with NULL operand returns _ (false/unknown).

Answer 30

unknown

Question 31

Find total number of times each customer made orders. `MyOrders: IrderId, CustomerId, Sales` ## Footnote Assume a customer can make multiple orders.

Answer 31

``` select CustomerID, count(*) as OrdersPerCustomer from MyOrders group by CustomerID; ```

Question 32

`avg` ignores _ values if at least one row is non-null.

Answer 32

null