1. a) Consider the Dataframe which is given below.
Sno Sname Grade
Stu1 10 Harshith A
Stu2 20 Koti C
Stu3 30 John B
Stu4 40 Charles C
(i) Create a dataframe as shown above.
(ii) Display first 3 records.
(iii) Display the student records whose grade is ‘C’.
(iv) Display the record of ‘Koti’.
(v) Create a line chart.
b) Write a program to transfer the following data from CSV to Data Frame:
Organization CEO Established
0 Alphabet Sundar Pichai 02-Oct-15
2 Microsoft Satya Nadella 04-Apr-75
2 Amazon Jeff Bezos 05-Jul-94
2. Write MySQL queries for the given questions based on the following table.
Table Name: Employee
Empno Ename Job DOJ Salary
111 John Manager 2010-10-01 20000
112 Charles Clerk 2010-01-04 15000
113 Swarna Manager 2009-03-01 30000
114 Suma Supervisor 2009-04-01 25000
115 Supreet Clerk 2008-08-05 18000
Prerequisite:
Create the database with your name.
Create the above table with suitable datatype and insert the records. Choose the
suitable primary key constraint for the table.
Answer the following questions:
1. Display the Employee name, Job , Salary in the order of salary highest to lowest.
2. Display the recent joined employee from the table.
3. Display the number of employees in each Job.
4. Display the minimum salary of employee of each job for Clerk and Manager only.
5. Increment the Salary of ‘Supreet’ by 5%
6. Display the names of employees and their corresponding day name of DOJ.
7. Display from 3rd character to 6th character from the ‘Job’ column.
3 a) Create the Dataframe TCH which is given below.
Tname salary department
1001 Scott 90000 Math
1002 Tom 34000 Computers
1003 Joy 32000 Computers
1004 Sam 40000 Math
1005 Martin 85000 Science
(i) Display columns Tname and department only.
(ii) Change the salary of Joy to 35000.
(iii) Retrieve the bottom three records from the Dataframe.
(iv) Delete a record in which Tname is ‘Tom’ from the DataFrame.
(v) Create a bar chart.
b) To Store the data into a CSV file with seperator as ‘$’
4. Write MySQL queries for the given questions based on the following table.
Table Name: Student
Roll_no Name Specialization Marks
11 Subhransu Patra Computers 90
12 Sudhansu Patra English 69
13 Jonny English 99
14 Missy Computers 78
15 Jenny Mathematics 96
16 Kyle Computers 97
17 Nathan English 76
18 Abby Computers 85
19 John Mathematics 67
20 Steve Sociology 83
Prerequisite:
Create the database with your name.
Create the above table with suitable datatype and insert the records. Choose the
suitable primary key constraint for the table.
Answer the following questions:
1. Display the student records in descending order based on marks.
2. Display the total of all students marks from the above table.
3. Display the number of students in each specialization.
4. Display last 4 characters of the ‘Specialization’ column.
5. Display the student details who scored maximum marks.
6. Display the names of students whose name contains letter ‘a’.
7. Display from 3rd character to 6th character from the ‘Specialization’ column.
