Question

Given (Oracle) SQL Script

"

CREATE TABLE Members(
   MemberID NUMBER(4) NOT NULL PRIMARY KEY,
   MFirst VARCHAR(25) NOT NULL,
   MLast VARCHAR(25) NOT NULL,
   Street VARCHAR(64) NOT NULL,
   City VARCHAR(25) NOT NULL,
   State VARCHAR(2) NOT NULL,
   ZipCode NUMBER(5) NOT NULL,
   CreditLimit NUMBER(7,2) NOT NULL,
   Gender VARCHAR(1) NOT NULL CHECK (Gender IN('F','M'))
);

CREATE TABLE Employees(
   EmployeeID NUMBER(3) NOT NULL PRIMARY KEY,
   EFirst VARCHAR(25) NOT NULL,
   ELast VARCHAR(25) NOT NULL,
   JobTitle VARCHAR(25) NOT NULL,
   Department VARCHAR(25) NOT NULL,
   Salary NUMBER(7,2) NOT NULL,
   EType VARCHAR(25) NOT NULL
);

CREATE TABLE Transactions(
   TransactionID NUMBER(4) NOT NULL PRIMARY KEY,
   TransactionDate DATE NOT NULL,
   Location VARCHAR(25) NOT NULL,
   EmployeeID NUMBER(4) NOT NULL,
   MemberID NUMBER(4) NOT NULL,
   CONSTRAINT Transactions_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
   CONSTRAINT Transactions_FK2 FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);

INSERT INTO Members VALUES(1001, 'Eugene', 'Harris', '5103 McMillan', 'Shady Cove', 'OR', 97539, 300.00, 'M');
INSERT INTO Members VALUES(1002, 'Jill', 'Milburn', '1668 Randolph', 'Bloomington', 'IN', 47404, 800.00, 'F');
INSERT INTO Members VALUES(1003, 'Rodney', 'Wolter', '6089 Oaskshire', 'Lansing', 'IL', 60438, 500.00, 'M');
INSERT INTO Members VALUES(1004, 'Ramona', 'Neill', '3008 Lafayette', 'Downers Grove', 'IL', 60516, 1200.00, 'F');
INSERT INTO Members VALUES(1005, 'Patty', 'Webb', '2565 Ashburn', 'Palatine', 'IL', 60055, 1000.00, 'F');
INSERT INTO Members VALUES(1006, 'Sabrina', 'Melvin', '4976 Dexter', 'Haubstadt', 'IN', 47406, 600.00, 'F');

INSERT INTO Employees VALUES(101, 'Dennis', 'Ulmer', 'Sales Rep', 'Sales', 56000, 'FullTime');
INSERT INTO Employees VALUES(102, 'Robert', 'Smith', 'Clerk', 'Operations', 32000, 'FullTime');
INSERT INTO Employees VALUES(103, 'Steve', 'Comstock', 'Sales Rep', 'Sales', 44000, 'FullTime');
INSERT INTO Employees VALUES(104, 'Richard', 'Rivero', 'Stocker', 'Operations', 12000, 'Intern');

INSERT INTO Transactions VALUES(1, TO_DATE('12/30/2014','mm/dd/yyyy'), 'Online', 103, 1001);
INSERT INTO Transactions VALUES(2, TO_DATE('12/30/2014','mm/dd/yyyy'), 'IL Store', 101, 1003);
INSERT INTO Transactions VALUES(3, TO_DATE('1/1/2015','mm/dd/yyyy'), 'Online', 101, 1001);
INSERT INTO Transactions VALUES(4, TO_DATE('6/17/2015','mm/dd/yyyy'), 'IN Store', 104, 1002);
INSERT INTO Transactions VALUES(5, TO_DATE('7/8/2015','mm/dd/yyyy'), 'IL Store', 103, 1003);
INSERT INTO Transactions VALUES(6, TO_DATE('11/23/2015','mm/dd/yyyy'), 'Online', 103, 1005);
INSERT INTO Transactions VALUES(7, TO_DATE('12/3/2016','mm/dd/yyyy'), 'IN Store', 103, 1005);
INSERT INTO Transactions VALUES(8, TO_DATE('1/10/2016','mm/dd/yyyy'), 'IL Store', 104, 1004);
INSERT INTO Transactions VALUES(9, TO_DATE('12/22/2016','mm/dd/yyyy'), 'IN Store', 104, 1001);
"

Please answer

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.

Answer 1

CREATE TABLE Members(
MemberID NUMBER(4) NOT NULL PRIMARY KEY,
MFirst VARCHAR(25) NOT NULL,
MLast VARCHAR(25) NOT NULL,
Street VARCHAR(64) NOT NULL,
City VARCHAR(25) NOT NULL,
State VARCHAR(2) NOT NULL,
ZipCode NUMBER(5) NOT NULL,
CreditLimit NUMBER(7,2) NOT NULL,
Gender VARCHAR(1) NOT NULL CHECK (Gender IN('F','M')));

INSERT INTO Members VALUES(1001, 'Eugene', 'Harris', '5103 McMillan', 'Shady Cove', 'OR', 97539, 300.00, 'M');
INSERT INTO Members VALUES(1002, 'Jill', 'Milburn', '1668 Randolph', 'Bloomington', 'IN', 47404, 800.00, 'F');
INSERT INTO Members VALUES(1003, 'Rodney', 'Wolter', '6089 Oaskshire', 'Lansing', 'IL', 60438, 500.00, 'M');
INSERT INTO Members VALUES(1004, 'Ramona', 'Neill', '3008 Lafayette', 'Downers Grove', 'IL', 60516, 1200.00, 'F');
INSERT INTO Members VALUES(1005, 'Patty', 'Webb', '2565 Ashburn', 'Palatine', 'IL', 60055, 1000.00, 'F');
INSERT INTO Members VALUES(1006, 'Sabrina', 'Melvin', '4976 Dexter', 'Haubstadt', 'IN', 47406, 600.00, 'F');

CREATE TABLE Employees(
EmployeeID NUMBER(3) NOT NULL PRIMARY KEY,
EFirst VARCHAR(25) NOT NULL,
ELast VARCHAR(25) NOT NULL,
JobTitle VARCHAR(25) NOT NULL,
Department VARCHAR(25) NOT NULL,
Salary NUMBER(7,2) NOT NULL,
EType VARCHAR(25) NOT NULL);

INSERT INTO Employees VALUES(101, 'Dennis', 'Ulmer', 'Sales Rep', 'Sales', 56000, 'FullTime');
INSERT INTO Employees VALUES(102, 'Robert', 'Smith', 'Clerk', 'Operations', 32000, 'FullTime');
INSERT INTO Employees VALUES(103, 'Steve', 'Comstock', 'Sales Rep', 'Sales', 44000, 'FullTime');
INSERT INTO Employees VALUES(104, 'Richard', 'Rivero', 'Stocker', 'Operations', 12000, 'Intern');

CREATE TABLE Transactions(
TransactionID NUMBER(4) NOT NULL PRIMARY KEY,
TransactionDate DATE NOT NULL,
Location VARCHAR(25) NOT NULL,
EmployeeID NUMBER(4) NOT NULL,
MemberID NUMBER(4) NOT NULL,
CONSTRAINT Transactions_FK1 FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
CONSTRAINT Transactions_FK2 FOREIGN KEY (MemberID) REFERENCES Members(MemberID));


INSERT INTO Transactions VALUES(1, TO_DATE('12/30/2014','mm/dd/yyyy'), 'Online', 103, 1001);
INSERT INTO Transactions VALUES(2, TO_DATE('12/30/2014','mm/dd/yyyy'), 'IL Store', 101, 1003);
INSERT INTO Transactions VALUES(3, TO_DATE('1/1/2015','mm/dd/yyyy'), 'Online', 101, 1001);
INSERT INTO Transactions VALUES(4, TO_DATE('6/17/2015','mm/dd/yyyy'), 'IN Store', 104, 1002);
INSERT INTO Transactions VALUES(5, TO_DATE('7/8/2015','mm/dd/yyyy'), 'IL Store', 103, 1003);
INSERT INTO Transactions VALUES(6, TO_DATE('11/23/2015','mm/dd/yyyy'), 'Online', 103, 1005);
INSERT INTO Transactions VALUES(7, TO_DATE('12/3/2016','mm/dd/yyyy'), 'IN Store', 103, 1005);
INSERT INTO Transactions VALUES(8, TO_DATE('1/10/2016','mm/dd/yyyy'), 'IL Store', 104, 1004);
INSERT INTO Transactions VALUES(9, TO_DATE('12/22/2016','mm/dd/yyyy'), 'IN Store', 104, 1001);

1)
select MFirst || ' ' || MLast "Full Name"
from Members
where State!='IN' or State!='IL';
  
2)
SELECT MFIRST|| ' ' || MLAST "MEMBER FULL NAME", EFIRST|| ' ' || ELAST "EMPLOYEE FULL NAME"
FROM EMPLOYEES E,MEMBERS M,TRANSACTIONS T
WHERE E.EMPLOYEEID=T.EMPLOYEEID AND M.MEMBERID=T.MEMBERID;

3)
SELECT MEMBERID,MFIRST||' ' || MLAST "FULL NAME" ,STREET||' '||CITY||' ' ||STATE||' '||ZIPCODE "ADSRESS"
FROM MEMBERS
WHERE CREDITLIMIT IN (SELECT MAX(CREDITLIMIT)
                           FROM MEMBERS);
                          
4)
SELECT EFIRST|| ' '|| ELAST "FULL NAME", JOBTITLE,DEPARTMENT
FROM EMPLOYEES E, TRANSACTIONS T
WHERE E.EMPLOYEEID=T.EMPLOYEEID AND LOCATION='Online';

5)
SELECT MEMBERID,MFIRST||' ' || MLAST "FULL NAME" ,CREDITLIMIT
FROM MEMBERS
WHERE CREDITLIMIT > (SELECT AVG(CREDITLIMIT)
           FROM MEMBERS);
                  
6)
SELECT T.MEMBERID,COUNT(*)
FROM Transactions T
WHERE MEMBERID IN(SELECT MEMBERID
FROM MEMBERS
WHERE MFIRST LIKE '%R%' OR MLAST LIKE '%R%')
GROUP BY T.MEMBERID;

7)
SELECT DISTINCT(M.MEMBERID),MFIRST||' ' || MLAST "FULL NAME",STREET||' '||CITY||' ' ||STATE||' '||ZIPCODE "ADSRESS"
FROM MEMBERS M,Transactions T
WHERE M.MEMBERID=T.MEMBERID;

8)
SELECT EMPLOYEEID,COUNT(*)
FROM Transactions
WHERE Location='Online' or Location='IL Store' or Location='IN Store'
group by employeeid;

9)   
SELECT EFIRST||' ' || ELAST "FULL NAME" ,SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
           FROM EMPLOYEES);

10)      
SELECT EMPLOYEEID,COUNT(*)
FROM Transactions
WHERE EMPLOYEEID IN(SELECT EMPLOYEEID
                   FROM EMPLOYEES
                   WHERE ETYPE='FullTime')
GROUP BY EmployeeID
ORDER BY COUNT(*) DESC;