(DBLab6-1-Online)

CREATE DATABASE mydata
USE mydata

CREATE TABLE customer(
cus_id VARCHAR(5),
cus_name VARCHAR(20),
city VARCHAR(10)NULL,
CONSTRAINT pk1 PRIMARY KEY(cus_id),
CONSTRAINT unique_no UNIQUE(cus_name)
);

CREATE TABLE account(
acc_id VARCHAR(5),
cus_id VARCHAR(5) FOREIGN KEY REFERENCES customer(cus_id),
branch VARCHAR(10)NOT NULL,
balance FLOAT DEFAULT 500 NOT NULL,
CONSTRAINT pk2 PRIMARY KEY(acc_id),
CHECK(branch IN('dhanmondi','gulshan','mohakhali')),

);

CREATE TABLE depositor(
acc_id VARCHAR(5),
cus_id VARCHAR(5),
CONSTRAINT fk1 FOREIGN KEY(acc_id)REFERENCES account(acc_id),
CONSTRAINT fk2 FOREIGN KEY(cus_id)REFERENCES customer(cus_id),
open_data datetime NOT NULL,

);

INSERT INTO customer (cus_id,cus_name,city) VALUES ('1','Rahim','Dhaka');
INSERT INTO customer (cus_id,cus_name,city) VALUES ('2','Karim','Dhaka');
INSERT INTO customer (cus_id,cus_name,city) VALUES ('3', 'Hakim', 'Chittagong');
INSERT INTO customer (cus_id,cus_name,city) VALUES ('4', 'kamal', 'Chittagong');

INSERT INTO account (acc_id,branch,balance) VALUES ('1','mohakhali',1000.0);
INSERT INTO account (acc_id,branch,balance) VALUES ('2','dhanmondi',2000.0);
INSERT INTO account (acc_id,branch,balance) VALUES ('3','gulshan',3000.0);
INSERT INTO account (acc_id,branch,balance) VALUES ('4','mohakhali',4000.0);

INSERT INTO depositor(acc_id,cus_id,open_data)VALUES('1','1',201/6/2);
INSERT INTO depositor(acc_id,cus_id,open_data)VALUES('2','2',2016/6/2);
INSERT INTO depositor(acc_id,cus_id,open_data)VALUES('3','3',2016/6/2);
INSERT INTO depositor(acc_id,cus_id,open_data)VALUES('4','4',2017004447/1/1);


SELECT customer.cus_name,account.balance FROM account FULL JOIN customer ON account.cus_id=customer.cus_id ORDER BY account.branch;
SELECT customer.cus_id,account.acc_id FROM account FULL JOIN customer ON account.cus_id=customer.cus_id WHERE customer.cus_name LIKE 'k%';
SELECT AVG(account.balance) AS average ,MAX(account.balance) AS maximum FROM account FULL JOIN customer ON account.cus_id=customer.cus_id WHERE customer.cus_name LIKE '%e%';
SELECT customer.cus_name,customer.city,account.branch FROM (depositor JOIN account ON depositor.cus_id=account.cus_id) JOIN customer ON (account.cus_id=customer.cus_id) WHERE depositor.open_data=2017/1/1;
SELECT * FROM (depositor JOIN account ON depositor.cus_id=account.cus_id) JOIN customer ON (account.cus_id=customer.cus_id);