We are almost done with the course. We started to

1. CREATE, DROP and ALTER database objects
2. and INSERT and UPDATE values into tables.

We also covered how to create a table from another table — including `CREATE SELECT` also referred to as `CREATE TABLE AS` that T-SQL does not support.

Download the class notes for day 7.

Last night, aside from INNER JOIN that allows us to retrieve all data shared by table1 and table2

SELECT field1, field2 ...
FROM table1         -- all data related to table2
INNNER JOIN table2  -- all data related to table1
  ON table1.fieldX = table2.fieldX;

we covered LEFT JOIN that allows us to retrieve data from table1 and any related data from table2

SELECT field1, field2 ...
FROM table1         -- all data
LEFT JOIN table2    -- corresponding data related to table1
  ON table1.fieldX = table2.fieldX;

and RIGHT JOIN that allows us to retrieve data from table2 and any related data from table1.

SELECT field1, field2 ...
FROM table1         -- corresponding data related to table2
RIGHT JOIN table2   -- all data
  ON table1.fieldX = table2.fieldX;

Download the class notes for day 6.

We are 50% done with the course.

1. Understanding core database concepts — lots of theory
2. Creating database objects — next week
3. Manipulating data — what we have done in the past two weeks

We have covered various T-SQL functions for strings and numbers. For example, we covered a way to capitalize the first character of a-single-word field or variable (@word declared as a VARCHAR(10) and initialized as 'tImE', in the example below).

DECLARE @word VARCHAR(10) = 'tImE';

PRINT CONCAT (
    UPPER(LEFT(@word, 1)),
    LOWER(SUBSTRING(@word, 2, LEN(@word)-1))
    );

You can download the 845-page PDF from Microsoft Docs for much more information.

Download the class notes for day 5.

Yesterday we continued covering functions like FORMAT() to dress up dates

FORMAT(date, 'MM/dd/yyyy', 'en-us')

and currency.

FORMAT(dollar_amount, 'C', 'en-us')

Download the class notes for day 4.

We started working with built-in functions.

“In information technology, the term function (pronounced FUHNK-shun) has a number of meanings. It’s taken from the Latin ‘functio’ — to perform.
1) In its most general use, a function is what a given entity does in being what it is.
2) In C language and other programming, a function is a named procedure that performs a distinct service. The language statement that requests the function is called a function call, Programming languages usually come with a compiler and a set of ‘canned’ functions that a programmer can specify by writing language statements. These provided functions are sometimes referred to as library routines. Some functions are self-sufficient and can return results to the requesting program without help. Other functions need to make requests of the operating system in order to perform their work.
3) In mathematics, a function is a relationship between two variables called the independent variable and the dependent variable. The dependent variable has at most one value for any specific value of the independent variable. A function is usually symbolized by a lowercase, italicized letter of the alphabet, followed by the independent variable in parentheses. For example, the expression y = f ( x ), read ‘y equals f of x,’ means that a dependent variable y is a function of the independent variable x, Functions are often graphed, and they usually appear as lines or curves on a coordinate plane.
4) In a hardware device, a function is one complete physical movement that has a discernible consequence relative to the device’s purposes. In a printer, for example, this might be a carriage return or a line feed.”
http://whatis.techtarget.com/definition/function

Download the class notes for day 3.

Yesterday we covered how to import data from an external file into SQL Server (white paper).

We also covered how to retrieve (SELECT) data from one or multiple tables (JOIN).

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
INNER/LEFT/RIGHT JOIN table3
  ON table1.common_field1 = table3.common_field1
    AND table1.common_field2 = table3.common_field2 ...

Download the class notes for day 2.

Remember that next Monday the 4th is Labor Day and BMCC will be closed. Enjoy the holiday and we will meet back on Wednesday the 6th.

If you have any questions, do not hesitate to contact me via email or https://introtosql.slack.com/.

Yesterday we had some “technical difficulties” with the SQL Server Express installation. At times, we can learn the hard way.

When installing SQL Server Express on your computer at home and work, make sure you are the user (administrator) of the server instance.

SQL Server Management Studio is simpler to install — no special configuration.

Download the class notes for day 1.

• special thanks to Wise Owl for the videos