October, 2023 - Swapnil Saurav

Oct 3 2023

Learn Python and Data Science – WkDay SEP 2023

This page is a live document and will be updated with content as we make progress

DAY 1: Introduction to Python - Installation

print(“skdjidsjgdfji”)
print(“5+3=”, 5+3)

num1 = 5
num2 = 3
print(num1 + num2)
num1 = 21
print(num1 – num2)

# print() –
print(“5+3=”,5+3,“and 6+4=”,6+4,end=” : “);
# every print() statement has an invisible newline \n
print(     “5+5=”,5+5,“and 6+14=”,6+14);

print(“Twinkle Twinkle”,  end=” ”   ) # sample comment  fgjdtjgdhjcghjgh
print(“Little star”)
# indentation is key!
#semicolon ; exist in Python but its not mandatory
print(“Hi there”); print(“How are you?”)
# syntax : grammer of human language

#variables – stores temporary values
var1 = 5
# create a variable by name var1 which currently has the value 5
var2 = 10
var1 = 20
print(var1 + var2)
#data which variables have
# data types- what kind of values a variable has
#basic data types: int (integer – no decimal part):  -5, -2,0,5,999…
# type() gives the current data types
print(“Type of var1 = “,type(var1))

# datatype – float: with decimal types: -5.9, -3.8978888,0.0, 5.7
var3 = 20.5
print(“datatype(var3): “, type(var3))

# complex numbers – square root of minus numbers
# square root of -1 is i (in maths) – j (in Python)
# sq root of -16:  16 * -1 = 4j
var4 = 3+4j
print(“Data type of var4 = “,type(var4))
# (3+4j)*(3-4j) = 9 – (-16)= 9+16 = 25 + 0j
print((3+4j)*(3–4j))
# 3.2 + 2.8 =6.0

# 4th datatype – text type is called –  str (string)
var5 = “hello” #string data will always be in quotes:  ‘ or “
print(“datatype(var5) =”,type(var5))

# 5th data type = boolean (bool)
# bool = True or False only
var6 = False
print(“Data type of var6 =”,type(var6))
# var6 is an object of class bool – meaning var6 inherits all the
# properties of class bool
var7 = ‘FALSE’

# program is run successfully when  exit code is 0
quantity = 17
price = 48
total_cost = quantity * price
print(“Cost of each pen is”,price,“so the total cost of”,quantity,“pens would be”,total_cost)
# format string
print(f”Cost of each pen is {price} so the total cost of {quantity} pens would be {total_cost}“)
”’
refer to lines 51 to 56, write below programs:
1. WAP to calulcate area and perimeter of a rectangle
2. WAP to calculate area and circunference of a circle
”’

var1 = 5
print(var1)
var1 = “Five”
print(var1)

DAY 2: Basic Programs

”’
#about variables
1. variable name should start with a text
2. variable name can have digits and _
”’
cost = 17
quantity = 5
price = cost * quantity
print(“The cost of each pen is”,cost,“so the total cost of”,quantity,“pens will be”,price)
# format string / f-string
print(f”The cost of each pen is {cost} so the total cost of {quantity} pens will be {price}“)

price = 100
quantity = 33
cost = price / quantity
print(“The cost of each pen is”,cost,“so the total cost of”,quantity,“pens will be”,price)
# format string / f-string
print(f”The cost of each pen is {cost:.1f} so the total cost of {quantity} pens will be {price}“)

player,country,position = “Kohli”,“India”,“Opener”
print(f”Player {player:<12} plays for the country {country:>10} and is {position:^15} for the team.”)

player,country,position = “Manbwange”,“Zimbabwe”,“Wicket-Keeper”
print(f”Player {player:<12} plays for the country {country:>10} and is {position:^15} for the team.”)
print(f”Player {player:.<12} plays for the country {country:>10} and is {position:X^15} for the team.”)

# input dynamic value from the user
a = input(“Enter a number: “)
print(“You have entered:”,a)
print(f”1. Data type of {a} is {type(a)}“)
b= 100  #assigning an integer
c=“100” #assigning a string
# input() since it cant predict what value is being entered,
# it assumes that all the input value is a string
a = input(“Enter a number: “)
a= int(a)  #explicit conversion of data types
print(f”2. Data type of {a} is {type(a)}“)

# int(), float(), str(), bool(), complex()

## escape sequeence – \ works only for one character after it appears
## to add or remove super power
print(“We are talking about\nis for new line”)
print(“We are talking about\tis for new line”)
print(“We are talking about\\tis for new line”)
print(“We are talking about\\nis for new line”)

# \\n in python prints \n
print(“\\\\n in python prints \\n”)

DAY 3: Formatting print()

# Different operators
# Arithematic operators
num1,num2 = 59,10
print(num1 + num2) #addition
print(num1 – num2) # subtraction
print(num1 * num2) # multiplication
print(num1 / num2)  #division – 5.0
print(2 ** 3)  #power
print(num1 // num2)  #integer division
print(num1 % num2) # % mod – remainder

# comparison /relational operators
# >  <  >=   <=  ==(is it equal?)  !=
#anything as input – output is always bool
num1,num2 = 59,10
print(“Greater”)
print(num1 > num2)  #True
print(num1 >= num1) #True
print(num1 > num1) # False
print(“Smaller”)
print(num1 < num2)  # False
print(num1 <= num2) # False
print(num1 < num1) # False
print(“Equal”)
print(num1 == num2)  # False
print(num1 == num1) # True
print(num1 != num2)  # True
print(num1 != num1) # False

# logical operators:
# operators: and or not
#input and output are all bool values
# prediction: Rahul and Rohit will open the batting
# actual: Rohit and Gill opened the batting
# and (*): True and True = True , rest all the 3 options are False
# or (+): False or False = False , rest all the 3 options are True
# prediction: Rahul or Rohit will open the batting
# actual: Rohit and Gill opened the batting
print(num1 > num2 or num1 >= num1 and num1 > num1 or num1 < num2 and num1 <= num2 or num1 < num1
      and num1 == num2 or num1 == num1 and num1 != num2 or num1 != num1)
”’
True
”’
print(not True)
print(not False)

####  ####
”’
Condition and iterations
Conditions are handled using if
”’
avg = 39
# if avg >= 40 then say pass otherwise say fail
if avg >=40:
    print(“You have passed!”)

if avg >=40:
                             print(“You have passed!”)
                             print(“PASSSSSSSSSSSSS”)
else:
    print(“You have failed!”)

DAY 4: Operators in Python

# conditions in Python
# WAP to find sum, avg of 3 subjects marks and check if pass or fail
”’
Any program is made up of: 3 parts: input, process, output

”’
# 1. input marks in 3 subjects
m1 = int(input(“Enter the marks in subject 1: “))
m2 = int(input(“Enter the marks in subject 2: “))
m3 = int(input(“Enter the marks in subject 3: “))
#process: calculating total and avg
total = m1 + m2 + m3
avg = total /3
if avg >=40:
    # output – when if condition becomes True
    print(“You have passed”)
else:  # when if is False
    print(“You have failed”)

#WAP to check if a number is positive or not
num1 = int(input(“Enter a number: “))
if num1 >0:
    print(f”{num1} is positive”)
elif num1 <0:
    print(f”{num1} is negative”)
else:
    print(f”{num1} is neither positive nor negative”)


#WAP to check if a number is odd or even
num1 = int(input(“Enter a number to check if its odd or even: “))
if num1>=0:
    print(“Can be odd or even”)

    if num1 % 2 == 0:
        print(f”{num1} is an even number”)
    else:
        print(f”{num1} is an odd number”)
else:
    print(“Negative numbers are not fit”)

”’
Assignment: input a number and check if its positive or negative.
If positive check if the number is divisible by 2 , 3, 7.
if its divisible by 7 then check if its even or odd.
If its odd check if its greater than 100 or not
”’
”’
Take average and check if pass or fail and
 then assign grade based on:
A : avg > 85%
B : avg > 75%
C : avg > 60%
D: avg > 40%
E: avg < 40%
”’
# 1. input marks in 3 subjects
m1 = int(input(“Enter the marks in subject 1: “))
m2 = int(input(“Enter the marks in subject 2: “))
m3 = int(input(“Enter the marks in subject 3: “))
#process: calculating total and avg
total = m1 + m2 + m3
avg = total /3
if avg >=40:
    # output – when if condition becomes True
    print(“You have passed”)
    if avg>=85:
        print(“Grade: A”)
    elif avg>=75:
        print(“Grade: B”)
    elif avg>=50:
        print(“Grade: C”)
    else:
        print(“Grade: D”)
else:  # when if is False
    print(“You have failed”)
    print(“Grade: E”)

”’
Loops – execute same block of code more than once
1. For loop: when you know exactly how many times to run the loop
2. While loop: when you dont know exactly how many times
    but you know  a perticular condition till when to run
”’
# working of range(): generates range of values
”’
range(start,end,increment): range() takes 3 values. Start indicates
the starting value of the series. End indicates last but one value(upto end, 
end is not included). increment will increase the start value
range(3,12,3) – 3,6,9

range(start,end): increment is default 1
range(3,8): 3,4,5,6,7

range(end): default start is 0 and increment is 1
range(5): 0,1,2,3,4
”’

for i in range(3,12,3):
    print(“Hello and the value of i is”,i)

for i in range(3,8):
    print(“Hello there and the value of i is”,i)

for i in range(5):
    print(“Hello and the value of i is”,i)

DAY 5: IF CONDITION

#while will have always have condition, as long as the condition
# is true, while loop will repeat
count = 0
while count < 5:
    print(“HELLO”)
    count=count+1
ch=“y”
while ch==“y”:
    print(“Do you want to continue?”)
    ch=input(“Enter y to continue or any other key to stop: “)
# for loop example using range
for i in range(5):
    print(“*”,end=” “)

”’
* * * * *
* * * * *
* * * * *
* * * * *
* * * * *
”’
for j in range(5):
    for i in range(5):
        print(“*”,end=” “)
    print()

”’
* 
* *
* * *
* * * *
* * * * *
”’
for j in range(5):
    for i in range(j+1):
        print(“*”,end=” “)
    print()

”’
* * * * *
* * * *
* * *
* *
* 
”’
for j in range(5):
    for i in range(5-j):
        print(“*”,end=” “)
    print()

”’
    * 
   * * 
  * * * 
 * * * * 
* * * * * 
”’
for j in range(5):
    for k in range(5-j-1):
        print(” “,end=“”)

    for i in range(j+1):
        print(“*”,end=” “)
    print()
”’
Assignment:

* * * * * 
 * * * * 
  * * * 
   * * 
    * 
”’

REFER DOC AT SCRIBD FOR BASIC PROGRAMMING CONCEPTS

# While loop in Python
”’
Write a program to keep checking if a number is odd or
even until user enters a negative number
”’

num1 = int(input(“Enter the number: “))
while num1 >=0:
    if num1%2 ==0:
        print(“Its even!”)
    else:
        print(“Its odd!”)
    num1 = int(input(“Enter the number: “))

cont = “y”
while cont ==“y”:
    num1 = int(input(“Enter first number: “))
    num2 = int(input(“Enter second number: “))
    print(“Menu:”)
    print(“1. Addition”)
    print(“2. Subtraction”)
    print(“3. Multiplication”)
    print(“4. Division”)
    print(“5. Quit”)
    op = input(“Enter the option from above menu (1 or 2 or 3 or 4 or 5): “)
    if op==“1”:
        print(“Sum of the given two numbers are: “,num1 + num2)
    elif op==“2”:
        print(“Difference of the given two numbers are: “,num1 – num2)
    elif op==“3”:
        print(“Product of the given two numbers are: “,num1 * num2)
    elif op==“4”:
        print(“Ratio of the given two numbers are: “,num1 / num2)
    elif op==“5”:
        cont = “n”
    else:
        print(“You have not given a valid option, start from beginning!”)

”’
Assignment: Modify the above program so that user is asked to enter numbers
only when they choose options 1 to 4.
”’

while True:
    num1 = int(input(“Enter first number: “))
    num2 = int(input(“Enter second number: “))
    print(“Menu:”)
    print(“1. Addition”)
    print(“2. Subtraction”)
    print(“3. Multiplication”)
    print(“4. Division”)
    print(“5. Quit”)
    op = input(“Enter the option from above menu (1 or 2 or 3 or 4 or 5): “)
    if op==“1”:
        print(“Sum of the given two numbers are: “,num1 + num2)
    elif op==“2”:
        print(“Difference of the given two numbers are: “,num1 – num2)
    elif op==“3”:
        print(“Product of the given two numbers are: “,num1 * num2)
    elif op==“4”:
        print(“Ratio of the given two numbers are: “,num1 / num2)
    elif op==“5”:
        break
    else:
        print(“You have not given a valid option, start from beginning!”)

# checking the condition: Entry check & Exit check
”’
WAP to generate Fibonacci series numbers:
0,1,1,2,3,5,8,13….

1. first 5 numbers of the series
2. till user wants
”’
print(“Fibonacci numbers are (using For loop):”)
n = 10
f,s=0,1
for i in range(n):
    if i<2:
        print(i,end=” , “)
    else:
        t = s+f # 0,1,
        print(t, end=” , “)
        f=s
        s=t

print(“\nFibonacci numbers are (using While loop):”)
print(“Enter any key to stop: “)
f,s=0,1
counter = 1
while True:
    if counter ==1:
        print(f, end=” , “)
    elif counter ==2:
        print(s, end=” , “)
    else:
        t = s+f
        print(t, end=” , “)
        f=s
        s=t

    counter += 1 #counter=counter + 1
    cont = input()
    if len(cont)>0:
        break

##
”’
Develop guessing number game:
Computer (giving the number) v Human (Guessing the number)
”’
import random
#random is a module which has functions related to random number generation
num = random.randint(1,100)
attempts = 0
while True:
    guess = int(input(“Enter the number to guess (1-100): “))
    if guess <1 or guess >100:
        print(“Invalid guess!”)
        continue
    attempts+=1  #attempts= attempts+1
    if guess == num:
        print(f”Congratulations! You have guessed it correctly in {attempts} attempts!”)
        break
    elif guess < num:
        print(“Try to guess a higher number!”)
    else:
        print(“Try to guess a lower number!”)

”’
Develop guessing number game:
Human (giving the number) v Computer (Guessing the number)
”’
import random
#random is a module which has functions related to random number generation
num = 55
attempts = 0
low,high = 1,100
while True:
    #guess = int(input(“Enter the number to guess (1-100): “))
    guess = random.randint(low,high)
    if guess <1 or guess >100:
        print(“Invalid guess!”)
        continue
    attempts+=1  #attempts= attempts+1
    if guess == num:
        print(f”Congratulations! You have guessed it correctly in {attempts} attempts!”)
        break
    elif guess < num:
        print(f”Try to guess a higher number than {guess}!”)
        low = guess+1
    else:
        print(f”Try to guess a lower number than {guess}!”)
        high=guess-1

”’
Develop guessing number game:
Computer (giving the number) v Computer (Guessing the number)
”’
import random
#random is a module which has functions related to random number generation
num = random.randint(1,100)
attempts = 0
low,high = 1,100
while True:
    #guess = int(input(“Enter the number to guess (1-100): “))
    guess = random.randint(low,high)
    if guess <1 or guess >100:
        print(“Invalid guess!”)
        continue
    attempts+=1  #attempts= attempts+1
    if guess == num:
        print(f”Congratulations! You have guessed it correctly in {attempts} attempts!”)
        break
    elif guess < num:
        print(f”Try to guess a higher number than {guess}!”)
        low = guess+1
    else:
        print(f”Try to guess a lower number than {guess}!”)
        high=guess-1

DAY 7: PYTHON LOOP EXAMPLES

# Strings – used for handling text data
str1 = ‘HELLO’ \
       ‘i’
str2 = “How are you”
str3 = ”’I am fine
I am doing alright
I am super”’
str4 = “””I am here
I am there
I am everywhere”””
print(str1, str2, str3, str4)
print(type(str1),type(str2),type(str3),type(str4))
str5 = “How are”
str6 = “You?”
print(str5 +” “+ str6)
print(“Hello ” + str(5))
print(“HELLO ” * 5)

#for loop
for i in str6:
    print(“running the loop: “,i)

# indexing
str7 = “I am fine here how are you there”
print(“Length: “,len(str7))
# position of each character starts from 0
print(str7)
print(“First character of str7 = “,str7[0])
print(“Second character of str7 = “,str7[1])
print(“Last character of str7 = “,str7[ len(str7) – 1 ])
print(“Last character of str7 = “,str7[  – 1 ])
print(“Second last character of str7 = “,str7[  – 2 ])
print(“6th to 9th characters: “,str7[5:9])

DAY 8: String tutorial - 1

# Strings
str1 = “I am fine”
# first 3
print(str1[0:3])
#last 3
print(str1[-3:])
#
print(type(str1))
#methods are the functions defined under a class
# is.. is for asking question
# isalpha() is like asking is the object alphabets
print(“Is alpha: “,str1.isalpha())
str2 = “helothere”
print(“Is alpha: “,str2.isalpha())

num1= input(“Enter a number: “)
if num1.isdigit():
    num1 = int(num1)
else:
    print(“Invalid number!”)
print(“Str1 isit titlecase:”, str1.istitle())
print(“Str1 convert to title case: “,str1.title())
print(“Str1 is it uppercase: “,str1.isupper())
print(“Str1 is  uppercase: “,str1.upper())
print(“Str1 is it lowercase: “,str1.islower())
print(“Str1 is  lowercase: “,str1.lower())
str2 = “I am fine I am good how are you am fine?”
print(“count of o: “,str2.count(“am”))
print(“count of o: “,str2.count(“am”,5,15))
print(“Split: “,str2.split(‘am’))
l1 = [‘I ‘, ‘ fine I ‘, ‘ good how are you ‘, ‘ fine?’]
print(“Join: “,“am”.join(l1))
start = 3
if str2.count(“am”,start)>0:
    print(“Index: “,str2.index(“am”,start))
else:
    print(“Given text not found in str2”)
print(str2.replace(“am”,“AM”))

#  votingin india you need to be over 18 yrs and nationality India
nationality=“” #initialize
age = input(“Enter your age:”)
if age.isdigit():
    age = int(age)
    if age>=18:
        #age criteria matched
        nationality = input(“Enter you country of nationality:”)
        if nationality.lower()==“india”:
            print(“You are eligible to vote in India”)
        else:
            print(“Your nationality doesnt match”)
    else:
        print(“Your age criteria not matched”)
else:
    print(“Not a valid age, hence exiting the program”)

print(“Printing nationality: “,nationality)

# Strings are immutable – you cant edit the value of a string
#nationality[2]=”d” TypeError: ‘str’ object does not support item assignment

# Collections – multiple values in one variable
# list – mutable ordered collection
list1 = [5,10,“Hello”, 6.5, True]
print(type(list1))
print(“first member: “,list1[0])
print(“last member: “,list1[-1])
print(“last 3 member: “,list1[-3:])
print(“first 3 member: “,list1[:3])
print([1,2,3] + [“First”,“Second”,“Third”])
print([1,2,3] * 4)

for i in list1:
    print(f”{i} => {type(i)}“)

for i in range(len(list1)):
    print(f”{list1[i]} => {type(list1[i])}“)

###
list1 = [5,10,“Hello”, 6.5, True]
if list1.count(“hello”) > 0:
    idx = list1.index(“hello”)
    list1.pop(idx)
else:
    print(“hello is not in the list”)

# pop() looks for index, remove() looks for the value
list1.remove(“Hello”)
print(list1)

# add value to the list:
# append() – will add values at the end of the list
# insert() – needs value and also index
list1.append([2,4,6,8])
list1.insert(1,“Sachin Tendulkar”)
print(“After adding: “,list1)
print(list1[-1][2])  # how to access list inside another list

DAY 9: String Tutorial - 2 & List - 1

#LIST – 2
# list is an ordered mutable collection
# methods like append(), insert(), remove(), pop(),
#count(), index()
#wap to read marks of 5 students in their 5 subjects
marks = []
for i in range(5):
    print((f”Enter the marks for student {i+1} “))
    tlist=[]
    for j in range(5):
        m = int(input(f”Enter the marks in subject {j+1}: “))
        tlist.append(m)
    marks.append(tlist)

print(“Marks are:”,marks)
”’
[[45, 46, 89, 89, 98], [90, 98, 81, 67, 76], [45, 55, 58, 57, 60], 
[5, 4, 5, 7, 8], [3, 6, 8, 9, 9]]
”’

marks=[[45, 46, 89, 89, 98], [90, 98, 81, 67, 76], [45, 55, 58, 57, 60],
       [5, 99, 5, 7, 8], [5, 99, 8, 9, 9]]
marks.sort()
print(marks)
marks.sort(reverse=True)
print(marks)
marks.reverse()
print(marks)
l1 = [1,2,3]
l2 = [5,6,7]
l1.extend(l2)  # l1 = l1 + l2
print(“L1: “,l1)

# copy
l3=l1 # l3 and l1 point to the same dataset(location in memory)
l4 =l1.copy()  #shallow copy – it creates a new copy
print(“1. L1: “,l1)
print(“1. L3: “,l3)
print(“1. L4: “,l4)
l1.append(11)
l1.append(12)
l3.append(13)
l4.append(15)
print(“2. L1: “,l1)
print(“2. L3: “,l3)
print(“2. L4: “,l4)

l1.clear()
print(“L3 = “,l3)

### TUPLES  ###
# Tuple – ordered immutable collection
t1 =()
print(type(t1))
t1=(1,2,3,4,1,2,3,1,2,1)
print(“Count = “,t1.count(2))
print(“Index = “,t1.index(2))
t1 = (4,)
print(type(t1))
t1 = (4,5)  #packing
print(type(t1))
a,b = t1  #unpacking
print(a,b)
t1 = list(t1)  #converting a tuple to list
t1 = tuple(t1)  #converting a list to tuple
#accessing tuples are faster than accessing lists

t1=(10,30,20,40)
#iteration (loop) and indexing is exactly like List/String
print(t1[1])

#Dictionary: unordered collection – doesnt have index, instead
# dictionary has key:value pair – as a user you have to give the key
dict1 = {1: “Five”, “Eight”: 80}
print(type(dict1))
print(dict1)
print(dict1[‘Eight’])

marks = {}
for i in range(3):
    n = input(f”Enter Student’s name: “)
    tlist=[]
    for j in range(3):
        m = int(input(f”Enter the marks in subject {j+1}: “))
        tlist.append(m)

    marks.update({n:tlist})

print(“Final list:\n“,marks)

”’
{‘S’: [3, 6, 9], ‘R’: [1, 5, 9], ‘P’: [9, 5, 1]}
”’

dict2 = {‘S’: [3, 6, 9], ‘R’: [1, 5, 9], ‘P’: [9, 5, 1]}
print(“All Keys are: “,dict2.keys())
print(“All Values are: “,dict2.values())
# items() will give key and values pairs
print(“Items are: “,dict2.items())

for k,v in dict2.items():
    print(f”{k} = {v}“)

dict3 = dict2.copy()  #shallow copy
dict4 = dict2   #deepcopy
dict2.update({‘Q’:{4,6,9}})
print(“Dict3 – copy: “,dict3)
print(“Dict4 – =: “,dict4)

# pop() and popitem() both are used to remove data
#popitem() has no arguments – it will delete on of its own (latest added)
# pop() takes the key and that perticular key:value pair is deleted
dict2.popitem()
print(“2. Dict4 – =: “,dict4)
dict2.pop(“R”)
print(“3. Dict4 – =: “,dict4)
dict2 = {‘S’: [3, 6, 9], ‘R’: [1, 5, 9], ‘P’: [9, 5, 1]}
print(dict2[‘S’])
print(“Get key for the given value: “,dict2.get(“S”))  #another way of getting the value
dict2 = {‘S’: [3, 6, 9], ‘R’: [1, 5, 9], ‘P’: [9, 5, 1]}
x = dict2.setdefault(“T”,{0,0,0})
print(x)
print(dict2)
dict2[“T”] = {10,20,30}
print(dict2)

DAY 10: List 2, Tuple, Dictionary

#SETS – linear unordered mutable collection
set1 = {}
print(type(set1))
set1 = {5,7,8,11, 11,11,8,7}
print(type(set1))
print(“Length = “,len(set1))
print(set1)
# set operations like: Union, Intersection, Difference
set1 = {5,7,8,11}
set2 = {3,4,11,8,7}
print(“union”)
print(set1 | set2)
print(set1.union(set2))
print(“intersection (filter common)”)
print(set1.intersection(set2))
print(set1 & set2)
print(“Difference”)
print(set1.difference(set2))
print(set1 – set2)
print(set2.difference(set1))
print(set2 – set1)
print(“Symmetric difference”)
print(set2.symmetric_difference(set1))
print(set2 ^ set1)

print(set1.isdisjoint(set2))
#set1.copy() – deep copy
set1.clear()
print(“Set 1: “,set1)
# sets, lists and tuples can be converted into each others form
set2 = {3,4,11,8,7}
set2 = list(set2)
set2.append(19)
set2 = set(set2)

## Functions ###
”’
1. inbuilts functions: print(), len(), set(), list() …..
2. user defined functions:
3. one line functions
”’
def list_of_questions():
    print(“How are you?”)
    print(“Where are you?”)
    print(“Are you coming?”)

list_of_questions()
print(“printing list of questions once again!!!!!!”)
list_of_questions()


# function definition: of no input argument
def sum_two_nums():
    num1 = 45
    num2 = 38
    add = num1 + num2
    print(“Sum of two numbers is”,add)

# function definition: of taking input argument
def sum_two_nums1(num1, num2):
    print(f’num1 = {num1} and num2 = {num2}‘)
    add = num1 + num2
    print(“Sum of two numbers is”,add)

# function definition: of taking input argumenta
# and returning values
# 1. required positional arguments: both required and positional
def sum_two_nums2(num1, num2):
    print(f’num1 = {num1} and num2 = {num2}‘)
    add = num1 + num2
    #print(“Sum of two numbers is”,add)
    return add

# default positional
def sum_two_nums3(num1, num2=9):
    print(f’num1 = {num1} and num2 = {num2}‘)
    add = num1 + num2
    #print(“Sum of two numbers is”,add)
    return add


#calling functions
sum_two_nums()
x = 38
y = 67
#calling functions by passing two values
sum_two_nums1(x, y)

#calling functions by passing two values and catching the return value
result = sum_two_nums2(27,37)
print(‘Function has returned ‘,result)

result = sum_two_nums3(27,39)
print(‘Function has returned ‘,result)
result = sum_two_nums3(27)
print(‘Function has returned ‘,result)

# how to make arguments non-positional
# by using keywords while passing the value
result = sum_two_nums3(num2 = 27,num1 = 39)
print(‘Function has returned ‘,result)

DAY 11: SETS and Function Introduction

# functions
”’
1. required: you have to provide value to this argument
2. positional: takes the value based on the position
3. default (non-required):
4. keyword (non-positional)
”’
def func1(n1,n2):
    print(“N1 = “,n1)
    print(“N2 = “,n2)
    add = n1 + n2
    return add

def func2(n1,n2=66): #assigning a default value
    print(“N1 = “,n1)
    print(“N2 = “,n2)
    add = n1 + n2
    return add

def func3(n1=-1,n2=0): #assigning a default value
    print(“N1 = “,n1)
    print(“N2 = “,n2)
    add = n1 + n2
    return add

def fun4(s1,s2,s3,*s,**info):
    print(“Values of S are:”,s)
    if len(s)==0:
        print(“We are dealing with a triangle”)
    elif len(s)==1:
        print(“We are dealing with a square or a rectangle”)
    elif len(s)==2:
        print(“We are dealing with a Pentagon”)
    elif len(s)==3:
        print(“We are dealing with a Hexagon”)
    elif len(s)==4:
        print(“We are dealing with a Heptagon”)
    elif len(s)==5:
        print(“We are dealing with a Octagon”)
    elif len(s)>5:
        print(“We are dealing with a shape greater than 8 sides”)

    print(“INFO = “,info)
    if “name” in info.keys():
        print(f”Name of the player is {info[‘name’]}“)
    if “game” in info.keys():
        print(f”The player plays {info[‘game’]}“)
    if “city” in info.keys():
        print(f”The player lives in {info[‘city’]}“)

# docstring is the multi line comment added in the first line in a function
def checkprime(num):
    ”’
    This is a function that takes parameter and returns
    True for Prime and False for non Prime
    :param num: value to check
    :return: True for Prime and False for Non-Prime
    ”’
    isPrime = True
    if num<2:
        isPrime = False
    elif num>2:
        for i in range(2,num//2 + 1):
            if num%i==0:
                isPrime = False
                break
    return isPrime

”’
10! = 10*9!
”’
def recurfacto(n):
    if n==0:
        return 1
    return n*recurfacto(n-1)


if __name__ ==“__main__”:

    result = func1(10,20)
    print(“Total is”,result)
    result = func2(10)
    print(“Total is”,result)
    result = func2(20,50)
    print(“Total is”,result)
    result = func3(2,5)
    print(“Total is”,result)
    result = func3()
    print(“Total is”,result)

    result = func3(n2=6)
    print(“Total is”,result)
    fun4(1,2,3,4,5,6,7,8, name=“Sachin”,game=“Cricket”,city=“Mumbai”)
    fun4(1,2,3, name=“Virat”,game=“Cricket”)
    for i in range(10,20):
        result = checkprime(i)
        if result:
            print(f”{i} is Prime”)
        else:
            print(f”{i} is not Prime”)

    # using the same above function to generate prime numbers between 5000 and 6000
    print(“Prime numbers are:”)
    for i in range(5000,6001):
        result = checkprime(i)
        if result:
            print(i,end=“, “)
    print()

    result =recurfacto(10)
    print(“10 factorial is”,result)

import p1 as MyOwnModule
from p1 import checkprime,fun4

if __name__==“__main__”:
    print(“Running PY18.py”)
    #printing doc string
    print(checkprime.__doc__)
    #result = p1.checkprime(97)
    MyOwnModule.checkprime(97)
    result = checkprime(97)
    if result:
        print(“97 is prime”)
    else:
        print(“97 is not a prime”)

DAY 12: FUNCTIONS COMPLETE TUTORIAL

a = “50”  # creating an object of class str
print(type(a))
b = “hello”
print(type(b))
b.upper()

class Book:
    # class members: variables and methods
    total_books = 0 #class variable
    def __init__(self, title, author): # object method
        self.title = title
        self.author = author
        Book.total_books +=1
    def put_data(self): # object method
        print(“Title = “,self.title)
        print(“Author = “,self.author)

    @classmethod
    def count_book(cls):
        print(“Total books in the shelf is “,cls.total_books)

b1 = Book(“Python”,“Sachin”)# init is called when we create object
print(type(b1))
print(“Total books =”,b1.total_books)
b2 = Book(“Data Science”,“Virat”) # __init__() is called
print(type(b2))
print(“Total books =”,b2.total_books)
#b1.input_data(“Python”,”Sachin”)  #calling method
b1.put_data()
#b2.input_data(“Data Science”,”Virat”)
b2.put_data()
print(“Total books =”,b1.total_books)
b2.count_book()
b1.count_book()
Book.count_book()

DAY 13: CLASS & OBJECTS Session - 1

”’
Properties of class & objects:
1. Inheritance:
2. Polymorphism:
3. Abstraction: hiding implementation detail
4. Encapsulation: hiding information
”’
# program to implement addition, subtraction, multiplication, division
class Super_Op:
    def __init__(self):
        print(“B2 is initialized”)

    def Class_Output1(self):
        print(“Output from Super_Op”)
class Math_Op(Super_Op):
    def __init__(self,n):
        self.n = n
    def val_square(self):
        return self.n ** 2
    def val_squarert(self):
        return self.n ** 0.5
    def Class_Output1(self):
        print(“Output from Math_Op”)
class B2:
    def __init__(self):
        print(“B2 is initialized”)

    def Class_Output1(self):
        print(“Output from B2”)
class Arith_Op (B2,Math_Op):

    def __init__(self, n1,n2):
        Math_Op.__init__(self,n1)
        # call Math_Op init to make sure the data is initialized
        self.n1 = n1
        self.n2 = n2
    def Add(self):
        return self.n1 + self.n2
    def Sub(self):
        return self.n1 – self.n2
    def Mul(self):
        return self.n1 * self.n2
    def Div(self):
        return self.n1 / self.n2
    def Class_Output1(self):
        print(“Output from Arith_OP”)

a1 = Arith_Op(20,10)
a2 = Arith_Op(200,100)
a3 = Arith_Op(120,110)
a4 = Arith_Op(520,110)
print(“Addition: “,a1.Add())
print(“Subtraction: “,a2.Sub())
print(“Multiplication: “,a3.Mul())
print(“Division: “,a4.Div())

b1 = Math_Op(50)
print(“Square root: “,b1.val_squarert())
print(“Square: “, b1.val_square())

a1.val_square()
print(“Square root: “,a1.val_squarert())
print(“Square: “, a1.val_square())
a1.Class_Output()

DAY 14: CLASS & OBJECTS Session - 2

”’
Properties of class & objects:
1. Inheritance:
2. Polymorphism:
3. Abstraction: hiding implementation detail
4. Encapsulation: hiding information: 3 types of accessibility provided are:
 4.1: public
 4.2: protected
 4.3: private
”’
# program to implement addition, subtraction, multiplication, division
class Super_Op:
    def __init__(self):
        print(“B2 is initialized”)

    def Class_Output(self):
        print(“Output from Super_Op”)

    def _Class_Output2(self): #protected
        print(“Output from Super_Op – Protected”)

    def __Class_Output2(self): #private
        print(“Output from Super_Op – Private”)
class Math_Op(Super_Op):
    def __init__(self,n):
        self.n = n
    def val_square(self):
        return self.n ** 2
    def val_squarert(self):
        return self.n ** 0.5
    def Class_Output(self):
        print(“Output from Math_Op”)
class B2:
    def __init__(self):
        print(“B2 is initialized”)

    def Class_Output(self):
        print(“Output from B2”)
class Arith_Op (B2,Math_Op):

    def __init__(self, n1=0,n2=0):
        Math_Op.__init__(self,n1)
        # call Math_Op init to make sure the data is initialized
        self.n1 = n1
        self.n2 = n2
    def Add(self):
        return self.n1 + self.n2
    def Sub(self):
        return self.n1 – self.n2
    def Mul(self):
        return self.n1 * self.n2
    def Div(self):
        return self.n1 / self.n2
    def Class_Output1(self):
        print(“Output from Arith_OP”)

class testClass:
    def testMethod(self):
        s1 = Super_Op()
        s1.Class_Output()
        s1._Class_Output2()
        ”’ Protected members is being called but technically it shouldnt be
        possible to call – this is not yet implemeted in Python”’
        #s1.__Class_Output2()
        # above method of Super_Op is not accessible.
”’
testClass is no way connected to any of the classes above but still it can
call Super_Op class members. – Public
so, public members can be called by any class.

protected: if you want to make any member protected, you need to add _(single
underscore) before the name. protected members can be called by derived classes only.
BUT THIS IS STRICTLY NOT IMPLEMENTED IN PYTHON!

private: if you want to make any member private, you need to add __(double
underscore) before the name. Private members can be accessed only by the members of 
the same class.
”’
t1 = testClass()
t1.testMethod()

a1 = Arith_Op(10,20)

m1 = Math_Op(4)
m1._Class_Output2()
#m1.__Class_Output2() – you cant call since its private

##################
”’
handling external files:
.text, .json  .csv

How to handle file reading:
import os
”’

import os
print(“Operating system is use: “,os.name)
# nt platform for windows
# posix platform for Mac, Unix, Linux
# java etc…

if os.name==“nt”:
    #windows related commands
    print(“You are working with Windows machine”)

    # to clear the screen – cls
    os.system(‘cls’)
    #create directory
    os.mkdir(“Test1”)
elif os.name==“posix”:
    print(“You are working on UNIX/Linux/Mac machine”)
    # those related commands you use
    os.system(‘clear’)
else:
    print(“Some other platform. Please check”)

############
”’
Modes of file:
r : read
w : write
a : append (writing additional content to existing file)
r+ : read and write mode: file should be existing
w+ : write and read mode: file neednt be there
a+: append and read
”’

poem=”’Twinkle Twinkle little star
How I wonder what you are
Up above the world so high
like a diamond in the sky”’
print(poem)

# write – all the content
# write line – adding line by line
# write lines – multiple lines – content has to be in []
# step 1: create a file pointer – needs where and how
file_ptr = open(“Test1/abc.txt”,“a”)
file_ptr.write(poem)
file_ptr.close()

file_ptr = open(“Test1/abc.txt”,“r”)
read_content = file_ptr.read()
file_ptr.close()
print(“Content read from the file:\n“,read_content)

DAY 15: CLASS & OBJECTS Session - 3 and Working with Files Session - 1

# reading external files – .txt file
# read and write to a file

filptr = open(“Test1\\abc.txt”,“r”)
# read
content = filptr.read()
print(“1 Content of the file is: \n“,content)
filptr.seek(10)
content = filptr.read(30)
print(“2 Content of the file is: \n“,content)
content = filptr.read(30)
print(“3 Content of the file is: \n“,content)
filptr.seek(0)

# readline
content = filptr.readline()
print(“1. Line:  Content of the file is: \n“,content)
content = filptr.readline(50000)
print(“2. Line:  Content of the file is: \n“,content)
filptr.seek(0)

# readlines
content = filptr.readlines()
print(“1. Lines:  Content of the file is: \n“,content)

# closing the file
filptr.close()

filptr = open(“Test1\\abc.txt”,“w”)  #operations same as append
# write
content=“””Ba Ba Black sheep
Do you have any wool
Yes sir yes sir
three bags full”””

filptr.write(content)
## writelines
content=[‘\nHello there\n‘,‘How are you doing?\n‘,
         ‘I am fine\n‘,‘Doing awesome\n‘]
filptr.writelines(content)
#close
filptr.close()

”’
Mini Project:
Write a .txt file based storage program to store data about Indian batsman 
for the world cup 2023. And pick up the highest score of each player through
the python code.

example: store player and their highest score
Rohit 43
Gill 55
Rahul 21
Iyer 5
Virat 86
”’

# reading external files – .csv file
import csv
header = [‘SNO’,‘NAME’,‘COUNTRY’,‘HIGHEST’]
info = [[‘1’,‘Rohit’,‘India’,43],[‘2’,‘Gill’,‘India’,67],
        [‘3’,‘Iyer’,‘India’,4],[‘4’,‘Virat’,‘India’,93]]
fileptr = open(“Test1\\abc.csv”,mode=“w”,newline=”)
fileptr_csv = csv.writer(fileptr, delimiter=“,”)
fileptr_csv.writerow(header)

for i in info:
    fileptr_csv.writerow(i)

fileptr.close()

fileptr = open(“Test1\\abc.csv”,mode=“r”,newline=”)
fileptr_csv = csv.reader(fileptr, delimiter=“,”)
for i in fileptr_csv:
    print(i[1],” – “,i[3])
fileptr.close()

”’
Modify the above program to print the name and the score of the highest player only
”’

DAY 16: Working With Files Session - Text and CSV files

# Working files – json file
# dump, dumps, load, loads
players_data = {
    “Players”:[
        {
            “Name”: “Rohit”,
            “Type”:“Batsman”,
            “City”:“Mumbai”
        },
        {
            “Name”: “Siraj”,
            “Type”:“Bowler”,
            “City”:“Hyderabad”
        },
        {
            “Name”: “Virat”,
            “Type”:“Batsman”,
            “City”:“Delhi”
        },
        {
            “Name”: “Ashwin”,
            “Type”:“Bowler”,
            “City”:“Chennai”
        }
    ]

}
import json

# write to a file
fileptr = open(“Test1\\data1.json”,“w”)
json.dump(players_data,fileptr, indent=4)

print(json.dumps(players_data,indent=4, sort_keys=True))
fileptr.close()

# reading a json file
fileptr = open(“Test1\\data1.json”,“r”)
json_content = json.load(fileptr)
print(json_content)
jason_txt = ”'{“Players”: [{“Name”: “Rohit”, “Type”: “Batsman”, “City”: “Mumbai”},

 {“Name”: “Siraj”, “Type”: “Bowler”, “City”: “Hyderabad”}]}”’
#loads actually reads data from the screen – data will be in a string format not
# as json format
json_content2 = json.loads(jason_txt)
print(json_content2)
print(json_content2[‘Players’])
print(json_content2[‘Players’][1][‘Name’])
for i in json_content2[‘Players’]:
    print(i[‘Name’])
fileptr.close()

DAY 17: Working With Files Session - JSON files

”’
Errors:
  1. Syntax error: rule not correctly written
  2. Logical error: made error in writing the logic
  3. Exceptions: runtime errors
”’
num1 = 5
num2 = 2
print(f”Sum of {num1} and {num2} is {num1-num2}“)

num1 = int(input(“Enter a number: “))
print(“Value entered is “,num1)
# ValueError:
# handling runtime errors are called Exception handling

try:
    num1 = int(input(“Enter a number: “))
except ValueError:
    print(“Since you have entered an invalid number, we are stopping here!”)
else:
    print(“This is a valid number”)
finally:
    print(“Thank you for using my application”)

”’
WAP to divide 2 numbers
”’
num1,num2 = 0,0
while True:
    try:
        num1 = int(input(“Enter first number: “))
    except ValueError:
        print(“Invalid number! Try again…”)
    else:
        break

while True:
    try:
        num2 = int(input(“Enter second number: “))
    except ValueError:
        print(“Invalid number! Try again…”)
    else:
        try:
            div = num1 / num2
        except ZeroDivisionError:
            print(“Denominator cant be Zero! Try again…”)
        else:
            break

div = num1 / num2
print(“Division value is:”,div)
print(“Thank you for using my application”)

DAY 18: Exception Handling - part 1

def myfun1(a,b):
    print(a+b)
    return a+b

def myfun2():
    print(“I do nothing”)

class Sample:
    def __init__(self):
        print(“Object created”)
    def printinfo(self):
        print(“Some output here”)

if __name__ ==“__main__”:
    myfun1(99,87)
    myfun1(99,7)
    s1 = Sample()

=====================

#import p11 as RocketScience
from p11 import  myfun1

#RocketScience.myfun2()
print(myfun1(5,10))

import random
random.random()

###########  FILE HANDLING
#mode of file handling: r (read), w (write-delete old content and write new), a (Append)
##  r+ (read & write), w+ (write & read), a+  (append and read)

filename = “abc.txt”

fileobj = open(filename,“r+”)
content = ”’This is a sample content
story about a king
and a queen
who lived in a jungle
so I am talking about
Lion the kind of jungle”’

fileobj.write(content)
content2 = [‘THis is sample line 1\n‘,‘line 2 content \n‘,‘line 3 content \n‘]
fileobj.writelines(content2)
fileobj.seek(20)
output = fileobj.read()
print(“Content from the file:\n“,output)
fileobj.seek(10)
output = fileobj.read()
fileobj.seek(10)
content3 = fileobj.read(15)
content4 = fileobj.readline()
print(“Content from the file:\n“,output)

fileobj.seek(0)
content5 = fileobj.readlines()
print(“Content from the file:\n“,content5)

fileobj.close()

## Exception handling
#SyntaxError : print(“Hello)
#logical error: you make error in the logic – very difficult to find
#runtime errors (exceptions):

num1 = int(input(“Enter a number: “))
# ValueError exception

DAY 20: MODULES, READING TEXT FILE, ERRORS IN PYTHON

# Exceptions
a=“k”
b=10
c=-1
try:
c = b/d

except ZeroDivisionError:
print(“Denominator is zero hence stopping the program from executing”)
except TypeError:
print(“Invalid numbers, hence exiting…”)
except NameError:
print(“One of the values has not been defined. Try again”)
except Exception:
print(“Not sure but some error has occurred, we need to stop”)
else:
print(“Answer is”,c)
finally:
print(“We have completed division process”)
#
class InvalidLength(Exception):
def __init__(self,value=0):
self.value = value

length, breadth = –1,-1
while True:
try:
length = int(input(“Enter length: “))
except ValueError:
print(“Invalid number, try again…”)
else:
#assert length > 0, “Rectangle with this diamension is not possible”
if length <=0:
try:
raise InvalidLength
except InvalidLength:
print(“Invalid value for Length hence resetting the value to 1”)
length=1
break
while True:
try:
breadth = int(input(“Enter breadth: “))
except ValueError:
print(“Invalid number, try again…”)
else:
assert breadth>0,“Rectangle with this diamension is not possible”
break

area = length * breadth
print(“Area of the rectangle is”,area)
## ### ##
# datetime, date, time

from datetime import datetime, timedelta
import time
from pytz import timezone

curr_time = datetime.now()
print(“Current time is”,curr_time)
print(“Current time is”,curr_time.strftime(“%d / %m /%Y”))
print(curr_time.year, curr_time.day, curr_time.date())
for i in range(5):
time.sleep(1) # sleep for 2 seconds
print(“Time left:”,5-i,“seconds”)
print(“Good Morning”)
print(“Current time is”,datetime.now())
print(“Date 2 days back was”,(curr_time-timedelta(days=2)).strftime(“%d/%m/%Y”))
print(“UTC Time is”,datetime.now(timezone(‘UTC’)))
print(“UTC Time is”,datetime.now(timezone(‘US/Eastern’)))
print(“UTC Time is”,datetime.now(timezone(‘Asia/Kolkata’)))

DAY 21 Tutorial: Exception Handling And Datetime Module

DAY 22 TUTORIAL - Database Lesson - 1

DAY 23 : Database Lession 2 - MYSQL

Access the slides from here

Download MYSQL database link:

https://dev.mysql.com/downloads/installer/

DAY 24 : Database Lession 3 : Working With MYSQL

Create table employees.Employees( EMPID INT Primary Key auto_increment, FNAME VARCHAR(55) NOT NULL, LNAME VARCHAR(55), DOB DATE, EMAIL VARCHAR(35) unique, PHONE VARCHAR(11), DOJ DATE Default(‘2021-07-20’), — YYYY-MM-DD SALARY FLOAT(2), DEPTID INT, Foreign Key (DEPTID) References Departments(DID), Constraint U_UC_LN_DOB Unique(LNAME,DOB), CHECK(Salary 0.0) ) — Constraints: Primary Key, Foreign Key, Not Null, Unique, Check, Default

DAY 25 TUTORIAL - DATABASE LESSON 4 - CREATE TABLE

— Modifying a table – Table is already created and in use – ALTER TABLE

— ADD or DELETE (DROP) or MODIFY or RENAME a Column

— DDL command to delete is DROP

— DDL command to modify is ALTER

use employees;

ALTER table employees ADD BONUS Float(3);

ALTER table employees ADD dummy Float(3);

ALTER TABLE Employees DROP COLUMN dummy;

ALTER TABLE EMPLOYEES MODIFY COLUMN BONUS float(4)

ALTER TABLE EMPLOYEES RENAME COLUMN BONUS to BONUS_PCT;

DAY 26 TUTORIAL - Database Lesson 5- ALTER TABLE

use employees;

— create table, drop table, alter table

— to read data we use Select

Select * from departments;

insert into departments (DID, HOD, DNAME, DCODE) values (100,’Sachin’,’CSE’,’AA00′);

insert into departments values (101,’MEC’,’Virat’,’AZ00′);

insert into departments values (103,’ECE’,’Rohit’,’KZ00′);

insert into departments values (105,’CIV’,’Dhoni’,’CZ00′);

insert into departments values (106,’TCE’,’Sunil’,’BZ00′);

Select * from employees;

Select @@GLOBAL.secure_file_priv;

— Import data into CSV

LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/EmployeesData.csv’

INTO TABLE EMPLOYEES

FIELDS TERMINATED BY ‘,’

ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\n’

IGNORE 1 ROWS;

— DELETE to remove a row

DELETE FROM EMPLOYEES WHERE EMPID=120;

— Update to modify the existing values in a row

Update Employees

Set Bonus_PCT = 0.45, Salary = 160000

where empid=122;

Select * from employees where empid=122

— SELECT

Select FNAME, EMAIL, PHONE, SALARY From Employees;

Select FNAME, EMAIL, PHONE, SALARY From Employees where salary >=100000;

Select FNAME, EMAIL, PHONE, SALARY From Employees where EMAIL =’ramakrishnavendra@wnbco.co’;

— Create Index to make query faster

Create Index idx_salary

on Employees (Salary)

— Relational operators in MYSQL: = > < >= <= <>

Select FNAME, EMAIL, PHONE, SALARY From Employees where EMAIL <>’ramakrishnavendra@wnbco.co’;

— BETWEEN LIKE IN

DAY 27 Tutorial - DB Session 6 : INSERT & SELECT WITH WHERE

use employees;

select * from employees;

select FNAME, LNAME, DOB, Salary from employees where salary > 75000;

— BETWEEN is used for numbers

select FNAME, LNAME, DOB, Salary from employees where salary BETWEEN 75000 AND 95000;

— below statement is same as above (BETWEEN)

select FNAME, LNAME, DOB, Salary from employees where salary >= 75000 AND salary <= 95000;

select FNAME, LNAME, DOB, Salary from employees where salary >= 75000 AND Bonus_pct <= 0.4; — 0 to 9 rows

select FNAME, LNAME, DOB, Salary from employees where salary >= 75000 OR Bonus_pct <= 0.4; — 25 to 34 rows

select FNAME, LNAME, DOB, Salary from employees where salary >= 75000 — 25 rows

select FNAME, LNAME, DOB, Salary from employees where Bonus_pct <= 0.4; — 9 rows

— LIKE – for text data comparison

Select * from employees where FNAME like ‘A%’

— IN – checking for given set of values

Select * from employees where FNAME IN (‘O%’, ‘A%’,’B%’);

Select * from employees where FNAME IN (‘Anikeet Dey’,’Amol Jain’,’Suresh Narayan Singh Yadav’ );

Select * from employees where SALARY IN (36107, 110266, 107799, 198890);

— Logical operator – AND OR NOT

select * from employees where not( deptid = 100 and salary >100000)

— ORDER BY

select * from employees order by salary DESC;

select * from employees order by salary ASC; — Ascending order is by default

select * from employees order by DEPTID ASC, Salary DESC;

DAY 28 DB Session 7: SELECT - Where - AND OR NOT BETWEEN IN LIKE

use employees;

select * from employees where bonus_pct is not null;

select * from employees order by FNAME;

select * from employees order by FNAME DESC;

UPDATE employees SET BONUS_PCT = NULL WHERE BONUS_PCT > 0.88;

select * from employees where bonus_pct is not null limit 3;

select * from employees where bonus_pct is not null order by salary DESC limit 5;

select avg(salary) as Max_Salary, deptid from employees group by deptid;

select avg(salary), count(salary), sum(salary) from employees;

select * from employees where salary > (select avg(salary) from employees);

— Find highest salary earner for each department

select deptid, MAX(salary) as max_salary FROM employees group by deptid;

select deptid, fname, salary from employees where (Deptid, salary) in

(select deptid, MAX(salary) as max_salary FROM employees group by deptid);

DAY 29 DB Session 8: ORDER BY, GROUP BY, MAX, MIN, AVG, SUM, COUNT

use employees;

select deptid, avg(salary) as “Average Salary” from employees group by deptid;

— COUNT, AVG, SUM, MIN, MAX

— Like: %a, a%, %a% , %b__% (exactly 1 value for _), ‘a%z’

select fname, email, salary from employees;

select fname, email, salary from employees where fname like ‘%ol%’;

— J followed by exactly three characters

select fname, email, salary from employees where fname like ‘%J___’;

— J followed by atleast four characters

select fname, email, salary from employees where fname like ‘%J____%’;

select * from employees where fname in (‘Pankaj Prajapati’,’Manju Mishra’ ,’Arijeet Dasgupta’);

select * from employees where fname in (select fname from employees where fname like ‘%J____%’);

select * from employees where (fname,salary) in (select fname,salary from employees where fname like ‘%J____%’);

select fname, email, salary from employees where fname like ‘%dey’;

DAY 30 DB Session 9: Database Project Discussion, GROUP BY, LIKE, IN, SUB QUERY

— combining data from 2 tables

use employees;

select * from employees;

select employees.FNAME, DID, departments.DNAME from employees, departments

select employees.FNAME, DID, departments.DNAME from employees, departments where employees.deptid = departments.did;

insert into departments values (107, ‘BIOT’,’Kapil’,’BB09′)

insert into employees (EMPID, FNAME, LNAME, DOB, EMAIL,PHONE, DOJ,SALARY)

values (133,’Sachin Tendulkar’,’T’,’1990-05-04′,’sachin@wnbco.co’,9999000009,’2023-05-15′,231456);

select * from departments;

select employees.FNAME, DID, departments.DNAME from employees, departments where employees.deptid = departments.did;

— JOINS

use employees;

select * from employees;

select * from departments;

select fname, hod from employees, departments where departments.did = employees.DEPTID; — Inner

select fname, hod from employees, departments where employees.DEPTID = departments.did order by empid;

select fname, hod from employees INNER JOIN departments ON employees.DEPTID = departments.did order by empid;

SELECT T1.COL1, T2.COL2, T3.COL3

FROM (((T1 INNER JOIN T2 ON T1.K1 = T2.K1) INNER JOIN T3 ON T2.K2 = T3.K2) INNER JOIN T4 ON T3.K3 = T4.K3);

select fname, hod from employees LEFT JOIN departments ON employees.DEPTID = departments.did order by empid;

DAY 31 - DB SESSION 10 Tutorial Day: JOINS

use employees;

select fname, hod from employees left join departments on employees.DEPTID = departments.did

UNION

select fname, hod from departments left join employees on employees.DEPTID = departments.did;

select fname, hod from departments inner join employees on employees.DEPTID = departments.did;

select * from employees;

select sum(EMPID) from employees;

select count(EMPID) from employees;

select count(EMPID) from employees where salary > 50000;

select count(EMPID) , DEPTID from employees

group by deptid

having count(EMPID) > 5;

select count(fname), hod from employees left join departments on employees.DEPTID = departments.did

group by deptid

having count(fname) > 3;

select * from employees;

— Salary_Grade: <50K: E 50-100K: D 100-150K – C 150-200-B >200K: A

select fname, salary, bonus_pct,

case when bonus_pct is null then salary

else salary+salary*bonus_pct

end as total_salary from employees;

select fname, salary,

CASE

when salary < 50000 Then ‘Grade: E’

when salary > 200000 Then ‘Grade: A’

when salary > 150000 Then ‘Grade: B’

when salary > 100000 Then ‘Grade: C’

Else ‘Grade: D’

End As Salary_Grade

from employees;

DAY 32 - DB SESSION 11 : JOINS, HAVING & CASE

use employees;

select * from employees;

select FNAME, DOB, DOJ, datediff(doj,dob) from employees;

select fname, NOW() from employees;

select now(); — current date and time (system)

select date(now());

select curdate();

— default date format in MYSQL is YYYY-MM-DD

— DD-MM-YYYY

select date_format(curdate(), ‘%d-%m-%Y’) as Date;

select curdate() as todays_date, date_add(curdate(),interval 1 Day) as Tomorrow,

date_add(curdate(),interval 1 Week) as next_week,

date_add(curdate(),interval 1 Month) as Next_month,

date_add(curdate(),interval 1 Year) as Next_year;

— Add date: Interval, day, week or month or year;

select curdate() as todays_date, date_sub(curdate(),interval 1 Day) as Yesterday,

date_sub(curdate(),interval 1 Week) as last_week,

date_sub(curdate(),interval 1 Month) as last_month,

date_sub(curdate(),interval 1 Year) as last_year;

select day(curdate()) as day, month(curdate()) as Month, quarter(curdate()) as Quarter, Year(curdate()) as Year,

Weekday(curdate()) as Weekday, week(curdate()) as week, weekofyear(curdate()) as WeekofYear;

select * from employees where salary > (select salary from employees where empid = 104);

— create a stored procedure

DELIMITER $$

Create Procedure GetEmpInfo()

Begin

select * from employees where salary > (select salary from employees where empid = 104);

End $$

DELIMITER ;

call GetEmpInfo();

drop Procedure GetEmpInfo;

— Features: exception handling, conditions and loops (While, Repeat),

— Creating a View

create view EmpData

Select EMPID, FNAME, LNAME, DOB, EMAIL, PHONE, DOJ, DEPTID from Employees;

select * from empdata;

— String functions: LTRIM, RTRIM, Replace, substring, COncat

select concat(fname,’.’,lname,’@wnbco.co’) from employees;

select fname, substring(fname, 3), substring(fname, -3) from employees;

DAY 33 - DB SESSION 12: DATE FUNCTIONS, STRING FUNCTIONS, PROCEDURES, VIEWS

import pymysql
con_str = pymysql.connect(host=“localhost”, user=“root”, password=“learnSQL”,database=“employees”)
cursor = con_str.cursor()

q1 = ”’Create Table PY_EMP (
EMPID INT PRIMARY KEY,
NAME VARCHAR(30),
CITY VARCHAR(15))
”’
#cursor.execute(q1)

q2 = ”’INSERT INTO PY_EMP VALUES(1,’Laxman’,’Hyderabad’)”’
cursor.execute(q2)
q2 = ”’INSERT INTO PY_EMP VALUES(2,’Rahul’,’Bangalore’)”’
cursor.execute(q2)

q3 = ”’Select * from Py_Emp”’
cursor.execute(q3)
results = cursor.fetchall()
for row in results:
print(row)

con_str.commit()

con_str.close()

DAY 34 -PYTHON TUTORIAL: Working With Database From Python

REST OF THE CONTENT IS IN PART 2. CLICK HERE TO ACCESS PART 2

admin 0 Comments

DAY 73: Power BI (Coming Soon)

DAY 74: Tableau (Coming soon)

Thats the end of the course - entire content in presented in 4 blog pages

About Us

Quick Links

Services

Contact Us