SQL Introduction

1. Basic Commands

• SELECT: Retrieve data from a table. This command is used to fetch data from a database. You can specify the columns you want to retrieve or use * to select all columns.

  SELECT column1, column2 FROM table_name;

• INSERT: Add new records to a table. This command is used to insert new rows of data into a table.

  INSERT INTO table_name (column1, column2) VALUES (value1, value2);

• UPDATE: Modify existing records in a table. This command is used to update existing data within a table.

  UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

• DELETE: Remove records from a table. This command is used to delete rows from a table.

  DELETE FROM table_name WHERE condition;

2. Creating Tables

• CREATE TABLE: Define a new table. This command is used to create a new table in the database with specified columns and data types.

  CREATE TABLE table_name (column1 datatype, column2 datatype, PRIMARY KEY (column1));

3. Data Types

• Common data types:
  • INT: Integer, a whole number.
  • VARCHAR(size): Variable-length string, used to store text.
  • DATE: Date, used to store dates.
  • FLOAT: Floating-point number, used to store decimal numbers.

4. Conditions and Operators

• WHERE: Filter records. This clause is used to specify conditions for filtering records.

  SELECT * FROM table_name WHERE condition;

• AND, OR, NOT: Combine multiple conditions. These operators are used to combine multiple conditions in a query.

  SELECT * FROM table_name WHERE condition1 AND condition2;
  SELECT * FROM table_name WHERE condition1 OR condition2;
  SELECT * FROM table_name WHERE NOT condition;

• LIKE: Search for a pattern. This operator is used in a WHERE clause to search for a specified pattern in a column.

  SELECT * FROM table_name WHERE column LIKE 'pattern%';

• IN: Match any value in a list. This operator is used to specify multiple possible values for a column.

  SELECT * FROM table_name WHERE column IN (value1, value2, ...);

5. SQL Joins

• INNER JOIN - Returns rows when there is at least one match in both tables.

• LEFT JOIN - returns all rows from the left table and the matched rows from the right table.

• RIGHT JOIN - returns all rows from the right table and the matched rows from the left table.

• FULL JOIN - returns rows when there is a match in one of the tables.

6. Grouping and Aggregating

• GROUP BY: Group rows that have the same values. This clause is used to arrange identical data into groups.

  SELECT column, COUNT(*) FROM table_name
  GROUP BY column;

• HAVING: Filter groups based on a condition. This clause is used to filter groups created by the GROUP BY clause.

  SELECT column, COUNT(*) FROM table_name
  GROUP BY column HAVING COUNT(*) > 1;

• Aggregate Functions: Perform calculations on a set of values.

  • COUNT(): Count the number of rows.
  • SUM(): Sum of a numeric column.
  • AVG(): Average value of a numeric column.
  • MIN(): Minimum value.
  • MAX(): Maximum value.

  SELECT COUNT(*), SUM(column), AVG(column), MIN(column), MAX(column)
  FROM table_name;

7. Indexes

• CREATE INDEX: Improve query performance. This command is used to create an index on a table to enhance the speed of data retrieval.

  CREATE INDEX index_name ON table_name (column);

8. Constraints

• Ensure data integrity. Constraints are rules enforced on data columns to ensure the accuracy and reliability of the data.

  • PRIMARY KEY: Uniquely identifies each record.
  • FOREIGN KEY: Uniquely identifies a record in another table.
  • UNIQUE: Ensures all values in a column are different.
  • NOT NULL: Ensures a column cannot have a NULL value.
  • CHECK: Ensures all values in a column satisfy a specific condition.

  CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype,
  CONSTRAINT fk_name FOREIGN KEY (column) REFERENCES other_table(column));

Similar pages