SQL Queries Using Workbench or pgAdmin

SQL (Structured Query Language) is the standard language for managing relational databases. Two of the most widely used relational databases are PostgreSQL and MySQL.

• PostgreSQL comes with pgAdmin, a graphical tool for writing and executing SQL queries.
• MySQL uses Workbench, an intuitive GUI for database management.

Here is step-by-step SQL operations, using examples that work in both PostgreSQL and MySQL.

Step-by-Step SQL Queries Using Workbench or pgAdmin

Step 1: Install PostgreSQL / MySQL and GUI Tools

First, install PostgreSQL (which comes with pgAdmin) or MySQL (with Workbench). These GUI tools make it easier to manage databases without using command-line commands.

• Install PostgreSQL and pgAdmin
• Install MySQL (includes Workbench)

Step 2: Connect to Database Server

After installation, connect to your database server. In pgAdmin, provide username/password; in MySQL Workbench, create a new connection with host, port and credentials.

• Connect to your PostgreSQL server
• Connect to MySQL Server

Step 3: Create a Database

A database is a container for tables, views and other objects. Before running queries, you must create a database to store your data.

• Create a database in PostgreSQL pgAdmin
• Create a Database in MySQL Workbench

Syntax:

CREATE DATABASE database_name;

Step 4: Create a Table

Tables are the backbone of relational databases. They store data in rows and columns. You can define the table structure using SQL commands (CREATE TABLE).

• Create a Table In PostgreSQL pgAdmin
• Create a table in MySQL Workbench

Syntax:

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

...

);

Step 5: Insert Data

Once the table is created, you can insert records into it using the INSERT INTO statement. This step helps you populate the table with sample data for testing queries.

• PostgreSQL: Insert Data into Table
• MySQL: Insert Data into Table

Syntax:

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Step 6: Retrieve Data (SELECT)

Use SELECT to fetch data from a table.

• PostgreSQL: Retrieve Data (SELECT Query)
• MySQL: Retrieve Data (SELECT Query)

Syntax:

SELECT column1, column2, ...

FROM table_name;

Step 7: Update Data

Use UPDATE to change existing records in a table. Always include a WHERE clause to avoid updating all rows.

• PostgreSQL: Update Data
• MySQL: Update Data

Syntax:

UPDATE table_name

SET column1 = value1

WHERE condition;

Step 8: Delete Data

The DELETE command removes records from a table. Use WHERE to delete specific rows instead of all.

• PostgreSQL: Delete Data
• MySQL: Delete Data

Syntax:

DELETE FROM table_name

WHERE condition;

Suggested Quiz

Edit Quiz

3 Questions

Which tools are commonly used to execute SQL queries for PostgreSQL and MySQL?

• A

  Oracle Forms & SSMS

• B

  pgAdmin & MySQL Workbench

• C

  VS Code & Eclipse

• D

  NetBeans & IntelliJ

Explanation:

PostgreSQL uses pgAdmin and MySQL uses Workbench.

Why should a WHERE clause be used with UPDATE?

• A

  To increase speed

• B

  To avoid updating all rows accidentally

• C

  To rename columns

• D

  To sort the result

Explanation:

Without WHERE, UPDATE would modify the entire table.

Which SQL command retrieves data from a table?

• A

  GET DATA

• B

  READ TABLE

• C

  SELECT

• D

  FETCH TABLE

Explanation:

SELECT is used for querying data.

Quiz Completed Successfully

Your Score :   2/3

Accuracy :  0%

Login to View Explanation

1/3 1/3 < Previous Next >