Database Administration Fundamentals (WS18SQL1001) – Day 5

We are half-way done with the course. We have covered how to retrieve data from table(s) and how to present that data in comprehensible manner.

SELECT [DISTINCT] table1.field1,
  table1.field2,
  ...
  table2.field1,
  table2.field2,
  ..
FROM table1
INNER|LEFT JOIN table2
  ON table1.sharedfile1 = table2.sharedfile1
    AND|OR table1.sharedfile2 = table2.sharedfile3
    ...
INNER|LEFT JOIN table3
  ON table1.sharedfile1 = table3.sharedfile1
    AND|OR table1.sharedfile2 = table3.sharedfile3
    ...
WHERE condition1
  AND|OR condition2
  ...
GROUP BY fields_not_in_aggregate_function
ORDER BY field1,
  field2
  ...;

Download the notes for day 5.

Database Administration Fundamentals (WS18SQL1001) – Day 3

We have started having fun with built-in functions (set of instructions) for numeric values.

  1. SUM() — total value of row
  2. AVG() — average value of a row
  3. COUNT() — how many records
  4. ROUND() — rounding a number to a specific decimal point (decimal, float, double, money…)
  5. CEILING() — rounding up to the closest whole number value (integer)
  6. FLOOR() — rounding down to the closest whole number value (integer)
  7. MAX() — highest value
  8. MIN() — lowest value
  9. RAND() — random value
  10. FORMAT()“dress” value for date or currency ($5.00, 2/14/2018, 14-FEB-18); outputs VARCHAR(n)

Download the notes for day 3.

Database Administration Fundamentals (WS18SQL1001) – Day 2

We have started having fun with built-in functions (set of instructions) for strings (character arrays).

  1. UPPER() — upper case
  2. LOWER() — lower case
  3. RTRIM() — trim spaces on the right
  4. LTRIM() — trim spaces on the left
  5. RIGHT() — part of a string starting on the right
  6. LEFT() — part of a string starting on the left
  7. SUBSTRING() — part of a string in the middle
  8. LEN() — length of a string
  9. REPLACE() — to replace part of a string

Download the notes for day 2.

Database Administration Fundamentals (WS18SQL1001) – Day 1

We have started a new course. Always keep in mind that the heart of SQL is the SELECT statement.

SELECT table1.field1,
  table1.field2,
  table2.field1,
  table2.field2 ...
FROM table1
INNER/LEFT/RIGHT JOIN table2
  ON table1.common_field1 = table2.common_field1
    AND table1.common_field2 = table2.common_field2

Download the notes for day 1.