SQL

Learn the basics SQL

What is SQL?

SQL, or Structured Query Language, is a standard programming language specifically designed for managing and manipulating relational databases. They use a structure that allows data to be organized into tables with rows (records).

• Example: A database for a library could store information about books, authors, and borrowers.

What is a table?

A table is a structured format in a database where data is stored. It consists of:

• Columns (fields): Define the structure of the data (like fields or attributes).

• Rows (records or tuple): Represent individual records (like entries or data points).

Think of a table as a spreadsheet:

• Columns are the headers, such as first_name, last_name, and email.

• Rows are the individual entries or data for each person.

Example Table: employees

• Columns: employee_id, first_name, last_name, department, salary

• Rows: Each row contains data about a specific employee.

Key Points

1. Tables are the building blocks of a database:

   • A database can have many tables (e.g., employees, departments, salaries).

   • Tables can be linked using relationships (e.g., foreign keys).

2. Columns define the type of data stored:

   • E.g., employee_id is an integer, first_name is text, and salary is a numeric value.

3. Rows contain the actual data:

   • Each row in a table represents one record.

What Can SQL do?

• SQL can execute queries against a database

• SQL can retrieve data from a database

• SQL can insert records in a database

• SQL can update records in a database

• SQL can delete records from a database

• SQL can create new databases

• SQL can create new tables in a database

• SQL can create stored procedures in a database

• SQL can create views in a database

• SQL can set permissions on tables, procedures, and views

What Are Relational Databases?

Relational databases are collections of data organized into tables, which consist of rows and columns, allowing for efficient data retrieval and management through relationships defined among them.

RDBMS Benefits and Limitations

Here are some of the benefits of using an RDBMS:

• Structured Data: RDBMS allows data storage in a structured way, using rows and columns in tables. This makes it easy to manipulate the data using SQL (Structured Query Language), ensuring efficient and flexible usage.
  

• ACID Properties: ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable and safe data manipulation in a RDBMS, making it suitable for mission-critical applications.

• Normalization: RDBMS supports data normalization, a process that organizes data in a way that reduces data redundancy and improves data integrity.
  

• Scalability: RDBMSs generally provide good scalability options, allowing for the addition of more storage or computational resources as the data and workload grow.
  

• Data Integrity: RDBMS provides mechanisms like constraints, primary keys, and foreign keys to enforce data integrity and consistency, ensuring that the data is accurate and reliable.
  

• Security: RDBMSs offer various security features such as user authentication, access control, and data encryption to protect sensitive data

Here are some of the limitations of using an RDBMS:

• Complexity: Setting up and managing an RDBMS can be complex, especially for large applications. It requires technical knowledge and skills to manage, tune, and optimize the database.
  

• Cost: RDBMSs can be expensive, both in terms of licensing fees and the computational and storage resources they require.
  

• Fixed Schema: RDBMS follows a rigid schema for data organization, which means any changes to the schema can be time-consuming and complicated.
  

• Handling of Unstructured Data: RDBMSs are not suitable for handling unstructured data like multimedia files, social media posts, and sensor data, as their relational structure is optimized for structured data.
  

• Horizontal Scalability: RDBMSs are not as easily horizontally scalable as NoSQL databases. Scaling horizontally, which involves adding more machines to the system, can be challenging in terms of cost and complexity.

SQL vs NoSQL

SQL (relational) and NoSQL (non-relational) databases represent two different approaches to data storage and retrieval. SQL databases use structured schemas and tables, emphasizing data integrity and complex queries through joins. NoSQL databases offer more flexibility in data structures, often sacrificing some consistency for scalability and performance. The choice between SQL and NoSQL depends on factors like data structure, scalability needs, consistency requirements, and the nature of the application.

Learn the Basic SQL Syntax

Data Types

Data types in SQL specify the type of data a column can hold. They ensure the data stored in a table is consistent and predictable.

Common SQL Data Types

1. INTEGER (or INT)

   • Used to store whole numbers (positive or negative).

   • Example: 1, 42, -99.

   • Usage: Storing IDs, counts, or numerical data without decimals.

   • Example Column: age INTEGER
     

2. VARCHAR (Variable Character)

   • Used to store text of varying length, up to a defined maximum.

   • Example: 'John', 'Hello, World!'

   • Usage: Names, emails, or any text data with a defined max size.

   • Example Column: email VARCHAR(255)
     

3. TEXT

   • Used to store large amounts of text without a predefined length limit.

   • Example: 'This is a long description...'

   • Usage: Storing descriptions, comments, or notes.

   • Example Column: description TEXT
     

4. DATE

   • Used to store calendar dates in YYYY-MM-DD format.

   • Example: '2025-01-04'

   • Usage: Storing dates of birth, order dates, or event dates.

   • Example Column: date_of_birth DATE
     

5. BOOLEAN

   • Used to store true or false values.

   • Example: TRUE, FALSE

   • Usage: Flags for conditions, such as whether a user is active.

   • Example Column: is_active BOOLEAN

Other Common Data Types

• DECIMAL (or NUMERIC): Stores precise numbers with decimal points.

  • Example: 10.99, -3.14

  • Usage: Storing prices or financial data.

  • Example Column: price DECIMAL(10, 2) (10 digits, 2 after the decimal point)
    

• FLOAT (or REAL): Stores approximate numbers with decimals.

  • Example: 3.14, -0.001
    

• TIME: Stores time values in HH:MM:SS format.

  • Example: '14:30:00'
    

• DATETIME: Combines date and time in YYYY-MM-DD HH:MM:SS format.

  • Example: '2025-01-04 14:30:00'

SQL Keywords / Statements / Operators

SQL keywords are reserved words in the SQL language that perform specific operations on a database, such as retrieving, updating, or modifying data. These keywords are case-insensitive (e.g., SELECT is the same as select), but by convention, they are written in uppercase.

Common SQL Keywords: These include commands (like SELECT, INSERT, UPDATE), clauses (such as WHERE, GROUP BY, HAVING), and other syntax elements that form the structure of SQL queries.

SQL Statements

1. SELECT

• Retrieves data from a database.

• Example:

  sql

  Copiar código

  SELECT first_name, last_name FROM employees;

2. FROM

• Specifies the table to retrieve data from.

• Example:

  sql

  Copiar código

  SELECT * FROM products;

3. WHERE

• Filters records based on a condition.

• Example:

  sql

  Copiar código

  SELECT * FROM orders WHERE total > 100;

4. INSERT INTO

• Adds new records to a table.

• Example:

  sql

  Copiar código

  INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com');

5. UPDATE

• Modifies existing records in a table.

• Example:

  sql

  Copiar código

  UPDATE products SET price = 19.99 WHERE product_id = 1;

6. DELETE

• Removes records from a table.

• Example:

  sql

  Copiar código

  DELETE FROM orders WHERE status = 'Cancelled';

7. CREATE TABLE

• Creates a new table in the database.

• Example:

  sql

  Copiar código

  CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) );

8. ALTER TABLE

• Modifies an existing table’s structure (e.g., adding columns).

• Example:

  sql

  Copiar código

  ALTER TABLE customers ADD phone_number VARCHAR(15);

9. DROP TABLE

• Deletes a table from the database.

• Example:

  sql

  Copiar código

  DROP TABLE customers;

10. JOIN

• Combines rows from two or more tables based on a related column.

• Example:

  sql

  Copiar código

  SELECT orders.order_id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

11. GROUP BY

• Groups rows with the same values in specified columns and performs aggregate functions.

• Example:

  sql

  Copiar código

  SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department;

12. ORDER BY

• Sorts results by one or more columns (ascending or descending).

• Example:

  sql

  Copiar código

  SELECT * FROM products ORDER BY price DESC;

13. LIMIT

• Limits the number of rows returned by a query.

• Example:

  sql

  Copiar código

  SELECT * FROM customers LIMIT 10;

14. DISTINCT

• Returns unique values in the result set.

• Example:

  sql

  Copiar código

  SELECT DISTINCT department FROM employees;

15. HAVING

• Filters groups based on a condition, often used with GROUP BY.

• Example:

  sql

  Copiar código

  SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

Key Points

• SQL keywords form the building blocks of queries.

• They must be used in the correct syntax order (e.g., SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY).

Operators

SQL operators are symbols or keywords used to perform operations on data in a SQL query. They are used in conditions within queries, such as filtering rows or combining multiple conditions.

Types of SQL Operators

1. Arithmetic Operators

2. Comparison Operators

Used to compare two values.

3. Logical Operators

Used to combine multiple conditions

4. SQL LIKE Operator

Filters rows based on pattern matching.

5. IS NULL and IS NOT NULL

Checks for NULL (missing) values.

SELECT * FROM orders WHERE delivery_date IS NULL;

SELECT * FROM orders WHERE delivery_date IS NOT NULL;

6. SQL EXISTS Operator

Checks if a subquery returns any rows.

SELECT * FROM customers WHERE EXISTS (
    SELECT 1 FROM orders WHERE customers.customer_id = orders.customer_id
);

7. SQL CONCAT Operator

Used to combine strings.

SELECT first_name || ' ' || last_name AS full_name FROM employees;

Key Points

• Operators are essential for filtering and manipulating data in SQL.

• They work with various SQL clauses like WHERE, HAVING, and SELECT.

• Operators are essential for filtering and manipulating data in SQL.

• They work with various SQL clauses like WHERE, HAVING, and SELECT.

1. SELECT

• Retrieves data from a database.

• Example:

SELECT first_name, last_name FROM employees;

2. FROM

• Specifies the table to retrieve data from.

• Example:

SELECT * FROM products;

3. WHERE

• Filters records based on a condition.

• Example:

SELECT * FROM orders WHERE total > 100;

4. INSERT INTO

• Adds new records to a table.

• Example:

INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com');

5. UPDATE

• Modifies existing records in a table.

• Example:

UPDATE products SET price = 19.99 WHERE product_id = 1;

6. DELETE

• Removes records from a table.

• Example:

DELETE FROM orders WHERE status = 'Cancelled';

7. CREATE TABLE

• Creates a new table in the database.

• Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

8. ALTER TABLE

• Modifies an existing table’s structure (e.g., adding columns).

• Example:

  ALTER TABLE customers ADD phone_number VARCHAR(15);

9. DROP TABLE

• Deletes a table from the database.

• Example:

DROP TABLE customers;

10. JOIN

• Combines rows from two or more tables based on a related column.

• Example:

SELECT orders.order_id, customers.name 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.customer_id;

11. GROUP BY

• Groups rows with the same values in specified columns and performs aggregate functions.

• Example:

SELECT department, COUNT(*) AS employee_count 
FROM employees 
GROUP BY department;

12. ORDER BY

• Sorts results by one or more columns (ascending or descending).

• Example:

SELECT * FROM products ORDER BY price DESC;

13. LIMIT

• Limits the number of rows returned by a query.

• Example:

SELECT * FROM customers LIMIT 10;

14. DISTINCT

• Returns unique values in the result set.

• Example:

SELECT DISTINCT department FROM employees;

15. HAVING

• Filters groups based on a condition, often used with GROUP BY.

• Example:

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

• SQL keywords form the building blocks of queries.

• They must be used in the correct syntax order (e.g., SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY).

Data Definition Language (DDL)

Data Definition Language (DDL) is a subset of SQL used to define and manage the structure of a database. Think of it as the blueprint for your database, outlining the tables, columns, and their relationships.

Here's a breakdown of key DDL commands:

• CREATE:

  • Purpose: Used to create new database objects.

  • Examples:

    • CREATE TABLE Customers (CustomerID INT, CustomerName VARCHAR(255));

    • CREATE DATABASE MyDatabase;

    • CREATE INDEX idx_CustomerName ON Customers (CustomerName);

• ALTER:

  • Purpose: Modifies the structure of existing database objects.

  • Examples:

    • ALTER TABLE Customers ADD COLUMN City VARCHAR(255);

    • ALTER TABLE Customers MODIFY COLUMN CustomerName VARCHAR(500);

• DROP:

  • Purpose: Deletes existing database objects.

  • Examples:

    • DROP TABLE Customers;

    • DROP DATABASE MyDatabase;

    • DROP INDEX idx_CustomerName;

In essence, DDL allows you to:

• Define the schema: Determine the structure of your tables, including columns, data types, and constraints.

• Modify the schema: Adjust the structure as your data needs evolve.

• Remove database objects: Delete tables, databases, or other objects that are no longer needed.

By effectively using DDL, you can ensure that your database has a well-defined and optimized structure, which is crucial for efficient data storage, retrieval, and management.