Example Queries Flashcards
(23 cards)
Example of average function.
SELECT AVG(column_name) FROM table_name
Example of above average statement.
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
Statement that gets count
SELECT COUNT(column_name) FROM table_name;
Example of returns distinct value.
SELECT COUNT(DISTINCT column_name) FROM table_name;
The following SQL statement counts the number of orders from “CustomerID”=7 from the “Orders” table:
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;
The FIRST() function returns the first value of the selected column.
SELECT FIRST(column_name) FROM table_name;
SQL LAST() Syntax
SELECT LAST(column_name) FROM table_name;
SQL MAX() Syntax
SELECT MAX(column_name) FROM table_name;
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name;
SQL MIN() Syntax
SELECT MIN(column_name) FROM table_name;
SQL SUM() Syntax
SELECT SUM(column_name
SELECT SUM(column_name) FROM table_name;
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
The following SQL statement counts as orders grouped by shippers:
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
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;
The UCASE() function converts the value of a field to uppercase.
SELECT UCASE(column_name) FROM table_name;
The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table, and converts the “CustomerName” column to uppercase:
SELECT UCASE(CustomerName) AS Customer, City FROM Customers;
The LCASE() function converts the value of a field to lowercase.
SELECT LCASE(column_name) FROM table_name;
The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table, and converts the “CustomerName” column to lowercase:
SELECT LCASE(CustomerName) AS Customer, City FROM Customers;
The MID() function is used to extract characters from a text field.
SELECT MID(column_name,start,length) ASsome_nameFROM table_name;
The following SQL statement selects the first four characters from the “City” column from the “Customers” table:
SELECT MID(City,1,4) AS ShortCity FROM Customers;
The LEN() function returns the length of the value in a text field.
SELECT LEN(column_name) FROM table_name;
The FORMAT() function is used to format how a field is to be displayed.
SELECT FORMAT(column_name,format) FROM table_name;
The following SQL statement selects the product name, and price for today (formatted like YYYY-MM-DD) from the “Products” table:
SELECT ProductName, Price, FORMAT(Now(),’YYYY-MM-DD’) AS PerDate
FROM Products;
