PostgreSQL String - Exercises, Practice, Solution

Last update on August 19 2022 21:50:56 (UTC/GMT +8 hours)

PostgreSQL string [14 exercises with solution]

1. Write a query to get the job_id and related employee's id.
Partial output of the query :

Go to editor

job_id	Employees ID
AC_ACCOUNT	206
AC_MGR	205
AD_ASST	200
AD_PRES	100
AD_VP	101 ,102
FI_ACCOUNT	110 ,113 ,111 ,109 ,112

Sample table : employees

Click me to see the solution of Exercise No.- 1. Click me again to collapse.

SELECT job_id, ARRAY_AGG(employee_id) 
FROM employees 
GROUP BY job_id;

2. Write a query to update the portion of the phone_number in the employees table, within the phone number the substring '124' will be replaced by '999'.

Go to editor

Sample table : employees

Click me to see the solution of Exercise No.- 2. Click me again to collapse.

UPDATE employees 
SET phone_number = REPLACE(phone_number, '124', '999') 
WHERE phone_number LIKE '%124%';

3. Write a query to get the details of the employees where the length of the first name greater than or equal to 8.

Go to editor

Sample table : employees

SELECT * 
FROM employees  
WHERE LENGTH(first_name) >= 8;

4. Write a query to display leading '*' before maximum and minimum salary.

Go to editor

Sample table : jobs

SELECT job_id,  LPAD(trim(to_char(max_salary,'9999999')),7,'*') "Max Salary" ,
LPAD(trim(to_char(min_salary,'9999999')),7,'*') "Min Salary" 
FROM jobs;

5. Write a query to append '@example.com' to email field.

Go to editor

Sample table : employees

Sample Output :

  EMAIL
  --------------------
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  - - - -  - - - - - - -
  - - - -  - - - - - - -

UPDATE employees 
SET email = CONCAT(email, '@example.com');

6. Write a query to get the employee id, first name and hire month.

Go to editor

Sample table : employees

SELECT employee_id, first_name, hire_date, 
SUBSTR(TO_CHAR(hire_date,'yyyy MM dd'), 6, 2) AS hire_month 
FROM employees;

7. Write a query to get the employee id, email id (discard the last three characters).

Go to editor

Sample table : employees

SELECT employee_id, REVERSE(SUBSTR(REVERSE(email), 4)) as Email_ID 
FROM employees;

8. Write a query to find all employees where first names are in upper case.

Go to editor

Sample table : employees

SELECT * 
FROM employees 
WHERE first_name = UPPER(first_name);

9. Write a query to extract the last 4 character of phone numbers.

Go to editor

Sample table : employees

SELECT RIGHT(phone_number, 4) as "Ph.No." 
FROM employees;

10. Write a query to get the locations that have minimum street length.

Go to editor

Sample table : locations

SELECT * 
FROM locations 
WHERE LENGTH(street_address)<=(
SELECT  MIN(LENGTH(street_address)) 
FROM locations);

11. Write a query to display the first word in job title.

Go to editor

Sample table : jobs

SELECT job_title,  SUBSTR(job_title,1, POSITION(' ' IN job_title)) 
FROM jobs;

12. Write a query to display the length of first name for employees where last name contain character 'c' after 2nd position.

Go to editor

Sample table : employees

SELECT first_name, last_name 
FROM employees 
WHERE POSITION('C' IN last_name) > 2;

13. Write a query that displays the first name and the length of the first name for all employees whose name starts with the letters 'A', 'J' or 'M'. Give each column an appropriate label. Sort the results by the employees' first names.

Go to editor

Sample table : employees

SELECT first_name "Name",
LENGTH(first_name) "Length"
FROM employees
WHERE first_name LIKE 'J%'
OR first_name LIKE 'M%'
OR first_name LIKE 'A%'
ORDER BY first_name ;

14. Write a query to display the first name and salary for all employees. Format the salary to be 10 characters long, left-padded with the $ symbol. Label the column SALARY.

Go to editor

Sample table : employees

SELECT first_name,
LPAD(TRIM(TO_CHAR(salary,'9999999')), 7, '$') SALARY 
FROM employees;

Practice Online

Go To Top

... More