MYSQL basic

Question 1

What is SQL?

Answer 1

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It’s used to perform tasks such as querying data, updating records, and creating or modifying database structures.

Question 2

What is a primary key?

Answer 2

A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same key and does not allow NULL values.

Question 3

What is the difference between DELETE and TRUNCATE?

Answer 3

DELETE removes specific rows based on a condition, can be rolled back, and triggers events. TRUNCATE removes all rows in a table, is faster, cannot be rolled back, and does not trigger events.

Question 4

Explain the SELECT statement and its use.

Answer 4

The SELECT statement is used to query data from a database. It specifies the columns to retrieve and can include conditions for filtering the data.

Question 5

What is a foreign key?

Answer 5

A foreign key is a field (or a set of fields) in one table that uniquely identifies a row of another table. It creates a relationship between two tables.(non primary of one table but primary key of another table)

Question 6

How does a JOIN work in SQL and types of JOIN’s?

Answer 6

JOIN is used to combine rows from two or more tables based on a related column between them. Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Question 7

Describe the difference between INNER JOIN and LEFT JOIN.

Answer 7

INNER JOIN returns only the rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table; non-matching rows from the right table result in NULL.

Question 8

What does the WHERE clause do?

Answer 8

The WHERE clause filters records to include only those that meet specific conditions. It is used in SELECT, UPDATE, and DELETE statements

Question 8

How do you sort results in SQL?

Answer 8

Use the ORDER BY clause to sort results in ascending (ASC) or descending (DESC) order based on one or more columns.

Question 9

What is GROUP BY used for?

Answer 9

The GROUP BY clause groups rows that have the same values in specified columns. It’s often used with aggregate functions like COUNT, SUM, AVG, etc.

Question 10

What is the purpose of the HAVING clause?

Answer 10

HAVING filters records after aggregation (using functions like SUM or COUNT), while WHERE filters rows before aggregation.

Question 10

Describe the UPDATE statement.

Answer 10

The UPDATE statement modifies existing records in a table based on specified conditions. eg
UPDATE students
SET email = ‘new_email@example.com’
WHERE student_id = 101;

Question 11

What is a subquery?

Answer 11

A subquery is a query nested within another query. It can return data that the outer query will use.

Question 12

What is a CHECK constraint?

Answer 12

A CHECK constraint limits the values that can be placed in a column by enforcing a condition that each row must meet. used during creation of table columns

Question 13

Explain the UNIQUE constraint.

Answer 13

The UNIQUE constraint ensures that all values in a column are distinct from each other, preventing duplicate values.

Question 14

Types of SQL commands

Answer 14

DML (Data Manipulation Language)
DDL (Data Definition Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
DQL (Data Query Language)

Question 15

Explain DML and it’s commands

Answer 15

DML (Data Manipulation Language)
Used to manipulate data within existing tables.
Commands:
SELECT: Retrieve data from a database.
INSERT: Insert new records into a table.
UPDATE: Modify existing records in a table.
DELETE: Delete records from a table.

Question 16

Explain DDL and it’s commands

Answer 16

Used to define, modify, or remove the structure of database objects like tables, indexes, etc.
Commands:
CREATE: Create new database objects (e.g., tables, indexes).
ALTER: Modify existing database objects.
DROP: Delete database objects.
TRUNCATE: Remove all records from a table without logging individual row deletions.

Question 17

Explain DCL and it’s commands

Answer 17

DCL (Data Control Language)
Used to control access to data in the database.
Commands:
GRANT: Provide specific privileges to users.
REVOKE: Remove previously granted privileges.

Question 18

Explain TCL and it’s commands

Answer 18

TCL (Transaction Control Language)
Used to manage transactions in the database to maintain integrity.
Commands:
COMMIT: Save the current transaction’s changes permanently.
ROLLBACK: Undo changes made in the current transaction.
SAVEPOINT: Set a save point within a transaction to roll back to if needed.
SET TRANSACTION: Set transaction characteristics such as isolation level.

Question 19

Explain DQL and it’s commands

Answer 19

DQL (Data Query Language)
Sometimes considered a subset of DML, DQL includes only the SELECT command used specifically for querying the database.
Commands:
SELECT: Retrieve data from tables based on specific criteria.

Question 20

What is an INNER JOIN?

Answer 20

An INNER JOIN returns records that have matching values in both tables.
Example:
SELECT students.first_name, enrollments.course_id
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id;

Question 21

Describe a LEFT JOIN.

Answer 21

A LEFT JOIN returns all records from the left table and matching records from the right table. If no match is found, NULL values are returned for columns from the right table.
Example:
SELECT students.first_name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id;

Question 22

What is a FULL OUTER JOIN?

Answer 22

Answer: A FULL OUTER JOIN returns all records when there is a match in either the left or right table. If there’s no match, NULL values are returned.
Note: Not all databases support FULL OUTER JOIN.

Question 23

What is the purpose of an INDEX?

Answer 23

An INDEX is used to speed up the retrieval of rows from a table. It works like a pointer and can improve performance on large datasets.

Question 24

Explain the difference between UNIQUE and PRIMARY KEY constraints.

Answer 24

Both constraints ensure unique values in a column. However, a PRIMARY KEY is the main identifier for a record and cannot contain NULL values, while a UNIQUE constraint allows one NULL value.

Question 25

What is the purpose of a DEFAULT constraint?

Answer 25

A DEFAULT constraint assigns a default value to a column if no other value is provided during record insertion.

Question 26

What are some common SQL data types?

Answer 26

Common data types include:
INT: Integer values.
VARCHAR(size): Variable-length string.
DATE: Date values.
BOOLEAN: True or false.
DECIMAL(p, s): Decimal numbers with precision and scale.

Question 27

How can you concatenate strings in SQL?

Answer 27

You can concatenate strings using the CONCAT function or the || operator (database-specific).
Example:
SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM students;