Understanding Structured Query Language fundamentals

SQL Fundamentals simplified

1. SQL FUNDAMENTALS -Ankit Rao

2. SQL Joins

3. SQL Sub-queries SQL subquery is usually added in the  WHERE   clause of the SQL statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database. 1) Corelated subqueries : If there are dependencies between the main and the sub-queries , then they are termed as co-related sub-queries. 2) Non- corelated subqueries 3) Nested subqueries 4) Subqueries in insert statements(Insert into) 5) Subquery as a output column in select statements. 6) Sub-queries used with ‘IN’ and ‘EXISTS’

4. SQL UNION Operator The SQL UNION operator combines the result of two or more SELECT statements. Syntax: SELECT  column_name (s)  FROM  table1 UNION/UNION ALL SELECT  column_name (s)  FROM  table2 Key points to be kept in mind: 1)Each SELECT statement within the UNION must have the same number of columns. 2) The columns must also have similar data types. 3)The columns in each SELECT statement must be in the same order.

5. UNION/UNION ALL: The UNION operator selects only distinct values by default. To allow duplicate values, UNION ALL is used. So naturally, UNION ALL runs in lesser time as no comparisons for similar data is done. USAGE TIPS: Unnecessary unions are always to be avoided as they consume more resources and take more time to execute thereby. Joins should always be preferred to the most possible extent and if not joins, sub-queries can be used. Union all should be preferred over union if data redundancy is not a major issue.

6. SQL GROUP BY The GROUP BY statement is used in along with the aggregate functions to group the result-set by one or more columns. SQL HAVING Clause: It was added as where clause cannot be used on aggregate functions. Syntax: SELECT column_name , aggregate_function ( column_name ) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function ( column_name ) operator value; All other non-aggregate fields in the select statement have to be included in the group by clause