Create a table Salary with following column and data and Write a query using above tables to display the Emp_no, name and net salary of each employee of a particular month

Question

Create a table Salary with the following column and data and Write a query using above tables to display the Emp_no, name and net salary of each employee of a particular month

 

in progress 1
Garima Singh 55 years 1 Answer 1018 views Newbie 0

Answer ( 1 )

  1. Garima Singh
    0
    Reply
    Create a table Salary with following column and data and Write a query using above tables to display the Emp_no, name and net salary of each employee of a particular month

    SQL> CREATE TABLE Salary
    2 (
    3 Emp_no number
    4 (3) NOT NULL,
    5 Basic decimal
    6 (10,2) DEFAULT NULL,
    7 DA decimal
    8 (10,2) DEFAULT NULL,
    9 Deduction decimal
    10 (10,2) DEFAULT NULL,
    11 Salary_Date date NOT NULL,
    12 FOREIGN KEY (Emp_no) REFERENCES employee(Emp_no_PrimaryKey)
    13 );

    Table created.

    SQL> INSERT INTO Salary VALUES(101, 25000.00, 5000.00, 2500.00, ’30-Jun-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(102, 50000.00 ,10000.00 ,5000.00 ,’30-Jun-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(103, 30000.00 ,6000.00 ,3000.00, ’30-Jun-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(104 ,35000.00, 7000.00 ,35000.00 ,’30-Jun-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(105 ,40000.00 ,8000.00 ,4000.00 ,’30-Jun-2011′);

    1 row created.

    SQL>
    SQL> INSERT INTO Salary VALUES(101 ,26000.00, 5200.00, 2600.00 ,’31-Jul-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(102, 52000.00, 14000.00, 5200.00, ’31-Jul-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(103, 31000.00, 6200.00, 3100.00, ’31-Jul-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(104, 38000.00, 7400.00, 3800.00, ’31-Jul-2011′);

    1 row created.

    SQL> INSERT INTO Salary VALUES(105, 41000.00, 8200.00, 4100.00, ’31-Jul-2011′);

    1 row created.

    SQL> ALTER TABLE salary ADD(NET_SALARY decimal(10,2));

    Table altered.

    SQL> UPDATE salary SET net_salary=Basic+DA-Deduction;

    10 rows updated.
    SQL> select * from salary;

    SQL> select employee.emp_no, emp_name, basic from employee,salary where employee.emp_no=salary.emp_no;


    Attachment

Leave an answer

Browse
Browse