Danh Sách

Đồ Chơi Sách
  • Kéo Xuống

    • Học Máy
    • Vui 1 chút
    • Toán Lớp 1.1
    • Toán $
  • Mẹo Nghịch Ảnh Xấu Mù Sản Phẩm Cùi

    Cười

    • Thử Xem
    • Công Thức Hài
    • Kiến thức Về bus
    • Thi Bằng Gì ấy
  • Menu

    Đặng Văn Cường

    • Home
    • TOÁN HỌC hot
      • Ăn Chơi
        • Đùa
        • Cười
      • Công Thức
        • Xàm
        • Vui
        • Đùa Tí
        • Thời Tiết
        • Tiếng Việt
      • Tiếng Anh
        • Tìa Liệu
        • OMG
    • :P
      • Nhích
        • Thích
        • Thì
      • FaceBook
        • Zalo
        • Kinh tế, Xã Hội
    • Khoa Học Đời Sống
    • AI
    Go
    Home » database » mysql » sql » MySQL Sample Databases

    MySQL Sample Databases

    một số db cơ bản

    There are many excellent and interesting sample databases available, that you can use as a template (or pattern) to design your own databases.

    1.  MySQL's Sample Employee Database

    Reference: MySQL's Sample Employees Database @                                                    http://dev.mysql.com/doc/employee/en/index.html.
    This is a rather simple database with 6 tables but with millions of records.

    1.1  Database and Tables

    There are 6 tables as follows:
    Database diagram
    Table "employees"
    CREATE TABLE employees (
        emp_no      INT             NOT NULL,  -- UNSIGNED AUTO_INCREMENT??
        birth_date  DATE            NOT NULL,
        first_name  VARCHAR(14)     NOT NULL,
        last_name   VARCHAR(16)     NOT NULL,
        gender      ENUM ('M','F')  NOT NULL,  -- Enumeration of either 'M' or 'F'  
        hire_date   DATE            NOT NULL,
        PRIMARY KEY (emp_no)                   -- Index built automatically on primary-key column
                                               -- INDEX (first_name)
                                               -- INDEX (last_name)
    );
    There are 300,024 records for this table.
    Table "departments"
    CREATE TABLE departments (
        dept_no     CHAR(4)         NOT NULL,  -- in the form of 'dxxx'
        dept_name   VARCHAR(40)     NOT NULL,
        PRIMARY KEY (dept_no),                 -- Index built automatically
        UNIQUE  KEY (dept_name)                -- Build INDEX on this unique-value column
    );
    The keyword KEY is synonym to INDEX. An INDEX can be built on unique-value column (UNIQUE KEY or UNIQUE INDEX) or non-unique-value column (KEY or INDEX). Indexes greatly facilitates fast search. However, they deplete the performance in INSERT, UPDATE and DELETE. Generally, relational databases are optimized for retrievals, and NOT for modifications.
    There are 9 records for this table.
    Table "dept_emp"
    Junction table to support between many-to-many relationship between employees and departments. A department has many employees. An employee can belong to different department at different dates, and possibly concurrently.
    CREATE TABLE dept_emp (
        emp_no      INT         NOT NULL,
        dept_no     CHAR(4)     NOT NULL,
        from_date   DATE        NOT NULL,
        to_date     DATE        NOT NULL,
        KEY         (emp_no),   -- Build INDEX on this non-unique-value column
        KEY         (dept_no),  -- Build INDEX on this non-unique-value column
        FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
               -- Cascade DELETE from parent table 'employee' to this child table
               -- If an emp_no is deleted from parent 'employee', all records
               --  involving this emp_no in this child table are also deleted
               -- ON UPDATE CASCADE??
        FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
               -- ON UPDATE CASCADE??
        PRIMARY KEY (emp_no, dept_no)
               -- Might not be unique?? Need to include from_date
    );
    The foreign keys have ON DELETE reference action of CASCADE. If a record having a particular key-value from the parent table (employees and departments) is deleted, all the records in this child table having the same key-value are also deleted. Take note that the default ON DELETE reference action of is RESTRICTED, which disallows DELETE on the parent record, if there are matching records in the child table.
    There are two reference actions: ON DELETE and ON UPDATE. The ON UPDATE reference action of is defaulted to RESTRICT (or disallow). It is more meaningful to set ON UPDATE to CASCADE, so that changes in parent table (e.g., change in emp_no and dept_no) can be cascaded down to the child table(s).
    There are 331,603 records for this table.
    Table "dept_manager"
    join table to support between many-to-many relationship between employees and departments. Same structure as dept_emp.
    CREATE TABLE dept_manager (
       dept_no      CHAR(4)  NOT NULL,
       emp_no       INT      NOT NULL,
       from_date    DATE     NOT NULL,
       to_date      DATE     NOT NULL,
       KEY         (emp_no),
       KEY         (dept_no),
       FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
                                      -- ON UPDATE CASCADE??
       FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
       PRIMARY KEY (emp_no, dept_no)  -- might not be unique?? Need from_date
    );
    There are 24 records for this table.
    Table "titles"
    There is a one-to-many relationship between employees and titles. One employee has many titles (concurrently or at different dates). A titles record refers to one employee (via emp_no).
    CREATE TABLE titles (
        emp_no      INT          NOT NULL,
        title       VARCHAR(50)  NOT NULL,
        from_date   DATE         NOT NULL,
        to_date     DATE,
        KEY         (emp_no),
        FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
                             -- ON UPDATE CASCADE??
        PRIMARY KEY (emp_no, title, from_date)
           -- This ensures unique combination. 
           -- An employee may hold the same title but at different period
    );
    There are 443,308 records for this table.
    Table "salaries"
    Similar structure to titles table. One-to-many relationship between employees and salaries.
    CREATE TABLE salaries (
        emp_no      INT    NOT NULL,
        salary      INT    NOT NULL,
        from_date   DATE   NOT NULL,
        to_date     DATE   NOT NULL,
        KEY         (emp_no),
        FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
        PRIMARY KEY (emp_no, from_date)
    );
    There are 2,844,047 records for this table.

    1.2  Stored Objects

    No stored objects (view, procedure, function, trigger, event) defined. [Shall try!]

    2.  MySQL's Sample Salika (DVD Rental) Database

    Reference: MySQL's Sample Sakila Database @ http://dev.mysql.com/doc/sakila/en/index.html.
    The MySQL's Sample Salika (DVD Rental) Database can be downloaded fromhttp://dev.mysql.com/doc/sakila/en/index.html. It is a complex database with 16 tables. It also illustrates features such as Views, Stored Procedures and Triggers. This is probably the best sample available for studying MySQL databases.
    Database diagram

    2.1  Database and Tables

    All the tables have DEFAULT CHARSET of utf8 for internationalization support. All the tables, except film_text, use InnoDB engine, which supports foreign key and transaction. The table film_text uses MyISAM to support FULLTEXT search.
    For UTF8 support, we could set the DEFAULT CHARSET at the database level as follows:
    -- Enable client program to communicate with the server using utf8 character set
    SET NAMES 'utf8';
    
    DROP DATABASE IF EXISTS `sakila`;
    -- Set the default charset to utf8 for internationalization, use case-insensitive (ci) collation
    CREATE DATABASE IF NOT EXISTS `sakila` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    USE `sakila`;
    We could use "SHOW CREATE DATABASE databaseName \G" and "SHOW CREATE TABLE tableName \G" to display all the defaults used in CREATE DATABASE and CREATE TABLE.
    Table "actor"
    CREATE TABLE actor (
      actor_id    SMALLINT     UNSIGNED NOT NULL AUTO_INCREMENT,
                               -- 16-bit unsigned int in the range of [0, 65535]
      first_name  VARCHAR(45)  NOT NULL,
      last_name   VARCHAR(45)  NOT NULL,
      last_update TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (actor_id),
      KEY idx_actor_last_name (last_name)   -- To build index (non-unique) on last_name
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
           -- Use InnoDB Engine, which supports foreign key and transaction
           -- Use Unicode 'utf8' character set for this table
    • There can be one TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP. If you wish to have both create and last_update, you need to use a ON INSERT trigger to set the create TIMESTAMP. For strict auditing, you might have create_timestamp, create_by, last_update_timestamp and last_update_by.
    • InnoDB engine is used, which support foreign key and transaction.
    • The default character set for this table is UTF8, which supports all languages for internationalization.
    • Better to use INT UNSIGNED for AUTO_INCREMENT column actor_id to avoid overrun.
    There are 200 records for this table.
    Table "language"
    Languages: such as English, Italian, Japanese, Mandrain, Cantonese, French, German.
    CREATE TABLE language (
      language_id  TINYINT    UNSIGNED NOT NULL AUTO_INCREMENT,
                              -- 8-bit unsigned int [0, 255]
      name         CHAR(20)   NOT NULL,
      last_update  TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (language_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    May be simpler to use an ENUM (one choice).
    There are 6 records for this table, i.e., 'English', 'Italian', 'Japanese', 'Mandarin', 'French', 'German'.
    Table "film"
    CREATE TABLE film (
      film_id              SMALLINT     UNSIGNED NOT NULL AUTO_INCREMENT,
      title                VARCHAR(255) NOT NULL,
      description          TEXT         DEFAULT NULL,       -- Up to 64KB
      release_year         YEAR         DEFAULT NULL,       -- 'yyyy'
      language_id          TINYINT      UNSIGNED NOT NULL,  -- 8-bit unsigned int [0, 255]
      original_language_id TINYINT      UNSIGNED DEFAULT NULL,
      rental_duration      TINYINT      UNSIGNED NOT NULL DEFAULT 3,
      rental_rate          DECIMAL(4,2) NOT NULL DEFAULT 4.99,  
                                        -- DECIMAL is precise and ideal for currency [99.99]. UNSIGNED?
      length               SMALLINT     UNSIGNED DEFAULT NULL,  -- 16-bit unsigned int [0, 65535]
      replacement_cost     DECIMAL(5,2) NOT NULL DEFAULT 19.99, -- [999.99], UNSIGNED??
      rating               ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G',
      special_features     SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
                                        -- Can take zero or more values from a SET
                                        -- But only one value from ENUM
      last_update          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (film_id),
      KEY idx_title (title),
      KEY idx_fk_language_id (language_id),
      KEY idx_fk_original_language_id (original_language_id),
            -- To build index on title, language_id, original_language_id and film_id (primary key)
      CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
            -- Cannot delete parent record if there is any matching child record
            -- Update the matching child records if parent record is updated
      CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    • Instead of hard-coding the "language" and "original language", it uses language_id to look up the language table, in a one-to-one relationship. Could use an ENUM for language directly for simplicity.
    • KEYs (INDEXes) are defined on certain columns to facilitate fast search on these columns. We would use "SHOW INDEX FROM tableName \G" to display the details on indexes.
    • Should include UNSIGNED for for non-negative numeric columns like rental_rate.
    There are 1000 records for this table.
    Table "film_actor"
    Junction table between actor and film to support the many-to-many relationship.
    CREATE TABLE film_actor (
      actor_id     SMALLINT UNSIGNED NOT NULL,
      film_id      SMALLINT UNSIGNED NOT NULL,
      last_update  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (actor_id, film_id),
      KEY idx_fk_film_id (`film_id`),
      CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 5462 records for this table.
    Table "category"
    CREATE TABLE category (
      category_id  TINYINT      UNSIGNED NOT NULL AUTO_INCREMENT,
      name         VARCHAR(25)  NOT NULL,
      last_update  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (category_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    • There are 16 records for this table, i.e., 'Action', 'Animation', 'Children', 'Classics', 'Comedy', 'Documentary', 'Drama', 'Family', 'Foreign', 'Games', 'Horror', 'Music', 'New', 'Sci-Fi', 'Sports', 'Travel'.
    • May be better to use a SET to support multiple categories per film, if the number of categories is small. A SET is limited to 64 items in MySQL.
    Table "film_category"
    Junction table to support many-to-many relationship between film and category.
    CREATE TABLE film_category (
      film_id      SMALLINT   UNSIGNED NOT NULL,
      category_id  TINYINT    UNSIGNED NOT NULL,
      last_update  TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (film_id, category_id),
      CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 1000 records for this table. Each of the 1000 films has ONE category.
    Table "film_text" - FULLTEXT Index and Search
    CREATE TABLE film_text (
      film_id      SMALLINT      NOT NULL,
      title        VARCHAR(255)  NOT NULL,
      description  TEXT,
      PRIMARY KEY  (film_id),
      FULLTEXT KEY idx_title_description (title, description)
         -- To build index on FULLTEXT to facilitate text search
         -- FULLTEXT is supported in MyISAM engine, NOT in InnoDB engine
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    • This table duplicates information from film table, to support FULLTEXT search. That is, user can efficiently search all the words in title and description columns.
    • To ensure consistency between film_text and film, the rows are inserted/updated via a trigger on film table.
    • FULLTEXT search is supported in MyISAM engine only, not the InnoDB engine. A FULLTEXT index is build on columns (title, description). You can perform FULLTEXT search on the index using "WHERE MATCH(columns) AGAINST(words)", for example,
      mysql> SELECT * FROM film_text
             WHERE MATCH(title, description) AGAINST ('great');
                   -- search for the given word on the FULLTEXT index columns
       
      mysql> SELECT * FROM film_text 
             WHERE MATCH(title, description) AGAINST ('great good');  
                   -- search for either 'great' or 'good'
       
      mysql> SELECT * FROM film_text 
             WHERE MATCH(title, description) AGAINST ('"very good"' IN BOOLEAN MODE);
                   -- Use BOOLEAN MODE to match exact phrase (enclosed in double-quotes)
       
      mysql> SELECT * FROM film_text 
             WHERE MATCH(title, description) AGAINST ('+good -bad' IN BOOLEAN MODE);
                   -- Use BOOLEAN MODE to search for the word 'good', but NOT the word 'bad'
       
      mysql> SELECT * FROM film_text 
             WHERE MATCH(title, description) AGAINST ('great*' IN BOOLEAN MODE);
                   -- In BOOLEAN MODE, wildcard * matches zero or more characters
       
      mysql> SELECT * FROM film_text 
             WHERE MATCH(title, description) AGAINST ('great' WITH QUERY EXPANSION);
                   -- Do a second search on words in the most relevant rows from the first search
    There are 1000 records for this table. Each film record has a film_text counterpart. The records in the film_text table is created via a INSERT trigger on the film table.
    Table "inventory"
    The company could have many copies of a particular film (in one store or many stores). Each copy is represented by an inventory record. The store is linked thru store_id to the table store.
    CREATE TABLE inventory (
      inventory_id  MEDIUMINT  UNSIGNED NOT NULL AUTO_INCREMENT,
                               -- Simpler to use INT UNSIGNED
      film_id       SMALLINT   UNSIGNED NOT NULL,
      store_id      TINYINT    UNSIGNED NOT NULL,
      last_update   TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (inventory_id),
      KEY idx_fk_film_id (film_id),
      KEY idx_store_id_film_id (store_id, film_id),
      CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 4581 records for this table.
    Table "store"
    Each store has a manager, linked thru manager_staff_id to the staff table. The address of the store is also linked thru address_id to the address table.
    CREATE TABLE store (
      store_id          TINYINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      manager_staff_id  TINYINT    UNSIGNED NOT NULL,
      address_id        SMALLINT   UNSIGNED NOT NULL,
      last_update       TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (store_id),
      UNIQUE KEY idx_unique_manager (manager_staff_id),  -- one manager manages only one store
      KEY idx_fk_address_id (address_id),
      CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) 
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 2 records for this table.
    Table "staff"
    CREATE TABLE staff (
      staff_id     TINYINT     UNSIGNED NOT NULL AUTO_INCREMENT,
      first_name   VARCHAR(45) NOT NULL,
      last_name    VARCHAR(45) NOT NULL,
      address_id   SMALLINT    UNSIGNED NOT NULL,
      picture      BLOB        DEFAULT NULL,           -- Kept a picture as BLOB (up to 64KB)
      email        VARCHAR(50) DEFAULT NULL,
      store_id     TINYINT     UNSIGNED NOT NULL,
      active       BOOLEAN     NOT NULL DEFAULT TRUE,  -- BOOLEAN FALSE (0) TRUE (non-0)
      username     VARCHAR(16) NOT NULL,
      password     VARCHAR(40) BINARY DEFAULT NULL,    -- BINARY??
      last_update  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (staff_id),
      KEY idx_fk_store_id (store_id),
      KEY idx_fk_address_id (address_id),
      CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 2 records for this table, with pictures (BLOB) provided.
    Table "customer"
    CREATE TABLE customer (
      customer_id  SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      store_id     TINYINT     UNSIGNED NOT NULL,
      first_name   VARCHAR(45) NOT NULL,
      last_name    VARCHAR(45) NOT NULL,
      email        VARCHAR(50) DEFAULT NULL,
      address_id   SMALLINT    UNSIGNED NOT NULL,
      active       BOOLEAN     NOT NULL DEFAULT TRUE,
      create_date  DATETIME    NOT NULL,
      last_update  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (customer_id),
      KEY idx_fk_store_id (store_id),
      KEY idx_fk_address_id (address_id),
      KEY idx_last_name (last_name),
      CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 599 records for this table.
    Table "rental"
    Rental rate is kept in the film table.
    CREATE TABLE rental (
      rental_id     INT        NOT NULL AUTO_INCREMENT,
      rental_date   DATETIME   NOT NULL,
      inventory_id  MEDIUMINT  UNSIGNED NOT NULL,
      customer_id   SMALLINT   UNSIGNED NOT NULL,
      return_date   DATETIME   DEFAULT NULL,
      staff_id      TINYINT    UNSIGNED NOT NULL,
      last_update   TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (rental_id),
      UNIQUE KEY  (rental_date, inventory_id, customer_id),
      KEY idx_fk_inventory_id (inventory_id),
      KEY idx_fk_customer_id (customer_id),
      KEY idx_fk_staff_id (staff_id),
      CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 16,044 records for this table.
    Table "payment"
    An rental can have multiple payments?
    CREATE TABLE payment (
      payment_id    SMALLINT     UNSIGNED NOT NULL AUTO_INCREMENT,
      customer_id   SMALLINT     UNSIGNED NOT NULL,
      staff_id      TINYINT      UNSIGNED NOT NULL,
      rental_id     INT          DEFAULT NULL,
      amount        DECIMAL(5,2) NOT NULL,
      payment_date  DATETIME     NOT NULL,
      last_update   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (payment_id),
      KEY idx_fk_staff_id (staff_id),
      KEY idx_fk_customer_id (customer_id),
      CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
      CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 16,049 records for this table, more than rental table.
    Table "address"
    It is unlikely that two persons share the same address. Address is often a required field for a rental transaction. So it is probably better to store directly inside the customers table.
    CREATE TABLE address (
      address_id   SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      address      VARCHAR(50) NOT NULL,
      address2     VARCHAR(50) DEFAULT NULL,
      district     VARCHAR(20) NOT NULL,
      city_id      SMALLINT    UNSIGNED NOT NULL,
      postal_code  VARCHAR(10) DEFAULT NULL,
      phone        VARCHAR(20) NOT NULL,
      last_update  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (address_id),
      KEY idx_fk_city_id (city_id),
      CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 603 records for this table.
    Table "city"
    CREATE TABLE city (
      city_id      SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      city         VARCHAR(50) NOT NULL,
      country_id   SMALLINT    UNSIGNED NOT NULL,
      last_update  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (city_id),
      KEY idx_fk_country_id (country_id),
      CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 600 records for this table.
    Table "country"
    Having a country table may facilitate the creation of pull-down menu. Alternatively, you could consider using an ENUM (number of countries may exceed ENUM's limit). For city, there are just too many cities in the world that the list can never be exhaustive. Probably better to keep inside the address table.
    CREATE TABLE country (
      country_id   SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      country      VARCHAR(50) NOT NULL,
      last_update  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY  (country_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 109 records for this table.

    2.2  Views

    A VIEW is a virtual table (without data) that provides an alternate way to look at the data. It could be a consolidated set of columns from multiple table, or include derived column (such as total price).
    We could use "SHOW CREATE VIEW viewName \G" to show all the defaults.
    View "staff_list"
    CREATE VIEW staff_list
    AS
    SELECT 
      s.staff_id AS ID,
      CONCAT(s.first_name, _utf8' ', s.last_name) AS name,
      a.address AS address,
      a.postal_code AS `zip code`,
      a.phone AS phone,
      city.city AS city,
      country.country AS country,
      s.store_id AS SID
    FROM staff AS s 
      JOIN address AS a ON s.address_id = a.address_id
      JOIN city ON a.city_id = city.city_id
      JOIN country ON city.country_id = country.country_id;
    • String literal can be expressed with optional introducer and collation in the form of:
      -- Syntax
      [_charsetName]'stringLiteral' [COLLATE collationName]
       
      -- Example
      SELECT _utf8' ';   -- space in UTF8
    For Example,
    mysql> SELECT * FROM staff_list;
    +----+--------------+----------------------+----------+-------------+------------+-----------+-----+
    | ID | name         | address              | zip code | phone       | city       | country   | SID |
    +----+--------------+----------------------+----------+-------------+------------+-----------+-----+
    |  1 | Mike Hillyer | 23 Workhaven Lane    |          | 14033335568 | Lethbridge | Canada    |   1 |
    |  2 | Jon Stephens | 1411 Lillydale Drive |          | 6172235589  | Woodridge  | Australia |   2 |
    +----+--------------+----------------------+----------+-------------+------------+-----------+-----+
    View "customer_list"
    CREATE VIEW customer_list
    AS
    SELECT 
      cu.customer_id AS ID,
      CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name,
      a.address AS address,
      a.postal_code AS `zip code`,
      a.phone AS phone,
      city.city AS city,
      country.country AS country,
      IF(cu.active, _utf8'active', _utf8'') AS notes,
      cu.store_id AS SID
    FROM customer AS cu 
      JOIN address AS a ON cu.address_id = a.address_id
      JOIN city ON a.city_id = city.city_id
      JOIN country ON city.country_id = country.country_id;
    View "film_list"
    CREATE VIEW film_list
    AS
    SELECT 
      film.film_id AS FID,
      film.title AS title,
      film.description AS description,
      category.name AS category,
      film.rental_rate AS price,
      film.length AS length,
      film.rating AS rating,
      GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
    FROM category 
      LEFT JOIN film_category ON category.category_id = film_category.category_id
      LEFT JOIN film ON film_category.film_id = film.film_id
      JOIN film_actor ON film.film_id = film_actor.film_id
      JOIN actor ON film_actor.actor_id = actor.actor_id
    GROUP BY film.film_id;
    • The GROUP_CONCAT(col SEPARATOR str) GROUP BY aggregate function can be used to produce a concatenate string for each group returned by the GROUP BY clause. Each film_id (in GROUP BY) has many actors.
    • For example,
      mysql> SELECT * FROM film_list LIMIT 1 \G
      *************************** 1. row ***************************
              FID: 1
            title: ACADEMY DINOSAUR
      description: A Epic Drama of a Feminist And a Mad Scientist who must Battle
                   a Teacher in The Canadian Rockies
         category: Documentary
            price: 0.99
           length: 86
           rating: PG
           actors: PENELOPE GUINESS, CHRISTIAN GABLE, LUCILLE TRACY, SANDRA PECK, JOHNNY CAGE,
                   MENA TEMPLE, WARREN NOLTE, OPRAH KILMER, ROCK DUKAKIS, MARY KEITEL
    View "nicer_but_slower_film_list"
    CREATE VIEW nicer_but_slower_film_list
    AS
    SELECT 
      film.film_id AS FID,
      film.title AS title,
      film.description AS description,
      category.name AS category,
      film.rental_rate AS price,
      film.length AS length,
      film.rating AS rating,
      GROUP_CONCAT(
        CONCAT(
           CONCAT(UCASE(SUBSTR(actor.first_name, 1, 1)),   -- first_name initial-cap
                  LCASE(SUBSTR(actor.first_name, 2, LENGTH(actor.first_name))),
           _utf8' ',                                       -- space
           CONCAT(UCASE(SUBSTR(actor.last_name, 1, 1)),    -- last_name initial-cap
                  LCASE(SUBSTR(actor.last_name, 2, LENGTH(actor.last_name))))))  -- end of outer CONCAT
        SEPARATOR ', ') AS actors
    FROM category 
      LEFT JOIN film_category ON category.category_id = film_category.category_id 
      LEFT JOIN film ON film_category.film_id = film.film_id
      JOIN film_actor ON film.film_id = film_actor.film_id
      JOIN actor ON film_actor.actor_id = actor.actor_id
    GROUP BY film.film_id;
    • The complex CONCAT() is used to produce camel-case (initial-capitalized) for the first_name and last_name, e.g., "Penelope Guiness".
    • LENGTH(str) returns the length of the string.
    • SUBSTR(str, fromIndex, length) returns the substring from index of length (index begins at 1).
    • UCASE(str) and LCASE(str) returns the uppercase and lowercase.
    • This view is exactly the same as film_list view. Why is it called nicer_but_slower_film_list?
    View "sales_by_store"
    CREATE VIEW sales_by_store
    AS
    SELECT
      CONCAT(c.city, _utf8',', cy.country) AS store,
      CONCAT(m.first_name, _utf8' ', m.last_name) AS manager,
      SUM(p.amount) AS total_sales
    FROM payment AS p
      INNER JOIN rental AS r ON p.rental_id = r.rental_id
      INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
      INNER JOIN store AS s ON i.store_id = s.store_id
      INNER JOIN address AS a ON s.address_id = a.address_id
      INNER JOIN city AS c ON a.city_id = c.city_id
      INNER JOIN country AS cy ON c.country_id = cy.country_id
      INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
    GROUP BY s.store_id
    ORDER BY cy.country, c.city;
    The SUM() GROUP BY aggregate function applies to each group of store_id, i.e., per store.
    For example,
    +---------------------+--------------+-------------+
    | store               | manager      | total_sales |
    +---------------------+--------------+-------------+
    | Woodridge,Australia | Jon Stephens |    33726.77 |
    | Lethbridge,Canada   | Mike Hillyer |    33679.79 |
    +---------------------+--------------+-------------+
    View "sales_by_film_category"
    CREATE VIEW sales_by_film_category
    AS
    SELECT
      c.name AS category,
      SUM(p.amount) AS total_sales
    FROM payment AS p
      INNER JOIN rental AS r ON p.rental_id = r.rental_id
      INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
      INNER JOIN film AS f ON i.film_id = f.film_id
      INNER JOIN film_category AS fc ON f.film_id = fc.film_id
      INNER JOIN category AS c ON fc.category_id = c.category_id
    GROUP BY c.name
    ORDER BY total_sales DESC;
    The GROUP BY aggregate function SUM() applies to each group of c.name, i.e., per category's name.
    View "actor_info"
    CREATE 
      DEFINER=CURRENT_USER
      SQL SECURITY INVOKER
      VIEW actor_info
    AS
    SELECT
      a.actor_id,
      a.first_name,
      a.last_name,
      GROUP_CONCAT(
         DISTINCT
         CONCAT(c.name, ': ',
            (SELECT 
               GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
               FROM sakila.film f
               INNER JOIN sakila.film_category fc ON f.film_id = fc.film_id
               INNER JOIN sakila.film_actor fa ON f.film_id = fa.film_id
               WHERE fc.category_id = c.category_id AND fa.actor_id = a.actor_id)
            )  -- end CONCAT
         ORDER BY c.name
         SEPARATOR '; ') AS film_info
    FROM sakila.actor a
    LEFT JOIN sakila.film_actor fa ON a.actor_id = fa.actor_id
    LEFT JOIN sakila.film_category fc ON fa.film_id = fc.film_id
    LEFT JOIN sakila.category c ON fc.category_id = c.category_id
    GROUP BY
      a.actor_id,
      a.first_name,
      a.last_name;
    • SQL SECURITY INVOKER specifies that the it executes with the privileges of the user who invoke it (instead of the DEFINER).
    • GROUP_CONCAT([DISTINCT] col [ORDER BY ...] [SEPARATOR ...]): You can apply optional DISTINCT and ORDER BY to GROUP_CONCAT().
    • For example,
      mysql> SELECT * FROM actor_info LIMIT 1 \G
      *************************** 1. row ***************************
        actor_id: 1
      first_name: PENELOPE
       last_name: GUINESS
       film_info: Animation: ANACONDA CONFESSIONS;
                  Children: LANGUAGE COWBOY;
                  Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT;
                  ......

    2.3  Stored Routines: Procedures and Functions

    Procedure "rewards_report"
    -- Change the MySQL statement delimiter to // as it crashes with procedure's delimiter ';'
    DELIMITER //
     
    CREATE PROCEDURE rewards_report (
       IN min_monthly_purchases TINYINT UNSIGNED,              -- min number of purchases
       IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED,  -- min dollar amount purchased
       OUT count_rewardees INT                                 -- number of customers to be rewarded
    )
    LANGUAGE SQL
    NOT DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT 'Provides a customizable report on best customers'
     
    proc: BEGIN
       DECLARE last_month_start DATE;
       DECLARE last_month_end DATE;
    
       /* Some sanity checks... */
       IF min_monthly_purchases = 0 THEN
          SELECT 'Minimum monthly purchases parameter must be > 0';
          LEAVE proc;
       END IF;
       IF min_dollar_amount_purchased = 0.00 THEN
          SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
          LEAVE proc;
       END IF;
     
       /* Determine start and end time periods */
       SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);
       SET last_month_start = STR_TO_DATE(
                                 CONCAT(YEAR(last_month_start), '-', MONTH(last_month_start), '-01'),
                                 '%Y-%m-%d');
       SET last_month_end = LAST_DAY(last_month_start);
    
       /* Create a temporary storage area for Customer IDs */
       CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
    
       /* Find all customers meeting the monthly purchase requirements */
       INSERT INTO tmpCustomer (customer_id)
         SELECT p.customer_id
         FROM payment AS p
         WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end
         GROUP BY customer_id
         HAVING 
           SUM(p.amount) > min_dollar_amount_purchased
           AND COUNT(customer_id) > min_monthly_purchases;
    
       /* Populate OUT parameter with count of found customers */
       SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
    
       /* Output ALL customer information of matching rewardees.
          Customize output as needed. */
       SELECT c.*
         FROM tmpCustomer AS t
         INNER JOIN customer AS c ON t.customer_id = c.customer_id;
    
       /* Clean up */
       DROP TABLE tmpCustomer;
    END //
     
    -- Change the MySQL delimiter back to ';'
    DELIMITER ;
    To test the procedure,
    mysql> CALL rewards_report(2, 10, @numRewardees);
    
    mysel> SELECT @numRewardees;
    Function "get_customer_balance"
    DELIMITER $$
     
    CREATE FUNCTION get_customer_balance(p_customer_id INT, p_effective_date DATETIME) RETURNS DECIMAL(5,2)
       DETERMINISTIC
       READS SQL DATA
    BEGIN
       # OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE
       # THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:
       #   1) RENTAL FEES FOR ALL PREVIOUS RENTALS
       #   2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE
       #   3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST
       #   4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED
    
       DECLARE v_rentfees DECIMAL(5,2);  # FEES PAID TO RENT THE VIDEOS INITIALLY
       DECLARE v_overfees INTEGER;       # LATE FEES FOR PRIOR RENTALS
       DECLARE v_payments DECIMAL(5,2);  # SUM OF PAYMENTS MADE PREVIOUSLY
    
       SELECT IFNULL(SUM(film.rental_rate), 0) INTO v_rentfees
         FROM film, inventory, rental
         WHERE film.film_id = inventory.film_id
          AND inventory.inventory_id = rental.inventory_id
          AND rental.rental_date <= p_effective_date
          AND rental.customer_id = p_customer_id;
     
       SELECT IFNULL(
                 SUM(
                    IF((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) > film.rental_duration,
                       ((TO_DAYS(rental.return_date) - TO_DAYS(rental.rental_date)) - film.rental_duration), 0)),
                 0)
              INTO v_overfees
       FROM rental, inventory, film
       WHERE film.film_id = inventory.film_id
          AND inventory.inventory_id = rental.inventory_id
          AND rental.rental_date <= p_effective_date
          AND rental.customer_id = p_customer_id;
     
       SELECT IFNULL(SUM(payment.amount), 0) INTO v_payments
       FROM payment
       WHERE payment.payment_date <= p_effective_date
          AND payment.customer_id = p_customer_id;
     
       RETURN v_rentfees + v_overfees - v_payments;
    END $$
    DELIMITER ;
    Procedure "film_in_stock"
    DELIMITER $$
    -- Given the film_id and store_id, find the film count
    CREATE PROCEDURE film_in_stock(
       IN  p_film_id INT,
       IN  p_store_id INT,
       OUT p_film_count INT)
    READS SQL DATA
    BEGIN
       SELECT inventory_id
         FROM inventory
         WHERE film_id = p_film_id
           AND store_id = p_store_id
           AND inventory_in_stock(inventory_id);
     
       SELECT FOUND_ROWS() INTO p_film_count;
    END $$
    DELIMITER ;
    Procedure "film_not_in_stock"
    DELIMITER $$
    CREATE PROCEDURE film_not_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
    READS SQL DATA
    BEGIN
       SELECT inventory_id
       FROM inventory
       WHERE film_id = p_film_id
       AND store_id = p_store_id
       AND NOT inventory_in_stock(inventory_id);
     
       SELECT FOUND_ROWS() INTO p_film_count;
    END $$
    DELIMITER ;
    Function "inventory_held_by_customer"
    DELIMITER $$
    CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT
    READS SQL DATA
    BEGIN
       DECLARE v_customer_id INT;
       DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
    
       SELECT customer_id INTO v_customer_id
       FROM rental
       WHERE return_date IS NULL AND inventory_id = p_inventory_id;
    
       RETURN v_customer_id;
    END $$
    DELIMITER ;
    Function "inventory_in_stock"
    DELIMITER $$
    CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
    READS SQL DATA
    BEGIN
       DECLARE v_rentals INT;
       DECLARE v_out     INT;
    
       # AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE
       # FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED
       SELECT COUNT(*) INTO v_rentals
       FROM rental
       WHERE inventory_id = p_inventory_id;
     
       IF v_rentals = 0 THEN
          RETURN TRUE;
       END IF;
     
       SELECT COUNT(rental_id) INTO v_out
       FROM inventory LEFT JOIN rental USING(inventory_id)
       WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL;
     
       IF v_out > 0 THEN
          RETURN FALSE;
       ELSE
          RETURN TRUE;
       END IF;
    END $$
    DELIMITER ;

    2.4  Triggers

    The film_text table duplicates information from film table to build a FULLTEXT search index. To ensure consistency between the two tables, triggers are used for INSERT, UPDATE and DELETE on each row of film table, that perform corresponding actions in the film_text table.
    Trigger "ins_film"
    DELIMITER $$
    -- Trigger for INSERT INTO film table
    -- Copy information to film_text table
    CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW 
    BEGIN
       INSERT INTO film_text (film_id, title, description)
          VALUES (new.film_id, new.title, new.description);
    END$$
    DELIMITER ;
    Trigger "upd_film"
    -- Trigger for UPDATE film table
    -- Update the film_text table
    DELIMITER $$
    CREATE TRIGGER `upd_film` AFTER UPDATE ON `film` FOR EACH ROW 
    BEGIN
       IF (old.title != new.title) or (old.description != new.description)
       THEN
          UPDATE film_text
          SET title=new.title,
              description=new.description,
              film_id=new.film_id
          WHERE film_id=old.film_id;
       END IF;
    END$$
    DELIMITER ;
    Trigger "del_film"
    -- Trigger for DELECT FROM film table
    -- DELETE from film_text table as well
    DELIMITER $$
    CREATE TRIGGER `del_film` AFTER DELETE ON `film` FOR EACH ROW 
    BEGIN
       DELETE FROM film_text WHERE film_id = old.film_id;
    END$$
    DELIMITER ;

    3.  Microsoft Northwind Trader Database

    For MS SQL Server, you can download the Northwind database from "Northwind and Pubs Sample Databases for SQL Server 2000". Run the downloaded ".msi" file, it will extract the files into "C:\SQL Server 2000 Sample Databases". The SQL statements are kept in "instnwnd.sql".
    For MS Access ⇒ Launch Access ⇒ Choose "Sample" ⇒ Northwind Sample Database ⇒ Download.
    There are various MySQL ports available. For example, "northwindextended" project @ http://code.google.com/p/northwindextended.

    3.1  Database and Tables

    There are 13 tables as follows:
    Database diagram
    Table "Customers"
    CREATE TABLE `Customers` (
       `CustomerID`   VARCHAR(5)   NOT NULL,
           -- First 5 letters of CompanyName
           -- Probably better to use an UNSIGNED INT
       `CompanyName`  VARCHAR(40)  NOT NULL,
       `ContactName`  VARCHAR(30),
       `ContactTitle` VARCHAR(30),
       `Address`      VARCHAR(60),
       `City`         VARCHAR(15),
       `Region`       VARCHAR(15),
       `PostalCode`   VARCHAR(10),
       `Country`      VARCHAR(15),
       `Phone`        VARCHAR(24),
       `Fax`          VARCHAR(24),
       PRIMARY KEY (`CustomerID`),
       INDEX (`City`),
       INDEX (`CompanyName`),
       INDEX (`PostalCode`),
       INDEX (`Region`)
           -- Build indexes on these columns for fast search
    );
    There are 93 records for this table.
    Table "Employees"
    CREATE TABLE `Employees` (
       `EmployeeID`      MEDIUMINT UNSIGNED  NOT NULL AUTO_INCREMENT,
                         -- [0, 65535]
       `LastName`        VARCHAR(20)         NOT NULL,
       `FirstName`       VARCHAR(10)         NOT NULL,
       `Title`           VARCHAR(30),  -- e.g., 'Sales Coordinator'
       `TitleOfCourtesy` VARCHAR(25),  -- e.g., 'Mr.' 'Ms.' (ENUM??)
       `BirthDate`       DATE,         -- 'YYYY-MM-DD'
       `HireDate`        DATE,
       `Address`         VARCHAR(60),
       `City`            VARCHAR(15),
       `Region`          VARCHAR(15),
       `PostalCode`      VARCHAR(10),
       `Country`         VARCHAR(15),
       `HomePhone`       VARCHAR(24),
       `Extension`       VARCHAR(4),
       `Photo`           BLOB,                          -- 64KB
       `Notes`           TEXT                NOT NULL,  -- 64KB
       `ReportsTo`       MEDIUMINT UNSIGNED  NULL,  -- Manager's ID
                                                    -- Allow NULL for boss
       `PhotoPath`       VARCHAR(255),
       `Salary`          INT,
       INDEX (`LastName`),
       INDEX (`PostalCode`),
       PRIMARY KEY (`EmployeeID`),
       FOREIGN KEY (`ReportsTo`) REFERENCES `Employees` (`EmployeeID`)
    );
    To load this table with the sample data provided, you need to move the second record as the first record and hardcode the employeeID. There are 9 records for this table. The photos are included as hex data.
    To list the worker names under the manager names, you need to join the employee table to itself. Use LEFT JOIN to retrieve ReportsTo of NULL.
    -- List the worker names under the managers' ID
    SELECT reportsTo AS `Manager ID`, CONCAT(employees.FirstName, ' ', employees.LastName) AS `Workers`
    FROM employees
    ORDER BY reportsTo;
    +------------+------------------+
    | Manager ID | Workers          |
    +------------+------------------+
    |       NULL | Andrew Fuller    |
    |          2 | Nancy Davolio    |
    |          2 | Janet Leverling  |
    |          2 | Margaret Peacock |
    |          2 | Steven Buchanan  |
    |          2 | Laura Callahan   |
    |          5 | Michael Suyama   |
    |          5 | Robert King      |
    |          5 | Anne Dodsworth   |
    +------------+------------------+
     
    -- List the worker name under the managers' name
    -- Need to use a LEFT JOIN
    SELECT
      CONCAT(managers.FirstName, ' ', managers.LastName) AS `Managers`, 
      CONCAT(employees.FirstName, ' ', employees.LastName) AS `Workers`
    FROM
      employees LEFT JOIN employees AS managers ON employees.ReportsTo = managers.employeeID
    ORDER BY
      managers.employeeID;
    +-----------------+------------------+
    | Managers        | Workers          |
    +-----------------+------------------+
    | NULL            | Andrew Fuller    |
    | Andrew Fuller   | Margaret Peacock |
    | Andrew Fuller   | Laura Callahan   |
    | Andrew Fuller   | Nancy Davolio    |
    | Andrew Fuller   | Steven Buchanan  |
    | Andrew Fuller   | Janet Leverling  |
    | Steven Buchanan | Robert King      |
    | Steven Buchanan | Anne Dodsworth   |
    | Steven Buchanan | Michael Suyama   |
    +-----------------+------------------+
    Table "Region"
    CREATE TABLE `Region` (
       `RegionID`           TINYINT UNSIGNED  NOT NULL AUTO_INCREMENT,
                            -- [0,255]
       `RegionDescription`  VARCHAR(50)       NOT NULL,
                            -- e.g., 'Eastern','Western','Northern','Southern'
                            -- Could use an ENUM and eliminate this table
       PRIMARY KEY (`RegionID`)
    );
    There are 4 records for this table ('Eastern', 'Western', 'Northern', 'Southern').
    Table "Territories"
    -- e.g., ('02116', 'Boston', 1)
    CREATE TABLE `Territories` (
       `TerritoryID`           VARCHAR(20)       NOT NULL,  -- ZIP code
       `TerritoryDescription`  VARCHAR(50)       NOT NULL,  -- Name
       `RegionID`              TINYINT UNSIGNED  NOT NULL,
                               -- Could use an ENUM to eliminate `Region` table
       PRIMARY KEY (`TerritoryID`),
       FOREIGN KEY (`RegionID`) REFERENCES `Region` (`RegionID`)
    );
    There are 53 records for this table.
    Table "EmployeeTerritories"
    -- Many-to-many Junction table between Employee and Territory
    CREATE TABLE `EmployeeTerritories` (
       `EmployeeID`  MEDIUMINT UNSIGNED  NOT NULL,
       `TerritoryID` VARCHAR(20) NOT NULL,
       PRIMARY KEY (`EmployeeID`, `TerritoryID`),
       FOREIGN KEY (`EmployeeID`) REFERENCES `Employees` (`EmployeeID`),
       FOREIGN KEY (`TerritoryID`) REFERENCES `Territories` (`TerritoryID`)
    );
    There are 49 records for this table. Each employee has more than one territories. Some territories are not covered (53-49=4).
    SELECT EmployeeID, COUNT(*) from EmployeeTerritories GROUP BY EmployeeID WITH ROLLUP;
    +------------+----------+
    | EmployeeID | COUNT(*) |
    +------------+----------+
    |          1 |        2 |
    |          2 |        7 |
    |          3 |        4 |
    |          4 |        3 |
    |          5 |        7 |
    |          6 |        5 |
    |          7 |       10 |
    |          8 |        4 |
    |          9 |        7 |
    |       NULL |       49 |
    +------------+----------+
     
    SELECT TerritoryID, TerritoryDescription
    FROM Territories LEFT JOIN EmployeeTerritories using (TerritoryID)
    WHERE EmployeeID IS NULL;
    +-------------+----------------------+
    | TerritoryID | TerritoryDescription |
    +-------------+----------------------+
    | 29202       | Columbia             |
    | 72716       | Bentonville          |
    | 75234       | Dallas               |
    | 78759       | Austin               |
    +-------------+----------------------+
    Table "Categories"
    CREATE TABLE `Categories` (
       `CategoryID`   TINYINT UNSIGNED  NOT NULL AUTO_INCREMENT,
                      -- [0, 255], not expected to be large
       `CategoryName` VARCHAR(30)       NOT NULL,
                      -- e.g., 'Beverages','Condiments',etc
       `Description`  TEXT,       -- up to 64KB characters
       `Picture`      BLOB,       -- up to 64KB binary
       PRIMARY KEY  (`CategoryID`),
       UNIQUE INDEX (`CategoryName`)
          -- Build index on this unique-value column for fast search
    );
    There are 8 records for the table, with pictures in hex code.
    Table "Suppliers"
    CREATE TABLE `Suppliers` (
       `SupplierID`   SMALLINT UNSIGNED  NOT NULL AUTO_INCREMENT,
                                         -- [0, 65535]
       `CompanyName`  VARCHAR(40)        NOT NULL,
       `ContactName`  VARCHAR(30),
       `ContactTitle` VARCHAR(30),
       `Address`      VARCHAR(60),
       `City`         VARCHAR(15),
       `Region`       VARCHAR(15),
       `PostalCode`   VARCHAR(10),
       `Country`      VARCHAR(15),
       `Phone`        VARCHAR(24),
       `Fax`          VARCHAR(24),
       `HomePage`     TEXT,          -- 64KB?? VARCHAR(255)?
        PRIMARY KEY (`SupplierID`),
        INDEX (`CompanyName`),       -- UNIQUE?
        INDEX (`PostalCode`)
    );
    There are 29 records for this table.
    Table "Products"
    CREATE TABLE `Products` (
       `ProductID`       SMALLINT UNSIGNED       NOT NULL AUTO_INCREMENT,
       `ProductName`     VARCHAR(40)             NOT NULL,
       `SupplierID`      SMALLINT UNSIGNED       NOT NULL,  -- one supplier only
       `CategoryID`      TINYINT UNSIGNED        NOT NULL,
       `QuantityPerUnit` VARCHAR(20),            -- e.g., '10 boxes x 20 bags'
       `UnitPrice`       DECIMAL(10,2) UNSIGNED  DEFAULT 0,
       `UnitsInStock`    SMALLINT                DEFAULT 0,  -- Negative??
       `UnitsOnOrder`    SMALLINT UNSIGNED       DEFAULT 0,
       `ReorderLevel`    SMALLINT UNSIGNED       DEFAULT 0,
       `Discontinued`    BOOLEAN                 NOT NULL DEFAULT FALSE,
       PRIMARY KEY (`ProductID`),
       INDEX (`ProductName`),
       FOREIGN KEY (`CategoryID`) REFERENCES `Categories` (`CategoryID`),
       FOREIGN KEY (`SupplierID`) REFERENCES `Suppliers` (`SupplierID`)
    );
    There are 77 records for this table.
    Table "Shippers"
    CREATE TABLE `Shippers` (
       `ShipperID`   TINYINT UNSIGNED  NOT NULL AUTO_INCREMENT,
       `CompanyName` VARCHAR(40)       NOT NULL,
       `Phone`       VARCHAR(24),
       PRIMARY KEY (`ShipperID`)
    );
    There are 3 records for this table.
    Table "Orders"
    CREATE TABLE `Orders` (
       `OrderID`        INT UNSIGNED        NOT NULL AUTO_INCREMENT,
                        -- Use UNSIGNED INT to avoid run-over
       `CustomerID`     VARCHAR(5),
       `EmployeeID`     MEDIUMINT UNSIGNED  NOT NULL,
       `OrderDate`      DATE,
       `RequiredDate`   DATE,
       `ShippedDate`    DATE,
       `ShipVia`        TINYINT UNSIGNED,
       `Freight`        DECIMAL(10,2) UNSIGNED  DEFAULT 0,
       `ShipName`       VARCHAR(40),
       `ShipAddress`    VARCHAR(60),
       `ShipCity`       VARCHAR(15),
       `ShipRegion`     VARCHAR(15),
       `ShipPostalCode` VARCHAR(10),
       `ShipCountry`    VARCHAR(15),
       PRIMARY KEY (`OrderID`),
       INDEX (`OrderDate`),
       INDEX (`ShippedDate`),
       INDEX (`ShipPostalCode`),
       FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`),
       FOREIGN KEY (`EmployeeID`) REFERENCES `Employees` (`EmployeeID`),
       FOREIGN KEY (`ShipVia`)    REFERENCES `Shippers`  (`ShipperID`)
    );
    There are 830 records for this table.
    Table "Order Details"
    -- Many-to-many Junction table between Orders and Products
    CREATE TABLE `Order Details` (
       `OrderID`   INT UNSIGNED           NOT NULL,
       `ProductID` SMALLINT UNSIGNED      NOT NULL,
       `UnitPrice` DECIMAL(8,2) UNSIGNED  NOT NULL DEFAULT 999999.99,
                                          -- max value as default
       `Quantity`  SMALLINT(2) UNSIGNED   NOT NULL DEFAULT 1,
       `Discount`  DOUBLE(8,0)            NOT NULL DEFAULT 0, -- e.g., 0.15
       PRIMARY KEY (`OrderID`, `ProductID`),
       FOREIGN KEY (`OrderID`)   REFERENCES `Orders`   (`OrderID`),
       FOREIGN KEY (`ProductID`) REFERENCES `Products` (`ProductID`)
    );
    There are 2155 records for this table.
    -- List the number of `Order Details` for each OrderID
    SELECT OrderID, COUNT(OrderID)
    FROM Orders INNER JOIN `Order Details` USING (OrderID)
    GROUP BY OrderID
    WITH ROLLUP;
    Table "CustomerDemographics"
    CREATE TABLE `CustomerDemographics` (
       `CustomerTypeID`  VARCHAR(10)  NOT NULL,
       `CustomerDesc`    TEXT,        -- 64KB
       PRIMARY KEY (`CustomerTypeID`)
    );
    No record is provided for this table?!
    Table "CustomerCustomerDemo"
    CREATE TABLE `CustomerCustomerDemo` (
       `CustomerID`     VARCHAR(5)   NOT NULL,
       `CustomerTypeID` VARCHAR(10)  NOT NULL,
       PRIMARY KEY (`CustomerID`, `CustomerTypeID`),
       FOREIGN KEY (`CustomerTypeID`) REFERENCES `CustomerDemographics` (`CustomerTypeID`),
       FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`)
    );
    No record is provided for this table too?!

    3.2  Views

    There are 16 views defined.
    View "Current Product List"
    -- List current products (not discontinued)
    CREATE VIEW `Current Product List`
    AS
    SELECT
       ProductID,
       ProductName 
    FROM Products 
    WHERE Discontinued = 0;
    View "Alphabetical list of products"
    -- List products (with category) order by ProductID
    -- which is arranged alphabetically in ProductName
    CREATE VIEW `Alphabetical list of products`
    AS
    SELECT 
       Products.*, 
       Categories.CategoryName
    FROM Categories 
       INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
    WHERE Products.Discontinued = 0;  -- FALSE
    -- Example
    mysql> SELECT * FROM `Alphabetical list of products` LIMIT 1 \G
    *************************** 1. row ***************************
          ProductID: 1
        ProductName: Chai
         SupplierID: 1
         CategoryID: 1
    QuantityPerUnit: 10 boxes x 20 bags
          UnitPrice: 18.00
       UnitsInStock: 39
       UnitsOnOrder: 0
       ReorderLevel: 10
       Discontinued: 0
       CategoryName: Beverages
    View "Products by Category"
    -- List all products grouped by category
    CREATE VIEW `Products by Category`
    AS
    SELECT 
       Categories.CategoryName, 
       Products.ProductName, 
       Products.QuantityPerUnit, 
       Products.UnitsInStock, 
       Products.Discontinued
    FROM Categories 
       INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
    WHERE Products.Discontinued = 0;  -- FALSE
    -- Example
    mysql> SELECT * FROM `Products by Category`;
    +----------------+----------------------------------+----------------------+--------------+--------------+
    | CategoryName   | ProductName                      | QuantityPerUnit      | UnitsInStock | Discontinued |
    +----------------+----------------------------------+----------------------+--------------+--------------+
    | Beverages      | Chai                             | 10 boxes x 20 bags   |           39 |            0 |
    | Beverages      | Chang                            | 24 - 12 oz bottles   |           17 |            0 |
    | Beverages      | Sasquatch Ale                    | 24 - 12 oz bottles   |          111 |            0 |
     .......
    View "Products Above Average Price"
    CREATE VIEW `Products Above Average Price`
    AS
    SELECT
       Products.ProductName, 
       Products.UnitPrice
    FROM Products
    WHERE Products.UnitPrice > (SELECT AVG(UnitPrice) From Products);  -- subquery
    mysql> SELECT * FROM `Products Above Average Price` ORDER BY UnitPrice DESC;
    +---------------------------------+-----------+
    | ProductName                     | UnitPrice |
    +---------------------------------+-----------+
    | Cte de Blaye                    |    263.50 |
    | Thringer Rostbratwurst          |    123.79 |
    | Mishi Kobe Niku                 |     97.00 |
     ......
    View "Customer and Suppliers by City"
    -- List all customers and suppliers (with an union)
    -- order by City and CompanyName
    CREATE VIEW `Customer and Suppliers by City`
    AS
    SELECT 
       City, 
       CompanyName, 
       ContactName, 
       'Customers' AS Relationship 
    FROM Customers
    UNION  -- Union two result sets (of same column numbers), remove duplicates
    SELECT City, 
       CompanyName, 
       ContactName, 
       'Suppliers'
    FROM Suppliers 
    ORDER BY City, CompanyName;
    mysql> SELECT * FROM `Customer and Suppliers by City` LIMIT 10;
    +--------------+----------------------------+------------------+--------------+
    | City         | CompanyName                | ContactName      | Relationship |
    +--------------+----------------------------+------------------+--------------+
    | NULL         | IT                         | Val2             | Customers    |
    | NULL         | IT                         | Valon Hoti       | Customers    |
    | Aachen       | Drachenblut Delikatessen   | Sven Ottlieb     | Customers    |
    | Albuquerque  | Rattlesnake Canyon Grocery | Paula Wilson     | Customers    |
    | Anchorage    | Old World Delicatessen     | Rene Phillips    | Customers    |
    | Ann Arbor    | Grandma Kelly's Homestead  | Regina Murphy    | Suppliers    |
     ......
    View "Order Details Extended"
    -- Extend `Order Details` to include ProductName and TotalPrice
    CREATE VIEW `Order Details Extended`
    AS
    SELECT
       `Order Details`.OrderID, 
       `Order Details`.ProductID, 
       Products.ProductName, 
       `Order Details`.UnitPrice, 
       `Order Details`.Quantity, 
       `Order Details`.Discount, 
       ROUND(`Order Details`.UnitPrice*Quantity*(1-Discount)) AS ExtendedPrice
    FROM Products 
       JOIN `Order Details` ON Products.ProductID = `Order Details`.ProductID;
    mysql> SELECT * FROM `Order Details Extended`;
    +---------+-----------+--------------+-----------+----------+----------+---------------+
    | OrderID | ProductID | ProductName  | UnitPrice | Quantity | Discount | ExtendedPrice |
    +---------+-----------+--------------+-----------+----------+----------+---------------+
    |   10265 |        17 | Alice Mutton |     31.20 |       30 |        0 |           936 |
    |   10279 |        17 | Alice Mutton |     31.20 |       15 |        0 |           468 |
    |   10294 |        17 | Alice Mutton |     31.20 |       15 |        0 |           468 |
     ......
    View "Invoices"
    -- All information (order, customer, shipper)
    -- for each `Order Details` line.
    -- An invoice is supposed to be per order?!
    CREATE VIEW `Invoices`
    AS
    SELECT 
       Orders.ShipName,
       Orders.ShipAddress,
       Orders.ShipCity,
       Orders.ShipRegion, 
       Orders.ShipPostalCode,
       Orders.ShipCountry,
       Orders.CustomerID,
       Customers.CompanyName AS CustomerName,
       Customers.Address,
       Customers.City,
       Customers.Region,
       Customers.PostalCode,
       Customers.Country,
       (Employees.FirstName + ' ' + Employees.LastName) AS Salesperson, 
       Orders.OrderID,
       Orders.OrderDate,
       Orders.RequiredDate,
       Orders.ShippedDate, 
       Shippers.CompanyName As ShipperName,
       `Order Details`.ProductID,
       Products.ProductName, 
       `Order Details`.UnitPrice,
       `Order Details`.Quantity,
       `Order Details`.Discount, 
       FLOOR(`Order Details`.UnitPrice*Quantity*(1-Discount)) AS ExtendedPrice,
             -- truncate to nearest dollars
       Orders.Freight
    FROM Customers
       JOIN Orders ON Customers.CustomerID = Orders.CustomerID  
       JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID    
       JOIN `Order Details` ON Orders.OrderID = `Order Details`.OrderID     
       JOIN Products ON Products.ProductID = `Order Details`.ProductID      
       JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia;
    -- Example
    mysql> SELECT * FROM `Invoices` LIMIT 2 \G
    *************************** 1. row ***************************
          ShipName: Ernst Handel
          ......
        CustomerID: ERNSH
      CustomerName: Ernst Handel
          ......
       Salesperson: 0
           OrderID: 10258
         OrderDate: 1996-07-17
      RequiredDate: 1996-08-14
       ShippedDate: 1996-07-23
       ShipperName: Speedy Express
         ProductID: 2
       ProductName: Chang
         UnitPrice: 15.20
          Quantity: 50
          Discount: 0
     ExtendedPrice: 760
           Freight: 140.51
    *************************** 2. row ***************************
          ShipName: Ernst Handel
          ......
        CustomerID: ERNSH
      CustomerName: Ernst Handel
          ......
       Salesperson: 0
           OrderID: 10258
         OrderDate: 1996-07-17
      RequiredDate: 1996-08-14
       ShippedDate: 1996-07-23
       ShipperName: Speedy Express
         ProductID: 5
       ProductName: Chef Anton's Gumbo Mix
         UnitPrice: 17.00
          Quantity: 65
          Discount: 0
     ExtendedPrice: 1105
           Freight: 140.51
    View "Orders Qry"
    -- List details (order and customer) of each order
    --   for customer query
    CREATE VIEW `Orders Qry`
    AS
    SELECT 
       Orders.OrderID,
       Orders.CustomerID,
       Orders.EmployeeID, 
       Orders.OrderDate, 
       Orders.RequiredDate,
       Orders.ShippedDate, 
       Orders.ShipVia, 
       Orders.Freight,
       Orders.ShipName, 
       Orders.ShipAddress, 
       Orders.ShipCity,
       Orders.ShipRegion,
       Orders.ShipPostalCode,
       Orders.ShipCountry,
       Customers.CompanyName,
       Customers.Address,
       Customers.City,
       Customers.Region,
       Customers.PostalCode, 
       Customers.Country
    FROM Customers 
       JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
    -- Example
    mysql> SELECT * FROM `Orders Qry` LIMIT 1 \G;
    *************************** 1. row ***************************
           OrderID: 10643
        CustomerID: ALFKI
        EmployeeID: 6
         OrderDate: 1997-08-25
      RequiredDate: 1997-09-22
       ShippedDate: 1997-09-02
           ShipVia: 1
           Freight: 29.46
          ShipName: Alfreds Futterkiste
          ......
       CompanyName: Alfreds Futterkiste
          ......
    View "Product Sales for 1997"
    -- List sales for each productName for 1997
    CREATE VIEW `Product Sales for 1997`
    AS
    SELECT 
       Categories.CategoryName, 
       Products.ProductName, 
       Sum(ROUND(`Order Details`.UnitPrice*Quantity*(1-Discount))) AS ProductSales
    FROM Categories
       JOIN Products On Categories.CategoryID = Products.CategoryID
       JOIN `Order Details` on Products.ProductID = `Order Details`.ProductID     
       JOIN `Orders` on Orders.OrderID = `Order Details`.OrderID 
    WHERE Orders.ShippedDate BETWEEN '1997-01-01' And '1997-12-31'
    GROUP BY Categories.CategoryName, Products.ProductName;
    -- Example
    mysql> SELECT * FROM `Product Sales for 1997`;
    +----------------+----------------------------------+--------------+
    | CategoryName   | ProductName                      | ProductSales |
    +----------------+----------------------------------+--------------+
    | Beverages      | Chai                             |         5296 |
    | Beverages      | Chang                            |         7600 |
    | Beverages      | Chartreuse verte                 |         4928 |
     ......
    View "Sales by Category"
    -- List Sales by ProductName
    CREATE VIEW `Sales by Category`
    AS
    SELECT
       Categories.CategoryID, 
       Categories.CategoryName, 
       Products.ProductName, 
       Sum(`Order Details Extended`.ExtendedPrice) AS ProductSales
    FROM Categories 
       JOIN Products ON Categories.CategoryID = Products.CategoryID
       JOIN `Order Details Extended` ON Products.ProductID = `Order Details Extended`.ProductID
       JOIN Orders ON Orders.OrderID = `Order Details Extended`.OrderID 
    WHERE Orders.OrderDate BETWEEN '1997-01-01' And '1997-12-31'
    GROUP BY
       Categories.CategoryID,
       Categories.CategoryName,
       Products.ProductName;
    mysql> SELECT * FROM `Sales by Category`;
    +------------+----------------+----------------------------------+--------------+
    | CategoryID | CategoryName   | ProductName                      | ProductSales |
    +------------+----------------+----------------------------------+--------------+
    |          1 | Beverages      | Chai                             |         5296 |
    |          1 | Beverages      | Chang                            |         7600 |
    |          1 | Beverages      | Chartreuse verte                 |         4928 |
     ......
    View "Category Sales for 1997"
    CREATE VIEW `Category Sales for 1997`
    AS
    SELECT
       `Product Sales for 1997`.CategoryName,   -- Use `Product Sales for 1997` view
       Sum(`Product Sales for 1997`.ProductSales) AS CategorySales
    FROM `Product Sales for 1997`
    GROUP BY `Product Sales for 1997`.CategoryName;
    mysql> SELECT * FROM `Category Sales for 1997`;
    +----------------+---------------+
    | CategoryName   | CategorySales |
    +----------------+---------------+
    | Beverages      |        108547 |
    | Condiments     |         59586 |
    | Confections    |         85678 |
     ......
    View "Quarterly Orders"
    -- List sales by customers in 1997
    CREATE VIEW `Quarterly Orders`
    AS
    SELECT DISTINCT 
       Customers.CustomerID, 
       Customers.CompanyName, 
       Customers.City, 
       Customers.Country
    FROM Customers 
       JOIN Orders ON Customers.CustomerID = Orders.CustomerID
    WHERE Orders.OrderDate BETWEEN '1997-01-01' And '1997-12-31';
    mysql> SELECT * FROM `Quarterly Orders`;
    +------------+------------------------------------+-----------------+-------------+
    | CustomerID | CompanyName                        | City            | Country     |
    +------------+------------------------------------+-----------------+-------------+
    | ALFKI      | Alfreds Futterkiste                | Berlin          | Germany     |
    | ANATR      | Ana Trujillo Emparedados y helados | Mxico D.F.      | Mexico      |
    | ANTON      | Antonio Moreno Taquera             | Mxico D.F.      | Mexico      |
     .......
    View "Order Subtotals"
    -- List the total amount for each order
    CREATE VIEW `Order Subtotals` 
    AS
    SELECT 
       `Order Details`.OrderID, 
       Sum(ROUND(`Order Details`.UnitPrice*Quantity*(1-Discount))) AS Subtotal
    FROM `Order Details`
    GROUP BY `Order Details`.OrderID;
    -- Example
    mysql> SELECT * FROM `Order Subtotals` LIMIT 5;
    +---------+----------+
    | OrderID | Subtotal |
    +---------+----------+
    |   10248 |      440 |
    |   10249 |     1863 |
    |   10250 |     1813 |
    |   10251 |      671 |
    |   10252 |     3730 |
    +---------+----------+
    View "Sales Totals by Amount"
    CREATE VIEW `Sales Totals by Amount`
    AS
    SELECT 
       `Order Subtotals`.Subtotal AS SaleAmount,   -- `Order Subtotals` is a view
       Orders.OrderID, 
       Customers.CompanyName, 
       Orders.ShippedDate
    FROM Customers 
       JOIN Orders ON Customers.CustomerID = Orders.CustomerID
       JOIN `Order Subtotals` ON Orders.OrderID = `Order Subtotals`.OrderID 
    WHERE (`Order Subtotals`.Subtotal > 2500) 
       AND (Orders.ShippedDate BETWEEN '1997-01-01' And '1997-12-31');
    -- Example
    mysql> SELECT * FROM `Sales Totals by Amount`;
    +------------+---------+------------------------------+-------------+
    | SaleAmount | OrderID | CompanyName                  | ShippedDate |
    +------------+---------+------------------------------+-------------+
    |       3302 |   10393 | Save-a-lot Markets           | 1997-01-03  |
    |       2736 |   10398 | Save-a-lot Markets           | 1997-01-09  |
    |       3063 |   10400 | Eastern Connection           | 1997-01-16  |
     ......
    View "Summary of Sales by Quarter"
    CREATE VIEW `Summary of Sales by Quarter`
    AS
    SELECT 
       Orders.ShippedDate, 
       Orders.OrderID, 
       `Order Subtotals`.Subtotal  -- Use `Order Subtotals` view
    FROM Orders 
       INNER JOIN `Order Subtotals` ON Orders.OrderID = `Order Subtotals`.OrderID
    WHERE Orders.ShippedDate IS NOT NULL;
    -- Example
    mysql> SELECT * FROM `Summary of Sales by Quarter`;
    +-------------+---------+----------+
    | ShippedDate | OrderID | Subtotal |
    +-------------+---------+----------+
    | 1996-07-16  |   10248 |      440 |
    | 1996-07-10  |   10249 |     1863 |
    | 1996-07-12  |   10250 |     1813 |
     ......
    View "Summary of Sales by Year"
    -- List each order
    CREATE VIEW `Summary of Sales by Year`
    AS
    SELECT
       Orders.ShippedDate, 
       Orders.OrderID, 
       `Order Subtotals`.Subtotal
    FROM Orders 
       INNER JOIN `Order Subtotals` ON Orders.OrderID = `Order Subtotals`.OrderID
    WHERE Orders.ShippedDate IS NOT NULL;
    -- Example
    mysql> SELECT * FROM `Summary of Sales by Year`;
    +-------------+---------+----------+
    | ShippedDate | OrderID | Subtotal |
    +-------------+---------+----------+
    | 1996-07-16  |   10248 |      440 |
    | 1996-07-10  |   10249 |     1863 |
    | 1996-07-12  |   10250 |     1813 |
     ......

    3.3  Stored Routines: Procedures and Functions

    There are 7 procedures defined.
    Procedure "CustOrdersDetail"
    -- Given an OrderID, print `Order Details`
    DELIMITER $$
    CREATE PROCEDURE `CustOrdersDetail`(IN AtOrderID INT)
    BEGIN
       SELECT ProductName,
          `Order Details`.UnitPrice,
          Quantity,
          Discount * 100 AS `Discount`, 
          ROUND(Quantity * (1 - Discount) * `Order Details`.UnitPrice) AS ExtendedPrice
       FROM Products INNER JOIN `Order Details` USING (ProductID)
       WHERE `Order Details`.OrderID = AtOrderID;
    END$$
    DELIMITER ;
    mysql> CALL `CustOrdersDetail`(10250);
    +----------------------------------+-----------+----------+----------+---------------+
    | ProductName                      | UnitPrice | Quantity | Discount | ExtendedPrice |
    +----------------------------------+-----------+----------+----------+---------------+
    | Jack's New England Clam Chowder  |      7.70 |       10 |        0 |            77 |
    | Manjimup Dried Apples            |     42.40 |       35 |        0 |          1484 |
    | Louisiana Fiery Hot Pepper Sauce |     16.80 |       15 |        0 |           252 |
    +----------------------------------+-----------+----------+----------+---------------+
    Procedure "CustOrdersOrders"
    DELIMITER $$
    CREATE PROCEDURE `CustOrdersOrders`(IN AtCustomerID VARCHAR(5))
    BEGIN
       SELECT 
          OrderID,
          OrderDate,
          RequiredDate,
          ShippedDate
       FROM Orders
       WHERE CustomerID = AtCustomerID
       ORDER BY OrderID;
    END $$
    DELIMITER ;
    mysql> CALL `CustOrdersOrders`('ANTON');
    +---------+------------+--------------+-------------+
    | OrderID | OrderDate  | RequiredDate | ShippedDate |
    +---------+------------+--------------+-------------+
    |   10365 | 1996-11-27 | 1996-12-25   | 1996-12-02  |
    |   10507 | 1997-04-15 | 1997-05-13   | 1997-04-22  |
    |   10535 | 1997-05-13 | 1997-06-10   | 1997-05-21  |
     ......
    Procedure "CustOrderHist"
    DELIMITER $$
    CREATE PROCEDURE `CustOrderHist`(IN AtCustomerID VARCHAR(5))
    BEGIN
       SELECT
          ProductName,
          SUM(Quantity) as TOTAL
       FROM Products
          INNER JOIN `Order Details` USING(ProductID)
          INNER JOIN Orders USING (OrderID)
          INNER JOIN Customers USING (CustomerID)
       WHERE Customers.CustomerID = AtCustomerID
       GROUP BY ProductName;
    END $$
    DELIMITER ;
    mysql> CALL `CustOrderHist`('ANTON');
    +-------------------------------+-------+
    | ProductName                   | TOTAL |
    +-------------------------------+-------+
    | Alice Mutton                  |    18 |
    | Boston Crab Meat              |    10 |
    | Chang                         |    20 |
     ......
    Procedure "Ten Most Expensive Products"
    DROP PROCEDURE IF EXISTS `Ten Most Expensive Products`;
    DELIMITER $$
    CREATE PROCEDURE `Ten Most Expensive Products`()
    BEGIN
       SELECT 
          Products.ProductName AS TenMostExpensiveProducts,
          Products.UnitPrice
       FROM Products
       ORDER BY Products.UnitPrice DESC
       LIMIT 10;
    END $$
    DELIMITER ;
    mysql> CALL `Ten Most Expensive Products`;
    +--------------------------+-----------+
    | TenMostExpensiveProducts | UnitPrice |
    +--------------------------+-----------+
    | Cte de Blaye             |    263.50 |
    | Thringer Rostbratwurst   |    123.79 |
    | Mishi Kobe Niku          |     97.00 |
     ......
    Procedure "Employee Sales by Country"
    DELIMITER $$
    CREATE PROCEDURE `Employee Sales by Country`(IN AtBeginning_Date DATE, IN AtEnding_Date DATE)
    BEGIN
       SELECT
          Employees.Country,
          Employees.LastName,
          Employees.FirstName,
          Orders.ShippedDate,
          Orders.OrderID,
          `Order Subtotals`.Subtotal AS SaleAmount
       FROM Employees
          INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
          INNER JOIN `Order Subtotals` ON Orders.OrderID = `Order Subtotals`.OrderID
       WHERE Orders.ShippedDate BETWEEN AtBeginning_Date AND AtEnding_Date;
    END $$
    DELIMITER ;
    mysql> CALL `Employee Sales by Country`('1997-01-01', '1997-01-31');
    +---------+-----------+-----------+-------------+---------+------------+
    | Country | LastName  | FirstName | ShippedDate | OrderID | SaleAmount |
    +---------+-----------+-----------+-------------+---------+------------+
    | USA     | Callahan  | Laura     | 1997-01-16  |   10380 |       1420 |
    | USA     | Fuller    | Andrew    | 1997-01-01  |   10392 |       1440 |
    | USA     | Davolio   | Nancy     | 1997-01-03  |   10393 |       3302 |
     ......
    Procedure "Sales by Year"
    DELIMITER $$
    CREATE PROCEDURE `Sales by Year`(IN AtBeginning_Date DATE, IN AtEnding_Date DATE)
    BEGIN
       SELECT
          Orders.ShippedDate,
          Orders.OrderID,
          `Order Subtotals`.Subtotal,
          ShippedDate AS Year
       FROM Orders 
          JOIN `Order Subtotals` ON Orders.OrderID = `Order Subtotals`.OrderID
       WHERE Orders.ShippedDate BETWEEN AtBeginning_Date AND AtEnding_Date;
    END $$
    DELIMITER ;
    mysql> CALL `Sales by Year`('1997-01-01', '1997-01-31');
    +-------------+---------+----------+------------+
    | ShippedDate | OrderID | Subtotal | Year       |
    +-------------+---------+----------+------------+
    | 1997-01-16  |   10380 |     1420 | 1997-01-16 |
    | 1997-01-01  |   10392 |     1440 | 1997-01-01 |
    | 1997-01-03  |   10393 |     3302 | 1997-01-03 |
     ......
    Procedure "SalesByCategory"
    DELIMITER $$
    CREATE PROCEDURE `SalesByCategory`(IN AtCategoryName VARCHAR(15), IN AtOrdYear VARCHAR(4))
    BEGIN
       SELECT
          ProductName,
          ROUND(SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice)) AS TotalPurchase
       FROM `Order Details` AS OD
          INNER JOIN Orders AS O USING (OrderID)
          INNER JOIN Products AS P USING (ProductID)
          INNER JOIN Categories AS C USING (CategoryID)
       WHERE C.CategoryName = AtCategoryName
          AND YEAR(O.OrderDate) = AtOrdYear
       GROUP BY ProductName
       ORDER BY ProductName;
    END $$
    DELIMITER ;
    mysql> CALL `SalesByCategory`('Beverages', 1997);
    +---------------------------+---------------+
    | ProductName               | TotalPurchase |
    +---------------------------+---------------+
    | Chai                      |          5296 |
    | Chang                     |          7600 |
    | Chartreuse verte          |          4928 |
     ......

    Try: i18n and UTF8 on MySQL Workbench.

    4.  MySQLTutorial.org's Sample Retailer Database

    Reference: The "Classic Models" Retailer database of http://www.mysqltutorial.org.

    4.1  Database and Tables

    Database Diagram
    There are 8 tables, with no stored objects (view, procedure, function, trigger and event) defined.
    I made some modifications to the data type, and added in the foreign keys and indexes.
    Table "offices"
    CREATE TABLE `offices` (
       `officeCode`    VARCHAR(10)  NOT NULL,
       `city`          VARCHAR(50)  NOT NULL,
       `phone`         VARCHAR(50)  NOT NULL,
       `addressLine1`  VARCHAR(50)  NOT NULL,
       `addressLine2`  VARCHAR(50)  DEFAULT NULL,
       `state`         VARCHAR(50)  DEFAULT NULL,
       `country`       VARCHAR(50)  NOT NULL,
       `postalCode`    VARCHAR(15)  NOT NULL,
       `territory`     VARCHAR(10)  NOT NULL,
       PRIMARY KEY  (`officeCode`),
       INDEX (`phone`),
       INDEX (`city`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 7 records for this table.
    Table "employees"
    CREATE TABLE `employees` (
       `employeeNumber`  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
       `lastName`        VARCHAR(50)   NOT NULL,
       `firstName`       VARCHAR(50)   NOT NULL,
       `extension`       VARCHAR(10)   NOT NULL,
       `email`           VARCHAR(100)  NOT NULL,
       `officeCode`      VARCHAR(10)   NOT NULL,
       `reportsTo`       INT UNSIGNED  DEFAULT NULL,
       `jobTitle`        VARCHAR(50)   NOT NULL,
       PRIMARY KEY  (`employeeNumber`),
       INDEX (`lastName`),
       INDEX (`firstName`),
       FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`)
          ON DELETE RESTRICT ON UPDATE CASCADE,
       FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 23 records for this table.
    Table "customers"
    CREATE TABLE `customers` (
       `customerNumber`   INT UNSIGNED  NOT NULL AUTO_INCREMENT,
       `customerName`     VARCHAR(50)   NOT NULL,
       `contactLastName`  VARCHAR(50)   NOT NULL,
       `contactFirstName` VARCHAR(50)   NOT NULL,
       `phone`            VARCHAR(50)   NOT NULL,
       `addressLine1`     VARCHAR(50)   NOT NULL,
       `addressLine2`     VARCHAR(50)   DEFAULT NULL,
       `city`             VARCHAR(50)   NOT NULL,
       `state`            VARCHAR(50)   DEFAULT NULL,
       `postalCode`       VARCHAR(15)   DEFAULT NULL,
       `country`          VARCHAR(50)   NOT NULL,
       `salesRepEmployeeNumber`  INT UNSIGNED  DEFAULT NULL,
       `creditLimit`      INT UNSIGNED  DEFAULT NULL,
       PRIMARY KEY (`customerNumber`),
       INDEX (`customerName`),
       INDEX (`contactLastName`),
       INDEX (`contactFirstName`),
       INDEX (`phone`),
       INDEX (`postalCode`),
       FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 122 records for this table.
    Table "products"
    CREATE TABLE `products` (
       `productCode`         VARCHAR(15)  NOT NULL,
       `productName`         VARCHAR(70)  NOT NULL,
       `productLine`         VARCHAR(50)  NOT NULL,
       `productScale`        VARCHAR(10)  NOT NULL,
       `productVendor`       VARCHAR(50)  NOT NULL,
       `productDescription`  TEXT         NOT NULL,  -- 64KB
       `quantityInStock`     SMALLINT     NOT NULL,  -- Allow negative
       `buyPrice`            DECIMAL(8,2) UNSIGNED  NOT NULL,
       `MSRP`                DECIMAL(8,2) UNSIGNED  NOT NULL,
       PRIMARY KEY (`productCode`),
       INDEX (`productName`),
       INDEX (`productVendor`),
       INDEX (`productLine`)    -- needed to be indexed to be used as foreign key
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 110 records for this table.
    Table "productlines"
    CREATE TABLE `productlines` (
       `productLine`      VARCHAR(50)   NOT NULL,
       `textDescription`  VARCHAR(4000) DEFAULT NULL,
       `htmlDescription`  TEXT          DEFAULT NULL,  -- 64 KB
       `image`            BLOB          DEFAULT NULL,  -- 64 KB
       PRIMARY KEY (`productLine`),
       FOREIGN KEY (`productLine`) REFERENCES `products` (`productLine`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    You need to index the productLine column of the products table to use the column as a foreign key here.
    There are 7 records for this table.
    Table "orders"
    CREATE TABLE `orders` (
       `orderNumber`     INT UNSIGNED  NOT NULL AUTO_INCREMENT,
       `orderDate`       DATE          NOT NULL,
       `requiredDate`    DATE          NOT NULL,
       `shippedDate`     DATE          DEFAULT NULL,
       `status`          VARCHAR(15)   NOT NULL,  -- use ENUM
       `comments`        TEXT          DEFAULT NULL,
       `customerNumber`  INT UNSIGNED  NOT NULL,
       PRIMARY KEY  (`orderNumber`),
       INDEX (`orderDate`),
       INDEX (`customerNumber`),
       FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 326 records for this table.
    Table "orderdetails"
    CREATE TABLE `orderdetails` (
       `orderNumber`      INT UNSIGNED       NOT NULL,
       `productCode`      VARCHAR(15)        NOT NULL,
       `quantityOrdered`  SMALLINT UNSIGNED  NOT NULL,  -- [0, 65535]
       `priceEach`        DECIMAL(7,2)       NOT NULL,
       `orderLineNumber`  TINYINT UNSIGNED   NOT NULL,   -- [0,255]
       PRIMARY KEY  (`orderNumber`,`productCode`),
       FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`)
          ON DELETE RESTRICT ON UPDATE CASCADE,
       FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    There are 2996 records for this table.
    Table "payments"
    CREATE TABLE `payments` (
       `customerNumber`  INT UNSIGNED           NOT NULL,
       `checkNumber`     VARCHAR(50)            NOT NULL,
       `paymentDate`     DATE                   NOT NULL,
       `amount`          DECIMAL(8,2) UNSIGNED  NOT NULL,
       PRIMARY KEY  (`customerNumber`,`checkNumber`),
       FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
          ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    This payment table does not reflect the order paid?! Could also provide a VIEW for invoices.
    There are 273 records for this table.
    Link to MySQL References & Resources
    Add Comment
    database mysql sql

    facebook

    twitter

    google+

    fb share

    About iamc

    Related Posts
    < Previous Post Next Post >

    Được tạo bởi Blogger.

    VC

    VC

    Liên quan :v

    • Tài liệu laravel tiếng việt
      https://github.com/petehouston/laravel-docs-vn
    • Tài liệu phổ thông
      Một số sách vào 10  một số tài liệu khối A  (click để tải về) Đề thi Kỹ Sư Tài Năng BKHN 1999-2xxx ( click ) # Toán Lí  Hóa...
    • Hôm nay là một ngày đặc biệt
      tác giả: tôi không nhớ     1. Vì hôm nay tôi phải rất vui vẻ. Licon đã từng nói đại đa số mọi người chủ cần hạ quyết tâm ...
    • Building your own Custom PHP Framework: Part 1
      PHP has been the language of the web for a very long time due to its easy of learning, community, being free and open source and...
    • JavaServer Pages (JSP)
      Java Server-Side Programming JavaServer Pages (JSP) (1.2 and 2.0) 1.  Introduction Instead of  static  contents that are indiffe...
    • Tạp Chí Toán Học Và Tuổi Trẻ
      Tạp Chí Toán Học Và Tuổi Trẻ                                                                                       Chính trị chỉ ch...
    • Tương lai dự án biệt thự, trung cư ở The Manor Central Park
      The Manor Central Park là một dự án lớn mà chủ đầu tư là tập đoàn  Tập đoàn BITEXCO. Vị trí dự án The Manor Central Park ở đường Nguyễn Xiển...
    • MySQL Sample Databases
      một số db cơ bản There are many excellent and interesting sample databases available, that you can use as a template (or pattern) to d...
    • tổng quan chi tiết về FrameWork Laravel
      Những nội dung có trong bài này: Luồng hoạt động của Laravel Middleware Request và Response View: Blade Template và Elixir Dat...

    video

    Posts

    ==BCS== (3) Laravel (2) Tiếng anh (2) 4C (1) 4P (1) Confessions (1) Kĩ năng (1) biệt thự (1) build an mvc framework with php (1) báo (1) copy (1) database (1) hà nội (1) hóa (1) java chat (1) jsp (1) lí (1) mysql (1) neu (1) phú túc (1) phú xuyên (1) sql (1) toán (1) trung cư (1) đặng văn cường (1)

    Audio

    Đặng Văn Cường