RDBMS EXERCISES2

 

RDBMS EXERCISES CONTINUED....

EXERCISE 4:

Create a table called Employee with the following structure. Empno (Number), Ename (Varchar2(20)), Job (Varchar2(20) ), Sal (Number) 

(i) Add a column DoJ (DATE) with the Employee table. 

(ii) Insert any five records into the table. 

(iii) Update the column details of job 

(iv) Rename the column Sal as Emp_Salary of Employ table using alter command.

 (v) Delete the employee whose empno is 101.

ANSWER:

Create the Employee Table

SQL>

CREATE TABLE Employee (

    Empno NUMBER PRIMARY KEY,

    Ename VARCHAR2(20),

    Job VARCHAR2(20),

    Sal NUMBER

);

(i) Add a Column DoJ (DATE) to the Employee Table

SQL>

ALTER TABLE Employee ADD DoJ DATE;

(ii) Insert Any Five Records into the Table

SQL>

INSERT INTO Employee (Empno, Ename, Job, Sal, DoJ) VALUES (100, 'Alice', 'Developer', 50000, TO_DATE('2020-01-15', 'YYYY-MM-DD'));

INSERT INTO Employee (Empno, Ename, Job, Sal, DoJ) VALUES (101, 'Bob', 'Manager', 75000, TO_DATE('2019-03-20', 'YYYY-MM-DD'));

INSERT INTO Employee (Empno, Ename, Job, Sal, DoJ) VALUES (102, 'Charlie', 'Analyst', 60000, TO_DATE('2018-07-22', 'YYYY-MM-DD'));

INSERT INTO Employee (Empno, Ename, Job, Sal, DoJ) VALUES (103, 'David', 'Tester', 45000, TO_DATE('2021-11-10', 'YYYY-MM-DD'));

INSERT INTO Employee (Empno, Ename, Job, Sal, DoJ) VALUES (104, 'Eve', 'HR', 55000, TO_DATE('2017-05-05', 'YYYY-MM-DD'));

(iii) Update the Column Details of Job

SQL>

UPDATE Employee SET Job = 'Senior Developer' WHERE Empno = 100;

SQL>

UPDATE Employee SET Job = 'Lead Manager' WHERE Empno = 101;

(iv) Rename the Column Sal as Emp_Salary Using ALTER Command

SQL>

ALTER TABLE Employee RENAME COLUMN Sal TO Emp_Salary;

(v) Delete the Employee Whose Empno is 101

SQL>

DELETE FROM Employee WHERE Empno = 101;

EXERCISE 5:

Create an Emp table with the following fields: (EmpNo, EmpName, Job, Basic, DA, HRA, PF, GrossPay, NetPay)

 (Calculate DA as 30% of Basic and HRA as 40% of Basic) (

i)               Insert Five Records and calculate GrossPay and NetPay. (Gross_pay as Basic Salary + HRA + DA and Net_Pay as Gross_Pay – PF.)

ii)             Display the employees whose Basic is lowest in each department.

iii)           If NetPay is less than < Rs.10,000 add Rs.1,200 as special allowances.

iv)            Display the employees whose GrossPay lies between 10,000 and 20,000

v)             Display all the employees who earn maximum salary.

ANSWER:

Create the Emp Table

SQL>

CREATE TABLE Emp (

    EmpNo INT PRIMARY KEY,

    EmpName VARCHAR(50),

    Job VARCHAR(50),

    DeptNo INT,

    Basic DECIMAL(10, 2),

    DA DECIMAL(10, 2),

    HRA DECIMAL(10, 2),

    PF DECIMAL(10, 2),

    GrossPay DECIMAL(10, 2),

    NetPay DECIMAL(10, 2)

);

i) Insert Five Records and Calculate GrossPay and NetPay

To calculate DA as 30% of Basic and HRA as 40% of Basic, and then calculate GrossPay and NetPay, we will use the following queries:

SQL>

INSERT INTO Emp (EmpNo, EmpName, Job, DeptNo, Basic, DA, HRA, PF, GrossPay, NetPay) 

VALUES

(1, 'Alice', 'Developer', 1, 30000, 30000 * 0.3, 30000 * 0.4, 3000, 30000 + (30000 * 0.3) + (30000 * 0.4), (30000 + (30000 * 0.3) + (30000 * 0.4)) - 3000),

(2, 'Bob', 'Manager', 2, 50000, 50000 * 0.3, 50000 * 0.4, 5000, 50000 + (50000 * 0.3) + (50000 * 0.4), (50000 + (50000 * 0.3) + (50000 * 0.4)) - 5000),

(3, 'Charlie', 'Analyst', 1, 20000, 20000 * 0.3, 20000 * 0.4, 2000, 20000 + (20000 * 0.3) + (20000 * 0.4), (20000 + (20000 * 0.3) + (20000 * 0.4)) - 2000),

(4, 'David', 'Tester', 2, 25000, 25000 * 0.3, 25000 * 0.4, 2500, 25000 + (25000 * 0.3) + (25000 * 0.4), (25000 + (25000 * 0.3) + (25000 * 0.4)) - 2500),

(5, 'Eve', 'HR', 3, 40000, 40000 * 0.3, 40000 * 0.4, 4000, 40000 + (40000 * 0.3) + (40000 * 0.4), (40000 + (40000 * 0.3) + (40000 * 0.4)) - 4000);

(ii) Display the employees whose Basic is lowest in each department.

SQL>

SELECT EmpNo, EmpName, Job, DeptNo, Basic  FROM Emp e1 WHERE Basic=(SELECT MIN(Basic) FROM Emp e2 WHERE e2.DeptNo = e1.DeptNo);

(iii) If NetPay is less than Rs.10,000, add Rs.1,200 as special allowances.

SQL>

UPDATE Emp SET NetPay = NetPay + 1200 WHERE NetPay < 10000;

(iv) Display the employees whose GrossPay lies between 10,000 and 20,000.

SQL>

SELECT EmpNo, EmpName, Job, GrossPay FROM Emp WHERE GrossPay BETWEEN 10000 AND 20000;

(v) Display all the employees who earn maximum salary.

SQL>SELECT EmpNo, EmpName, Job, GrossPay FROM Emp WHERE GrossPay = (    SELECT MAX(GrossPay) FROM Emp);

 

EXERCISE 6:

Create a table called Branch table.

Name                          Type

Branch name               Varchar2(20)

Branch_city                 Varchar2(20)

Asserts                        Number

(i)             Increase the size of data type for Asserts to the branch

(ii)           Add and drop a column to the Branch table

(iii)         Insert values to the table.

(iv)          Update the Branch_name column

(v)           Delete any two columns from the table

ANSWER

Create the Branch Table

SQL>

CREATE TABLE Branch (

    Branch_name VARCHAR2(20),

    Branch_city VARCHAR2(20),

    Asserts NUMBER

);

(i)             Increase the size of data type for Asserts to the branch

SQL>ALTER TABLE Branch MODIFY Asserts NUMBER(15, 2);

(ii)           Add and drop a column to the Branch table

SQL> ALTER TABLE Branch ADD Branch_code VARCHAR2(10);

SQL>ALTER TABLE Branch DROP COLUMN Branch_code;

(iii)         Insert values to the table.

SQL>

INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('Main', 'New York', 5000000);

SQL>

INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('West', 'Los Angeles', 3000000);

SQL>

INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('East', 'Boston', 4000000);

SQL>

INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('South', 'Houston', 3500000);

SQL>

INSERT INTO Branch (Branch_name, Branch_city, Asserts) VALUES ('North', 'Chicago', 4500000);

 

(iv)          Update the Branch_name column

SQL> UPDATE Branch SET Branch_name = 'Central' WHERE Branch_name = 'Main';

 

(v)           Delete any two columns from the table

SQL> ALTER TABLE Branch DROP COLUMN Branch_city;

SQL>ALTER TABLE Branch DROP COLUMN Asserts;


EXERCISE 7:

Create a table called Stock table

Name             Type

Sid                  Number

Sname            Varchar2(20)

Rating            Varchar2(20)

Create the Stock Table

SQL>

CREATE TABLE Stock (

    Sid NUMBER,

    Sname VARCHAR2(20),

    Rating VARCHAR2(20));

(i)             Add column age to the Stock table.

SQL> ALTER TABLE Stock ADD age NUMBER;

(ii)           Insert values into the Stock table.

SQL>

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (1, 'ItemA', '5', 2);

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (2, 'ItemB', '9', 5); 

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (3, 'ItemC', '7', 3); 

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (4, 'ItemD', '10', 4); 

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (5, 'ItemE', '6', 1);

(iii)         Delete the row with Rating >8.

SQL>

DELETE FROM Stock WHERE TO_NUMBER(Rating) > 8;

(iv)          Update the column details of Stock.

SQL>

UPDATE Stock SET Sname = 'ItemUpdated', age = 10 WHERE Sid = 3;

(v)           Insert null values into the table.

SQL>

INSERT INTO Stock (Sid, Sname, Rating, age) VALUES (6, NULL, NULL, NULL);

 

EXERCISE 8:

Create a table called Customer table

Name             Type

Cust_name     Varchar2(20)

Cust_street     Varchar2(20)

Cust_city        Varchar2(20)

 

Create the Customer Table

SQL>

CREATE TABLE Customer (

    Cust_name VARCHAR2(20),

    Cust_street VARCHAR2(20),

    Cust_city VARCHAR2(20)

);

QUERIES:

(i)             Insert records into the table.

SQL>

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('John Doe', '123 Elm St', 'New York');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Jane Smith', '456 Oak St', 'Los Angeles');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Alice Johnson', '789 Pine St', 'Chicago');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Bob Brown', '101 Maple St', 'New York');

INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Eve Davis', '202 Birch St', 'Houston');

(ii)           Add salary column to the table.

SQL> ALTER TABLE Customer ADD salary NUMBER(10, 2);

(iii)         Alter the table column domain.

SQL> ALTER TABLE Customer MODIFY Cust_street VARCHAR2(50);

(iv)          Drop salary column of the customer table.

SQL> ALTER TABLE Customer DROP COLUMN salary;

(v)           Delete the rows of customer table whose cust_city is “New York”.

SQL> DELETE FROM Customer WHERE Cust_city = 'New York';

 

Comments

Post a Comment

Popular posts from this blog

RDBMS LAB EXERCISES WITH ANSWER

  EXERCISE:1 Create a table called Customer table   Name Type   Cust_name Varchar2(20)   Cust_street Varchar2(20)   Cust_city Varchar2(20)   (i) Insert records into the table.   (ii) Add salary column to the table.   (iii) Alter the table column domain.   (iv) Drop salary column of the customer table.   (v) Delete the rows of customer table whose cust_city is  “New York”. 💠 Step-by-step SQL Queries: ✅ (1) Create the Customer table SQL> CREATE TABLE Customer (     Cust_name VARCHAR2(20),     Cust_street VARCHAR2(20),     Cust_city VARCHAR2(20) ); ✅ (2) Insert records into the table SQL> INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Alice', '1st Avenue', 'New York'); INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Bob', '2nd Street', 'Los Angele...
Read more

DATA STRUCTURES-UNIT IV

Image
  UNIT IV   INTRODUCTION TO ALGORITHMS: Definition   of   Algorithms-   Overview   and   importance   of   algorithms-   pseudocode conventions, Asymptotic notations, practical complexities. Graph   Applications:   Breadth   first   traversal   –   Depth   first   traversal-   -Single   source shortest path – Minimal spanning trees – prim’s and kruskal’s algorithms   INTRODUCTION TO ALGORITHMS   4.1)Definition   of   Algorithms   ●       An algorithm is a well-defined, step-by-step procedure or set of rules used to solve a particular problem or perform a specific task. ●       Algorithms are widely applied in various fields like mathematics, computer science, and data processing. 4.2)Overview   and   importance   of   algorithms ●       In data...
Read more

DATA STRUCTURES-UNIT V

Image
                                                                                                      UNIT V DIVIDE AND CONQUER ALGORITHMS: General Method – Binary Search- Quick Sort- Merge Sort. BACKTRACKING: General method, 8 Queens, Graph coloring, Hamiltonian cycle. DIVIDE AND CONQUER ALGORITHMS: General Method Divide and Conquer   Algorithm  is a problem-solving technique used to solve problems by dividing the main problem into subproblems, solving them individually and then merging them to find solution to the original problem. Divide and Conquer is mainly useful when we divide a problem into independent subproblems. If we have overlapping subproblems, then w...
Read more