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
For SQL Server
SELECT Upper(column_name) FROM table_name;
L Case ans: The LCASE() function converts the value of a field to lowercase.
Select lcase(customername) as Lowercustomer,address From Customers;
For SQL Server
SELECT LOWER(column_name) FROM table_name;
Mid() ans: MID() function is used to extract characters from a text field.
SELECT MID(column_name,start[,length]) AS some_name FROM table_name;
The equivalent function for SQL Server is SUBSTRING():
SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;
n_name -Required-The field to extract characters from
2.Start -Required. Specifies the starting position (starts at 1)
3.Length -Optional. The number of characters to return. If omitted, the MID() function returns the rest of the text
In SQL Server
Note: The equivalent function for SQL Server is SUBSTRING():
SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;
Len() ans: LEN() function returns the length of the value in a text field.
SQL LEN() Syntax
SELECT LEN(column_name) FROM table_name;
Round() ans: SELECT ROUND(column_name,decimals) FROM table_name;
n_name Required. The field to round.
als Required. Specifies the number of decimals to be returned.
NOw() ans: The NOW() function returns the current system date and time.
Select CustomerName,Proce,Now() as Today'sdate from Column_Name;
Fo9rmat() ans: To display a column data in a specific format as specified in the syntax
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
Distinct ans: TO IDENTIFY THE DISTINCT VALUES IN A COLUMN
SELECT DISTINCT COLUMN_NAME,COLUMN_NAME2
FROM TABLE NAME
WHERE ans: SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN NAME='XYZ';
AND ans: SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN NAME='XYZ' AND COLUMN='ABC';
OR ans: SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN NAME='XYZ' OR COLUMN='ABC';
----
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN NAME='XYZ' AND
(COLUMN_NAME2='ABC' OR 'DEF');
OPERATORS ans: = Equal
Not equal.
Note: In some versions of SQL this operator may be written as !=
Greater than
Less than
= Greater than or equal
= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
ORDER BY ans: SELECT COLUMN NAME,COLUMN_NAME2,COLUMN_NAME3
FROM TABLE NAME
ORDERBY COLUMN_NAME1,COLUMN_NAME2 ASC/DESC
ASC/DESC-ASCENDING OR DESCENDING
INSERT INTO ans: To add new records to columns in table
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Update ans: UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
DELETE ans: To remove records from a table
DELETE FROM table_name
WHERE some_column=some_value;
To remove all records
Delete * from Table_Name;
SQL INJECTION ans: It is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.
Injected SQL commands can alter SQL statement and compromise the security of a web application.
Parameters for Protection ans: Some web developers use a "blacklist" of words or characters
to search for in SQL input, to prevent SQL injection attacks
Cant avoid the common syntax used in SQL as a Blacklist as then it will hamper the normal usage of the syntax to fetch data
The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.
SELECT TOP ans: SELECT TOP number|percent column_name(s)
FROM table_name;
** DONT USE THE SIGN % INSTEAD USE THE DESCRIPTION 'PERCENT'
SELECT column_name(s)
FROM table_name
LIMIT number;
LIKE ans: WHERE TO9 FETCH DATA OF A SPECIFIC PATTERN IN A COLUMN
SELECT * FROM Customers
WHERE City LIKE 's%';
a)when beg9ins with a specific letter: use 'letter%'-'a%'
b)when ends with specific letter/sign :use 'letter/sign%'-'%a'
c)when letter or sign in the content on the column then use :'%letter/sign%'-'%land%'
NOT LIKE ans: TO SELECT RECORDS THAT ARE NOT LIKE THE PATTER MENTIONED.. MEANING EXCLUDING THE RECORDS STATED WITH THE PATTERN MENTIONED.
SELECT * FROM Customers
WHERE City NOT LIKE 's%';
This will list the records from the table customers where the records are not following the pattern of City name beginning with s.So will list the records of customers with city name otherthan beginning with s.
Wildcards ans: A wildcard character can be used to substitute for any other character(s) in a string.
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist]
or
[!charlist] Matches
IN ans: The IN operator allows you to specify multiple values in a WHERE clause
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
Between ans: The BETWEEN operator is used to select values within a range.
Vista previa del contenido
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