Create tables with constraints example | SQL Query

Create the Tables for the following descriptions using the constraints as specified.

TABLE NAME-- CUSTOMER

COLUMN NAME	CONSTRAINT DESCRIPTION
CUST_NO	-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START WITH LETTER 'C'
NAME	NOT NULL
PHONE_NO
CITY	NOT NULL

CREATE TABLE CUSTOMER

(CUST_NO VARCHAR(5) CONSTRAINT PKCU PRIMARY KEY,

NAME VARCHAR(20) NOT NULL,

PHONE_NO NUMBER(10),

CITY VARCHAR(20) NOT NULL,

CONSTRAINT CHCU CHECK (CUST_NO LIKE 'C____'));

TABLE NAME-- BRANCH

 

COLUMN NAME	CONSTRAINT DESCRIPTION
BRANCH_CODE	-PRIMARY KEY

BRANCH_NAME	NOT NULL
BRANCH_CITY	-ANY METRO CITY OF INDIA – DELHI, MUMBAI, KOLKATA, CHENNAI.

 

CREATE TABLE BRANCH

(BRANCH_CODE VARCHAR(10) CONSTRAINT PKBC PRIMARY KEY,

BRANCH_NAME VARCHAR(20) NOT NULL,

BRANCH_CITY VARCHAR(10),

CONSTRAINT CHBC CHECK(BRANCH_CITY IN('DELHI','MUMBAI','KOLKATA','CHENNAI')));

TABLE NAME-- ACCOUNT

 

COLUMN NAME	CONSTRAINT DESCRIPTION
ACCOUNT_NO	-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START WITH LETTER 'A'
TYPE	-TYPES- 'SB', 'FD', 'CA'
BALANCE	-MUST BE < 10000000
BRANCH_CODE	-FOREIGN KEY, REFERS TABLE BRANCH

 

CREATE TABLE ACCOUNT

(ACCOUNT_NO VARCHAR(5) CONSTRAINT PKAN PRIMARY KEY,

TYPE VARCHAR(2),

CONSTRAINT CHTY CHECK(TYPE IN('SB','FD','CA')),

BALANCE NUMBER(20) CONSTRAINT CHBA CHECK(BALANCE<100000000),

BRANCH_CODE VARCHAR(10),

CONSTRAINT CHAN CHECK(ACCOUNT_NO LIKE 'A____'),

CONSTRAINT FKBC FOREIGN KEY(BRANCH_CODE) REFERENCES BRANCH(BRANCH_CODE));

TABLE NAME-- DEPOSTOR

 

COLUMN NAME	CONSTRAINT DESCRIPTION
CUST_NO	-FOREIGN KEY, REFERS TABLE CUSTOMER -PRIMARY KEY
ACCOUNT_NO	-FOREIGN KEY, REFERS TABLE ACCOUNT -PRIMARY KEY

 

CREATE TABLE DEPOSTOR

(CUST_NO VARCHAR(5) CONSTRAINT FKCN REFERENCES CUSTOMER(CUST_NO),

ACCOUNT_NO VARCHAR(5) CONSTRAINT FKAN REFERENCES ACCOUNT(ACCOUNT_NO),

CONSTRAINT PKCA PRIMARY KEY (CUST_NO,ACCOUNT_NO));

TABLE NAME-- LOAN

 

COLUMN NAME	CONSTRAINT DESCRIPTION
LOAN-NO	-PRIMARY KEY -MUST BE 5 CHARACTER LONG & START WITH LETTER 'L'
CUST_NO	-FOREIGN KEY, REFERS TABLE CUSTOMER
AMOUNT	-MUST BE > 1000
BRANCH_CODE	-FOREIGN KEY, REFERS TABLE BRANCH

 

CREATE TABLE LOAN

(LOAN_NO VARCHAR(5) CONSTRAINT PKLN PRIMARY KEY,

CUST_NO VARCHAR(5) CONSTRAINT FKCN REFERENCES CUSTOMER(CUST_NO),

AMOUNT NUMBER(20) CONSTRAINT CKAM CHECK(AMOUNT>1000),

BRANCH_CODE VARCHAR(10) CONSTRAINT FKBC REFERENCES BRANCH(BRANCH_CODE),

CONSTRAINT CKLN CHECK(LOAN_NO LIKE 'L____'));

TABLE INSTALLMENT

 

COLUMN NAME	CONSTRAINT DESCRIPTION
INST_NO	-PRIMARY KEY -MUST BE<=10
LOAN_NO	-PRIMARY KEY -FOREIGN KEY, REFERS TABLE LOAN
INST_AMOUNT	-NOT NULL

 

CREATE TABLE INSTALLMENT

(INST_NO NUMBER(10) CONSTRAINT CHIN CHECK(INST_NO<=10),

LOAN_NO VARCHAR(10) CONSTRAINT FKLN REFERENCES LOAN(LOAN_NO),

INST_AMOUNT NUMBER(20) NOT NULL;

CONSTRAINT PKIL PRIMARY KEY(INST_NO,LOAN_NO));

• LEARN SQL