practice.sql

CREATE DATABASE hello_sql_0825;

USE hello_sql_0825;

CREATE TABLE coffee
(
    id     int PRIMARY KEY,
    name   varchar(20),
    origin varchar(30)
);

INSERT INTO coffee
VALUES (1, 'Dak - Liquid Donuts', 'Amsterdam');
INSERT INTO coffee
VALUES (2, 'Glitch - Geisha', 'Panama');

# INSERT INTO coffee (id, name, origin)
      # VALUES (3, 'Blue Bottle - Bella Donovan', 'Ethiopia'),
            #        (4, 'Stumptown - Hair Bender', 'Colombia'),
#        (5, 'Intelligentsia - Black Cat', 'Honduras'),
#        (6, 'Verve - Streetlevel', 'Guatemala');

SELECT *
FROM coffee;

SELECT name
FROM coffee;

SELECT *
FROM coffee
WHERE id = 1;


CREATE TABLE roasters
(
    id      int PRIMARY KEY AUTO_INCREMENT,
    name    varchar(30) UNIQUE,
    country varchar(2)
);

INSERT INTO roasters (name, country)
VALUES ('Blue Bottle', 'US');

INSERT INTO roasters (name, country)
VALUES ('Astro Coffee', 'ES');

# INSERT INTO roasters (name, country)
      # VALUES ('Astro Coffee', 'ES');


SELECT * FROM roasters;


CREATE TABLE country(
                        code varchar(2) PRIMARY KEY,
                        name varchar(30)
);

INSERT INTO country (code, name)
VALUES ('US', 'United States'),
       ('ES', 'Spain'),
       ('NL', 'Netherlands'),
       ('PA', 'Panama'),
       ('ET', 'Ethiopia'),
       ('CO', 'Colombia'),
       ('HN', 'Honduras'),
       ('GT', 'Guatemala');


INSERT INTO country (code, name)
VALUES ('IT', 'Italy');

SELECT * FROM country;

create table coffee_place(
                             id int PRIMARY KEY AUTO_INCREMENT,
                             name varchar (255),
                             country varchar(2),
                             FOREIGN KEY (country) REFERENCES country(code)
);

INSERT INTO coffee_place (name, country)
VALUES ('Coffeelab', 'ES');

INSERT INTO coffee_place (name, country)
VALUES ('Bar dello stadio', 'IT');

DROP TABLE country;


SELECT * FROM coffee_place;

SELECT * FROM country;

UPDATE coffee_place SET name = 'New Coffee Lab1'   WHERE id = 1;


DELETE FROM coffee_place WHERE id;