USE Assignment_2;
(001, 'Bankim', 'Chaterjee', 'bankim.chaterjee@yahoo.com', 'Dumdum',
'Kolkata', 'West Bengal', 700028),
(002, 'Jose', 'Batista', 'jose.batista@rediffmail.com', 'Retiro', 'Madrid',
'Madrid', 055430),
-- 1. Create an ‘Orders’ table which comprises of these columns: ‘order_id’,
‘order_date’, ‘amount’, ‘customer_id’.
CREATE TABLE Cust_Order
(O_Id INT PRIMARY KEY,
O_Date DATE NOT NULL,
Amount FLOAT NOT NULL,
C_Id INT FOREIGN KEY REFERENCES Customer(C_Id));
-- 2. Insert 5 new records.
INSERT INTO Cust_Order (O_Id, O_Date, Amount, C_Id) VALUES
(101, '2019-11-27', 5000.00, 004),
(102, '2020-08-25', 4567.97, 003),
(103, '1993-01-19', 6342.11, 001),
(104, '1990-10-11', 1125.19, 005),
(105, '2023-08-15', 258.00, 002);
-- 3. Make an inner join on ‘Customer’ and ‘Orders’ tables on the
‘customer_id’ column.
SELECT *
FROM Customer AS C
INNER JOIN
Cust_Order AS CO
ON C.C_Id= CO.C_Id;
-- 4. Make left and right joins on ‘Customer’ and ‘Orders’ tables on
the‘customer_id’ column.
SELECT *
FROM Customer AS C
LEFT JOIN
Cust_Order AS CO
ON C.C_Id= CO.C_Id;
SELECT *
FROM Customer AS C
RIGHT JOIN
Cust_Order AS CO
ON C.C_Id= CO.C_Id;
-- 5. Make a full outer join on ‘Customer’ and ‘Orders’ table on the
‘customer_id’ column.
SELECT *
FROM Customer AS C
FULL JOIN
Cust_Order AS CO
ON C.C_Id= CO.C_Id;
-- 6. Update the ‘Orders’ table and set the amount to be 100
where‘customer_id’
UPDATE Cust_Order
SET Amount= 100
WHERE C_Id= 3;
SELECT * FROM Cust_Order;
