PostgreSQL String() Function: Find the details of employees who contain eight or more characters in their first name
3. Write a query to find the details of those employees who contain eight or more characters in their first name.
Sample Solution:
Code:
-- This SQL query retrieves all columns from the employees table where the length of the first_name is 8 characters or more.
SELECT * -- Selects all columns from the employees table
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
WHERE LENGTH(first_name) >= 8; -- Filters the rows to include only those where the length of the first_name is 8 characters or more
Explanation:
- This SQL query retrieves data from the employees table.
- The SELECT * statement selects all columns from the employees table.
- The FROM clause specifies the table from which to retrieve the data, which is the employees table.
- The WHERE clause filters the rows to include only those where the length of the first_name column is 8 characters or more.
- The LENGTH(first_name) >= 8 condition checks if the length of the first_name column is greater than or equal to 8 characters.
- The result set will contain all columns for rows where the length of the first_name is 8 characters or more.
Sample table: employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | .......... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Output:
pg_exercises=# SELECT *
pg_exercises-# FROM employees
pg_exercises-# WHERE LENGTH(first_name) >= 8;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id |
-------------+-------------+-----------+---------------+--------------------+------------+------------+----------+----------------+------------+---------------|
103 | Alexander | Hunold | not available | 590.423.4567 | 1987-06-20 | IT_PROG | 9030.00 | 0.10 | 102 | 60 |
115 | Alexander | Khoo | not available | 515.127.4562 | 1987-07-02 | PU_CLERK | 3130.00 | 0.10 | 114 | 30 |
176 | Jonathon | Taylor | not available | 011.44.1644.429265 | 1987-09-01 | SA_REP | 8630.00 | 0.10 | 149 | 80 |
178 | Kimberely | Grant | not available | 011.44.1644.429263 | 1987-09-03 | SA_REP | 7030.00 | 0.10 | 149 | 0 |
153 | Christopher | Olsen | not available | 011.44.1344.498718 | 1987-08-09 | SA_REP | 8030.00 | 0.10 | 145 | 80 |
189 | Jennifer | Dilly | not available | 650.505.2876 | 1987-09-14 | SH_CLERK | 3630.00 | 0.10 | 122 | 50 |
112 | Jose Manuel | Urman | not available | 515.123.4469 | 1987-06-29 | FI_ACCOUNT | 7830.00 | 0.10 | 108 | 100 |
169 | Harrison | Bloom | not available | 011.44.1343.829268 | 1987-08-25 | SA_REP | 10030.00 | 0.10 | 148 | 80 |
172 | Elizabeth | Bates | not available | 011.44.1343.529268 | 1987-08-28 | SA_REP | 7330.00 | 0.10 | 148 | 80 |
200 | Jennifer | Whalen | not available | 515.123.4444 | 1987-09-25 | AD_ASST | 4400.00 | 0.00 | 101 | 10 |
Go to:
PREV : Write a query to update the phone_number column with '999' where the substring '124' found in that column.
NEXT : Write a query to fill the maximum and minimum salary with leading asterisks whether these two columns does not contain a seven digit number.
Practice Online
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?