Learn SQL for Beginners: The Comprehensive Hands-on Bootcamp

Welcome to the course. You are taking a great step by enrolling in this course. Better skills create a better life. You are on your way to a better life. As you learn new skills, you are building a better life. I commend you for your efforts to improve your life. As you improve your life, you are improving the world --- one person at a time. You are making the world better, and you are making your life better. This is a win-win for everybody. Great work! Also, this is your course. Use it in the way which is best for you. If you want to skip ahead, skip ahead. As your teacher, my job is to help you succeed. The content here is designed to help you succeed both with understanding databases and SQL, and also as a student and in life.

I am a tenured professor in California. I have taught at both the college and university level. I have also taught in multiple disciplines including business, information systems, computer science, and online education. In 1997, I was one of the first professors at the university to teach online. I did post-graduate work in online education at UC San Diego. I co-founded and taught in the “Online Teacher’s Training Program” which trained professors how to teach online. In 2008, I was selected as one of the best instructors in the entire California Community College system. Currently, when measured by the number of students served, I am the world’s leading trainer in Google’s new programming language which is one of the fastest growing, highest paying programming languages in America. My background in business, information systems, computer science, and online education has prepared me to teach this course!

You can find everything I use in the course and all of the courses resources here:

• Course outline - http://bit.ly/db-sql

  https://docs.google.com/document/d/1Xlj5V5dTYj-AuPbvlOrog7CVPcjzMZtBxpBoxOUIp9Y/edit?usp=sharing

  
  

• Folder

  https://drive.google.com/drive/folders/1eJZ0mEln0tK1Oj5yrCi4dffY7L1Ln8g1?usp=sharing

  
  

• github

  https://github.com/GoesToEleven/postgresql-course/

  
  

• TutorialsPoint tutorial

  
  

• Some of my favorite things

  https://docs.google.com/document/d/1757yhrMGdwhtGW5WSIvCQnwZ8oF0aemqiP9kLgA026Y/edit?usp=sharing

This course is one course in a logical series. If you wanted to take all of the courses from start to finish, you would take them in this order:

• VS Code

• HTML / CSS

• Learn How To Code Google’s Go (golang) Programming Language

• Relational Databases SQL

• Web Development with Google’s Go Programming Language

• Collaboration and Crawling with Go

• Web Architecture Fundamentals with Go

• Intermediate Go Web Authentication, Encryption, JWT, & OAuth

Understanding what has made others successful can help you become successful. These are principles which have helped me become successful. I learned these principles from others and from my own experience. I share these principles to help you succeed in this course and in life:

• GRIT

  • grit and fit

    • persistence and passion

      • solving problems

      • creating things

      • change the world

      • work for dynamic companies

      • make a boatload of money if all goes well

    • to what are you going to devote your life?

      • some factors to think about

        • marketability

          • can I make money at this

          • what are the opportunities like

        • room for growth

        • creative expression

          • do I get to build something

If I don't practice one day, I know it; two days, the critics know it; three days, the public knows it. Jascha Heifetz

• Grit - Angela Duckworth

• DRIP LEARNING

  • Time on task

  • Small frequent engagements

  • Multiple perspectives, multiple engagements

  • my teachers

    • drop by drop, the bucket gets filled

    • persistently, patiently, you are bound to succeed

• FOCUS

  • Bill Gates & Warren Buffett

• PLANNING

  • Bill Gates, “If you want to be successful, get in front of what’s coming and let it hit you.”

• RESOURCES

  • The 7 Habits of Highly Effective People

  • The Journey - Skills To Build A Better life: Healthier, Wealthier, Happier

You can change the speed of videos: either slow them down, or speed them up. Adjust the speed as necessary for your learning style. Fast-forward if you want to skim over material. Slow me down if you need me to go slower.

A great resource for golang questions:

• sql - stackoverflow

• https://forum.golangbridge.org/

The fastest way to reach me is via Twitter: https://twitter.com/Todd_McLeodAsk your questions there by (1) tweeting the question and tagging me @Todd_McLeod in it or (2) sending me a direct message.

Things change. The most important thing to learn are principles: a concept, an idea, what is possible. The implementation of a concept or an idea will change. There is the “principle” (what can be done) and there is the “practice” (the way it’s done). If there is a difference between the principle and the practice, if there is a difference between what I show you and how you do it now, the most important thing to remember is the principle: the concept, the idea of what is possible. As the world changes, it is necessary for us to figure out how to do what we want or need to do. Knowing that something can be done is the first part. Figuring out how to do it is the second part. What I show you may have changed since the time when I recorded this video. If this is the case, see if you can figure out the new way to do what needs to be done:

• Google for the solution

• YouTube for the solution

• Tag me in a tweet and ask:

  • @Todd_McLeod

  • https://twitter.com/?todd_mcleod

As you go through the course, if you find a video covering a principle that is too different from the practice, please email me and let me know: [email protected] I will update the video. Together we all succeed.

The exercises are for your benefit. The more you do something, the better you get at it. The exercises are not required.

Sometimes my courses are used in classes at colleges, universities, high schools, and other schools. If this is the case and this course here is being used in a course you are taking at a school, your teacher there might require that you do the assignments. If that is your case, check with your teacher.

Occasionally I will provide personal anecdotes. When learning, it is natural for individuals in a course to connect in a personal way. Research shows that students do better in classes where there is a personal connection. By and large, I will reserve personal anecdotes for the end of a video. By and large, I will let you know that (1) we are done learning what we needed to learn in this one video and (2) now I am going to share a personal anecdote and, if you are in a rush, you can stop watching this video and go to the next video.

• What makes a good life? Lessons from the longest study on happiness | Robert Waldinger

• Smarter Faster Better: The Transformative Power of Real Productivity by Charles Duhigg

  • http://amzn.to/2y4fBED

  • internal locus of control

Daniel Hoffmann

See the image

• Who was the first customer to rent American Beauty?

• Who was the last person to rent Alien?

• What are all of the movies that Jose has rented?

For the previous “puzzle” exercise, what are some things which need to be true for the system to work?

• Grouped

  • customers in customers table

  • movies in movies table

  • GROUP SIMILAR DATA

    • normalization

• generally speaking, don’t repeat data

• referential integrity

  • references need integrity

  • numbers stored in rentals need to exist

• key fields

  • unique identifier

• SQL

  • aka, schema

  • schema: set of rules

• NO-SQL

  • aka, schema-less

  • schema-less: no rules; don’t mess it up; know what you’re doing

  • redundant data is okay

    • speeds lookups

Creating a single table.

Creating a movie table.

Creating a "transaction" table to connect our two other tables.

Modify the customers table so that an unlimited amount of phone numbers can be stored. Hint: this will require the creation of another table.

We also discussed:

• one-to-one

• one-to-many

• many-to-many

Modify the database schema so that you can associate actors with each movie. Hint: this will require the creation of two more tables!

Modify the database schema so that you can associate directors with each movie. Hint: this will require the creation of two more tables!

Modify your database schema so that you have one table called “people” which stores customers, actors, and directors

• the database

  • enforced rules about the structure of the database

• “nosql” is also known as “schema-less” / “no schema”

https://www.eversql.com/most-popular-databases-in-2018-according-to-stackoverflow-survey/

DATABASE

TABLES

RECORDS

FIELDS

CHARACTERS

Limiting redundancy

Making sure the data stored is accurate

You want your data to be reliable

• unique identifier for each record

• “foreign key” is a key field in a different table

All references must have integrity meaning a customer id in a rental table transaction MUST refer to an actual customer

ACID transactions: A set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. In the context of databases, a sequence of database operations that satisfies the ACID properties (and these can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

• Atomicity

  • Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.

  • An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes. A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright.

• Consistency

  • Consistency ensures that a transaction can only bring the database from one valid state to another: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct. Referential integrity guarantees the primary key – foreign key relationship.

• Isolation

  • Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

• Durability

  • Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash).

“Structured Query Language”

Diagram the schema of a database for a bicycle store.

Diagram the schema of a database for an amusement park.

Diagram the schema of a database for a company that provides event functions on a sailboat.

This section will show you how to set up your development environment. If you are experienced with programming, you can skip this section and any other sections you want. This is your course after all. I am here to help you. As there are a variety of students who will take this course, I believe in providing a clear continuity of curriculum so that even those who are just getting started have a pathway to success.

• terminology

  • GUI = graphical user interface

  • CLI = command line interface - command line

    • terminal = text input/output environment; console = physical terminal

      • unix / linux / mac / posix

        • shell / bash / terminal

      • windows

        • command prompt / windows command / cmd / dos prompt

        • powershell

• https://git-scm.com/

• linux on Windows

  • developer features

    • Linux subsystem for Windows

  • bash

article with steps here and another article

• shell / bash commands

  • pwd

  • ls

    • ls -la

  • cd <directory>

  • cd ../

  • cd or cd ~

  • mkdir <directory>

  • rm -rf <directory>

  • clear or command + k

• cmd commands

  • cd

  • dir

    • dir /a

  • cd <directory>

  • cd ..

  • cd %USERPROFILE%

  • mkdir <directory>

  • rmdir /S <directory>

  • cls

• shell / bash commands

  • ls -la

• shell / bash commands

  • touch <file name>

    • example: touch temp.txt

  • nano <file name>

  • cat <file name>

• shell / bash commands

  • rm <file name>

  • rm -rf <file or folder name>

• cmd commands

  • del <file name>

  • rmdir /S <directory>

• shell / bash commands

  • chmod <owner group world> <file or folder>

    • example: chmod 764 temp.txt

  • sudo

    • super user do

• permissions

  • owner, group, world

  • r, w, x, no permission

  • 4, 2, 1, 0

r = read w = write x = execute

rwxrwxrwx = owner, group, world

• Environment variables are a set of variables on a computer. They are part of the environment in which a process runs.

• shell / bash commands

  • env

• “path” environment variable

  • PATH is an environment variable on Unix-like operating systems, DOS, OS/2, and Microsoft Windows, specifying a set of directories where executable programs are located. In general, each executing process or user session has its own PATH setting.

  • often executable files will be in a “bin” folder which stands for binary.

  • echo $PATH

  • Windows

    • echo %PATH%

• .bash_profile & .bashrc

  • .profile

  • .bash_profile is executed for login shells

    • When you login (type username and password) via console, either sitting at the machine, or remotely via ssh, .bash_profile is executed to configure your shell before the initial command prompt.

  • .bashrc is executed for interactive non-login shells

• shell / bash commands

  • export <environment variable name>=“<path>”

  • $<environment variable name>

examples of setting environment variables:

• export GOPATH=“/Users/toddmcleod/go”

• export PATH=“$PATH:/Users/toddmcleod/go/bin”

Setting environment variables - windows

Search for “environment variables”

For this hands-on exercise, at the terminal:

• navigate to “Documents”

• create a folder “HappyDog”

• navigate to that folder

• look to see if there are any files or folders in that folder

• navigate back to documents

For this hands-on exercise, at the terminal:

• navigate to the folder “HappyDog”

• create a file “be-happy.txt”

• edit this file to include items that make you happy

• show the contents of this file at the terminal

• navigate to “Documents”

For this hands-on exercise:

• Navigate to “HappyDog”

• change the permissions on the file “be-happy.txt”

  • user: read, write

  • group: read

  • world: nothing

• confirm that the permissions are set that way

• navigate to “Documents”

For this hands-on exercise:

• Navigate to “HappyDog”

• Delete “be-happy.txt”

For this hands-on exercise:

• Navigate to “Documents”

• Delete “HappyDog”

• confirm this directory has been deleted

• navigate to your user’s folder

Here is the documentation for Postgres

https://www.postgresql.org/docs/manuals/

Installing mac

• /Library/PostgreSQL/12/scripts/runpsql.sh

Installing windows

• C:Program FilesPostgreSQL12scriptsrunpsql.bat

Installing Linux

• sudo apt install postgresql-10

• sudo su postgres

• cd

• Create config file

• /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Installing digital ocean

username = doadmin

password = xbdqn0b29inb1mxw

host = postgres-sandbox-do-user-1489496-0.db.ondigitalocean.com

port = 25060

database = defaultdb

sslmode = require

postgresql://doadmin:xbdqn0b29inb1mxw@postgres-sandbox-do-user-1489496-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require

cd <postgres-folder>bin

psql --help

psql -h postgres-sandbox-do-user-1489496-0.db.ondigitalocean.com -p 25060 -U doadmin -d defaultdb

AWS discussion

• https://aws.amazon.com/rds/postgresql/resources/

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.PostgreSQL.html

All commands

• listed here:

• https://docs.google.com/document/d/1E2O0KDzWJ5efAG2UFZPO06KEjI2CnkyruVnjsRBsvs4/edit?usp=sharing

Creating a database

• login to postgres database server

• listing databases

  • l

• CREATE DATABASE "dbname";

Working with a database

• switching to a database

  • c dbname;

• exit database

  • q

• drop database

  • DROP DATABASE dbname;

  • Cannot drop a database that has any open connections, including our own connection. Switch to a different database, then drop the one to which you’re no longer connected.

Creating a table

• CREATE TABLE <tablename>(<field datatype contraints>, ...)

• examples

  • CREATE TABLE COMPANY(
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
    );

  • CREATE TABLE DEPARTMENT(
    ID INT PRIMARY KEY NOT NULL,
    DEPT CHAR(50) NOT NULL,
    EMP_ID INT NOT NULL
    );

  • 
    

• listing tables

  • d

  • d tablename

Exploring data types

https://www.postgresql.org/docs/12/datatype.html

Drop a table

• drop table

  • DROP TABLE tablename;

For this hands-on exercise, at the terminal:

• create a database for a bikestore

• create a table for customers

Hands-on exercise #2

For this hands-on exercise, at the terminal:

• create a database for amusement park

• create table for visitors

  • you will need to record the birthday for the visitors

anecdote:

https://insights.stackoverflow.com/survey/2019#salary

Hands-on exercise #3

For this hands-on exercise:

• Delete your two databases

  • Delete the tables before deleting the databases

A quick review.

Inserting records

syntax

INSERT INTO tablename (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN), (...);

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Examples of inserting records

create a database

bikestore database example

create table

CREATE TABLE customers(

cid SERIAL PRIMARY KEY NOT NULL,

cfirst VARCHAR(50) NOT NULL,

clast VARCHAR(50) NOT NULL

);

insert records

INSERT INTO customers (cfirst, clast) VALUES ('James', 'Bond');

INSERT INTO customers (cfirst, clast) VALUES ('James', 'Bond'), ('Jenny', 'Moneypenny');

Constraints

https://www.postgresql.org/docs/12/sql-createtable.html

https://www.postgresql.org/docs/12/ddl-constraints.html

• constraints on what can be done

  • rules enforced on data

    • ensures data validity

    • increases data integrity

• constraints can be placed on

  • database

  • table

  • column

• common column constraints

  • NOT NULL

    • column cannot have NULL value

  • UNIQUE

    • all values in a column are different

  • PRIMARY KEY

    • uniquely identifies each row/record in a database table.

  • REFERENCES (FOREIGN KEY)

    • constraint based on columns in other tables

  • CHECK

    • ensures that all values in a column satisfy certain conditions.

Common constraints

not null

• By default, a column can hold NULL values.

• A NULL is not the same as no data; rather, it represents an unknown data.

• If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.

• A NOT NULL constraint is always written as a column constraint.

unique

• prevents two records from having identical values in a particular column.

• for example, you might want to prevent two or more people from having the same social security number

primary key

• NOT NULL

• UNIQUE

• uniquely identifies each record in a table.

• Primary keys must contain unique values.

• only one primary key in a table.

• Primary keys are unique ids.

• We use them to refer to table rows.

• Primary keys become foreign keys in other tables, when creating relations among tables.

• A primary key column cannot have NULL values.

• When multiple fields are used as a primary key, they are called a composite key.

references (foreign key)

• values in a column (or a group of columns) must match the values appearing in some row of another table.

• We say this maintains the referential integrity between two related tables.

• They are called foreign keys because the constraints are foreign; that is, outside the table.

• Foreign keys are sometimes called a referencing key.

check

• check a condition before a value is entered into a record.

• If the condition evaluates to false, the record violates the constraint and is not entered into the table.

Primary key, not null

• create database

CREATE DATABASE bikeshop;

• create table

  • constraints:

    • PRIMARY KEY

    • NOT NULL

CREATE TABLE customers (

cid SERIAL PRIMARY KEY,

cFIRST varchar(50) NOT NULL,

clast varchar(50)

);

• insert records

INSERT INTO customers (cFIRST, clast) VALUES ('James', 'Bond'), ('Jenny',NULL);

SELECT * FROM customers;

Unique, references

• create table

  • constraints:

    • PRIMARY KEY

    • UNIQUE

    • REFERENCES

CREATE TABLE phonenumbers (

pnID SERIAL PRIMARY KEY,

pnNumber varchar(20) UNIQUE,

cid INT REFERENCES customers(cid)

);

• insert records

INSERT INTO phonenumbers (pnnumber, cid) VALUES ('1234567890',1), ('57345634523', 2);

• foreign key error

INSERT INTO phonenumbers (pnnumber, cid) VALUES ('24343543', 3);

• unique error

INSERT INTO phonenumbers (pnnumber, cid) VALUES ('1234567890', 2);

Check

create table

CREATE TABLE inventory (

iID SERIAL PRIMARY KEY,

iName varchar(50) NOT NULL,

iDescription varchar(1000),

iCost INT NOT NULL CHECK(iCost>0)

);

insert

INSERT INTO inventory (iName, iCost) VALUES ('iPhone', 4000);

insert fails

INSERT INTO inventory (iName, iCost) VALUES ('iPhone', -4000);

solution for money

CHECK(iCost > 0::money)

Dropping constraints

• To remove a constraint you need to know its name.

• If the name is known, it is easy to drop.

• Else, you need to find out the system-generated name.

• The psql command d <table name> can be helpful here.

ALTER TABLE table_name DROP CONSTRAINT some_name;

Naming a constraint

CREATE TABLE emails(

eID SERIAL PRIMARY KEY,

eMail VARCHAR(100) NOT NULL,

eTimesSent INT CONSTRAINT positive_sent CHECK (eTimesSent > 0)

);

Transaction tables

Simple queries

https://www.postgresql.org/docs/12/sql-select.html

SELECT * FROM <table name>;