MySQL vs PostgreSQL vs SQL Server overview - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Which database supports JSON data type natively?

Among MySQL, PostgreSQL, and SQL Server, which one supports JSON as a native data type allowing efficient querying and indexing?

A Both MySQL and PostgreSQL support JSON natively.

B Only PostgreSQL supports JSON natively.

C Only MySQL supports JSON natively.

D None of them support JSON natively.

💡 Hint

Think about which databases allow storing JSON documents and querying them efficiently.

Both MySQL (from version 5.7) and PostgreSQL support JSON data types natively, allowing efficient storage and querying. SQL Server supports JSON but stores it as plain text without a native JSON type.

❓ query_result

intermediate

Identify the database by its default case sensitivity in string comparisons

Given the following behavior: String comparisons are case-insensitive by default in this database. Which database is it?

A MySQL

B PostgreSQL

C SQL Server

D All three are case-sensitive by default

💡 Hint

Consider which database treats 'abc' and 'ABC' as equal by default in WHERE clauses.

MySQL and SQL Server use case-insensitive collation by default for string comparisons, making them case-insensitive unless specified otherwise. PostgreSQL is case-sensitive by default.

📝 Syntax

advanced

Which SQL syntax is valid for limiting query results in all three databases?

You want to limit the number of rows returned by a query to 5. Which of the following SQL statements will work correctly in MySQL, PostgreSQL, and SQL Server?

SQL

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

A SELECT * FROM employees WHERE ROWNUM <= 5;

B SELECT TOP 5 * FROM employees;

C SELECT * FROM employees LIMIT 5;

D SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

💡 Hint

Think about the SQL standard syntax supported by all three databases.

The syntax 'FETCH FIRST 5 ROWS ONLY' is part of the SQL standard and supported by PostgreSQL, SQL Server (2012+), and MySQL (8.0+). LIMIT works in MySQL and PostgreSQL but not SQL Server. TOP is SQL Server specific. ROWNUM is Oracle specific.

❓ optimization

advanced

Which database supports partial indexes for query optimization?

Partial indexes allow indexing only a subset of rows in a table. Which database supports partial indexes natively?

A PostgreSQL

B MySQL

C SQL Server

D None of them support partial indexes

💡 Hint

Think about which database allows creating indexes with a WHERE clause.

PostgreSQL supports partial indexes, allowing indexes on a subset of rows using a WHERE clause. MySQL and SQL Server do not support partial indexes natively.

🔧 Debug

expert

Why does this SQL Server query fail but works in MySQL and PostgreSQL?

Consider this query: SELECT * FROM orders WHERE order_date = '2023-02-30'; It runs (with warning) in MySQL but fails in SQL Server and PostgreSQL. Why?

A SQL Server requires dates to be in ISO 8601 format only.

B MySQL automatically converts invalid dates to NULL silently.

C SQL Server strictly validates date literals and '2023-02-30' is invalid.

D MySQL treats all strings as valid dates regardless of format.

💡 Hint

Think about how each database handles invalid date values.

SQL Server and PostgreSQL validate date literals strictly and throw an error for invalid dates like '2023-02-30'. MySQL issues a warning but allows the query to run, converting invalid dates silently.

Practice

1. Which database system is known for easy setup and is commonly used in web projects?

easy

A. Oracle

B. PostgreSQL

C. SQL Server

D. MySQL

Solution

1. Step 1: Understand the common uses of MySQL

   MySQL is widely recognized for its easy installation and is popular in web development.

2. Step 2: Compare with other databases

   PostgreSQL is more feature-rich for complex queries, and SQL Server is suited for Windows business environments.

3. Final Answer:

   MySQL -> Option D

4. Quick Check:

   Easy setup and web use = MySQL [OK]

Hint: Think 'easy web projects' and pick MySQL [OK]

Common Mistakes:

• Confusing PostgreSQL with MySQL
• Choosing SQL Server for web projects
• Choosing Oracle, which is enterprise-focused

2. Which of the following is the correct way to start a SQL Server service on Windows using command line?

easy

A. service start sqlserver

B. net start MSSQLSERVER

C. systemctl start sqlserver

D. start-service sqlserver

Solution

1. Step 1: Identify Windows command for services

   On Windows, the command to start services is 'net start' followed by the service name.

2. Step 2: Recognize SQL Server service name

   The default SQL Server service is named MSSQLSERVER, so the command is 'net start MSSQLSERVER'.

3. Final Answer:

   net start MSSQLSERVER -> Option B

4. Quick Check:

   Windows service start = net start MSSQLSERVER [OK]

Hint: Windows services start with 'net start' [OK]

Common Mistakes:

• Using Linux commands like systemctl
• Using incorrect service names
• Confusing PowerShell commands

3. Given the following SQL query run on PostgreSQL:

SELECT LENGTH('Hello World');

What will be the output?

medium

A. 10

B. 12

C. 11

D. Error

Solution

1. Step 1: Understand LENGTH function in PostgreSQL

   The LENGTH function returns the number of characters in the string.

2. Step 2: Count characters in 'Hello World'

   There are 11 characters including the space between Hello and World.

3. Final Answer:

   11 -> Option C

4. Quick Check:

   Count characters including space = 11 [OK]

Hint: Count all characters including spaces [OK]

Common Mistakes:

• Ignoring the space character
• Confusing LENGTH with LEN
• Expecting error due to function name

4. You try to run this query on MySQL:

SELECT TOP 5 * FROM users;

But it gives an error. What is the problem?

medium

A. MySQL does not support SELECT TOP syntax

B. Missing WHERE clause

C. Table 'users' does not exist

D. Incorrect use of semicolon

Solution

1. Step 1: Recognize SELECT TOP syntax usage

   SELECT TOP is used in SQL Server to limit rows, but MySQL uses LIMIT.

2. Step 2: Identify correct MySQL syntax

   In MySQL, the query should be: SELECT * FROM users LIMIT 5;

3. Final Answer:

   MySQL does not support SELECT TOP syntax -> Option A

4. Quick Check:

   MySQL uses LIMIT, not TOP [OK]

Hint: MySQL uses LIMIT, not TOP [OK]

Common Mistakes:

• Assuming TOP works in MySQL
• Thinking WHERE is mandatory
• Blaming semicolon usage

5. You want to use a database system that supports advanced JSON querying and complex data types for a data analytics project. Which database is the best choice?

hard

A. PostgreSQL

B. MySQL

C. SQL Server

D. SQLite

Solution

1. Step 1: Identify database strengths for complex data

   PostgreSQL is known for powerful features including advanced JSON support and complex data types.

2. Step 2: Compare with other options

   MySQL has JSON support but less advanced; SQL Server supports JSON but is more Windows-focused; SQLite is lightweight and limited.

3. Final Answer:

   PostgreSQL -> Option A

4. Quick Check:

   Advanced JSON and complex data = PostgreSQL [OK]

Hint: Pick PostgreSQL for complex data and JSON [OK]

Common Mistakes:

• Choosing MySQL for advanced JSON
• Selecting SQL Server for cross-platform JSON
• Picking SQLite for heavy analytics