Learn SQL for Beginners - How To Learn SQL The Easy Way

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