Please enable JavaScript.
Coggle requires JavaScript to display documents.
SELECT DML, WHERE - Some not all data, Operators
& Expressions, JOINS…
SELECT DML
Retrieve
-
Column lists, use comma separated
-
-
-
-
Operators
& Expressions
-
-
-
-
Logical
(NOT, AND, OR, ANY, ALL EXISTS)
-
-
JOINS
-
-
-
Use with WHERE, ORDER BY & DISTINCT
-
-
-
-
-
-
String
Concatenation
-
-
If they are different, use CONVERT to make INT a CHAR
-
-
-
LEFT OUTER JOIN
-
-
-
Match rows using inner join, even if there unknown values
RIGHT OUTER JOIN
-
-
-
Match rows using inner join, even if there unknown values
SELECT SYNTAX
-
-
SELECT <col>, <col1>, <col2>,.. FROM <table>
SELECT, FROM, WHERE, ORDER BY
-
-
INNER JOIN
Combine multiple inner joins,
-
-
UPDATE SYNTAX
UPDATE <tableName>
SET <ColumnName> = Some Value, <ColumnName> = Some Value,<ColumnName> = Some Value
SELECT @@rowcount As Rows, @@error As Errors
@@error gives you the error number if one exists.
@@rowcount gives the no. of rows that were changed
DELETE SUBQUERY
SELECT * FROM tbl1 WHERE Column IN
(SELECT DISTINCT <col>
FROM tbl1 as x
INNER JOIN tbl2 as y
ON x.keycolumn = y.keycolumn
WHERE <col> BETWEEN
'01-mm-yyyy' AND '31-mm-yyyy')
SELECT @@rowcount AS Rows, @@error as Error;
-
-
-
-
-
TRANSACTIONAL SQL
BEGIN TRAN
Delete FROM tbl WHERE condition
Select FROM tbl (check delete command)
ROLLBACK TRAN or COMMIT TRAN
if the delete operation was correct
-
INNER JOIN
SELECT * FROM <tbl1> INNER JOIN <tbl2> ON <tbl1.col> = <tbl2.col> INNER JOIN<tbl3> ON <tbl2.col> = <tbl3.col> INNER JOIN<tbl4> ON <tbl3.col> = <tbl4.col>
-
MANIPULATE DATA
SELECT
-
-
Some data not all, apply Where clause
Bitwise
SELECT 5 % 2
gives remainder after dividing by 2
5 divide by 2 = 2, remainder 1
Add DML
INSERT
Zero or more columns, you can provide default value
Column list, use comma delimited lists
Nulls are accepted, if column property allows Null
-
-
INSERT SYNTAX
INSERT INTO <tbl> (col1, col2, col3,..)
VALUES (value list, with, comma separation)
Change DML
UPDATE
-
-
Without WHERE, UPDATES changes every row
-
Remove DML
DELETE
Removes records from one, & only one table
-
-
-
Cannot delete a parent row, child tables are dependent
-
-
-