Set A
Q1) data = {
'year': [2014, 2015, 2016, 2017, 2018, 2019],
'KV1': [90, 85, 80, 95, 88, 79],
'KV2': [100, 98, 86, 75, 80, 70]}
df = [Link](data)
print(df)
print([Link](3))
[Link][6] = [2020, 98, 96]
print(df)
df = [Link](columns={'KV1': 'SC1', 'KV2': 'SC2'},inplace=True)
print(df)
df = [Link](index={0: 'A', 1: 'B'},inplace=True)
print(df)
Q2) import [Link] as plt
subjects = ['IP', 'Physics', 'Chemistry', 'Maths', 'English']
percentage = [98, 95, 96, 99, 97]
[Link](subjects, percentage, color='green')
[Link]('Subjects')
[Link]('Percentage')
[Link]('Result Analysis')
[Link]()
Q3) CREATE TABLE STUDENT (
RollNo INT PRIMARY KEY,
Name VARCHAR(20),
Class VARCHAR(5),
DOB DATE,
Gender CHAR(1),
City VARCHAR(20),
Marks INT
);
INSERT INTO STUDENT VALUES (1, 'Naman', 'XII', '1995-05-09', 'M', 'Anand', 453);
INSERT INTO STUDENT VALUES (2, 'Nandini', 'X', '1997-04-08', 'F', 'Baroda', 551);
INSERT INTO STUDENT VALUES (3, 'Nakshatra', 'X', '1997-03-02', 'F', 'Baroda', 553);
INSERT INTO STUDENT VALUES (4, 'Shailesh', 'XI', '1995-04-07', 'M', 'Surat', 458);
INSERT INTO STUDENT VALUES (5, 'Trisha', 'XII', '1996-04-01', 'F', 'Anand', 430);
INSERT INTO STUDENT VALUES (6, 'Manisha', 'XII', '1995-02-05', 'F', 'Anand', 530);
INSERT INTO STUDENT VALUES (7, 'Hetvee', 'XII', '1995-08-17', 'F', 'Junagadh', 555);
INSERT INTO STUDENT VALUES (8, 'Neel', 'X', '1997-10-19', 'M', 'Godhara', 559);
SELECT * FROM STUDENT WHERE Class = 'XII' ORDER BY Marks DESC;
SELECT Name, Gender, DOB FROM STUDENT WHERE Name LIKE '%i%';
SELECT Class, MAX(Marks) FROM STUDENT GROUP BY Class;
SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class;
SELECT DISTINCT City FROM STUDENT;
Set B
Q1)
import pandas as pd
data = {
'Pcd': ['P01', 'P02', 'P03', 'P04'],
'title': ['Notebook', 'Pencilbox', 'WaterBottle', 'SchoolBag'],
'Price': [85, 76, 129, 730],
'qty': [500, 200, 50, 70]
df = [Link](data)
df['ACC_NO'] = [135, 153, 225, 442]
[Link][4] = ['P05', 'Eraser', 10, 100, 500]
df = [Link]('qty', axis=1,inplace=True)
df = [Link]([0, 2],inplace=True)
print(df)
Q2) import [Link] as plt
days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
tickets_sold = [2000, 2800, 3000, 2500, 2300, 2500, 1000]
[Link](days, tickets_sold, color='magenta')
[Link]('Days')
[Link]('Tickets Sold')
[Link]('Weekly Ticket Sales')
[Link]()
Q3) CREATE TABLE STOCK (
stockid INT PRIMARY KEY,
dopurchase DATE,
name VARCHAR(50),
make VARCHAR(50),
Price INT
);
INSERT INTO STOCK VALUES (101, '2020-07-06', 'CPU', 'ACER', 12000);
INSERT INTO STOCK VALUES (102, '2020-09-01', 'CPU', 'ACER', 12750);
INSERT INTO STOCK VALUES (103, '2020-09-01', 'MONITOR', 'ACER', 7500);
INSERT INTO STOCK VALUES (104, '2016-08-03', 'PROJECTOR', 'GLOBUS', 37250);
INSERT INTO STOCK VALUES (105, '2016-05-26', 'VISUALIZES', 'GLOBUS', 17500);
INSERT INTO STOCK VALUES (106, '2020-07-23', 'WIFI RECEIVER', 'ZEBION', 450);
INSERT INTO STOCK VALUES (107, '2015-02-18', 'PRINTER', 'LEXMARK', 38000);
INSERT INTO STOCK VALUES (108, '2020-07-23', 'HEADPHONE', 'BOAT', 750);
SELECT make, COUNT(*) FROM STOCK GROUP BY make;
UPDATE STOCK SET Price = Price- Price * 0.05 WHERE YEAR(dopurchase) = 2020;
DELETE FROM STOCK WHERE YEAR(dopurchase) < 2015;
ALTER TABLE STOCK ADD STATUS CHAR(1);
SELECT make, SUM(Price) FROM STOCK WHERE Price > 1000 GROUP BY make;
Set C
Q1)
import pandas as pd
import numpy as np
names = ['Amit', 'Neha', 'Rahul', 'Priya', 'Karan', 'Sneha']
marks = [Link]([85.5, 92.0, 76.3, 88.9, 67.5, 95.2])
s = [Link](marks, index=names)
s['Rahul'] = 81.4
print(s[s > 85])
print(s[['Neha', 'Rahul', 'Priya', 'Karan']])
print([Link])
Q2) import [Link] as plt
english = [77, 66, 88, 99, 55, 44, 33, 79, 68, 83]
maths = [56, 89, 70, 50, 60, 65, 90, 80, 47, 82]
[Link]([english, maths], label=['English', 'Maths'])
[Link]('Subject Marks Distribution')
[Link]('Marks Range')
[Link]('Frequency')
[Link]()
[Link]('marks_histogram.png')
[Link]()
Q3) CREATE TABLE TRAVEL (
CNO INT PRIMARY KEY,
CNAME VARCHAR(50),
TRAVELDATE DATE,
KM INT,
VCODE VARCHAR(5),
NOP INT
);
INSERT INTO TRAVEL VALUES (101, 'K. Niwal', '2015-12-13', 200, 'V01', 32);
INSERT INTO TRAVEL VALUES (103, 'Fredrick Sym', '2016-03-21', 120, 'V03', 45);
INSERT INTO TRAVEL VALUES (105, 'Hitesh Jain', '2016-04-23', 450, 'V02', 42);
INSERT INTO TRAVEL VALUES (102, 'Ravi Anish', '2016-01-13', 80, 'V02', 40);
INSERT INTO TRAVEL VALUES (107, 'John Malina', '2015-02-10', 65, 'V04', 2);
INSERT INTO TRAVEL VALUES (104, 'Sahanubhuti', '2016-01-28', 90, 'V05', 4);
INSERT INTO TRAVEL VALUES (106, 'Ramesh Jaya', '2016-04-06', 100, 'V01', 25);
SELECT CNAME FROM TRAVEL WHERE VCODE IN ('V01', 'V02');
SELECT CNO, CNAME FROM TRAVEL WHERE TRAVELDATE BETWEEN '2016-01-01' AND '2016-06-30';
SELECT * FROM TRAVEL WHERE KM > 120 ORDER BY NOP ASC;
SELECT VCODE, COUNT(*) FROM TRAVEL GROUP BY VCODE;
UPDATE TRAVEL SET KM = 100 WHERE CNAME = 'Ravi Anish';