Untitled

create database lab6

create table Customer(
CustID int identity (1,1) primary key,
Name varchar(50) not null,
Age int not null check (Age>=18),
Address varchar(200) null,
Salary decimal(18,2) null,
)

insert into Customer (Name, Age, Address, Salary)
values('Rahim', 32, 'Gulshan', 2000.00),
('Himel', 25, 'Uttara', 8000.00),
('Karim', 22, 'Nikunju', 5000.00),
('Himel', 25, 'Dhanmondi', 8000.00),
('Akash', 27, 'Bonani', 7000.00),
('Priya', 23, 'Dhanmondi', 8000.00);



create table ORDERS(
OrderID int identity (11,1) primary key,
CustomerID int not null foreign key references Customer (CustID),
Date date null,
Amount money null
)

INSERT INTO ORDERS(CustomerID, Date, Amount)
VALUES(1, '2022-03-15', 1200),
(4, '2022-02-17', 1500),
(3, '2022-04-16', 1700),
(5, '2022-02-16', 1500);

select * from ORDERS


SELECT * 
FROM Customer
WHERE CustID IN (SELECT CustID
FROM Customer 
WHERE Salary >4500)


SELECT CustomerID 
FROM ORDERS
WHERE Amount>1200

SELECT Name, Address
FROM Customer
WHERE CustID IN (SELECT CustomerID 
FROM ORDERS
WHERE Amount>1200)

SELECT Customer.Name, Customer.Address
FROM Customer
INNER JOIN ORDERS ON Customer.CustID = ORDERS.CustomerID and  Amount >1200;