SQL Flashcards
How to get all books record created last 7 days in SQL query?
SELECT * FROM books WHERE created_at >= CURRENT_DATE - INTERVAL 7 DAY;
How to get ordering in sql query?
SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC | DESC];
How to get only 5 results in sql query?
SELECT * FROM books LIMIT 5;
How to count the total number of records in table?
SELECT COUNT(*) AS total_records FROM your_table;
How to count only non-null values in a specific column in SQL?
SELECT COUNT(column_name) AS total_records FROM your_table;
How to count only records that meet a specific condition in SQL?
SELECT COUNT(*) AS total_records FROM your_table WHERE status = 'active';
How to sum some numeric data in column?
SELECT SUM(amount) AS total_amount FROM sales;
How to get author name and book name, when book and author are separate tables?
SELECT authors.name, books.name FROM authors INNER JOIN books ON authors.id = books.author_id;
What are different types of JOIN?
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
SELF JOIN
INNER JOIN (Default Join)
Returns only the matching rows from both tables.
~~~
SELECT A.column, B.column
FROM TableA A
INNER JOIN TableB B ON A.id = B.id;
~~~
LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table and matching rows from the right table. If no match is found, NULL is returned for columns from the right table.
~~~
SELECT A.column, B.column
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id;
~~~
RIGHT JOIN (RIGHT OUTER JOIN)
Returns all rows from the right table and matching rows from the left table. If no match is found, NULL is returned for columns from the left table.
~~~
SELECT A.column, B.column
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;
~~~
FULL JOIN (FULL OUTER JOIN)
Returns all records when there is a match in either table. If no match is found, NULL is returned for columns from the missing table.
~~~
SELECT A.column, B.column
FROM TableA A
FULL JOIN TableB B ON A.id = B.id;
~~~
CROSS JOIN
Returns a Cartesian product of both tables (every row from TableA is combined with every row from TableB).
SELECT A.column, B.column FROM TableA A CROSS JOIN TableB B;
Example response:
J.K. Rowling Harry Potter
J.K. Rowling 1984
J.K. Rowling Animal Farm
J.K. Rowling Pride and Prejudice
George Orwell Harry Potter
George Orwell 1984
George Orwell Animal Farm
George Orwell Pride and Prejudice
Mark Twain Harry Potter
Mark Twain 1984
Mark Twain Animal Farm
Mark Twain Pride and Prejudice
Jane Austen Harry Potter
Jane Austen 1984
Jane Austen Animal Farm
Jane Austen Pride and Prejudice
SELF JOIN
A table joins itself using an alias.
SELECT A.name AS Employee, B.name AS Manager FROM employees A JOIN employees B ON A.manager_id = B.id;
Example:
(Let’s assume authors has a recommended_author_id column linking to another author.)
author recommended_author
J.K. Rowling George Orwell
Mark Twain Jane Austen
What are operators for creating, editing, removing data?
Creating Data (INSERT)
Editing Data (UPDATE)
Removing Data (DELETE)
Removing All Data and Table (TRUNCATE & DROP)
Creating a Table (CREATE TABLE)
Modifying Table Structure (ALTER TABLE)
Creating Data (INSERT)
INSERT INTO employees (name, age, position)
VALUES ('Alice', 30, 'Developer');
```
INSERT INTO employees (name, age, position)
VALUES
(‘Bob’, 28, ‘Designer’),
(‘Charlie’, 35, ‘Manager’);
~~~
Editing Data (UPDATE)
UPDATE employees SET position = 'Senior Developer' WHERE name = 'Alice';
```
UPDATE employees
SET age = 31, position = ‘Lead Developer’
WHERE name = ‘Alice’;
~~~
Removing Data (DELETE)
DELETE FROM employees WHERE name = 'Charlie';
```
DELETE FROM employees
WHERE age > 60;
``` DELETE FROM employees;
Removing All Data and Table (TRUNCATE & DROP)
(Delete rows, keep table)
TRUNCATE TABLE employees;
(Remove entire table)
~~~
DROP TABLE employees;
~~~
Modifying Table Structure (ALTER TABLE)
ALTER TABLE employees ADD salary DECIMAL(10,2);
```
ALTER TABLE employees
MODIFY age SMALLINT;
``` ALTER TABLE employees CHANGE COLUMN position job_title VARCHAR(50);
```
ALTER TABLE employees
DROP COLUMN salary;
~~~
What are TRANSACTIONS?
A transaction in SQL is a sequence of operations performed as a single unit of work. A transaction follows the ACID properties to ensure database integrity.
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 500 WHERE name = 'Bob'; COMMIT; -- Saves the transaction
What is ACID?
Atomicity All operations in a transaction must complete fully or not at all. If one step fails, everything is rolled back.
Consistency The database must move from one valid state to another valid state. No partial or corrupt data is allowed.
Isolation Transactions must not interfere with each other. One transaction’s changes should not be visible until it is committed.
Durability Once a transaction is committed, the changes are permanently saved, even in case of system failures.
What if TRANSACTION will went wrong?
If something goes wrong (e.g., second query fails), we can undo the first step using ROLLBACK.
~~~
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE name = ‘Alice’;
– Simulating an error
IF (SELECT balance FROM accounts WHERE name = ‘Alice’) < 0 THEN
ROLLBACK;
ELSE
UPDATE accounts SET balance = balance + 500 WHERE name = ‘Bob’;
COMMIT;
END IF;
~~~
How to make partial ROLLBACK in TRANSACTION?
You can create a checkpoint and roll back only part of a transaction.
~~~
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE name = ‘Alice’;
SAVEPOINT after_deduction; – Savepoint here
UPDATE accounts SET balance = balance + 500 WHERE name = ‘Bob’;
– If something goes wrong, rollback to the savepoint
ROLLBACK TO after_deduction;
COMMIT;
~~~
What is INDEX?
An INDEX in SQL is a performance optimization feature that makes queries faster by allowing the database to find data quickly instead of scanning the entire table.
CREATE INDEX idx_lastname ON employees(last_name);
Why use INDEX?
Without an index, when you run:
SELECT * FROM employees WHERE last_name = 'Smith';
❌ The database scans every row in the employees table (called a full table scan), which is slow for large datasets.
✅ With an index on last_name, the database jumps directly to matching rows, making the query much faster.
What are migrations?
A migration is a way to manage database schema changes in a structured and version-controlled manner. Instead of manually running SQL commands to modify the database, migrations help developers track, apply, and revert schema changes using code.
Think of migrations as a “version history” for your database, just like Git tracks code changes.
