LPIC 105.3 SQL data management

Types of Databases

Key-Value Database
Products:Berkeley DB, Redis(network structure)

  • Key-Value Database much like a dictionary
  • They usually don't have a network structure

Relational Database
Products: Oracle, MySQL, SQLite,PosegreSQL

  • Relational DB deal with both data and their relationships

Schemaless Database
Products: MongoDB, Couchbase, ElasticSearch

  • Schemaless Database also called NoSQL Database because they typically don't use SQL language.
  • Schemaless DB use document storage
  • Schemaless DB are good at huge databases, such as if you're running a large social network.
  • Schemaless DB are easy from developer use when table design is not finalized.

Learning SQL

Using SQLite

  • A semicolon(;) terminates a statement in SQL
  • Comments start with 2 dashs(--)
SELECT * -- I want all the columns  
FROM t1; -- t1 holds sales data  
  • SQL commands are case-insensitive, but the table, column name and strings are case sensitive
  • Set a friendly display format
.header on
.mode column

Selecting Data

Examples:

--The simplest way to use SELECT is to ask for a single string
SELECT 'Hello World!';  
Hello World

---SELECT can also run some expressions
SELECT 1+1;  
2

---SELECT should work together with FROM
SELECT * FROM book;  
SELECT title, year FROM book;

---More Examples
SELECT title, year, written FROM book WHERE year <> written;  
SELECT title, year FROM book WHERE year IN (2001, 2005);  
SELECT title, year FROM book WHERE year BETWEEN 2004 AND 2005;

---% matches zero or more characters
SELECT title FROM book WHERE title LIKE '% Exam Cram 2';  
  • Databases will join every row on left table to right table if you don't give it join conditions
sqlite> SELECT * FROM author JOIN book;  
id          first_name  last_name   id          title               year        author_id   written  
----------  ----------  ----------  ----------  ------------------  ----------  ----------  ----------
1           Sean        Walberg     1           LPIC 1 Exam Cram 2  2004        2           2004  
2           Ross        Brunson     1           LPIC 1 Exam Cram 2  2004        2           2004  
1           Sean        Walberg     2           Linux and Windows   2001        2           2000  
2           Ross        Brunson     2           Linux and Windows   2001        2           2000  
1           Sean        Walberg     3           Wireless All In On  2009        1           2009  
2           Ross        Brunson     3           Wireless All In On  2009        1           2009  
1           Sean        Walberg     4           Check Point CCSA E  2005        1           2004  
2           Ross        Brunson     4           Check Point CCSA E  2005        1           2004  
  • Instead, you should use ON keyword to give it conditions
sqlite> SELECT * FROM author JOIN book ON book.author_id = author.id;  
id          first_name  last_name   id          title               year        author_id   written  
----------  ----------  ----------  ----------  ------------------  ----------  ----------  ----------
2           Ross        Brunson     1           LPIC 1 Exam Cram 2  2004        2           2004  
2           Ross        Brunson     2           Linux and Windows   2001        2           2000  
1           Sean        Walberg     3           Wireless All In On  2009        1           2009  
1           Sean        Walberg     4           Check Point CCSA E  2005        1           2004  
  • Use AS to make table alias
---The `AS` is used right after the first mention of the table name and is directly followed by the alias.
sqlite> SELECT * FROM book AS a JOIN author AS b ON a.author_id = b.id;  
---The `AS` keyword is optional. So, below syntax is same as above
sqlite> SELECT * FROM book a JOIN author b ON a.author_id = b.id;  
  • Left join and right join
---Insert one record to author table
sqlite> INSERT INTO AUTHOR (id, first_name, last_name) VALUES (3, 'Jalo', 'Wang');  
---left join
sqlite> SELECT first_name, last_name, title FROM author LEFT JOIN book ON author.id = book.author_id;  
first_name  last_name   title  
----------  ----------  ----------------------------
Sean        Walberg     Check Point CCSA Exam Cram 2  
Sean        Walberg     Wireless All In One For Dumm  
Ross        Brunson     LPIC 1 Exam Cram 2  
Ross        Brunson     Linux and Windows 2000 Integ  
Jalo        Wang  
---You'll get error when try to use right join as sqlite doesn't support right join.
---Error: RIGHT and FULL OUTER JOINs are not currently supported

NULL

  • NULL is more than empty value. You can regard it as placeholder.
  • NULL cannot be compared to NULL, use keyword IS NULL instead.

Subselects

  • The subselects are enclosed in parentheses and usually given as an argument to a WHERE column IN clause.
sqlite> SELECT title FROM book  
   ...> WHERE author_id
   ...> IN (SELECT id FROM author WHERE first_name = 'Sean');
title  
-------------------------------
Wireless All In One For Dummies  
Check Point CCSA Exam Cram 2  

group and count

sqlite> SELECT first_name, last_name, title  
   ...> FROM author LEFT JOIN book
   ...> ON author_id = author.id;
first_name  last_name   title  
----------  ----------  ----------------------------
Sean        Walberg     Check Point CCSA Exam Cram 2  
Sean        Walberg     Wireless All In One For Dumm  
Ross        Brunson     LPIC 1 Exam Cram 2  
Ross        Brunson     Linux and Windows 2000 Integ  
Jalo        Wang

---Group BY roll up rows with similar contents
---It usually work with aggregate functions(COUNT, MAX, MIN, SUM or AVG)
sqlite> SELECT first_name, last_name, COUNT(title) AS book  
   ...> FROM author LEFT JOIN book
   ...> ON author_id = author.id
   ...> GROUP BY first_name, last_name;
first_name  last_name   book  
----------  ----------  ----------
Jalo        Wang        0  
Ross        Brunson     2  
Sean        Walberg     2  

Insert Update and Delete

Insert Syntax:

  • INSERT INTO tablename (columns) VALUES (values);
  • INSERT INTO tablename VALUES (values);
  • INSERT INTO tablename (columns) VALUES (values1), (values2), ...;