TableCreationScript

USE CITI
drop table USERS
drop table SECURITIES

CREATE TABLE USERS(
	USERID INT IDENTITY(1, 1) CONSTRAINT CHECK_USER_ID_NOT_NULL NOT NULL CONSTRAINT USER_ID_PK PRIMARY KEY,
	USERNAME VARCHAR(20) UNIQUE CONSTRAINT CHECK_USERNAME_NOT_NULL NOT NULL,
	PASSCODE VARCHAR(20) CONSTRAINT CHECK_PASSWORD_NOT_NULL NOT NULL
	);

	select * from USERS

CREATE TABLE SECURITIES(
	SECID INT CONSTRAINT CHECK_SECID_NOT_NULL NOT NULL CONSTRAINT SECID_PK PRIMARY KEY,
	SECURITY_NAME VARCHAR(50) CONSTRAINT CHECK_NAME_NOT_NULL NOT NULL,
	ISSUER_NAME VARCHAR(50) CONSTRAINT CHECK_ISSUER_NOT_NULL NOT NULL,
	FACE_VALUE FLOAT CONSTRAINT CHECK_FACE_NOT_NULL NOT NULL,
	COUPON_RATE FLOAT CONSTRAINT CHECK_COUPON_NULL NULL,
	FREQUENCY INT CONSTRAINT CHECK_FREQUENCY_NOT_NULL NOT NULL,
	MATURITY_DATE DATE CONSTRAINT CHECK_MATURITY_NOT_NULL NOT NULL,
	DAY_COUNT_CONVENTION INT CONSTRAINT CHECK_DAY_NOT_NULL NOT NULL,
	--QUOTE_CONVENTION INT CHECK_QUOTE_NOT_NULL NOT NULL,
	--TODO: See if better solution to store multiple dates
	COUPON_DATES VARCHAR(30) CONSTRAINT CHECK_COUPON_NULL NULL,
	ISIN VARCHAR(20) CONSTRAINT CHECK_ISIN_NOT_NULL NOT NULL
	);

	select * from SECURITIES

CREATE TABLE TRADES(
	TRADEID INT IDENTITY(1, 1) CONSTRAINT CHECK_TRADE_ID_NOT_NULL NOT NULL CONSTRAINT TRADE_ID_PK PRIMARY KEY,
	TRADE_DATE DATE CONSTRAINT CHECK_TRADE_DATE_NOT_NULL NOT NULL,
	TRADE_TIME TIME CONSTRAINT CHECK_TRADE_TIME_NOT_NULL NOT NULL,
	TRADE_TYPE VARCHAR(10) CONSTRAINT CHECK_TRADE_TYPE_NOT_NULL NOT NULL,
	TRADE_PRICE VARCHAR(20) CONSTRAINT CHECK_TRADE_PRICE_NOT_NULL NOT NULL,
	COUNTER_PARTY VARCHAR(20) CONSTRAINT CHECK_COUNTER_PARTY_NOT_NULL NOT NULL,
	SETTLEMENT_DATE DATE CONSTRAINT CHECK_SETTLEMENT_DATE_NOT_NULL NOT NULL,
	LAST_COUPON_DATE DATE CONSTRAINT CHECK_LAST_COUPON_DATE_NOT_NULL NOT NULL,
	TICKS FLOAT CONSTRAINT CHECK_TICKS_NOT_NULL NOT NULL,
	DIRTY_PRICE FLOAT CONSTRAINT CHECK_DIRTY_PRICE_NOT_NULL NOT NULL,
	CLEAN_PRICE FLOAT CONSTRAINT CHECK_CLEAN_PRICE_NOT_NULL NOT NULL,
	SECID INT CONSTRAINT CHECK_SECID_NOT_NULL NOT NULL CONSTRAINT SECID_FK REFERENCES SECURITIES(SECID),
	USERID INT CONSTRAINT CHECK_USERID_NOT_NULL NOT NULL CONSTRAINT USERID_FK REFERENCES USERS(USERID),
	);

	select * from TRADES

CREATE TABLE ACCRUEDS(
	ACCID INT IDENTITY(1, 1) CONSTRAINT CHECK_ACC_ID_NOT_NULL NOT NULL CONSTRAINT ACC_ID_PK PRIMARY KEY,
	ACCRUED_INTEREST FLOAT CONSTRAINT CHECK_ACCRUED_INTEREST_NOT_NULL NOT NULL,
	MARKET_PRICE VARCHAR(20) CONSTRAINT CHECK_MARKET_PRICE_NOT_NULL NOT NULL,
	MARKET_YIELD FLOAT CONSTRAINT CHECK_MARKET_YIELD_NOT_NULL NOT NULL,
	TRADEID INT CONSTRAINT CHECK_TRADEID_NOT_NULL NOT NULL CONSTRAINT TRADEID_FK REFERENCES TRADES(TRADEID),
	);

	-- TODO: Remove code below this line
	SELECT*FROM SECURITIES
	SELECT * FROM USERS
	INSERT INTO USERS VALUES('manan','mananjain')

	UPDATE USERS SET USERS.PASSCODE='mahak', USERS.USERNAME='mahak' WHERE USERNAME='manan'

	select * from USERS
	select * from TRADES
	select * from SECURITIES
	select * from ACCRUEDS


	insert into TRADES 
	use CITI
	select * from SECURITIES
	insert into SECURITIES values(123,'US Treasury Bond 12/oct/2040','US Government',1000,4.32,2,'2040-10-12',30,'12/april&12/oct','US1234576EW')