SET 1

1. CREATE the following DataFrame RESULT.

HalfYearly UT1 UT2 Final

Sharad 66 63 49 89
Mansi 86 67 97 90
Kanika 92 78 45 66
Ramesh 58 64 55 78
Ankita 53 75 88 69
Pranay 98 79 88 96

The Names of the students are row labels and the names (UT, HalfYearly, UT2 and Final are column
lables. Answer the following questions based on the above DataFrame.
1. Add a new row for the student with name Aastha and marks equal to 49, 56, 71,58.
2. Add a new column Internal Assessment with values ['A’, ‘A,’’B’,’A’,’C’,’B’,’C’].
3. Rename Pranay to Puneet.
4. Display marks in Half Yearly and Final of all students.
5 Display marks of Mansi to Ankita in UT1 and UT2.
6. Display first 3 records and last three records.
7. Delete the record of Ankita.
8. Write code in python so draw a bar graph between student names and Final mark columns of
the above DataFrame Set the xlabel "Name" and ylabel as "Final Marks" and title as 'Result’.

Q2) Write queries given below: (7) Table: Stock

Item no Item Dcode Qty UnitPrice StockDate
5005 Ball Pen 0.5 102 100 16 31-Mar-10
5003 Ball Pen 0.25 102 150 20 01-Jan-10
5002 Gel Pen Premium 101 125 14 14-Feb-10
5001 Eraser small 102 210 5 01-Jan-09
5004 Eraser big 102 60 10 12-Dec-09
5009 Sharpener classic 103 160 8 23-Mar-09
(i) To create the table Stock as per giver specification.

(ii) To display details of all items in the stock table in ascending order of StockDate. 1
(iii) To display ItemNo and Item name of those items from Stock table whose unit price is more than 10
rupees.
(iv) To display the details of those items whose dealer code(DCode ) is 102 or quantity in stock(Qty) is
more than 100 from the stock table.
(v) To display the maximum UnitPrice of items for each dealer individually as per Dcode from the table
stock.

(vi) To add a primary key constraint in item number field.

Answer
Q1) import pandas as pd

import matplotlib.pyplot as plt

Create the initial DataFrame

data = {

'UT1': [66, 86, 92, 58, 53, 98],

'HalfYearly': [63, 67, 78, 64, 75, 79],

'UT2': [49, 97, 45, 55, 88, 88],

'Final': [89, 90, 66, 78, 69, 96] }

students = ['Sharad', 'Mansi', 'Kanika', 'Ramesh', 'Ankita', 'Pranay']

df = pd.DataFrame(data, index=students)

1. Add a new row for the student Aastha

df.loc['Aastha'] = [49, 56, 71, 58]

2. Add a new column Internal Assessment

df['Internal Assessment'] = ['A', 'A', 'B', 'A', 'C', 'B', 'C']

3. Rename Pranay to Puneet

df.rename(index={'Pranay': 'Puneet'}, inplace=True)

4. Display marks in Half Yearly and Final of all students

half_final_marks = df[['HalfYearly', 'Final']]

print("Half Yearly and Final Marks:\n", half_final_marks)

5. Display marks of Mansi to Ankita in UT1 and UT2

mansi_to_ankita = df.loc['Mansi':'Ankita', ['UT1', 'UT2']]

print("\nMarks of Mansi to Ankita in UT1 and UT2:\n", mansi_to_ankita)

6. Display first 3 records and last three records

first_three = df.head(3)

last_three = df.tail(3)

print("\nFirst 3 records:\n", first_three)

print("\nLast 3 records:\n", last_three)

7. Delete the record of Ankita

df.drop('Ankita', inplace=True)

8. Draw a bar graph between student names and Final mark columns

plt.figure(figsize=(10, 6))

plt.bar(df.index, df['Final'], color='skyblue')

plt.xlabel("Name")

plt.ylabel("Final Marks")

plt.title("Result")

plt.xticks(rotation=45)

plt.tight_layout()

plt.show()

2)

1) CREATE TABLE Stock (

 ItemNo INT,

Item VARCHAR(50),

Dcode INT,

Qty INT,

UnitPrice DECIMAL(10, 2),

StockDate DATE

);

2) SELECT * FROM Stock

ORDER BY StockDate ASC;

3) SELECT ItemNo, Item FROM Stock

WHERE UnitPrice > 10;

4) SELECT * FROM Stock

WHERE Dcode = 102 OR Qty > 100;

5) SELECT Dcode, MAX(UnitPrice) AS MaxUnitPrice FROM Stock

GROUP BY Dcode;

6) ALTER TABLE Stock

ADD CONSTRAINT PK_ItemNo PRIMARY KEY (ItemNo);

Set 2
CREATE the following DataFrame emp
Fname Lname Salary Designation
100 Ravi Kumar 75000 Manager
103 Harry Waltur 65000 Manager
215 Sarah Ackerman 75000 Manager
244 Manila Sengupta 50000 Clerk
 300 Robert Samuel 45000 Clerk
Answer the following questions based on the shove DataFrame
1. Add a new row for the employee with index as 167 and values (Arpita. Sharma, 50000, Manager).
2. Change the row label 244 to 567.
3. Display designation of employees with index from 152 to 300.
4. Display designation of Robert.
5. Display last 3 records
6. Add a new column 'column' with values [400, 700, 500, 300, 200, 400, 600,800].
7. Delete the record of Sarah.
8 Write python code to create a Line Graph using Fname and salary columns. Set xlabel as "Name" and
ylabel as "Salary". Title of graph is "Analysis of Salary".

Q2) Write queries given below: (7) Table: DRESS

DCODE DESCRIPTION PRICE MCODE LAUNCHDATE

10001 FORMAL SHIRT 1250 M001 12-JAN-08
10020 FROCK 750 M004 09-SEP-07
10012 INFORMAL SHIRT 1450 M002 06-JUN-08
10019 EVENING GOWN 850 M003 06-JUN-09
10090 TULIP SKIRT 1250 M002 31-MAR-07
(I) To create the Table DRESS as per given specification.
(II) To display DCODE and DESCRIPTION of each dress in ascending order of DCODE.
(III) To display the details of all dresses which have launch date in between 05-dec-07 and 20-jun-
08(inclusive of both dates).

(IV) To display the average price of all the dresses which are made up of material with MCODE as M003.
(V) To display material wise highest and lowest price of dresses from DRESS table.(Display MCODE of
each dress along with highest and lowest price)
(VI) To add a primary key constraint in dcode field,

Q1) import pandas as pd

import matplotlib.pyplot as plt

Create the initial DataFrame

data = {

'Fname': ['Ravi', 'Harry', 'Sarah', 'Manila', 'Robert'],

'Lname': ['Kumar', 'Waltur', 'Ackerman',

'Sengupta', 'Samuel'],

'Salary': [75000, 65000, 75000, 50000, 45000],

'Designation': ['Manager', 'Manager', 'Manager',

'Clerk', 'Clerk'] }

index = [100, 103, 215, 244, 300]

emp = pd.DataFrame(data, index=index)

1. Add a new row for the employee with

index as 167

emp.loc[167] = ['Arpita', 'Sharma', 50000,

'Manager']

2. Change the row label 244 to 567

emp.rename(index={244: 567}, inplace=True)

3. Display designation of employees with
index from 152 to 300

Since there is no index 152, it will return an

empty DataFrame

designation_152_to_300 = emp.loc[152:300,
'Designation'] print("Designation of employees with
index from 152 to 300:\n",
designation_152_to_300)

4. Display designation of Robert

robert_designation = emp.loc[300, 'Designation']

print("\nDesignation of Robert:\n",
robert_designation)

5. Display last 3 records

last_three_records = emp.tail(3) print("\nLast 3

records:\n", last_three_records)

6. Add a new column 'column' with specified

values

emp['Column'] = [400, 700, 500, 300, 200]

# Adjusting to match the number of rows

7. Delete the record of Sarah

emp.drop(emp[emp['Fname'] == 'Sarah'].index,
inplace=True)

8. Create a Line Graph using Fname and

Salary columns

plt.figure(figsize=(10, 6))

plt.plot(emp['Fname'], emp['Salary'], marker='o')

plt.xlabel("Name")

plt.ylabel("Salary")

plt.title("Analysis of Salary")

plt.xticks(rotation=45)

plt.tight_layout()

plt.show()

Q2)

1) CREATE TABLE DRESS (

DCODE INT,

DESCRIPTION VARCHAR(50),

PRICE DECIMAL(10, 2),

 MCODE VARCHAR(10),

LAUNCHDATE DATE

);

2) SELECT DCODE, DESCRIPTION FROM DRESS

ORDER BY DCODE ASC;

3) SELECT * FROM DRESS WHERE LAUNCHDATE

BETWEEN TO_DATE('05-DEC-07', 'DD-MON-YY')
AND TO_DATE('20-JUN-08', 'DD-MON-YY');

4) SELECT AVG(PRICE) AS AveragePrice FROM

DRESS

WHERE MCODE = 'M003';

5) SELECT MCODE, MAX(PRICE) AS HighestPrice,

MIN(PRICE) AS LowestPrice FROM DRESS

GROUP BY MCODE;

6) ALTER TABLE DRESS

ADD CONSTRAINT PK_DCODE PRIMARY KEY

(DCODE);
 Set 3
Q1 Create the DataFrame SHOP given below.
Answer the following questions based on the above dataframe:
items qty city Price
101 Biscuit 100 Delhi 10
102 Jam 110 Kolkata 25
103 Coffee 200 Mumbai 55
104 Maggie 150 Shimla 40
105 Tea 270 Bihar 150
1.Display the record of item - Coffee.
2. Display the columns Item and Price for all items.
3. Display the Item and city for all items from 102 to 103.
4. Rename the column "Qty" to "Quantity".km1
5.Display first 3 and last 3 records.
6.Add a new column "Company" with values
['P&G', 'Kissan', 'Nescafe', 'H&L", "Tajmahal"]
it7. Delete the column City
8. Write python code to draw a simple bargraph using ITEM and QTY and set xlabel as "Items", ylabel as
"Quantity" and title as "Stock"

Q2. Write queries given below. Table: Employee

ecode ename desig dept Salary doj

101 Rinkoo Sharma MNGR ACCTS 80000 2010-10-01
102 Shiv Bansal CLRK ADMIN 45000 2012-01-04
103 Raman Ahuja EXEC ADMIN 95000 2009-03-01
104 Aman Sharma SALESMAN SALES 55000 2009-01-01
105 Seema Singh MNGR SALES 85000 2008-02-01
106 Aman Singh SALESMAN SALES 45000 2006-12-12
107 Rajat Singh CLRK ACCTS 35000 2009-10-01
1. Create the table Employee with ecode as a primary key and insert the given rows in the table.
2. Display the details of all the employees in order of their department and name.
3. Display the Employee code, Employee name and salary of the all Managers and Executives
4. Increase the salary of all clerks by 10%.
5. Display Department wise maximum, minimum and average salary paid.
6. Display Number of Employees in each designation.
Q1) import pandas as pd import matplotlib.pyplot as plt

Create the initial DataFrame

data = {

'items': ['Biscuit', 'Jam', 'Coffee', 'Maggie', 'Tea']0,

'qty': [100, 110, 200, 150, 270], 'city':

['Delhi', 'Kolkata', 'Mumbai', 'Shimla', 'Bihar'],

'Price': [10, 25, 55, 40, 150] }

index = [101, 102, 103, 104, 105]

SHOP = pd.DataFrame(data, index=index)

1. Display the record of item - Coffee

coffee_record = SHOP.loc[103]

print("Record of item - Coffee:\n", coffee_record)

2. Display the columns Item and Price for all items

item_price = SHOP[['items', 'Price']]

print("\nItem and Price for all items:\n", item_price)

3. Display the Item and city for all items from 102 to 103

item_city_102_to_103 = SHOP.loc[102:103, ['items', 'city']]

print("\nItem and city for all items from 102 to 103:\n", item_city_102_to_103)

4. Rename the column "Qty" to "Quantity"

SHOP.rename(columns={'qty': 'Quantity'}, inplace=True)

5. Display first 3 and last 3 records

first_three = SHOP.head(3)
last_three = SHOP.tail(3)’

print("\nFirst 3 records:\n", first_three)

print("\nLast 3 records:\n", last_three)

6. Add a new column "Company" with specified values

SHOP['Company'] = ['P&G', 'Kissan', 'Nescafe', 'H&L', 'Tajmahal']

7. Delete the column City

SHOP.drop(columns=['city'], inplace=True)

8. Draw a simple bar graph using ITEM and QTY

plt.figure(figsize=(10, 6))

plt.bar(SHOP['items'],

SHOP['Quantity'], color='skyblue')

plt.xlabel("Items")

plt.ylabel("Quantity")

plt.title("Stock")

plt.xticks(rotation=45)

plt.tight_layout()

plt.show()

Q2)

CREATE TABLE Employee (

ecode INT PRIMARY KEY,
ename VARCHAR(50),
desig VARCHAR(20),
dept VARCHAR(20),
Salary DECIMAL(10, 2),
doj DATE
 );

INSERT INTO Employee (ecode, ename, desig, dept, Salary, doj)

VALUES
(101, 'Rinkoo Sharma', 'MNGR', 'ACCTS', 80000, '2010-10-01'),
(102, 'Shiv Bansal', 'CLRK', 'ADMIN', 45000, '2012-01-04'),
(103, 'Raman Ahuja', 'EXEC', 'ADMIN', 95000, '2009-03-01'),
(104, 'Aman Sharma', 'SALESMAN', 'SALES', 55000, '2009-01-01'),
(105, 'Seema Singh', 'MNGR', 'SALES', 85000, '2008-02-01'),
(106, 'Aman Singh', 'SALESMAN', 'SALES', 45000, '2006-12-12'),
(107, 'Rajat Singh', 'CLRK', 'ACCTS', 35000, '2009-10-01');

2) SELECT * FROM Employee

ORDER BY dept, ename;

3) SELECT ecode, ename, Salary FROM Employee

WHERE desig IN ('MNGR', 'EXEC');

4) UPDATE Employee

SET Salary = Salary * 1.10

WHERE desig = 'CLRK';

5) SELECT dept,

MAX(Salary) AS MaxSalary,

MIN(Salary) AS MinSalary,

AVG(Salary) AS AvgSalary

FROM Employee

GROUP BY dept;

6) SELECT desig, COUNT(*) AS NumberOfEmployees

FROM Employee

GROUP BY desig;
 Set 4

Q1 Create the DataFrame DF given below.

Country Expenditure Growth Rate Update Date
0 Brazil 785300000 15.00 2016-03
1 India 530780000 18.60 2016-05
2 United kingdom 694582009 13.79 2016-02
3 Nigeria 130581000 14.87 2016-01
4 China 407191800 20.86 2016-09
5 Pakistan 475400000 12.54 2016-05
Answer the following questions based on the above dataframe:
1. To Display the column Country.
2. To display the columns "Country", "Expenditure" and "UpdateDate"
3. To display the records of "India and "United Kingdom"
4. To display the records of those countries whose growth rate is greater than 18.
5. To change the rowindex 4 to 14.
6. To insert a new column "rank" with values(1,3,5,4,2,6)
7. To delete the record of Nigeria permanently.
8. Write python code to draw a simple horizontal bar graph using columns COUNTRY and EXPENDITURE
and set xlabel as "Countries", ylabel as "Expenditure" and title as "Expenditure
analysis of different countries"

Q2) Write queries given below: (7) Table: Product

1. Create the table Product with Pcode as a primary key and insert the given rows in the table. (2)
2. Display the details of the product produced by Apple having quantity more than 50. (1)
3. Display pname, qty, price and company for the products where price is between 30000 to 80000. (1)
 4. Display maximum price of products for each company. (1)
5. Display the average price of LED TV . (1)
6. Display the difference between maximum price and minimum price for each company (1)

Q1) import pandas as pd import matplotlib.pyplot as plt

Create the initial DataFrame

data = {

'Country': ['Brazil', 'India', 'United Kingdom', 'Nigeria', 'China', 'Pakistan'],

'Expenditure': [785300000, 530780000, 694582009, 130581000, 407191800, 475400000],

'Growth Rate': [15.00, 18.60, 13.79, 14.87, 20.86, 12.54],

'Update Date': ['2016-03', '2016-05', '2016-02', '2016-01', '2016-09', '2016-05'] }

DF = pd.DataFrame(data)

1. Display the column Country

print("Column Country:\n", DF['Country'])

2. Display the columns "Country", "Expenditure" and "Update Date"

print("\nColumns 'Country', 'Expenditure' and 'Update Date':\n",

DF[['Country', 'Expenditure', 'Update Date']])

3. Display the records of "India" and "United Kingdom"

india_uk_records = DF[DF['Country'].isin(['India', 'United Kingdom'])]

print("\nRecords of 'India' and 'United Kingdom':\n", india_uk_records)

4. Display the records of those countries whose growth rate is greater than 18

high_growth_rate = DF[DF['Growth Rate'] > 18]

print("\nCountries with Growth Rate greater than 18:\n", high_growth_rate)

5. Change the row index 4 to 14

DF.rename(index={4: 14}, inplace=True)

6. Insert a new column "rank" with specified values

DF['rank'] = [1, 3, 5, 4, 2, 6] # Adjusting to match the number of rows

7. Delete the record of Nigeria permanently

DF.drop(DF[DF['Country'] == 'Nigeria'].index, inplace=True)

8. Draw a simple horizontal bar graph using columns COUNTRY and EXPENDITURE

plt.figure(figsize=(10, 6))

plt.barh(DF['Country'], DF['Expenditure'], color='skyblue')

plt.xlabel("Expenditure")

plt.ylabel("Countries")

plt.title("Expenditure Analysis of Different Countries")

plt.tight_layout() plt.show()