Using the following tables: 6.List the member ID and the total number of transac
ID: 3702936 • Letter: U
Question
Using the following tables:
6.List the member ID and the total number of transactions made by the members whose
name (including first name and/or last name) contains letter “R”.
7.List the member ID, full name and address of the members who have made a
transaction.
8.List the total number of transactions handled by each employee at each location (IN
store, IL store, or Online).
9.List the Employee ID and full name of the employees whose salary is above the average.
10.List the full name and the total number of transactions handled by the full time
employee. Rank your results in descending order on the total number of transactions.
Table: MEMBERS Column Data Type Length Precision Scale Nullable MEMBERID NUMBER 4 0 No MFIRST VARCHAR2 25 No MLAST VARCHAR2 25 No STREET VARCHAR2 64 No CITY VARCHAR2 25 . No STATE VARCHAR2 2 . No ZIPCODE NUMBER 0 No CREDITLIMIT NUMBER 7 2 No GENDER VARCHAR2 - NoExplanation / Answer
Run this sql query to get answer to all of the queries posted above :
6.
select m.MEMBERID , Count(TRANSACTIONID) AS TotalNumberOfTransactions
from TRANSACTIONS t join MEMBERS m
on t.memberid = m.memberid
where m.mfirst like '%R%' and m.mlast like '%R%'
group by memberid;
7.
select m.memberid , m.mfirst + '' + m.mlast as FullName , street ,state , city , zipcode
from members m join transactions t
on m.memberid = t.memberid;
8.
select employeeid , location , count(transactionid) as TotalNumberOfTransactions
from transactions
group by employeeid , location;
9.
select employeeid , e.efirst + '' + e.elast as FullName
from employeess
where salary > (select avg(salary) from employees);
10.
select e.efirst + '' + e.elast as FullName , count(transactionid) as TotalNumberOfTransactions
from transactions t join employees e on t.employeeid = e.employeeid
where etype = 'fulltime'
group by employeeid
order by desc count(transactionid);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.