MySQL Basics

Question 1

What is MySQL?

Answer 1

MySQL is an open-source relational database management system (RDBMS) that uses SQL to manage and manipulate data.

Question 2

What does SQL stand for?

Answer 2

Structured Query Language.

Question 3

What is a database?

Answer 3

A structured collection of data stored electronically and organized for easy access and management.

Question 4

What is a table in MySQL?

Answer 4

A table is a collection of related data organized into rows and columns.

Question 5

What is a primary key?

Answer 5

A unique identifier for each record in a table. Example: id INT PRIMARY KEY.

Question 6

What is a foreign key?

Answer 6

A key used to link two tables together, referencing a primary key in another table.

Question 7

What command is used to show all databases?

Answer 7

Use SHOW DATABASES;

Question 8

How do you create a new database?

Answer 8

Use CREATE DATABASE db_name;

Question 9

How do you delete a database?

Answer 9

Use DROP DATABASE db_name;

Question 10

How do you select a database to use?

Answer 10

Use USE db_name;

Question 11

How do you create a new table?

Answer 11

Use CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

Question 12

How do you view all tables in a database?

Answer 12

Use SHOW TABLES;

Question 13

How do you see the structure of a table?

Answer 13

Use DESCRIBE table_name; or SHOW COLUMNS FROM table_name;

Question 14

How do you insert data into a table?

Answer 14

Use INSERT INTO table_name (columns) VALUES (values);

Question 15

How do you view all data from a table?

Answer 15

Use SELECT * FROM table_name;

Question 16

How do you select specific columns?

Answer 16

Use SELECT column1, column2 FROM table_name;

Question 17

How do you filter results in MySQL?

Answer 17

Use WHERE clause. Example: SELECT * FROM users WHERE age > 18;

Question 18

How do you sort query results?

Answer 18

Use ORDER BY. Example: SELECT * FROM users ORDER BY name ASC;

Question 19

How do you limit the number of results returned?

Answer 19

Use LIMIT. Example: SELECT * FROM users LIMIT 5;

Question 20

How do you update data in a table?

Answer 20

Use UPDATE table_name SET column=value WHERE condition;

Question 21

How do you delete data from a table?

Answer 21

Use DELETE FROM table_name WHERE condition;

Question 22

How do you prevent deleting all rows accidentally?

Answer 22

Always include a WHERE clause in DELETE statements.

Question 23

How do you find unique values in a column?

Answer 23

Use SELECT DISTINCT column_name FROM table_name;

Question 24

How do you rename a column or table?

Answer 24

Use ALTER TABLE table_name RENAME COLUMN old_name TO new_name; or RENAME TABLE old_name TO new_name;

Question 25

How do you add a new column to a table?

Answer 25

Use `ALTER TABLE table_name ADD column_name datatype;`

Question 26

How do you remove a column from a table?

Answer 26

Use `ALTER TABLE table_name DROP COLUMN column_name;`

Question 27

How do you combine results from two or more tables?

Answer 27

Use `JOIN`. Example: `SELECT * FROM users JOIN orders ON users.id = orders.user_id;`

Question 28

What are the main types of JOINs?

Answer 28

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.

Question 29

How do you count rows in a table?

Answer 29

Use `SELECT COUNT(*) FROM table_name;`

Question 30

How do you get the maximum or minimum value from a column?

Answer 30

Use `MAX()` and `MIN()`. Example: `SELECT MAX(age) FROM users;`

Question 31

How do you calculate the average of a column?

Answer 31

Use `AVG()`. Example: `SELECT AVG(price) FROM products;`

Question 32

How do you sum values in a column?

Answer 32

Use `SUM()`. Example: `SELECT SUM(quantity) FROM sales;`

Question 33

How do you group results in MySQL?

Answer 33

Use `GROUP BY`. Example: `SELECT city, COUNT(*) FROM users GROUP BY city;`

Question 34

How do you filter grouped results?

Answer 34

Use `HAVING`. Example: `SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;`

Question 35

How do you combine results from two queries?

Answer 35

Use `UNION`. Example: `SELECT name FROM employees UNION SELECT name FROM managers;`

Question 36

How do you sort results randomly?

Answer 36

Use `ORDER BY RAND();`

Question 37

How do you find the number of rows returned?

Answer 37

Use `ROW_COUNT()` after a query or `SELECT COUNT(*)`.

Question 38

How do you check MySQL version?

Answer 38

Use `SELECT VERSION();` or `mysql --version` in terminal.

Question 39

What command shows current database user?

Answer 39

Use `SELECT USER();`

Question 40

What command exits MySQL?

Answer 40

Type `EXIT;` or `QUIT;`

Question 41

What is a NULL value?

Answer 41

A value that represents missing or unknown data in a table.

Question 42

How do you check for NULL values?

Answer 42

Use `IS NULL` or `IS NOT NULL`. Example: `SELECT * FROM users WHERE email IS NULL;`

Question 43

How do you create an index?

Answer 43

Use `CREATE INDEX index_name ON table_name (column_name);`

Question 44

Why use an index?

Answer 44

To speed up searches and queries on large datasets.

Question 45

What is normalization?

Answer 45

The process of organizing data to reduce redundancy and improve efficiency.

Question 46

What is a transaction in MySQL?

Answer 46

A set of SQL statements that execute as a single unit using `START TRANSACTION`, `COMMIT`, and `ROLLBACK`.