Lab Tutorial 8: SQL Operators

Sample Database:

Assume you have the same sample database as before:

1. books:
   • Columns: book_id, title, author, price, quantity.
2. customers:
   • Columns: customer_id, name, email, phone.
3. orders:
   • Columns: order_id, customer_id, order_date.
4. order_items:
   • Columns: order_item_id, order_id, book_id, quantity, unit_price.

Sample Queries for Operators:

Arithmetic Operators:

Calculate the total price for each order item.
SELECT book_id, quantity, unit_price, quantity * unit_price AS total_price FROM order_items;

Comparison Operators:

Find all books with a price greater than $15.
SELECT * FROM books WHERE price > 15;

Logical Operators:

Find all customers who have placed orders after January 1, 2022, and whose name starts with 'A'.
SELECT * FROM customers WHERE order_date > '2022-01-01' AND name LIKE 'A%';

Concatenation Operator:

Combine name and email columns of customers into a single column.
SELECT CONCAT(name, ' - ', email) AS customer_info FROM customers;

IS NULL Operator:

Find all customers who do not have an email address.
SELECT * FROM customers WHERE email IS NULL;

IN Operator:

Find all books with a price of either $10 or $20.
SELECT * FROM books WHERE price IN (10, 20);

LIKE Operator:

Find all customers whose name starts with 'J'.
SELECT * FROM customers WHERE name LIKE 'J%';