SQL commands QUESTIONS AND ANSWERS

SQL commands
AVG() ans: SELECT AVG(COLUMN NAME) FROM TABLE_NAME
Using it in WHERE CLAUSE so a different form of presentation:
TO LIST ONLY THOSE PRODUCTS AND PRICES WHERE THE AVERAGE IS MORE THAN A SPECIFIC
AMOUNT:-
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

COUNT() ans: SELECT COUNT(*) FROM TABLE_NAME
(to select all the NON null values of records in the table
SELECT COUNT(Column Name) FROM TABLE_NAME
(to count all values in a column)
SELECT COUNT(Distinct Column Name) FROM TABLE_NAME
(to count all distinct values in a column)
SELECT COUNT(Distinct Column Name) as NEWNAME FROM TABLE_NAME
(to count all distinct values in a column and label the column with a new name)

COUNT DISTINCT VALUES ans: SQL COUNT(DISTINCT column_name) Syntax
SELECT COUNT(DISTINCT COLUMN NAME) FROM TABLE_NAME
*** Distinct command doesn't work in MS access.Works in SQL and Oracle)***

FIRST() ans: To return the first value of a column.
SELECT FIRST(column_name) FROM table_name;
***ONLY IN ACCESS***

Last() ans: To return the last value of a column
SELECT LASt(column_name) FROM table_name;

AGGREGATE FUNCTIONS ans: •AVG() - Returns the average value
•COUNT() - Returns the number of rows
•FIRST() - Returns the first value
•LAST() - Returns the last value
•MAX() - Returns the largest value
•MIN() - Returns the smallest value
•SUM() - Returns the sum

Scalar Functions ans: •UCASE() - Converts a field to upper case
•LCASE() - Converts a field to lower case
•MID() - Extract characters from a text field
•LEN() - Returns the length of a text field
•ROUND() - Rounds a numeric field to the number of decimals specified
•NOW() - Returns the current system date and time
•FORMAT() - Formats how a field is to be displayed

, Max() ans: Return the max value from a column
SELECT MAX(Price) AS HighestPrice FROM Products;

Min() ans: Return the min value from a column
SELECT MIN(Price) AS HighestPrice FROM Products;

Sum() ans: Return the sum total of values from a column
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

group by ans: The GROUP BY statement is used in conjunction with the aggregate functions to group the
result-set by one or more columns.

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

>GROUP BY MORE THAN ONE COLUMN

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

Having Clause ans: HAVING clause was added to SQL because the WHERE keyword could not be used
with aggregate functions
>
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
>if LOOKING OUT FOR INFO ON SPECIFIC ENTRY
to identify if a specific employee satisfies the criteria
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
where lastname="XXX" or Lastname="YYY"
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

U Case ans: The UCASE() function converts the value of a field to uppercase.
Select Ucase(Customername) as Customers,Address FROM Table_Name