Advance-SQL-1 (1)

ADVANCE SQL

  • Advanced SQL techniques go beyond basic queries to handle complex operations, optimize performance, and manipulate data efficiently.
  • This guide covers advanced SQL concepts including:
    • SQL JOINS
    • SQL OPERATORS
    • Other advanced SQL functions.

SQL JOIN

  • The SQL JOIN statement combines rows from two tables based on a common column.
  • It selects records that have matching values in these columns.
    • Example:
      sql SELECT Customers.customer_id, Customers.first_name, Orders.item FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id;     
Result Set Description
  • The command selects:
    • customer_id and first_name from the Customers table.
    • item amount from the Orders table.
  • Resulting values will be those matching the customer_id from both tables.

Joining Multiple Tables

  • SQL supports joining multiple tables:
    • Example:sql SELECT Customers.first_name, Orders.item, Shippings.status FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Shippings ON Customers.customer_id = Shippings.customer;     

Types of Joins

INNER JOIN
  • Joins two tables based on a common column, selecting matching rows.
    • Example:sql SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer;     
LEFT JOIN
  • Combines two tables, returning all records from the left table and matched records from the right.
    • Example:sql SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer;     
RIGHT JOIN
  • Returns all records from the right table and matched records from the left.
    • Example:sql SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers RIGHT JOIN Orders ON Customers.customer_id = Orders.customer;     
FULL OUTER JOIN
  • Combines records from both tables, returning all matching values and unmatched rows.
    • Example:sql SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer;     

SQL Operators

  • Operators are symbols/keywords used to perform operations with values.
  • They can be categorized as:
    • Arithmetic Operators: Used for mathematical operations.
    • Comparison Operators: Compare two values.
    • Logical Operators: Combine multiple SQL statements.
Arithmetic Operators
Description
  • Perform simple arithmetic operations:
    • Addition (+)
    • Subtraction (-)
    • Multiplication (*)
    • Division (/)
    • Modulo (%)
Example Queries

  • Addition Example:sql SELECT item, amount, amount+100 AS total_amount FROM Orders;     

  • Subtraction Example:sql SELECT item, amount, amount-20 AS offer_price FROM Orders;     

  • Multiplication Example:sql SELECT item, amount, amount*4 AS total_amount FROM Orders;     

  • Division Example:sql SELECT item, amount, amount/2 AS half_amount FROM Orders;     

Comparison Operators
  • Compare two values returning true (1) or false (0).
Operators:
  • Equal to (=)
    • Example: Querying orders of a specific customer: sql SELECT order_id, item, amount FROM Orders WHERE customer_id = 4;     
  • Less Than (<)
    • Example: Finding items below a price: sql SELECT order_id, item, amount FROM Orders WHERE amount < 400; &nbsp;&nbsp;&nbsp;&nbsp;
  • Less Than or Equal To (<=)
    • Example:sql SELECT order_id, item, amount FROM Orders WHERE amount <= 400; &nbsp;&nbsp;&nbsp;&nbsp;
  • Greater Than Or Equal To (>=)
    • Example:sql SELECT order_id, item, amount FROM Orders WHERE amount >= 400; &nbsp;&nbsp;&nbsp;&nbsp;
  • Not Equal To (!=)
    • Example:sql SELECT order_id, item, amount FROM Orders WHERE amount != 400; &nbsp;&nbsp;&nbsp;&nbsp;
Logical Operators
  • Used to compare multiple SQL commands.
  • Return true (1) or false (0).
Available Operators:
  • ANY
  • ALL
  • AND
  • OR
  • NOT
  • BETWEEN
  • EXISTS
  • IN
  • LIKE
  • IS NULL

SQL EXISTS Operator

  • Tests existence of values in a subquery.
  • Executes outer query if subquery result is not NULL.
    • Example:sql SELECT customer_id, first_name FROM Customers WHERE EXISTS (SELECT order_id FROM Orders WHERE Orders.customer_id = Customers.customer_id); &nbsp;&nbsp;&nbsp;&nbsp;

IN and NOT IN Operators

  • Used to specify multiple values in a WHERE clause.
    • Example: Finding customers from specific countries: sql SELECT first_name, country FROM Customers WHERE country IN ('USA', 'UK'); &nbsp;&nbsp;&nbsp;&nbsp;

LIKE and NOT LIKE Operators

  • Used for pattern matching within string data.
    • Example: Selecting customers from a specific country: sql SELECT * FROM Customers WHERE country LIKE 'UK'; &nbsp;&nbsp;&nbsp;&nbsp;

IS NULL and IS NOT NULL

  • Used to check if a column contains NULL values.