Scribd
Upload
4 views2 pages

MySQL Assignment 2 - Querying Data

The document outlines an assignment for querying data in MySQL, building on a previous assignment involving database and table creation. It includes tasks such as retrieving distinct values, using aliases, applying WHERE clauses, sorting, grouping, and performing joins on employee data. Specific queries are provided for various operations like calculating sums, averages, and handling missing designations.

Uploaded by

tamileduonline
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
4 views2 pages

MySQL Assignment 2 - Querying Data

The document outlines an assignment for querying data in MySQL, building on a previous assignment involving database and table creation. It includes tasks such as retrieving distinct values, using aliases, applying WHERE clauses, sorting, grouping, and performing joins on employee data. Specific queries are provided for various operations like calculating sums, averages, and handling missing designations.

Uploaded by

tamileduonline
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

MySQL Assignment 2 – Querying Data

Instructions:
★​ Continue from the previous assignment (database & table creation with constraints).
★​ Use the provided DML insert codes to populate the tables from here: Employee Data
★​ Execute the queries in this assignment after inserting the data.

1.​ Distinct Values:

⦿ a query to retrieve distinct salaries from the Employees table.

2.​ Alias (AS):

⦿ Provide aliases for the "age" and "salary" columns as "Employee_Age" and
"Employee_Salary", respectively.

3.​ Where Clause & Operators:

⦿ Retrieve employees with a salary greater than ₹50000 and hired before
2016-01-01.

⦿ Find the employee whose designation is missing and fill it with "Data
Scientist".

Sorting and Grouping Data:

1.​ ORDER BY:

⦿ Find employees sorted by department ID in ascending order and salary in


descending order.

2.​ LIMIT:

⦿ Display the first 5 employees hired in the year 2018.


3.​ Aggregate Functions:

⦿ Calculate the sum of all salaries in the Finance department.

⦿ Find the minimum age among all employees.

4.​ GROUP BY:

⦿ List the maximum salary for each location.

⦿ Calculate the average salary for each designation containing the word
'Analyst'.

5.​ HAVING:

⦿ Find departments with less than 3 employees.

⦿ Find locations with female employees whose average age is below 30.

Joins:
1.​ Inner Join:

⦿ List employee names, their designations, and department names where


employees are assigned to a department.

2.​ Left Join:

⦿ List all departments along with the total number of employees in each
department, including departments with no employees.

3.​ Right Join:

⦿ Display all locations along with the names of employees assigned to each
location. If no employees are assigned to a location, display NULL for
employee name.

You might also like