sql exercise

CREATE DATABASE BANK_SYSTEM
ON 
( 
    NAME = bank_dat,
    FILENAME = 'F:\bank_dat.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
)
LOG ON
( 
    NAME = bank_log,
    FILENAME = 'F:\bank_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB 
);

GO

USE BANK_SYSTEM

GO



CREATE TABLE customer (
    customer_name char(20),
    customer_street char(20) NOT NULL,
    customer_city char(20),
    CONSTRAINT primary_key1 PRIMARY KEY ( customer_name )
);

CREATE TABLE branch (
    branch_name char(20),
    branch_city char(20),
    assets int,
    PRIMARY KEY ( branch_name )
);

CREATE TABLE account (
    account_number char(20),
    branch_name char(20) NOT NULL,
    balance int,
    PRIMARY KEY ( account_number ),
    FOREIGN KEY ( branch_name ) REFERENCES branch( branch_name ),
    CONSTRAINT chk_balance CHECK ( balance >= 0 )
);

CREATE TABLE depositor ( 
    customer_name char(20),
    account_number char(20),
    FOREIGN KEY ( customer_name ) REFERENCES customer( customer_name ),
    FOREIGN KEY ( account_number ) REFERENCES account( account_number )
);

CREATE TABLE loan (
    loan_number char(20),
    branch_name char(20),
    amount int,
    PRIMARY KEY ( loan_number ),
    FOREIGN KEY ( branch_name ) REFERENCES branch( branch_name )
)

CREATE TABLE borrower (
    customer_name char(20),
    loan_number char(20),
    FOREIGN KEY ( customer_name ) REFERENCES customer( customer_name ),
    FOREIGN KEY ( loan_number ) REFERENCES loan( loan_number )
);


/* initialize the bank-system-data base */
/* the referenced table should be dropped after dropping the referencing table if neccessary
drop table customer;
drop table branch;
drop table depositor;
drop table account;
drop table loan;
drop table  borrower;
*/
--------------------------------------------------------------------------
INSERT INTO customer VALUES ('Adams','Spring','Pittsfied');
INSERT INTO customer VALUES ('Brooks','Senator','Brooklyn');
INSERT INTO customer VALUES ('Curry','North','Rye');
INSERT INTO customer VALUES ('Glenn','Sand Hill','Woodside');
INSERT INTO customer VALUES ('Green','Walnut','Stamford');
INSERT INTO customer VALUES ('Hayes','Main','Harrison');
INSERT INTO customer VALUES ('Johnson','Alma','Palo Alto');
INSERT INTO customer VALUES ('Jones','Main','Harrison');
INSERT INTO customer VALUES ('Lindsay','Park','Pittsfied');
INSERT INTO customer VALUES ('Smith','North','Rye');
INSERT INTO customer VALUES ('Turner','Putnam','Stamford');
INSERT INTO customer VALUES ('Williams','Nassau','Princeton');
--------------------------------------------------------------------------
INSERT INTO branch VALUES ('Brighton','Brooklyn',7100000);
INSERT INTO branch VALUES ('Downtown','Brooklyn',9000000);
INSERT INTO branch VALUES ('Mianus','Horseneck',400000);
INSERT INTO branch VALUES ('North Town','Rye',3700000);
INSERT INTO branch VALUES ('Perryridge','Horseneck',1700000);
INSERT INTO branch VALUES ('Pownal','Bennington',300000);
INSERT INTO branch VALUES ('Redwood','Palo Alto',2100000);
INSERT INTO branch VALUES ('Round Hill','Horseneck',8000000);
--------------------------------------------------------------------------
INSERT INTO account VALUES ('A-101','Downtown',500);
INSERT INTO account VALUES ('A-102','Perryridge',400);
INSERT INTO account VALUES ('A-201','Brighton',900);
INSERT INTO account VALUES ('A-215','Mianus',700);
INSERT INTO account VALUES ('A-217','Brighton',750);
INSERT INTO account VALUES ('A-222','Redwood',700);
INSERT INTO account VALUES ('A-305','Round Hill',350);
--------------------------------------------------------------------------
INSERT INTO depositor VALUES ('Hayes','A-102');
INSERT INTO depositor VALUES ('Johnson','A-101');
INSERT INTO depositor VALUES ('Johnson','A-201');
INSERT INTO depositor VALUES ('Jones','A-217');
INSERT INTO depositor VALUES ('Lindsay','A-222');
INSERT INTO depositor VALUES ('Smith','A-215');
INSERT INTO depositor VALUES ('Turner','A-305');
--------------------------------------------------------------------------
INSERT INTO loan VALUES ('L-11','Round Hill',900);
INSERT INTO loan VALUES ('L-14','Downtown',1500);
INSERT INTO loan VALUES ('L-15','Perryridge',1500);
INSERT INTO loan VALUES ('L-16','Perryridge',1300);
INSERT INTO loan VALUES ('L-17','Downtown',1000);
INSERT INTO loan VALUES ('L-23','Redwood',2000);
INSERT INTO loan VALUES ('L-93','Mianus',500);
--------------------------------------------------------------------------
INSERT INTO borrower VALUES ('Adams','L-16');
INSERT INTO borrower VALUES ('Curry','L-93');
INSERT INTO borrower VALUES ('Hayes','L-15');
INSERT INTO borrower VALUES ('Johnson','L-14');
INSERT INTO borrower VALUES ('Jones','L-17');
INSERT INTO borrower VALUES ('Smith','L-11');
INSERT INTO borrower VALUES ('Smith','L-23');
INSERT INTO borrower VALUES ('Williams','L-17');
--------------------------------------------------------------------------


/* query part */
SELECT *
FROM customer;

SELECT *
FROM branch;

SELECT *
FROM account;

SELECT *
FROM depositor;

SELECT *
FROM loan;

SELECT *
FROM borrower;

SELECT avg( balance )
FROM account;

--find all customers who have an account at all branches located in Brooklyn
SELECT customer_name 
FROM customer 
WHERE EXISTS( SELECT * FROM depositor
                WHERE NOT EXISTS ( SELECT * FROM branch
                    WHERE branch.branch_city = 'Brooklyn' AND
                        NOT EXISTS ( SELECT * FROM depositor 
                            WHERE customer.customer_name = depositor.customer_name AND
                                EXISTS ( SELECT * FROM account 
                                    WHERE account.account_number = depositor.account_number AND
                                        account.branch_name = branch.branch_name ))));



SELECT DISTINCT customer_name 
FROM depositor AS y
WHERE NOT EXISTS ( SELECT * FROM branch 
                   WHERE branch.branch_city = 'Brooklyn' AND 
                        NOT EXISTS ( SELECT * FROM depositor AS x
                                     WHERE y.customer_name = x.customer_name AND 
                                            EXISTS ( SELECT * FROM account 
                                                     WHERE account.account_number = x.account_number AND
                                                               account.branch_name = branch.branch_name )));

--find the names of all branches that have greater assets than some branches located in Brooklyn
SELECT a.branch_name
FROM branch AS a,branch AS b
WHERE b.branch_city = 'Brooklyn' AND a.assets > b.assets;

SELECT branch_name 
FROM branch
WHERE assets > SOME ( SELECT assets
                      FROM branch
                      WHERE branch_city = 'Brooklyn' );

--find all customers who have a loan,an account,or both------------------------------------
( SELECT customer_name FROM depositor )
UNION 
( SELECT customer_name FROM borrower );

--find the number of depositor of each branch----------------------------------------------
SELECT branch_name,count(DISTINCT customer_name)
FROM depositor,account
WHERE depositor.account_number = account.account_number
GROUP BY branch_name;

--find the branch that has the highest average balance ------------------------------------
SELECT branch_name 
FROM account 
GROUP BY branch_name 
    HAVING avg( balance ) >= ALL( SELECT avg( balance )
                                  FROM account 
                                  GROUP BY branch_name );
--find the largest account balance---------------------------------------------------------
SELECT balance 
FROM account AS x
WHERE NOT EXISTS ( SELECT * FROM account AS y 
                        WHERE x.balance < y.balance );

SELECT balance 
FROM account
WHERE balance = ( SELECT max( balance )
                  FROM account );

--a view consisting of branches and their customers----------------------------------------
CREATE VIEW all_customer AS
(
    SELECT branch_name,customer_name
    FROM depositor,account
    WHERE depositor.account_number = account.account_number
)
UNION 
(
    SELECT branch_name,customer_name
    FROM borrower,loan
    WHERE borrower.loan_number = loan.loan_number
);

--find all customers of Perryridge branch--------------------------------------------------
SELECT customer_name 
FROM all_customer
WHERE branch_name = 'Perryridge';

--find the names of all branches where the average account balance is more than $1200
SELECT branch_name,avg( balance )
FROM account
GROUP BY branch_name
    HAVING avg( balance ) > 700;

CREATE VIEW loan_info AS
    SELECT loan_number,branch_name
    FROM loan
    WHERE branch_name = 'Perryridge'
    WITH CHECK OPTION;

INSERT INTO loan_info VALUES ('L-222','Redwood');    --无法执行

GRANT select 
ON all_customer
TO u1
WITH GRANT OPTION;

--find all branches where the total account deposit is less than the average total account deposit at all branches
SELECT branch_name
FROM account
GROUP BY branch_name 
    HAVING sum( balance ) < ( SELECT avg( sum_balance )
                              FROM     ( SELECT sum( balance )
                                       FROM account
                                       GROUP BY branch_name ) AS branch_tot(sum_balance) );

SELECT branch_name,tot_balance
FROM ( SELECT branch_name,sum( balance )
       FROM account
       GROUP BY branch_name ) AS branch_tot(branch_name,tot_balance) 
WHERE tot_balance < ( SELECT avg( tot_balance ) 
                      FROM ( SELECT branch_name,sum( balance ) 
                             FROM account 
                             GROUP BY branch_name ) AS branch_tot(branch_name,tot_balance) 
);

WITH branch_tot(branch_name,tot_balance) 
AS (
    SELECT branch_name,sum( balance )
    FROM account
    GROUP BY branch_name )
SELECT branch_name 
FROM branch_tot
WHERE tot_balance < ( SELECT avg( tot_balance ) 
                      FROM branch_tot );