Create a table with the below scenario:
Company A wants to maintain the list of employees. Here are the details Company A wants to track:
- First Name
- Last Name
- A Unique Id to each employee.
- Data of birth,
- Joined date
- Phone number
- Whether employee is currently with organization or left the organization
- Job roles( Assume that roles of organization are 100,101,102,103 and default is 101)
Above example should include all the constraints such as Default, NotNull, and Unique.
All working class age should be between 20-60.
- Now Company A wants to track aadhar no as well in addition to the existing details .
- Company A can search people using Aadhar number as well.
- Add 10 records to the Table.
- Do the Delete and Truncate. Write down the difference
- Write a Query to list the people whose name starts with Chand ends with ed.
- Write a query to display only 3 rows without writing any condition.
- Write a Query to display First Name column name as fnand Last Column name as ln.
- Write a Query to find the second highest age of the employee
- Write a query to find the third largest age of the employee
- Write a query to find the people with maximum service with the organization.
- Create a table to track the leave details. Here are the details Company A wants to track: Employee Id
- Leave type (Only Sick leave and Earned leaves are applicable)
- Start Date
- End Date
- Insert sample Data here .At least 20 records
- Write a Query display the employee details who has not taken single leave in last one year Write a Query to display the employee details who has taken maximum leaves
- Write a query to display the employee details who has taken maximum leaves in the last one month.
- Write a query display the employee name and leaves taken in the current financial year.
- Create a table to maintain the leaves as per the job levels
- Assume that here are the leaves as per job level:
- Write the query to print employees who have taken more leaves than eligible leaves
- Write a query to display employee name, eligible leaves and taken leaves and percent of leaves utilized.
- Write a query to display the employee count job level wise.
Create a table to track payroll information.
Here are the details Company A wants to track:
Enter the sample data.
- Write down the Query to find the employee whose salary is highest and lowest job level wise.
- Create a table to track the Project allocations.
- Here are the details of Project allocations:
- Project name
- Manager details
- Employee details
- Project budget
- Project duration in months Enter sample data.
- Write the query to find the employees who are not allocated to single project.
- Write the query to find the employees who are working as managers
- Write the query to find the projects with high budget.
- Write the query to find the project on which maximum employees are allocated.
Refer to the attached word document