Homework SourseCREATE TABLE Members MemberID NUMBER4 NOT NULL PRIMARY KEY M
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\');
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 first 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.
Solution
/*INSERT INTO Transactions VALUES(101,2016-06-10,\'Hyderabad\',102,1005);
INSERT INTO Transactions VALUES(102,2016-06-30,\'Begumpet\',103,1005);
INSERT INTO Transactions VALUES(103,2016-05-30,\'INDIA\',102,1004);
INSERT INTO Transactions VALUES(104,2016-08-30,\'INDIA\',102,1003);
INSERT INTO Transactions VALUES(105,2016-01-30,\'BANGALOR\',101,1003);
SELECT * FROM Employees;
SELECT * FROM Members;*/
-- First question--
SELECT MFirst,MLast FROM Members WHERE State NOT IN (\'IN\' , \'IL\');
/*SELECT CONCAT(MFirst,\' \',MLast) FROM Members WHERE State NOT IN (\'IN\' , \'IL\');*/
-- Second question--
SELECT m.MFirst,m.MLast,e.EFirst,e.ELast FROM Members as m , Employees as e
INNER JOIN Transactions as t
ON t.MemberID == m.MemberID AND t.EmployeeID == e.EmployeeID;
-- Thrid question --
SELECT MFirst,MLast,Street,City,State,Zipcode
FROM Members WHERE CreditLimit = (SELECT MAX(CreditLimit) FROM Members);
/*SELECT CONCAT(MFirst,\' \',MLast) as FULLNAME,CONCAT(Street,\' \',City,\' \',State,\' \',Zipcode) AS ADDRESS
FROM Members WHERE CreditLimit = (SELECT MAX(CreditLimit) FROM Members);*/
-- Fourth question --
SELECT EFirst,ELast,JobTitle,Department FROM Employees as e
INNER JOIN Transactions as t
ON e.EmployeeID == t.EmployeeID;
-- Fifth question --
SELECT MemberID,MFirst,MLast,CreditLimit FROM Members WHERE CreditLimit>(SELECT AVG(CreditLimit) FROM Members)
