DBMS Home

DBMS

DATA BASE MANAGEMENT SYSTEM Introduction
Types of Databases
Types of Databases
DBMS Architecture 1-level, 2-Level, 3-Level
DBMS vs. File System
Database Schema
Instance in DBMS
Database Language
View of data in DBMS
ER Model Introduction
Entity, Relationship, Attributes in DBMS
Constraints in DBMS
Generalization, Specialization and Aggregation in ER Model
Basic SQL Structured
SQL Trigger
Assertion in SQL
Checkpoints in DBMS
File Organization
Sequential File Organization
Heap file organization
Hash File Organization
B+ File Organization
Indexed sequential access method (ISAM)
Cluster file organization
What is a storage device
Vorolatile Storage vs Non-Volatile Stage
 RAID Or Redundancy Array Of The Independent Disk
Distributed Database System
Distributed DBMS Architectures
Fragmentation in Distributed DBMS
Data Replication in DBMS
Data Allocation in Distributed Database Design
Transaction
Transaction property
What is a Recoverability
What is Serializability
View Serializability 
Conflict Serializability
Lock-based Protocols
Two-Phase Locking 2PL 
Timestamp Ordering Protocol
Recovery Techniques
Deferred Update & Immediate Update
Shadow Paging
Algorithm for Recovery and Isolation Exploiting Semantics (ARIES)

ORACLE/MySQL Query List For University Exam & NET/GATE Exam practice queries

1. Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the Employee Number appearing first.
2. Query to display unique Jobs from the Employee Table.
3. Query to display the Employee Name concatenated by a Job separated by a comma.
5. Query to display the Employee Name and Salary of all the employees earning more than ₹28500. 
6. Query to display Employee Name and Department Number for the Employee No = E90.
7. Query to display Employee Name and Salary for all employees whose salary is not in the range of ₹15000 and ₹28500.
8. Query to display Employee Name and Department No of all the employees in Dept 101 and Dept 301 in the alphabetical order by name. 
9. Query to display Name and Hire Date of every Employee who was hired in 1981. 
10. Query to display Name and Job of all employees who don’t have a current Manager. 
11. Query to display the Name, Salary and Commission for all the employees who earn commission. Sort the data in descending order of Salary and Commission. 
12. Query to display Name of all the employees where the Second letter of their name is ‘a’. 
13. Query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name and are either in Dept No = 301 or their Manger’s Employee No = E78.
14. Query to display Name, Salary and Commission for all employees whose Commission Amount is greater than their Salary increased by 5%. 
15. Query to display the Current Date.
16. Query to display Name, Hire Date and Salary Review Date which is the 1st Monday after six months of employment.
17. Query to display Name and calculate the number of months between today and the date each employee was hired.
18. Query to display the following for each employee:- earns monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.
19. Query to display Name with the 1st letter capitalized and all other letter lower case and length of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.
20. Query to display Name, Hire Date and Day of the week on which the employee started.
21. Query to display Name, Department Name and Department No for all the employees.
22. Query to display Unique Listing of all Jobs that are in Department # 301. 
23. Query to display Name, Dept Name of all employees who have an ‘A’ in their name. 
24. Query to display Name, Job, Department No. And Department Name for all the employees working at the New Delhi location.
25. Query to display name and Employee no. along with their Manager’s Name and the Manager’s employee no.; Along with the Employee’s name who do not have a Manager. 
26. Query to display Name, Dept No. and Salary of any employee whose department No. and Salary match both the department no. and the salary of any employee who earns a commission. 
27. Query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies ₹10,000. 
28. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees.
 
29. Query to display the number of employees performing the same Job type functions. 
30. Query to display the no. of managers without listing their names.
31. Query to display the Department Name, Location Name, No. of Employees and the average salary for all employees in that department.
32. Query to display Name and Hire Date for all employees in the same dept. As Rupesh.
33. Query to display the Employee No. And Name for all employees who earn more than the average salary.
34. Query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’. 
35. Query to display the names and salaries of all employees who report to Kunal. 
36. Query to display the department no, name and job for all employees in the Sales department.
37. Create a query to display the Employee name and salary for all employees. Format the salary to be 15 characters long, left-padded with ₹. Label the column SALARY. 
38. Display the Employee name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday. 
39. List the employees in the ascending order of Designations of those, joined after the second half of 1981.
40. List the employees who are either ‘CLERK’ or ‘ANALYST’ in the Desc order. 
41. List the employees who joined on 25-JAN-81, 09-MAY-81, 05-NOV-81, 04- JUN-80 in asc order of seniority. 
42. List the employees who are joined in the month of Jan 1982.
43. List the Enames those are starting with ‘A’ and with five characters.
44. List the emps those are having six chars and fourth character must be ‘e’.
45. List the six character names starting with ‘R’ and ending with ‘h’.
46. List all the employees who joined before or after 1981. 
47. List the employees who are working under ‘Manager’. 
48. List the emps who joined in any year but not belongs to the month of January. 
49. List the emps of Department no 301 or 101 joined in the year 1981.
50. List the Eno, Ename, Salary, Dname of all the ‘Manager’ and ‘ANALYST’ working in Paris, Tokyo with an experience more than 7 years without receiving the Commission ascending order of Location.
51. Display the Employee no, Ename, Salary, Dname, Location, Department no, Job of all employees working at Tokyo or working for ACCOUNTING department with Annual Salary>28000, but the Salary should not be = 30000 or 28000 who doesn’t belongs to the Manager and whose eno is having a digit ‘7’ or ‘8’ in 3rd position in the ascending order of Dno and desc order of job.
52. List the employees who are senior to Karanveer working at Paris & Beijing. 
53. List the employees whose Jobs are same as Manish or Salary is more than Amit.
54. List the Employees whose Salary is greater than the total remuneration of the SALESMAN.
55. List the employees whose jobs same as Sameer or Arjun. 
4. Query to display all the data from the Employee Table. Separate each Column by a comma and name the said column as THE_OUTPUT.