1.
Table: Students 10
i. Write a SQL query to display all records from the table.
SELECT * FROM Students;
ii. Write a query to fetch the names of students who scored more than 80 marks.
SELECT Name FROM Students WHERE Marks > 80;
iii. Write a query to display all students' details sorted by Marks in descending order.
SELECT * FROM Students ORDER BY Marks DESC;
iv. Write a query to find the students who belong to the city 'Delhi'.
SELECT * FROM Students WHERE City = 'Delhi';
v. Write an SQL query to calculate the average marks of all students.
SELECT AVG(Marks) AS AverageMarks FROM Students;
vi. Write a query to find the total number of students in each section.
SELECT Section, COUNT(*) AS TotalStudents FROM Students GROUP BY Section;
vii. Write a query to fetch the details of students whose marks are between 70 and 90.
SELECT * FROM Students WHERE Marks BETWEEN 70 AND 90;
viii. Write a query to fetch the names of students born after 2008-06-01.
SELECT Name FROM Students WHERE DateOfBirth > '2008-06-01';
2. Table: Employees
i. Write a query to display all the details of employees.
SELECT * FROM Employees;
ii. Write a query to fetch the names of employees working in the IT department.
SELECT Name FROM Employees WHERE Department = 'IT';
iii. Write a query to display all employee details sorted by their salary in
ascending order.
SELECT * FROM Employees ORDER BY Salary ASC;
iv. Write a query to find the employees who are older than 30 years.
SELECT * FROM Employees WHERE Age > 30;
v. Write a query to calculate the total salary paid to all employees.
SELECT SUM(Salary) AS TotalSalary FROM Employees;
vi. Write a query to find the number of employees in each department.
SELECT Department, COUNT(*) AS EmployeeCount FROM Employees GROUP BY
Department;
vii. Write a query to fetch the details of employees whose salaries are between 55,000
and 75,000.
SELECT * FROM Employees WHERE Salary BETWEEN 55000 AND 75000;
viii. Write a query to find the names of employees who joined after 2016-01-01.
SELECT Name FROM Employees WHERE DateOfJoining > '2016-01-01';
3. Table: Books 10
i. Write a query to display all the details of books available in the library.
SELECT * FROM Books;
ii. Write a query to fetch the titles of all books under the "Fiction" genre.
SELECT Title FROM Books WHERE Genre = 'Fiction';
iii. Write a query to display all book details sorted by price in descending order.
SELECT * FROM Books ORDER BY Price DESC;
iv. Write a query to find books published after the year 2000.
SELECT * FROM Books WHERE PublishedYear > 2000;
v. Write a query to find the total number of available copies of all books in the library.
SELECT SUM(AvailableCopies) AS TotalAvailableCopies FROM Books;
vi. Write a query to find the number of books available in each genre.
SELECT Genre, COUNT(*) AS TotalBooks FROM Books GROUP BY Genre;
vii. Write a query to find books with a price between ₹300 and ₹500.
SELECT * FROM Books WHERE Price BETWEEN 300 AND 500;
4. Consider the following table: Sales
Sales_ID Prod_NAME Price Discount
1101 Laptop 65000 2500
1103 Pen Tab 29500 1000
1105 Desktop 50000 1550
1106 Printer 12000 2000
i. How many records and fields are there in sales table?
ii. Write SQL commands to do the following:
a. Display sales ID and price of all products whose discount is more
than 1000.
b. Display the details alphabetically by product name.
c. Display product name and sales price after deducting the discount
from the price.
Note: Sales price can be calculated as (Price-Discount)