PostgreSQL vs MySQL key differences - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from a table named 'employees'.

PostgreSQL

SELECT [1] FROM employees;

Drag options to blanks, or click blank then click option'

Aeverything

B*

CALL

Dcolumns

Attempts:

3 left

💡 Hint

Common Mistakes

Using words like ALL or columns instead of *

Leaving the blank empty

In SQL, * means select all columns from the table.

2fill in blank

medium

Complete the code to limit the number of rows returned to 5 in PostgreSQL.

PostgreSQL

SELECT * FROM products [1] 5;

Drag options to blanks, or click blank then click option'

ALIMIT

BFETCH

CTOP

DROWNUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using TOP which is for SQL Server or MySQL

Using ROWNUM which is for Oracle

PostgreSQL uses LIMIT to restrict the number of rows returned.

3fill in blank

hard

Fix the error in the PostgreSQL query to add a new column named 'age' of type integer.

PostgreSQL

ALTER TABLE users [1] COLUMN age INTEGER;

Drag options to blanks, or click blank then click option'

AADD

BCHANGE

CMODIFY

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using MODIFY or CHANGE which are MySQL keywords

Using UPDATE which is for changing data, not structure

In PostgreSQL, to add a new column, use ADD COLUMN.

4fill in blank

hard

Fill both blanks to write a PostgreSQL query that selects all columns from 'orders' and orders results by 'order_date' descending.

PostgreSQL

SELECT [1] FROM orders ORDER BY order_date [2];

Drag options to blanks, or click blank then click option'

A*

BASC

CDESC

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using ASC instead of DESC for descending order

Using ALL instead of * for selecting columns

Use * to select all columns and DESC to sort descending.

5fill in blank

hard

Fill all three blanks to write a PostgreSQL query that creates a table 'customers' with columns 'id' as serial primary key, 'name' as text, and 'email' as unique text.

PostgreSQL

CREATE TABLE customers (id [1] PRIMARY KEY, name [2], email [3] UNIQUE);

Drag options to blanks, or click blank then click option'

ASERIAL

BTEXT

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR instead of TEXT for name or email

Not using SERIAL for id column

Use SERIAL for auto-increment id, and TEXT for name and email columns.

Practice

1. Which database system is known for its strong support of advanced data types and strict data integrity?

easy

A. MongoDB

B. MySQL

C. SQLite

D. PostgreSQL

Solution

1. Step 1: Understand database features

   PostgreSQL is designed with advanced features like support for JSON, arrays, and custom types, ensuring strong data integrity.

2. Step 2: Compare with MySQL

   MySQL focuses more on speed and simplicity but has less strict data integrity features compared to PostgreSQL.

3. Final Answer:

   PostgreSQL -> Option D

4. Quick Check:

   Advanced features and strong integrity = PostgreSQL [OK]

Hint: Advanced data types and integrity point to PostgreSQL [OK]

Common Mistakes:

• Confusing speed with data integrity
• Thinking MySQL has stronger data types
• Mixing up database names

2. Which of the following is the correct way to start a transaction in PostgreSQL?

easy

A. SET TRANSACTION;

B. START TRANSACTION;

C. BEGIN;

D. TRANSACTION START;

Solution

1. Step 1: Recall PostgreSQL transaction syntax

   PostgreSQL uses BEGIN; to start a transaction block.

2. Step 2: Compare other options

   While SET TRANSACTION; and START TRANSACTION; are valid in MySQL, PostgreSQL specifically uses BEGIN;.

3. Final Answer:

   BEGIN; -> Option C

4. Quick Check:

   PostgreSQL transaction start = BEGIN; [OK]

Hint: PostgreSQL uses BEGIN; to start transactions [OK]

Common Mistakes:

• Using MySQL syntax in PostgreSQL
• Adding extra keywords like TRANSACTION
• Confusing order of words

3. Given the following SQL snippet in PostgreSQL:

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
SELECT * FROM users WHERE id = 2;

What will be the output of the SELECT query?

medium

A. An error because SERIAL is not supported

B. Row with id=2 and name='Bob'

C. Row with id=1 and name='Alice'

D. Empty result set

Solution

1. Step 1: Understand SERIAL behavior in PostgreSQL

   The SERIAL type auto-increments starting at 1, so the first inserted row gets id=1, second id=2.

2. Step 2: Analyze the SELECT query

   The query selects the row where id=2, which corresponds to the second inserted user 'Bob'.

3. Final Answer:

   Row with id=2 and name='Bob' -> Option B

4. Quick Check:

   id=2 matches 'Bob' row [OK]

Hint: SERIAL auto-increments starting at 1 in PostgreSQL [OK]

Common Mistakes:

• Assuming SERIAL is unsupported
• Mixing up id values
• Expecting empty results

4. You run this MySQL query in PostgreSQL:

SELECT NOW;

But it returns an error. What is the likely cause?

medium

A. The function NOW() exists but requires parentheses in PostgreSQL

B. The query syntax is incorrect for PostgreSQL

C. PostgreSQL uses CURRENT_TIMESTAMP instead of NOW() for current time

D. NOW() is not a valid function in PostgreSQL

Solution

1. Step 1: Check function availability

   PostgreSQL supports the NOW() function and it requires parentheses, so NOW() is valid.

2. Step 2: Identify common mistake

   Sometimes users omit parentheses and write NOW without (), causing syntax errors.

3. Final Answer:

   The function NOW() exists but requires parentheses in PostgreSQL -> Option A

4. Quick Check:

   NOW() needs () in PostgreSQL [OK]

Hint: Always use parentheses with NOW() in PostgreSQL [OK]

Common Mistakes:

• Omitting parentheses after NOW
• Confusing CURRENT_TIMESTAMP with NOW
• Assuming NOW() is unsupported

5. You want to migrate a MySQL database to PostgreSQL. Which key difference should you consider to avoid data loss?

hard

A. PostgreSQL enforces stricter data types and constraints by default

B. MySQL supports JSON data type but PostgreSQL does not

C. PostgreSQL does not support transactions

D. MySQL uses SERIAL for auto-increment but PostgreSQL uses AUTO_INCREMENT

Solution

1. Step 1: Identify data type and constraint differences

   PostgreSQL enforces data types and constraints more strictly, which can cause migration errors if data doesn't comply.

2. Step 2: Evaluate other options

   MySQL supports JSON, PostgreSQL also supports JSON; PostgreSQL supports transactions; SERIAL is PostgreSQL's auto-increment, not AUTO_INCREMENT.

3. Final Answer:

   PostgreSQL enforces stricter data types and constraints by default -> Option A

4. Quick Check:

   Stricter constraints in PostgreSQL = watch for data loss [OK]

Hint: Stricter constraints in PostgreSQL can cause migration issues [OK]

Common Mistakes:

• Assuming PostgreSQL lacks JSON support
• Confusing auto-increment syntax
• Thinking PostgreSQL lacks transactions