UNIVERSITY OF CHITTAGONG

Department of Computer Science and Engineering

Lab Task 01: (Basic SQL)

Course Code: CSE 414
Course Title: Database Systems Lab

Submitted To:
Dr. Rudro Pratap Deb Nath
Professor
Department of Computer Science and Engineering
University of Chittagong

Submitted By:
Kawsar Ahmed Khan
ID: 23701055
Session: 2022-23
[Link]. Engineering (4th Semester)
Department of Computer Science and Engineering
University of Chittagong

Date of Submission: 25 February 2026

 Chapter-1

Question: 1. The following SELECT statement executes successfully:

SELECT last name, job id, salary AS Sal
FROM employees;

Answer: TRUE
SELECT last name, job id, salary AS Sal
FROM [Link];

Question: 2. Does the following SQL statement execute successfully?

SELECT *
FROM job grades;

Answer: False, table job grades doesn’t exist

SELECT * FROM job grades;

Question: 3. There are four coding errors in the following statement. Can you identify them?
SELECT employee id, last name sal x 12 ANNUAL SALARY
FROM employees;

Answer:
1. No comma after last name.
2. sal is incorrect, should be SALARY.
3. x is incorrect, should be *.
4. ANNUAL SALARY should be inside double quotation. ”
Correct query:
SELECT employee id, last name,
SALARY * 12 AS ”ANNUAL SALARY”
FROM [Link];

Question: 4. Your first task is to determine the structure of the DEPARTMENTS table and its contents.

Answer: To determine the structure of a table we use ”Desc tab name;” command.
Desc [Link];
SELECT * FROM [Link];

2
Question: 5. You need to determine the structure of the EMPLOYEES table. The HR department wants
a query to display the last name, job code, hire date, and employee number for each employee, with the
employee number appearing first. Provide an alias STARTDATE for the HIRE DATE column. Save your
SQL statement to a file named lab 01 [Link] so that you can dispatch this file to the HR department.

Answer: To determine the structure of a table we use ”Desc tab name;” command.
Desc [Link];
SELECT EMPLOYEE ID, last name, job id, hire date AS ”HIREDATE” FROM [Link];

Question: 6. After you have executed the query, make sure that you do not enter your next query in the
same worksheet. Open a new worksheet.
Answer: To open a new worksheet: Ctrl + Shift + N

Question: 7. The HR department wants a query to display all unique job codes from the EMPLOYEES table

Answer: Query to determine unique job codes from employees:

SELECT DISTINCT(job id) FROM [Link];

Question: 8. The HR department wants more descriptive column headings for its report on employees.
Copy the statement from lab 01 [Link] to a new SQL Worksheet. Name the column headings Emp #, Em-
ployee, Job, and Hire Date, respectively.

Answer: Had to change the column names using the AS command.

SELECT EMPLOYEE ID AS ”EMP#”, first name AS ”EMPLOYEE”, job id AS ”JOB”, HIRE DATE
FROM [Link];

Question: 9. The HR department has requested a report of all employees and their job IDs. Display the last
name concatenated with the job ID (separated by a comma and space) and name the column Employee and
Title.

Answer: To concatenate two separate columns into one column, we can use CONCAT() command
SELECT CONCAT(FIRST NAME, ’, ’, JOB ID) AS ”EMPLOYEE and TITLE” FROM [Link];

Question: 10. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all
the data from that table. Separate each column output by a comma. Name the column title THE OUTPUT.

Answer: To do so, we can use CONCAT() command

SELECT CONCAT(EMPLOYEE ID, ’, ’, FIRST NAME, ’, ’, LAST NAME, ’, ’, JOB ID) AS ”OUTPUT”
FROM [Link];

3
 Chapter-2

Question: 1. Because of budget issues, the HR department needs a report that displays the last name and
salary of employees who earn more than $12,000. Save your SQL statement as a file named lab 02 [Link].
Run your query.

Answer:
- We can implement the condition using WHERE clause.
SELECT LAST NAME, SALARY FROM [Link] WHERE SALARY ¿ 12000;

Question: 2. Open a new SQL Worksheet. Create a report that displays the last name and department
number for employee number 176. Run the query.

Answer:
SELECT LAST NAME, DEPARTMENT ID FROM [Link] WHERE EMPLOYEE ID=176;

Question: 3. The HR department needs to find high-salary and low-salary employees. Modify lab 02 [Link]
to display the last name and salary for any employee whose salary is not in the range of $5,000 to $12,000.
Save your SQL statement as lab 02 [Link].

Answer:
- We can do perform that query using BETWEEN clause
SELECT LAST NAME, SALARY FROM [Link] WHERE SALARY NOT BETWEEN 5000
and 12000;

Question: 4. Create a report to display the last name, job ID, and start date for the employees with the last
names of Matos and Taylor. Order the query in ascending order by the start date.

Answer:
- Using WHERE clause and IN() function
SELECT last name, job id, hire date FROM [Link] WHERE last name IN(’Matos’, ’Taylor’);

Question: 5. Display the last name and department number of all employees in departments 20 or 50 in
ascending alphabetical order by name.

Answer: - to sort in order we use ORDER BY clause.

SELECT last name, department id FROM [Link] where DEPARTMENT ID IN (20, 50) OR-
DER BY last name ASC;

4
Question: 6. Modify lab 02 [Link] to display the last name and salary of employees who earn between
$5,000 and $12,000, and are in department 20 or 50. Label the columns Employee and Monthly Salary,
respectively. Resave lab 02 [Link] as lab 02 [Link]. Run the statement in lab 02 [Link]

Answer:
SELECT LAST NAME AS ”Employee”, SALARY AS ”MONTHLY SALARY” FROM [Link]
WHERE SALARY BETWEEN 5000 and 12000 AND DEPARTMENT ID IN(20,50);

Question: 7. The HR department needs a report that displays the last name and hire date for all employees
who were hired in 1994.

Answer:
SELECT LAST NAME, HIRE DATE FROM [Link] WHERE EXTRACT(YEAR FROM HIRE DATE)=1

Question: 8. Create a report to display the last name and job title of all employees who do not have a
manager.

Answer:
SELECT LAST NAME, JOB ID
FROM [Link]
WHERE MANAGER ID is null;

Question: 9. Create a report to display the last name, salary, and commission of all employees who earn
commissions. Sort data in descending order of salary and commissions. Use the column’s numeric position
in the ORDER BY clause.

Answer:
SELECT LAST NAME, SALARY, COMMISSION PCT
FROM [Link]
WHERE COMMISSION PCT IS NOT NULL
ORDER BY 2 DESC, 3 DESC;

Question: 10. Members of the HR department want to have more flexibility with the queries that you are
writing. They would like a report that displays the last name and salary of employees who earn more than
an amount that the user specifies after a prompt. Save this query to a file named lab 02 [Link]. If you enter
12000 when prompted, the report displays the following results:

Answer: - input using &amount.

SELECT LAST NAME, SALARY

5
FROM [Link]
WHERE SALARY ¿ &amount;

Question: 11. The HR department wants to run reports based on a manager. Create a query that prompts the
user for a manager ID and generates the employee ID, last name, salary, and department for that manager’s
employees. The HR department wants the ability to sort the report on a selected column. You can test the
data with the following values:

Answer:
SELECT EMPLOYEE ID, LAST NAME, SALARY, DEPARTMENT ID
FROM [Link]
WHERE MANAGER ID = &manager id
ORDER BY &order column;

Question: 12. Display all employee last names in which the third letter of the name is A

Answer:
- Using SUBSTR() function
SELECT LAST NAME
FROM [Link]
WHERE SUBSTR(LAST NAME, 3, 1) = ’a’;

Question: 13. Display the last names of all employees who have both an ”a” and an ”e” in their last name.

Answer:
- Using ’LIKE’
SELECT LAST NAME
FROM [Link]
WHERE LAST NAME LIKE ’%a%’ AND LAST NAME LIKE ’%e%’;

Question: 14. Display the last name, job, and salary for all employees whose jobs are either those of a sales
representative or of a stock clerk, and whose salaries are not equal to $2,500, $3,500, or $7,000.

Answer:
- NOT IN() and IN() function
SELECT LAST NAME, JOB ID, SALARY
FROM [Link]
WHERE JOB ID IN (’SA REP’, ’ST CLERK’)
AND SALARY NOT IN (2500, 3500, 7000);

6
Question: 15. Display the last name, salary, and commission for all employees whose commission is 20%

Answer:
SELECT LAST NAME AS Employee, SALARY AS ”Monthly Salary”, COMMISSION PCT
FROM [Link]
WHERE COMMISSION PCT = 0.2;

7
 Chapter-3

Question: 1. Write a query to display the system date. Label the column as Date. Note: If your database is
remotely located in a different time zone, the output will be the date for the operating system on which the
database resides.

Answer:
SELECT SYSDATE AS ”DATE” FROM dual;

Question: 2. The HR department needs a report to display the employee number, last name, salary, and
salary increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary.
Save your SQL statement:

Answer:
SELECT EMPLOYEE ID, LAST NAME, SALARY, ROUND(SALARY * 1.155) AS ”New Salary”
FROM [Link];

Question: 3. Run your query :

Answer:
SELECT EMPLOYEE ID, LAST NAME, SALARY, ROUND(SALARY * 1.155) AS ”New Salary”
FROM [Link];

Question: 4. Modify your query lab 03 [Link] to add a column that subtracts the old salary from the new
salary. Label the column Increase. Save the contents of the file as lab 03 [Link]. Run the revised query.

Answer:
SELECT EMPLOYEE ID, LAST NAME, SALARY, ROUND(SALARY * 1.155) AS ”New Salary”,
ROUND(SALARY * 1.155) - SALARY AS Increase
FROM [Link];

Question: 5. Write a query that displays the last name (with the first letter in uppercase and all the other
letters in lowercase) and the length of the last name for all employees whose name starts with the letters ”J,”
”A,” or ”M.” Give each column an appropriate label. Sort the results by the employees’ last names

Answer:
SELECT INITCAP(last name) AS Name, LENGTH(last name) AS Length
FROM [Link]
WHERE UPPER(SUBSTR(last name, 1, 1)) IN (’J’, ’A’, ’M’) ORDER BY last name;

8
Question: 6. The HR department wants to find the duration of employment for each employee. For each
employee, display the last name and calculate the number of months between today and the date on which
the employee was hired. Label the column as MONTHS WORKED. Order your results by the number of
months employed. Round the number of months up to the closest whole number.

Answer:
SELECT LAST NAME,
ROUND(MONTHS BETWEEN(SYSDATE, HIRE DATE)) AS MONTHS WORKED FROM [Link]
ORDER BY MONTHS WORKED;

Question: 7. Create a query to display the last name and salary for all employees. Format the salary to be
15 characters long, left-padded with the $ symbol. Label the column as SALARY.

Answer:
SELECT LAST NAME,
LPAD(CONCAT(’$’, SALARY), 15, ’$’) AS SALARY
FROM [Link];

Question: 8. Create a query that displays the first eight characters of the employees’ last names and indi-
cates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in
descending order of salary. Label the column as EMPLOYEES AND THEIR SALARIES.

Answer:
SELECT
RPAD(SUBSTR(LAST NAME, 1, 8), 8) —— ’ ’ —— RPAD(’*’, TRUNC(SALARY/1000), ’*’) AS ”EM-
PLOYEES AND THEIR SALARIES”
FROM [Link]
ORDER BY SALARY DESC;

Question: 9. Create a query to display the last name and the number of weeks employed for all employees
in department 90. Label the number of weeks column as TENURE. Truncate the number of weeks value
to 0 decimal places. Show the records in descending order of the employee’s tenure. Note: The TENURE
value will differ as it depends on the date on which you run the query

Answer:
SELECT last name,
TRUNC((SYSDATE - hire date)/7) AS TENURE
FROM [Link]
WHERE department id = 90
ORDER BY TENURE DESC;

9
 Chapter-4

Question: 1. Create a report that produces the following for each employee: ¡employee last name¿ earns
¡salary¿ monthly but wants ¡3 times salary¿. Label the column Dream Salaries.

Answer:
SELECT
LAST NAME —— ’ earns ’ —— TO CHAR(SALARY, ’9,999,999.00’) —— ’ monthly but wants ’ ——
TO CHAR(SALARY * 3, ’9,999,999.00’) AS ”Dream Salaries” FROM [Link];

Question: 2. Display each employee’s last name, hire date, and salary review date, which is the first Mon-
day after six months of service. Label the column REVIEW. Format the dates to appear in the format similar
to ”Monday, the Thirty-First of July.”

Answer:
SELECT
LAST NAME,
HIRE DATE,
TO CHAR(NEXT DAY(ADD MONTHS(HIRE DATE, 6), ’MONDAY’), ’Day, ”the” Ddth ”of” Month,
YYYY’) AS REVIEW
FROM [Link];

Question: 3. Display the last name, hire date, and day of the week on which the employee started. Label
the column DAY. Order the results by the day of the week, starting with Monday.

Answer:
SELECT LAST NAME,
HIRE DATE,
TO CHAR(hire date, ’DAY’) AS day
FROM [Link]
ORDER BY TO CHAR(hire date, ’D’);

Question: 4. Create a query that displays the employees’ last names and commission amounts. If an em-
ployee does not earn commission, show ”No Commission.” Label the column COMM.

Answer:
SELECT LAST NAME,
CASE
WHEN COMMISSION PCT IS NULL THEN ’No Commission’

10
ELSE TO CHAR(COMMISSION PCT)
END AS COMM
FROM [Link];

Question: 5. Using the DECODE function, write a query that displays the grade of all employees based on
the value of the column JOB ID, using the following data:

Answer:
SELECT JOB ID,
DECODE(JOB ID,
’AD PRES’, ’A’,
’ST MAN’, ’B’,
’IT PROG’, ’C’,
’SA REP’, ’D’,
’ST CLERK’, ’E’,
’0’) AS GRADE
FROM [Link];

Question: 6. Rewrite the statement in the preceding exercise using the CASE syntax.

Answer:
SELECT JOB ID,
CASE
WHEN JOB ID = ’AD PRES’ THEN ’A’
WHEN JOB ID = ’ST MAN’ THEN ’B’
WHEN JOB ID = ’IT PROG’ THEN ’C’
WHEN JOB ID = ’SA REP’ THEN ’D’
WHEN JOB ID = ’ST CLERK’ THEN ’E’
ELSE ’0’
END AS GRADE
FROM [Link];

11
 Chapter-5

Question: 1. Group functions work across many rows to produce one result per group.

Answer: True

Question: 2. Group functions include nulls in calculations.

Answer: False

Question: 3. The WHERE clause restricts rows before inclusion in a group calculation.

Answer: True

Question: 4. Find the highest, lowest, sum, and average salary of all employees. Label the columns as
Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.
Save your SQL statement as lab 05 [Link]. Run the query.

Answer:
SELECT ROUND(MAX(salary)) AS ”Maximum”,
ROUND(MIN(salary)) AS ”Minimum”,
ROUND(SUM(salary)) AS ”Sum”,
ROUND(AVG(salary)) AS ”Average”
FROM [Link];

Question: 5. Modify the query in lab 05 [Link] to display the minimum, maximum, sum, and average
salary for each job type. Resave lab 05 [Link] as lab 05 [Link]. Run the statement in lab 05 [Link].

Answer:
SELECT job id, ROUND(MAX(salary)) AS ”Maximum”,
ROUND(MIN(salary)) AS ”Minimum”,
ROUND(SUM(salary)) AS ”Sum”,
ROUND(AVG(salary)) AS ”Average”
FROM [Link]
GROUP BY job id;

12
Question: 6. Write a query to display the number of people with the same job. Generalize the query so that
the user in the HR department is prompted for a job title. Save the script to a file named lab 05 [Link]. Run
the query. Enter IT PROG when prompted.

Answer:
SELECT job id, COUNT(*)
FROM [Link]
WHERE job id = ’&job title’
GROUP BY job id;

Question: 7. Determine the number of managers without listing them. Label the column as Number of
Managers. Hint: Use the MANAGER ID column to determine the number of managers.

Answer:
SELECT COUNT(DISTINCT manager id) AS ”Number of Managers”
FROM [Link];

Question: 8. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.

Answer:
SELECT MAX(salary) - MIN(salary) AS DIFFERENCE
FROM [Link];

Question: 9. Create a report to display the manager number and the salary of the lowest-paid employee
for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum
salary is $6,000 or less. Sort the output in descending order of salary.

Answer:
SELECT manager id, MIN(salary)
FROM [Link]
WHERE manager id IS NOT NULL
GROUP BY manager id
HAVING MIN(salary) ¿ 6000
ORDER BY MIN(salary) DESC;

13