From GPT

Question 1

What is the purpose of a SELECT statement in SQL?

Answer 1

The purpose of a SELECT statement is to retrieve specific columns or all columns from one or more tables.

Question 2

Write the basic syntax for a SELECT statement.

Answer 2

SELECT column1, column2 FROM table_name;

SELECT name, position FROM employees;

Question 3

How do you filter records in SQL to include only those that meet specific conditions?

Answer 3

By using the WHERE clause.
Syntax: SELECT column1, column2 FROM table_name WHERE condition;

SELECT name, age FROM employees WHERE department = 'HR';

Question 4

What is an INNER JOIN used for?

Answer 4

An INNER JOIN is used to return rows when there is at least one match in both tables.

SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

Question 5

Provide an example of an INNER JOIN statement.

Answer 5

SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

Question 6

What is the purpose of a LEFT JOIN?

Answer 6

A LEFT JOIN returns all rows from the left table, and the matched rows from the right table.

SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

Question 7

How can you group rows that have the same values in specified columns?

Answer 7

By using the GROUP BY clause.
Syntax: SELECT column, COUNT(*) AS alias FROM table_name GROUP BY column;

SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department;

Question 8

What is the purpose of the HAVING clause?

Answer 8

The HAVING clause filters groups based on aggregated results.

SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department HAVING num_employees > 3;

Question 9

How do you sort the result set of a query by one or more columns?

Answer 9

By using the ORDER BY clause.
Syntax: SELECT column1, column2 FROM table_name ORDER BY column1 DESC;

SELECT name, salary FROM employees ORDER BY salary DESC;

Question 10

Write an example of an INSERT statement.

Answer 10

INSERT INTO employees (name, age, department) VALUES ('Alice Green', 28, 'Marketing');

Question 11

How can you modify existing records in a table?

Answer 11

By using the UPDATE statement.
Syntax: UPDATE table_name SET column1 = value1 WHERE condition;

UPDATE employees SET age = 29 WHERE name = 'Alice Green';

Question 12

What is the purpose of the DELETE statement in SQL?

Answer 12

The DELETE statement is used to delete existing records from a table.

DELETE FROM employees WHERE name = 'Alice Green';

Question 13

Provide an example of creating a table with a primary key.

Answer 13

CREATE TABLE departments ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) );

Question 14

Name 3 types of functions that can be used in SQL queries.

Answer 14

1. String functions
2. Date functions
3. Numerical functions

SELECT name, ROUND(salary, 0) AS rounded_salary FROM employees;

Question 15

Write an example of using a subquery in a SQL statement.

Answer 15

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Question 16

How do you create a temporary table in SQL?

Answer 16

CREATE TEMPORARY TABLE high_earners AS SELECT name, salary FROM employees WHERE salary > 80000; SELECT * FROM high_earners;

Question 17

What is the purpose of creating indices in SQL?

Answer 17

Indices are created to improve the performance of query operations.

CREATE INDEX idx_salary ON employees(salary);

Question 18

Which tools can be used for basic reporting and data visualization with SQL databases?

Answer 18

• Tableau: Connect to MySQL, extract employee data, and create interactive dashboards.
• Power BI: Import MySQL data, model it, and share insights through rich visualizations.
• Excel: Use ODBC to pull data into spreadsheets for further analysis and charting.

Question 19

How do you manage user access and security in SQL?

Answer 19

By using the GRANT statement.
Syntax: GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

GRANT SELECT, INSERT ON company_db.employees TO 'jdoe'@'localhost';