Shortcuts to Remember: PostgreSQL, PGAdmin, Oracle SQL, MySQL

Work in progress.

PostgreSQL / PGAdmin

Creating Tables:

​CREATE TABLE phones (
     ​name VARCHAR(50),
     ​manufacturer VARCHAR(50),
     ​price INTEGER,
     ​units_sold INTEGER,
 ​);

Inserting Data into Tables:

INSERT INTO phones (name, manufacturer, price, units_sold)
  VALUES
      ('N8', 'Nokia', 150, 7543),
      ('Droid', 'Motorola', 150, 8395),
      ('Wave S8500', 'Samsung', 175, 9259);

Filtering Records:

​SELECT
     ​name,
     ​area
 ​FROM
     ​cities
 ​WHERE
     ​area NOT IN(3043, 8223)
     ​OR name = 'Delhi'
     ​OR name = 'Tokyo';

Renaming Columns and Performing Calculations in “Where” Clauses:

​SELECT
     ​name,
     ​price * unit sold AS total_revenue
 ​FROM
     ​phones
 ​WHERE
     ​price * unit sold > 1000000;

Add / Insert:

INSERT INTO cities (name, country, population, area)
  VALUES ('Tokyo', 'Japan', 38505000, 8223)

Update:

​UPDATE phones
 ​SET units_sold = 8543
 ​WHERE name = 'N8';

Delete:

​DELETE FROM phones
 ​WHERE manufacturer = 'Samsung';

Example One-To-Many and Many-To-One Relationships:

A boat has many crew members, a school has many students, a company has many employees, a photo has many comments, a user has many likes, a book has one author, an office has many employees, companies have many ships, ships have many containers.

• A database containing users, photos, comments, and likes only contains one-to-many and many-to-one relationships (based on specific conditions)

Generating primary key for a users table:

​CREATE TABLE users(
     ​id SERIAL PRIMARY KEY,
     ​username VARCHAR(50)
 ​);

insert username values into the users table

​INSERT INTO users (username)
 ​VALUES
     ​('monahan93'),
     ​('pferrer'),
     ​('si93onis'),
     ​('99stroman');

Display all columns in a users table

​SELECT * FROM users

Generating a foreign key for a photos table:

​CREATE TABLE photos (
     ​id SERIAL PRIMARY KEY,
     ​url VARCHAR(200),
     ​user_id INTEGER REFERENCES users(id)
 ​);

Add a row to the photos table

​INSERT INTO photos (url, user_id)
 ​VALUES
   ​('http://one.jpg', 4);

Display all columns in a photos table

​SELECT * FROM photos;

- Find all the photos created by user with ID 4

​SELECT * FROM photos
 ​WHERE user_id = 4;

- List all photos with details about the associated user for each

​SELECT url, username FROM photos
 ​JOIN users ON users.id = photos.user_id

- This is a join.