SQL Fundamentals Lecture Template

Question 1

What is SQL?

Answer 1

SQL (Structured Query Language) is used to interact with relational databases, allowing us to retrieve, manipulate, and manage data efficiently.

Question 2

What does the SELECT statement do?

Answer 2

The SELECT statement retrieves data from a database.

Question 3

What is the syntax for a basic SELECT statement?

Answer 3

SELECT column1, column2 FROM table_name;

Question 4

How do you select all columns from a table?

Answer 4

SELECT * FROM employees;

Question 5

What does the WHERE clause do?

Answer 5

The WHERE clause filters data based on a condition.

Question 6

Provide an example of filtering data with WHERE.

Answer 6

SELECT first_name FROM employees WHERE department = ‘HR’;

Retrieves employees working in the HR department.

Question 7

What does an INNER JOIN do?

Answer 7

Returns only matching records from both tables.

Question 8

Provide an example of an INNER JOIN.

Answer 8

SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

Retrieves order IDs and corresponding customer names.

Question 9

What does a LEFT JOIN do?

Answer 9

Returns all records from the left table and matching records from the right.

Question 10

Provide an example of a LEFT JOIN.

Answer 10

SELECT customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Shows all customers, even if they haven’t placed orders.

Question 11

What does a RIGHT JOIN do?

Answer 11

Returns all records from the right table and matching records from the left.

Question 12

Provide an example of a RIGHT JOIN.

Answer 12

SELECT orders.order_id, customers.customer_name FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Question 13

What does a FULL OUTER JOIN do?

Answer 13

Returns all records when there is a match in either table.

Question 14

Provide an example of a FULL OUTER JOIN.

Answer 14

SELECT customers.customer_name, orders.order_id FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Question 15

What does a CROSS JOIN do?

Answer 15

Produces a Cartesian product of two tables.

Question 16

Provide an example of a CROSS JOIN.

Answer 16

SELECT products.product_name, suppliers.supplier_name FROM products CROSS JOIN suppliers;

Question 17

What does the UNION operator do?

Answer 17

Combines rows from two queries, removing duplicates.

Question 18

Provide an example of the UNION operator.

Answer 18

SELECT city FROM customers UNION SELECT city FROM suppliers;

Question 19

What does the INTERSECT operator do?

Answer 19

Returns only the common rows between two queries.

Question 20

Provide an example of the INTERSECT operator.

Answer 20

SELECT city FROM customers INTERSECT SELECT city FROM suppliers;

Question 21

What does the MINUS operator do?

Answer 21

Returns rows from the first query that are not in the second.

Question 22

Provide an example of the MINUS operator.

Answer 22

SELECT city FROM customers MINUS SELECT city FROM suppliers;

Question 23

What does the ORDER BY clause do?

Answer 23

Sorts the result set in ascending (ASC) or descending (DESC) order.

Question 24

Provide an example of using ORDER BY.

Answer 24

SELECT first_name FROM employees ORDER BY first_name ASC;

Question 25

What does the DISTINCT keyword do?

Answer 25

Removes duplicate values.

Question 26

Provide an example of using DISTINCT.

Answer 26

SELECT DISTINCT department FROM employees;

Question 27

What do LIMIT and OFFSET do?

Answer 27

Limits the number of rows returned.

Question 28

Provide an example of LIMIT and OFFSET.

Answer 28

SELECT * FROM employees LIMIT 5 OFFSET 10; ## Footnote Returns 5 employees, skipping the first 10.

Question 29

What does the LIKE operator do?

Answer 29

Searches for patterns using % (wildcard for multiple characters) and _ (single character).

Question 30

Provide an example of using LIKE.

Answer 30

SELECT first_name FROM employees WHERE first_name LIKE 'J%'; ## Footnote Finds all employees whose first name starts with 'J'.

Question 31

What are aggregate functions?

Answer 31

Functions like AVG, SUM, MAX, MIN, and COUNT that perform calculations on a set of values.

Question 32

Provide an example of using COUNT with GROUP BY.

Answer 32

SELECT department, COUNT(*) FROM employees GROUP BY department; ## Footnote Counts employees per department.

Question 33

What does the HAVING clause do?

Answer 33

Filters grouped results.

Question 34

Provide an example of using HAVING.

Answer 34

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; ## Footnote Returns only departments with more than 5 employees.

Question 35

How do you insert data into a table?

Answer 35

INSERT INTO employees (first_name, last_name, department) VALUES ('John', 'Doe', 'Sales');

Question 36

How do you update data in a table?

Answer 36

UPDATE employees SET department = 'Marketing' WHERE first_name = 'John';

Question 37

How do you delete data from a table?

Answer 37

DELETE FROM employees WHERE department = 'HR';

Question 38

What is the syntax to create a table?

Answer 38

CREATE TABLE employees (id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, department VARCHAR(50));

Question 39

How do you alter a table?

Answer 39

ALTER TABLE employees ADD salary INT;

Question 40

How do you drop a table?

Answer 40

DROP TABLE employees;

Question 41

What is a primary key?

Answer 41

Ensures uniqueness for each row.

Question 42

Provide an example of creating a primary key.

Answer 42

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

Question 43

What is a foreign key?

Answer 43

Enforces referential integrity.

Question 44

Provide an example of creating a foreign key.

Answer 44

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

Question 45

What is a unique constraint?

Answer 45

Ensures column values are unique.

Question 46

Provide an example of a unique constraint.

Answer 46

CREATE TABLE users (email VARCHAR(100) UNIQUE);

Question 47

What is a check constraint?

Answer 47

Restricts allowed values.

Question 48

Provide an example of a check constraint.

Answer 48

CREATE TABLE employees (salary INT CHECK (salary > 30000));

Question 49

What is the purpose of indexing?

Answer 49

Speeds up queries.

Question 50

Provide an example of creating an index.

Answer 50

CREATE INDEX idx_name ON employees (first_name, last_name);

Question 51

What is a view in SQL?

Answer 51

A virtual table for querying.

Question 52

Provide an example of creating a view.

Answer 52

CREATE VIEW employee_view AS SELECT first_name, last_name FROM employees;

Question 53

What is a trigger in SQL?

Answer 53

Executes automatically before or after events.

Question 54

Provide an example of creating a trigger.

Answer 54

CREATE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW EXECUTE PROCEDURE check_employee_data();

Question 55

What topics were covered in the lecture?

Answer 55

Querying data, Joins & operators, Sorting & filtering, Aggregate functions, Data manipulation, Managing tables, Constraints, Indexing & views.