Using the following tables: 1.List the full name of members who are not from the

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.

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

If you have any doubts, please give me comment...

--1.List the full name of members who are not from the state of IN nor IL.

SELECT CONCAT(MFIRST, ' ', MLAST) AS NAME
FROM MEMBERS
WHERE STATE<>'IN' OR STATE <>'IL';

--2. List the full name of each member and the full names of the employees’ who ever assisted that member on a transaction.

SELECT CONCAT(MFIRST, '', MLAST) AS NAME
FROM MEMBERS M INNER JOIN TRANSACTIONS T ON M.MEMBERID = T.MEMBERID
UNION
SELECT CONCAT(MFIRST, ' ', MLAST) AS FULLNAME
FROM EMPLOYEES E INNER JOIN TRANSACTIONS T ON E.EMPLOYEEID = T.EMPLOYEEID;

--3. List the full name and full address of the member who has the highest credit limit.

SELECT CONCAT(MFIRST, ' ', MLAST) AS NAME, CONCAT(STREET, ', ',CITY, ', ', STATE, ' - ', ZIPCODE) AS ADDRESS
FROM MEMBERS
WHERE CREDITLIMIT = (
    SELECT MAX(CREDITLIMIT)
    FROM MEMBERS
);

--4.List the full name, title, and department of the employee who handled online transactions.

SELECT CONCAT(MFIRST, ' ', MLAST) AS NAME, JOBTITLE, DEPARTMENT
FROM EMPLOYEES E INNER JOIN TRANSACTIONS T ON E.EMPLOYEEID = T.EMPLOYEEID;

--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.

SELECT MEMBERID, CONCAT(MFIRST, ' ', MLAST) AS NAME, CREDITLIMIT
FROM MEMBERS
WHERE CREDITLIMIT >= (
    SELECT AVG(CREDITLIMIT)
    FROM MEMBERS
);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.