1. Q. 1. Show the Structure of DEPT. Select all data from DEPT table.

Create a query to display

unique jobs from the EMP table

1. CREATE TABLE

employee` (

`emp_id` VARCHAR(15) NOT NULL,

`emp_name` VARCHAR(45) NULL,

`job_name` VARCHAR(45) NULL,

`manager_id` VARCHAR(45) NULL,

manager_name` VARCHAR(45) NULL,

`hire_date` DATE NULL,

`salary` VARCHAR(45) NULL,

`commission` VARCHAR(45) NULL,

`dep_id` VARCHAR(45) NULL,

`dep_number` VARCHAR(45) NULL

`dep_name` VARCHAR(45) NULL

PRIMARY KEY (`emp_id`));

INSERT INTO `biswa`.`employee` (`emp_id`, `emp_name`,

`job_name`,`manager_id``manager_name``hire_date`, `salary`, `dep_id`, `dep_number`,
`dep_name`)

VALUES ('68319', ' KAYLING', 'PRESIDENT', '1991-11-18', '6000.00', '1001');

INSERT INTO `biswa`.`employee` (`emp_id`, `emp_name`, `job_name`,

`manager_id`,`manager_name` `hire_date`, `salary`, `dep_id`)

VALUES ('66928', 'BLAZER', 'MANAGER', '68319', '1991-05-01', ' 2750.00', '3001');

INSERT INTO `biswa`.`employee` (`emp_id`, `emp_name`, `job_name`, `manager_id`, `hire_date`,

`salary`, `dep_id`)

VALUES ('67832', 'CLARE', 'MANAGER', '68319', '1991-06-0', '2550', '1001');

INSERT INTO `biswa`.`employee` (`emp_id`, `emp_name`, `job_name`,

`manager_id`, `hire_date`, `salary`, `dep_id`)

VALUES ('65646', 'JONAS', 'MANAGER', '68319', ' 1991-04-02', '2957', '2001');

2.Q. Write a query to Name the column headings EMP#, Employee, Job and Hire date, respectively.
Run the query.

Ans- Sql> select * from employee;

3.Q. Create a query to display the Name and salary of employees earning more than Rs.2850.Save
the query and run it.
Sql> select emp_name,salary from employee where salary>2850;

4.Q. Create a query to display the employee name and department no. for employee no. 7566.
Ans- Sql> select emp_name,dept_no from employee where dept_no=7566;

5.Q. Display the employee name, job and start date of employees hire date between Feb.20.1981
and May 1, 1981. Order the query in ascending order of start date.

Ans- Sql> select emp_name, job, hiredate where hiredate between '20-FEB-81' AND '01-MAY-81'
from emp ASND;

6.Q. Display the name and title of all employees who don’t have a Manager.

Ans- Sql> select emp_name,emp_lname from employee NOT IN (SELECT DISTINCT manager_name
FROM employee WHERE manager_name IS NOT NULL);

7.Q. Display the name, salary and comm. For all employee who earn comm. Sort data in descending
order of salary and comm.
Ans- Sql> select emp_name,salary,commission from employee

ORDER BY sal,comm desc WHERE comm>0;

8.Q. Display the name job, salary for all employees whose job is Clerk or Analyst their salary is not
equal to Rs.1000, Rs.3000, Rs.5000.

Ans- Sql> select ename from emp where job=’CLERK’ OR JOB=’SALESMAN’ OR JOB=’ANALYST’ AND
SAL<>1000<>3000<>5000;

9. Q. Write a query to display the date. Label the column DATE.

Ans- Sql> select emp_name,hire_date from employee;

10.Q. Create a unique listing of all jobs that are in department 30.

Ans- Sql> SELECT DISTINCT job_name

FROM employees

WHERE dep_id IN (30)

ORDER BY job_name DESC;

11.Q. Write a query to display the name, department number and department name for all
employees.

Ans- Sql> select emp_name,dept_number,dept_name from employee;

12.Q. Write a query to display the employee name, department name, and location of all employee
who earn a commission.

Ans- Sql> select emp_name,dept_name,address from employee ORDER BY comm >0;

13.Q. Write a query to display the name, job, department number and department name for all
employees who works in DALLAS.

Ans- Sql> Select emp_name,job,dept_no,dept_name from employee where dept_name= DALLAS;

14.Q Write a query to display the number of people with the same job. Save the query @ run it.
Ans- Sql> SELECT count(emp_name) from employee where job_name = employee.job_name;

15.Q. Create a query to display the employee name and hire date for all employees in same
department.

Ans- Sql> select emp_name,hire_date count (dept_no) from employee group by dept_no;

16.Q. Display the employee name and salary of all employees who report to KING.
Ans- Sql> SELECT emp_name AS emp_name,(SELECT emp_name FROM employee
whereemp_id=manager_id) AS manager_name FROM employee ;

17. Display the mane, department name and salary of any employee whose salary and commission
matches both the salary and commission of any employee located in DALLAS.

Ans- Sql> SELECT e.emp_name,

e.salary, e.commission FROM employees e WHERE

(SELECT max(salary+commission)

FROM employees) >= ANY

(SELECT salary FROM employees);

18.Q. Create a student database table using create command using Regd. No as Primary Key , insert
data of your class.

Ans- Sql> CREATE TABLE STUDENT

(RNO INT PRIMARY KEY, NAME VARCHAR(60),CLASS INT,

SECTION VARCHAR(5),GENDER VARCHAR(10),MARKS INT);

Ans- Sql> INSERT INTO student(' 'RNO','NAME', 'CLASS' ,'SECTION', 'GENDER'

,'MARKS') VALUES( '1232','RAMA',IV','B','MALE',……………………….);

19.Q. Delete the information of student having roll No -15 and City- Bhubaneswar. Rename the
Student database table to STUDENT INFORMATION.

Ans- Sql> DELETE from student where Roll_No=15 AND city='bhubaneswar';

Ans- Sql> ALTER TABLE student RENAME student_information;