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

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)
('N8', 'Nokia', 150, 7543),
('Droid', 'Motorola', 150, 8395),
('Wave S8500', 'Samsung', 175, 9259);

Filtering Records:

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

Renaming Columns and Performing Calculations in “Where” Clauses:

​price * unit sold AS total_revenue
​price * unit sold > 1000000;

Add / Insert:

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


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


​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:

​username VARCHAR(50)

insert username values into the users table

​INSERT INTO users (username)

Display all columns in a users table

​SELECT * FROM users

Generating a foreign key for a photos table:

​CREATE TABLE photos (
​url VARCHAR(200),
​user_id INTEGER REFERENCES users(id)

Add a row to the photos table

​INSERT INTO photos (url, user_id)
​('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 = photos.user_id

- This is a join.



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store