| |
 |
|
| |
SQL Tutorial:
(Based on M.G. University - MSc Computer Science) |
|
| |
|
|
| |
|
|
| |
|
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. |
List details of employee with empcode 'E105'.
|
2. |
Count the number of employees whose age is less than 45.
|
3. |
Compute NET SALARY (Netsal = Basic + HRA +DA) for employees in the Payslip table.
|
4. |
Give the employee with empcode 'E110' a bonus of 15% of Basic and update the value held in column Netsal.
|
5. |
Display Name, Address, Empcode, Net salary, & age from EMPLY and PAYSLIP tables respectively.
|
6. |
Retrieve all information from EMPLY table for employees whose Basic Salary is more than 6000.
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
