6.List the member ID and the total number of transactions made by the members wh
ID: 328335 • Letter: 6
Question
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 - No
Explanation / Answer
6. SELECT TRANSACTIONS.MEMBERID, COUNT(TRANSACTIONS.TRANSACTIONID) FROM TRANSACTIONS, MEMBERS WHERE MEMBERS.MEMEBERID = TRANSACTIONS.MEMBERID AND MEMBERS.MFIRST LIKE '%R%' OR MEMBERS.MLAST LIKE '%R%' GROUP BY TRANSACTIONS.MEMBERID
7. SELECT TRANSACTIONS.MEMBERID, MEMBERS.MFIRST, MEMBERS.MLAST, MEMBERS.STREET, MEMBERS.CITY, MEMBERS.STATE, MEMBERS.ZIPCODE FROM TRANSACTIONS, MEMBERS WHERE MEMBERS.MEMEBERID = TRANSACTIONS.MEMBERID
8. SELECT COUNT(TRANSACTIONS.TRANSACTIONID) FROM TRANSACTIONS, EMPLOYEES WHERE TRANSACTIONS.EMPLOYEEID = EMPLOYEES.EMPLOYEEID GROUP BY TRANSACTIONS.LOCATION
9. SELECT EMPLOYEES.EMPLOYEEID, EMPLOYEES.EFIRST, EMPLOYEES.ELAST FROM EMPLOYEES WHERE EMPLOYEES.SALARY > AVG(EMPLOYEES.SALARY)
10. SELECT EMPLOYEES.EFIRST, EMPLOYEES.ELAST, COUNT(TRANSACTIONS.TRANSACTIONID) FROM EMPLOYEES, TRANSACTION WHRE TRANSACTIONS.EMPLOYEEID = EMPLOYEES.EMPLOYEEID AND EMPLOYEES. ETYPE = 'FULL TIME' GROUP BY EMPLOYEES.EMPLOYEEID ORDER BY COUNT(TRANSACTIONS.TRANSACTIONID) DESC
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.