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)
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.

--

--

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