SQL

  • SQL (Structured Query Language): The standard language for relational database management, encompassing data definition (DDL), data manipulation (DML), data control (DCL), and transaction control (TCL). It's used for managing, querying, and updating data in relational database systems.

SQL (Structured Query Language)

SQL (Structured Query Language) is the standard language for interacting with relational databases. It provides a comprehensive set of commands for data manipulation, management, and retrieval. SQL is not just a query language; it includes:

  • Data Definition Language (DDL) for defining database schemas.

  • Data Manipulation Language (DML) for inserting, updating, and deleting data.

  • Data Control Language (DCL) for managing access and security.

  • Transaction Control Language (TCL) for managing database transactions.

🎯 Key Uses of SQL:

  • Manage data in relational database management systems (RDBMS):
    SQL is essential for managing and organizing data within systems like MySQL, PostgreSQL, Oracle, SQL Server, and others. It ensures data integrity, enables efficient retrieval, and supports complex transactions.

  • Perform complex queries to extract valuable insights:
    SQL allows users to formulate intricate queries to retrieve specific data subsets, enabling data analysis and reporting. These queries can involve multiple tables, aggregate functions, subqueries, and conditional logic, providing extensive analytical capabilities.

  • Retrieve, insert, update, and delete records efficiently:
    SQL commands facilitate quick and precise data modifications, ensuring data accuracy and relevance. Efficient data modification is crucial for maintaining up-to-date information and supporting real-time applications.

  • Define and modify database structures and schema:
    SQL enables users to create, alter, and optimize database structures to suit evolving data requirements. Schema modifications can include adding new tables, altering column types, defining relationships via foreign keys, and creating indexes to optimize query performance.

  • Ensure data integrity and consistency through constraints:
    SQL constraints, such as primary keys, foreign keys, unique constraints, and check constraints, enforce data rules, preventing inconsistencies and errors. Constraints are essential for maintaining data quality and reliability.

🧱 Basic SQL Concepts

📂 1. Databases and Tables

  • Database: An organized collection of related data, structured to provide efficient data storage and retrieval.
    Databases provide a structured way to store and manage data, ensuring data integrity and accessibility. They are designed to handle large volumes of data while maintaining performance. Relational databases organize data into tables with rows and columns.

  • Table: A structured collection of rows (records) and columns (fields) that store data in a relational format.
    Tables are the fundamental building blocks of databases, organizing data into rows and columns for easy querying and manipulation. Each column has a specific data type, ensuring data consistency. Tables can be related to each other through primary and foreign keys.

CREATE DATABASE school;
USE school;

CREATE TABLE students (
 id INT PRIMARY KEY,
 name VARCHAR(100),
 age INT,
 grade VARCHAR(10)
);

🛠 2. Data Types

  • INT – Integer numbers (e.g., 1,100,−51, 100, -5):
    Represents whole numbers without decimal points. Used for columns storing counts, IDs, or any non-fractional numeric data. Integers can be signed or unsigned, depending on the range of values to be stored. Other integer types include SMALLINT, BIGINT, and TINYINT.

  • VARCHAR(n) – Variable-length string with a maximum length of nn characters (e.g., 'John Doe'):
    Stores character strings of varying lengths, up to the specified maximum. Suitable for names, addresses, and other text-based data. VARCHAR is efficient for storing strings of different lengths, as it only uses the necessary storage space. CHAR(n) is a fixed-length string data type.

  • DATE – Date format (e.g.,'2024-01-01'):
    Represents calendar dates, storing year, month, and day. Useful for recording events, deadlines, and time-related information. Date formats can vary, but the standard format is YYYY-MM-DD. Other date and time data types include TIME, DATETIME, and TIMESTAMP.

  • BOOLEAN – True or False values:
    Stores binary values representing truth or falsehood. Used for flags, statuses, or any binary categorical data. Boolean values are essential for representing conditions and states. Some databases use BIT or TINYINT to represent boolean values.

  • FLOAT / DOUBLE – Decimal numbers with single (FLOAT) or double (DOUBLE) precision:
    Represents numbers with decimal points. FLOAT provides single-precision, while DOUBLE offers double-precision for higher accuracy. Choose DOUBLE for applications requiring high precision, such as scientific calculations. DECIMAL(p, s) is a fixed-precision data type with pp digits and ss decimal places.

✏ 3. Basic Queries

🔍 SELECT

SELECT * FROM students;
SELECT name, age FROM students;

  • The SELECT statement is used to retrieve data from one or more tables.
    SELECT * retrieves all columns, while SELECT name, age retrieves only the specified columns. The SELECT statement is the foundation of data retrieval in SQL. It can be combined with other clauses like WHERE, ORDER BY, and LIMIT to refine the results.

📥 INSERT

INSERT INTO students (id, name, age, grade)
VALUES (1, 'Najuka', 19, 'A');

  • The INSERT statement is used to add new rows to a table.
    You specify the table name, the columns to insert into, and the values to be inserted. Ensure that the data types of the inserted values match the column data types. You can insert multiple rows at once using a single INSERT statement with multiple value sets.

🔄 UPDATE

UPDATE students SET grade = 'B' WHERE id = 1;

  • The UPDATE statement is used to modify existing data in a table.
    You specify the table name, the columns to update, the new values, and a WHERE clause to identify the rows to update. Always use a WHERE clause to avoid unintentionally updating all rows in the table. Updating multiple columns can be done in a single UPDATE statement.

❌ DELETE

DELETE FROM students WHERE id = 1;

  • The DELETE statement is used to remove rows from a table.
    You specify the table name and a WHERE clause to identify the rows to delete. Be cautious when using the DELETE statement, as deleted data cannot be easily recovered. You can delete all rows from a table by omitting the WHERE clause, but this is generally discouraged.

🔎 Filtering and Sorting

🎯 WHERE Clause

SELECT * FROM students WHERE age > 18;

  • The WHERE clause is used to filter records based on specified conditions.
    In this example, only students older than 18 are selected. The WHERE clause can include comparison operators (=, >, <, >=, <=, !=), logical operators (AND, OR, NOT), and pattern matching (LIKE, ILIKE, SIMILAR TO).

🔢 ORDER BY

SELECT * FROM students ORDER BY name ASC;

  • The ORDER BY clause is used to sort the result-set in ascending or descending order.
    ASC specifies ascending order, while DESC specifies descending order. By default, it sorts in ascending order. Sorting is essential for presenting data in a meaningful way. You can sort by multiple columns by including them in the ORDER BY clause, with the first column having the highest precedence.

🧮 Functions

  • COUNT() – Counts the number of rows

  • SUM() – Calculates the sum of values in a column

  • AVG() – Calculates the average value of a column

  • MAX() – Finds the maximum value in a column

  • MIN() – Finds the minimum value in a column

SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;

  • These functions perform aggregate calculations on data.
    COUNT(*) counts all rows, while AVG(age) calculates the average age of students. Aggregate functions are commonly used in data analysis and reporting. They can be combined with the GROUP BY clause to perform calculations on groups of rows.

🔗 Joins

🤝 Types of Joins:

  • INNER JOIN – Returns only matching records from both tables

  • LEFT JOIN – Returns all records from the left table and matched records from the right table

  • RIGHT JOIN – Returns all records from the right table and matched records from the left table

  • FULL JOIN – Returns all records from both tables

SELECT * FROM students
INNER JOIN courses ON students.id = courses.student_id;

  • Joins are used to combine rows from two or more tables based on a related column.
    Joins are essential for querying data from multiple related tables. The ON clause specifies the join condition. Other types of joins include CROSS JOIN and SELF JOIN.

🧠 Constraints

  • PRIMARY KEY – Uniquely identifies a record in a table:
    Ensures each row in a table is uniquely identifiable. Only one primary key is allowed per table. Primary keys are crucial for maintaining data integrity. They can consist of one or more columns.

  • FOREIGN KEY – Establishes a link between two tables using a column that refers to the primary key of another table:
    Creates a parent-child relationship between tables, enforcing referential integrity. Foreign keys ensure that relationships between tables are consistent. They reference the primary key of another table.

  • UNIQUE – Ensures that all values in a column are unique:
    Prevents duplicate entries in a column, ensuring data uniqueness. Unique constraints are useful for columns like email addresses and usernames. They can be combined with NOT NULL to ensure that a column always contains a unique value.

  • NOT NULL – Ensures that a column cannot contain NULL values:
    Requires a value to be present in a column, preventing missing or undefined data. NOT NULL constraints ensure that essential data is always provided. They can be applied to any column in a table.

📦 Advanced SQL

🎛 Subqueries

SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);

  • Subqueries are queries nested inside another query.
    They are used to perform queries based on the results of another query. In this example, the subquery retrieves the maximum age, and the outer query selects students with that age. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.

📜 Views

CREATE VIEW teen_students AS
SELECT * FROM students WHERE age < 20;

  • Views are virtual tables based on the result-set of an SQL statement.
    They provide a way to simplify complex queries and restrict access to certain data. Views do not store data; they simply provide a different perspective on the underlying tables. You can query views just like tables.

🗂 Indexes

  • Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
    Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. Creating an index on a column that is frequently used in WHERE clauses can significantly improve query performance. However, indexes can slow down INSERT, UPDATE, and DELETE operations, so they should be used judiciously.

💼 Transactions

  • Transactions are a sequence of SQL operations that are performed as a single logical unit of work.
    Transactions ensure that either all operations are successfully completed, or none are. This is known as atomicity. Transactions also ensure consistency, isolation, and durability (ACID properties). Transactions are typically used to maintain data integrity in complex operations that involve multiple tables.

🔒 Security

  • SQL provides various mechanisms for securing data, including user authentication, access control, and encryption.
    User authentication verifies the identity of users, while access control restricts users to only the data they are authorized to access. Encryption protects data from unauthorized access, both in transit and at rest. SQL also provides mechanisms for auditing database activity.

⚙ Stored Procedures

  • Stored procedures are precompiled SQL code that can be executed by name.
    They provide a way to encapsulate complex logic and improve performance. Stored procedures can accept parameters and return values. They are typically used to implement business rules and automate common tasks. Stored procedures can also improve security by reducing the need to expose underlying tables.

🚥 Triggers

  • Triggers are SQL code that automatically executes in response to certain events on a particular table.
    For example, a trigger can be executed when a row is inserted, updated, or deleted.