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 1st Jan 1995 and belongs to job “ANALYST”.