(DbLab4Online)

CREATE DATABASE lab_6;
USE lab_6;

CREATE TABLE customer
(
cus_id VARCHAR(5) PRIMARY KEY,
cus_name VARCHAR(20) UNIQUE,
city VARCHAR(10) NOT NULL,
street VARCHAR(20) NOT NULL
);
CREATE TABLE account
(
acc_id VARCHAR(5) PRIMARY KEY,
branch VARCHAR(10) NOT NULL,
balance FLOAT DEFAULT 500 NOT NULL,
CHECK (branch IN('dhanmondi','gulshan','mohakhali','agrabad'))
);

INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('1','alvi','dhaka','dhanmondi');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('2','khan','dhaka','gulshan');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('3','al','dhaka','mohakhali');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('4','billah','dhaka','agrabad');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('5','noor','dhaka','dhanmondi');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('6','maruf','dhaka','agrabad');
INSERT INTO customer(cus_id,cus_name,city,street)
VALUES ('7','zafir','dhaka','gulshan');

INSERT INTO account(acc_id,branch,balance)
VALUES ('1','agrabad','1000');
INSERT INTO account(acc_id,branch,balance)
VALUES ('2','agrabad','2000');
INSERT INTO account(acc_id,branch,balance)
VALUES ('3','agrabad','3000');
INSERT INTO account(acc_id,branch,balance)
VALUES ('4','dhanmondi','35000');
INSERT INTO account(acc_id,branch,balance)
VALUES ('5','gulshan','40000');
INSERT INTO account(acc_id,branch,balance)
VALUES ('6','agrabad','500');
INSERT INTO account(acc_id,branch,balance)
VALUES ('7','agrabad','9000');

SELECT account.acc_id,account.balance
FROM account
WHERE branch='dhanmondi' OR branch = 'agrabad';

SELECT account.acc_id
FROM account
WHERE balance BETWEEN 1000 AND 35000;

SELECT customer.cus_id,customer.cus_name
FROM customer
WHERE customer.city='dhaka' AND cus_name LIKE '%a%' ;

SELECT top 5 branch
FROM account;

SELECT customer.cus_name
FROM customer
WHERE customer.street != 'agrabad' OR customer.city != 'chittagong';