Databases

information around databases

• Databases
  • Some free databases to try
  • Entity relationship diagram (ERD)
  • SQL Commands
  • SQL Joins
  • Examples

Return Home

Some free databases to try

• MySQL
• MariaDB
• Progress
• MongoDB

^ back to top ^

Entity relationship diagram (ERD)

Used to map data entities, thier attributes, and relationships between entities.

^ back to top ^

SQL Commands

^ back to top ^

SQL Joins

^ back to top ^

Examples

Example DDL and SQL

drop database mydb;
create database mydb;
use mydb;

-- create table with check constraint
CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `age` int,
  CHECK (age >= 18),
  PRIMARY KEY (`customer_id`),
  UNIQUE (email)
);

-- Before insert trigger
CREATE TRIGGER check_customer_age_before_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Customer must be 18 or older.';
    END IF;
END;

-- insert row
INSERT INTO `customers` (`first_name`, `last_name`, `email`, `age`) VALUES ('Austin', 'Powers', 'austin.powers@gmail.com', 17);

-- update 
UPDATE `customer` SET `email` = "drevil@shawing.com.au" where customer_id = 1;

-- create view
create view customers as
SELECT
    *
FROM
    customers

-- list triggers
show triggers from mydb;

-- list MySQL version
SELECT version();

^ back to top ^