Free SQL 101 Class Bootcamp Beginners Intro to SQL NYC

Websites to run MySQL code (Ephemeral):

1. https://www.db-fiddle.com/

2. http://www.sqlfiddle.com/

Supports SQL Server:

http://rextester.com/l/mysql_online_compiler

Creating a Database for Attendees

Which columns should we add? Deciding in the data

How  many tables we should make? Normalization into difference tables

What we will do with the data? Analytics and may be predictive analytics

CREATE TABLE Attendees (

    PersonID int,

    Heroname varchar(255),

    Age int,

    Assets int,

    Sector varchar(255)

);

INSERT INTO Attendees (    PersonID ,Heroname ,    Age ,    Assets ,    Sector  )

VALUES (1, 'Spider Man ', 32, 300000, ‘Fin’);

Data Types (INT, CHAR and BOOLEAN)

When to use Quotes (Single vs Double)

Char uses quotes but not in number or boolean

Color Convention - reserved keywords, strings numbers

Code beautify

https://codebeautify.org/sqlformatter

Selecting info from a table:

select * from attendees; select * from avgsalary;

Calculation on columns:

select *, assets/salary as frugalmeasure from attendees order by frugalmeasure desc;

Subquery example:

select * from attendees where       city in    (select        city2 from avgsalary where avgsalary >10000)

Remainder function Mod example:

SELECT * FROM   attendees WHERE  Mod(personid, 2) = 0;

Find the person with Max salary:

SELECT Max(assets) FROM   attendees WHERE  assets Not IN (SELECT Max(assets)                  FROM   attendees);

Casting number to Character for string concatenation:

select * , concat (cast(assets/1000 as CHAR), 'k') Assetsink from attendees

Case statement to display Queens and Others by checking each value:

SELECT heroname, CASE    WHEN city = "Q" THEN "Queens"     ELSE "Other City" END FROM attendees;

Join needs a common column

This is for SQL server as Full outer join not supported in MySQL

select * from attendees full outer join avg salary on attendees.city = avgsalary.city2

Below works with MYSQL:

    select *     from attendees    inner join avgsalary on attendees.city = avgsalary.city2

Creating Alias and then joining:

select * from ( select *  from Attendees where Age>60  ) A join Avgsalary on A.City = Avgsalary.City

Union stacks one table below the other:

SELECT * from Avgsalary union all select * from Avgsalary2;

Flat file saved in CSV vs SQL vs Excel

Here the questions would be which kind of analytics you want to run and the complexity and size of the data.

Excel gives equivalent what we do in SQL

Vlookup = Joins

Pivots = Group by

Vlookup in an equivalent of what?

Joins (but to be specific it is Left Join)

How can we do group by in Excel?

By Running Pivot Table commands - there is no other way to aggregate things in Excel.

What does pivot table means?

In SQL it can be done by Group by and Pivot (SQL Server)

Power BI and Cubes?

Power BI and Cubes let us do all the joins, group by and charting and filtering in a very complicated environment to do a holistic analytics for the company data.

Speak aloud in Write in English in a succinct way to do the following:

1. Calculate average salary of the class

2. Calculate average salary of all fin sector

3. Calculate average age of all sector

4. Put across every name the rating (what will we call this)

5. Take column and create new columns