SQL Practice -

Submit SQL statements

Using the tables (cs3350.branches, cs3350.transactions, cs3350.employees, cs3350.customers)

CS3350.EMPLOYEES

( EMPLOYEE_ID NUMBER,

BRANCH NUMBER,

FIRST_NAME VARCHAR2,

LAST_NAME VARCHAR2

)

CS3350.CUSTOMERS

( CUSTOMER_ID NUMBER,

PRIMARY_BRANCH NUMBER,

FIRST_NAME VARCHAR2,

LAST_NAME VARCHAR2

)

CS3350.TRANSACTIONS

( TRANSACTION_ID NUMBER,

CUSTOMER_ID NUMBER,

BRANCH_NUMBER NUMBER,

AMOUNT NUMBER(6,2),

TRANSACTION_DATE DATE

)

CS3350.BRANCHES

( BRANCH_NUMBER NUMBER,

STREET VARCHAR2,

CITY VARCHAR2,

ZIPCODE VARCHAR2

)

write Select SQL statements to retrieve the following information

1. For all transactions in January, the full name as last name, first name (Godby, Brionne) of the customer as a single field, the branchs street address, the amount, and the date as Year-Month-Day (2017-01-24) listed by customer_id in ascending order.

2. The first and last name of all customers with zero transactions before June 1, 2016 listed in alphabetical order by first_name.

3. For each customer, their customer_id, first name, last name, and the number of transactions theyve performed where the amount was more than $20 (positive or negative) listed by customer_id. Note:You do not have to list customers without transactions > $20.

4. The customer_id, first and last name of each customer whose average transaction amount (absolute value) is greater than the average amount of all transactions.

5. For each customer, their first and last name, the street address of their primary branch, the total of all transactions theyve performed, and the date of their last transaction as Year-Month-Day (2017-01-24). Note: List all customers, even those without transactions.