1

Chinmaya International Residential School​

​
​
​
​

INFORMATICS
LABORATORY WORK
​
​
​
​
Name:Utkarsh Shah

Class :XI Science

Roll number:4125

​
​
​
​

Academic Year: 2025

 2
TABLE OF CONTENTS

S No Contents Page Number

1 PYTHON PROGRAMS 1-26

1.1 Program 1 – Find sum of 5

two numbers

1.2 Program 2 – Average of 5

marks of 5 subjects

1.3 Program 3 – Convert 5

Fahrenheit to Celsius

1.4 Program 4 – Check if a 5

number is even or odd

1.5 Program 5 – Arithmetic 6

operations (Sum,
Product, Difference,
Quotient)

1.6 Program 6 – Convert 6

years into months, days,
hours, minutes, seconds

1.7 Program 7 – Right angled 7

triangle function

1.8 Program 8- Area of a 7

rectangle with function

1.9 Program 9 – Check if 7

number is positive,
negative, or zero

1.10 Program 10 – Sum of 8

numbers in a range

1.11 Program 11 – Factorials 8

in a range

1.12 Program 12 – 8
Multiplication table
 3

1.13 Program 13 – Sum of odd 9

and even digits of a
number

1.14 Program 14 – Perfect 9

number check

1.15 Program 15 – Count 9

perfect numbers between
1–100

1.16 Program 16 – Roller 10

coaster ride eligibility

1.17 Program 17 – Prime 10

number check

1.18 Program 18 – Prime 11

numbers in a range

1.19 Program 19 – Guess the 12

number game

1.20 Program 20 – Count 12

vowels in a sentence

1.21 Program 21 – Leap year 13

or century year

1.22 Program 22 – Print series 13

(different patterns)

1.23 Program 23 – Area of 14

different shapes (circle,
rectangle, etc.)

1.24 Program 24 – Armstrong 15

number (3-digit)

1.25 Program 25 – Fibonacci 16

series

1.26 Program 26 – Stone, 16

Paper, Scissor game
 4

1.27 Program 27 – Print 18

values of a list

1.28 Program 28 – Enter 19

values in a list and add
all values

1.29 Program 29 – Separate 19

odd and even numbers in
a list

1.30 Program 30 – Split words 20

of a sentence

1.31 Program 31 – Numbers 20

divisible by 5 and 7
between 500–700

1.32 Program 32 – Remove 20

even numbers from a list

1.33 Program 33 – First letter 21

of each word in a list

1.34 Program 34 – Print words 21

with 4 characters

1.35 Program 35 – Various list 21

operations in Python

2 SQL QUERIES 27-49

2.1 EMPLOYEE Table 27

Queries

2.2 GYM Table Queries 32

2.3 LOANS Database & 36

Loan_Accounts Table
Queries

2.4 COURSE Table Queries 46

 5
Python
[Link] a program to find sum of two numbers.
print("Enter two numbers")
n=int(input(""))
m=int(input(""))
s=n+m
print("Sum= ",s)

[Link] a program to enter name and print average of marks of 5 subjects.

name=input("Enter your name")

print("Enter marks for 5 subjects")
s=0
for i in range(5):
marks=int(input(""))
s=s+marks
avg=s/5
print("Average marks= ",avg )

[Link] a program to convert temperature in Farenheit to Celsius

f=float(input("Enter temperature in Farenheit"))

c=(32*f-32)*5/9
print("Temperature in Celsius= ",c)

[Link] a program to find if a number is even or odd.

n=int(input("Enter a number"))
if n%2==0:
print("Even")
 6
else:
print("Odd")

[Link] a program to use the desired arithmetic operator.

print("Enter two numbers")

n=int(input(""))
m=int(input(""))
s=n+m
print("Sum= ",s)
p=n*m
print("Product= ",p)
d=n-m
print("Difference= ",d)
div=n/m
print("Quotient = ",div)

[Link] a program to enter number of years and convert it to days,hours,minutes and

seconds.

year=int(input("Enter the number of years"))

months=year*12
days=months*30
hours=days*24
minutes=hours*60
seconds=minutes*60
print("Months=",months)
print("Days",days)
print("Hours",hours)
print("minutes",minutes)
print("Seconds",seconds)

[Link] a program to create a user defined function to create a right angled triangle
 7
def print_right_triangle(rows):

if rows <= 0:
print("Number of rows must be a positive integer.")
return

for i in range(1, rows + 1):

print("*" * i)

print("Triangle with 5 rows:")

print_right_triangle(5)

print("\nTriangle with 3 rows:")

print_right_triangle(3)

print("\nTriangle with 0 rows:")

print_right_triangle(0)

[Link] a program to create a user defined function to find the area of rectangle.

def rectangle_area(length, width):

return length * width
l = float(input("Enter the length of the rectangle: "))
w = float(input("Enter the width of the rectangle: "))
print("Area of rectangle =", rectangle_area(l, w))

[Link] a program to enter a numbers and check if it’s positive or negative or zero.
n=int(input("Enter a number"))
if n>0:
print("Positive")
elif n<0:
 8
print("Negative")
else:
print("Zero")

[Link] a program to find sum of numbers in a range.

s=int(input("Enter the starting point of the range"))

e=int(input("Enter the ending point of the range"))
sum=0
for i in range(s,e+1):
sum=sum+i
print("Sum in the given range= ",sum)

[Link] program to find factorial of numbers in a given range.

s=int(input("Enter the starting point of the range"))

e=int(input("Enter the ending point of the range"))
for i in range(s,e+1):
x=i
f=1
for k in range(1,x+1):
f=f*k
print("Factorial of ",x,"is= ",f)

[Link] a program to enter a number and print it’s multiplication table.

n=int(input("Enter a number"))
print("Multiplication table")
for i in range(1,11):
 9
print(i*n)

[Link] a program to enter a number and add all even and odd numbers including it.

n=int(input("Enter a number"))
i=n
e=o=0
while(n>0):
d=n%10
if d%2==0:
e=e+d
else:
o=o+d
n//=10
print("Sum of odd digits ",o)
print("Sum of even digits ",e)

​ ​

[Link] a program to obtain a number and find out if it’s a perfect number or not.

n=int(input("Enter a number"))
s=0
for i in range(1,n):
if n%i==0:
s+=i
if s==n :
print("Perfect")
else:
print("Not Perfect")

15. Write a program to find the number of perfect numbers from 1 to 100.
 10
print("")
s=0
c=0
for i in range(1,101):
n=i
s=0
for k in range(1,n):
if n%k==0:
s+=i
if s==n :
c=c+1
print("Frequency of perfect numbers between 0-101 is= ",c)

[Link] a program to check the eligibility for a roller coaster ride.

height=float(input("Enter height in cm"))

age=int(input("Enter your age"))
if height >=120 :
print("Eligible to ride")
if age>5 and age<12:
cost=100
elif age <19:
cost=150
else:
cost=200
print("Do you want photos?")
print("Enter 1 for yes and 2 for no")
photo=int(input(""))
if photo==1:
cost=cost+50
print("Your total fair =",cost)
print("Enjoy!!")
if height<120:
print("Sorry! Ineligible to ride")

[Link] a program to enter a number and check if it is a prime number or not.

n=int(input("Enter a number"))
 11
c=0
for i in range(1,n+1):
if n%i==0:
c+=1
if c==2 :
print("Prime")
else:
print("Not Prime")

[Link] a program to find out all prime numbers within a given range.

s=int(input("Enter the starting point of the range"))

e=int(input("Enter the ending point of the range"))
print("Prime numbers within the given range:")
for n in range(s,e+1):
c=0
for i in range(1,n+1):
if n%i==0:
c+=1
if c==2 :
print(n)
 12

19. Write a program to make guess the number game.

import random

def game():
n = [Link](1, 10)
c=0

while True:
x = int(input("Guess a number between 1 and 10: "))
c += 1

if x > n:
print("Too high!")
elif x < n:
print("Too low!")
else:
print("You Got It!!")
break

print("Attempts taken:", c)

game()

[Link] a program to enter a sentence and find the number of vowels in it.

s = input("Enter a sentence: ")

v = ['a','e','i','o','u','A','E','I','O','U']
c=0

for ch in s:
if ch in v:
c += 1
 13

print("Frequency of vowels in the given string is =", c)

21. Write a program to check if it is a leap year or only a century year.

year = int(input("Enter a year: "))

if year % 400 == 0:
print(year, "is a Leap Year and also a Century Year.")
elif year % 100 == 0:
print(year, "is a Century Year but not a Leap Year.")
elif year % 4 == 0:
print(year, "is a Leap Year.")
else:
print(year, "is neither a Leap Year nor a Century Year.")

[Link] a program to print series.

n = int(input("Enter the number of rows: "))

for i in range(1, n + 1):

for j in range(1, i + 1):
print(j, end="")
print()

n = int(input("Enter the number of rows: "))

for i in range(n, 0, -1):

for j in range(n, i - 1, -1):
print(j, end="")
print()

n = int(input("Enter the number of rows: "))

for i in range(1, n + 1):

for j in range(i, 0, -1):
print(j, end="")
print()
 14

[Link] a program to find the area of different shapes.

import math
i=0
x=int(input("How many times do you want to use the AREA calculator??"))
while(i<x) :
print("\n===== Area Calculator =====")
print("1. Circle")
print("2. Rectangle")
print("3. Triangle")
print("4. Square")
print("5. Exit")

choice = int(input("Enter your choice (1-5): "))

if choice == 1:
r = float(input("Enter radius of the circle: "))
area = [Link] * r * r
print("Area of Circle =", area)

elif choice == 2:
l = float(input("Enter length: "))
b = float(input("Enter breadth: "))
area = l * b
print("Area of Rectangle =", area)

elif choice == 3:
base = float(input("Enter base: "))
height = float(input("Enter height: "))
area = 0.5 * base * height
print("Area of Triangle =", area)

elif choice == 4:
side = float(input("Enter side length: "))
 15
area = side * side
print("Area of Square =", area)

elif choice == 5:
print("Exiting the program. Goodbye!")
break

else:
print("Invalid choice! Please select between 1 and 5.")
i+=1

[Link] a program to find out if the given 3-digit number is an Armstrong number or
not.

n=int(input("Enter a 3-digit number"))

i=n
s=0
while(i>0):
d=i%10
s=s+d**3
i//=10
if s==n:
print("Armstrong number")
else:
print("Not an Armstrong number")
 16

25. Write a program to print fibonacci series till the given number of terms.

c=0
a=0
b=1
n=int(input("Enter the number of terms"))
print("Fibonacci Series")
print(a)
print(b)
i=3
while(i<=n):
c=a+b
print(c)
a=b
b=c
i+=1

26. Write a program to create stone , paper , scissor game using ascii.

import random

stone = "stone"
paper = "paper"
scissor = "scissor"

list1 = [stone, paper, scissor]

comp = [Link](list1)

print("Welcome to Stone! Paper! Scissor! game")

player = input("Enter your choice [stone/paper/scissor]: ").lower()
 17
img1 = '''
_ ,-, _
,--, /: :\/': :`\/: :\
|`; ' `,' `.; `: |
| | | ' | |.
| : | | | ||
| :. | : | : | : | \
\__/: :.. : :.. | :.. | )
`---',\___/,\___/ /'
`==._ .. . /'
`-::-'
'''

img2 = '''
.-. _
||/)
| |/ /
_|__ /_
/ __)-' )
\ `(.-')
> ._>-'
/ \/
'''

img3 = '''
___..__
__..--""" ._ __.'
"-..__
'"--..__";
___ '--...__"";
`-..__ '"---..._;"
""""----'
'''

# Show player's choice image

if player == stone:
print(img1)
elif player == scissor:
print(img2)
elif player == paper:
print(img3)
else:
print("Invalid choice! Please choose stone, paper, or scissor.")
exit()

print("Computer chose:", comp)

# Show computer's choice image

 18
if comp == stone:
print(img1)
elif comp == scissor:
print(img2)
else:
print(img3)

# Game logic
if comp == player:
print("It's a draw!")
elif (player == stone and comp == scissor) or \
(player == scissor and comp == paper) or \
(player == paper and comp == stone):
print("Congrats, you win :)")
else:
print("Sorry, you lost :(")

27. Write a program to print values of a list.

l= [10, 20, 30, 40, 50]

print("The values in the list are:")

# Method 1: Using a loop

for value in l:
print(value)
 19

28. Write a program to enter values in a list and add all its values.

n = int(input("Enter how many numbers you want in the list: "))

numbers = []

for i in range(n):
val = int(input(f"Enter number {i+1}: "))
[Link](val)
total = sum(numbers)
print("The list is:", numbers)
print("The sum of all values is:", total)

[Link] a program to enter values in a list and segregate into odd and even.

n = int(input("Enter how many numbers you want in the list: "))

numbers = []
odd=[]
even=[]
for i in range(n):
val = int(input(f"Enter number {i+1}: "))
if val%2==0:
[Link](val)
else:
[Link](val)
[Link](val)
print("List: ",numbers)
print("Odd= ",odd)
print("Even: ",even)
 20

[Link] a program to enter a sentence and split all its words.

s = input("Enter a string: ")

words = [Link]()
print("Split string =", words)

[Link] a program to find those numbers which are divisible by both 5 and 7 between
500 and 700(both included)

print("Numbers divisible by 5 and 7 between 500 and 700 both included are:")
for i in range(500,701):
if i%5==0 and i%7==0:
print(i)

[Link] a program to enter numbers in a list and create and print a new list by
removing all even numbers from it.

l=[20,43,76,26,64,33,97,13]
print("List without even numbers")
o=[]
for i in range (len(l)):
if l[i]%2!=0:
[Link](l[i])
print(o)
 21

[Link] a program to enter words in a list and print a new list with first letter of each
word .
l=["India","Australia","South Africa","New Zealand","England"]
n=[]
for i in range(len(l)):
x=l[i]
[Link](x[0])
print("List with first letter of each word")
print(n)

[Link] a program using list to display the names with 4 characters.

l1=[]
c=0
print("Enter words in a list")
for i in range(5):
s=input("")
[Link](s)
print("Words with 4 characters")
for k in range(5):
c=len(l1[k])
if c==4:
print(l1[k])

[Link] a program to perform various list operations in python.

print("Enter 1. to append")
print("Enter 2. to extend")
print("Enter 3. to insert")
print("Enter 4. to remove")
 22
print("Enter 5. to pop")
print("Enter 6. to delete (slice)")
print("Enter 7. to reverse")
print("Enter 8. to sort")
print("Enter 9. to split string")
print("Enter 10. to find the length")
print("Enter 11. to find index")
print("Enter 12. to find sum")
print("Enter 13. to find minimum value")
print("Enter 14. to find maximum value")
print("Enter 15. to count occurrences")

ch = int(input("Enter choice: "))

def append_item():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
x = int(input("Enter the value you want to append: "))
[Link](x)
print("Modified list =", l1)

def extend_list():
l1 = []
print("Enter numbers in first list")
for i in range(5):
n = int(input())
[Link](n)
l2 = []
print("Enter numbers in second list to extend")
for k in range(5):
m = int(input())
[Link](m)
[Link](l2)
print("Modified list =", l1)

def insert_item():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
v = int(input("Enter the value you want to insert: "))
ind = int(input("Enter the index: "))
[Link](ind, v)
print("Modified list =", l1)
 23

def remove_item():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
v = int(input("Enter the value you want to remove: "))
if v in l1:
[Link](v)
else:
print("Value not found!")
print("Modified list =", l1)

def pop_item():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
v = int(input("Enter the index to pop: "))
if 0 <= v < len(l1):
[Link](v)
else:
print("Invalid index!")
print("Modified list =", l1)

def delete_slice():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
s = int(input("Enter the start index: "))
e = int(input("Enter the stop index: "))
del l1[s:e]
print("Modified list =", l1)

def reverse_list():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
[Link]()
print("Reversed list =", l1)

def sort_list():
 24
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
[Link]()
print("Ascending sorted list =", l1)
[Link](reverse=True)
print("Descending sorted list =", l1)

def split_string():
s = input("Enter a string: ")
words = [Link]()
print("Split string =", words)

def find_length():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
print("Length of the list =", len(l1))

def find_index():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
v = int(input("Enter the value whose index you want: "))
if v in l1:
print("Index =", [Link](v))
else:
print("Value not found!")

def find_sum():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
print("Sum of values in the list =", sum(l1))

def find_min():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
 25
[Link](n)
print("Minimum value in the list =", min(l1))

def find_max():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
print("Maximum value in the list =", max(l1))

def count_occurrences():
l1 = []
print("Enter numbers in a list")
for i in range(5):
n = int(input())
[Link](n)
v = int(input("Enter the value to count: "))
print("Frequency of value in the list =", [Link](v))

if ch == 1:
append_item()
elif ch == 2:
extend_list()
elif ch == 3:
insert_item()
elif ch == 4:
remove_item()
elif ch == 5:
pop_item()
elif ch == 6:
delete_slice()
elif ch == 7:
reverse_list()
elif ch == 8:
sort_list()
elif ch == 9:
split_string()
elif ch == 10:
find_length()
elif ch == 11:
find_index()
elif ch == 12:
find_sum()
elif ch == 13:
find_min()
elif ch == 14:
find_max()
 26
elif ch == 15:
count_occurrences()
else:
print("Invalid choice!")
 27
SQL
1.​ Consider the following tables EMPLOYEE and write the SQL query for the following
requirements.

Eid Name Deptid Qualifi Gender Points Basic DA HRA Bonus

cation
1 Deepali 101 MCA F 20 6000 2000 2300 200
2 Rajat 101 BCA M 15 2000 300 300 30
3 Hari 102 B.A M 15 1000 300 300 40
4 Harry 102 M.A M 40 1500 390 490 30
5 Sumit 103 [Link] M 32 8000 900 900 80
6 Jyoti 101 [Link] F 12 10000 300 490 89

mysql> use ip;

Database changed
mysql> CREATE TABLE EMPLOYEE (
-> Eid INT,
-> Name VARCHAR(20),
-> Deptid INT,
-> Qualification VARCHAR(20),
-> Gender CHAR(1),
-> Points INT,
-> Basic INT,
-> DA INT,
-> HRA INT,
-> Bonus INT
-> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO EMPLOYEE (Eid, Name, Deptid, Qualification, Gender, Points, Basic, DA, HRA, Bonus)
VALUES
-> (1, 'Deepali', 101, 'MCA', 'F', 20, 6000, 2000, 2300, 200),
-> (2, 'Rajat', 101, 'BCA', 'M', 15, 2000, 300, 300, 30),
-> (3, 'Hari', 102, 'B.A', 'M', 15, 1000, 300, 300, 40),
-> (4, 'Harry', 102, 'M.A', 'M', 40, 1500, 390, 490, 30),
-> (5, 'Sumit', 103, '[Link]', 'M', 32, 8000, 900, 900, 80),
-> (6, 'Jyoti', 101, '[Link]', 'F', 12, 10000, 300, 490, 89);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

1.​ Display the Names begin with alphabet “H”

 28

mysql> SELECT Name FROM EMPLOYEE WHERE Name LIKE 'H%';

+-------+
| Name |
+-------+
| Hari |
| Harry |
+-------+
2 rows in set (0.00 sec)

2.​ Display the records of female candidates.

mysql> SELECT * FROM EMPLOYEE WHERE Gender='F';

+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
| Eid | Name | Deptid | Qualification | Gender | Points | Basic | DA | HRA | Bonus |
+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
| 1 | Deepali | 101 | MCA |F | 20 | 6000 | 2000 | 2300 | 200 |
| 6 | Jyoti | 101 | [Link] |F | 12 | 10000 | 300 | 490 | 89 |
+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
2 rows in set (0.00 sec)

3.​ Display the name and qualification of “B. Tech.” graduates.

mysql> SELECT Name, Qualification FROM EMPLOYEE WHERE Qualification='[Link]';

+-------+---------------+
| Name | Qualification |
+-------+---------------+
| Sumit | [Link] |
+-------+---------------+
1 row in set (0.00 sec)

4.​ Display the record whose gender is ‘f’ and deptid is101 and arrange in ascending order of
name and descending of deptid.
mysql> SELECT * FROM EMPLOYEE WHERE Gender='F' AND Deptid=101
-> ORDER BY Name ASC, Deptid DESC;

+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
| Eid | Name | Deptid | Qualification | Gender | Points | Basic | DA | HRA | Bonus |
+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
 29
| 1 | Deepali | 101 | MCA |F | 20 | 6000 | 2000 | 2300 | 200 |
| 6 | Jyoti | 101 | [Link] |F | 12 | 10000 | 300 | 490 | 89 |
+------+---------+--------+---------------+--------+--------+-------+------+------+-------+
2 rows in set (0.00 sec)
5.​ Add one more field for the employee table -DOJ (date of joining) next to Dept id.
mysql> ALTER TABLE EMPLOYEE ADD DOJ DATE AFTER Deptid;

Query OK, 6 rows affected (0.05 sec)

Records: 6 Duplicates: 0 Warnings: 0

6.​ Display the records of employees except “Jyoti” and “Harry”.

mysql> SELECT * FROM EMPLOYEE WHERE Name NOT IN ('Jyoti','Harry');

+------+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| Eid | Name | Deptid | DOJ | Qualification | Gender | Points | Basic | DA | HRA | Bonus |
+------+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| 1 | Deepali | 101 | NULL | MCA |F | 20 | 6000 | 2000 | 2300 | 200 |
| 2 | Rajat | 101 | NULL | BCA |M | 15 | 2000 | 300 | 300 | 30 |
| 3 | Hari | 102 | NULL | B.A |M | 15 | 1000 | 300 | 300 | 40 |
| 5 | Sumit | 103 | NULL | [Link] |M | 32 | 8000 | 900 | 900 | 80 |
+------+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
4 rows in set (0.00 sec)
7.​ Display Name, Deptid, Point, and NewScore. The NewScore is a product of Point and 12

mysql> SELECT Name, Deptid, Points, Points*12 AS NewScore FROM EMPLOYEE;

+---------+--------+--------+----------+
| Name | Deptid | Points | NewScore |
+---------+--------+--------+----------+
| Deepali | 101 | 20 | 240 |
| Rajat | 101 | 15 | 180 |
| Hari | 102 | 15 | 180 |
| Harry | 102 | 40 | 480 |
| Sumit | 103 | 32 | 384 |
| Jyoti | 101 | 12 | 144 |
+---------+--------+--------+----------+
6 rows in set (0.02 sec)

8.​ Display the Name, deptid, Basic whose date of joining is after 23/09/2000.

mysql> SELECT Name, Deptid, Basic

-> FROM EMPLOYEE
 30
-> WHERE DOJ > '2000-09-23';
Empty set (0.01 sec)

9.​ Change the HRA as 400 whose HRA is 300.

mysql> UPDATE EMPLOYEE

-> SET HRA = 400
-> WHERE HRA = 300;

Query OK, 2 rows affected (0.08 sec)

Rows matched: 2 Changed: 2 Warnings: 0

10.​Add primary key to eid.

mysql> ALTER TABLE EMPLOYEE

-> ADD PRIMARY KEY (Eid);
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0

11.​ Remove the row whose bonus is 89.

mysql> DELETE FROM EMPLOYEE
-> WHERE Bonus = 89;

Query OK, 1 row affected (0.05 sec)

12.​Display the records in descending order according to their Names.

mysql> SELECT * FROM EMPLOYEE
-> ORDER BY Name DESC;

+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| Eid | Name | Deptid | DOJ | Qualification | Gender | Points | Basic | DA | HRA | Bonus |
+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| 5 | Sumit | 103 | NULL | [Link] |M | 32 | 8000 | 900 | 900 | 80 |
| 2 | Rajat | 101 | NULL | BCA |M | 15 | 2000 | 300 | 400 | 30 |
| 4 | Harry | 102 | NULL | M.A |M | 40 | 1500 | 390 | 490 | 30 |
| 3 | Hari | 102 | NULL | B.A |M | 15 | 1000 | 300 | 400 | 40 |
| 1 | Deepali | 101 | NULL | MCA |F | 20 | 6000 | 2000 | 2300 | 200 |
+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
5 rows in set (0.00 sec)

13.​Modify the column name of name as ename.

mysql> ALTER TABLE EMPLOYEE

 31
-> CHANGE Name Ename VARCHAR(25);

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0 Warnings: 0

14.​Display the name whose name ends with ‘I’.

mysql> SELECT Ename

-> FROM EMPLOYEE
-> WHERE Ename LIKE '%i';

+---------+
| Ename |
+---------+
| Deepali |
| Hari |
+---------+
2 rows in set (0.00 sec)
15.​Change the points of Jyoti to 28.

mysql> UPDATE EMPLOYEE

-> SET Points = 28
-> WHERE Ename = 'Jyoti';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

16.​Display the record whose qualification is B.A, M.A, M.C.A.

mysql> SELECT *
-> FROM EMPLOYEE
-> WHERE Qualification IN ('B.A','M.A','MCA');

+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| Eid | Ename | Deptid | DOJ | Qualification | Gender | Points | Basic | DA | HRA | Bonus |
+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
| 1 | Deepali | 101 | NULL | MCA |F | 20 | 6000 | 2000 | 2300 | 200 |
| 3 | Hari | 102 | NULL | B.A |M | 15 | 1000 | 300 | 400 | 40 |
| 4 | Harry | 102 | NULL | M.A |M | 40 | 1500 | 390 | 490 | 30 |
+-----+---------+--------+------+---------------+--------+--------+-------+------+------+-------+
3 rows in set (0.00 sec)

17.​Change the datatype of name as varchar(25).

 32
-> MODIFY Ename VARCHAR(25);

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

2. Consider the following table named “GYM” with details about Fitness
products being sold in the
store.

mysql> use ip;

Database changed
mysql> CREATE TABLE GYM (
-> Prcode VARCHAR(10),
-> Prname VARCHAR(30),
-> UnitPrice DECIMAL(10,2),
-> Manufacturer VARCHAR(30)
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO GYM (Prcode, Prname, UnitPrice, Manufacturer) VALUES

-> ('P101', 'Cross Trainer', 25000, 'Avon Fitness'),
-> ('P102', 'TreadMill', 32000, 'AG Fitline'),
-> ('P103', 'Massage Chair', 20000, 'Fit Express'),
-> ('P104', 'Vibration Trainer', 22000, 'Avon Fitness'),
-> ('P105', 'Bike', 13000, 'Fit Express');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0

Write SQL statements to do the following:

a) Display the names of all the products in the store.

mysql> SELECT Prname FROM GYM;

+-------------------+
| Prname |
+-------------------+
| Cross Trainer |
 33
| TreadMill |
| Massage Chair |
| Vibration Trainer |
| Bike |
+-------------------+
5 rows in set (0.00 sec)

b) Display the names and unit price of all the products in the store.
mysql> SELECT Prname, UnitPrice FROM GYM;

+-------------------+-----------+
| Prname | UnitPrice |
+-------------------+-----------+
| Cross Trainer | 25000.00 |
| TreadMill | 32000.00 |
| Massage Chair | 20000.00 |
| Vibration Trainer | 22000.00 |
| Bike | 13000.00 |
+-------------------+-----------+
5 rows in set (0.00 sec)
c) Display the names of all the products with unit price less than Rs.20000.00

mysql> SELECT Prname FROM GYM WHERE UnitPrice < 20000;

+--------+
| Prname |
+--------+
| Bike |
+--------+
1 row in set (0.08 sec)

d) Display details of all the products with unit price in the range 20000 to
30000

mysql> SELECT * FROM GYM WHERE UnitPrice BETWEEN 20000 AND 30000;

+--------+-------------------+-----------+--------------+
| Prcode | Prname | UnitPrice | Manufacturer |
+--------+-------------------+-----------+--------------+
| P101 | Cross Trainer | 25000.00 | Avon Fitness |
| P103 | Massage Chair | 20000.00 | Fit Express |
| P104 | Vibration Trainer | 22000.00 | Avon Fitness |
+--------+-------------------+-----------+--------------+
3 rows in set (0.03 sec)
e) Display names of all products by the manufacturer "Fit Express"

mysql> SELECT Prname FROM GYM WHERE Manufacturer='Fit Express';

+---------------+
| Prname |
+---------------+
| Massage Chair |
 34
| Bike |
+---------------+
2 rows in set (0.00 sec)

f) Display all rows sorted in descending order of unit price.

mysql> SELECT * FROM GYM ORDER BY UnitPrice DESC;

+--------+-------------------+-----------+--------------+
| Prcode | Prname | UnitPrice | Manufacturer |
+--------+-------------------+-----------+--------------+
| P102 | TreadMill | 32000.00 | AG Fitline |
| P101 | Cross Trainer | 25000.00 | Avon Fitness |
| P104 | Vibration Trainer | 22000.00 | Avon Fitness |
| P103 | Massage Chair | 20000.00 | Fit Express |
| P105 | Bike | 13000.00 | Fit Express |
+--------+-------------------+-----------+--------------+
5 rows in set (0.00 sec)

g) Add a new row for product with the details: "P106","Vibro Exerciser",
23000, manufacturer : "Avon Fitness".

mysql> INSERT INTO GYM (Prcode, Prname, UnitPrice, Manufacturer)

-> VALUES ('P106', 'Vibro Exerciser', 23000, 'Avon Fitness');
Query OK, 1 row affected (0.01 sec)
h) Change the Unit Price data of all the rows by applying a 10% discount
reduction on all the products.

mysql> UPDATE GYM SET UnitPrice = UnitPrice * 0.9;

Query OK, 6 rows affected (0.01 sec)

Rows matched: 6 Changed: 6 Warnings: 0

i) Display details of all products with manufacturer name starting with "A"

mysql> SELECT * FROM GYM WHERE Manufacturer LIKE 'A%';

+--------+-------------------+-----------+--------------+
| Prcode | Prname | UnitPrice | Manufacturer |
+--------+-------------------+-----------+--------------+
| P101 | Cross Trainer | 22500.00 | Avon Fitness |
| P102 | TreadMill | 28800.00 | AG Fitline |
| P104 | Vibration Trainer | 19800.00 | Avon Fitness |
| P106 | Vibro Exerciser | 20700.00 | Avon Fitness |
+--------+-------------------+-----------+--------------+
4 rows in set (0.00 sec)
j) Change the unitprice of Bike to 15000.

mysql> UPDATE GYM SET UnitPrice = 15000 WHERE Prname='Bike';

 35
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

k)Remove the data where unit price is less than 13000.

mysql> DELETE FROM GYM WHERE UnitPrice < 13000;

Query OK, 0 rows affected (0.00 sec)

l) Change the data type of unit price to int(7).

mysql> ALTER TABLE GYM MODIFY UnitPrice INT(7);

Query OK, 6 rows affected (0.11 sec)

Records: 6 Duplicates: 0 Warnings: 0

m) To rename the column name of prcode as Prodcode .

mysql> ALTER TABLE GYM CHANGE Prcode Prodcode VARCHAR(10);

Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0

n) Display the manufacturer names of all products, without duplicates.

mysql> SELECT DISTINCT Manufacturer FROM GYM;
+--------------+
| Manufacturer |
+--------------+
| Avon Fitness |
| AG Fitline |
| Fit Express |
+--------------+
3 rows in set (0.05 sec)
o) Display the details of products whose prname is ‘ Bike’, Message Chai’ or
TreadMill’

mysql> SELECT * FROM GYM

-> WHERE Prname IN ('Bike','Massage Chair','TreadMill');
+----------+---------------+-----------+--------------+
| Prodcode | Prname | UnitPrice | Manufacturer |
+----------+---------------+-----------+--------------+
| P102 | TreadMill | 28800 | AG Fitline |
| P103 | Massage Chair | 18000 | Fit Express |
| P105 | Bike | 15000 | Fit Express |
+----------+---------------+-----------+--------------+
3 rows in set (0.00 sec)
 36

3)Consider a database LOANS with the following table :

Write SQL commands for the tasks 1 to 35

1. Create the database LOANS.

mysql> CREATE DATABASE LOANS;

Query OK, 1 row affected (0.02 sec)

2. Use the database LOANS.

mysql> USE LOANS;

Database changed

3. Create the table Loan_Accounts and insert tuples in it.

mysql> -- Create table

mysql> CREATE TABLE Loan_Accounts (
-> AccNo INT PRIMARY KEY,
-> Cust_Name VARCHAR(50),
-> Loan_Amount INT,
-> Instalments INT,
-> Int_Rate DECIMAL(5,2),
-> Start_Date DATE,
-> Interest DECIMAL(12,2),

-> );
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> -- Insert records
mysql> INSERT INTO Loan_Accounts (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate,
Start_Date, Interest) VALUES
-> (1, 'R.K. Gupta', 300000, 36, 12.00, '2009-07-19', NULL),
-> (2, 'S.P. Sharma', 500000, 48, 10.00, '2008-03-22', NULL),
-> (3, 'K.P. Jain', 300000, 36, NULL, '2007-03-08', NULL),
 37
-> (4, 'M.P. Yadav', 800000, 60, 10.00, '2008-12-06', NULL),
-> (5, 'S.P. Sinha', 200000, 36, 12.50, '2010-01-03', NULL),
-> (6, 'P. Sharma', 700000, 60, 12.50, '2008-06-05', NULL),
-> (7, 'K.S. Dhall', 500000, 48, NULL, '2008-03-05', NULL);
Query OK, 7 rows affected (0.08 sec)
Records: 7 Duplicates: 0 Warnings: 0

4. Display the details of all the loans.

mysql>
mysql> -- Queries (Q4 - Q35)
mysql> SELECT * FROM Loan_Accounts; -- Q4
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
| 4 | M.P. Yadav | 800000 | 60 | 10.00 | 2008-12-06 | NULL | NULL |
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
| 6 | P. Sharma | 700000 | 60 | 12.50 | 2008-06-05 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
7 rows in set (0.00 sec)

5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.

mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts; -- Q5

+-------+-------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+-------------+-------------+
| 1 | R.K. Gupta | 300000 |
| 2 | S.P. Sharma | 500000 |
| 3 | K.P. Jain | 300000 |
| 4 | M.P. Yadav | 800000 |
| 5 | S.P. Sinha | 200000 |
| 6 | P. Sharma | 700000 |
| 7 | K.S. Dhall | 500000 |
+-------+-------------+-------------+
7 rows in set (0.00 sec)

6. Display the details of all the loans with less than 40 instalments.

mysql>
mysql> SELECT * FROM Loan_Accounts WHERE Instalments < 40; -- Q6
 38
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
3 rows in set (0.00 sec)

7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.

mysql> SELECT AccNo, Loan_Amount FROM Loan_Accounts WHERE Start_Date < '2009-04-01';
-- Q7
+-------+-------------+
| AccNo | Loan_Amount |
+-------+-------------+
| 2 | 500000 |
| 3 | 300000 |
| 4 | 800000 |
| 6 | 700000 |
| 7 | 500000 |
+-------+-------------+
5 rows in set (0.00 sec)

8. Display the Int_Rate of all the loans started after 01-04-2009.

Using NULL

mysql> SELECT Int_Rate FROM Loan_Accounts WHERE Start_Date > '2009-04-01'; -- Q8

+----------+
| Int_Rate |
+----------+
| 12.00 |
| 12.50 |
+----------+
2 rows in set (0.00 sec)

9. Display the details of all the loans whose rate of interest is NULL

mysql>
mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate IS NULL; -- Q9
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
2 rows in set (0.00 sec)
 39
10. Display the details of all the loans whose rate of interest is not NULL.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate IS NOT NULL; -- Q10

+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 4 | M.P. Yadav | 800000 | 60 | 10.00 | 2008-12-06 | NULL | NULL |
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
| 6 | P. Sharma | 700000 | 60 | 12.50 | 2008-06-05 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
5 rows in set (0.00 sec)

11. Display the amounts of various loans from the table Loan_Accounts. A loan amount
should appear only once.

mysql>
mysql> SELECT DISTINCT Loan_Amount FROM Loan_Accounts; -- Q11
+-------------+
| Loan_Amount |
+-------------+
| 300000 |
| 500000 |
| 800000 |
| 200000 |
| 700000 |
+-------------+
5 rows in set (0.00 sec)

12. Display the number of instalments of various loans from the table Loan_Accounts. An
instalment should appear only once.

mysql> SELECT DISTINCT Instalments FROM Loan_Accounts; -- Q12

+-------------+
| Instalments |
+-------------+
| 36 |
| 48 |
| 60 |
+-------------+
3 rows in set (0.00 sec)

13. Display the details of all the loans started after 31-12-2008 for which the number of
instalments aremore than 36.

mysql>
mysql> SELECT * FROM Loan_Accounts WHERE Start_Date > '2008-12-31' AND Instalments >
36; -- Q13
Empty set (0.00 sec)
 40

14. Display the Cust_Name and Loan_Amount for all the loans which do not have number
of instalments36.

mysql> SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Instalments <> 36; --
Q14
+-------------+-------------+
| Cust_Name | Loan_Amount |
+-------------+-------------+
| S.P. Sharma | 500000 |
| M.P. Yadav | 800000 |
| P. Sharma | 700000 |
| K.S. Dhall | 500000 |
+-------------+-------------+
4 rows in set (0.00 sec)

15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount
is less than500000 or int_rate is more than 12.

mysql> SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Loan_Amount <

500000 OR Int_Rate > 12; -- Q15
+------------+-------------+
| Cust_Name | Loan_Amount |
+------------+-------------+
| R.K. Gupta | 300000 |
| K.P. Jain | 300000 |
| S.P. Sinha | 200000 |
| P. Sharma | 700000 |
+------------+-------------+
4 rows in set (0.00 sec)

16. Display the details of all the loans which started in the year 2009.

mysql> SELECT * FROM Loan_Accounts WHERE YEAR(Start_Date) = 2009; -- Q16

+-------+------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
1 row in set (0.06 sec)

17. Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000.

mysql> SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND

500000; -- Q17
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
 41
2 rows in set (0.00 sec)

18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12; -- Q18
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
1 row in set (0.00 sec)

19. Display the Cust_Name and Loan_Amount for all the loans for which the number of
instalments are24, 36, or 48.

mysql> SELECT Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Instalments IN (24,

36, 48); -- Q19
+-------------+-------------+
| Cust_Name | Loan_Amount |
+-------------+-------------+
| R.K. Gupta | 300000 |
| S.P. Sharma | 500000 |
| K.P. Jain | 300000 |
| S.P. Sinha | 200000 |
| K.S. Dhall | 500000 |
+-------------+-------------+
5 rows in set (0.00 sec)

20. Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000.

mysql>
mysql> SELECT * FROM Loan_Accounts WHERE Loan_Amount BETWEEN 400000 AND
500000; -- Q20
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
2 rows in set (0.00 sec)
 42
21. Display the details of all the loans whose rate of interest is in the range 11% to 12%.

mysql> SELECT * FROM Loan_Accounts WHERE Int_Rate BETWEEN 11 AND 12; -- Q21
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
+-------+------------+-------------+-------------+----------+------------+----------+----------+
1 row in set (0.00 sec)

22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with Sharma

mysql>
mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name
LIKE '%Sharma'; -- Q22
+-------+-------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+-------------+-------------+
| 2 | S.P. Sharma | 500000 |
| 6 | P. Sharma | 700000 |
+-------+-------------+-------------+
2 rows in set (0.00 sec)

23. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with a.

mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name

LIKE '%a'; -- Q23
+-------+-------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+-------------+-------------+
| 1 | R.K. Gupta | 300000 |
| 2 | S.P. Sharma | 500000 |
| 5 | S.P. Sinha | 200000 |
| 6 | P. Sharma | 700000 |
+-------+-------------+-------------+
4 rows in set (0.00 sec)

24. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains &#39;a&#39;

mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name

LIKE '%a%'; -- Q24
 43
+-------+-------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+-------------+-------------+
| 1 | R.K. Gupta | 300000 |
| 2 | S.P. Sharma | 500000 |
| 3 | K.P. Jain | 300000 |
| 4 | M.P. Yadav | 800000 |
| 5 | S.P. Sinha | 200000 |
| 6 | P. Sharma | 700000 |
| 7 | K.S. Dhall | 500000 |
+-------+-------------+-------------+
7 rows in set (0.00 sec)

25. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name does not contain &#39;P&#39;.

mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name

NOT LIKE '%P%'; -- Q25
+-------+------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+------------+-------------+
| 7 | K.S. Dhall | 500000 |
+-------+------------+-------------+
1 row in set (0.00 sec)

26. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name contains &#39;a&#39; as the second last character.

mysql> SELECT AccNo, Cust_Name, Loan_Amount FROM Loan_Accounts WHERE Cust_Name

LIKE '%a_'; -- Q26
+-------+------------+-------------+
| AccNo | Cust_Name | Loan_Amount |
+-------+------------+-------------+
| 4 | M.P. Yadav | 800000 |
+-------+------------+-------------+
1 row in set (0.00 sec)

27. Display the details of all the loans in the ascending order of their Loan_Amount.

mysql>
mysql> SELECT * FROM Loan_Accounts ORDER BY Loan_Amount ASC; -- Q27
 44
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
| 6 | P. Sharma | 700000 | 60 | 12.50 | 2008-06-05 | NULL | NULL |
| 4 | M.P. Yadav | 800000 | 60 | 10.00 | 2008-12-06 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
7 rows in set (0.00 sec)

28. Display the details of all the loans in the descending order of their Start_Date.

mysql> SELECT * FROM Loan_Accounts ORDER BY Start_Date DESC; -- Q28

+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 4 | M.P. Yadav | 800000 | 60 | 10.00 | 2008-12-06 | NULL | NULL |
| 6 | P. Sharma | 700000 | 60 | 12.50 | 2008-06-05 | NULL | NULL |
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
7 rows in set (0.00 sec)

29. Display the details of all the loans in the ascending order of their Loan_Amount and
within Loan_Amount in the descending order of their Start_Date.

mysql> SELECT * FROM Loan_Accounts ORDER BY Loan_Amount ASC, Start_Date DESC; --

Q29
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| AccNo | Cust_Name | Loan_Amount | Instalments | Int_Rate | Start_Date | Interest | Category |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
| 5 | S.P. Sinha | 200000 | 36 | 12.50 | 2010-01-03 | NULL | NULL |
| 1 | R.K. Gupta | 300000 | 36 | 12.00 | 2009-07-19 | NULL | NULL |
| 3 | K.P. Jain | 300000 | 36 | NULL | 2007-03-08 | NULL | NULL |
| 2 | S.P. Sharma | 500000 | 48 | 10.00 | 2008-03-22 | NULL | NULL |
| 7 | K.S. Dhall | 500000 | 48 | NULL | 2008-03-05 | NULL | NULL |
| 6 | P. Sharma | 700000 | 60 | 12.50 | 2008-06-05 | NULL | NULL |
| 4 | M.P. Yadav | 800000 | 60 | 10.00 | 2008-12-06 | NULL | NULL |
+-------+-------------+-------------+-------------+----------+------------+----------+----------+
7 rows in set (0.00 sec)
 45
30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.

mysql>
mysql> UPDATE Loan_Accounts SET Int_Rate = 11.50 WHERE Int_Rate IS NULL; -- Q30
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0

31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more
than 400000.

mysql> UPDATE Loan_Accounts SET Int_Rate = Int_Rate + 0.5 WHERE Loan_Amount > 400000;
-- Q31
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0

32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.

mysql> UPDATE Loan_Accounts SET Interest = (Loan_Amount * Int_Rate * Instalments) /

(12*100); -- Q32
Query OK, 7 rows affected (0.09 sec)
Rows matched: 7 Changed: 7 Warnings: 0

33. Delete the records of all the loans whose start date is before 2007.

mysql> DELETE FROM Loan_Accounts WHERE Start_Date < '2007-01-01'; -- Q33

Query OK, 0 rows affected (0.00 sec)

34. Delete the records of all the loans of &#39;K.P. Jain&#39;

mysql> DELETE FROM Loan_Accounts WHERE Cust_Name = 'K.P. Jain'; -- Q34

Query OK, 1 row affected (0.09 sec)

35. Add another column Category of type CHAR(1) in the Loan table.

mysql>ALTER TABLE Loan_Accounts ADD Category CHAR(1);

Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
 46
4)Write the MYSQL statements for the following questions based on the given
table

mysql>

mysql> use ip;

Database changed

mysql> CREATE TABLE Course (

-> C_ID VARCHAR(10),

-> C_Name VARCHAR(50),

-> Duration_Months INT,

-> Fees INT,

-> Mode VARCHAR(20),

-> Student_Name VARCHAR(30)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> INSERT INTO Course (C_ID, C_Name, Duration_Months, Fees, Mode, Student_Name) VALUES

-> ('C01', 'Artificial Intelligence', 12, 100000, 'Online', 'Rahul'),

-> ('C02', 'Python Programming', 6, 20000, 'Online', 'Preethi'),

-> ('C03', 'CCNA', 3, 15000, 'Regular', 'Akila'),

 47
-> ('C04', 'Cyber Security', 18, 200000, 'Regular', 'Akshara'),

-> ('C02', 'Python Programming', 6, 20000, 'Online', 'Arpit'),

-> ('C04', 'Cyber Security', 18, 200000, 'Regular', 'Nirmal'),

-> ('C05', 'Cloud Computing', 6, 15000, 'Online', 'Rakshana'),

-> ('C04', 'Cyber Security', 18, 200000, 'Regular', 'Diya');

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

i) Display the Course name without duplicates

mysql>

mysql> SELECT DISTINCT C_Name FROM Course;

+-------------------------+

| C_Name |

+-------------------------+

| Artificial Intelligence |

| Python Programming |

| CCNA |

| Cyber Security |

| Cloud Computing |

+-------------------------+

5 rows in set (0.00 sec)

ii) Display details of students who opted for “Cyber Security”

mysql>

mysql> SELECT * FROM Course WHERE C_Name='Cyber Security';

+------+----------------+-----------------+--------+---------+--------------+

| C_ID | C_Name | Duration_Months | Fees | Mode | Student_Name |

+------+----------------+-----------------+--------+---------+--------------+

| C04 | Cyber Security | 18 | 200000 | Regular | Akshara |

 48
| C04 | Cyber Security | 18 | 200000 | Regular | Nirmal |

| C04 | Cyber Security | 18 | 200000 | Regular | Diya |

+------+----------------+-----------------+--------+---------+--------------+

3 rows in set (0.00 sec)

iii) Display the Course Name and Fees where duration is 6 months

mysql>

mysql> SELECT C_Name, Fees FROM Course WHERE Duration_Months=6;

+--------------------+-------+

| C_Name | Fees |

+--------------------+-------+

| Python Programming | 20000 |

| Python Programming | 20000 |

| Cloud Computing | 15000 |

+--------------------+-------+

3 rows in set (0.00 sec)

iv) Change duration of Cyber Security to 1 year (12 months)

mysql> UPDATE Course SET Duration_Months=12 WHERE C_Name='Cyber Security';

Query OK, 3 rows affected (0.02 sec)

Rows matched: 3 Changed: 3 Warnings: 0

v) Add column “Fee_Payment_Status” next to Fees

mysql> ALTER TABLE Course ADD Fee_Payment_Status VARCHAR(20) AFTER Fees;

Query OK, 8 rows affected (0.05 sec)

Records: 8 Duplicates: 0 Warnings: 0

vi) Delete details of students who opted for “Cloud Computing”

mysql> DELETE FROM Course WHERE C_Name='Cloud Computing';

Query OK, 1 row affected (0.00 sec)

49