Manage Employee Data in PostgreSQL

Introduction

In this challenge, you'll step into the role of a database administrator and manage employee data within a PostgreSQL database. The task involves using PostgreSQL commands to update employee records in the employees table to reflect changes in the organization.

You'll need to connect to the PostgreSQL database using the command line interface and execute SQL commands to insert a new employee, Diana Prince, into the 'Security' department, and update Bob Johnson's department to 'Management'. The initial table structure and some sample data will be provided for you to work with.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") subgraph Lab Skills postgresql/row_add -.-> lab-551130{{"Manage Employee Data in PostgreSQL"}} postgresql/row_edit -.-> lab-551130{{"Manage Employee Data in PostgreSQL"}} end

Manage Employee Data

You're a database admin. A new employee joined, and another moved departments. Update the employee database accordingly using PostgreSQL commands.

Tasks

Insert a new employee, Diana Prince, into the employees table with the department 'Security'.
Update Bob Johnson's department to 'Management'.

Requirements

Connect to PostgreSQL using: sudo -u postgres psql
Use the INSERT INTO command to add Diana Prince to the employees table.
Use the UPDATE command to change Bob Johnson's department to 'Management'.
The initial table structure and data are already set up for you.

Examples

Example result of querying employees table

After completing the tasks, querying the employees table should produce the following results:

 id |      name      | department
----+----------------+------------
  1 | Alice Smith    | Sales
  2 | Bob Johnson | Management
  3 | Diana Prince   | Security
(3 rows)

Hints

Use \dt to list available tables
Use SELECT * FROM employees; to view current data
Remember to specify the table name and column names when inserting data
Use the WHERE clause in the UPDATE statement to specify which record to update
Double-check the spelling of column names and values to avoid errors
End each SQL command with a semicolon (;)

✨ Check Solution and Practice

Summary

In this challenge, you acted as a database administrator, managing employee data within a PostgreSQL database. The task involved using PostgreSQL command line interface to execute SQL commands for inserting a new employee, Diana Prince, into the employees table with the 'Security' department, and updating an existing employee, Bob Johnson, to the 'Management' department.

The challenge reinforced the use of INSERT INTO and UPDATE SQL commands for data manipulation. It also emphasized the importance of specifying table and column names correctly when inserting data and using the WHERE clause to target specific rows for updates. Finally, it demonstrated how to interact with PostgreSQL directly through the command line interface to manage database records effectively.