Wednesday, October 2, 2019

SQL Commands list

The commands in SQL are called Queries and they are of two types
  • Data Definition Query: The statements which defines the structure of a database, create tables, specify their keys, indexes and so on
  • Data manipulation queries: These are the queries which can be edited.
E.g.: Select, update and insert operation

Basic Commands in SQL:

CommandSyntaxDescription
ALTER tableALTER TABLE table_name ADD column_name datatype;It is used to add columns to a table in a database
ANDSELECT column_name(s)FROM table_nameWHERE column_1 = value_1  AND column_2 = value_2;It is an operator that is used to combine two conditions
ASSELECT column_name AS ‘Alias’FROM table_name;It is an keyword in SQL that is used to rename a column or table using an alias name
AVGSELECT AVG(column_name)FROM table_name;It is used to aggregate a numeric column and return its average
BETWEENSELECT column_name(s)FROM table_nameWHERE column_name BETWEEN value_1 AND value_2;It is an operator used to filter the result within a certain range
CASESELECT column_name,CASEWHEN condition THEN ‘Result_1’WHEN condition THEN ‘Result_2’ELSE ‘Result_3’ENDFROM table_name;It is a statement used to create different outputs inside a SELECT statement
COUNTSELECT COUNT(column_name)FROM table_name;It is a function that takes the name of a column as argument and counts the number of rows when the column is not NULL
Create TABLECREATE TABLE table_name (  column_1 datatype,   column_2 datatype,   column_3 datatype);It is used to create a new table in a database and specify the name of the table and columns inside it
DELETEDELETE FROM table_nameWHERE some_column = some_value;It is used to remove the rows from a table
GROUP BYSELECT column_name, COUNT(*)FROM table_nameGROUP BY column_name;It is an clause in SQL used for aggregate functions in collaboration with the SELECT statement
HAVINGSELECT column_name, COUNT(*)FROM table_nameGROUP BY column_nameHAVING COUNT(*) > value;It is used in SQL because the WHERE keyword cannot be used in aggregating functions
INNER JOINSELECT column_name(s)FROM table_1JOIN table_2  ON table_1.column_name = table_2.column_name;It is used to combine rows from different tables if the Join condition goes TRUE
INSERTINSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, ‘value_2’, value_3);It is used to add new rows to a table
IS NULL/ IS NOT NULLSELECT column_name(s)FROM table_nameWHERE column_name IS NULL;It is an operator used with the WHERE clause to check for the empty values
LIKESELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern;It is a special operator used with the WHERE clause to search for a specific pattern in a column
LIMITSELECT column_name(s)FROM table_nameLIMIT number;It is a clause to specify the maximum number of rows the result set must have
MAXSELECT MAX(column_name)FROM table_name;It is a function that takes number of columns as an argument and return the largest value among them
MINSELECT MIN(column_name)FROM table_name;It is a function that takes number of columns as an argument and return the smallest value among them
ORSELECT column_nameFROM table_nameWHERE column_name = value_1   OR column_name = value_2;It is an operator that is used to filter the result set to contain only the rows where either condition is TRUE
ORDER BYSELECT column_nameFROM table_nameORDER BY column_name ASC | DESC;It is a clause used to sort the result set by a particular column either numerically or alphabetically
OUTER JOINSELECT column_name(s)FROM table_1LEFT JOIN table_2  ON table_1.column_name = table_2.column_name;It is sued to combine rows from different tables even if the condition is NOT TRUE
ROUNDSELECT ROUND(column_name, integer)FROM table_name;It is a function that takes the column name and a integer as an argument, and rounds the values in a column to the number of decimal places specified by an integer
SELECTSELECT column_name FROM table_name;It is a statement that is used to fetch data from a database
SELECT DISTINCTSELECT DISTINCT column_nameFROM table_name;It is used to specify that the statement is a query which returns unique values in specified columns
SUMSELECT SUM(column_name)FROM table_name;It is function used to return sum of values from a particular column
UPDATEUPDATE table_nameSET some_column = some_valueWHERE some_column = some_value;It is used to edit rows in a atble
WHERESELECT column_name(s)FROM table_nameWHERE column_name operator value;It is a clause used to filter the result set to include the rows which where the condition is TRUE
WITHWITH temporary_name AS (SELECT *FROM table_name)SELECT *FROM temporary_nameWHERE column_name operator value;It is used to store the result of a particular query in a temporary table using an alias

Commands and syntax for querying data from single table and multiple tables:

Single TableMultiple Table
SELECT c1 FROM t
To select the data in Column c1 from table t
SELECT c1, c2
FROM t1
INNER JOIN t2 on conditionSelect column c1 and c2 from table t1 and perform an inner join between t1 and t2
SELECT * FROM t
To select all rows and columns from table t
SELECT c1, c2
FROM t1
LEFT JOIN t2 on condition
Select column c1 and c2 from table t1 and perform a left join between t1 and t2
SELECT c1 FROM t
WHERE c1 = ‘test’
To select data in column c1 from table t, where c1=test
SELECT c1, c2
FROM t1
RIGHT JOIN t2 on condition
Select column c1 and c2 from table t1 and perform a right join between t1 and t2
SELECT c1 FROM t
ORDER BY c1 ASC (DESC)
To select data in column c1 from table t either in ascending or descending order
SELECT c1, c2
FROM t1
FULL OUTER JOIN t2 on condition
Select column c1 and c2 from table t1 and perform a full outer join between t1 and t2
SELECT c1 FROM t
ORDER BY c1LIMIT n OFFSET offset
To skip the offset of rows and return the next n rows
SELECT c1, c2
FROM t1
CROSS JOIN t2
Select column c1 and c2 from table t1 and produce a Cartesian product of rows in a table
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
To group rows using an aggregate function
SELECT c1, c2
FROM t1, t2Select column c1 and c2 from table t1 and produce a Cartesian product of rows in a table
SELECT c1, aggregate(c2)
FROM t
GROUP BY c1HAVING condition
Group rows using an aggregate function and filter these groups using ‘HAVING’ clause
SELECT c1, c2
FROM t1 A
INNER JOIN t2 B on condition
Select column c1 and c2 from table t1 and join it to itself using INNER JOIN clause

No comments:

Post a Comment

Get max value for identity column without a table scan

  You can use   IDENT_CURRENT   to look up the last identity value to be inserted, e.g. IDENT_CURRENT( 'MyTable' ) However, be caut...