SQL (EI20) Cheat Sheet by mckoy

CREATE TABLE restaurants ( 
  id                       serial     PRIMARY KEY,
  name                     text       NOT NULL,
  nyc_restaurant_id        integer,
  borough                  borough_options,
  cuisine                  text,
  address_building_number  text,
  address_street           text,
  address_zipcode          text
);

INSERT INTO restaurants
  (name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES
  ('Prince Taco', 'Queens', 'Mexican', '620', 'Astoria Boulevard', '11372');

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (
    name text,
    current_mood mood
);

INSERT INTO person VALUES ('Moe', 'happy');
INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');

SELECT * from restaurants;

SELECT id, name, borough, cuisine
FROM restaurants;

SELECT DISTINCT borough
FROM restaurants;

SELECT id, name, borough, cuisine 
FROM restaurants
WHERE id='225';

SELECT name, borough, cuisine FROM restaurants
WHERE borough = 'Brooklyn'
AND cuisine = 'Italian';

SELECT name, borough, cuisine FROM restaurants
WHERE borough = 'Brooklyn'
AND cuisine in ('Italian', 'Chinese');

UPDATE restaurants 
    SET name = 'Famous Original Ray''s Pizza'
    WHERE id = 5269;

UPDATE restaurants 
    SET cuisine = 'Vegetarian'
    WHERE cuisine = 'Pizza';

SELECT id, name from restaurants
WHERE borough = 'Bronx'
AND cuisine = 'Japanese'
ORDER BY name DESC;

SELECT id, name
FROM restaurants
ORDER BY name DESC;
LIMIT 3;

SELECT count(*) FROM grades;

SELECT max(score) FROM grades;

SELECT min(score) FROM grades;

SELECT avg(score) FROM grades;

DELETE FROM restaurants WHERE id = 225;

DELETE FROM grades WHERE grade = 'Z';

DELETE FROM grades; -- BEWARE: DELETES ALL ROWS IN GRADES!

DROP TABLE restaurants;