DBMS Lab Assignment
Data Definition Language (DDL) Commands
Create a table called EMP with the following structure.
| Name | Type |
|---|---|
| EMPNO | NUMBER(6) |
| ENAME | VARCHAR(20) |
| JOB | VARCHAR(10) |
| DEPTNO | NUMBER(3) |
| SAL | NUMBER(7,2) |
Allow NULL for all columns except ename and job.
empno as the primary key.
Deptno as the foreign key.
Add a column EXPERIENCE to the emp table with data type number and allow null.
Modify the column width of the job field of the emp table.
Create a table called DEPT with the following structure.
| Name | Type |
|---|---|
| DEPTNO | NUMBER(2) |
| DNAME | VARCHAR(10) |
| LOC | VARCHAR(10) |
Deptno as the primary key.
Create the EMP1 table with ename and empno, add constraints to check the empno value while entering (i.e) empno > 100.
Drop a column EXPERIENCE to the emp table.
Truncate the emp table and drop the dept table.
Data Manipulation Language (DML) Commands
Insert a single record into the DEPT table.
Insert more than a record into the EMP table using a single insert command.
Update the EMP table to set the salary of all employees to Rs15000/- who are working as ASP.
Create a pseudo table EMPLOYEE with the same structure as the table EMP and insert rows into the table using select clauses.
Select employee name, job from the EMP table.
Delete only those who are working as lecturers.
List the records in the EMP table orderby salary in ascending order.
List the records in the EMP table orderby salary in descending order.
Display only those employees whose deptno is 1.
Display deptno from the table EMPLOYEE avoiding the duplicate values.
Data Control Language, Transaction Control Language Commands
Develop a query to grant some privileges of EMPLOYEES table into DEPARTMENTS table.
Develop a query to revoke some privileges of EMPLOYEES table from DEPARTMENTS table.
Write a query to implement the save point.
Write a query to implement the rollback.
Write a query to implement the commit.
In built Functions
Display all the details of the records whose employee name starts with 'A'.
Display all the details of the records whose employee name does not start with 'A'.
Display the rows whose salary ranges from 15000 to 30000.
Calculate the total and average salary amount of the EMP table.
Count the total records in the EMP table.
Determine the max and min salary and rename the column as max_salary and min_salary.
Find how many job titles are available in the EMPLOYEE table.
What is the difference between maximum and minimum salaries of employees in the organization?
Nested and Join Queries
Display all employee names and salary whose salary is greater than the minimum salary of the company and job title starts with 'A‘.
Write a query to find all the employees who work in the same job as Arjun.
Write a query to display information about employees who earn more than any employee in dept 1.
Display the employee details, departments that the departments are the same in both the EMP and DEPT.
Display the employee details, departments that the departments are not the same in both the EMP and DEPT.
Write a query to display their employee names.
Display the details of those who draw the salary greater than the average salary.
Write a query to perform left outer join.
Write a query to perform the right outer join.
Write a query to perform a full outer join.
Set Operators
Display all the dept numbers available with the DEPT and EMP tables avoiding duplicates.
Display all the dept numbers available with the DEPT and EMP tables.
Display all the dept numbers available in EMP and not in DEPT tables and vice versa.
Views
The organization wants to display only the details of the employees those who are ASP.
The organization wants to display only the details like empno, empname, deptno, deptname of the employees.
Execute the DML commands on the view created.
Drop a view.