K33pdQQT

1      "Topic Two: Advanced SQL\n\n\n\n"
2"SQL: DML (Data Manipulation Language)\n\n\n"
3
4"Objectives of SQL:\n\n\n"
5"A database language allows a user to create a database with relational structures where the data can be modified.\n\n"
6"It has two components, DML for retrieving/updating data, and DDL for defining database structure.\n\n"
7"SQL is non-procedural, meaning you specify what info you need, not how to get it. \n\n"
8"Follows an ISO standard\n\n"
9"FROM, WHERE, GROUP BY, HAVING, SELECT and ORDER BY statements\n\n\n"
10
11"SELECT Statements – Aggregates\n\n\n"
12"Five aggregate functions:\n\n"
13"COUNT: returns number of values in column\n\n"
14"SUM: returns sum of values in column\n\n"
15"AVG: returns average of values in column\n\n"
16"MIN: returns smallest value in column\n\n"
17"MAX: returns largest value in column\n\n\n"
18
19"Aggregates operate on a single column of a table and returns one value. Except for COUNT(*), "
20"each function eliminates nulls first and operates only on remaining non-null values. The use of DISTINCT before a column name eliminates duplicates.\n\n"
21"They can only be used in SELECT list and in HAVING clause\n\n\n"
22
23"SELECT Statement – Grouping\n\n\n"
24"GROUP By clause gets sub-totals\n"
25"SELECT and GROUP BY are closely integrated, each item in SELECT list must be single-valued per group, "
26and SELECT clause may only contain column names, aggregate functions, constants and expressions involving combinations of the above. \n\n\n"
27
28"Restricted Groupings – HAVING CLAUSE\n\n\n"
29"Designed for use with GROUP BY to restrict groups that appear in result table. Similar to WHERE, "
30"but WHERE filters individual rows whereas HAVING filters groups. "
31"Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function. \n\n\n"
32
33"Subqueries:\n\n\n"
34"Some SQL statements can have a SELECT embedded in them. A sub select can be used in WHERE and HAVING clauses of an outer SELECT, "
35"where its called a subquery or nested query. Sub selects may also appear in INSERT, UPDATE, and DELETE statements. \n\n\n"
36
37"Multi-Table Queries:\n\n\n"
38"Use of subqueries provided result columns come from the same table. If result columns come from more than one table, "
39"then you must use a Join. To do a Join, you must include more than one table in the FROM clause. "
40"You use comma as a separator and typically include WHERE clause to specify Join columns. \n\n\n"
41
42"EXISTS and NOT EXISTS\n\n\n"
43"For use only with subqueries. They produce a simple true/false result. True only if there is at least one row in the result table "
44"returned by the subquery, false if subquery returns empty result table.\n\n\n"
45
46
47"SQL: DDL (Data Definition Language):\n\n\n"
48
49"ISO SQL Data Types:\n\n\n"
50
51"IMAGE?\n\n\n"
52
53"Integrity Constraints:\n\n\n"
54"1.	Required data (required fields to not be empty (not NULL))\n"
55"2.	Domain constraints (required field entries to be of a certain domain)\n"
56"3.	Entity integrity (Unique Primary Key)\n"
57"4.	Referential integrity (Foreign Key has a matching Primary key)\n"
58"5.	General constraints\n\n\n"
59
60"Data Definition\n\n\n"
61"SQL DDL allows database objects like schemas, domains, tables, views, and indexes to be created and destroyed. \n\n"
62"Relations and other database objects exist in an environment. Each environment has one or more catalogs, "
63"where each catalog has a set of schemas. A schema is a named collection of related database objects. "
64"Objects in a schema can be tables, views, domains, assertions, collations, translations, and character sets.\n\n\n"
65
66"Views\n\n\n"
67"Views\n\n\n"
68"-	IMAGE?\n\n\n"
69
70"Views are the dynamic result of one or more relational operations operating on base relations to produce another relation. "
71"Virtual relation that doesn’t necessarily exist in the database but is produced upon request, at time of request. \n\n"
72
73"Contents of a view are defined as a query one plus base relations. With view resolution, "
74"any operations on the view are automatically translated into operations on relations from which its derived. "
75"With view materialisation, the view is stored as a temporary table, which is maintained even if the underlying base tables are updated. \n\n\n"