Basic_SQL.md

November 28, 2023 ยท View on GitHub

Database Knowledge: Basic SQL

SQL (Structured Query Language) is the standard language for managing and manipulating databases. Basic SQL involves understanding how to perform various operations on the data stored in databases, including querying, updating, inserting, and deleting data.

Key SQL Operations

  1. SELECT: Used to retrieve data from a database.

    SELECT column1, column2 FROM table_name;

  2. WHERE Clause: Used to filter records.

    SELECT * FROM table_name WHERE condition;

  3. INSERT INTO: Used to insert new records in a table.

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

  4. UPDATE: Used to modify existing records.

    UPDATE table_name SET column1 = value1 WHERE condition;

  5. DELETE: Used to delete records.

    DELETE FROM table_name WHERE condition;

  6. JOIN: Used to combine rows from two or more tables, based on a related column.

    SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

  7. GROUP BY: Used to group rows that have the same values in specified columns.

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

  8. ORDER BY: Used to sort the result set.

    SELECT * FROM table_name ORDER BY column_name ASC|DESC;

Basic SQL Data Types

  • Text Types: CHAR, VARCHAR, TEXT
  • Numeric Types: INT, FLOAT, DECIMAL
  • Date and Time Types: DATE, TIME, DATETIME
  • Boolean Type: BOOLEAN

Creating and Managing Tables

  • CREATE TABLE: Creates a new table.

    CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

  • ALTER TABLE: Used to add, delete, or modify columns in an existing table.

    ALTER TABLE table_name ADD column_name datatype;

  • DROP TABLE: Deletes an existing table.

    DROP TABLE table_name;

SQL Constraints

Constraints are used to specify rules for data in a table. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

Conclusion

Basic SQL skills are fundamental for anyone working with data, as SQL is widely used for interacting with relational databases. Understanding these basics enables the manipulation, querying, and management of data stored in databases, which is a critical skill in many software development and data analysis roles.