EMPLOYEE_TBL EMP_ID VARCHAR(9) NOT NULL primary key, LAST_NAME VARCHAR(15) NOT N

Question

EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMERIC(5) NOT NULL,
PHONE CHAR(10),
PAGER CHAR(10),

EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATE,
PAY_RATE DECIMAL(4,2),
DATE_LAST_RAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)


ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key,
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY NUMERIC(6) NOT NULL,
ORD_DATE DATE,



1. Write a query to list the city and count the times of each city appeared and then order the
results in descending order, from highest count to lowest.

2. Write a query to list the average pay rate by position from the EMPLOYEE_PAY_TBL table.

3. Write a query to list the average salary by position from the EMPLOYEE_PAY_TBL table.

Explanation / Answer

Dear user,

1.SELECT CITY, COUNT(*) FROM EMPLOYEE_TBL GROUP BY CITY,
   ORDER BY COUNT(*) DESC;

-----------------------------------------------------------------------------------
2. SELECT POSITION, AVG(PAY_RATE) FROM EMPLOYEE_PAY_TBL
     GROUP BY POSITION;

-----------------------------------------------------------------------------------
3.SELECT POSITION, AVG(SALARY) FROM EMPLOYEE_PAY_TBL
    GROUP BY POSITION;

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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