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

Raspberry Pi UART/Serial communication

Coming soon: Compact tree view for backlogs

How to Delete an Undeletable Folder in Windows 10

Leetcode 343: Integer Break

Change Service Network in running OpenShift 4 cluster

HTB Vpn on Kasm using KALI made easy.

ssh and login to your ssh KASM server

Getting started with Git/Github.

32 products we use to run a 7-figure product company.

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

Query Plan in Postgres

Configure BiDirectional MySQL Replication (Master-Master)

RabbitMQ Server And Clustering Setup on Ubuntu 18.04

CI-CD Operations with Github Actions