SQLTASK-05.sql

USE TASK3;
CREATE TABLE CUSTOMERS (
CUSTOMER_ID INT PRIMARY KEY,
NAME VARCHAR(100),
EMAIL VARCHAR(100),
PHONE VARCHAR(20),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ORDERS (
ORDER_ID INT PRIMARY KEY,
CUSTOMER_ID INT,
TOTAL_AMOUNT DECIMAL (10,2),
STATUS VARCHAR(50),
FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
);

ALTER TABLE ORDERS
ADD ORDER_DATE DATE;

INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME, EMAIL, PHONE, CREATED_AT) VALUES
(1,'ALEX',NULL,NULL,TIMESTAMP('2025-06-30')),
(2,'JULIE',NULL,NULL,TIMESTAMP('2025-06-30')),
(3,'ALICE',NULL,NULL,TIMESTAMP('2025-06-30'));
SELECT * FROM CUSTOMERS;

INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, TOTAL_AMOUNT) VALUES
(101, 1, '250.00'),
(102, 1, '180.00'),
(103, 2, '300.00');
SELECT * FROM ORDERS;

SELECT C.CUSTOMER_ID, C.NAME, O.ORDER_ID, O.TOTAL_AMOUNT
FROM CUSTOMERS C
INNER JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;

SELECT C.CUSTOMER_ID, C.NAME, O.ORDER_ID, O.TOTAL_AMOUNT
FROM CUSTOMERS C
LEFT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;

SELECT C.CUSTOMER_ID, C.NAME, O.ORDER_ID, O.TOTAL_AMOUNT
FROM CUSTOMERS C
RIGHT JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;

SELECT C.CUSTOMER_ID, C.NAME, O.ORDER_ID, O.TOTAL_AMOUNT
FROM CUSTOMERS C 
FULL JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID;