Data Analytics Training In Chennai

SQL Tasks – Hands-On Practice

Day 1: Filtering and Sorting Data (Part 1)

1. Retrieve names starting with ‘S’.
2. Display names that begin with ‘S’ and end with ‘n’.
3. Find employees in department 90 whose names start with ‘S’.
4. List employees working in departments 10, 20, 50, and 90.
5. Retrieve names, salaries, and department IDs of employees outside these departments.
6. Display employees with salaries between 5000 and 7000.
7. List employees without managers.
8. Retrieve all employee records sorted by first name.
9. Display department ID and salary, sorted by department (ascending) and salary (descending).
10. Find employees earning more than $12,000.
11. Retrieve the last name and department number for employee ID 176.
12. Display employees hired between Feb 20, 2003, and May 1, 2005, sorted by hire date.
13. List employees from departments 20 and 50, sorted alphabetically.
14. Show job titles of employees without managers.
15. Display salary and commission details for employees earning commissions.

Day 2: Aggregate Functions

1. Count the number of employees.
2. Calculate the average salary.
3. Identify the highest and lowest salaries.
4. Determine the average salary per department.
5. Find departments with more than 5 employees.
6. Compute the average salary for employees earning above $50,000.
7. Calculate the total salary expense for each department.
8. Determine employee hires per year.
9. Find hires per month.
10. Identify hires by weekday.
11. Count employees sharing the same job title.
12. Display max, min, sum, and average salaries.
13. Find salary statistics per job type.
14. Calculate the difference between highest and lowest salaries.
15. Count the number of managers.

Day 3: Analytical vs. Aggregate Functions

1. Compute the organization’s average salary.
2. Calculate the department-wise average salary.
3. Display average salary alongside each employee.
4. Find the cumulative salary per department.
5. Compute cumulative salary for the entire organization.
6. Rank employees’ salaries within departments.
7. Identify the highest and lowest salaries per department.
8. Find the oldest and newest joiners per department.

Day 4: Date Functions

1. Retrieve the current date and time.
2. Extract year, month, and day from a date.
3. Calculate the difference between two dates in days.
4. Format dates in SQL Server.
5. Add and subtract days from a date.
6. Identify the first and last day of the current month.
7. Extract the quarter of the year from a date.
8. Determine the number of weeks between two dates.
9. Compute hours, minutes, and seconds between two timestamps.

Day 5: String Functions

1. Extract first names from full names.
2. Convert employee names to uppercase.
3. Retrieve employees with names starting with ‘J’.
4. Concatenate first and last names.
5. Format phone numbers with dashes.
6. Find employees with specific email domains.
7. Identify last names with exactly five characters.
8. Extract the domain from email addresses.
9. Replace specific characters in strings.
10. Reverse characters in last names.

Day 6: Case & Convert Functions

1. Indicate whether employees receive commissions.
2. Classify employees based on experience levels.
3. Categorize employees’ salaries as Low, Medium, or High.
4. Convert hire dates into various formats.
5. Use CASE to label employees based on job titles.

DAY 7

DATA DEFINITION LANGUAGE (DDL):

Creating and Modifying Tables:

1. Write an SQL query to create a table named Employees with the following structure:

   • EmployeeId (integer, primary key)

   • FirstName (varchar(50))

   • LastName (varchar(50))

   • HireDate (date)

   • Salary (decimal(10,2))

2. Modify the Employees table to include a new column EmailAddress of type varchar(100).

3. Alter the Salary column in Employees to allow NULL values.

4. Rename the LastName column in Employees to Surname.

Dropping and Renaming Tables: 5. Remove the Employees table from the database. 6. Change the table name from Employees to Staff.

Constraints and Indexes: 7. Apply a unique constraint to the EmailAddress column to prevent duplicate values. 8. Establish a foreign key in Employees referencing the DepartmentId column in the Departments table. 9. Retrieve a list of all tables present in the current database. 10. Construct a Projects table with columns ID (primary key), ProjectName (varchar(100)), StartDate (date), and Budget (decimal(15,2)).

DAY 8

DATA MANIPULATION LANGUAGE (DML) & TRANSACTION CONTROL LANGUAGE (TCL):

Data Manipulation:

1. Insert a new row into Employees with EmployeeId 101, FirstName ‘John’, LastName ‘Doe’, HireDate ‘2023-05-15’, and Salary 75000.

2. Update the Salary of EmployeeId 101 to 80000.

3. Remove the record for EmployeeId 101 from Employees.

4. Set DepartmentId to 5 for employees earning more than 70000.

5. Insert multiple records into Projects with names ‘Project X’, ‘Project Y’, and ‘Project Z’ along with respective start dates and budgets.

6. Fetch all records from Employees, replacing NULL values in EmailAddress with ‘No Email’.

7. Update all NULL values in CommissionPCT column to 0 in Employees.

8. Add multiple entries in Projects using a single query.

9. Delete records from Projects where StartDate is before ‘2024-03-01’.

10. Update EmailAddress to ‘updated@example.com‘ for employees whose LastName is ‘Doe’.

Transactions: 11. Define transactions in SQL Server and explain their significance. 12. Explain the use of the COMMIT statement. 13. Describe the function of the ROLLBACK statement. 14. Explain what a save point is and how it can be used. 15. Compare COMMIT, ROLLBACK, and SAVE TRANSACTION.

DAY 9

CONSTRAINTS:

Primary Key Constraints:

1. Create a Students table with StudentId as the primary key.

2. Insert a student with StudentId 1001, FirstName ‘Alice’, LastName ‘Smith’, and DateOfBirth ‘2000-01-01’.

Unique Constraints: 3. Create an Employees table ensuring Email is unique.

Foreign Key Constraints: 4. Define a Departments table and reference DepartmentId in the Employees table.

Check Constraints: 5. Add a constraint ensuring Quantity is at least 0 and Price is greater than 0 in Products. 6. Modify Employees to enforce Salary constraints between 30,000 and 150,000.

Default Constraints: 7. Create an Orders table with Status defaulting to ‘Pending’. 8. Set a default constraint on OrderDate to auto-fill with the current date.

Composite Constraints: 9. Establish a composite primary key on StudentId and CourseId in CourseEnrollments. 10. Ensure FirstName and LastName combinations are unique in Employees.

DAY 10

JOINS:

1. Retrieve employee names with corresponding department names using an INNER JOIN.

2. Fetch employee details including job titles, even if job titles are absent (LEFT JOIN).

3. Display all departments with associated employees, including empty departments (RIGHT JOIN).

4. List all employees and departments, even if unlinked (FULL OUTER JOIN).

5. Use a self-join to match employees with their managers.

6. Perform a CROSS JOIN to create all possible employee-department combinations.

7. Group employees by department and count them.

8. Use a subquery to find employees earning above the department’s average salary.

9. Join multiple tables to list employee names, job titles, and department names.

10. Retrieve employees from departments with more than five employees.

DAY 11

SET OPERATORS:

1. Use UNION to combine average and max salaries across departments.

2. Find departments where the average salary was identical in different years using INTERSECT.

3. Identify departments with higher total salaries in 2023 than in 2022 using EXCEPT.

4. Combine employee count and average salaries using UNION ALL.

5. Use INTERSECT to find matching salary data across different tables.

6. Apply EXCEPT to locate departments where active salaries exceed retired salaries.

DAY 12

SUBQUERIES:

1. List employees from the same department as a specific individual, excluding that person.

2. Find employees earning above the department’s average salary.

3. Retrieve employees whose department location is 1700.

4. Identify employees reporting to a specific manager.

5. Locate employees in the Executive department.

DAY 13

VIEWS:

1. Create a view displaying employee names and salaries.

2. Develop a view for employees earning above $15,000.

3. Create a view joining Employees and Departments.

4. Modify an existing view to increase salaries by 10%.

5. Drop the previously created view.

DAY 14

STORED PROCEDURES:

1. Explain stored procedures and their purpose.

2. Demonstrate how to create and execute a stored procedure.

3. Use parameters within a stored procedure.

4. Modify an existing stored procedure.

5. List benefits of stored procedures.

6. Create a procedure to fetch employee details by EmployeeId.

7. Develop a procedure to count employees in a department.

8. Create a procedure to delete employees by department.

DAY 15

INDEXING:

1. Define indexes and explain their importance.

2. Describe different index types in SQL Server.

3. Show how to create an index on a table.

4. Differentiate between clustered and non-clustered indexes.

5. Retrieve existing indexes on a table.

6. Create a clustered index on EmployeeId.

7. Develop a non-clustered index on LastName.

8. Remove an index.

9. Establish a unique index on Email.

10. Create a non-clustered index on DepartmentId and Salary.