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.

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

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

Examples

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 (;)

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.