Untitled

Q5
Environment Specifications & Instructions
Type of Database: MySQL
Database Name to be used: DB_Company
Existing Information
Table Descriptions: TBL_Employee, TBL_Department
TBL_Employee
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| EmpID | varchar(50) | YES | | NULL | |
| EmpFirstNm | varchar(50) | YES | | NULL | |
| EmpLastNm | varchar(50) | YES | | NULL | |
| Title | varchar(100) | YES | | NULL | |
| EmailID | varchar(150) | YES | | NULL | |
| Phone | varchar(30) | YES | | NULL | |
| City | varchar(50) | YES | | NULL | |
| State | varchar(50) | YES | | NULL | |
| Country | varchar(50) | YES | | NULL | |
| PostalCode | varchar(50) | YES | | NULL | |
| DepartmentID | int(11) | YES | | NULL | |
| hire_date | datetime | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
TBL_Department
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Department | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
Problem Statement
Louis is working in the HR Department. He wants to prepare data that shows each department's wise count of employees. Construct a query that displays
the department as Department and the number of employees for each department as No_of_Employees. Display the output in an ascending order of the
Department.
Column Name: Department, No_of_Employees
Sample Output
+------------+-----------------+
| Department | No_of_Employees |
+------------+-----------------+
| Admin | 4 |
| IT | 7 |
| Sales | 9 |
+------------+-----------------+
Note:
The Sample Output is given here for illustrative purposes.
 
ConQ6
Environment Specifications & Instructions
Type of Database
MySQL
Database: EmpDb
Problem Statement:-
Write a SQL query to categorize employees into three groups based on their salary:
Employees with a salary greater than or equal to 50000 should be categorized as 'High Salary.'
Employees with a salary between 30000 and 49999 should be categorized as 'Medium Salary.'
Employees with a salary less than 30000 should be categorized as 'Low Salary.'
The string value is case-sensitive here. The rows should be returned in the increasing order of “employee_id”.
Column Name: employee_id, first_name, last_name, salary, salary_category
Table Description :
employees
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| employee_id | int(11) | NO | PRI | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| department | varchar(50) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
Sample output:-
+-------------+------------+-----------+----------+-----------------+
| employee_id | first_name | last_name | salary | salary_category |
+-------------+------------+-----------+----------+-----------------+
| 101 | John | Smith | 52000.00 | High Salary |
| 102 | Jane | Doe | 48000.00 | Medium Salary |
| 103 | Tom | Johnson | 29000.00 | Low Salary |
| 104 | Emily | Brown | 34000.00 | Medium Salary |
| 105 | Martin | Luther | 56000.00 | High Salary |
+-------------+------------+-----------+----------+-----------------+
Note:
Make sure use EmpDb is present in the query before you submit as your output is compared with the expected output.
You can run multiple queries at once and see their output in the output section in the order the queries were written