SQL---College/Practical3_Table1.sql at main · devpatel-1/SQL---College · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
USE collgePr1;

CREATE TABLE ACCOUNT_Key_24012011080 (
    Acc_no VARCHAR(5) PRIMARY KEY CHECK (Acc_no LIKE 'A%'),
    Name VARCHAR(30) NOT NULL,
    City VARCHAR(20) NOT NULL,
    Balance DECIMAL(10,2) CHECK (Balance >= 500),
    Loan_taken VARCHAR(3) CHECK (Loan_taken IN ('NO','YES'))
);
DESCRIBE ACCOUNT_Key_24012011080;


INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A001', 'Patel Jigar', 'Mehsana', 50000, 'YES');  -- Success
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A002', 'Patel Ramesh', 'Mehsana', 50000, 'YES');  -- Success
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A003', 'Dave Hardik', 'Ahmedabad', 75000, 'NO');  --  Success
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A004', 'Soni Hetal', 'Ahmedabad', 100000, 'NO');  --  Success
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A005', 'Sony Atul', 'Vadodara', 100000, 'YES');  --  Success
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A005', 'Patel Arun', 'Surat', 4000, 'NO');  --  Fail (Acc_no already exists)
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A006', NULL, 'Baroda', 5000, 'NO');  --  Fail (Name cannot be NULL)
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A007', 'Patel Rachit', NULL, 6000, 'NO');  --  Fail (City cannot be NULL)
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A008', 'Patel Vir', 'Mehsana', 400, 'NO');  --  Fail (Balance must be >= 500)
INSERT INTO ACCOUNT_Key_24012011080 (Acc_no, Name, City, Balance, Loan_taken)
VALUES ('A009', 'Patel Vyom', 'Surat', 1000, 'ABC');  -- Fail (Loan_taken must be 'YES' or 'NO')
SELECT * FROM ACCOUNT_Key_24012011080;