The hr department needs a report with the following

This preview shows page 21 - 23 out of 29 pages.

5. The HR department needs a report with the following specifications: Last name and department ID of all the employees from the EMPLOYEES table, regardless of whether or not they belong to a department Department ID and department name of all the departments from the DEPARTMENTS table, regardless of whether or not they have employees working in them Write a compound query to accomplish this. SELECT last_name,department_id,TO_CHAR(null) FROM employees UNION SELECT TO_CHAR(null),department_id,department_name FROM departments; Practice 8: Solutions The HR department wants you to create SQL statements to insert, update, and delete employee data. As a prototype, you use the MY_EMPLOYEE table, before giving the statements to the HR department. Insert data into the MY_EMPLOYEE table. 1. Run the statement in the lab_08_01.sql script to build the MY_EMPLOYEE table to be used for the lab.
2. Describe the structure of the MY_EMPLOYEE table to identify the column names. DESCRIBE my_employee 3. Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the INSERT clause. ID LAST_NAME FIRST_NAME USERID SALARY 1 Patel Ralph rpatel 895 2 Dancs Betty bdancs 860
3 Biri Ben bbiri 1100 4 Newman Chad cnewman 750 5 Ropeburn Audrey aropebur 1550 INSERT INTO my_employee VALUES (1, 'Patel', 'Ralph', 'rpatel', 895); 4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list. This time, list the columns explicitly in the INSERT clause.
5. Confirm your addition to the table. *
6. Write an insert statement in a dynamic reusable script file named loademp.sql to load rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the first seven characters of the last name to produce the user ID. Save this script to a file named lab_08_06.sql.
22

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture