Database management systems

1
Database management systems

• Data Manipulation Language

2
SQL

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY
• INSERT
• UPDATE
• DELETE

3
Structured Query Language

• GROUP BY ALL arranges rows into groupings based
  upon common values in a group for all columns in
  a table aggregation computations can then be
  performed
• GROUP BY ColumnName, ColumnName2 arranges rows
  into groupings, and subgroupings based upon
  common values in the designated column(s)
  aggregation computations can then be performed
• Aggregations include AVG, COUNT, MAX, MIN,
  STDEV, SUM
• GROUP BY COLUMNS (or column aggregations) must be
  included in the SELECT clause
• HAVING should be used to limit sets based upon
  GROUP BY aggregation results, rather than within
  the WHERE clause

4
Structured Query Language

• INSERT TableName VALUES (value1, value2)
  assigns values as one row in a 2-column table
• INSERT TableName (col003, col005) VALUES (value1,
  value2) assigns values to columns 3 and 5 in a
  multi-column table
• INSERT INTO works like SELECT INTO on an
  existing table
• UPDATE TableName SET ColumnName operand
• Operand can be value, variable, column or
  expression
• DELETE FROM TableName WHERE
• Operands, operators and operations are determined
  by the WHERE clause

5
Structured Query Language

• JOINS
• Crossjoin (a Cartesian Product) the number of
  rows produced will equal the product of the
  number of rows in each relation
• SELECT FROM AllData, AllData
• SELECT FROM AllData CROSS JOIN AllDATA
• Equijoin
• SELECT FROM AllData AS T1, AllData AS T2 WHERE
  T1.ProductStrength T2.Strength
• SELECT FROM AllDATA AS T1 INNER JOIN AllDATA AS
  T2 ON T1.ProductStrength T2.Strength

6
Structured Query Language

• Joins
• LEFT/RIGHT/FULL OUTER JOINS
• SELECT FROM AllData AS T1 RIGHT OUTER JOIN
  (SELECT DISTINCT RouteCode, RouteName FROM
  AllData) AS T2 ON T1.RouteCode T2.RouteCode
• SELECT FROM AllData AS T1 LEFT OUTER JOIN
  (SELECT DISTINCT RouteCode, RouteName FROM
  AllData) AS T2 ON T1.RouteCode T2.RouteCode
• SELECT FROM AllData AS T1 FULL OUTER JOIN
  (SELECT DISTINCT RouteCode, RouteName FROM
  AllData) AS T2 ON T1.RouteCode T2.RouteCode