CREATE DATABASE BILLS;
CREATE TABLE BILLS ( NAME CHAR(30) NOT NULL, AMOUNT NUMBER, ACCOUNT_ID NUMBER NOT NULL);
CREATE TABLE BANK_ACCOUNTS ( ACCOUNT_ID NUMBER NOT NULL, TYPE CHAR(30), BALANCE NUMBER, BANK CHAR(30));
CREATE TABLE COMPANY ( NAME CHAR(30) NOT NULL, ADDRESS CHAR(50), CITY CHAR(30), ST CHAR(2));
CREATE VIEW DEBTS AS SELECT * FROM BILLS;
SELECT * FROM DEBTS;
CREATE VIEW CREDITCARD_DEBTS AS SELECT * FROM DEBTS WHERE ACCOUNT_ID = 4;
SELECT * FROM CREDITCARD_DEBTS;
CREATE VIEW COMPANY_INFO AS SELECT NAME, ST FROM COMPANY;
SELECT * FROM COMPANY_INFO;
CREATE VIEW ENVELOPE (COMPANY, MAILING_ADDRESS) AS SELECT NAME, ADDRESS || ' ' || CITY || ', ' || ST FROM COMPANY;
SELECT * FROM ENVELOPE;
SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE, BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
CREATE VIEW BILLS_DUE (NAME, AMOUNT, ACCT_BALANCE, BANK) AS SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE, BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
SELECT * FROM BILLS_DUE WHERE ACCT_BALANCE > 500;
SELECT DISTINCT COMPANY.ST FROM COMPANY GROUP BY COMPANY.ST;
SELECT COMPANY.ST, COUNT(COMPANY.ST) FROM COMPANY GROUP BY COMPANY.ST;
SELECT COMPANY.ST, COUNT(COMPANY.ST), SUM(BILLS.AMOUNT) FROM BILLS, COMPANY WHERE BILLS.NAME = COMPANY.NAME GROUP BY COMPANY.ST;
CREATE VIEW EXAMPLE (ST, TOTAL_BILLS, TOTAL_AMOUNT) AS SELECT COMPANY.ST, COUNT(COMPANY.ST), SUM(BILLS.AMOUNT) FROM BILLS, COMPANY WHERE BILLS.NAME = COMPANY.NAME GROUP BY COMPANY.ST;
SELECT * FROM EXAMPLE;
CREATE VIEW LATE_PAYMENT (NAME, NEW_TOTAL, ACCOUNT_TYPE, ACCOUNT_ID) AS SELECT BILLS.NAME, BILLS.AMOUNT * 1.10, BANK_ACCOUNTS.TYPE, BILLS.ACCOUNT_ID FROM BILLS, BANK_ACCOUNTS WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
SELECT * FROM LATE_PAYMENT;
SELECT * FROM BANK_ACCOUNTS;
CREATE VIEW BANK_CHECK AS SELECT * FROM BANK_ACCOUNTS;
UPDATE BANK_CHECK SET BALANCE = BALANCE * 1.10:
SELECT * FROM BANK_ACCOUNTS;
DELETE FROM BANK_CHECK WHERE BANK = 'Fidelity';
SELECT * FROM BANK_ACCOUNTS;
UPDATE BANK_CHECK SET BALANCE = BALANCE + 500 WHERE BALANCE > 1000; 
SELECT * FROM BANK_ACCOUNTS;
SELECT * FROM BANK_CHECK;
CREATE VIEW BILLS_NAME AS SELECT NAME FROM BILLS;
CREATE VIEW JAPANESE_BILLS (NAME, JPN_AMOUNT) AS SELECT NAME, AMOUNT * 140 FROM BILLS;
SELECT * FROM JAPANESE_BILLS;
CREATE TABLE BILLS1 AS SELECT * FROM BILLS WHERE AMOUNT < 50;
CREATE TABLE BILLS2 (NAME, AMOUNT, ACCOUNT_ID) AS SELECT BILLS.* FROM BILLS, COMPANY WHERE BILLS.NAME = COMPANY.NAME AND COMPANY.ST = 'TX';
SELECT * FROM BILLS1;
SELECT * FROM BILLS2;
CREATE VIEW BILLS3 AS SELECT * FROM BILLS2 WHERE NAME IN (SELECT NAME FROM BILLS1);
SELECT * FROM BILLS3;
CREATE VIEW BANKS_IN_TEXAS (BANK) AS SELECT BANK_ACCOUNTS.BANK FROM BANK_ACCOUNTS, BILLS3 WHERE BILLS3.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
SELECT * FROM BANKS_IN_TEXAS;
CREATE INDEX ID_INDEX ON BILLS( ACCOUNT_ID );
DROP INDEX ID_INDEX;
CREATE INDEX ID_CMPD_INDEX ON BILLS( ACCOUNT_ID, AMOUNT );
CREATE UNIQUE INDEX UNIQUE_ID_NAME ON BILLS(ACCOUNT_ID, NAME);
INSERT INTO BILLS (NAME, AMOUNT, ACCOUNT_ID) VALUES('Power Company', 125, 1);
