MYSQL Concepts

Explain the importance of good database design in MYSQL.

• Good database design in MYSQL is not important

• Good database design in MYSQL is crucial for maintaining data integrity, improving performance, and ensuring scalability and security.

• Database design in MYSQL has no impact on data integrity

• Poor database design in MYSQL is preferred for performance

Define normalization and explain its importance in database management.

• Normalization is not necessary in database management

• Normalization decreases database efficiency

• Normalization increases data redundancy

• Normalization in database management is important as it ensures data consistency, reduces redundancy, and improves overall database efficiency.

What are stored procedures in MYSQL and how are they used?

• Stored procedures in MYSQL are temporary SQL statements stored in the client-side for reuse.

• Stored procedures in MYSQL cannot accept parameters or return results.

• Stored procedures in MYSQL are precompiled SQL statements stored in the database server for reuse. They can accept parameters, perform operations, and return results. Stored procedures are used to improve performance, security, and maintainability by reducing network traffic and preventing SQL injection attacks.

• Stored procedures in MYSQL are used to slow down performance and increase network traffic.

What are the different normal forms in database normalization?

• 6NF

• 1NF, 2NF, 3NF, BCNF, 4NF

• 5NF

• 2NF

Write a SQL query to retrieve all records from a table named 'employees'.

• SELECT * FROM employee;

• SELECT * FROM employee

• SELECT * FROM employees;

• SELECT * FROM Employees;

What is the purpose of primary keys in database tables?

• To provide a secondary level of security for the database

• To automatically sort the records in the table

• To allow for duplicate records within the table

• To uniquely identify each record in a table and ensure data integrity.

How can you optimize MYSQL queries for better performance?

• Increase the number of columns selected

• Use inappropriate data types

• Use indexes, avoid DELETE *

• Use indexes, avoid SELECT *, limit columns selected, use appropriate data types, optimize joins, and use query caching.

Discuss the advantages and disadvantages of denormalization in database design.

• Enhanced data integrity, complex queries

• Improved data consistency, slower read performance

• Increased data security, reduced storage space

• Advantages: Improved read performance, simplified queries. Disadvantages: Data redundancy, potential for data inconsistency.

What is the role of foreign keys in relational databases?

• Foreign keys are used to store duplicate data in different tables.

• Foreign keys are not necessary in relational databases.

• Foreign keys are only used for sorting data in tables.

• Foreign keys link tables by referencing the primary key of another table and enforce referential integrity.

Write a SQL query to update the 'salary' column in a table named 'employees'.

• UPDATE employees SET salary = new_salary_value;

• ALTER employees UPDATE salary = new_salary_value;

• CHANGE employees SET salary = new_salary_value;

• MODIFY employees SET salary = new_salary_value;