Download the class notes for day 10 (the final). Thank you for taking this course.
2018/02/05 WS18SQL1001
Database Administration Fundamentals (WS18SQL1001) – Day 9
We have covered how to CREATE, DROP and ALTER data objects — especially tables adding constraints like PK, FK and CPK.
Download the notes for day 9.
Get ready for the final!
Database Administration Fundamentals (WS18SQL1001) – Day 8
We are finally in the fun section of the course — stored procedures (UDP) and user-defined functions (UDF). We can finally write code once and recycle it.
Download the notes for day 8.
Database Administration Fundamentals (WS18SQL1001) – Day 7
We have only three (3) more days. We have covered how to CREATE, DROP and ALTER data objects (databases, schemas, tables and views) with the correct data types.
Download the notes for day 7.
Database Administration Fundamentals (WS18SQL1001) – Day 6
We have finished section one (retrieving data) and have started section two (creating, destroying and modifying data objects).
CREATE object_type object_name AS ( some_structure );
DROP object_type object_name;
ALTER object_type object_name ADD|DROP|ALTER object_type data_type;
Download the notes for day 6.
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 4
Database Administration Fundamentals (WS18SQL1001) – Day 3
We have started having fun with built-in functions (set of instructions) for numeric values.
- SUM() — total value of row
- AVG() — average value of a row
- COUNT() — how many records
- ROUND() — rounding a number to a specific decimal point (decimal, float, double, money…)
- CEILING() — rounding up to the closest whole number value (integer)
- FLOOR() — rounding down to the closest whole number value (integer)
- MAX() — highest value
- MIN() — lowest value
- RAND() — random value
- 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).
- UPPER() — upper case
- LOWER() — lower case
- RTRIM() — trim spaces on the right
- LTRIM() — trim spaces on the left
- RIGHT() — part of a string starting on the right
- LEFT() — part of a string starting on the left
- SUBSTRING() — part of a string in the middle
- LEN() — length of a string
- 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.