Skip to content

Latest commit

 

History

History
139 lines (98 loc) · 5.33 KB

SQL Syntax.md

File metadata and controls

139 lines (98 loc) · 5.33 KB
Raw

SQL Syntax

SQL (Structured Query Language) is a standard language used to interact with relational databases, including PostgreSQL. SQL allows you to create, retrieve, update, and delete data in a relational database, as well as manage database objects such as tables, views, and indexes. Here's an overview of common SQL syntax and operations:

  1. SELECT: The SELECT statement is used to retrieve data from one or more tables. You can specify the columns you want to retrieve, apply filters using the WHERE clause, and sort the results using the ORDER BY clause.

Example: Get all rows from the 'customers' table with the 'first_name', 'last_name', and 'email' columns:

SELECT first_name, last_name, email
FROM customers;
  1. INSERT: The INSERT statement is used to insert new rows into a table. You can specify the values for each column or use a subquery to insert data from another table.

Example: Insert a new customer into the 'customers' table:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');
  1. UPDATE: The UPDATE statement is used to modify existing rows in a table. You can set new values for specific columns and apply filters using the WHERE clause.

Example: Update the email address of a customer with a specific 'customer_id':

UPDATE customers
SET email = 'new.email@example.com'
WHERE customer_id = 1;
  1. DELETE: The DELETE statement is used to remove rows from a table. You can apply filters using the WHERE clause to delete specific rows.

Example: Delete a customer with a specific 'customer_id':

DELETE FROM customers
WHERE customer_id = 1;
  1. JOIN: JOIN clauses are used to retrieve data from multiple tables based on a related column. There are several types of JOINs, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Example: Get the 'first_name' and 'last_name' of customers along with their order 'total' from the 'customers' and 'orders' tables:

SELECT c.first_name, c.last_name, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
  1. GROUP BY: The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions (COUNT, SUM, AVG, MAX, or MIN) to perform calculations on each group.

Example: Get the total number of orders per customer:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;
  1. HAVING: The HAVING clause is used to filter the results of a GROUP BY query based on a condition applied to the result of an aggregate function.

Example: Get customers who have placed more than five orders:

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
  1. CREATE TABLE: The CREATE TABLE statement is used to create a new table with the specified columns and their data types, along with any constraints such as primary keys, foreign keys, and unique constraints.

Example: Create a new 'products' table:

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price NUMERIC(10, 2) NOT NULL,
  description TEXT
);
  1. ALTER TABLE: The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns, changing data types, or modifying constraints.

Example: Add a new column 'stock' to the 'products' table:

ALTER TABLE products
ADD COLUMN stock INTEGER NOT NULL DEFAULT 0;
  1. DROP TABLE: The DROP TABLE statement is used to remove a table and all its data permanently from the database.

Example: Drop the 'products' table:

DROP TABLE products;
  1. CREATE INDEX: The CREATE INDEX statement is used to create an index on one or more columns of a table, which can improve query performance.

Example: Create an index on the 'email' column of the 'customers' table:

CREATE INDEX idx_customers_email ON customers(email);
  1. Transactions: Transactions are used to group multiple SQL statements into a single unit of work. Transactions ensure that either all statements are executed successfully, or none are executed if an error occurs. In PostgreSQL, you can use the BEGIN, COMMIT, and ROLLBACK statements to control transactions.

Example: Transfer 100 from 'account1' to 'account2':

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'account1';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'account2';
COMMIT;
  1. Subqueries: A subquery is a SELECT statement nested inside another query, such as SELECT, INSERT, UPDATE, or DELETE. Subqueries can be used to filter, aggregate, or manipulate data before using it in the outer query.

Example: Get customers who have placed orders with a total greater than the average order total:

SELECT first_name, last_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > (
    SELECT AVG(total) FROM orders
  )
);

These are some of the fundamental SQL syntax elements and operations you can use to interact with PostgreSQL or any other relational database management system. As you work with SQL, you'll learn more advanced techniques, such as window functions, common table expressions (CTEs), and stored procedures to help you optimize and manage your database more effectively.