CREATE DATABASE [CUSTOMER]
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
	NAME = N'CUSTOMER_sales', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER\CUSTOMER_SYSTEM_DATA.mdf' ,  -- this is for system tables
	SIZE = 1000KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 650KB 
),
 FILEGROUP [CUSTOMER_DATA] 
( 
	NAME = N'PRODUCT_DATA1', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER\CUSTOMER_DATA1.ndf' , -- this is for database tables
	SIZE = 1000KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 650KB 
),
( 
	NAME = N'PRODUCT_DATA2', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER\CUSTOMER_DATA2.ndf' , -- this is for database tables
	SIZE = 1000KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 650KB 
)
 LOG ON 
( 
	NAME = N'CUSTOMER_sales_log', 
	FILENAME = N'D:\SQL2019DATAI1\CUSTOMER\CUSTOMER_sales_log.ldf' , -- this is for database logs
	SIZE = 1000KB ,
	 MAXSIZE = UNLIMITED, 
	 FILEGROWTH = 650KB 
)
GO

DROP SCHEMA IF EXISTS MARKETING
GO

CREATE SCHEMA MARKETING
GO

CREATE TABLE [MARKETING].[CUSTOMER_DEMOGRAPHICS](
	[CUST_ID] [NVARCHAR](32) NOT NULL,
	[CUST_CREDIT_RATING] [SMALLINT] NULL,
	[CUST_MARITAL_STATUS] [NVARCHAR](50) NULL
) ON [CUSTOMER_DATA]
GO

/****************************/
/* POPULATE TABLE WITH SSMS */
/****************************/

/******/
/* OR */
/******/

INSERT INTO [MARKETING].[CUSTOMER_DEMOGRAPHICS]
SELECT 
	[CUST_ID],
	CONVERT(SMALLINT,RIGHT([CUST_ID],1)),
	CASE 
		WHEN RAND() < .5 THEN 'M'
		WHEN RAND() > .5 THEN 'S'
	ELSE 'M'
	END AS [CUST_MARITAL_STATUS]
FROM [MASTER_DATA].[CUSTOMER]
GO

SELECT * FROM [dbo].[CUSTOMER_DEMOGRAPHICS]
GO