MySQL Basics Quiz: Essential Concepts and Commands Quiz

Test your foundational knowledge of MySQL with this 15-question quiz, covering key terms, common SQL statements, data types, schema design, and basic data manipulation. Ideal for interview preparation or beginners wanting to learn core MySQL concepts.

1. What is MySQL?

   Which statement best describes MySQL?

   1. MySQL is an open-source relational database management system.
   2. MySQL is a spreadsheet program for statistical analysis.
   3. MySQL is an operating system used for servers.
   4. MySQL is a programming language for mobile apps.

   Explanation: The correct answer is that MySQL is an open-source relational database management system, commonly used to manage and query structured data with SQL. It is not a spreadsheet tool like option B, nor is it a programming language as suggested in option C. Option D is incorrect because MySQL is not an operating system.

2. Purpose of SQL

   What is the main purpose of SQL in MySQL?

   1. To write website templates
   2. To draw network diagrams
   3. To query and manage data in databases
   4. To create images for applications

   Explanation: SQL is used for querying and managing data within databases, which is the correct function in MySQL. Options A and D refer to unrelated activities like creating images or diagrams. Option B is incorrect because SQL does not handle website templates.

3. SQL vs MySQL

   How does SQL differ from MySQL?

   1. SQL is a language, while MySQL is a database management system.
   2. SQL computes graphics; MySQL stores images directly.
   3. SQL is a type of keyboard; MySQL is a mouse.
   4. SQL is a web browser; MySQL is a search engine.

   Explanation: SQL is the language used to interact with databases, while MySQL is the system that stores and manages the data using SQL. The other options describe incorrect relationships or unrelated tools, such as hardware devices or web tools.

4. Features of MySQL

   Which of the following is a feature commonly supported by MySQL?

   1. Video editing tools
   2. ACID transactions
   3. Graphic design templates
   4. 3D animation rendering

   Explanation: MySQL supports ACID transactions, helping to ensure data reliability and integrity. The other options refer to media creation or editing, which are not within the scope of database management systems like MySQL.

5. Understanding Database Schema

   What does a database schema define in MySQL?

   1. The color theme of the user interface
   2. The speed of data download
   3. The amount of RAM required
   4. The logical structure of tables, columns, and relationships

   Explanation: A database schema organizes how data is structured, including tables and relationships. Options A, C, and D address appearance, performance, or hardware requirements, none of which are defined by the schema.

6. MySQL Data Types

   Which is NOT a data type supported by MySQL?

   1. VARCHAR
   2. TIMESTAMP
   3. PICTURE
   4. DECIMAL

   Explanation: MySQL supports various data types like VARCHAR, DECIMAL, and TIMESTAMP, but 'PICTURE' is not a valid data type. The other choices are standard types for strings, numbers, and dates.

7. CHAR vs VARCHAR

   What is a key difference between the CHAR and VARCHAR data types in MySQL?

   1. CHAR stores fixed-length strings; VARCHAR stores variable-length strings
   2. CHAR is always variable-length; VARCHAR is always fixed-length
   3. CHAR is only used for passwords
   4. Only CHAR supports numbers; VARCHAR does not

   Explanation: CHAR pads data to a fixed length, while VARCHAR only uses the needed space. Option A reverses their behavior and option C falsely claims CHAR supports only numbers. Option D is inaccurate; either can be used for many text fields.

8. Maximum VARCHAR Length

   What is the general maximum length allowed for a VARCHAR column in MySQL?

   1. 16 characters
   2. 2,048 characters
   3. 255 characters
   4. 65,535 characters

   Explanation: In most cases, MySQL allows VARCHAR columns up to 65,535 characters. The other options provide limits that are either too small, like 16 or 255, or not standard, like 2,048.

9. INT vs BIGINT

   How do the INT and BIGINT data types differ in MySQL?

   1. INT has a smaller range of values than BIGINT
   2. INT only stores decimal numbers; BIGINT stores text
   3. BIGINT is only for dates
   4. BIGINT uses less storage space than INT

   Explanation: INT supports smaller integer ranges compared to BIGINT, which can store much larger values. BIGINT is not for text or dates specifically, and it generally uses more, not less, storage than INT. Option A incorrectly states their data type functionality.

10. Purpose of ENUM

    What does the ENUM data type allow you to do in MySQL?

    1. Define a column with a specific list of allowed values
    2. Store phone numbers in international format
    3. Automatically encrypt all text entries
    4. Create a reference to another table

    Explanation: ENUM restricts entries to predefined options, improving data integrity. Options B and C relate to formats or encryption, which ENUM does not handle. Option D refers to foreign keys, not ENUM.

11. Creating Tables

    Which command is used to create a new table in MySQL?

    1. CREATE TABLE
    2. ADD DATABASE
    3. MAKE NEW
    4. START SCHEMA

    Explanation: The 'CREATE TABLE' command defines a new table. 'MAKE NEW' and 'ADD DATABASE' do not work in MySQL. 'START SCHEMA' incorrectly suggests beginning a schema, which is not a command.

12. Primary Key Function

    What is the main function of a PRIMARY KEY in a MySQL table?

    1. To store graphical data
    2. To uniquely identify each record and prevent duplicate or null values
    3. To enable automatic backups
    4. To display column names in bold text

    Explanation: A PRIMARY KEY uniquely marks each row and prevents duplicates or nulls. It does not affect how data is displayed, automate backups, or manage graphics.

13. Adding Columns

    Which statement adds a new column to an existing MySQL table?

    1. INSERT COLUMN TO table_name
    2. CHANGE TABLE column column_name
    3. ALTER TABLE table_name ADD COLUMN column_definition
    4. CREATE TABLE ADD column_name

    Explanation: ALTER TABLE with ADD COLUMN correctly adds new columns. The other statements either confuse table creation, insertion, or use incorrect syntax that MySQL does not recognize.

14. Drop vs Truncate

    What is the difference between the DROP and TRUNCATE commands in MySQL?

    1. TRUNCATE deletes the entire database; DROP deletes only records
    2. DROP and TRUNCATE do the exact same thing
    3. DROP deletes the table and its structure; TRUNCATE removes rows but keeps the structure
    4. Both remove only a single column

    Explanation: DROP removes everything—data and structure. TRUNCATE deletes just the rows while keeping the table for future use. The other options describe false information or oversimplify the differences.

15. Selecting All Columns

    Which SQL statement retrieves all columns from a table named 'employees'?

    1. GET ALL ROWS employees;
    2. SHOW * employees;
    3. SELECT * FROM employees;
    4. PULL employees ALL;

    Explanation: The correct command is 'SELECT * FROM employees;'. The other options use incorrect syntax or unsupported keywords that are not valid in SQL.