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;
