Databases Basics - Functions, Triggers and Transactions Homework - Problem 12. Calculating Interest

Databases Basics - Functions, Triggers and Transactions Homework - Problem 12. Calculating Interest

kracheff avatar kracheff 11 Точки

Databases Basics - Functions, Triggers and Transactions Homework - Problem 12. Calculating Interest

Колеги, за на пръв поглед лесната задачка 12 от домашното по Functions, Triggers and Transactions, не мога да разбера от къде трябва да се вземе баланса на сметката, за да се увеличи с лихвата.

Импортирах си Bank Database от тук https://softuni.bg/downloads/svn/DB-Fundamentals/DB-Basics-MSSQL/Sept-2016/08.%20DB-Basics-MSSQL-Exam-Preparation/Bank%20Skeleton.sql , но гледам в скрипта, че в таблица Accounts няма колона с баланса.

CREATE TABLE Accounts( 
AccountID INT,
AccountNumber CHAR(12) NOT NULL,
StartDate DATE NOT NULL,
CustomerID INT NOT NULL,
CONSTRAINT PK_Accounts PRIMARY KEY(AccountID),
CONSTRAINT FK_Accounts_Customers FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID)
)

И не намирам и в другите таблици информация за баланса по сметката.

Аз ли не разбирам добре условията на задачите или нещо е пропуснато в базата данни за банката?

Тагове:
0
Databases Basics
angelnb avatar angelnb 24 Точки

Таблицата която трябва да ползваш е тази:

CREATE DATABASE Bank;
USE Bank;

CREATE TABLE account_holders
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
ssn CHAR(10) NOT NULL,
CONSTRAINT pk_account_holders PRIMARY KEY (id)
);

CREATE TABLE accounts
(
id INT NOT NULL,
account_holder_id INT NOT NULL,
balance DECIMAL(19, 4) DEFAULT 0,
CONSTRAINT pk_accounts PRIMARY KEY (Id),
CONSTRAINT fk_accounts_account_holders FOREIGN KEY (account_holder_id) REFERENCES account_holders(id)
);

INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (1, 'Susan', 'Cane', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (2, 'Kim', 'Novac', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (3, 'Jimmy', 'Henderson', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (4, 'Steve', 'Stevenson', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (5, 'Bjorn', 'Sweden', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (6, 'Kiril', 'Petrov', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (7, 'Petar', 'Kirilov', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (8, 'Michka', 'Tsekova', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (9, 'Zlatina', 'Pateva', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (10, 'Monika', 'Miteva', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (11, 'Zlatko', 'Zlatyov', '1234567890');
INSERT INTO account_holders (id, first_name, last_name, ssn) VALUES (12, 'Petko', 'Petkov Junior', '1234567890');

INSERT INTO accounts (id, account_holder_id, balance) VALUES (1, 1, 123.12);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (2, 3, 4354.23);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (3, 12, 6546543.23);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (4, 9, 15345.64);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (5, 11, 36521.20);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (6, 8, 5436.34);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (7, 10, 565649.20);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (8, 11, 999453.50);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (9, 1, 5349758.23);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (10, 2, 543.30);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (11, 3, 10.20);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (12, 7, 245656.23);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (13, 5, 5435.32);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (14, 4, 1.23);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (15, 6, 0.19);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (16, 2, 5345.34);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (17, 11, 76653.20);
INSERT INTO accounts (id, account_holder_id, balance) VALUES (18, 1, 235469.89);

0
angelnb avatar angelnb 24 Точки

Предполагам са забравили да я качат, като ресурс. На мен ми я даде един колега от "Java DB Fundamentals Sept 2016 @ SoftUni" групата.

0
12/10/2016 20:39:43
kracheff avatar kracheff 11 Точки

Така вече има логика :) 

Благодаря ти много!

Вероятно са забравили да го качат наистина.

Поздрави и успех!

0