
--create DataBase
Create Database OrderManagement

-- Create OrderMasters Table

CREATE TABLE [dbo].[OrderMasters](  
[Order_No] [varchar](20) NOT NULL,  
[Table_ID] [varchar](20) NOT NULL,  
[Description] [varchar](200) NOT NULL,  
[Order_DATE] [datetime] NOT NULL,  
[Waiter_Name] [varchar](20) NOT NULL
CONSTRAINT [PK_OrderMasters] PRIMARY KEY CLUSTERED   
(  
   [Order_No] ASC  
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
) ON [PRIMARY]  

-- Insert OrderMasters sample data

INSERT INTO [OrderMasters]  
          ([Order_No],[Table_ID] ,[Description],[Order_DATE],[Waiter_Name])  
    VALUES  
          ('ORD_001','T1','Order for Table T1',GETDATE(),'SHANU' )  
     
INSERT INTO [OrderMasters]  
          ([Order_No],[Table_ID] ,[Description],[Order_DATE],[Waiter_Name])  
    VALUES  
           ('ORD_002','T2','Order for Table T2',GETDATE(),'Afraz' )  
             
INSERT INTO [OrderMasters]  
          ([Order_No],[Table_ID] ,[Description],[Order_DATE],[Waiter_Name])  
     VALUES  
             ('ORD_003','T3','Order for Table T3',GETDATE(),'Afreen')  
             
             
CREATE TABLE [dbo].[OrderDetails](  
  [Order_Detail_No] [varchar](20) NOT NULL,  
 [Order_No] [varchar](20) CONSTRAINT  fk_OrderMasters FOREIGN KEY REFERENCES OrderMasters(Order_No),  
 [Item_Name] [varchar](20) NOT NULL,   
 [Notes] [varchar](200) NOT NULL,  
[QTY]  INT NOT NULL,  
 [Price] INT NOT NULL  
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED   
(  
    [Order_Detail_No] ASC  
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
 
--Now let¡¯s insert the 3 items for the above Order No 'Ord_001'.  
INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_001','ORD_001','Ice Cream','Need very Cold',2 ,160)  
 
INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_002','ORD_001','Coffee','Hot and more Suger',1 ,80)  
          
          INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_003','ORD_001','Burger','Spicy',3 ,140)  
          
          INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_004','ORD_002','Pizza','More Chees and Large',1 ,350)  
          
          INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_005','ORD_002','Cola','Need very Cold',3 ,50)  
          
          INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_006','ORD_003','IDLY','Hot',3 ,40)  
          INSERT INTO [OrderDetails]  
          ([Order_Detail_No],[Order_No],[Item_Name],[Notes],[QTY] ,[Price])  
   VALUES  
          ('OR_DT_007','ORD_003','Thosa','Hot',3 ,50)  

-- To Select and test Order Master and Details
Select * FROM OrderMasters

Select * From OrderDetails