DBMS (CS104)
Lab Assignment 4
SQL Operators and Functions
Objective: The objective of this lab is to practice SQL queries in MySQL using operators, string
functions, conditional functions, and aggregate concepts. Students will learn how to filter,
manipulate, and format data effectively.
Sample Database
Create the following table and insert sample data before attempting the questions:
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
job_title VARCHAR(50),
salary DECIMAL(10,2),
bonus DECIMAL(10,2)
);
INSERT INTO employees VALUES
(1, 'John', 'Smith', 'IT', 'Developer', 60000, NULL),
(2, 'James', 'Brown', 'Design', 'Designer', 50000, 5000),
(3, 'Alice', 'Johnson', 'HR', 'Manager', 75000, NULL),
(4, 'Bob', 'Davis', 'IT', 'Tester', 55000, 2000),
(5, 'Carol', 'Miller', 'Finance', 'Analyst', 48000, NULL),
(6, 'David', 'Wilson', 'Design', 'Architect', 65000, 3000),
(7, 'Eve', 'Taylor', 'IT', 'Developer', 72000, 4000);
Lab Questions
1. List the first name and last name of employees whose first name starts with 'J'.
2. List the first name and job title of employees who work in the IT or Design
department.
3. Find employees whose bonus is missing (NULL).
4. Display employees whose salary is not between 50,000 and 60,000.
5. Display employees who work in the IT department and have a salary greater than
55,000.
6. Display the full names (first name + last name) of employees.
7. List all the unique department names from the employees table.
8. Display the first name in uppercase, last name in lowercase, and the length of first
name.
9. Write a query to add 100 and 200 and return the result as sum_value.
10. Display employee details in the format:
FirstName works in Department.
11. Display the first three letters of each employee's first name.
12. Find the position of the letter 'a' in each employee's first name.
13. Remove extra spaces from the string ' Hello World '.
14. Display employees’ first names padded with * (on the left and right) up to length 10.
15. Replace all occurrences of the letter 'a' in the first name with '@'.
16. Display each employee’s salary and the remainder when divided by 10,000.
17. Display the monthly salary (salary ÷ 12) rounded to 2 decimal places.
18. Display the monthly salary (salary ÷ 12) truncated to remove decimal values.
19. Display employees’ first names along with their bonus. If the bonus is NULL, show it as
0.
20. Display employees’ first names and a bonus status:
Has Bonus if bonus is not NULL
No Bonus if bonus is NULL
21. Return NULL if an employee belongs to the IT department, otherwise return their
department name.
Subject Faculty
Dr. Jitendra
Dept of CSE, JUET
