Scribd Logo
Upload

Welcome to Scribd!

  • 50 views

    Week004-DML-LabExer001 Rivera Dennis

    Uploaded by

    Mary Jane Pagay
    This document contains instructions for a database management systems laboratory exercise. Students are directed to perform various SQL commands to manipulate a table with employee data, including: 1) Creating the employee table with various columns 2) Inserting sample employee records 3) Modifying the table structure by adding and removing columns 4) Updating existing employee records by changing values 5) Deleting records from the table Students are asked questions about the validity and effects of each SQL statement to test their understanding.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Week004-DML-LabExer001 Rivera Dennis

    Uploaded by

    Mary Jane Pagay
    50 views6 pages
    This document contains instructions for a database management systems laboratory exercise. Students are directed to perform various SQL commands to manipulate a table with employee data, including: 1) Creating the employee table with various columns 2) Inserting sample employee records 3) Modifying the table structure by adding and removing columns 4) Updating existing employee records by changing values 5) Deleting records from the table Students are asked questions about the validity and effects of each SQL statement to test their understanding.

    Original Title

    Week004-DML-LabExer001_Rivera_Dennis

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions for a database management systems laboratory exercise. Students are directed to perform various SQL commands to manipulate a table with employee data, including: 1) Creating the employee table with various columns 2) Inserting sample employee records 3) Modifying the table structure by adding and removing columns 4) Updating existing employee records by changing values 5) Deleting records from the table Students are asked questions about the validity and effects of each SQL statement to test their understanding.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    50 views6 pages

    Week004-DML-LabExer001 Rivera Dennis

    Uploaded by

    Mary Jane Pagay
    This document contains instructions for a database management systems laboratory exercise. Students are directed to perform various SQL commands to manipulate a table with employee data, including: 1) Creating the employee table with various columns 2) Inserting sample employee records 3) Modifying the table structure by adding and removing columns 4) Updating existing employee records by changing values 5) Deleting records from the table Students are asked questions about the validity and effects of each SQL statement to test their understanding.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 6

    Course Code CS351

    Database Management System


    Description
    1
    College / Department:
    Quiz No. 001
    Online Education
    Laboratory Exercise

    Direction: Copy and paste the PL/SQL code on the space provided after each
    questions ad explain each number with question that requires you to answer in essay
    form.
    1. Create a table name as: EMPLOYEE_RECORD with the following column as shown below:
    ID FIRSTNAME LASTNAME EMAIL JOB_ID SALARY DEPT_ID
    Number(6) Varchar(10) Varchar(10) Varchar(15) Varchar15 Number(6) Number(6)
    PK NOT NULL

    2. Insert the following Data as shown in the table below:


    ID FIRSTNAME LASTNAME EMAIL JOB_ID SALARY DEPT_ID
    100 Steven King Sking Ad_pres 240000 10
    101 Nena Kochar Nkochar Ad_vp 17000 20
    102 Lex De haan Ldehaan Ad_vp 17000 50
    103 Alexander Hunold Ahunold It_prog 9000 60
    104 Bruce Ernst Bernst It_prog 6000 80
    107 Diana Lorentz Dlorentz St_man 5800 90
    124 Kevin Mourgos Kmourgos St_clerk 5800 110
    141 Trina Rajs Trajs St_clerk 5800 190
    142 Curtis Davias Cdavias St_clerk 5800 191
    143 Randal Matos Rmatos St_man 4200 195

    3. Add new column name as ADDRESS data type char size 20.

    4. After you add a new column address view now the table? What happen to the addresses of all old
    employees? Explain the reason why?
     Null, Because it was empty and no details added.
    5. Add new record as shown below:
    10 JULY SORIANO MSORIANO TEACHER 2600 80 BRGY.PAMBUAN GAPAN
    5

     Is inserting a new record in No.5 is possible? Why and why not? Explain the reason why?
     Yes, Because the details are the same from the previous record and having 7 columns.

    6. Add again a new record as shown below:


    10 ARRIANE SALAMAT ASALAMAT TEACHER 2600 80 BRGY.MANGINO
    5

     Is inserting a new record in No.6 is possible? Why and why not? Explain the reason why?
     No, It is not possible there is an error because of duplicating the primary key.

    7. Add again a new record as shown below:


    10 RAYCHELOU VALENCI RVALENCIA DEAN 5000 80 CANIOGAN ST. DR.
    6 A 0 SIXTO BLDG. PASIG

     Is inserting a new record in No. 8 is possible? If not perform some modification on table structure in
    order to insert the record of Ms. Valencia.
     No, Because the VARCHAR limitation is only 20. It must update the VARCHAR to 34. Check
    the output below.

    Note: For succeeding activity: make sure to view you table and study its content.
    8. All employees that having a salary of 5800 should be assigned in one dept_id 90, update their
    departments to 90.

    9. Trina got married, after her leave Ms. Trina ask you to update her record in the database from RAJS to
    DE LEON.
    10. After serving the company for three year Randal got promoted as the newest ad_pres and with a new
    salary of 250000. Update his record using one SQL only.

    11. Since Mr. Randal replace the position of Mr. Steven, delete the record of Mr. Steven since he is no
    longer connected to the company.

    12. All employees are given an additional 2% increase in their salaries. Create an SQL statement that would
    change the employees’ salaries with additional 10%. Note: Use rows with the same values in order to
    limit the number of query to be use.
     Not yet discussed in the lecture. Mam can I do this after the discussion on how to add the
    increment please  . I am sorry Mam Mary.
    13. Email address is not being use, and save space you have to delete the said column.

    14. Select the table now and draw the final output after you perform the DDL and DML statements.

    15. What will happen if the accidentally type DELETE * FROM EMPLOYEES without issuing WHERE
    condition?
     Error, If I add (*) but if I removed the (*) all rows are deleted.

    You might also like