Basic operations
SELECT: Used to retrieve data from a database
SELECT * FROM customers;INSERT: Used to insert new data into a database
INSERT INTO customers (name, email)
VALUES ('John Doe', 'johndoe@example.com');UPDATE: Used to update existing data in a database
UPDATE customers
SET email = 'johndoe@gmail.com'
WHERE name = 'John Doe';DELETE: Used to delete data from a database
DELETE FROM customers
WHERE name = 'John Doe';WHERE: Used to filter results based on specific conditions
SELECT * FROM customers
WHERE name = 'John Doe';ORDER BY: Used to sort results in ascending or descending order
SELECT * FROM customers
ORDER BY name DESC;DELETE: Used to delete data from a database,
DELETE FROM customers
WHERE name = 'John Doe';Data Definition Language
CREATE TABLE: Used to create a new table in a database.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);ALTER TABLE: Used to modify the structure of an existing table
ALTER TABLE customers
ADD COLUMN address VARCHAR(100);DROP TABLE: Used to delete a table from a database
DROP TABLE customers;Joins
INNER JOIN: Returns only the rows where there is a match in both tables
SELECT customers.name, orders.order_number
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values will be returned for the columns from the right table.
SELECT customers.name, orders.order_number
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values will be returned for the columns from the left table.
SELECT customers.name, orders.order_number
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;FULL OUTER JOIN: Returns all the rows from both tables, including the matching and non-matching rows. If there is no match, NULL values will be returned for the missing columns.
SELECT customers.name, orders.order_number
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customer_id;