Run DROP TABLE NEW_EMP; CREATE TABLE NEW_EMP ( ID NUMBER NOT NULL, LAST_NAME VAR
ID: 3913394 • Letter: R
Question
Run
DROP TABLE NEW_EMP;
CREATE TABLE NEW_EMP (
ID NUMBER NOT NULL,
LAST_NAME VARCHAR2(15),
FIRST_NAME VARCHAR2(15),
SALARY NUMBER,
DEPTNO VARCHAR2(2) NOT NULL,
USERID CHAR(7),
CONSTRAINT NEW_EMP_FKS FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
CONSTRAINT NEW_EMP_PKS PRIMARY KEY (ID));
7. UPDATE the NEW_EMP table USERID column based on the following rules: Concatenate the first three characters of the FIRST_NAME column and the first four characters of the LAST_NAME column to produce the USERID column. Write a query to display the result and permanently save the change to the database table.
8. Create a script file based on the following data and name it as loadme.sql and save it under C:. ID LAST_NAME FIRST_NAME SALARY DEPTNO 6 Lopez Jennifer 4000 10 7 Jones Jacksons 5500 30 8 Williams Andy 5000 30 9 Newman Paul 7500 30
9. Please run your loadme.sql script file under your SQL>, and then write a query to display the final result.
10. Change the last name of employee 3 from ‘Biri’ to ‘Drexler’ from the new_emp table, and then write a query to display the final result and permanently save your data change to database table.
11. Raise employee’s salary a 1000 more for all employees with a salary less than3000, and write a query to display the final result.
12. Delete all employee records if they don’t have USERID data in the NEW_EMP table, and write a query to display the final result. 13. Commit all pending changes. (Control data transaction to the MY_EMPLOYEE tables.) 14. Write a SQL code to empty the entire table.
Explanation / Answer
7. UPDATE NEW_EMP SET USERID = SUBSTRING(FIRST_NAME, 1, 3) || SUBSTRING(LAST_NAME, 1, 4);
SELECT * FROM NEW_EMP;
10. UPDATE NEW_EMP SET LAST_NAME = 'Drexler' WHERE ID = 3 AND LAST_NAME = 'Biri';
SELECT * FROM NEW_EMP;
11. UPDATE NEW_EMP SET SALARY = SALARY + 1000 WHERE SALARY < 3000;
SELECT * FROM NEW_EMP;
12. DELETE FROM NEW_EMP WHERE USERID IS NULL;
SELECT * FROM NEW_EMP;
13. COMMIT;
14. TRUNCATE TABLE NEW_EMP;
NOTE: I could not understand the problems 8 and 9. Could you please explain it a little bit, like what data to load exactly? For time being, I have skipped them, but once you explain them, I will try to answer them too.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.