3-MySQL

Question 1

What is the purpose of the CREATE DATABASE SQL command?
A) To delete an existing database
B) To update data in the database
C) To create a new database
D) To retrieve data from the database

Answer 1

C) To create a new database

Explanation: The CREATE DATABASE command in SQL is used to create a new database within a MySQL/MariaDB system.

Question 2

How can you view the list of databases in MySQL?
A) LIST DATABASES
B) SHOW DATABASES
C) DISPLAY DATABASES
D) FIND DATABASES

Answer 2

B) SHOW DATABASES

Explanation: The SHOW DATABASES command is used in MySQL to display all the databases managed by the database server.

Question 3

Which data type would you use to store a username in MySQL?
A) INT
B) DATE
C) VARCHAR
D) FLOAT

Answer 3

Which data type would you use to store a username in MySQL?
A) INT
B) DATE
C) VARCHAR
D) FLOAT

Explanation: The VARCHAR data type is appropriate for storing strings such as usernames, allowing for variable-length entries.

Question 4

What is the standard MySQL/MariaDB port number?
A) 1521
B) 3306
C) 1433
D) 5432

Answer 4

B) 3306

Explanation: The default port for MySQL/MariaDB is 3306, although it can be configured to operate on a different port.

Question 5

What keyword is used to automatically increment a column’s value in MySQL?
A) INCREMENT ALWAYS
B) AUTO_INCREMENT
C) INCREASE
D) AUTO_ADD

Answer 5

B) AUTO_INCREMENT

Explanation: The AUTO_INCREMENT keyword in MySQL is used to have a column automatically increase its value with each new record inserted.

Question 6

Which command is used to view the structure of a table in MySQL?
A) VIEW TABLE
B) SHOW TABLE
C) DESCRIBE
D) EXPLAIN TABLE

Answer 6

C) DESCRIBE

Explanation: The DESCRIBE command is used to display the structure of a table, including its fields and their data types.

Question 7

Quiz 7: What does SQL stand for?
A) Structured Query Language
B) Simple Query Language
C) Standard Query Language
D) Secure Query Language

Answer 7

A) Structured Query Language

Explanation: SQL stands for Structured Query Language, a standardized programming language used for managing relational databases.

Question 8

How do you log in to a MySQL database from the command line?
A) mysql -u [username] -p[password]
B) mysql -user [username] -password [password]
C) connect mysql [username] [password]
D) login -u [username] -p [password]

Answer 8

A) mysql -u [username] -p[password]

Explanation: To log in to MySQL from the command line, you use the mysql -u [username] -p command, and then you are prompted to enter the password.

Question 9

Which SQL command changes the active database?
A) CHANGE DATABASE
B) SWITCH DATABASE
C) SELECT DATABASE
D) USE

Answer 9

D) USE

Explanation: The USE command in SQL is used to switch the active database to the one specified in the command.

Question 10

What does the NOT NULL constraint ensure in an SQL table?
A) The column must always have a numeric value
B) The column can never have a duplicate value
C) The column must always have some value and cannot be left blank
D) The column automatically increments its value

Answer 10

C) The column must always have some value and cannot be left blank

Explanation: The NOT NULL constraint in SQL ensures that a column cannot be left empty and must always have a value.

Question 11

What does the INSERT statement do in SQL?
A) Deletes records from a table
B) Adds new records to a table
C) Modifies existing records in a table
D) Lists all records in a table

Answer 11

B) Adds new records to a table

Explanation: The INSERT statement is used to add new records to an existing table in a database.

Question 12

What is the function of the SELECT statement in SQL?
A) To modify data in a table
B) To delete data from a table
C) To retrieve data from a table
D) To create a new table

Answer 12

C) To retrieve data from a table

Explanation: The SELECT statement is used to query and retrieve data from a database, allowing you to select one or more rows from one or more tables.

Question 13

How do you insert multiple records into a table in a single query?
A) Using multiple INSERT statements
B) Using a JOIN clause
C) By separating values with a comma in the INSERT statement
D) By using the UPDATE statement

Answer 13

C) By separating values with a comma in the INSERT statement

Explanation: Multiple records can be inserted in one go by separating each record’s values with a comma within the INSERT statement.

Question 14

What SQL statement would you use to remove a table from a database?
A) REMOVE TABLE
B) DELETE TABLE
C) DROP TABLE
D) ERASE TABLE

Answer 14

C) DROP TABLE

Explanation: The DROP TABLE statement is used to delete a table and all of its data from the database.

Question 15

Which SQL command is used to modify the structure of a table?
A) CHANGE TABLE
B) MODIFY TABLE
C) ALTER TABLE
D) UPDATE TABLE

Answer 15

C) ALTER TABLE

Explanation: The ALTER TABLE command is used to change the structure of an existing table, such as adding, deleting, or modifying columns.

Question 16

What clause is necessary in an UPDATE statement to specify which records should be modified?
A) SELECT
B) WHERE
C) WHEN
D) HAVING

Answer 16

B) WHERE

Explanation: The WHERE clause in an UPDATE statement specifies the conditions that must be met for the records to be updated.

Question 17

How would you view all the columns for all records in a table named employees?
A) SELECT * FROM employees
B) SHOW * FROM employees
C) LIST * FROM employees
D) DISPLAY * FROM employees

Answer 17

A) SELECT * FROM employees

Explanation: The SELECT * FROM table_name syntax is used to select all columns from all records in the specified table.

Question 18

What is the default behavior when inserting passwords directly into a database table without hashing?
A) The passwords are automatically encrypted by SQL.
B) It’s considered a best practice for security.
C) The passwords are stored in cleartext, which is a bad practice.
D) The passwords are compressed for storage efficiency.

Answer 18

C) The passwords are stored in cleartext, which is a bad practice.

Explanation: Storing passwords in cleartext (unencrypted) is considered a bad practice because it exposes sensitive information to potential security risks.

Question 19

What SQL command is used to add a new column to an existing table?
A) ADD COLUMN in CREATE TABLE
B) INSERT COLUMN
C) ALTER TABLE ADD
D) UPDATE TABLE ADD COLUMN

Answer 19

C) ALTER TABLE ADD

Explanation: To add a new column to an existing table, you would use the ALTER TABLE command followed by ADD.

Question 20

If you want to change the data type of a column in a table, which SQL command would you use?
A) CHANGE COLUMN
B) ALTER TABLE MODIFY
C) UPDATE TABLE
D) MODIFY DATA TYPE

Answer 20

B) ALTER TABLE MODIFY

Explanation: The ALTER TABLE MODIFY command is used to change the data type of a column in an existing table.

Question 21

What is the purpose of the ORDER BY clause in SQL?
A) To delete records in a specified order
B) To update records in a specified order
C) To select records in a specified order
D) To limit the number of records returned

Answer 21

C) To select records in a specified order

Explanation: The ORDER BY clause in SQL is used to sort the results of a query in either ascending or descending order based on one or more columns.

Question 22

How do you sort query results in descending order by a column named age?
A) SELECT * FROM people ORDER BY age ASC
B) SELECT * FROM people ORDER BY age DESC
C) SELECT * FROM people SORT BY age
D) SELECT * FROM people LIMIT age DESC

Answer 22

B) SELECT * FROM people ORDER BY age DESC

Explanation: To sort the results in descending order by the age column, you would use the ORDER BY age DESC clause in your SELECT statement.

Question 23

What SQL clause is used to limit the number of records returned by a query?
A) LIMIT
B) WHERE
C) RESTRICT
D) TOP

Answer 23

A) LIMIT

Explanation: The LIMIT clause is used in SQL to specify the maximum number of records the query should return.

Question 24

How can you retrieve only the first 5 records from a table called employees?
A) SELECT * FROM employees LIMIT 5
B) SELECT * FROM employees WHERE count=5
C) SELECT TOP 5 * FROM employees
D) SELECT * FROM employees FETCH FIRST 5 ROWS ONLY

Answer 24

A) SELECT * FROM employees LIMIT 5

Explanation: To retrieve only the first 5 records from the employees table, use SELECT * FROM employees LIMIT 5.

Question 25

What is the purpose of the WHERE clause in SQL? A) To specify the tables to select from B) To define the columns to select C) To conditionally select data based on specific criteria D) To sort the output of a query

Answer 25

C) To conditionally select data based on specific criteria ## Footnote Explanation: The WHERE clause is used in SQL to filter records and retrieve only those that meet a specified condition.

Question 26

How do you select rows where the username is exactly 'admin'? A) SELECT * FROM users WHERE username = 'admin' B) SELECT * FROM users WHERE username == 'admin' C) SELECT * FROM users LIMIT username = 'admin' D) SELECT * FROM users ORDER BY username = 'admin'

Answer 26

A) SELECT * FROM users WHERE username = 'admin' ## Footnote Explanation: To select rows where the username is 'admin', the correct SQL syntax is SELECT * FROM users WHERE username = 'admin'.

Question 27

What does the LIKE clause in SQL do? A) Deletes records that match a pattern B) Updates records to match a pattern C) Selects records that match a pattern D) Limits the number of records to those that match a pattern

Answer 27

C) Selects records that match a pattern ## Footnote Explanation: The LIKE clause is used in SQL to select records that match a specific pattern, often used with wildcard characters like % or _.

Question 28

Which wildcard character in SQL matches a single character? A) % B) # C) _ D) *

Answer 28

C) _ ## Footnote Explanation: In SQL, the _ wildcard character is used to match any single character in a LIKE clause.

Question 29

How can you use ORDER BY to sort by multiple columns? A) SELECT * FROM employees ORDER BY department, salary DESC B) SELECT * FROM employees ORDER BY department AND salary DESC C) SELECT * FROM employees ORDER BY department THEN salary DESC D) SELECT * FROM employees MULTIORDER BY department, salary DESC

Answer 29

A) SELECT * FROM employees ORDER BY department, salary DESC ## Footnote Explanation: To sort by multiple columns, list the columns separated by commas in the ORDER BY clause, specifying ASC or DESC for each column as needed.

Question 30

How do you use an offset with the LIMIT clause to skip the first record and take the next two? A) SELECT * FROM employees LIMIT 1 OFFSET 2 B) SELECT * FROM employees LIMIT 2 OFFSET 1 C) SELECT * FROM employees LIMIT 2, 1 D) SELECT * FROM employees OFFSET 1 LIMIT 2

Answer 30

B) SELECT * FROM employees LIMIT 2 OFFSET 1 ## Footnote Explanation: To skip the first record and fetch the next two, use LIMIT 2 OFFSET 1, where 2 is the number of records to return and 1 is the number of records to skip.

Question 31

What result is returned by the SQL expression 1 = 1 AND 'test' = 'abc'? A) 1 B) 0 C) NULL D) Error

Answer 31

B) 0 ## Footnote Explanation: The AND operator returns true only if both conditions are true. Since 'test' = 'abc' is false, the overall expression evaluates to false, which is represented as 0 in SQL.

Question 32

Which SQL operator is used to test for more than one condition where both conditions must be true for the row to be included in the results? A) OR B) AND C) NOT D) NOR

Answer 32

B) AND ## Footnote Explanation: The AND operator is used in SQL to combine multiple conditions, and all conditions must be true for the row to be included in the results.

Question 33

What does the NOT operator do in an SQL query? A) It ensures that both conditions are true. B) It negates a boolean value, turning true to false and vice versa. C) It combines two conditions and returns true if either is true. D) It subtracts one condition from another.

Answer 33

B) It negates a boolean value, turning true to false and vice versa. ## Footnote Explanation: The NOT operator in SQL is used to negate a boolean condition, turning true into false and false into true.

Question 34

How would you write an SQL query to find rows where the column 'age' is either greater than 30 or less than 20? A) SELECT * FROM people WHERE age > 30 AND age < 20; B) SELECT * FROM people WHERE age > 30 OR age < 20; C) SELECT * FROM people WHERE NOT age BETWEEN 20 AND 30; D) Both B and C are correct.

Answer 34

D) Both B and C are correct. ## Footnote Explanation: Both options B and C correctly use SQL logical operators to find rows where 'age' is either greater than 30 or less than 20. B uses the OR operator directly, and C uses NOT with BETWEEN to achieve the same effect.

Question 35

Which of the following is the correct way to use the SQL OR operator? A) SELECT * FROM users WHERE id = 10 OR id = 20; B) SELECT * FROM users WHERE id = 10 AND id = 20; C) SELECT * FROM users WHERE id = 10 NOR id = 20; D) SELECT * FROM users WHERE id = 10 XOR id = 20;

Answer 35

A) SELECT * FROM users WHERE id = 10 OR id = 20; ## Footnote Explanation: The OR operator is used to specify that any of the conditions separated by OR should be true for the rows to be included in the result set.

Question 36

What is the result of SELECT NOT 1 = 1;? A) 1 B) 0 C) NULL D) Error

Answer 36

B) 0 ## Footnote Explanation: The NOT operator inverses the result of the comparison. Since 1 = 1 is true, NOT 1 = 1 results in false, which is represented as 0 in SQL.

Question 37

In SQL, how is the NOT operator symbolically represented? A) ! B) - C) ^ D) %

Answer 37

A) ! ## Footnote Explanation: The NOT operator can be symbolically represented as ! in SQL, especially in systems that support symbol-based logical operators.

Question 38

Which SQL statement uses symbol operators correctly? A) SELECT * FROM products WHERE price > 100 && quantity < 10; B) SELECT * FROM products WHERE price > 100 || quantity < 10; C) Both A and B D) Neither A nor B

Answer 38

C) Both A and B ## Footnote Explanation: Both statements A and B use symbol operators correctly. && represents AND, and || represents OR, both of which are valid in SQL environments that allow symbol operators.

Question 39

What does the SQL operator != represent? A) Equals B) Not equals C) Greater than D) Less than

Answer 39

B) Not equals ## Footnote Explanation: The != operator in SQL is used to compare two values, indicating "not equals."

Question 40

How can you write a query to select all records where the username is not 'john' using symbol operators? A) SELECT * FROM logins WHERE username != 'john'; B) SELECT * FROM logins WHERE username ! 'john'; C) SELECT * FROM logins WHERE !username = 'john'; D) SELECT * FROM logins WHERE username = ! 'john';

Answer 40

A) SELECT * FROM logins WHERE username != 'john'; ## Footnote Explanation: The correct use of the not equals symbol operator is !=, making option A the correct way to select records where the username is not 'john'.