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.

Excited to learn, and even happier to teach.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Docker Images: Setup a basic Dockerfile

Integration of Google Custom Search in Amazon LEX

Beginning | How to CTO

[Update:2] Sometimes your workaround.

How to Run Manual Jobs in Gitlab CI/CD

What is Ansible and How to configure apache webserver in Docker using Ansible?

How to bring requirements, risks, tests and issue management together (Part 2 of 2)

My Journey at Le Wagon Singapore

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
Joshycsm

Joshycsm

Excited to learn, and even happier to teach.

More from Medium

Setup PostGresSQL on Windows environment.

Create Database HR payroll using Ms SQL studio.

Create Database HR Payroll

SQL Fundamental

Randomized Database Ids