Open Source

Database Administration Fundamentals (WS18SQL1001) – Day 3

Francisco Olvera

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

Francisco Olvera

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

Francisco Olvera

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.

Yet Another Reason to Learn SQL

Francisco Olvera

If you are interested in learning how to program for Android or other mobile system, you might need to manage data and that is where SQL comes in handy — especially SQLite.

In Android development, we tend to use SQLite which is ideal for mobile applications and particularly useful for permanently storing data. MySQL is more commonly installed on servers where it can be used by web apps. Either way, using databases to store information allows us to organize that data in a far more logical and structured manner, and then access and manipulate that information dynamically.
https://www.androidauthority.com/sql-android-app-development-812969/

Database Administration Fundamentals (SF17SQL1001) – Day 8

Francisco Olvera

We are almost done with the course. This means that we have one more day to learn new material and review all the material. Then we take care of our final (just another lab, but a tad longer).

In the meantime, we have covered how to CREATE and ALTER database objects — DATABASEs, SCHEMAs, TABLEs, VIEWs and COLUMNs.

CREATE object_type object_name
  [code_to_create_object]

DROP object_type object_name
  [code_to_drop_object]

ALTER object_type object_name
  ADD/DROP/ALTER object_type
  [code_to_alter_object]

Download the class notes for day 8.