Using the following tables: 1.List the full name of members who are not from the
ID: 3702846 • Letter: U
Question
Using the following tables:1.List the full name of members who are not from the state of IN nor IL.
2.List the full name of each member and the full names of the employees’ who ever assisted that member on a transaction.
3.List the full name and full address of the member who has the highest credit limit.
4.List the full name, title, and department of the employee who handled online transactions.
5.List the member ID, full name, and credit limit of each customer whose credit limit is greater than the average credit limit of all members.
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
1. SELECT
CONCAT(mfirst, ' ', mlast) name
FROM
members
WHERE
state NOT IN ('IN' , 'IL')
2. SELECT
CONCAT(m.mfirst, ' ', m.mlast) mname,
CONCAT(e.efirst, ' ', e.elast) ename
FROM
transactions t
JOIN
employees e ON t.employeeid = e.employeeid
JOIN
members m ON t.memberid = m.memberid
3. SELECT
CONCAT(mfirst, ' ', mlast) name,
CONCAT(street, ',', city, ',', state) address
FROM
members
WHERE
creditlimit = (SELECT MAX(creditlimit) FROM members)
4. SELECT
CONCAT(e.efirst, ' ', e.elast) ename,
e.jobtitle title,
e.department department
FROM
transactions t
JOIN
employees e ON t.employeeid = e.employeeid
WHERE
location = 'online'
5. SELECT
memberid, CONCAT(mfirst, ' ', mlast) name, creditlimit
FROM
members
WHERE
creditlimit > (SELECT AVG(creditlimit) FROM members)
6. SELECT
t.memberid, COUNT(*) total_transactions
FROM
transactions t
JOIN
members m ON t.memberid = m.memberid
WHERE
m.mfirst LIKE '%R%'
OR m.mlast LIKE '%R%'
GROUP BY t.memberid
7. SELECT DISTINCT
t.memberid,
CONCAT(m.mfirst, ' ', m.mlast) name,
CONCAT(m.street, ',', m.city, ',', state) address
FROM
transactions t
JOIN
members m ON t.memberid = m.memberid
8. SELECT
t.employeeid, t.location, COUNT(*) total_transactions
FROM
transactions t
JOIN
employees e ON t.employeeid = e.employeeid
GROUP BY t.employeeid , t.location
9. SELECT
employeeid, CONCAT(efirst, ' ', elast) ename
FROM
employees
WHERE
salary > (SELECT AVG(salary) FROM employees)
10. SELECT
CONCAT(e.efirst, ' ', e.elast) ename,
COUNT(*) total_transactions
FROM
transactions t
JOIN
employees e ON t.employeeid = e.employeeid
WHERE
e.etype = 'Fulltime'
GROUP BY t.employeeid
ORDER BY total_transactions DESC
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.