SQL commands

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.