basics.yml

# Copyright (C) 2023 Ahmad Ismail
# SPDX-License-Identifier: CC-BY-SA-4.0

d1:
  title: Introduction
  type: yesno
  content: |-
    A relational database organizes data in multiple tables that consist of rows and columns.

    Rows are known as records.

    Columns are known as attributes.

    A relational database management system (RDBMS) is a program that manages relational databases.

    SQL is a standardized language that is used to query (read) and modify relational databases.

d2:
  title: The Players Table
  type: yesno
  command: mysql -h localhost -u sql_tutor -D sql_tutor -t -e 'SELECT * FROM Players;'

d3:
  title: The SELECT statement
  type: inputbox
  content: |-
    The SELECT statement is used to show one or more data columns of a table.

    Syntax: SELECT column1, column2,... FROM table_name;

    Example: To show the "HP" column: SELECT HP FROM Players;
    
    Try it out:
  
  command: SELECT HP FROM Players;

d4:
  title: Practice
  type: inputbox
  content: Enter the query to show PlayerID, HP and Name.

  command: SELECT PlayerID, HP, Name FROM Players;

d5:
  title: SELECT all?
  type: inputbox
  content: |-
    What if you want to display all data columns? Writing the names of all columns can be tedious and error prone.
    
    In this case, use the wildcard "*".

    The query becomes: SELECT * FROM table_name;
    
    Type the query that views all columns in our "Players" table:
  
  command: SELECT * FROM Players;

d6:
  title: WHERE statement
  type: yesno
  content: |-
    The WHERE statement filters the output according to the provided conditions.

    Supported operations: = < > <= >= BETWEEN LIKE IN

    AND, OR, NOT can be used with conditions.

    The LIKE operator will be explained in details later.

    Don't worry, examples coming next!

d7:
  title: Examples
  type: yesno
  content: |-
    Select rows where HP is smaller or equal to 20:
    SELECT * FROM Players WHERE HP <= 20;

    Select rows where Strength is between 40 and 200:
    SELECT * FROM Players WHERE Strength BETWEEN 40 AND 200;

    Select rows with specific values (like specific usernames):
    SELECT * FROM Players WHERE Name IN ("sparks87", "stormheat79");
    
    Select rows where the Class is not "light":
    SELECT * FROM Players WHERE Class NOT IN ("light");


d8:
  title: Practice
  type: inputbox
  content: Enter a query that shows the HP and Name of light Class players
  command: SELECT HP, Name FROM Players WHERE Class IN ('light');

d9:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the Name of the Players having a negative PlayerID:
  
  command: SELECT Name FROM Players WHERE PlayerID < 0;

d10:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows all columns of heavy Class players:
  
  command: SELECT * FROM Players WHERE Class IN ('heavy');

d11:
  title: The LIKE statement
  type: yesno
  content: |-
    This statement is used to search for a specific pattern in strings.
    
    It supports two wildcards: % and _
    '%' matches zero or more characters, '_' (underscore) matches exactly one character.
    
    If you have reserved characters in the data (like _ %), you can escape them in the query using a backslash '\'.

    If you need a literal backslash in the string, type '\\'.

d12:
  title: Examples
  type: yesno
  content: |-
    Select rows where Name starts with "c":
    SELECT * FROM Players WHERE Name LIKE "c%";

    Select rows where Name is exactly 8 characters (those are 8 underscores):
    SELECT * FROM Players WHERE Name LIKE "________";
  
d13:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the HP and PlayerID of players having an "s" anywhere in their Name:
  
  command: SELECT HP, PlayerID FROM Players WHERE Name LIKE '%s%';

d14:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the Class and Name of Players whose name start with an "a" and is exactly 4 characters long:

  command: SELECT Class, Name FROM Players WHERE Name LIKE 'a___';

d15:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows all columns of Players whose Name starts with a "b" and has an "n" somewhere in the name:

  command: SELECT * FROM Players WHERE Name LIKE 'b%n%'

d16:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the Players having underscore somewhere in their name:

  command: SELECT * FROM Players WHERE Name LIKE '%\_%'

d17:
  title: Dealing with duplicate entries
  type: yesno
  content: |-
    A column may have identical entries. To show only distinct data, use the DISTINCT statement as follows:
    SELECT DISTINCT column1, column2, ... FROM table_name ...;

    Note: Two rows may have one or more (but not all) identical columns.
    This ensures that all rows are unique (or by defining a primary key, that's for later)

d18:
  title: Practice
  type: inputbox
  content: |-
    Write the query that shows distinct Name and HP of Players:
  command: SELECT DISTINCT Name, HP FROM Players;

d19:
  title: SQL Functions
  type: yesno
  content: |-
    MariaDB has a large collection of built-in functions to handle strings and values. We mention some of them:
    CONCAT(str1, str2): Concatenates two strings.
    STRCMP(str1, str2): Compares two strings (returns 0 if strings match, -1 if str1 < str2, 1 if str1 > str2).
    TRIM(str1): Removes leading and trailing whitespaces from the string.
    
    AVG, MIN, MAX, CEIL, FLOOR: returns the average, min, max, ceil, floor of values in a column.
    COUNT(): Counts number of rows.

d20:
  title: Examples
  type: yesno
  content: |-
    Show the number of rows of the HP column:
    SELECT COUNT(HP) FROM Players;

    Show player Names concatenated with their ID:
    SELECT CONCAT(Name, PlayerID) FROM Players;

    Get the average of HP of all players:
    SELECT AVG(HP) FROM Players;

d21:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the maximum Strength among all players:
  command: SELECT MAX(Strength) FROM Players;

d22:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that returns the concatenation of Class and Name for all data rows:
  command: SELECT CONCAT(Class,Name) FROM Players;

d23:
  title: Practice
  type: inputbox
  content: |-
   Enter a query that returns the sum of HP of light Class players:
  command: SELECT SUM(HP) FROM Players WHERE Class IN ('light');

d24:
  title: Order the results
  type: yesno
  content: |-
    To order rows according to a specific column, we use the "ORDER BY ASC|DESC <columns>" statement.
    For columns that contain strings, the order is alphabetic.
    For int, float... columns, the order is numeric.

    Example: To show all columns ordered by PlayerID in ascending order:
    SELECT * FROM Players ORDER BY PlayerID ASC;
  
d25:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows Name and PlayerID ordered by HP in descending order:
  command: SELECT Name, PlayerID FROM Players ORDER BY HP DESC;

d26:
  title: Limit the number of results
  type: yesno
  content: |-
    To limit the number of rows in the output, we can use the LIMIT statement as follows:
    SELECT * FROM table_name LIMIT nb_of_rows;

d27:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows all columns of 5 rows:
  
  command: SELECT * FROM Players LIMIT 5;

d28:
  title: Subqueries
  type: yesno
  content: |-
    A MySQL subquery is a query nested within another query.
    A subquery is enclosed in parenthesis and is executed before the main query.
    
    Example: 
    SELECT * FROM Players WHERE HP = (SELECT MAX(HP) FROM Players);
  
d29:
  title: Practice
  type: inputbox
  content: |-
    Enter a query that shows the maximum HP of players having an "s" in their Name (using a subquery):
  command: SELECT MAX(HP) FROM Players WHERE Name IN ( SELECT Name FROM Players WHERE Name LIKE '%s%' );