Understanding Database 2 - SQL

Structured Query Langauge, Manipulating relational databases, International standard since 1986(ANSI, ISO)

https://larryludwig.com/lamp-stack/

SQL is language, and MySQL is DBMS. Web interfaces( e.g., phpMyAdmin) 

Several notes 

VARCHAR(20): By specifying the number of characters for memory management purposes. 

 

Schema Management: Definition of the table, key management, modify the table 

- CREATE table, ALTER table, DROP table(NO UNDO) 

Create a table and set the primary key, auto-increment, foreign key, and unique key all in one query. (Figure 1)

Figure 1. Create table example

 

Code conventions

• recommend using "reserved words" in UPPERCASE (Not To confuse) 
• recommend using table name as case-sensitive; attribute names are never case-sensitive

Table Data management: Managing data in a table - INSERT, DELETE, UPDATE

Figure 2. example of DELETE and INSERT

Figure 3. Example of UPDATE

Retrieving data with queries

In addition to the below image, COUNT(DISTINCT name), ORDER BY name ASC(or DESC), etc., can be used.

When joining the tables, aliases can be used, as shown in Figure 4—for instance, audit_trail as a. (as is not mandatory)

Figure 4. Example of SELECT

DBMS forbid invalid operations like the below due to the confusion of the table relationship:

• deleting primary keys
• deleting foreign keys
• inserting data of the wrong domain
• inserting a duplicate primary key value
• drop a table which other tables depend

NULL

Null is a state, not a value; Uknown, Unavailable, Withheld, Not applicable.

You can set a value to NULL. You can test if a value is NULL or NOT NuLL. A PK can never have a NULL.

• Checking for Null: Cannot use "=", need to use IS NULL:

SELECT name from VIPs WHERE age IS NULL;

Three-Valued Logic 

https://modern-sql.com/concept/three-valued-logic : Details can be found here. 

NOT: Opposite, but Unknown <> Unknown 

AND: If one of them is False-> False, False> Unknown>True 

OR: If one of them is True-> True, True>Unknown>False

Image from WIKIPEDIA

 

JOIN 

A join is used to combine tables (based on the relationship)

• Inner Join: Only return the data from both tables when they meet the condition.
• Right Outer Join: Returns all rows from the right side + matching one from the left side 
• Left Outer Join: Returns all rows from the left side + matching one from the right side
• Full Outer Join: Returns matching rows + remaining one from both tables (Thus, it can be done by combining left join + right join with UNION ALL)
• Cross Join: Join one by one every single row from one table by rows from another table. 

**UNION ALL combines the results of multiple queries and removes duplicates. (Some DBMS might not) 

 

This website shows and summarises the content well.

https://cloud.google.com/blog/topics/developers-practitioners/bigquery-explained-working-joins-nested-repeated-data?hl=en