Question

Space X Bank

CREATE TABLE Branch(BranchIDNumber VARCHAR(15) PRIMARY KEY NOT NULL, BranchName VARCHAR(6) NOT NULL, Address VARCHAR(50) NOT NULL, City TEXT NULL,
State CHAR(2) NULL, ZipCode INT(11) NOT NULL, OfficeNum VARCHAR(15) NOT NULL, FaxNum VARCHAR(15) NOT NULL);

CREATE TABLE Employee(EmployeeIDNumber VARCHAR(15) NOT NULL,FirstName VARCHAR(35) NOT NULL, LastName VARCHAR(35) NOT NULL,
Email VARCHAR(100) NOT NULL, BranchIDNumber VARCHAR(11) NOT NULL, FOREIGN KEY(BranchIDNumber) REFERENCES Branch(BranchIDNumber),
JobTitle ENUM("Manager","Staff") NOT NULL, Salary DECIMAL(8, 2) NOT NULL, HomeNumber VARCHAR(13) NULL, CellNumber VARCHAR(13) NOT NULL);

CREATE TABLE Customer(CustomerIDNumber VARCHAR(15) PRIMARY KEY NOT NULL, FirstName VARCHAR(35) NOT NULL, LastName VARCHAR(35) NOT NULL, Address VARCHAR(35) NOT NULL,
City TEXT NOT NULL, State CHAR(2) NULL, ZipCode INT(10) NOT NULL, CellNumber VARCHAR(13) NOT NULL, Email VARCHAR(100) NOT NULL,
BranchIDNumber VARCHAR(11) NOT NULL, FOREIGN KEY(BranchIDNumber) REFERENCES Branch(BranchIDNumber));

CREATE TABLE Account(BAccountNumber VARCHAR(25) NOT NULL PRIMARY KEY, AccountBalance DECIMAL(8, 2) NOT NULL, CustomerIDNumber VARCHAR(15) NOT NULL, FOREIGN KEY(CustomerIDNumber)
REFERENCES Customer(CustomerIDNumber), EmployeeIDNumber VARCHAR(15) NOT NULL, FOREIGN KEY(EmployeeIDNumber) REFERENCES Employee(EmployeeIDNumber),
AccountType ENUM("Checking", "MoneyMarketSavings") NOT NULL, ExpirationDate DATE Not NULL,
CardType ENUM("Mastercard", "Other"));

CREATE TABLE Checking(BAccountNumber VARCHAR(25) NOT NULL, FOREIGN KEY (BAccountNumber) REFERENCES Account(BAccountNumber), OverDraftFee INT(2) NOT NULL);

CREATE TABLE MoneyMarketSavings(BAccountNumber VARCHAR(25) NOT NULL, FOREIGN KEY (BAccountNumber) REFERENCES Account(BAccountNumber), InterestRate DECIMAL(8, 3));

CREATE TABLE CreditCard(CreditCardNumber VARCHAR(25) PRIMARY KEY NOT NULL, SecurityCode INT(3) NOT NULL, FOREIGN KEY(SecurityCode) REFERENCES CreditCard(SecurityCode),
CurrentBalance DECIMAL(8,2) NOT NULL, ExpirationDate DATE NOT NULL, CardType ENUM("MasterCard", "Visa") NOT NULL, CreditLimit INT(15) NOT NULL, AnnualFee INT(3) NOT NULL,
LateFee INT(3) NOT NULL, CustomerIDNumber VARCHAR(15) NOT NULL, FOREIGN KEY(CustomerIDNumber) REFERENCES Customer(CustomerIDNumber));

CREATE TABLE CreditCardPayment(PaymentNumber VARCHAR(35) PRIMARY KEY NOT NULL, CreditCardNumber VARCHAR(35) NOT NULL, FOREIGN KEY (CreditCardNumber)
REFERENCES CreditCard(CreditCardNumber), PaymentDate DATE NOT NULL, PaymentAmount DECIMAL(8,2) NOT NULL,PaymentType ENUM("Cash", "Bank Transfer") NOT NULL);
_______________________

Retrieve queries:

5. Retrieve the credit card number, the current balance, card type and expiration date. Only show those whose current balance is under 2500 or the expiration date is from 12/01/2020 to 12/01/2021 Format the date to show as 10/14/2020 and format the current balance to show $2500

6. Retrieve the customer full name and email. In addition, show the customers account number, their balance, and account type. Only show those customers whose balance is over 5000 and are not Money Market Savings accounts.

7. Retrieve the money market savings account number, its interest rate, the account balance, show how much interest amount that was earned from every account. In addition apply that interest amount to a new column called new balance. Round the new balance to 2nd decimal place.

8. Retrieve the Customers Full name and their full address. Show how many accounts each customer has opened.

9. Retrieve the customer full name, and email. Also retrieve credit card number, current balance, expiration date and card type. Show the total amount of money paid for each account. IN addition, reduce that amount from the current balance and put it in a new column called new balance. Sort it by the customer full name Also format the date to show as 12/2020 and Current balance, total amount of money paid and new balance to show as $45,000.00

Answer 1

SQL QUERIES:

1.

SELECT EmployeeIDNumber, FirstName, LastName

FROM Employee

WHERE BranchIDNumber= (SELECT BranchIDNumber FROM Branch WHERE BranchName= 'SpaceX');

2.

SELECT AccountNumber, AccountBalance, AccountType

FROM Account

WHERE AccountBalance BETWEEN 500 AND 1000;

3.

SELECT COUNT(EmployeeIDNumber) AS "Number of Employees"

FROM Employee

WHERE BranchIDNumber= 'SpaceX';

4.

SELECT AccountNumber

FROM Account

WHERE AccountBalance> 5000

AND AccountType= "Money Market Savings";