Advanced Querying Techniques

This page covers several powerful SQL clauses and operators that allow for more complex subqueries and data manipulation: ANY, ALL, EXISTS, SELECT INTO, and INSERT INTO SELECT.

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS ArchivedProducts;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Books;

CREATE TABLE Products (
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
INSERT INTO Products (ProductName, Price)
VALUES ('Laptop', 1200), ('Mouse', 25), ('Keyboard', 75);

CREATE TABLE ArchivedProducts (
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
INSERT INTO ArchivedProducts (ProductName, Price)
VALUES ('Old Mouse', 20), ('Old Keyboard', 50);

CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName VARCHAR(100)
);
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Amount DECIMAL(10, 2)
);
INSERT INTO Orders (OrderID, CustomerID, Amount)
VALUES (101, 2, 50.00), (102, 1, 75.00), (103, 2, 25.00);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(50),
    PublishedYear INTEGER
);
INSERT INTO Books (BookID, Title, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 'Fantasy', 1937),
       (2, '1984', 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 'Fantasy', 1954),
       (4, 'Dune', 'Sci-Fi', 1965);

Database Overview & Sample Data

The database is pre-loaded with the Products, ArchivedProducts, Customers, Orders, and Books tables. Run the queries below to explore the data.

Products Table

SELECT * FROM Products;

ArchivedProducts Table

SELECT * FROM ArchivedProducts;

Customers Table

SELECT * FROM Customers;

Orders Table

SELECT * FROM Orders;

Books Table

SELECT * FROM Books;

The ANY and ALL Operators

ANY Operator

The ANY operator returns TRUE if any subquery values meet the condition.

Operation: Find products whose price is greater than any item in ArchivedProducts (greater than the minimum archived price of 20).

Query:

SELECT ProductName, Price
FROM Products
WHERE Price > ANY (SELECT Price FROM ArchivedProducts);

Result:

ALL Operator

ALL returns TRUE only if the comparison is true for all of the values in the result set.

Operation: Find all current products that are more expensive than all products in the archive (greater than 50).

Query:

SELECT ProductName, Price
FROM Products
WHERE Price > ALL (SELECT Price FROM ArchivedProducts);

Result:

The EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records, otherwise it returns FALSE. EXISTS is often used in correlated subqueries, where the inner query depends on data from the outer query.

Operation: Find the names of all customers who have placed at least one order.

Query:

SELECT CustomerName
FROM Customers
WHERE EXISTS (
    SELECT 1
    FROM Orders
    WHERE Orders.CustomerID = Customers.CustomerID
);

Explanation: This is a correlated subquery. For each Customer row being considered by the outer query, the inner query runs:

1. For Alice (ID 1): Matching order found (OrderID 102) → EXISTS is TRUE.
2. For Bob (ID 2): Matching orders found → EXISTS is TRUE.
3. For Charlie (ID 3): No matching orders → EXISTS is FALSE.

Result:

Copying Data: SELECT INTO and INSERT INTO SELECT

SELECT INTO (Non-Standard SQL)

The SELECT INTO statement copies data from one table into a new table. In standard SQL/SQLite, this is accomplished with CREATE TABLE ... AS SELECT.

Operation: Create a new backup table called Books_Backup containing all books published before 1950.

Query:

DROP TABLE IF EXISTS Books_Backup;
CREATE TABLE Books_Backup AS
SELECT BookID, Title, Genre
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM Books_Backup;

INSERT INTO SELECT (Standard SQL)

The INSERT INTO SELECT statement copies data from one or more tables and inserts it into an existing table.

Operation: Archive all books published before 1950 into an existing ArchivedBooks table.

Setup Archive Table:

DROP TABLE IF EXISTS ArchivedBooks;
CREATE TABLE ArchivedBooks (
    BookID INT,
    Title VARCHAR(255),
    ArchivedDate DATE
);

Query:

INSERT INTO ArchivedBooks (BookID, Title, ArchivedDate)
SELECT BookID, Title, '2025-09-24'
FROM Books
WHERE PublishedYear < 1950;

-- Check the result
SELECT * FROM ArchivedBooks;

Hands-on Exercise

Task: Use EXISTS (or NOT EXISTS) to find all products from the Products table that have never been archived (i.e., their ProductName does not exist in the ArchivedProducts table).

Your Query:

-- Write your query here

Expected Results: