w3resource

SQL SORTING and FILTERING on HR Database: Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name

SQL SORTING and FILTERING on HR Database: Exercise-19 with Solution

19. From the following table, write a SQL query to find those employees whose first name contains a character 's' in 3rd position. Return first name, last name and department id.

Sample table : employees


Sample Solution:

SELECT first_name,last_name, department_id
 FROM employees
  WHERE first_name LIKE '__s%';

Sample Output:

 first_name  | last_name | department_id
-------------+-----------+---------------
 Jose Manuel | Urman     |           100
 Jason       | Mallin    |            50
 Joshua      | Patel     |            50
 Lisa        | Ozer      |            80
 Susan       | Mavris    |            40
(5 rows)

Relational Algebra Expression:

Relational Algebra Expression: Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name.

Relational Algebra Tree:

Relational Algebra Tree: Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name - Duration

Rows:

Query visualization of Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name - Rows

Cost:

Query visualization of Display the first and last name, department number for those employees who holds a letter s as a 3rd character in their first name - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who earn above 11000 or the seventh character in their phone number is 3. Sort the result-set in descending order by first name. Return full name (first name and last name), hire date, commission percentage, email, and telephone separated by '-', and salary.
Next: From the following table, write a SQL query to find those employees who are working in the departments, which are not included in the department number 50 or 30 or 80. Return employee_id, first_name, job_id, department_id.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

MySQL export schema without data

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

Ref: https://bit.ly/3xzB9dS