MySQL vs PostgreSQL vs SQLite - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

SELECT [1] FROM users;

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

A*

Ball

Ceverything

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 create a table named 'products' with an integer 'id' column as primary key.

MySQL

CREATE TABLE products (id [1] PRIMARY KEY);

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

ATEXT

BFLOAT

CVARCHAR(255)

DINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using text types like TEXT or VARCHAR for numeric IDs

Using FLOAT which allows decimals

The INT type is used for integer numbers, suitable for an ID column.

3fill in blank

hard

Fix the error in the query to get the count of rows in 'orders'.

MySQL

SELECT COUNT([1]) FROM orders;

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

Aorder_id

B*

Call

Drows

Attempts:

3 left

💡 Hint

Common Mistakes

Using words like 'all' or 'rows' which cause errors

Using a specific column name which may miss NULL values

Using COUNT(*) counts all rows in the table.

4fill in blank

hard

Fill both blanks to select the name and price from 'items' where price is greater than 100.

MySQL

SELECT [1], price FROM items WHERE price [2] 100;

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

Aname

B<

C>

Ditem_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for price comparison

Using wrong column names

Select the column name and filter rows where price > 100.

5fill in blank

hard

Fill all three blanks to insert a new user with id 5, name 'Alice', and email '[e-mail]' into 'users'.

MySQL

INSERT INTO users ([1], [2], [3]) VALUES (5, 'Alice', '[e-mail]');

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

Aid

Bname

Cemail

Dusername

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names like 'username' instead of 'name'

Mismatching columns and values order

The columns must match the values: id, name, and email.

Practice

1. Which database is best known for being lightweight and easy to set up without a server?

easy

A. SQLite

B. MySQL

C. PostgreSQL

D. Oracle

Solution

1. Step 1: Understand SQLite's design

   SQLite is designed as a serverless, self-contained database engine, making it lightweight and easy to use.

2. Step 2: Compare with other databases

   MySQL and PostgreSQL require server setup, unlike SQLite.

3. Final Answer:

   SQLite -> Option A

4. Quick Check:

   Lightweight and serverless = SQLite [OK]

Hint: Lightweight and no server means SQLite [OK]

Common Mistakes:

• Confusing MySQL as lightweight
• Thinking PostgreSQL is serverless
• Choosing Oracle which is not open-source

2. Which of the following is the correct way to start a MySQL server from the command line?

easy

A. service mysql start

B. mysql --start

C. start mysql

D. mysql start-server

Solution

1. Step 1: Recall MySQL server start commands

   On many systems, MySQL server is started using system service commands like 'service mysql start'.

2. Step 2: Check other options

   'mysql --start', 'start mysql', and 'mysql start-server' are not valid MySQL server start commands.

3. Final Answer:

   service mysql start -> Option A

4. Quick Check:

   Use system service command to start MySQL [OK]

Hint: Use 'service mysql start' to launch MySQL server [OK]

Common Mistakes:

• Using 'mysql --start' which is invalid
• Trying 'start mysql' which is not a command
• Confusing client commands with server commands

3. Given the following SQL snippet run on PostgreSQL, what will be the output?

SELECT version();

medium

A. An error because version() is not supported

B. Empty result set

C. The MySQL version string

D. The PostgreSQL version string

Solution

1. Step 1: Understand the version() function in PostgreSQL

   PostgreSQL supports the version() function which returns the current version string of the server.

2. Step 2: Evaluate the query output

   The query returns a single row with the PostgreSQL version details.

3. Final Answer:

   The PostgreSQL version string -> Option D

4. Quick Check:

   version() returns PostgreSQL version info [OK]

Hint: version() shows PostgreSQL version info [OK]

Common Mistakes:

• Assuming version() is MySQL-only
• Expecting an error from version()
• Thinking it returns empty result

4. You try to run this SQLite command but get an error:

CREATE DATABASE testdb;

What is the problem?

medium

A. Database name 'testdb' is reserved

B. Syntax error in the command

C. SQLite does not support CREATE DATABASE command

D. You need to specify a file path

Solution

1. Step 1: Understand SQLite database creation

   SQLite does not use CREATE DATABASE; it creates databases by opening a file.

2. Step 2: Analyze the error cause

   Using CREATE DATABASE causes an error because SQLite expects a file to open, not a database creation command.

3. Final Answer:

   SQLite does not support CREATE DATABASE command -> Option C

4. Quick Check:

   SQLite creates DB by file, no CREATE DATABASE [OK]

Hint: SQLite creates DB by file, no CREATE DATABASE command [OK]

Common Mistakes:

• Assuming CREATE DATABASE works in SQLite
• Thinking syntax is wrong
• Believing database names are reserved words

5. You want to choose a database for a web app that needs complex queries, full ACID compliance, and extensibility. Which is the best choice?

hard

A. SQLite

B. PostgreSQL

C. MySQL

D. MariaDB

Solution

1. Step 1: Identify requirements

   The app needs complex queries, full ACID compliance, and extensibility.

2. Step 2: Match database features

   PostgreSQL is known for advanced features, strong ACID compliance, and extensibility compared to SQLite and MySQL.

3. Final Answer:

   PostgreSQL -> Option B

4. Quick Check:

   Advanced features and ACID = PostgreSQL [OK]

Hint: Advanced features and ACID compliance point to PostgreSQL [OK]

Common Mistakes:

• Choosing SQLite for complex queries
• Picking MySQL over PostgreSQL for extensibility
• Confusing MariaDB as more advanced than PostgreSQL