How to write programs in sql for the following question: 1. how to show columns

Question

How to write programs in sql for the following question:

1. how to show columns in the EMPLOYEES table. Hint: use the DESCRIBE command.

2. how to create new table called EX25 that has the same column names and data types as the EMPLOYEES table.
Hint: use CREATE TABLE and the output from the previous step.

3. how to show all the columns in the EX25 table. Hint: use the DESCRIBE command.

4. how to Insert a copy of all rows from the EMPLOYEES table into the EX25 table.
Hint: Use INSERT INTO .... SELECT * FROM .....

5.how to Insert a new row in the EX25 table and populate all columns. Make up any data you want but populate the FIRST_NAME and LAST_NAME columns with your first name and last name and be sure your manager is Steven King.
Hint: To find the MANAGER_ID, look for Steven King's EMPLOYEE_ID.

6. how to update the EX25 table by giving a 10% raise to all employees whose manager is Steven King. DO NOT use the manager's name to do the update - use only the MANAGER_ID column in the WHERE clause for the update.
Hints: Use UPDATE .......;
Use WHERE MANAGER_ID = ...;
To find the MANAGER_ID, look for Steven King's EMPLOYEE_ID;
a 20% raise is salary X 1.1.

7. company has outsourced the Information Technology department and fired all workers with a JOB_ID of IT_PROG except for the department manager. Remove those employees from the EX10 table. Use only the MANAGER_ID column in the WHERE clause. Do NOT use JOB_ID in the WHERE clause.
Hint: use DELETE FROM .... WHERE MANAGER_ID = ......

8. Show the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, MANAGER_ID, DEPARTMENT_ID for the manager of the Information Technology department who did not get fired.
Hint: use SELECT ..... FROM ....... WHERE .......

Explanation / Answer

1)
DESCRIBE EMPLOYEES;

2)SELECT *
INTO EX25
FROM EMPLOYEES;

3)
DESCRIBE EX25;

4)
INSERT INTO EX25 SELECT * FROM EMPLOYEES;

5)
INSERT INTO EX25 (firstName, LastName)
SELECT firstName, LastName FROM EMPLOYEES;

6)
UPDATE EX25
SET salary=10
WHERE MANAGER_ID=12345;

7)
DELETE FROM EX25
WHERE MANAGER_ID=12345;

8)
SELECT EMPLOYEE_ID, FIRSTNAME,LASTNAME,JOB_ID, MANAGER_ID,DEPARTMENT_ID
FROM EX25 WHERE FIRED="no";

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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