Provide SQL query for the questions below: 1. List the full name of members who

Question

Provide SQL query for the questions below:

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.

----------------------------------------------------------------------------------------------------------

Text file:

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);

Explanation / Answer

Solution:

Query:

1)

SELECT MFirst,MLast FROM Members WHERE State NOT IN ('IN' , 'IL');
/*SELECT CONCAT(MFirst,' ',MLast) FROM Members WHERE State NOT IN ('IN' , 'IL');*/
2)
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;
3)
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);*/
4)
SELECT EFirst,ELast,JobTitle,Department FROM Employees as e
INNER JOIN Transactions as t
ON e.EmployeeID == t.EmployeeID;
5)

SELECT MemberID,MFirst,MLast,CreditLimit FROM Members WHERE CreditLimit>(SELECT AVG(CreditLimit) FROM Members)?

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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