19mis0367 DBMS Lab

DBMS LAB
NAME: SYED RASHA MAHABOOB
REG.NO: 19MIS0367
1. Database Schema for a Employee-pay scenario:
employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer,
deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date) For the above schema,
perform the following: Create the tables with the appropriate
integrity constraints Insert around 3 records in each of the
tables. Single line text.
employee(emp_id : integer, emp_name: string)
department(dept_id: integer,dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic:
integer, deductions: integer, additions: integer,
DOJ: date)
payroll(emp_id : integer, pay_date: date)

create database db_emp_pay_scenerio;
use db_emp_pay_scenerio;
create table employee(emp_id int(10),emp_name
varchar(50));
create table department(dept_id int(10),dept_name
varchar(50));
create table paydetails(emp_id int(5) references
employee(emp_id),dept_id int(6) references
department(dept_id),basic int(7),deductions
int(6),additions int(6),DOJ date);
create table payroll(emp_id int(5) references
employee(emp_id),pay_date date);

insert into employee values(101,"Aman");
insert into employee values(102,"Avi");
insert into employee values(103,"Avinash");
insert into department values(1,"CSE");

insert into paydetails
values(101,1,0101,500,500,"1998-08-05");
values(102,2,600,200,700,"1998-08-05");
values(103,3,900,700,900,"1999-04-06");
insert into payroll values(110,"1996-07-03");

2. Answer the following queries: a) List the employee details
department wise. b) List the details of employees whose basic
salary is between 10,000 and 20,000. c) Give a count of how
many employees are working in each department d) Give a
names of the employees whose net salary>10,000 e) List the
details for an Employee_id=5.. Single line text.
SQL> insert into pay details

values('&emp_id','&dept_id','&basic','&deductions','&additions','&doj');
Enter value for emp_id: 2374
Enter value for dept_id: 8
Enter value for basic: 15000
Enter value for deductions: 3000
Enter value for additions: 1500
Enter value for doj: 25-09-1987
old 1: insert into pay details
values('&emp_id','&dept_id','&basic','&deductions','&additions','&doj')
new 1: insert into pay details values('2374','8','15000','3000','1500','25-09-1987')
insert into pay details values('2374','8','15000','3000','1500','25-09-1987')
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into paydetails

Enter value for deductions: 3000
Enter value for additions: 1500
Enter value for doj: 25-09-1987
old 1: insert into paydetails
new 1: insert into paydetails values('2374','8','15000','3000','1500','25-09-1987')
insert into paydetails values('2374','8','15000','3000','1500','25-09-1987')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
SQL> insert into paydetails

Enter value for deductions: 43.09
Enter value for additions: 71.23
Enter value for doj: 08-jan-93
new 1: insert into paydetails values('2374','8','15000','43.09','71.23','08-jan-93')
1 row created.
SQL> //
Enter value for doj: 01-jan-96
new 1: insert into paydetails values('2894','5','14000','23.98','40.09','01-jan-96')
1 row created.
SQL> //
Enter value for doj: 06-jul-97
new 1: insert into paydetails values('8793','9','9000','30.54','40.09','06-jul-97')
1 row created.
SQL> //
Enter value for doj: 08-aug-97
new 1: insert into paydetails values('0005','8','17000','32.78','65.09','08-aug-97')
1 row created.
SQL> insert into payroll values('&emp_id','&date');

Enter value for date: 30-oct-20
old 1: insert into payroll values('&emp_id','&date')
new 1: insert into payroll values('2374','30-oct-20')
1 row created.
SQL> //
insert into payroll values('2874','28-oct-20')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C007409) violated - parent key not
found
SQL> //
1 row created.
SQL> //
1 row created.
SQL> //
1 row created.

19mis0367 DBMS Lab

Uploaded by

Copyright:

Available Formats

19mis0367 DBMS Lab

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

19mis0367 DBMS Lab

Uploaded by

Copyright:

Available Formats

DBMS LAB

NAME: SYED RASHA MAHABOOB

1. Database Schema for a Employee-pay scenario:

employee(emp_id : integer, emp_name: string)

department(dept_id: integer, dept_name:string)

paydetails(emp_id : integer, dept_id: integer, basic: integer,

deductions: integer, additions: integer, DOJ: date)

payroll(emp_id : integer, pay_date: date) For the above schema,

perform the following: Create the tables with the appropriate

integrity constraints Insert around 3 records in each of the

tables. Single line text.

employee(emp_id : integer, emp_name: string)

paydetails(emp_id : integer, dept_id: integer, basic:

integer, deductions: integer, additions: integer,

payroll(emp_id : integer, pay_date: date)

create table employee(emp_id int(10),emp_name

create table department(dept_id int(10),dept_name

create table paydetails(emp_id int(5) references

employee(emp_id),dept_id int(6) references

int(6),additions int(6),DOJ date);

create table payroll(emp_id int(5) references

insert into employee values(102,"Avi");

insert into employee values(103,"Avinash");

insert into department values(1,"CSE");

insert into department values(2,"CSE");

insert into department values(3,"CSE");

insert into paydetails

insert into paydetails

insert into payroll values(110,"1996-07-03");

insert into payroll values(109,"1903-03-03");

2. Answer the following queries: a) List the employee details

department wise. b) List the details of employees whose basic

salary is between 10,000 and 20,000. c) Give a count of how

many employees are working in each department d) Give a

names of the employees whose net salary>10,000 e) List the

details for an Employee_id=5.. Single line text.

SQL> insert into pay details

SQL> insert into paydetails

SQL> insert into paydetails

SQL> insert into payroll values('&emp_id','&date');

You might also like