Example Queries

Question 1

Example of average function.

Answer 1

SELECT AVG(column_name) FROM table_name

Question 2

Example of above average statement.

Answer 2

SELECT ProductName, Price FROM Products

WHERE Price>(SELECT AVG(Price) FROM Products);

Question 3

Statement that gets count

Answer 3

SELECT COUNT(column_name) FROM table_name;

Question 4

Example of returns distinct value.

Answer 4

SELECT COUNT(DISTINCT column_name) FROM table_name;

Question 5

The following SQL statement counts the number of orders from “CustomerID”=7 from the “Orders” table:

Answer 5

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

Question 6

The FIRST() function returns the first value of the selected column.

Answer 6

SELECT FIRST(column_name) FROM table_name;

Question 7

SQL LAST() Syntax

Answer 7

SELECT LAST(column_name) FROM table_name;

Question 8

SQL MAX() Syntax

Answer 8

SELECT MAX(column_name) FROM table_name;

Question 9

SQL MIN() Syntax

Answer 9

SELECT MIN(column_name) FROM table_name;

Question 10

SQL MIN() Syntax

Answer 10

SELECT MIN(column_name) FROM table_name;

Question 11

SQL SUM() Syntax

SELECT SUM(column_name

Answer 11

SELECT SUM(column_name) FROM table_name;

Question 12

SQL GROUP BY Syntax

Answer 12

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Question 13

The following SQL statement counts as orders grouped by shippers:

Answer 13

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

Question 14

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Answer 14

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

Question 15

The UCASE() function converts the value of a field to uppercase.

Answer 15

SELECT UCASE(column_name) FROM table_name;

Question 16

The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table, and converts the “CustomerName” column to uppercase:

Answer 16

SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;

Question 17

The LCASE() function converts the value of a field to lowercase.

Answer 17

SELECT LCASE(column_name) FROM table_name;

Question 18

The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table, and converts the “CustomerName” column to lowercase:

Answer 18

SELECT LCASE(CustomerName) AS Customer, City
FROM Customers;

Question 19

The MID() function is used to extract characters from a text field.

Answer 19

SELECT MID(column_name,start,length) ASsome_nameFROM table_name;

Question 20

The following SQL statement selects the first four characters from the “City” column from the “Customers” table:

Answer 20

SELECT MID(City,1,4) AS ShortCity
FROM Customers;

Question 21

The LEN() function returns the length of the value in a text field.

Answer 21

SELECT LEN(column_name) FROM table_name;

Question 22

The FORMAT() function is used to format how a field is to be displayed.

Answer 22

SELECT FORMAT(column_name,format) FROM table_name;

Question 23

The following SQL statement selects the product name, and price for today (formatted like YYYY-MM-DD) from the “Products” table:

Answer 23

SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate
FROM Products;