Mastering MySQL Concepts

What is the purpose of database normalization?

• The purpose of database normalization is to minimize redundancy and dependency by organizing fields and table relationships.

• To enhance security by restricting data access.

• To simplify complex queries by merging tables.

• To increase data duplication for faster access.

Explain the difference between INNER JOIN and LEFT JOIN.

• INNER JOIN returns all rows from both tables; LEFT JOIN returns only unmatched rows from the left.

• INNER JOIN returns matched rows only; LEFT JOIN returns all rows from the left table and matched rows from the right.

• INNER JOIN returns all rows from the right table; LEFT JOIN returns only matched rows from the left.

• INNER JOIN includes all rows from the left table; LEFT JOIN excludes unmatched rows from the right.

What is the result of using UNION in SQL?

• Sorts the results of multiple SELECT statements in ascending order.

• Combines results of multiple SELECT statements, removing duplicates.

• Only retrieves data from a single table.

• Combines results of multiple SELECT statements without removing duplicates.

Name three aggregate functions in MySQL.

• MAX

• SUM, COUNT, AVG

• FIRST

• MIN

What data type would you use for storing a person's name?

• boolean

• string

• integer

• float

Write a SQL query to select all records from a table named 'students'.

• SELECT ALL FROM students;

• SELECT * FROM students;

• GET * FROM students;

• SELECT students;

What is the function of COUNT() in SQL?

• To retrieve unique values from a column.

• The function of COUNT() in SQL is to count the number of rows in a result set.

• To calculate the average value of a column.

• To sort the rows in a result set.

How do you define a primary key in a MySQL table?

• Define the primary key in a separate ALTER TABLE statement.

• Use the UNIQUE constraint in the CREATE TABLE statement.

• Set the primary key using a foreign key reference.

• Use the PRIMARY KEY constraint in the CREATE TABLE statement.

What is the difference between CHAR and VARCHAR data types?

• CHAR is variable-length; VARCHAR is fixed-length.

• CHAR can store more data than VARCHAR.

• VARCHAR is used for numeric data types; CHAR is for text.

• CHAR is fixed-length; VARCHAR is variable-length.

Write a SQL function to calculate the average of a column named 'score'.

• CREATE FUNCTION calculate_average() RETURNS FLOAT AS $$ BEGIN RETURN (SELECT AVG(score) FROM your_table_name); END; $$ LANGUAGE plpgsql;

• CREATE FUNCTION average_score() RETURNS INTEGER AS $$ BEGIN RETURN (SELECT AVG(score) FROM scores); END; $$ LANGUAGE sql;

• CREATE FUNCTION get_average() RETURNS FLOAT AS $$ BEGIN RETURN (SELECT SUM(score) FROM your_table_name) / COUNT(score); END; $$ LANGUAGE plpgsql;

• CREATE PROCEDURE calculate_avg() AS $$ BEGIN SELECT AVG(score) FROM your_table_name; END; $$;