Database Administration Fundamentals (WS18SQL1002) – Day 10

Download the class notes for day 10 (quiz3 & quiz3b).

  -- Just for fun, read and run or run and read.

  DECLARE @yourName VARCHAR(50) = 'your_name';
  DECLARE @CourseCd VARCHAR(15) = 'WS18SQL1002';

  PRINT CONCAT(CHAR(084),CHAR(104),CHAR(097),CHAR(110),
     CHAR(107),CHAR(032),CHAR(121),CHAR(111),CHAR(117),
     CHAR(044),CHAR(032),@yourName,CHAR(044),CHAR(032),
     CHAR(102),CHAR(111),CHAR(114),CHAR(032),CHAR(116),
     CHAR(097),CHAR(107),CHAR(105),CHAR(110),CHAR(103),
     CHAR(032),CHAR(099),CHAR(108),CHAR(097),CHAR(115),
     CHAR(115),CHAR(032),@CourseCd,CHAR(046),CHAR(013),
     CHAR(083),CHAR(101),CHAR(101),CHAR(032),CHAR(121),
     CHAR(111),CHAR(117),CHAR(032),CHAR(105),CHAR(110),
     CHAR(032),CHAR(116),CHAR(104),CHAR(101),CHAR(032),
     CHAR(105),CHAR(110),CHAR(116),CHAR(101),CHAR(114),
     CHAR(109),CHAR(101),CHAR(100),CHAR(105),CHAR(097),
     CHAR(116),CHAR(101),CHAR(032),CHAR(099),CHAR(108),
     CHAR(097),CHAR(115),CHAR(115),CHAR(046),CHAR(013),
     CHAR(013),CHAR(070),CHAR(046),CHAR(079),CHAR(108),
     CHAR(118),CHAR(101),CHAR(114),CHAR(097),CHAR(013),
     CHAR(102),CHAR(111),CHAR(108),CHAR(118),CHAR(101),
     CHAR(114),CHAR(097),CHAR(064),CHAR(098),CHAR(109),
     CHAR(099),CHAR(099),CHAR(046),CHAR(099),CHAR(117),
     CHAR(110),CHAR(121),CHAR(046),CHAR(101),CHAR(100),
     CHAR(117),CHAR(013),CHAR(104),CHAR(116),CHAR(116),
     CHAR(112),CHAR(058),CHAR(047),CHAR(047),CHAR(102),
     CHAR(111),CHAR(108),CHAR(118),CHAR(101),CHAR(114),
     CHAR(097),CHAR(046),CHAR(099),CHAR(111),CHAR(109),
     CHAR(109),CHAR(111),CHAR(110),CHAR(115),CHAR(046),
     CHAR(103),CHAR(099),CHAR(046),CHAR(099),CHAR(117),
     CHAR(110),CHAR(121),CHAR(046),CHAR(101),CHAR(100),
     CHAR(117),CHAR(047));      -- characters in ASCII

Database Administration Fundamentals (WS18SQL1002) – Day 6

We have started to CREATE database objects.

  CREATE object_type object_name
  [AS structure]

So far we are making (CREATE),

  CREATE TABLE table_name
  (
    field1 datatype [null|not null] [unique] [primary key],
    field2 datatype [null|not null],
    ...
  )

modifying (ALTER)

  ALTER TABLE table_name
  ADD|DROP|ALTER COLUMN field_name datatype

and destroying (DROP) tables.

  DROP TABLE table_name

Download the class notes for day 6.

Database Administration Fundamentals (WS18SQL1002) – Day 5

We have been working with WHERE to limit our output.

  SELECT table1.field1, table1.field2...
    table2.field1, table2.field2...
  FROM table1
  INNER|LEFT|RIGHT JOIN table2
    ON table1.shared_field1 = table2.shared_field1
  WHERE table1.field2 = some_value;

We have also seen the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.

Download the class notes for day 5.

Database Administration Fundamentals (WS18SQL1002) – Day 4

We are almost half way done with the course. We have covered how to retrieve data (SELECT), filtering data (WHERE), organizing data (ORDER BY), grouping data (GROUP BY) when using aggregate functions and formatting numeric values (FORMAT()).

SELECT table1.field1, table1.field2...
  table2.field1, table2.field2...
  table3.field1, table3.field2...
FROM table1
INNER|LEFT|RIGHT JOIN table2
  ON table1.shared_field1 = table2.shared_field1
    AND table1.shared_field2 = table2.shared_field2
INNER|LEFT|RIGHT JOIN table3
  ON table1.shared_field1 = table3.shared_field1
    AND table1.shared_field2 = table3.shared_field2...
WHERE condition1
  AND|OR condition2
  ...

Download the class notes for day 4.