MockRounds Logo

Mysql basics to pro level topics Quiz

Challenge your understanding of MySQL, covering foundational commands and advanced database features. This quiz explores topics like data types, indexing, joins, transactions, functions, performance tuning, and query optimization for comprehensive learning.

  1. Identifying Primary Keys

    Which SQL statement correctly sets the 'id' column as the primary key when creating a 'users' table?

    1. CREATE users TABLE (id INT, name VARCHAR(50), PRIMARY KEY);
    2. CREATE TABLE users (id INT, name VARCHAR(50), PRIMARY id);
    3. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY);
    4. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

    Explanation: The correct option specifies 'id INT PRIMARY KEY' within the column definition, correctly making 'id' the primary key. The second option uses incorrect syntax for specifying a primary key. The third option incorrectly places 'PRIMARY KEY' outside the proper context. The fourth option places 'PRIMARY KEY' next to 'VARCHAR(50)', which would make 'name' the key, not 'id', and the syntax is incorrect.

  2. Choosing Correct Data Types

    What data type is most appropriate for storing prices such as 19.99 and 101.95 in MySQL?

    1. BINARY
    2. VARCHAR
    3. DECIMAL
    4. INT

    Explanation: DECIMAL is the correct data type as it is designed for fixed-point numbers like monetary values to prevent rounding errors. VARCHAR is for text data and unsuitable for calculations. INT cannot store fractional values as it only supports integers. BINARY is used for binary data such as images or files, not numbers.

  3. Understanding AUTO_INCREMENT

    Suppose you want the 'customer_id' to be generated automatically with each new row added to a table. Which attribute should be used in the column definition?

    1. UPDATE_NULL
    2. SERIALIZE
    3. AUTOFILL
    4. AUTO_INCREMENT

    Explanation: AUTO_INCREMENT ensures the database generates a unique sequential value for each new row, which is commonly used for primary keys. UPDATE_NULL is not a valid MySQL keyword. SERIALIZE and AUTOFILL are not MySQL attributes for handling incrementing values.

  4. Basic SELECT Query Syntax

    What is the correct SQL statement to retrieve all columns from a table named 'orders'?

    1. GET ALL FROM orders;
    2. EXTRACT * FROM orders;
    3. SELECT FROM * orders;
    4. SELECT * FROM orders;

    Explanation: SELECT * FROM orders; is the correct syntax to select all columns from the 'orders' table. The second option has incorrect word order. 'GET ALL FROM orders;' is not valid SQL and 'EXTRACT * FROM orders;' uses an invalid SQL command.

  5. Using WHERE Clauses

    Which query finds users with an email ending in '.com' in the 'users' table?

    1. SELECT * FROM users WHERE email HAS '.com';
    2. SELECT * FROM users WHERE email LIKE '%.com';
    3. SELECT * FROM users WHERE email = '.com';
    4. SELECT * FROM users WHERE email ENDSWITH '.com';

    Explanation: Using the LIKE operator with the pattern '%.com' correctly matches any email ending in '.com'. The second option only matches exact emails equal to '.com', which is unlikely. 'HAS' and 'ENDSWITH' are not valid MySQL operators for this purpose.

  6. Understanding JOIN Types

    Which JOIN retrieves all records from the left table and matching records from the right table, filling with NULL if no match exists?

    1. CROSS JOIN
    2. LEFT JOIN
    3. INNER JOIN
    4. RIGHT JOIN

    Explanation: LEFT JOIN returns all records from the left table and matches from the right, padding with NULL where there is no match. INNER JOIN only returns matched records. RIGHT JOIN does the opposite, returning all records from the right table. CROSS JOIN returns a cartesian product of both tables, not based on matches.

  7. Creating Indexes for Performance

    If you often search the 'customers' table by 'email', which statement adds an index for faster queries?

    1. SET INDEX email ON customers;
    2. CREATE INDEX idx_email ON customers (email);
    3. ADD INDEX TO customers (email);
    4. INSERT INDEX email IN customers;

    Explanation: CREATE INDEX idx_email ON customers (email); is the correct syntax for adding an index to the 'email' column. The second, third, and fourth options are either not valid commands or use incorrect syntax not recognized by MySQL.

  8. Transaction Control Commands

    Which statement guarantees a set of SQL operations are all completed successfully or rolled back if any fail?

    1. ALTER SEQUENCE;
    2. BEGIN; ... COMMIT;
    3. MERGE ALL;
    4. REPEAT UNTIL SUCCESS;

    Explanation: Using BEGIN; ... COMMIT; wraps operations in a transaction, ensuring atomic execution or rollback upon error. MERGE ALL is not a transaction command. ALTER SEQUENCE is for changing sequence objects, not transactions. REPEAT UNTIL SUCCESS; is not a recognized statement in MySQL.

  9. Understanding GROUP BY Usage

    Which SQL clause is used to group results by a specific column, such as city names in an 'addresses' table?

    1. BUNDLE BY
    2. ORDER BY
    3. CLASSIFY BY
    4. GROUP BY

    Explanation: GROUP BY is the correct SQL clause for grouping records that have the same values in specified columns. ORDER BY arranges row order but does not group them. CLASSIFY BY and BUNDLE BY are not valid SQL clauses.

  10. Limiting Result Sets

    How would you modify a query to retrieve only the first 5 rows from a 'products' table?

    1. SELECT * FROM products LIMIT 5;
    2. SELECT TOP 5 FROM products;
    3. SELECT * FROM products FIRST 5;
    4. FETCH 5 ROWS FROM products;

    Explanation: The LIMIT clause restricts the number of rows returned, making 'SELECT * FROM products LIMIT 5;' correct. SELECT TOP 5 is not standard MySQL syntax. The THIRD and fourth options use incorrect SQL or non-existent clauses.

  11. Defining Foreign Keys

    To enforce a relationship where each order in 'orders' is linked to an entry in 'customers', how do you declare a foreign key?

    1. FOREIGN KEY (customer_id) REFERENCES customers(id)
    2. CONNECT customer_id WITH customers.id
    3. REFER (customer_id) TO customers(id)
    4. IMPORT KEY customer_id FROM customers(id)

    Explanation: FOREIGN KEY (customer_id) REFERENCES customers(id) is the correct syntax for establishing referential integrity. The other options use incorrect or non-existent SQL keywords for creating a foreign key.

  12. Safe String Matching

    When searching for partial matches of the name 'Ann' in a 'people' table, which operator should be used?

    1. FIND
    2. LIKE
    3. MATCHES
    4. EQUALS

    Explanation: LIKE supports wildcards for partial string matching, making it suitable for this case. EQUALS only works for complete, exact matches. MATCHES and FIND are not valid operators in standard MySQL SQL.

  13. Using Aggregate Functions

    Which SQL function calculates the total number of rows in a table regardless of NULL values?

    1. ROWS()
    2. TOTAL()
    3. COUNT(*)
    4. SUM(*)

    Explanation: COUNT(*) counts all rows, including those with NULL values, making it the correct choice. SUM(*) is not a valid syntax and SUM is used for adding column values. TOTAL() and ROWS() are not standard MySQL functions.

  14. Understanding NULL Handling

    Which SQL condition can be used to specifically select records where the 'deleted_at' column has no value?

    1. WHERE deleted_at = NULL
    2. WHERE deleted_at IS NULL
    3. WHERE deleted_at EQUALS NULL
    4. WHERE deleted_at IS BLANK

    Explanation: 'IS NULL' correctly checks for missing or NULL values in a column. Using '=' for NULL comparisons is invalid in SQL logic. IS BLANK and EQUALS NULL do not exist as valid conditional checks in MySQL.

  15. Subquery Usage

    Which SQL statement finds products with a price greater than the average price in the 'products' table?

    1. SELECT * FROM products HAVING price u003E AVERAGE(price);
    2. SELECT * FROM products WHERE price u003E ALL (AVG(price));
    3. SELECT * FROM products WHERE price u003E MEAN(products.price);
    4. SELECT * FROM products WHERE price u003E (SELECT AVG(price) FROM products);

    Explanation: The correct query uses a subquery to calculate the average and compares each price against it. HAVING is for filtering after grouping, not suitable here. MEAN and ALL(AVG(price)) are invalid or misuse functions in standard SQL.

  16. Optimizing Query Performance

    Which approach commonly improves query speed when filtering by 'username' in a large 'users' table?

    1. Increasing VARCHAR size for usernames
    2. Storing usernames as JSON data
    3. Creating an index on the 'username' column
    4. Using DELETE instead of SELECT

    Explanation: Adding an index enables faster searches by reducing the need to scan every row. Storing data as JSON can complicate and slow down filtering. Using DELETE is irrelevant for data retrieval, and increasing VARCHAR size affects storage, not lookup performance.

mockrounds.com Learn • Practice • Evaluate