Use the following tables to complete the exercises: EMPLOYEE_TBL EMP_ID VARCHAR(

Question

Use the following tables to complete the exercises:

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. Will the following SELECT statements work? If not, what will fix the statements?
a. SELECT COUNT *
FROM EMPLOYEE_PAY_TBL;

b. SELECT COUNT(EMPLOYEE_ID), SALARY
FROM EMPLOYEE_PAY_TBL;

c. SELECT MIN(BONUS), MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;

d. SELECT EMP_ID, COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID
GROUP BY SALARY;

2. Use EMPLOYEE_PAY_TBL to construct SQL statements to solve the following exercises.
a. What is the average salary?
b. What is the maximum bonus?
c. What are the total salaries?
d. What is the minimum pay rate?
e. How many rows are in the table?

3. How many employees do we have whose last names begin with a G?

4. If every product cost $10.00, what would be the total dollar amount for all orders?

5. 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.

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

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

Explanation / Answer

1)
a) Given SELECT Statement:

SELECT COUNT *
FROM EMPLOYEE_PAY_TBL;

It will not work.The corrected SELECT statement is given below:

SELECT COUNT( *)
FROM EMPLOYEE_PAY_TBL;

b) Given SELECT Statement:

SELECT COUNT(EMPLOYEE_ID), SALARY
FROM EMPLOYEE_PAY_TBL;

It will not work.The corrected SELECT statement is given below:

SELECT COUNT(EMP_ID), SALARY
FROM EMPLOYEE_PAY_TBL;
_______________________________________________________________

c) Given SELECT Statement:

SELECT MIN(BONUS), MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;

Yes it exactly works.

d) Given SELECT Statement:

SELECT EMP_ID, COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID
GROUP BY SALARY;

Yes it exactly works.

__________________________________________________________

2. Use EMPLOYEE_PAY_TBL to construct SQL statements to solve the following exercises.

a. What is the average salary?

SELECT AVG(SALARY) FROM EMPLOYEE_PAY_TBL;

b. What is the maximum bonus?

SELECT AVG(BONUS) FROM EMPLOYEE_PAY_TBL;

________________________________________________________________________

c. What are the total salaries?

SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL;

d. What is the minimum pay rate?

SELECT MIN(PAY_RATE) FROM EMPLOYEE_PAY_TBL;

e. How many rows are in the table?

SELECT COUNT(*) FROM EMPLOYEE_PAY_TBL;

________________________________________________________________________

3. How many employees do we have whose last names begin with a G?

SELECT COUNT(LAST_NAME) FROM EMPLOYEE_TBL
WHERE LAST_NAME LIKE ‘G%’;

4. If every product cost $10.00, what would be the total dollar amount for all orders?

SELECT SUM(QTY)*10
FROM ORDERS_TBL;

Note: Only one question per post, as per the Cramster rule. Please Ask remaining Questions in other post.

I Hope this will helpful you....

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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