Please write queries based on the following requirements using Oracle iSQL. Writ
ID: 3684099 • Letter: P
Question
Please write queries based on the following requirements using Oracle iSQL.
Write the SQL query to create the following tables and input the records in each table. The primary key is underlined and foreign key is made italic. Include a screen shot of each table with all the records in Oracle. Input yourself as a new member to Table "Members". You should make up an appropriate MemberlD and you are free to use a fake address, and phone number etc. but you must use your first and last name. Include a screen shot of Members Table with your information. List the full name and full address of the member who has the highest credit limit. 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 first name contains letter "R". 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
Question 1
CREATE TABLE MEMBERS (MEMBERID NUMBER(4) PRIMARY KEY,MFIRST VARCHAR2(50), MLAST VARCHAR2(50), STREET VARCHAR2(50), CITY VARCHAR2(50), STATE VARCHAR2(10), ZIPCODE NUMBER(5), CREDITLIMIT NUMBER(10), GENDER VARCHAR2(1));
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1001','Eugene','Harris','5103 McMillan','Shady Cove','OR','97539','300','M');
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1002','Jill','Milburn','1668 Randolph','Bloomington','IN','47404','800','F');
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1003','Rodney','Wolter','6089 Oaskshire','Lansing','IL','60438','500','M');
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1004','Ramona','Neill','3008 Lafayette','Downers Grove','IL','60516','1200','F');
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1005','Patty','Webb','2565 Ashburn','Palatine','IL','60055','1000','F');
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1006','Sabrina','Melvin','4976 Dexter','Haubstadt','IN','47406','600','F');
COMMIT;
CREATE TABLE TRANSACTIONS (TRANSACTIONID NUMBER(5) PRIMARY KEY, TRASACTIONDATE VARCHAR2(50), LOCATION VARCHAR2(40), EMPLOYEEID NUMBER(5), MEMBERID NUMBER(5), CONSTRAINT FK FOREIGN KEY (MEMBERID) REFERENCES MEMBERS(MEMBERID));
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('1','12/30/2014','Online','103','1001');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('2','12/30/2014','IL Store','101','1003');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('3','1/1/2015','Online','101','1001');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('4','6/17/2015','IL Store','104','1002');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('5','7/8/2015','IL Store','103','1003');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('6','11/23/2014','Online','103','1005');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('7','12/3/2016','IN Store','103','1005');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('8','1/10/2016','IL Store','104','1004');
INSERT INTO TRANSACTIONS (TRANSACTIONID, TRASACTIONDATE, LOCATION, EMPLOYEEID, MEMBERID) VALUES('9','2/22/2016','IN Store','104','1001');
COMMIT;
CREATE TABLE EMPLOYEES ( EMPLOYEEID NUMBER(5) PRIMARY KEY, EFIRST VARCHAR2(100), ELAST VARCHAR2(100), JOBTITLE VARCHAR2(100), DEPARTMENT VARCHAR2(100), SALARY NUMBER(10), ETYPE VARCHAR2(50));
INSERT INTO EMPLOYEES (EMPLOYEEID, EFIRST, ELAST, JOBTITLE, DEPARTMENT, SALARY, ETYPE) VALUES('101','Dennis','Ulmer','Sales Rep','Sales','56000','FullTime');
INSERT INTO EMPLOYEES (EMPLOYEEID, EFIRST, ELAST, JOBTITLE, DEPARTMENT, SALARY, ETYPE) VALUES('102','Robert','Smith','Clerk','Operations','32000','FullTime');
INSERT INTO EMPLOYEES (EMPLOYEEID, EFIRST, ELAST, JOBTITLE, DEPARTMENT, SALARY, ETYPE) VALUES('103','Steve','Comstock','Sales Rep','Sales','44000','FullTime');
INSERT INTO EMPLOYEES (EMPLOYEEID, EFIRST, ELAST, JOBTITLE, DEPARTMENT, SALARY, ETYPE) VALUES('104','Richard','Rivero','Stocker','Operations','12000','FullTime');
COMMIT;
Question 2
INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1007','Suresh','Murapaka','aaaaaaa','aaaaaa','IN','47444','6000','M');
COMMIT;
SQL> INSERT INTO MEMBERS(MEMBERID, MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT, GENDER) VALUES ('1007','Suresh','Murapaka','aaaaaaa','aaaaaa','IN','47444','6000','M');
1 row created.
SQL> COMMIT;
Commit complete.
Question 3
SELECT MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT FROM MEMBERS WHERE CREDITLIMIT = (SELECT MAX(CREDITLIMIT) FROM MEMBERS);
SQL> SELECT MFIRST, MLAST, STREET, CITY, STATE, ZIPCODE, CREDITLIMIT FROM MEMBERS WHERE CREDITLIMIT = (SELECT MAX(CREDITLIMIT) FROM MEMBERS);
MFIRST MLAST STREET CITY
STATE ZIPCODE CREDITLIMIT
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------------------------------
--- ---------- ---------- -----------
Suresh Murapaka aaaaaaa aaaaaa
IN 47444 6000
Question 4
SELECT EFIRST, ELAST, DEPARTMENT FROM EMPLOYEES A, TRANSACTIONS B WHERE A.EMPLOYEEID = B.EMPLOYEEID AND LOCATION = 'Online';
SQL> SELECT EFIRST, ELAST, DEPARTMENT FROM EMPLOYEES A, TRANSACTIONS B WHERE A.EMPLOYEEID = B.EMPLOYEEID AND LOCATION = 'Online';
EFIRST ELAST
DEPARTMENT
---------------------------------------------------------------------------------------------------- ------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------
Dennis Ulmer
Sales
Steve Comstock
Sales
Steve Comstock
Sales
Question 5
SELECT MEMBERID, MFIRST, MLAST, CREDITLIMIT FROM MEMBERS WHERE CREDITLIMIT > = (SELECT AVG(CREDITLIMIT) FROM MEMBERS);
SQL> SELECT MEMBERID, MFIRST, MLAST, CREDITLIMIT FROM MEMBERS WHERE CREDITLIMIT > = (SELECT AVG(CREDITLIMIT) FROM MEMBERS);
MEMBERID MFIRST MLAST CREDITLIMIT
---------- -------------------------------------------------- -------------------------------------------------- -----------
1007 Suresh Murapaka 6000
Question 6
SELECT B.MEMBERID, COUNT(B.TRANSACTIONID) FROM MEMBERS A, TRANSACTIONS B WHERE A.MEMBERID = B.MEMBERID AND A.MFIRST LIKE 'R%' GROUP BY B.MEMBERID;
SQL> SELECT B.MEMBERID, COUNT(B.TRANSACTIONID) FROM MEMBERS A, TRANSACTIONS B WHERE A.MEMBERID = B.MEMBERID AND A.MFIRST LIKE 'R%' GROUP BY B.MEMBERID;
MEMBERID COUNT(B.TRANSACTIONID)
---------- ----------------------
1003 2
1004 1
Question 7
SELECT DISTINCT A.MEMBERID, A.MFIRST, A.MLAST, A.STREET, A.CITY, A.STATE, A.ZIPCODE, A.CREDITLIMIT FROM MEMBERS A, TRANSACTIONS B WHERE A.MEMBERID = B.MEMBERID;
SQL> SELECT DISTINCT A.MEMBERID, A.MFIRST, A.MLAST, A.STREET, A.CITY, A.STATE, A.ZIPCODE, A.CREDITLIMIT FROM MEMBERS A, TRANSACTIONS B WHERE A.MEMBERID = B.MEMBERID;
MEMBERID MFIRST MLAST STREET CITY
STATE ZIPCODE CREDITLIMIT
---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------
-------------- ---------- ---------- -----------
1003 Rodney Wolter 6089 Oaskshire Lansing
IL 60438 500
1005 Patty Webb 2565 Ashburn Palatine
IL 60055 1000
1004 Ramona Neill 3008 Lafayette Downers Grove
IL 60516 1200
1001 Eugene Harris 5103 McMillan Shady Cove
OR 97539 300
1002 Jill Milburn 1668 Randolph Bloomington
IN 47404 800
Question 8
SELECT EMPLOYEEID, LOCATION, COUNT(LOCATION) FROM TRANSACTIONS GROUP BY LOCATION, EMPLOYEEID;
SQL> SELECT EMPLOYEEID, LOCATION, COUNT(LOCATION) FROM TRANSACTIONS GROUP BY LOCATION, EMPLOYEEID;
EMPLOYEEID LOCATION COUNT(LOCATION)
---------- ---------------------------------------- ---------------
104 IL Store 2
103 IN Store 1
103 IL Store 1
101 IL Store 1
101 Online 1
103 Online 2
104 IN Store 1
7 rows selected.
Question 9
SELECT EMPLOYEEID, EFIRST, ELAST, SALARY FROM EMPLOYEES WHERE SALARY > = (SELECT AVG(SALARY) FROM MEMBERS);
SQL> SELECT EMPLOYEEID, EFIRST, ELAST, SALARY FROM EMPLOYEES WHERE SALARY > = (SELECT AVG(SALARY) FROM MEMBERS);
EMPLOYEEID EFIRST ELAST
SALARY
---------- ---------------------------------------------------------------------------------------------------- ----------------
------------ ----------
101 Dennis Ulmer
56000
102 Robert Smith
32000
103 Steve Comstock
44000
104 Richard Rivero
12000
Question 10
SELECT B.EMPLOYEEID, COUNT(B.TRANSACTIONID) FROM EMPLOYEES A, TRANSACTIONS B WHERE A.EMPLOYEEID = B.EMPLOYEEID AND A.ETYPE LIKE 'FullTime' GROUP BY B.EMPLOYEEID ORDER BY COUNT(B.TRANSACTIONID) DESC;
SQL> SELECT B.EMPLOYEEID, COUNT(B.TRANSACTIONID) FROM EMPLOYEES A, TRANSACTIONS B WHERE A.EMPLOYEEID = B.EMPLOYEEID AND A.ETYPE LIKE 'FullTime' GROUP BY B.EMPLOYEEID ORDER BY COUNT(B.TRANSACTIONID) DESC;
EMPLOYEEID COUNT(B.TRANSACTIONID)
---------- ----------------------
103 4
104 3
101 2