SQL Practice

1. A table, named PRODUCTS, in the SHOPDB database, has the following

structure:

Write the following Python function to perform the specified operation:

AddProductAndList():
1. Input details of a product and store them in the table PRODUCTS.
2. Retrieve and display all records from the PRODUCTS table where the
price is greater than 1000.
Assume the following for Python-Database connectivity:
 Host: localhost
 User: root
 Password: ShopPass
Ans: import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password=" ShopPass ",
database=" SHOPDB "
)
cursor = conn.cursor()
def AddProductAndList():

# Input details for a new book

productID = int(input("Enter Book ID: "))
 productName = input("Enter Book Title: ")
category= input("Enter Author Name: ")
price = float(input("Enter Price of the Book: "))
# Insert the record into the BOOKS table
insert_query = "INSERT INTO BOOKS VALUES (%s, ‘%s’, ‘%s’, %s)
" %(productID, productName, category, price)
insert_query = "INSERT INTO BOOKS VALUES ({}, ‘{}’, ‘{}’, {})
".format(productID, productName, category, price)
cursor.execute(insert_query)
conn.commit()
print("Book details added successfully!")
# Retrieve and display records with price > 500
select_query = "SELECT * FROM BOOKS WHERE price > 500"
cursor.execute(select_query)
results = cursor.fetchall()
print("Books with Price greater than 500:")
for record in results:
print(record)
AddAndRetrieve()
1. Create & use the database Edbase
Ans: Create database Edbase;
Use Edbase;
2. Write a query to create the EMPLOYEE table with the structure given above.

Ans: CREATE TABLE EMPLOYEE ( EMPID INT PRIMARY KEY, NAME

 VARCHAR(50), DEPT VARCHAR(20), SALARY FLOAT, JOIN_DATE
DATE, COMMISSION FLOAT DEFAULT 10 );
3. Creating and Modifying the Table
a) Write a query to add a column AGE (INT) to the EMPLOYEE table.
b) Write a query to drop the COMMISSION column from the
EMPLOYEE table.
Ans: a) ALTER TABLE EMPLOYEE ADD AGE INT;
b) ALTER TABLE EMPLOYEE DROP COMMISSION;
4. Inserting Data
a) Insert a new employee with the following details into the EMPLOYEE
table:
EMPID: 101, NAME: 'John Doe', DEPT: 'HR', SALARY: 50000,
JOIN_DATE: '2023-01-15', COMMISSION: 2000.
b) Insert a new employee with the following details:
102, ‘SAM’, 60000
Ans: a) INSERT INTO EMPLOYEE VALUES (101, 'John Doe', 'HR',
50000, '2023-01-15', 2000);
b) INSERT INTO EMPLOYEE (EMPID, NAME, SALARY)
VALUES (102, ‘SAM’, 60000);
5. Retrieving Data
a) Display all details of employees who work in the HR department.
b) Display the names and salaries of employees who earn more than
40,000.
c) Retrieve the department-wise count of employees
d) Retrieve details of employees who joined after '2022-12-31'.
Ans: a) SELECT * FROM EMPLOYEE WHERE DEPT = 'HR';
b) SELECT NAME, SALARY FROM EMPLOYEE WHERE
SALARY > 40000;
c) SELECT DEPT, COUNT(*) AS Employee_Count FROM
EMPLOYEE GROUP BY DEPT;
d) SELECT * FROM EMPLOYEE WHERE JOIN_DATE > '2022-
 12-31';
6. Using Constraints
a) Write a query to add a NOT NULL constraint to the NAME column.
b) Write a query to add a CHECK constraint on the SALARY column,
ensuring it is greater than 10,000.
c) Write a query to add a default value ‘ADMIN’ for the DEPT column.
Ans: a) ALTER TABLE EMPLOYEE MODIFY NAME VARCHAR(50)
NOT NULL;
b) ALTER TABLE EMPLOYEE ADD CHECK (SALARY > 10000);
(OR)
ALTER TABLE EMPLOYEE MODIFY SALARY INT
CHECK(SALARY > 10000);
c) ALTER TABLE EMPLOYEE MODIFY DEPT VARCHAR(20)
DEFAULT ‘ADMIN’;
7. Updating Data
a) Update the SALARY of employees in the IT department by adding a
10% hike.
b) Set the COMMISSION to 0 for all employees who have not received
any commission.
Ans: a) UPDATE EMPLOYEE SET SALARY = SALARY * 1.10 WHERE
DEPT = 'IT';
(OR)
UPDATE EMPLOYEE SET SALARY = SALARY + (SALARY*
0.10) WHERE DEPT = 'IT';
b) UPDATE EMPLOYEE SET COMMISSION = 0 WHERE
COMMISSION IS NULL;
8. Using LIKE
a) Display the names of employees whose names start with the letter 'A'.
b) Retrieve employees whose department names end with 'R'.
Ans: a) SELECT * FROM EMPLOYEE WHERE NAME LIKE 'A%';
b) SELECT * FROM EMPLOYEE WHERE DEPT LIKE '%R';
9. Sorting
 a) Display all employees' details in ascending order of their SALARY.
b) Display all employees' names in descending order of their
JOIN_DATE.
Ans: a) SELECT * FROM EMPLOYEE ORDER BY SALARY ASC;
b) SELECT NAME FROM EMPLOYEE ORDER BY JOIN_DATE
DESC;
10. Aggregate Functions
a) Retrieve the average salary of employees in each department.
b) Find the maximum salary of employees in the Finance department.
Ans: a) SELECT DEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY
DEPT;
b) SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPT =
'Finance';
11. Deleting Data
a) Write a query to delete employees from the Sales department.
b) Write a query to delete all records from the EMPLOYEE table where
the SALARY is less than 20,000.
Ans: a) DELETE FROM EMPLOYEE WHERE DEPT = 'Sales';
b) DELETE FROM EMPLOYEE WHERE SALARY < 20000;
12. Grouping by Department (Group by)
a) Write an SQL query to display the total salary for each department
from the EMPLOYEE table.
b) Write an SQL query to calculate the average salary of employees for
each department.
c) Write an SQL query to count how many employees are there in each
department.
d) Write an SQL query to find the highest salary in each department.
e) Write an SQL query to find the lowest salary in each department.
Ans: a) SELECT DEPT, SUM(SALARY) AS Total_Salary FROM
EMPLOYEE GROUP BY DEPT;
b) SELECT DEPT, AVG(SALARY) AS Avg_Salary FROM
EMPLOYEE GROUP BY DEPT;
 c) SELECT DEPT, COUNT(*) AS Employee_Count FROM
EMPLOYEE GROUP BY DEPT;
d) SELECT DEPT, MAX(SALARY) AS Max_Salary FROM
EMPLOYEE GROUP BY DEPT;
e)
13. Grouping with Filtering (Having)
a) Write an SQL query to retrieve the departments where the sum of
salaries of all employees exceeds 200,000.
b) Write an SQL query to display the departments where the average
salary of employees is greater than 30,000.
c) Write an SQL query to find departments that have more than 5
employees.
d) Write an SQL query to retrieve the departments where the highest
salary is more than 60,000.
e) Write an SQL query to display the departments where the sum of all
salaries is less than 500,000.
Ans: a) SELECT DEPT, SUM(SALARY) FROM EMPLOYEE GROUP
BY DEPT HAVING SUM(SALARY) > 200000;
b) SELECT DEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY
DEPT HAVING AVG(SALARY) > 30000;
c) SELECT DEPT, COUNT(*) FROM EMPLOYEE GROUP BY
DEPT HAVING COUNT(*) > 5;
d) SELECT DEPT, MAX(SALARY) FROM EMPLOYEE GROUP
BY DEPT HAVING MAX(SALARY) > 60000;
e) SELECT DEPT, SUM(SALARY) FROM EMPLOYEE GROUP
BY DEPT HAVING SUM(SALARY) < 500000;
14. Grouping with Sorting
a) Write an SQL query to display the total salary for each department,
sorted in descending order of total salary.
b) Write an SQL query to display the number of employees in each
department, sorted by the number of employees in ascending order.
c) Write an SQL query to show the average salary for each department,
 sorted in descending order of average salary.
d) Write an SQL query to retrieve the department that has the highest
number of employees.
Ans: a) SELECT DEPT, SUM(SALARY) AS Total_Salary FROM
EMPLOYEE GROUP BY DEPT ORDER BY Total_Salary DESC;
b) SELECT DEPT, COUNT(*) AS Employee_Count FROM
EMPLOYEE GROUP BY DEPT ORDER BY Employee_Count
ASC;
c) SELECT DEPT, AVG(SALARY) AS Avg_Salary FROM
EMPLOYEE GROUP BY DEPT ORDER BY Avg_Salary DESC;
d) SELECT DEPT, COUNT(*) AS Employee_Count FROM
EMPLOYEE GROUP BY DEPT ORDER BY Employee_Count
DESC LIMIT 1;
15. Grouping with Multiple Columns
a) Write an SQL query to display the total salary and the count of
employees in each department, sorted alphabetically by the department
name.
b) Write an SQL query to calculate the average salary for employees
grouped by their department and year of joining.
Ans: a) SELECT DEPT, SUM(SALARY) AS Total_Salary, COUNT(*) AS
Employee_Count FROM EMPLOYEE GROUP BY DEPT
ORDER BY DEPT;
b) SELECT DEPT, YEAR(JOIN_DATE) AS Join_Year,
AVG(SALARY) AS Avg_Salary FROM EMPLOYEE GROUP BY
DEPT, YEAR(JOIN_DATE) ORDER BY DEPT, Join_Year;