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;
 

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.