SQL Tutorial — Full database course for beginner step by step

Study Note from free-code-camp open source course — Full database course for beginner

Course Link : https://www.youtube.com/watch?v=HXV3zeQKqGY&t=159s

Guideline

1. What is a Database (DB)?
2. Tables & Keys
3. SQL Basic
4. Creating a Table
5. Inserting Values
6. Update & Delete
7. Basic Queries
8. Function
9. Wildcards
10. Union
11. Join

What is a Database?

“What is a database..?”

“Any collection of related information is a database. Here is example!”

1. Phone Book
2. Shopping List
3. Todo List
4. Facebook User Base

“Hmm.. But how can we store the databases?”

1. Computer
2. In your mind
3. paper
4. powerpoint

“All above can store databases. But I think computer is the best way to store databases. ”

We use DBMS(Data Base Management Systems) to create and maintain a database on a computer. Especially when you have large amount of information like Amazon. DBMS is a better choice than paper!! Advantage for DBMS :

1. Store trillion of information
2. Handle Security
3. Backups your data
4. Importing / Exporting data

Notice that DBMS is not actually a database. It’s a software application that can create, read, update, delete the information from the actual database. Creating, reading, updating, deleting (C.R.U.D.) are the 4 core operation we want DBMS do for us.

Two type of database :

1.Relational Database : Organize data in tables. Each table has column and row. A unique key identifies each row. Using SQL(Structured Query Language) to interact with Relational-DBMS.

2. Non-Relational Database : Except traditional table. Ex : Document, Graphic ..

Tables & Keys

Primary key : Unique for each row. Student id is a primary key. It differentiates two Jack.

Surrogate key : Type of primary. Have no mapping to anything in the real world. employee id is a surrogate key.

Natural key : Have connection to real world. The opposite of Surrogate key.

Foreign key : A primary key for another table. It’s a way to define a relationship between two table. (A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.)

“branch_id” is a primary key for below table. It’s a foreign key for upper table. It connects two table.

Composite key : Combine two column as a primary key. If we don’t have single key which is unique for each row, we need to use composite key.

SQL Basic

Concept1 : SQL can do…

1. C.R.U.D the data
2. Create & Manage database
3. Design database table
4. Perform administration task (Security, User Management,..)

Concept2 : SQL is a hybrid language. SQL is four type of language mass into one.

1. Data Query Language (DQL)
2. Data Definition Language (DDL)
3. Data Control Language (DCL)
4. Data Manipulate Language (DML)

Concept3 : Queries. A set of instruction(指令) given to RDBMS that tell RDBMS what piece of information we need. And it only get back what we want.

Concept4 : SQL & MySQL Difference

SQL : A Language

MySQL : RDBMS

Data Type

Creating Tables

Install MySQL : https://dev.mysql.com/downloads/installer/

Install PopSQL : https://popsql.com/

Install MySQL and PopSQL Tutorial : https://www.youtube.com/watch?v=HXV3zeQKqGY&t=159s

Generally, we will use capital to distinguish reserve word and general text in SQL.

Line1 : create a table

Line2 : key (student_id) + datatype (INT)

Line3 : Allocate 20 characters to “name”

Line5 : define primary key

Line6 : every command in SQL will end with “ ; ”

Line 10, 11 : Add/Delete the column. Decimal(3,2) mean gpa is X.XX number. ex : 3.25

Inserting Values

INSERT INTO table VALUES()

Inserting Values

Line 10 : Using “INSERT INTO” to insert values into table

Line 11 : Insert values into certain column

Line 8 : Get all information in the table

Run line 10~12 before running line 8. You can see now we have a table with 3 row.

NOT NULL : Don’t accept null values

UNIQUE : Values must be unique

PRIMARY KEY : NOT NULL+ UNIQUE

AUTO_INCREMENT : Auto assign the number. Must be primary key.

DEFAULT : Set the default values when you insert null values.

AUTO_INCREMENT : you don’t need to input id, just like line 10

Update & Delete

1.Update the data

Change major ‘Biology’ into ‘Bio’. We can also select using student_id.

2.

Change major ‘Bio’ and ‘Chemistry’ into ‘Biochemistry’

Comparison Ops : AND, <> (not equal to), OR, <, >, < =, > =

3.

Delete the row which student_id = 1

Basic Queries

Query is a block of SQL that design to ask particular information

1.Select name and major in student table and only want two data.

DESC / ASC : Big to small / Small to big

Only choose name and major.

2. We can select specific name ‘Emily’ and ‘Mike’ and major is ‘CS’

3.

Rename the column

4.

Company database

Foreign key : Store the different table’s primary key. Super id show the supervisor the employee have. mgr_id means manager id.

#Red is primary key.

#Green is foreign key.

#Blue is Attribute.

We can find first 5 employee in the table and change first_name column into forename.

We can find different gender.

Function

We can use count to find out how many employee in the table. (count the primary key numbers)

COUNT with more condition

We can group the data

Wildcard

We can find the company which have ‘LLC’ in the end of the name.

Or we can also find the company name which contain ‘Label’.

Find the employee whose birthday is in October.

_ means one character.

Union

We can merge two list using keyword “UNION”. Using “UNION” need to have the same data type.

Find the money company earn or spend in a single column.

JOIN

Now we join employee table and branch table together into one table.

ON : condition. We only want to combine employee.emp_id = branch.mgr_id row.

Nest Query

Find employee name who sold over 30000 in sales. IN keyword allow return value >1.

If we use “=”, return value can only have one.