SQL Queries: A Mind Map

This post focus on e using SELECT, JOINs and SUBQUERIES, CTE etc, have a look to the following mind map to connect together all this basic concept or the ER model…


1 select

SELECT [ ALL |DISTINCT] column_list FROM {table1 [tab_alias1] } ,… *** complete version SELECT select_list [INTO new_table_] FROM table [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ];

Subqueries(Relationship)
1.1 result set
The result of a SELECT statement is another table, also known as a result set.

1.2 alias
An alias is another name for the corresponding table

1.3 column_list
it can be: – The asterisk symbol (*) – The explicit specification of column names – The specification column_name [AS] column_heading – An expression – A system or an aggregate function

1.4 projection
A SELECT statement can retrieve either certain columns

1.5 selection
A SELECT statement can retrieve either certain rows

1.6 ex

 USE sample; SELECT dept_no, dept_name, location FROM department; 

1.7 WHERE Clause
to define one or more conditions that limit the selected rows

1.7.1 ex

USE sample; SELECT dept_name, dept_no FROM department WHERE location = 'Dallas';

1.7.2 Boolean Operators
ex

USE sample; SELECT project_no, emp_no FROM works_on WHERE project_no = 'p1' OR project_no = 'p2';

NOT operation
IN and BETWEEN Operators
ex

 USE sample; SELECT emp_no, emp_fname, emp_lname FROM employee WHERE emp_no IN (29346, 28559, 25348); 
 USE sample; SELECT project_name FROM project WHERE budget NOT BETWEEN 100000 AND 150000; 

1.7.3 NULL Values
ex

 USE sample; SELECT emp_no, project_no FROM works_on WHERE project_no = 'p2' AND job IS NULL; 

1.7.4 LIKE Operator
used for pattern matching The general form of the LIKE operator is column [NOT] LIKE ‘pattern’

pattern
Two of them are % (percent sign) Specifies any sequence of zero or more characters _ (underscore) Specifies any single character The condition column NOT LIKE ‘pattern’ is equivalent to the condition NOT (column LIKE ‘pattern’)

ex

 SE sample; SELECT emp_fname, emp_lname, emp_no FROM employee WHERE emp_fname LIKE '_a%'; 

(%, _, [, ], or ^)
ex

 USE sample; SELECT * FROM department WHERE location LIKE '[C-F]%'; 

ESCAPE option
1.8 DISTINCT
the DISTINCT option can be used only once in a SELECT list, and it must precede all column names in that list

1.9 GROUP BY Clause
it defines one or more columns as a group such that all rows within any group have the same values for those columns N> Each column appearing in the SELECT list of the query must also appear in the GROUP BY clause. This restriction does not apply to constants and to columns that are part of an aggregate function

1.9.1 ex

 USE sample; SELECT job FROM works_on GROUP BY job; 
 USE sample; SELECT project_no, job FROM works_on GROUP BY project_no, job; 

1.10 Aggregate Functions
they are functions that are used to get summary values

1.10.1 Convenient aggregate functions
they operate on a single argument, which can be either a column or an expression

MAX
MIN
ex

 USE sample; SELECT emp_lname, MIN(emp_no) FROM employee; 

N> it’s wrong add the GROUP BY on emp_lname

SUM

ex

 SELECT SUM (budget) sum_of_budgets FROM project GROUP BY(); N> use the explicit syntax 

DISTINCT
AVG
NULL
COUNT
COUNT ([DISTINCT] col_name) COUNT (*)

ex

USE sample; SELECT project_no, COUNT(DISTINCT job) job_count FROM works_on GROUP BY project_no; 

N> all NULL values are eliminated

 USE sample; SELECT job, COUNT(*) job_count FROM works_on GROUP BY job; 

N> it does not eliminate NULL values, because this function operates on rows and not on columns

COUNT_BIG
1.10.2 Statistical aggregate functions
VAR
STDEV
1.10.3 Analytic aggregate functions
1.10.4 User-defined aggregate functions
to support the implementation of user-defined aggregate functions.

1.10.5 HAVING Clause
HAVING condition it defines the condition that is then applied to groups of rows. N> Columns with the TEXT (or IMAGE) data type cannot be used with the HAVING clause.

ex

 USE sample; SELECT project_no FROM works_on GROUP BY project_no HAVING COUNT(*) < 4; 

1.10.6 COMPUTE Clause it calculates summary values that appear as additional rows in the result N> The result of a COMPUTE clause is not a table: it is a report N>Use the GROUP BY ROLLUP statement instead

BY option
BY column_name specifies that the values of the column_ name column are used to build groups

1.11 ORDER BY Clause
ORDER BY {[col_name | col_number [ASC | DESC]]} , … it defines the particular order of the rows in the result of a query.

1.11.1 ex

 USE sample; SELECT project_no, COUNT(*) emp_quantity FROM works_on GROUP BY project_no ORDER BY 2 DESC 

1.12 IDENTITY Property
SET IDENTITY_INSERT table_name ON N>IDENTITY does not generally enforce uniqueness. Use the UNIQUE or PRIMARY KEY constraint for this task (due to previous)

1.12.1 ex

 USE sample; CREATE TABLE product (product_no INTEGER IDENTITY(10000,1) NOT NULL, product_name CHAR(30) NOT NULL, price MONEY); SELECT IDENTITYCOL FROM product WHERE product_name = 'Soap'; 

1.12.2 IDENTITYCOL
1.12.3 IDENT_SEED and IDENT_INCR
1.13 Set Operators
the INTERSECT operator has the highest priority, EXCEPT is evaluated next, and the UNION operator has the lowest priority

1.13.1 UNION
select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}… N> SELECT lists must have the same number of columns, and the corresponding columns must have compatible data types

ALL
ALL means that duplicates are not removed from the result set

1.13.2 INTERSECT
intersection of rows

ex

 USE sample; SELECT emp_no FROM employee WHERE dept_no = 'd1' INTERSECT SELECT emp_no FROM works_on WHERE enter_date < '01.01.2008'; 

1.13.3 EXCEPT difference operator

2 Subqueries

Transact-SQL language offers the ability to compare column values with the result of another SELECT statement. when one or more SELECT statements are nested in the WHERE clause of another SELECT statement N> Almost all SELECT statements that join tables and use the join operator can be rewritten as subqueries and vice vers

2.1 outer query
The first SELECT statement of a subquery, the outer query receives the values of the inner query.

2.2 inner query
2.3 Self-contained
2.3.1 Comparison operators
Any comparison operator can be used, provided that the inner query returns exactly one row

ex

 USE sample; SELECT emp_fname, emp_lname FROM employee WHERE dept_no = (SELECT dept_no FROM department WHERE dept_name = 'Research'); 

2.3.2 IN operator
when the result of an inner query contains a set of values

ex

 USE sample; SELECT * FROM employee WHERE dept_no IN (SELECT dept_no FROM department WHERE location = 'Dallas'); 

2.3.3 ANY or ALL operator
column_name operator [ANY | ALL] query The ANY operator evaluates to true if the result of the corresponding inner query contains at least one row that satisfies the comparison

ex

 USE sample SELECT DISTINCT emp_no, project_no, job FROM works_on WHERE enter_date > ANY (SELECT enter_date FROM works_on) 

tN> condition rue for all but the earliest value of the enter_date column.

2.4 Correlated
if the inner query depends on the outer query for any of its values

2.4.1 ex

 USE sample; SELECT emp_lname FROM employee WHERE 'p3' IN (SELECT project_no FROM works_on WHERE works_on.emp_no = employee.emp_no); 

2.5 EXISTS Function
it akes an inner query as an argument and returns true if the inner query returns one or more rows, and returns false if it returns zero rows.

2.5.1 ex

 USE sample; SELECT emp_lname FROM employee WHERE EXISTS (SELECT * FROM works_on WHERE employee.emp_no = works_on.emp_no AND project_no = 'p1'); 

2.6 Table Expressions
Table expressions are subqueries that are used where a table is expected

2.6.1 Derived tables
a table expression that appears in the FROM clause of a query

ex

 USE sample; SELECT enter_month FROM (SELECT MONTH(enter_date) as enter_month FROM works_on) AS m GROUP BY enter_month; 

2.6.2 Common table expressions
a named table expression that is supported by Transact-SQL

Nonrecursive queries
WITH cte_name (column_list) AS ( inner_query) outer_query

ex

 USE AdventureWorks; WITH price_calc(year_2002) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = '2002') SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT year_2002 FROM price_calc) AND Freight > (SELECT year_2002 FROM price_calc)/2.5; 

Recursive queries
WITH cte_name (column_list) AS (anchor_member UNION ALL recursive_member) outer_query

3 CASE Expressions

3.1 Simple CASE expression
CASE expression_1 {WHEN expression_2 THEN result_1} … [ELSE result_n] END

3.2 Searched CASE expression
CASE {WHEN condition_1 THEN result_1} … [ELSE result_n] END If none of the WHEN conditions evaluates to true, the value of the ELSE expression is returned

3.2.1 ex

USE sample; SELECT project_name, CASE WHEN budget > 0 AND budget < 100000 THEN 1 WHEN budget >= 100000 AND budget < 200000 THEN 2 WHEN budget >= 200000 AND budget < 300000 THEN 3 ELSE 4 END budget_weight FROM project;

4 Temporary Tables

4.1 Local temporary tables reated it and is visible only to that session

4.1.1 tempdb

4.1.2 #table_name

4.1.3 ex

 USE sample; SELECT project_no, project_name INTO #project_temp FROM project; 

4.2 Global temporary tables deleted when all users that are referencing the table disconnect from the database server. 4.2.1 prefix ##

5 Join Operator

the most important operator for relational database systems N>the join operator “joins” columns of tables.

5.1 Natural join
5.2 Cartesian product (cross join)
5.3 Outer join
5.4 Theta-join, self-join and semi-join
5.5 Syntax
5.5.1 Explicit join syntax
using the corresponding name for each type of the join operation N>Use of the explicit join syntax is recommended. N>Database Engine requires that the corresponding join columns have compatible data types

CROSS JOIN
Cartesian product of two tables

ex

 USE sample; SELECT employee.*, department.* FROM employee CROSS JOIN department; 

[INNER] JOIN
natural join of two tables

natural join
equi-join
ex

 USE sample; SELECT employee.*, department.* FROM employee INNER JOIN department ON employee.dept_no = department.dept_no; -- “Old-style” join syntax: USE sample; SELECT employee.*, department.* FROM employee, department WHERE employee.dept_no = department.dept_no; 

Joining More Than Two Tables
f you join n tables, you need n – 1 join conditions to avoid a Cartesian product

ex

 USE sample; SELECT emp_fname, emp_lname FROM works_on JOIN employee ON works_on.emp_no=employee.emp_no JOIN department ON employee.dept_no=department.dept_no AND location = 'Seattle' AND job = 'analyst'; 

Self-Join
a natural join operation can also be applied to a single table

ex

 USE sample; SELECT t1.dept_no, t1.dept_name, t1.location FROM department t1 JOIN department t2 ON t1.location = t2.location WHERE t1.dept_no <> t2.dept_no; 

[OUTER] JOIN
Sometimes it is necessary to retrieve, in addition to the matching rows, the unmatched rows from one or both of the tables N>Every outer join operation can be simulated using the UNION operator plus the NOT EXISTS function

LEFT
RIGHT
FULL
ex

 USE sample; SELECT employee_enh.*, department.location FROM employee_enh JOIN department ON domicile = location; 

Qualifying Columns
table_name.column_name. Qualifying” a column name means that, to avoid any possible ambiguity about which table the column belongs to,

Theta Join
using a comparison operator other than equality

ex

 USE sample; SELECT emp_fname, emp_lname, domicile, location FROM employee_enh JOIN department ON domicile < location; 

5.5.2 Implicit join syntax
is defined implicitly via the WHERE clause

For the previous one check
https://mamatucci.wordpress.com/2011/06/29/sql-ddl-a-mind-map/

I hope it will help you 8-P

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s