IP Practical

Set 1
#1
import pandas as pd

import matplotlib.pyplot as plt

x=['Java','Python','PHP','JavaScript','C#','C++']

y=[22.2,17.6,8.8,8,7.7,6.7]

plt.bar(x,y,color=['r','k','blue','yellow','pink','orange'])

plt.xlabel('Languages')

plt.ylabel('Popularity')

plt.title('Bar Chart')

plt.show()

#2
import pandas as pd

import numpy as np

a={'name':['Anas','Dim','Kat','Jam','Emil','Mich','Mat','Lau','Kev','Jon'],\

'score':[12.5,9.0,16.5,np.nan,9.0,20.0,14.5,np.nan,8.0,19.0],\

'attempts':[1,3,2,3,2,3,1,1,2,1],\

'qualify':['yes','no','yes','no','no','yes','yes','no','no','yes']}

df=pd.DataFrame(data=a,index=['a','b','c','d','e','f','g','h','i','j'])

print(df)
print()

print(df.loc[:,'name':'score'])

#3

1)mysql> Create database IP;

mysql> create table PATIENT

-> (NO int(2),

-> NAME varchar(9),

-> AGE int(3),

-> DEPARTMENT varchar(15),

-> CHARGES int(4),

-> GENDER varchar(2));

2)mysql> insert into PATIENT

-> (2,'ZARINA',22,'HISTORYENT',250,'F'),

-> (3,'KAREEM',32,'ORTHOPAEDIC',200,'M'),

-> (4,'ARUN',12,'SURGERY',300,'M');

3)mysql> select count(DEPARTMENT) from PATIENT;

4)mysql> select*from patient

5)mysql> select name, gender from patient

-> order by name asc;

7)mysql> select sum(charges)

-> where gender='M';

Set 2
#1
import matplotlib.pyplot as plt

x=[2,4,6,8,10]

y=[6,7,8,2,4]

x2=[1,3,5,7,9]

y2=[7,8,2,4,2]

plt.plot(x, y, color='orange', linestyle='dashed', marker='.')

plt.plot(x2,y2,color='r', linestyle='dashed', marker='D')

plt.xlabel('x-axis')

plt.ylabel('y-axis')

plt.title('Line Graph!')

plt.show()

#2
import pandas as pd

import numpy as np

a=pd.Series(data=[5000.0,8000.0,12000.0,18000.0,np.nan,np.nan,np.nan],\

index=['Q1','Q2','Q3','Q4','A','B','C'])
b=pd.Series(data=[np.nan,np.nan,np.nan,np.nan,13000.0,14000.0,12000.0],\

index=['Q1','Q2','Q3','Q4','A','B','C'])

c={1:a,2:b}

df=pd.DataFrame(c)

print(df)

print()

print(df.axes)

#3

1)
mysql> create table SPORT

-> (CID int(2),

-> COACHNAME varchar(10),

-> AGE int(3),

-> SPORTS varchar(15),

-> DATEOFAPP date,

-> PAY int(9),

-> SEX varchar(3),

-> NO_PLAYERS int(3));

2)
mysql> insert into SPORT

-> values(1,'ADITI',35,'KARATE','2012-03-25',20000,'F',20),

-> (2,'MOHAN',34,'BASKETBALL','2011-01-20',15000,'M',15),

-> (3,'SEEMA',34,'SQUASH','2010-03-11',20000,'F',2),

-> (4,'RAGHAVA',33,'KARATE','2008-03-25',15500,'M',16);
3)
mysql> select*from SPORT

-> where SPORTS='KARATE';

4)
mysql> select*from sport

-> where coachname like '%A';

5)
mysql> Select coachname,pay,age

-> from sport;

6)
mysql> select*from sport

-> where pay=20000 and sex='F';

7)
mysql> select distinct(sports) from sport

-> where sports like '___A%';

Set 3
#1
import matplotlib.pyplot as plt

left=['one','two','three','four','five']

height=[10,24,36,40,5]

plt.bar(left,height,color=['red','green'])

plt.xlabel('x-axis')

plt.ylabel('y_axis')
plt.title('Bar Chart')

plt.show()

#2
import pandas as pd

import numpy as np

a=np.arange(50,100,4)

s=pd.Series(a)

print(s)

print()

print(s[s>75])

#3

1)
create table STUDENT

-> (NO INT(2),

-> NAME VARCHAR(10),

-> AGE INT(4),

-> DEPARTMENT VARCHAR(10),

-> FEE INT(10),

-> SEX VARCHAR(2));

2)INSERT INTO STUDENT VALUES

-> (1,"PANKAJ",24,"COMPUTER",120,"M"),

-> (2,"SHALINI",21,"HISTORY",200,"F"),

-> (3,"SANJAY",22,"HINDI",300,"M"),

-> (4,"SUDHA",25,"HISTORY",400,"F");
3)SELECT NAME,AGE FROM STUDENT

-> WHERE SEX="M";

4)SELECT * FROM STUDENT

-> ORDER BY NAME DESC;

5)SELECT SUM(FEE) AS "TOTAL" FROM STUDENT;

6) SELECT * FROM STUDENT

-> WHERE DEPARTMENT="COMPUTER";

7)SELECT * FROM STUDENT

-> WHERE DEPARTMENT LIKE "H%";

Set 4
#1
import pandas as pd

import matplotlib.pyplot as plt

x=[20,55,90,10,100,20]

y=[10,100,10,80,80,10]

plt.plot(x,y,markersize=5,color='red')

plt.xlabel("x-axis")

plt.ylabel("y-axis")

plt.show()

#2
import pandas as pd

import matplotlib.pyplot as plt

a=[10,11,12,13,14,15]
b=['Aniket','Ram','Shyam','Bhim','Rim','Kim']

df=pd.DataFrame({'Rollno':a,'Name':b},index=[True,False,True,False,True,False])

print(df)

#3

1)
CREATE TABLE CLUB

(C_Id integer(5),

CoachName varchar(10),

Age int(5),Sports varchar(10),

DateofApp date,

Pay int(10),

Sex varchar(4),

No_Players int(10));

2)
insert into CLUB values

-> (1,"KUKREJA",35,"KARATE","2012-03-27",1000,"M",20),

-> (2,"RAVINA",34,"KARATE","2008-01-20",1200,"F",15),

-> (3,"KARAN",34,"SQUASH","2009-02-19",2000,"M",2),

-> (4,"TARUN",33,"BASKETBALL","2012-01-01",1500,"M",16));

3)SELECT MAX(AGE),MIN(PAY) FROM CLUB;

4) select distinct(Sports)
from CLUB;

5)select CoachName,DateofApp
from CLUB
 order by DateofApp desc;

6)select CoachName
from CLUB

where CoachName like "%A" and DateofApp>'2008-01-20';

7) select * from CLUB

Set 5
1)
import pandas as pd

import numpy as np

import matplotlib.pyplot as plt

girls=[10,20,20,40]

boys=[20,30,25,30]

x=np.arange(4)

plt.bar(x+0.25,girls,color="blue",label="girls",width=0.25)

plt.bar(x+0.5,boys,color="red",label="boys",width=0.25)

plt.xlabel("x-axis")

plt.ylabel("y-axis")

plt.legend()

plt.show()

2)
import pandas as pd

import numpy as np
a=["Nancy Drew","Hardy Boys","Diary of a wimpy kid","Harry Potter"]

b=[150,180,225,500]

df=pd.DataFrame({"Name":a,"Price":b})

print(df)

1)

df.at[:,"Special_Price"]=[135,150,200,440]

print(df)

2)

df.loc["4"]=["The Secret",800,0]

print(df)

3)

df=df.drop(["Special_Price"],axis=1)

print(df)

#3

1)
CREATE TABLE SchoolBus

(Rtno int(10),

Area_covered varchar(10),

Capacity int(10),

Noofstudents int(10),

Distance int(10),

Transporter varchar(10),

Charges int(10));

2)
insert into SchoolBus values

(1,"Vasant kunj",100,120,10,"Shivam travels",100000),

(2,"Hauz Khas",80,80,10,"Anand travels",85000),

(3,"Pitampura",60,55,30,"Anand travels",60000),

(4,"Rohini",100,90,35,"Anand travels",100000);

3)
select Rtno from SchoolBus where Distance<30;

4)
select Transporter,max(Capacity) from SchoolBus

group by Transporter;

5)
select Area_covered from SchoolBus

where Area_covered like "%j"

order by Area_covered desc;

6)
select Transporter,sum(Charges) from SchoolBus

where Transporter="Anand travels"

group by Transporter;

7)
select Transporter,max(Charges) from SchoolBus

where Transporter="Anand travels"

group by Transporter;