0% found this document useful (0 votes)
27 views4 pages

Dbms Lab Note

Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
27 views4 pages

Dbms Lab Note

Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

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;

You might also like