Scribd
Upload
7 views5 pages

ANS KEY SET 2

The document contains Python code for creating a DataFrame with movie data, displaying various outputs, and manipulating the DataFrame by dropping a column and renaming another. Additionally, it includes SQL commands for creating and populating 'Employees' and 'Departments' tables, along with several queries to fetch and update data. The outputs of the DataFrame and SQL queries are also provided.

Uploaded by

banerjeesusmita490
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views5 pages

ANS KEY SET 2

The document contains Python code for creating a DataFrame with movie data, displaying various outputs, and manipulating the DataFrame by dropping a column and renaming another. Additionally, it includes SQL commands for creating and populating 'Employees' and 'Departments' tables, along with several queries to fetch and update data. The outputs of the DataFrame and SQL queries are also provided.

Uploaded by

banerjeesusmita490
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

SET B

1.a.

b.

import pandas as pd

data = {
"MovieID": [1,2,3,4,5],
"Title": ["LAGAAN","TAARE ZAMEEN PAR","3 IDIOTS","DANGAL","ANDHADHUN"],
"Year": [2001 ,2007 , 2009,2016,2018],
"Rating": [8.4,8.5,8.4,8.4,8.3],

#load data into a DataFrame object:


df = pd.DataFrame(data)

print(df)

print(df.head(2)) #display first 2 rows

print(df['Title']) #Title of all Movies

df = df.drop('Rating', axis=1) # Delete the column Rating


print(df)

print(df.loc[2:4,'Title']) #Display the data of the 'Title' column from indexes 2 to 4

df.rename(columns={'Title':'Name'}, inplace=True) #Rename the column name 'Title' to 'Name'.


print(df)

Output :-

Output:

MovieID Title Year Rating


0 1 LAGAAN 2001 8.4
1 2 TAARE ZAMEEN PAR 2007 8.5
2 3 3 IDIOTS 2009 8.4
3 4 DANGAL 2016 8.4
4 5 ANDHADHUN 2018 8.3

MovieID Title Year Rating


0 1 LAGAAN 2001 8.4
1 2 TAARE ZAMEEN PAR 2007 8.5
0 LAGAAN
1 TAARE ZAMEEN PAR
2 3 IDIOTS
3 DANGAL
4 ANDHADHUN
Name: Title, dtype: object

MovieID Title Year


0 1 LAGAAN 2001
1 2 TAARE ZAMEEN PAR 2007
2 3 3 IDIOTS 2009
3 4 DANGAL 2016
4 5 ANDHADHUN 2018

2 3 IDIOTS
3 DANGAL
4 ANDHADHUN
Name: Title, dtype: object

MovieID Name Year


0 1 LAGAAN 2001
1 2 TAARE ZAMEEN PAR 2007
2 3 3 IDIOTS 2009
3 4 DANGAL 2016
4 5 ANDHADHUN 2018

2.
-- create
CREATE TABLE Employees ( EmpID INT, Name VARCHAR(30) , DeptID INT, Salary INT);

-- insert
INSERT INTO Employees VALUES (101 ,"Alice" ,1 ,50000);
INSERT INTO Employees VALUES (102 ,"Bob" ,2 ,60000);
INSERT INTO Employees VALUES (103 ,"Charlie" ,1 ,55000);
INSERT INTO Employees VALUES (104 ,"David" ,3 ,62000);
INSERT INTO Employees VALUES (105 ,"Eva" ,2 ,52000);

-- fetch
SELECT * FROM Employees;

CREATE TABLE Departments (DeptID INT, DeptName VARCHAR(30), Location VARCHAR(30));

INSERT INTO Departments VALUES (1, "HR" , "New York");


INSERT INTO Departments VALUES (2 ,"IT" ,"San Francisco");
INSERT INTO Departments VALUES (3 ,"Finance", "Chicago");

SELECT * FROM Departments;

-- QUERY 1
SELECT A.Name ,B.DeptName FROM Employees A ,Departments B WHERE A.DeptID=B.DeptID;
-- QUERY 2
SELECT AVG(Salary) FROM Employees GROUP BY DeptID;
-- QUERY 3
SELECT A.Name FROM Employees A ,Departments B WHERE A.DeptID=B.DeptID AND DeptName="IT";
-- QUERY 4
UPDATE Employees SET Salary=Salary+(Salary*10)/100 WHERE DeptID = 1;
SELECT * FROM Employees;

Output:

+-------+---------+--------+--------+
| EmpID | Name | DeptID | Salary |
+-------+---------+--------+--------+
| 101 | Alice | 1 | 50000 |
| 102 | Bob | 2 | 60000 |
| 103 | Charlie | 1 | 55000 |
| 104 | David | 3 | 62000 |
| 105 | Eva | 2 | 52000 |
+-------+---------+--------+--------+
+--------+----------+---------------+
| DeptID | DeptName | Location |
+--------+----------+---------------+
| 1 | HR | New York |
| 2 | IT | San Francisco |
| 3 | Finance | Chicago |
+--------+----------+---------------+
+---------+----------+
| Name | DeptName |
+---------+----------+
| Alice | HR |
| Bob | IT |
| Charlie | HR |
| David | Finance |
| Eva | IT |
+---------+----------+
+-------------+
| AVG(Salary) |
+-------------+
| 52500.0000 |
| 56000.0000 |
| 62000.0000 |
+-------------+
+------+
| Name |
+------+
| Bob |
| Eva |
+------+
+-------+---------+--------+--------+
| EmpID | Name | DeptID | Salary |
+-------+---------+--------+--------+
| 101 | Alice | 1 | 55000 |
| 102 | Bob | 2 | 60000 |
| 103 | Charlie | 1 | 60500 |
| 104 | David | 3 | 62000 |
| 105 | Eva | 2 | 52000 |
+-------+---------+--------+--------+

You might also like