MockRounds Logo

Fundamentals of MySQL Basics Quiz Quiz

Assess your foundational knowledge of MySQL with these essential questions on SQL syntax, data types, table operations, and database management. This quiz helps users understand core MySQL concepts, including basic queries, common commands, and introductory database system principles.

  1. Selecting Data from a Table

    Which SQL statement would you use to retrieve all columns from a table named 'customers' in MySQL?

    1. SELECT * FROM customers;
    2. GET ALL FROM customers;
    3. CHOOSE ALL IN customers;
    4. PICK * FROM customers;

    Explanation: The correct SQL statement to retrieve all columns from 'customers' is 'SELECT * FROM customers;'. The asterisk (*) acts as a wildcard for all columns. The other options use incorrect keywords such as 'GET ALL,' 'CHOOSE ALL,' or 'PICK,' which are not valid in standard SQL and will result in errors if used.

  2. Creating a Table

    How do you define a new table called 'orders' with columns 'id' (an integer) and 'order_date' (a date) in MySQL?

    1. CREATE TABLE orders (id INT, order_date DATE);
    2. MAKE TABLE orders (id NUMBER, orderDate DATE);
    3. NEW TABLE orders (id INTEGER, ordered_date DATETIME);
    4. FORM TABLE orders (id INT, order_date DATETIME);

    Explanation: The 'CREATE TABLE' statement is the correct SQL syntax, using 'INT' for integer and 'DATE' for date type. 'MAKE TABLE' and 'NEW TABLE' are invalid syntaxes, and while 'DATETIME' is a valid type, the question specifically asks for 'DATE'. Using 'ordered_date' or camel-case column names also doesn't match the specification.

  3. Inserting Data

    To add a record with id 5 and name 'Olivia' into a table named 'users', which is the correct SQL statement?

    1. INSERT INTO users (id, name) VALUES (5, 'Olivia');
    2. ADD TO users (id = 5, name = 'Olivia');
    3. INSERT users SET id = 5, name = 'Olivia';
    4. PUT INTO users VALUES (5, 'Olivia');

    Explanation: The correct way to insert data is with 'INSERT INTO' specifying both columns and values. 'ADD TO' and 'PUT INTO' are not valid SQL commands, and 'INSERT users SET...' is incorrect syntax for inserting multiple columns at once in SQL; it's only used in specific contexts.

  4. Selecting Specific Columns

    Which query gets only the 'email' column from a table named 'contacts'?

    1. SELECT email FROM contacts;
    2. GET email FROM contacts;
    3. SHOW email IN contacts;
    4. SELECT contacts.email();

    Explanation: The correct syntax is 'SELECT email FROM contacts;'. 'GET' and 'SHOW' are not SQL keywords for retrieving data, and 'contacts.email()' is not valid syntax. Only 'SELECT email FROM contacts;' works as intended.

  5. Data Type for Text

    If you want to store variable-length strings up to 255 characters in a MySQL table, which data type should you use?

    1. VARCHAR(255)
    2. TEXT255
    3. STRING(255)
    4. VARSTRING

    Explanation: The 'VARCHAR(255)' data type is appropriate for storing variable-length character strings up to 255 characters. 'TEXT255' and 'VARSTRING' are not valid types in MySQL, and 'STRING(255)' is also not standard in SQL.

  6. Deleting Records

    What is the correct SQL command to delete all records from a table called 'logs' without removing the table itself?

    1. DELETE FROM logs;
    2. DROP logs;
    3. REMOVE ALL FROM logs;
    4. ERASE logs;

    Explanation: The 'DELETE FROM logs;' command removes all records while keeping the structure of the table intact. 'DROP logs;' would attempt to drop the table, 'REMOVE ALL FROM logs;' is not valid SQL, and 'ERASE logs;' is also incorrect syntax.

  7. Primary Key Purpose

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

    1. To uniquely identify each row in a table
    2. To store the largest value in a table
    3. To allow sorting of columns automatically
    4. To create automatic backups of data

    Explanation: A PRIMARY KEY is used to uniquely identify each row within a table. It enforces uniqueness across the column(s) chosen. It does not store the largest value, manage sorting automatically, or create backups.

  8. Auto-Increment Column

    Which keyword is used to automatically generate sequential values for a numeric column in MySQL?

    1. AUTO_INCREMENT
    2. AUTOINC
    3. INCREASE
    4. SERIES

    Explanation: 'AUTO_INCREMENT' is used in MySQL to generate sequential numeric values, commonly used with primary keys. 'AUTOINC' and 'SERIES' are sometimes seen in other systems or heard as concepts but are not MySQL keywords. 'INCREASE' does not represent a MySQL column attribute.

  9. Viewing Table Definition

    Which command lets you see the structure of an existing table named 'products'?

    1. DESCRIBE products;
    2. SHOW FIELDS IN products;
    3. EXAMINE TABLE products;
    4. LOOKUP products;

    Explanation: 'DESCRIBE products;' provides a list of columns, their types, and other attributes for the table. 'SHOW FIELDS IN products;' is close but not the standard syntax. 'EXAMINE TABLE' and 'LOOKUP' are not valid in MySQL.

  10. Filtering Rows

    Which SQL clause is used in MySQL to filter rows based on a specific condition, such as selecting users older than 18 years?

    1. WHERE
    2. ORDER BY
    3. JOIN
    4. FILTER

    Explanation: 'WHERE' is the correct clause to filter results. For example, 'SELECT * FROM users WHERE age > 18;'. 'ORDER BY' is used for sorting, 'JOIN' combines tables, and 'FILTER' is not an SQL keyword.

  11. Sorting Results

    If you want to display all rows from 'students' ordered by the 'score' column from highest to lowest, which clause should be used?

    1. ORDER BY score DESC
    2. GROUP BY score DESC
    3. WHERE score = DESC
    4. SORT score DOWN

    Explanation: 'ORDER BY score DESC' sorts the results in descending order by the 'score' column. 'GROUP BY' is for grouping data, and the other statements represent incorrect syntaxes or non-existent commands.

  12. Combining Conditions

    Which keyword allows you to combine two conditions in a MySQL WHERE clause so that both must be true?

    1. AND
    2. OR
    3. PLUS
    4. ALSO

    Explanation: 'AND' combines conditions so both sides must be true. 'OR' requires only one condition to be true, while 'PLUS' and 'ALSO' are not used as logical operators in SQL.

  13. Pattern Matching

    To find all users whose name starts with 'A', which SQL operator should you use with WHERE in MySQL?

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

    Explanation: The 'LIKE' operator is used for pattern matching, such as 'WHERE name LIKE 'A%''. 'MATCHES' is not valid in MySQL, while 'IS' and 'EQUALS' are used for equality, not patterns.

  14. Updating Data

    What is the correct way to change the 'email' of a user with id 8 to 'alex@example.com' in a table named 'users'?

    1. UPDATE users SET email = 'alex@example.com' WHERE id = 8;
    2. MODIFY users email TO 'alex@example.com' WHERE id = 8;
    3. CHANGE users SET email = 'alex@example.com' IF id = 8;
    4. ALTER users email = 'alex@example.com' WHERE id = 8;

    Explanation: 'UPDATE users SET email = 'alex@example.com' WHERE id = 8;' is proper syntax. 'MODIFY', 'CHANGE', and 'ALTER' are not used this way for updating row data in SQL.

  15. Deleting an Entire Table

    If you want to permanently remove a table named 'archive' and all its data from the database, what command should you use?

    1. DROP TABLE archive;
    2. DELETE FROM archive;
    3. REMOVE archive;
    4. ERASE TABLE archive;

    Explanation: 'DROP TABLE archive;' deletes both the table and its data permanently. 'DELETE FROM archive;' only clears the data but leaves the table itself. 'REMOVE' and 'ERASE' are invalid in this context.

  16. Counting Rows

    Which SQL function gets the number of rows in the table 'employees'?

    1. SELECT COUNT(*) FROM employees;
    2. SELECT SUM(*) FROM employees;
    3. SELECT TOTAL employees;
    4. SELECT NUMBER(*) FROM employees;

    Explanation: 'SELECT COUNT(*) FROM employees;' provides the row count. 'SUM(*)' attempts to add up numeric values. 'TOTAL' and 'NUMBER' are not SQL aggregate functions.

  17. Renaming a Table

    How can you change the name of a table from 'clients' to 'customers'?

    1. RENAME TABLE clients TO customers;
    2. ALTER TABLE clients RENAME TO customers;
    3. CHANGE clients TO customers;
    4. MODIFY TABLE clients NAME customers;

    Explanation: 'RENAME TABLE clients TO customers;' is the standard way in MySQL to rename a table. While 'ALTER TABLE ... RENAME TO ...' works in some systems, it is less common in MySQL. 'CHANGE' and 'MODIFY' do not rename tables.

  18. Limiting Query Output

    What MySQL clause restricts the number of returned rows, for example, to just 10?

    1. LIMIT
    2. RESTRICT
    3. STOP AT
    4. TOP

    Explanation: The 'LIMIT' clause, such as 'LIMIT 10', restricts the number of rows returned by a query. 'RESTRICT' and 'TOP' are not standard in MySQL (though 'TOP' exists in some other databases), and 'STOP AT' is not valid.

  19. Finding Unique Values

    Which keyword helps fetch only distinct values from a column in MySQL, for example, unique country names from 'users'?

    1. DISTINCT
    2. UNIQUE
    3. SINGLE
    4. DIFFERENT

    Explanation: 'DISTINCT' ensures that only unique results are fetched. 'UNIQUE', 'SINGLE', and 'DIFFERENT' are not valid SQL keywords for this purpose. 'UNIQUE' may refer to index constraints but not result limiting.

  20. Joining Tables

    If you want to retrieve data that combines rows from two tables where related columns match, which clause do you use?

    1. JOIN
    2. TOGETHER
    3. COMBINE
    4. ATTACH

    Explanation: 'JOIN' is the correct SQL clause for combining rows from two or more tables based on a related column. 'TOGETHER', 'COMBINE', and 'ATTACH' are not SQL keywords for this operation.

  21. Default Value Setting

    Which keyword can you use when creating a table to assign a column a value if none is provided?

    1. DEFAULT
    2. INIT
    3. FIRST
    4. STANDIN

    Explanation: 'DEFAULT' sets a column's default value when creating or altering a table. Using 'INIT', 'FIRST', or 'STANDIN' will not set a default and are not SQL keywords.

  22. Backing Up a Database

    Which MySQL command creates a backup file with the structure and contents of a database?

    1. mysqldump
    2. COPY DATABASE
    3. EXPORT DB
    4. BACKUP DATABASE

    Explanation: 'mysqldump' is the correct utility to export a database's structure and data in MySQL, producing a backup file. 'COPY DATABASE', 'EXPORT DB', and 'BACKUP DATABASE' are not valid built-in MySQL commands and may be used differently on other systems or require additional tools.

  23. Changing a Column's Data Type

    How do you alter the 'age' column in 'people' table from INT to VARCHAR(3)?

    1. ALTER TABLE people MODIFY age VARCHAR(3);
    2. CHANGE TABLE people age VARCHAR(3);
    3. ALTER age FROM people TO VARCHAR(3);
    4. UPDATE people SET age TYPE VARCHAR(3);

    Explanation: 'ALTER TABLE people MODIFY age VARCHAR(3);' is the correct statement to change a column's type. 'CHANGE TABLE' is not correct in this context, 'ALTER age FROM people...' is invalid, and 'UPDATE' is not used for altering column definitions.

  24. Selecting Records with NULL Values

    Which condition checks for records where the 'address' column in the 'customers' table does not have a value?

    1. WHERE address IS NULL
    2. WHERE address = ''
    3. WHERE address IS EMPTY
    4. WHERE address = NULL

    Explanation: 'WHERE address IS NULL' correctly checks for missing values. 'WHERE address = ''' would check for empty strings, 'IS EMPTY' is not valid, and 'address = NULL' doesn't work as expected in SQL.

  25. Reducing Duplicate Data

    What database design concept helps reduce data duplication in a MySQL database?

    1. Normalization
    2. Aggregation
    3. Compiling
    4. Standardization

    Explanation: 'Normalization' involves organizing data to reduce redundancy. 'Aggregation' relates to summarizing data, 'Compiling' is unrelated, and 'Standardization' means ensuring consistency but not specifically reducing repetition.

  26. Viewing All Databases

    What command shows all databases available on the MySQL server?

    1. SHOW DATABASES;
    2. SELECT ALL DB;
    3. DISPLAY DATABASES;
    4. LIST DB;

    Explanation: 'SHOW DATABASES;' is the standard SQL command to list all databases on your server. The other choices are not recognized commands in MySQL and will produce errors.

  27. Case Sensitivity in MySQL

    Which statement best describes case sensitivity in MySQL table and column names?

    1. Case sensitivity depends on the operating system.
    2. MySQL is always case sensitive.
    3. MySQL is always case insensitive.
    4. Only column names are case sensitive.

    Explanation: Case sensitivity in MySQL varies based on the underlying operating system’s file system. MySQL is not always case sensitive or insensitive, and both table and column names can be affected. It is not limited to column names alone.

mockrounds.com Learn • Practice • Evaluate