Show Menu
Cheatography

SQL (EI20) Cheat Sheet by

CREATE A TABLE

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 A ROW

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

ENUMER­ATIONS

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 person WHERE curren­t_mood = 'happy';
name | curren­t_mood
-----+­---­---­---­-----
Moe | happy

SELECT * FROM person WHERE curren­t_mood > 'sad';
name | curren­t_m­ood
-----+­---­---­---­-----
Moe | happy
Curly | ok

EXAMPLE QUERYING

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');

UPDATING

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

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

ORDERING AND LIMITING RESULTS

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;

AGGREGATE: COUNT, MAX, MIN, & AVG

SELECT count(*) FROM grades;

SELECT max(score) FROM grades;

SELECT min(score) FROM grades;

SELECT avg(score) FROM grades;

DELETING

DELETE FROM restaurants WHERE id = 225;

DELETE FROM grades WHERE grade = 'Z';

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

DROP A TABLE

DROP TABLE restaurants;

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.