From GPT Flashcards
What is the purpose of a SELECT statement in SQL?
The purpose of a SELECT statement is to retrieve specific columns or all columns from one or more tables.
Write the basic syntax for a SELECT statement.
SELECT column1, column2 FROM table_name;
SELECT name, position FROM employees;
How do you filter records in SQL to include only those that meet specific conditions?
By using the WHERE clause. Syntax: SELECT column1, column2 FROM table_name WHERE condition;
SELECT name, age FROM employees WHERE department = 'HR';
What is an INNER JOIN used for?
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;
Provide an example of an INNER JOIN statement.
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
What is the purpose of a LEFT JOIN?
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;
How can you group rows that have the same values in specified columns?
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;
What is the purpose of the HAVING clause?
The HAVING clause filters groups based on aggregated results.
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING num_employees > 3;
How do you sort the result set of a query by one or more columns?
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;
Write an example of an INSERT statement.
INSERT INTO employees (name, age, department) VALUES ('Alice Green', 28, 'Marketing');
How can you modify existing records in a table?
By using the UPDATE statement. Syntax: UPDATE table_name SET column1 = value1 WHERE condition;
UPDATE employees SET age = 29 WHERE name = 'Alice Green';
What is the purpose of the DELETE statement in SQL?
The DELETE statement is used to delete existing records from a table.
DELETE FROM employees WHERE name = 'Alice Green';
Provide an example of creating a table with a primary key.
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
Name 3 types of functions that can be used in SQL queries.
- String functions
- Date functions
- Numerical functions
SELECT name, ROUND(salary, 0) AS rounded_salary FROM employees;
Write an example of using a subquery in a SQL statement.
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
How do you create a temporary table in SQL?
CREATE TEMPORARY TABLE high_earners AS SELECT name, salary FROM employees WHERE salary > 80000;
SELECT * FROM high_earners;
What is the purpose of creating indices in SQL?
Indices are created to improve the performance of query operations.
CREATE INDEX idx_salary ON employees(salary);
Which tools can be used for basic reporting and data visualization with SQL databases?
- 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.
How do you manage user access and security in SQL?
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';
