sign in
 
   
 
 
 
   
  SQL Tutorial: (Based on M.G. University - MSc Computer Science)  
 
SQL Lab Practice - 2
 
   
  Create the following tables  


Table - I ( EMPLY )
Data for Client_Master
Empcode
Empname
Address
Age
Dept_Code
E101
Anjaly
Anjaly Nivas, Thiruvalla
25
D301
E102
Bobby
Alapuzha
28
D305
E103
Aravind
Chennai
31
D305
E104
Lakshmi
Mannar
55
D707
E105
Daisy
Chaithram, Angamaly
35
D707
E106
Esha
Mumbai
23
D707
E107
Georgy
Pala
45
D909
E108
Prakash
Vennikulam
36
D110
E109
Madhavan
Mynakum, Kottayam
46
D202
E110
Anugraha
Aparna, Angamaly
47
D301
E111
Deva
Trichy
38
D301
E112
Saju
Dhanya, Ernakulam
27
D202
E113
Priyesh
Priya Nivas, Kottayam
26
D302


Table - II ( PAYSLIP )
Data for Sale_Order
Empcode
Basic
HRA
DA
E101
4000
1900
1500
E102
4500
2200
2000
E103
5000
2800
2200
E104
7000
3000
2700
E105
3000
1500
1200
E106
5700
3000
2900
E107
6200
3300
3000
E108
5700
3700
3000
E109
7500
4660
545
E110
5000
300
100
E111
6000
200
120

Write SQL commands for the following:

1.

Update table EMPLY, add 15 years of age to employee with EMPCODE 'E107'.

2.

Create a view which shows details of employees whose age is between 35 and 45.

3.

Retrieve empcode, empname, address, Netpay from EMPLY and PAYSLIP. Display in ascending order of empcode.

4.

Create an English like sentence to display the following output from payslip table. "Employees with empcode E101 draws a basic salary 4000".

5.

List the names and address of employees drawing a basic salary between 5000 and 7000.

6.

Add new column Total_Sal in the PAYSLIP table and display all details including total salary for Employees with Empcode > E107.