Scribd
Upload
6 views2 pages

Data Science Assignment Question

The document outlines a data science assignment involving employee data management using SQL. It includes tasks such as creating a table, modifying columns, updating salaries, deleting records, and retrieving specific employee information. Additionally, it covers transaction management and privilege assignments for different user roles.

Uploaded by

Debankur Basu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Data Science Assignment Question

The document outlines a data science assignment involving employee data management using SQL. It includes tasks such as creating a table, modifying columns, updating salaries, deleting records, and retrieving specific employee information. Additionally, it covers transaction management and privilege assignments for different user roles.

Uploaded by

Debankur Basu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Data Science Assignment Question

EmpID Name Department Gender Experience Salary Email

101 Amit IT M 2 3000 amit@[Link]

102 Nisha HR F 4 4000 NULL

103 Yogesh IT M 3 3000 yogesh@[Link]

104 Puja HR F 6 6000 puja@[Link]

105 Anil IT M 5 5000 anil@[Link]

106 Shiv IT M 7 7000 NULL

107 Risha HR F 2 4000 risha@[Link]

108 Ram IT M 8 8000 ram@[Link]

109 Husain IT M 4 5000 husain@[Link]

110 Kumar HR M 10 10000 kumar@[Link]

Answer the following questions:

1. Write the SQL query for creating the above table.


2. The HR team realizes that employee performance ratings must now be stored. Write a
DDL command to add a new column PerformanceRating to the Emp table.
3. Later, management decides salary must support decimal values. Modify the datatype of
Salary to DECIMAL.
4. Employees in the IT department receive a 10% salary hike. Write a query to update
their salaries.
5. HR finds duplicate test records for interns with zero experience. Delete all employees
whose Experience = 0.
6. Management asks for the top 3 highest-paid employees. Write an SQL query to fetch
the information.
7. Find employees whose: Salary is between 4000 and 8000 and Department is IT or HR.
Write a query using BETWEEN and IN.
8. HR wants to email employees who do not have email IDs registered. Retrieve names
of employees with NULL emails.
9. Compute: Mean salary, Maximum salary and Standard deviation. Return all three
values in one query.
10. Write a query to return average salary per department, but only for departments whose
average salary is greater than the company-wide average. (Hint: subquery + GROUP
BY + HAVING).
11. You begin a transaction, update salaries for HR employees, create a savepoint sp1, then
delete some IT records, and finally issue: ROLLBACK TO sp1;. Explain which
operations remain and which are undone.
12. After creating two savepoints sp1 and sp2, you issue a COMMIT and then try:
ROLLBACK TO sp1;. What happens? Explain why.
13. A junior analyst must be allowed to only read employee data. Write the appropriate
DCL command.
14. A senior HR user must be able to update salaries but not delete records. Grant suitable
privileges.

You might also like