Question

Database Queries

Use the file at the bottom of this assignment to complete the following:

1. Create a query that shows all customers with account balances less than $18,000. Sort ascending by the date the account was open.
2. Create a query that shows all customers from Durham with Savings accounts. Sort ascending by the last name of the customer.
3. Create a query that shows all customers from Raleigh who opened their accounts before 1998. Sort descending by account type.
4. Create a query that shows all customers from Cary and Garner with a savings account.
5. Create a query that shows all customers in Raleigh and Durham with checking account balances greater $13,000. Sort ascending by account balance.
6. I need help with the proper syntax in access to create these queries.

Answer 1

Note : You need to install mysql in your system or test in any mysql online ide.

Create database with name 'bank' :

CREATE DATABSE bank;

Check where database is create or not :

SHOW DATABASES;

Use 'bank' database :

USE bank;

Create table with name 'accounts' to insert data & for query :

CREATE TABLE accounts(
ACCOUNT_NUMBER INT(11) NOT NULL PRIMARY KEY,
CUSTOMER_FIRST_NAME CHAR(30),
CUSTOMER_LAST_NAME CHAR(30),
ACCOUNT_BALANCE INT(20),
ACCOUNT_TYPE CHAR(15),
CUSTOMER_CITY CHAR(30),
ACCOUNT_OPENING_DATE, DATE
);

Insert datas into the table 'accounts' :

INSERT INTO accounts
(ACCOUNT_NUMBER, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, ACCOUNT_BALANCE,
ACCOUNT_TYPE,CUSTOMER_CITY, ACCOUNT_OPENING_DATE) VALUES
       (10000000001, 'Ranveer', 'Napoor', 10000, 'Saving', 'Aberdeen', '1994-02-27'),
       (10000000002, 'Sunil', 'Keshri', 18000, 'Current', 'Bangor', '1995-03-31'),
       (10000000003, 'Rohit', 'Kumar', 25000, 'Current', 'Bath', '1996-05-14'),
       (10000000004, 'Anmol', 'Singh', 50000, 'Saving', 'Durhan', '1997-07-16'),
       (10000000005, 'Avery', 'Aiden', 15000, 'Saving', 'Bradford', '1998-11-21'),
       (10000000006, 'Elia', 'Jackson', 27000, 'Current', 'Cambridge', '1999-04-14'),
       (10000000007, 'Grace', 'Samuel', 75000, 'Saving', 'Carlisle', '2000-05-16'),
       (10000000008, 'Lilly', 'John', 5000, 'Saving', 'Cary', '2005-08-23');
       (10000000009, 'Zoey', 'Anthony', 13000, 'Current', 'Garner', '2007-08-20'),
       (10000000010, 'Harper', 'Joseph', 200000, 'Saving', 'Raleigh', '2009-12-31');

Queries on table :

1) Create a query that shows all customers with account balances less than $18,000. Sort ascending by the date the account was open.

SELECT *
FROM accounts
WHERE ACCOUNT_BALANCE < 18000
ORDER BY ACCOUNT_OPENING_DATE ASC;

2) Create a query that shows all customers from Durham with Savings accounts. Sort ascending by the last name of the customer.

SELECT *
FROM accounts
WHERE CUSTOMER_CITY = 'Durhan' AND ACCOUNT_TYPE = 'Saving'
ORDER BY CUSTOMER_LAST_NAME ASC;

3) Create a query that shows all customers from Raleigh who opened their accounts before 1998. Sort descending by account type.

SELECT *
FROM accounts
WHERE CUSTOMER_CITY = 'Raleigh' AND YEAR(ACCOUNT_OPENING_DATE) = 1998
ORDER BY ACCOUNT_TYPE ASC;

4) Create a query that shows all customers from Cary and Garner with a savings account.

SELECT *
FROM accounts
WHERE ACCOUNT_TYPE = 'Saving' AND (CUSTOMER_CITY = 'Cary' AND CUSTOMER_CITY = 'Garner');

5) Create a query that shows all customers in Raleigh and Durham with checking account balances greater $13,000. Sort ascending by account balance.

SELECT *
FROM accounts
WHERE ACCOUNT_BALANCE > 13000 AND (CUSTOMER_CITY = 'Raleigh' AND CUSTOMER_CITY = 'Durhan');

Please upvote if it helps. Feel free to comment if you have any query.

***thank you***