Q1.
Write a SQL command to create a table Students with
fields: Roll (INT), Name (VARCHAR(30)), Marks (INT),
Grade (CHAR(1)).
Answer:
CREATE TABLE Students (
Roll INT PRIMARY KEY,
Name VARCHAR(30),
Marks INT,
Grade CHAR(1));
Q2. Insert 3 records into the Students table.
Answer:
INSERT INTO Students VALUES (1, 'Amit', 85, 'A');
INSERT INTO Students VALUES (2, 'Riya', 72, 'B');
INSERT INTO Students VALUES (3, 'Sahil', 56, 'C');
Q3. Display all records from the Students table.
Answer:
SELECT * FROM Students;
Q4. Display Name and Marks of students who scored
more than 70.
Answer:
SELECT Name, Marks FROM Students WHERE Marks > 70;
Q5. Increase marks of Riya by 5.
Answer:
UPDATE Students SET Marks = Marks + 5 WHERE Name='Riya';
Q6. Delete the record of Sahil from the table.
Answer:
DELETE FROM Students WHERE Name='Sahil';
Q7. Count the number of students having Grade ‘A’.
Answer:
SELECT COUNT(*) FROM Students WHERE Grade='A';
Q8. Display all students in descending order of Marks.
Answer:
SELECT * FROM Students ORDER BY Marks DESC;
Q9. Find the maximum marks scored by any student.
Answer:
SELECT MAX(Marks) FROM Students;
Q10. Create a table Employee with fields: EID INT
PRIMARY KEY, Name VARCHAR(30) NOT NULL,
Salary INT DEFAULT 30000.
Answer:
CREATE TABLE Employee (
EID INT PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Salary INT DEFAULT 30000
);
Q11. Display all employees whose Salary is between 20000
and 50000.
Answer:
SELECT * FROM Employee
WHERE Salary BETWEEN 20000 AND 50000;
Q12. Create a table Orders having fields: OrderID INT
PRIMARY KEY, EID INT (Foreign Key referencing
Employee), Amount INT.
Answer:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EID INT,
Amount INT,
FOREIGN KEY (EID) REFERENCES Employee(EID)
);
Q13. Display the total salary paid to all employees.
Answer:
SELECT SUM(Salary) FROM Employee;
Q14. Display the names of employees whose name starts
with ‘A’.
Answer:
SELECT * FROM Employee WHERE Name LIKE 'A%';
Q15. Display the number of employees grouped into two
categories: Salary > 40000 as 'High Salary' and Salary ≤
40000 as 'Low Salary'.
Answer:
SELECT
CASE
WHEN Salary > 40000 THEN 'High Salary'
ELSE 'Low Salary'
END AS Category,
COUNT(*)
FROM Employee
GROUP BY Category;
Q16. Write a Python program to connect to a MySQL
database named ‘school’.
Answer:
import [Link]
con = [Link](
host="localhost",
user="root",
password="yourpassword",
database="school"
)
if con.is_connected():
print("Connection Successful")
Q17. Write a Python program to insert a new student
record into the Students table.
Answer:
import [Link]
con = [Link](host="localhost", user="root",
password="yourpassword", database="school")
cur = [Link]()
query = "INSERT INTO Students VALUES (%s, %s, %s, %s)"
data = (4, "Neha", 91, "A")
[Link](query, data)
[Link]()
print("Record Inserted")
Q18. Write a Python program to fetch and display all
rows from the Students table.
Answer:
import [Link]
con = [Link](host="localhost", user="root",
password="yourpassword", database="school")
cur = [Link]()
[Link]("SELECT * FROM Students")
rows = [Link]()
for r in rows:
print(r)
Q19. Write a Python program to update the marks of the
student with Roll number 1 by adding 10.
Answer:
import [Link]
con = [Link](host="localhost", user="root",
password="yourpassword", database="school")
cur = [Link]()
[Link]("UPDATE Students SET Marks = Marks + 10 WHERE Roll = 1")
[Link]()
print("Marks Updated")
Q20. Write a Python program to delete the record of the
student whose Roll number is 3.
Answer:
import [Link]
con = [Link](host="localhost", user="root",
password="yourpassword", database="school")
cur = [Link]()
[Link]("DELETE FROM Students WHERE Roll = 3")
[Link]()
print("Record Deleted")