SQL Practical Question

Create table “EMP” as per the following specification:

COLUMN NAME	DATATYPE	SIZE	CONSTRAINTS
Emp_No	Integer	4	Primary Key
Emp_Name	Varchar2	20	Not Null
Job	Varchar2	10
Hiredate	Date	dd-mm-yyyy
Salary	Float	10,2	Salary>10000
Commission	Integer	4
DeptNo	Integer	2	10,20 or 30

Job Types are:

·         President

·         Manager

·         Clerk

·         Salesman

Commission given only to ‘salesman’.

QUESTIONS

1)      Create the above table including its constraints.

2)      Insert 10 records into it.

3)      Display the name , job, salary of all employee having salary between 15000 and 25000

4)      Display the employee name , department and total salary calculated as (Salary +comm.) for all employees.

5)      Display the content of the table in the descending order of salary and ascending order of name.

6)      Display Emp_no , Emp_Name, annual salary under the heading “ANNUAL SALARY” where salary is calculated as (salary + commissim*12)

7)      Display the maximum salary from the table department wise.

8)      Display the name and job of all the employees whose name starts with ‘A’ and ends with ‘A’.

9)      Display the name i.e. Emp_Name, job and hiredate of the employee having job as manager, salesman or clerk.

10)   Count the no. of employee having salary greater than 15000.

11)   Create view DEPT20 with Emp_No, Emp_Name and salary of employee for department no. 20.

12)   To display the information of all the employees who were hired during year 1995.

13)   Show the average salary for all the departments with more than three people for a job.

14)   Write a SQL command to add the column named as remark with datatype variable length string (Varchar(20)).

15)   Increase the salary of all employees by 20% who were hired before 1^st Jan 1995 and belongs to job “ANALYST”.