SQL Practice - Submit SQL statements Using the tables (cs3350.branches, cs3350.t
ID: 3816529 • Letter: S
Question
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
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.
The first and last name of all customers with zero transactions before June 1, 2016 listed in alphabetical order by first_name.
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.
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.
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
1. select concat('lastname',' ','firstname') as fullname,street,amount,to_char(transaction_date,'YYYY-MM-DD') from customers,branches,transactions order by customers.customer_id ASC;
2. select concat('lastname,' ','firstname') as fullname,from customers where transaction_id=0 and to_char(transaction_date,'month day,yyyy')<'June 1,2016';
3. select c.customer_id,c.first_name,c.last_name,count(t.transaction_id) from customers c,transactions t where c.customer_id=t.customer_id and t.amount>20 and t.amount>-20 order by c.customer_id
4. select c.customer_id,c.first_name,c.last_name from customer c,transactions t where c.customer_id=t.customer_id and avg(amount)>sum(amount)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.