w3resource

PostgreSQL String() Function - Exercises, Practice, Solution

PostgreSQL String() Function [14 exercises with solution]

1. Write a query to get the job_id and the ID(s) for those employees who is working in that post.

Partial output of the query:

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

2. Write a query to update the phone_number column with '999' where the substring '124' found in that column.

Sample table: employees


Click me to see the solution

3. Write a query to find the details of those employees who contain eight or more characters in their first name.

Sample table: employees


Click me to see the solution

4. Write a query to fill the maximum and minimum salary with leading asterisks whether these two columns does not contain a seven digit number.

Sample table: jobs


Click me to see the solution

5. Write a query to join the text '@example.com' with the email column.

Sample Output :

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

Sample table: employees


Click me to see the solution

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

Sample table: employees


Click me to see the solution

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

Sample table: employees


Click me to see the solution

8. Write a query to find all the employees which first name contains all the uppercase letter.

Sample table: employees


Click me to see the solution

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

Sample table: employees


Click me to see the solution

10. Write a query to get the information about those locations which contain the characters in its street address is on and below the minimum character length of street_address.

Sample table: locations


Click me to see the solution

11. Write a query to display the first word in the job title if the job title contains more than one words.

Sample table: jobs


Click me to see the solution

12. Write a query to display the first name, last name for the employees, which contain a letter 'C' to their last name at 3rd or greater position.

Sample table: employees


Click me to see the solution

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.

Sample table: employees


Click me to see the solution

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

Sample table: employees


Click me to see the solution

 

... More

Structure of 'hr' database :

hr database



Follow us on Facebook and Twitter for latest update.