DBMS LAB ASSIGNMENT 1

LAB EXERCISES

LAB 1:

SQL

1. Select all information of various tables

1.1 Salgrade 1.2 Emp 1.3 Dept

2. See the structure of the above tables.

3. List all information whose salary in between 1000 and 3000. Use EMP table.

4. List name and salary only of employees. Use EMP table.

5. List the above in sorted order. Sort by name. Use EMP table.

6. List all employee name and dept no who are in dept 10 and 30. Use EMP table.

7. List name ,job of all clerks in dept 20 . Use EMP table.

8. List name,job of all clerks in dept 20 and 30. Use EMP table.

9. Display all employees whose name starts with ‘S’. Use EMP table.

10. Display all employees whose name has four characters only. Use EMP table.

11. Display all employees whose name ends with ‘L’. Use EMP table.

12.List all employees who have a manager. Use EMP table.

13.List all employees who do not have a manager. Use EMP table.

14. List name and Total of salary i.e sal+commission. Use EMP table.

15.List name and Annual Salary i.e sal*12. Use EMP table.

16. List all employees who joind in the year 1991. Use EMP table.

17. Display data as who , what ,when and how much display should look like

Eg: SMITH HAS HELD THE POSITION OF CLERK IN DEPARTMENT 20 SINCE ’12-OCT-1990’AND EARNS 1500.

SQL OPERATORS:

18. Supply values at runtime and display all employees in the user specified job title.

19. Find all employees joined on a specified date entered by the user.

20. Generate a query that accepts two dates i.e. the joining dates of EMP(range) at runtime and gives the output. Rerun it and then change the substitution variables with && and return it twice.

Defining and accepting commands:

21. Define one variable i.e the REM= ‘sal*12+NVL(comm.,0)

Use the variable to find all employees who earn $10000 a year or more.

TABLES

22. Create a EMP10 table which has the following fields

Empno NUMBER(2)

Ename VARCHAR2(25)

Date_join DATE

Deptno NUMBER(2)

Salary NUMBER(10,2)

Job VARCHAR2(10)

Comm NUMBER(7,2)

23. Create another table with the following constraints

Empno NUMBER(2)

Ename VARCHAR2(25)

Date_join DATE

Deptno NUMBER(2)

Salary NUMBER(10,2)

Job VARCHAR2(10)

Comm NUMBER(7,2)

24. Give different field names to the table. Create a table emp20 with only name, sal and job from

EMP table with employees of department 20.