SQL functions for data analysis

Important SQL functions

• Select – Selects data from a database
• Where- Filters a result set to include only records that fulfill a specified condition – Note: when working with string or date based fields, the value in the WHERE clause will need to be surrounded by single quotes.
• Like - Searches for a specified pattern in a column

Joins

• Inner Join-Returns rows that have matching values in both tables
• Left Join-Returns all rows from the left table, and the matching rows from the right table
• Right Join-Returns all rows from the right table, and the matching rows from the left table
• Full Outer Join- Returns all rows when there is a match in either left table or right table

Operators

• Equal/Not Equal - allows a single value to be specified in the WHERE clause -equal operator is used to check whether the given two expressions equal or not
• In/Not In- Allows  to specify multiple values in a WHERE clause
• And - Only includes rows where both conditions is true
• Or - Includes rows where either condition is true
• Order by - Sorts the result set in ascending or descending order – Default is ASC
• Case - Creates different outputs based on conditions
• Union-  Union is used to combine two record sets into one overall set

DDL Commands

• Create table - Creates a new table in the database
• Drop table - Deletes an existing table in the database
• Truncate table - Deletes the data inside a table, but not the table itself

DML Commands

• Insert - The insert command is used for inserting one or more rows into a database table with specified table column values
• Update - UPDATE statements are used to change the data in tables
• Delete - Deletes rows from a table
• Stored Procedure - A group of SQL statements put together in order to achieve a task within a database in a procedural manner.

Table and View

• Local temporary tables: Available to the current connection to the database for the current user and are dropped when the connection is closed  -  #local_temp
• Global temporary tables: Available to any connection once created, and are dropped when the last connection using it is closed - ##global_temp
• Permanent Table: Form part of the physical schema, available to any connection once created, and are NOT dropped automatically  - analysis.dbo.perm_temp

SQL Methods and functions

• NULL - The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank
• Aggregate functions – An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.
• Group by - Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
• Having- Used instead of WHERE with aggregate functions
• Unique- A constraint that ensures that all values in a column are unique

• Count - COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause
• Count Distinct – count distinct returns the number of distinct rows in a table satisfying the criteria specified in the WHERE clause
• Partition by - The PARTITION BY clause is a sub-clause of the OVER clause. The PARTITION BY clause divides a query's result set into partitions. The window function is operated on each partition separately and recalculate for each partition. 
•   Sub-queries - One can use sub-queries to query within a query, or join to the results of a query.
• Function reference- count, sum, min, max
• Date functions/variables
• Data types
• Numeric
• Date
• Character (String)
• Cast/Convert