thank you very much for the help in advance SQL Practice - Submit SQL statements
ID: 3818529 • Letter: T
Question
thank you very much for the help in advance
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 branch’s 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 they’ve 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 they’ve performed, and the date of their last transaction as Year-Month-Day (2017-01-24).
Note: List all customers, even those without transactions.
Explanation / Answer
Answer 1) select CONCAT_WS(c.last_name,',',c.first_name) as full_name, b.street,t.amount,TO_CHAR(t.transaction_date,'YY-MM-DD') as date_trans from CS3350.CUSTOMERS c, CS3350.BRANCHES b, CS3350.TRANSACTIONS t where c.CUSTOMER_ID = t.CUSTOMER_ID and b.BRANCH_NUMBER = t.BRANCH_NUMBER and t.transaction_date like '01%' order by c.customer_id asc
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.