Kathmandu BernHardt Secondary School/College
LAB ASSIGNMENT ON DBMS
1. Create a database name 'Office' .
Note: Format for the name of database is "studentname_Office_Section" . (For resolving overriding)
For example, name of database for student "Ram" of section A is "Ram_Office_A" .
2. Create table 'employee' in "Office" with appropriate data type. The schema of tables is:
▪ Employee(eid, name, address, gender, department, post)
▪ Define eid as Primary key.
3. Add column job_status in table employee .
4. Create table Salary with following schema:
▪ Salary(eid int, basic_sal, allowance, d_allow, gross_sal, tax, net_sal)
▪ Define appropriate data type for each fields.
▪ Define eid as foreign key employee with reference to primary key of employee table.
▪ Set default value 0 to fields allowance , d_allow , gross_sal , tax , net_sal .
5. Insert the following 10 records in employee table:
EID NAME ADDRESS GENDER DEPARTMENT POST JOB_STATUS
101 Aman Nepal Kirtipur Male ADMIN Officer P
102 Ram Karki Lalitpur Male ADMIN Assistant T
103 Sita Maharjan Chitwan Female ACC Officer T
104 Bibek Thapa Kathmandu Male ACC Manager P
105 Anju Shrestha Chitwan Female Admin Director P
106 Salin malla Lalitpur Male Sales Manager T
107 Neeta Bista Kirtipur Female Sales Officer P
108 Partham Dangol Kathmandu Male Sales Assistant T
109 Ritesh Basnet Lalitpur Male Store Officer P
110 Bisesh baskota Kathmandu Male Store Assistant P
Page 1
6. Insert the following records in salary table:
EID BASIC_SAL
101 50000
102 33000
103 50000
104 65000
105 75000
106 65000
107 50000
108 33000
109 50000
110 33000
7. Display detail of all employee from "employee" table whose job_status is 'P'.
8. Display detail of all employee from "employee" table whose name begin with 'A'.
9. Display detail of all employee from "employee" table whose name does not begin with 'A'.
10. Display detail of all employee from "employee" table whose name has 't' as third character.
11. Display detail of all employee from "employee" table whose name begin with 'B' and end with 'a'.
12. Display detail of all employee from "employee" table whose post is not 'manager'.
13. Calculate allowance as 15% of basic_sal for all employees.
14. Calculate d_allow as 10% of basic salary for all employees whose post is 'manager' or 'Director'.
15. Calculate gross salary as sum of basic salary, allowance and d_allow .
16. Calculate tax as 7.5% of gross salary if gross salary is greater than 60000.
17. Calculate net salary. (net salary = gross salary − tax)
18. Display all salary details of employees along with their eid , name and post .
Page 2