ORACLE DATABASE MANAGEMENT SYSTEM
SQL Basics (1–20)
1. Create a table named Students with id, name, and age.
2. Insert 5 rows into the Students table.
3. Display all records from the Students table.
4. Update age of a student in Students table.
5. Delete one student record from Students table.
6. Create a table Employees with id, name, and salary.
7. Insert 3 employees into Employees.
8. Display employee names and salaries.
9. Increase salary of all employees by 10%.
10. Delete all employees with salary less than 20000.
11. Create a table Books with book_id, title, and author.
12. Insert 5 records into Books.
13. Display all book titles.
14. Update book author name.
15. Delete one record from Books.
16. Create a table Departments with dept_id and dept_name.
17. Insert 3 departments.
18. Display all departments.
19. Change department name in Departments.
20. Delete one department.
Constraints (21–35)
21. Create a table Courses with primary key course_id.
22. Add NOT NULL constraint on course_name.
23. Add UNIQUE constraint on email in Students.
24. Add CHECK constraint for salary > 10000 in Employees.
25. Add DEFAULT constraint for dept_name as “General”.
26. Create a foreign key between Employees and Departments.
27. Insert records to check primary key works.
28. Try inserting duplicate data to check UNIQUE.
29. Try inserting NULL in NOT NULL column.
30. Create table Marks with student_id as foreign key.
31. Insert data into Marks.
32. Display data from Marks.
33. Delete one student and check cascading effect.
34. Modify column size of Students table.
35. Drop constraint from Employees.
Queries (36–55)
36. Select all records from Students table.
37. Display only student names from Students.
38. Display students older than 20.
39. Display employees with salary more than 30000.
40. Display books written by a specific author.
41. Display all departments in alphabetical order.
42. Find highest salary from Employees.
43. Find lowest salary from Employees.
44. Find average salary of employees.
45. Find total number of students.
46. Find total salary of all employees.
47. Display students whose name starts with ‘A’.
48. Display books where title ends with “SQL”.
49. Display students whose age is between 18 and 22.
50. Display employees whose salary is between 20000 and 40000.
51. Find all employees not in department 1.
52. Display distinct department names.
53. Find number of employees in each department.
54. Group students by age.
55. Sort employees by salary in descending order.
Joins (56–70)
56. Perform INNER JOIN on Students and Marks.
57. Perform LEFT JOIN on Employees and Departments.
58. Perform RIGHT JOIN on Employees and Departments.
59. Perform FULL JOIN on Employees and Departments.
60. Display students and their marks using join.
61. Display employees and their department names.
62. Find employees who do not belong to any department.
63. Find students who have no marks entry.
64. Join Books and Students for issued books.
65. Find total salary per department using join.
66. Find students with highest marks using join.
67. Display all employees with department = "HR".
68. Display students with subject = "Math".
69. Display department name and number of employees.
70. Display book title and student who borrowed it.
Subqueries & Views (71–85)
71. Find employees earning more than average salary.
72. Find students older than average age.
73. Find employees with salary greater than employee “John”.
74. Find books issued by student “Rahul”.
75. Display top 3 highest salaries.
76. Display top 5 oldest students.
77. Create a view for employee details.
78. Create a view for student marks.
79. Drop a view created earlier.
80. Create a view for book details.
81. Use subquery to find max marks.
82. Use subquery to find min salary.
83. Display names of students who got maximum marks.
84. Display names of employees who earn maximum salary.
85. Display departments having more than 2 employees.
PL/SQL (86–100)
86. Write a PL/SQL block to display student name.
87. Write a PL/SQL block to display all employee salaries.
88. Write a PL/SQL block with IF ELSE condition.
89. Write a PL/SQL block with FOR loop.
90. Write a PL/SQL block with WHILE loop.
91. Write a PL/SQL block with CASE statement.
92. Create a procedure to insert student record.
93. Create a procedure to display all students.
94. Create a procedure to update employee salary.
95. Create a function to calculate square of a number.
96. Create a function to calculate factorial.
97. Create a trigger before inserting into Students.
98. Create a trigger after updating Employees.
99. Create a cursor to fetch employee records.
100. Create a cursor to fetch student records.
