8 - MySql Practical for class 12 CS and IP

 

8.  Write the SQL Query to increase 20% salary of the employee whose experience is more than 5 year of the table Emp(id, name, salary, exp)

 

7 - MySql Practical for class 12 CS and IP

 

7.       Write the SQL Query to display the difference of highest and lowest salary of each department having maximum salary greater than 4000.

 

6 - MySql Practical for class 12 CS and IP

 

6.       Shiva is using a table employee. It has following details: Employee(Code, Name, Salary, DeptCode). Write the SQL query to display maximum salary department wise.

 

5 - MySql Practical

         

5.       Write the SQL query to find out the square root of 26.

 

2 - MySql Practical

         

2.       Shiva is using a table with the following details:

Students(Name, Class, Stream_id, Stream_Name)

Write the SQL query to display the names of students who have not been assigned any stream or have been assigned Stream_Name that end with “computers”

 

1 - MySql Practical

    

1.       Shiva, a student of class XII, created a table “CLASS”. Grade is one of the columns of this table. Write the SQL query to find the details of students whose grade have not been entered. 

3.2 Creating Bar Charts in matplotlib

 

Creating Bar Charts:

A Bar chart or a Bar Graph is a graphical display of data using bars of different heights. A bar chart can be drawn vertically or horizontally using rectangles or bars of heights/widths PyPlot offers bar() function to create vertical bar chart and barh() function for horizontal bar.

 

a, b = [1,2,3,4], [2,4,6,8]

plt.bar(a,b)

plt.xlabel(“Values”)

plt.ylabel(“Doubles”)

plt.show()

 

Changing Widths of the Bar:

Default width of a bar is 0.8 units

#Same Width for all bar

plt.bar(x,y,width=<float value>)                              

For different width of all bar

width = [0.5, 0.6, 0.7, 0.8]

 

Changing Color of the Bar:

Same color for all bar

plt.bar(x, y, color=<color code / name>)

Different color for all bar

color=[‘red’,’b’,’g’,’black’]

 

Assignment - 3

1.       Create a bar chart which display the name of four cities on x axis and population of those cities on y axis. 

2.       Create a bar chart which display the types of medals (Gold, Silver, Bronze, Total) on x axis and number of medals won by India.

3.       Create a bar chart which display the types of medals (Gold, Silver, Bronze, Total) on x axis and number of medals won by India and medals won by Australia in Same chart.

4.       Create a bar chart which display the types of medals (Gold, Silver, Bronze, Total) on x axis and number of medals won by India with different bar width and different color.

5.       Val is a list having three lists inside it. It contains summarized data of three different trials conducted by a company. Create a bar chart that plots these subtitles of Val in a Single chart. Keep the width of each bar as 0.25

 

3.1 Data Visualization using matplotlib in Python

 

Data Visualization using matplotlib in Python

LINE CHART

Data Visualization:

                Data Visualization refers to the graphical or visual representation of information and data using visual elements like charts, graphs, maps etc.

 

PyPlot:

                PyPlot is a collection of methods within matplotlib library of Python, Which allow user to create 2D Plots easily. Matplotlib is a Python library.

To install matplotlib use the following command.

Pip install matplotlib

    Type of basic charts of PyPlot

1.       Line Chart: A line chart is created using plot() method.

2.       Bar Chart: A vertical bar chart is created using bar() method and horizontal bar chart using barh() method.

3.       Histogram Plot: A histogram is created using hist() method

4.       Pie Chart: A pie chart is created using pie() method.

5.       Scatter Plot: A scatter plot is created using scatter() method.

 

import matplotlib.pyplot as plt

x=[1,2,3,4,5]

y=[2,3,4,5,6]

plt.plot(x,y)        #draw a line chart

plt.xlabel(“x axis values”)

plt.ylabel(“y axis values”)

plt.legend()        #show the legend

plt.show()           #show the chart/plot as per given specification

 

 

import matplotlib.pyplot as plt

x=[1,2,3,4,5]

y=[1,2,3,4,5]

plt.plot(x,y)

plt.show()

 

 

 

import matplotlib.pyplot as plt

x=["Ram","Raj","Sam","john","Amit"]

y=[30,20,25,40,35]

plt.plot(x,y)

plt.title("Marks Sheet")

plt.xlabel("Student's Name")

plt.ylabel("Marks")

plt.show()

 

 

 

import matplotlib.pyplot as plt

x=[2,3,4,5,6,7]

y=[1,2,3,4,5,6]

plt.plot(x,y)

plt.show()

 

 

import matplotlib.pyplot as plt

x=["VII","VIII","IX","X"]

y=[40,50,35,45]

plt.bar(x,y)

plt.show()

 

Specifying Plot Size and Grid

 

plt.figure(figsize=(width,length))

 

plt.grid(True)

 

Changing Line Color and Style

 

Plt.plot(x,y,color_code)

 

 

Color Code

 

‘r’ red

‘g’ green

‘b’ blue

‘m’ magenta

‘y’ yellow

‘k’ black

‘c’ cyan

‘w’ white

 

 

Change Line Width

 

linewidth=width

plt.plot(x,y,linewidth=2)

 

Line Style

 

linestyle or ls =[‘solid’ | ‘dashed’ | ‘dashdot’ | ‘dotted ]

 

ls=’:’ | ‘-‘  | ‘_ _’ | ‘-‘

 

plt.plot(x,y,linewidth=4,linestyle=’dashed’)

 

 

Assignment - 1

 

1.       WAP to plot a line chart to depict the changing weekly onion prices for four weeks. Give appropriate axis labels.

 

2.       Marks is a list that stores marks of a student in 10 unit tests. Write a program to plot the student’s performance in these 10 unit tests.

 

3.       Ram is doing some research. He has a stored line of pascal’s triangle numbers as ar2 as shown below:

ar2 = [1,7,21,35,21,7,1]

·         He want to plot the sine, cosine, and tangent values for the same array.

·         He wants cyan color for sine line, red color for cosine line and black color for tangent line

·         Tangent line should be dashed. 

Changing Marker Type, Size and Color

 

The data points being plotted on a graph/chart are called markers.

 

marker=<marker_type>, markersize=<in points>, makeredgecolor=<valid color>

 

plt.plot(x,y,’k’,marker=’d’,markersize=5,markeredgecolor=’red)

 Types of marker:

 

.	,	o	+
X	D	d	s
P	*	h	H
1	2	3	4
V	^	<	>
|	_

 

 

plt.plot(x,y,’ro’)

 

Display only marker not line

Assignment - 2

 

1.       First 10 terms of Fibonacci series are stored in a list namely fib:

 

fib = [0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

 

Write a program to plot Fibonacci terms and their square-roots with two separate lines on the same plot.

(a)    Series should be plotted as a cyan line with ‘o’ markers having size as 5 and edge-color as red.

(b)   The square-root series should be plotted as a black line with ‘+’ markers having size as 7 and edge-color as red.

 

2.       Given a series nfib that contains reversed Fibonacci numbers with Fibonacci numbers as show below:

[0, -1, -1, -2, -3, -5, -8, -13, -21, -34, 0, 1, 1, 2, 3, 5, 8, 13, 21, 34]

 

Write a program to plot nfib with following specifications:

 

(a)    The line color should be magenta

(b)   The marker edge color should be black with size 5

(c)    Grid should be displayed

1.2 Series in Pandas

 1.2 Series in Pandas

Adding NaN values in a Series:

You can specify missing or empty value using np.NaN or None

 

S = pd.Series([6.5,np.NaN,2.34])

 

import pandas as pd

import numpy as np

S=pd.Series([6.5,np.NaN, 2.34])

print(S)

 

S=pd.Series([6.5,None, 2.34])

print(S)

Specify Index in Series:

S = pd.Series(data=data_values, index=index_values)

 

1.       Create a Series with month name as index and no. of days in month as data of series.

 

import pandas as pd

I=['Jan','Feb','Mar','Apr']

D=[31,28,31,30]

S=pd.Series(D,I)                                               #by default first argument is data and 2^nd is Index

print(S)

 

S=pd.Series(I,D)                                               #by default first argument is data and 2^nd is Index

print(S)

 

S=pd.Series(index=I,data=D)

print(S)

 

S=pd.Series(data=D,index=I)

print(S)

 

1.       A List namely section that store the section names of class 12 and another list contri store the contribution made by students section wise. Create a Series for these data.

 

import pandas as pd

section=['A','B','C','D']

contri=[6700,5600,5000,5200]

S=pd.Series(data=contri,index=section)

print(S)

Specify data type in Series

S = pd.Series(data=None, index=None, dtype=None)

import pandas as pd

import numpy as np

months=['Jan','Feb','Mar','Apr']

days=[31,28,31,30]

S=pd.Series(data=days,index=months,dtype=np.float64)

print(S)

 

1.1 Series in Pandas

1.1 Series in Pandas

Pandas:

Pandas is a Python Library. Pandas stands for “Panel Data System”

Series:

Series is a data structure. Which can store 1 D homogeneous data.

DataFrame:

DataFrame is a 2 D Data Structure, which can store 2 D Heterogeneous data.

Series	Data Frame
Series is a data structure	Data is a data structure
Series is 1 Dimensional	DataFrame is 2 Dimensional
Series is Homogeneous	DataFrame is Heterogeneous
Value Mutable	Value Mutable
Size Immutable	Size Mutable

 

 

How To create:

1.       Using Python Sequence (List, Tuple, String)

2.       Using ndarray

3.       Using Python Dictionary

4.       Using Scalar Value

 

Create a Series using List

 

Assignment: 1 (Create a Series)

1.       WAP in Python to create a Series using Python Sequence [4, 6, 8, 10].

2.       WAP in Python to create a Series using Python Sequence (11, 21, 31, 41).

3.       WAP in Python to create a Series using individual characters ‘o’, ’h’, and ‘o’.

4.       WAP in Python to create a Series using a string “So Funny”.

5.       WAP in Python to Create a Series using three words: “I”, “am”, “Laughing”.

6.       WAP to create a Series using ndarray that has 5 elements in the range 24 to 64.

7.       WAP to create a Series using ndarray that is created by that is created by tilling a list [3, 5] twice.

8.       WAP to create a Series using dictionary that stores the number of students in each section of class 12 in your school.

9.       WAP to create a Series that store the initial budget allocated 500000/ - each for the quarters of the year: Qtr1, Qtr2, Qtr3, Qtr4.

10.   Total numbers of medals to be won is 200 in the games held every alternate year. WAP to Create a Series that store these medals for games to be help in the decade 2020-2029.

 

 

#Series: 1 : - WAP in Python to create a Series using Python Sequence [4, 6, 8, 10].

 

import pandas as pd

L=[4, 6, 8, 10]

S=pd.Series(L)

print(S)

 

 

#Series: 2 : - WAP in Python to create a Series using Python Sequence (11, 21, 31, 41).

 

import pandas as pd

T=(11, 21, 31, 41)

S=pd.Series(T)

print(S)

 

#Series: 3 : - WAP in Python to create Series using individual

#characters ‘o’, ’h’, and ‘o’.

import pandas as pd

C=['o','h','o']

S=pd.Series(C)

print(S)

 

 

#Series: 4 : - WAP in Python to create Series using a string “So Funny”.

import pandas as pd

C="So Funny"

S=pd.Series(C)

print(S)

 

#Series: 5 : - WAP in Python to create Series using three words: “I”, “am”, “Laughing”.

import pandas as pd

C=["I","am","Laughing"]

S=pd.Series(C)

print(S)

 

 

#Series: 6 : - WAP in Python to create Series using ndarray that has 5 elements

#in the range 24 to 64.

import pandas as pd

import numpy as np

E=np.linspace(24,64,5)

S=pd.Series(E)

print(S)

 

 

#Series: 7 : - WAP in Python to create Series using ndarray that is created

#by by tilling a list [3, 5] twice.

import pandas as pd

import numpy as np

E=np.tile([3,5],2)

S=pd.Series(E)

print(S)

 

 

#Series: 8 : - WAP to create a Series using dictionary that stores the number

#of students in each section of class 12 in your school.

import pandas as pd

D={'A':42,'B':48,'C':51,'D':47}

S=pd.Series(D)

print(S)

 

 

#Series: 9 : - WAP to create a Series that store the initial budget allocated

#500000/ - each for the quarters of the year: Qtr1, Qtr2, Qtr3, Qtr4.

import pandas as pd

I=['Qtr1','Qtr2','Qtr3','Qtr4']

S=pd.Series(500000,index=I)

print(S)

 

 

#Series: 10 : - Total numbers of medals to be won is 200 in the games held every

#alternate year. WAP to Create a Series that store these medals for games to be

#help in the decade 2020-2029.

import pandas as pd

I=list(range(2020,2029,2))

S=pd.Series(200,index=I)

print(S)