Create table customer
(custid int constraint ct_id primary key,
cust_name nvarchar(100),
cust_addr1 nvarchar(100),
cust_addr2 nvarchar(100),
city,
cust_zip number(5),
cust_plus4 number(4))
Create table Orders
(OrderNbr int constraint ord_id primary key,
custid int,
Order_date datetime,
OrderQty int,
RecentOrders Nvarchar(10),
Constraint fk_custid foreign key (custid) references customer (custid))
Insert into customer(cust_name,cust_addr1,cust_addr2,cust_zip,cust_plus4)
Values(‘Mike Smith’,111 Smith Rd,,61111,1111),
(‘Nicole Smith’,143 Nick Lane,,61112,1101),
(‘Jose Gomez’,155 New York St, 55555,2323)
Insert into Orders(custid,order_date,orderQty,RecentOrders)
Values(101,’11-22-2017’,20,1),
(101,’11-23-2017’,11,2),
(101,’11-24-2017’,5,’3),
(101,’12-02-2017’,25,4),
(101,’01-09-2018’,2,5),
(102,’09-02-2017’,55,1),
(102,’11-05-2017,6,2),
(103,’01-20-2016’,1,1),
(103,’11-10-2017’,13,2),
(103,’01-12-2018’,25,3)
Output should look like this.
OrderNbr,custid,order_date,orderQty,RecentOrders
1,101,11-22-2017,20,1,3
2,101,11-23-2017,11,2,null
3,101,11-24-2017,5,’3,null
4,101,12-02-2017,25,4,null
5,101,01-09-2018,2,5,null
1,102,09-02-2017,55,1,none
2,102,11-05-2017,6,2,null
1,103,01-20-2016,1,1,3
2,103,11-10-2017,13,2,null
3,103,01-12-2018,25,3,null