Dbms Lab Note
Dbms Lab Note
1. CREATE TABLE
employee` (
2.Q. Write a query to Name the column headings EMP#, Employee, Job and Hire date, respectively.
Run the query.
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
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;
10.Q. Create a unique listing of all jobs that are in department 30.
FROM employees
12.Q. Write a query to display the employee name, department name, and location of all employee
who earn a commission.
13.Q. Write a query to display the name, job, department number and department name for all
employees who works in 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.
(SELECT max(salary+commission)
18.Q. Create a student database table using create command using Regd. No as Primary Key , insert
data of your class.