IP PRACTICAL FILE
Topic: SQL Commands and Python
Name: K R I T A R T H J A I N
Class and Section: X I - S C I - B
Roll No.40
Subject teacher. Mr. Akash
Subject code. 065
SQL COMMANDS
Q1.
1. Display the details of students who scored more than 80 marks.
2. Add a new column 'Email' to the table.
3. Change the datatype of Marks to FLOAT.
4. Delete the record of the student whose AdmNo = 105.
5. Display the names of students whose name starts with 'S'.
6. Insert a new student record: (106, 'Kabir', 11, 90)
Solution:
SELECT * FROM student WHERE marks > 80; ALTER TABLE student ADD COLUMN Email
VARCHAR(50); ALTER TABLE student MODIFY marks FLOAT; DELETE FROM student
WHERE AdmNo = 105; SELECT name FROM student WHERE name LIKE 'S%'; INSERT
INTO student VALUES(106, 'Kabir', 11, 90);
Output:
1, 2, 3, 4, 5, 6 (Commands executed successfully)
Q2.
1. Display the names of employees who work in the HR department.
2. Display all details of employees whose Salary is greater than 50,000.
3. Display all the departments in the given table.
4. Display employee names in descending order of Salary.
Solution:
SELECT name FROM employee WHERE dept = 'HR'; SELECT * FROM employee WHERE
salary > 50000; SELECT DISTINCT dept FROM employee; SELECT name FROM
employee ORDER BY salary DESC;
Output:
(Query results displayed)
Q3.
1. Display the Product Name and Price of all products whose price is less than
100.
2. Display all details from table where ProdName like '_a*'.
3. Update the price of Bag to 850.
4. Set primary key on ProdID.
Solution:
SELECT ProdName, Price FROM product WHERE Price < 100; SELECT * FROM product
WHERE ProdName LIKE '_a%'; UPDATE product SET Price = 850 WHERE ProdName =
'Bag'; ALTER TABLE product ADD PRIMARY KEY (ProdID);
Output:
(Query results displayed)
Q4.
1. Write a SQL command to create the following table.
2. Display the Title and Author of all books priced between 100 - 300.
3. Increase the price of the book titled "Gitanjali" by 100.
4. Show the structure of the table.
Solution:
CREATE TABLE books(BookID INT PRIMARY KEY, Title VARCHAR(100), Author
VARCHAR(50), Price FLOAT); SELECT Title, Author FROM books WHERE Price
BETWEEN 100 AND 300; UPDATE books SET Price = Price + 100 WHERE Title =
'Gitanjali'; DESC books;
Output:
(Query results displayed)
Q5.
Consider a table STUDENT with the following columns:
RollNo
Name
Class
Marks
City
Write SQL commands for the following:
1. Create the table STUDENT with the columns mentioned above.
2. Display the Name and City of students who live in Delhi.
3. Display details of students who scored more than 80 marks.
4. Display the average marks of all students.
5. Display student details in ascending order of Marks.
6. Add a new column Email of datatype VARCHAR(30).
7. Update the Marks of the student with AdmNo = 102 to 95.
8. Delete the record of the student whose AdmNo = 105.
Solution:
CREATE TABLE STUDENT(RollNo INT PRIMARY KEY, Name VARCHAR(50), Class INT,
Marks FLOAT, City VARCHAR(30)); SELECT Name, City FROM STUDENT WHERE City =
'Delhi'; SELECT * FROM STUDENT WHERE Marks > 80; SELECT AVG(Marks) FROM
STUDENT; SELECT * FROM STUDENT ORDER BY Marks ASC; ALTER TABLE STUDENT ADD
COLUMN Email VARCHAR(30); UPDATE STUDENT SET Marks = 95 WHERE RollNo = 102;
DELETE FROM STUDENT WHERE RollNo = 105;
Output:
Q2: (Results) Q3: (Results) Q4: Average: (calculated value) Q5: (Results) Q6:
(Email added) Q7: (Marks updated) Q8: (Record deleted)
PYTHON
Q1. Find volume of a cuboid whose dimensions are given as 15 units, 20 units
and 30 units.
Answer:
length = 15 width = 20 height = 30 volume = length * width * height
print(f"Volume of cuboid: {volume} cubic units")
Output:
Volume of cuboid: 9000 cubic units
Q2. The radius of a sphere is 7.5 m. Write a python script to calculate its area
and volume.
Answer:
import math radius = 7.5 surface_area = 4 * [Link] * radius ** 2 volume =
(4/3) * [Link] * radius ** 3 print(f"Surface Area: {surface_area:.2f} m²")
print(f"Volume: {volume:.2f} m³")
Output:
Surface Area: 706.86 m² Volume: 1767.15 m³
Q3. Write a python program to print the squares of numbers 1 to 20.
Answer:
for i in range(1, 21): print(f"{i}² = {i**2}") # Or in one line: print([i**2
for i in range(1, 21)])
Output:
1² = 1 2² = 4 3² = 9 ... (continues up to) 20² = 400
Q4. Marks of five students out of 100 are given as 81, 32, 45, 91, 75. According
to the criteria if a child scores more than 33 then he is pass, if more than 50,
he is average, if more than 80, he is good, if more than 90, then he is brilliant.
1. Print the result of every student.
2. Also calculate the average marks.
Answer:
marks = [81, 32, 45, 91, 75] # a. Print result of every student for i, mark
in enumerate(marks, 1): if mark > 90: result = "Brilliant" elif mark > 80:
result = "Good" elif mark > 50: result = "Average" elif mark > 33: result =
"Pass" else: result = "Fail" print(f"Student {i}: {result}") # b. Calculate
average marks average = sum(marks) / len(marks) print(f"\nAverage marks:
{average}")
Output:
Student 1: Good Student 2: Fail Student 3: Pass Student 4: Brilliant Student
5: Average Average marks: 64.8
Q5. Create a dictionary with the following details:
AdmNo: 101
Name: "Arjun"
Class: 11
Marks: 85
City: "Delhi"
A. Write a Python statement to display the Name and Marks of the student.
B. Increase the student's Marks by 5 and display the updated dictionary.
C. Add a new key "Email" to the student dictionary with value "arjun@[Link]"
and display the dictionary.
D. Delete the key City from the dictionary and display the updated dictionary.
Answer:
student = { 'AdmNo': 101, 'Name': 'Arjun', 'Class': 11, 'Marks': 85, 'City':
'Delhi' } # A. Display Name and Marks print(f"Name: {student['Name']}, Marks:
{student['Marks']}") # B. Increase marks by 5 student['Marks'] += 5
print(f"Updated Dictionary (A): {student}") # C. Add Email student['Email']
= 'arjun@[Link]' print(f"Updated Dictionary (B): {student}") # D.
Delete City del student['City'] print(f"Updated Dictionary (C): {student}")
Output:
A. Name: Arjun, Marks: 85 B. Updated Dictionary: {'AdmNo': 101, 'Name':
'Arjun', 'Class': 11, 'Marks': 90, 'City': 'Delhi'} C. Updated Dictionary:
{'AdmNo': 101, 'Name': 'Arjun', 'Class': 11, 'Marks': 90, 'City': 'Delhi',
'Email': 'arjun@[Link]'} D. Updated Dictionary: {'AdmNo': 101, 'Name':
'Arjun', 'Class': 11, 'Marks': 90, 'Email': 'arjun@[Link]'}
Q6. Write a python program to create a list of every integer between 0 and
100 inclusive named L1 using python sorted in increasing order.
Answer:
L1 = list(range(0, 101)) print(L1)
Output:
[0, 1, 2, 3, ..., 98, 99, 100]
Q7. Write a program to enter names of employees and their salaries as input
and show them in a dictionary.
Answer:
n = int(input("Enter number of employees: ")) employees = {} for i in
range(n): name = input(f"Enter employee {i+1} name: ") salary =
float(input(f"Enter {name}'s salary: ")) employees[name] = salary
print("\nEmployee Dictionary:") print(employees)
Output:
Enter number of employees: 3 Enter employee 1 name: Raj Enter Raj's salary:
50000 ... Employee Dictionary: {'Raj': 50000, 'Priya': 60000, 'Vikram':
55000}
Q8. Write a program to count the number of times a character appears in a
given string.
Answer:
string = input("Enter a string: ") character = input("Enter a character to
count: ") count = [Link](character) print(f"The character
'{character}' appears {count} times in '{string}'")
Output:
Enter a string: hello world Enter a character to count: l The character 'l'
appears 3 times in 'hello world'
Q9. Create a dictionary whose keys are month names and whose values are
the number of days in the corresponding months.
1. Ask the user to enter a month name and use the dictionary to tell how many
days are in the month.
2. Print out all of the months within 31 days.
Answer:
months = { 'January': 31, 'February': 28, 'March': 31, 'April': 30, 'May':
31, 'June': 30, 'July': 31, 'August': 31, 'September': 30, 'October': 31,
'November': 30, 'December': 31 } # a. Ask user for month month =
input("Enter a month name: ").capitalize() if month in months:
print(f"{month} has {months[month]} days") else: print("Invalid month name")
# b. Print months with 31 days print("\nMonths with 31 days:") for month,
days in [Link](): if days == 31: print(month)
Output:
Enter a month name: May May has 31 days Months with 31 days: January March
May July August October December