Understanding MySQL and SQL Concepts

What is the difference between MySQL and SQL?

• MySQL is a programming language; SQL is a database.

• SQL is a type of software; MySQL is a query language.

• MySQL is a cloud service; SQL is a local database.

• SQL is a language; MySQL is a database management system that uses SQL.

Write a SQL query to create a table called 'students' with columns 'roll_no', 'name', 'class', and 'marks'.

• CREATE TABLE students (id INT, name TEXT, grade VARCHAR(50), score INT);

• CREATE TABLE students (roll_no INT, full_name VARCHAR(100), class_name VARCHAR(50), total_marks DECIMAL);

• CREATE TABLE learners (roll_no INT, name VARCHAR(100), section VARCHAR(50), marks INT);

• CREATE TABLE students (roll_no INT, name VARCHAR(100), class VARCHAR(50), marks FLOAT);

What is the purpose of the PRIMARY KEY constraint in MySQL?

• To group multiple records together in a table.

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

• To automatically generate unique IDs for each record.

• To define the data type of each column in a table.

Write a SQL query to insert data into the 'students' table.

• UPDATE students SET age = 20 WHERE name = 'John Doe';

• DELETE FROM students WHERE name = 'John Doe';

• SELECT * FROM students WHERE name = 'John Doe';

• INSERT INTO students (name, age) VALUES ('John Doe', 20);

What is the difference between DELETE and TRUNCATE commands in MySQL?

• DELETE removes all rows and cannot be rolled back; TRUNCATE is row-specific and can be rolled back.

• DELETE is row-specific and can be rolled back; TRUNCATE removes all rows quickly and cannot be rolled back.

• TRUNCATE can be rolled back; DELETE cannot be rolled back.

• DELETE is faster than TRUNCATE and is used for large datasets; TRUNCATE is slower and used for small datasets.

Explain the role of foreign keys in relational databases.

• Foreign keys are primarily used for data encryption.

• Foreign keys are used to create indexes on tables.

• Foreign keys enforce referential integrity between tables in a relational database.

• Foreign keys allow for the duplication of data across tables.

Write a SQL query to update the marks of a student in the 'students' table.

• UPDATE students SET marks = new_marks WHERE student_id = specific_id;

• UPDATE students SET score = new_marks WHERE id = specific_id;

• UPDATE students SET marks = specific_marks WHERE student_id = new_id;

• UPDATE students SET marks = new_marks;

What is normalization in databases? Why is it important?

• Normalization increases data redundancy and complexity.

• Normalization has no impact on data integrity or efficiency.

• Normalization is only necessary for small databases.

• Normalization is important because it minimizes data redundancy, prevents data anomalies, and enhances data integrity, making the database more efficient and easier to maintain.

Write a SQL query to retrieve all students with marks greater than 80.

• SELECT * FROM students WHERE marks >= 80;

• SELECT name FROM students WHERE marks > 80;

• SELECT * FROM students WHERE marks < 80;

• SELECT * FROM students WHERE marks > 80;

What are indexes in MySQL and how do they improve performance?

• Indexes in MySQL are structures that enhance query performance by allowing faster data retrieval.

• Indexes are primarily used for data storage rather than performance.

• Indexes slow down data retrieval by adding extra overhead.

• Indexes in MySQL are only used for sorting data.