***PLEASE POST THE QUERIES IN SQL/ORACLE FORMAT TO THE QUESTIONS BELOW USING THE
ID: 3701706 • Letter: #
Question
***PLEASE POST THE QUERIES IN SQL/ORACLE FORMAT TO THE QUESTIONS BELOW USING THE FOLLOWING DATABASE, THANK YOU***
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);
1. List the full name of members who are not from the state of IN nor IL.
List the full name of each member and the full names of the employees’ who ever assisted that member on a transaction.
List the full name and full address of the member who has the highest credit limit.
2. List the full name, title, and department of the employee who handled online transactions.
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.
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”.
3. List the member ID, full name and address of the members who have made a transaction.
List the total number of transactions handled by each employee at each location (IN store, IL store, or Online).
List the Employee ID and full name of the employees whose salary is above the average.
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.
Explanation / Answer
1. List the full name of members who are not from the state of IN nor IL.
select
MFIRST||' '||MLAST as FULL_NAME
from members
where state not in ('IL','IN');
List the full name of each member and the full names of the employees’ who ever assisted that member on a transaction.
select
distinct
mem.MFIRST||' '||mem.MLAST as MEMBER_FULL_NAME,
emp.EFIRST||' '||emp.ELAST as EMPLOYEE_FULL_NAME
from
members mem
join
Transactions tns
on
mem.MEMBERID=tns.MEMBERID
join
employees emp
on
emp.EMPLOYEEID=tns.EMPLOYEEID
;
List the full name and full address of the member who has the highest credit limit.
select
MFIRST||' '||MLAST as FULL_NAME,
STREET||' '||City||' '||State AS ADDRESS
from members
where
CREDITLIMIT=(select MAX(CREDITLIMIT) from members) ;
2. List the full name, title, and department of the employee who handled online transactions.
select
emp.EFIRST||' '||emp.ELAST as EMPLOYEE_FULL_NAME,
emp.JOBTITLE as TITLE,
emp.DEPARTMENT as DEPARTMENT
from
employees emp
join
Transactions tns
on
emp.EMPLOYEEID=tns.EMPLOYEEID
;
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 as MEMBER_ID,
MFIRST||' '||MLAST as FULL_NAME,
STREET||' '||City||' '||State AS ADDRESS
from members
where CREDITLIMIT >(select Avg(CREDITLIMIT) from members) ;
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”.
select
mem.MEMBERID AS MEMBER_ID,
COUNT(TRANSACTIONID) as TOTAL_NUMBER_OF_TRANSACTIONS
from
members mem
join
Transactions tns
on
mem.MEMBERID=tns.MEMBERID
WHERE
upper(mem.MFIRST) like '%R%'
OR
upper(mem.MLAST) like '%R%'
GROUP by
mem.MEMBERID
;
3. List the member ID, full name and address of the members who have made a transaction.
select
mem.MEMBERID as MEMBER_ID,
mem.MFIRST||' '||mem.MLAST as FULL_NAME,
mem.STREET||' '||mem.City||' '||mem.State AS ADDRESS
from
members mem
join
Transactions tns
on
mem.MEMBERID=tns.MEMBERID
;
List the total number of transactions handled by each employee at each location (IN store, IL store, or Online).
select
emp.EMPLOYEEID AS EMPLOYEE_ID,
emp.EFIRST||' '|| emp.ELAST AS EMPLOYEE_FULL_NAME,
tns.LOCATION,
COUNT(TRANSACTIONID) as TOTAL_NUMBER_OF_TRANSACTIONS
from
employees emp
join
Transactions tns
on
emp.EMPLOYEEID=tns.EMPLOYEEID
WHERE
LOCATION in( 'IN Store','IL Store','Online')
GROUP by
emp.EMPLOYEEID,
emp.EFIRST||' '|| ELAST,
tns.LOCATION
;
List the Employee ID and full name of the employees whose salary is above the average.
select
EMPLOYEEID AS EMPLOYEE_ID,
EFIRST||' '|| ELAST AS EMPLOYEE_FULL_NAME
from
employees
where
Salary >( select avg(Salary) from employees)
;
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.
select
emp.EFIRST||' '|| emp.ELAST AS EMPLOYEE_FULL_NAME,
COUNT(TRANSACTIONID) as TOTAL_NUMBER_OF_TRANSACTIONS
from
employees emp
join
Transactions tns
on
emp.EMPLOYEEID=tns.EMPLOYEEID
WHERE
ETYPE='FullTime'
GROUP by
emp.EFIRST||' '|| ELAST
order by
TOTAL_NUMBER_OF_TRANSACTIONS DESC
;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.