In this hands-on project, you create, query, and manage a database using PostgreSQL. 1....

In this hands-on project, you create, query, and manage a database using PostgreSQL.

1. Switch to a command-line terminal (tty2) by pressing Ctrl+Alt+F2 and log in to the terminal using the user name of root and the password of secret.

2. At the command prompt, type rpm –qi postgresql and press Enter. Is the PostgreSQL server installed?

3. At the command prompt, type passwd postgres and press Enter. Type a password of secret and press Enter at both prompts to set a password of secret for the postgres user account.

4. At the command prompt, type service postgresql initdb and press Enter to initialize the PostgreSQL system databases.

5. At the command prompt, type service postgresql start and press Enter to start the PostgreSQL server.6. Switch to a different command-line terminal (tty3) by pressing Ctrl+Alt+F3 and log in to the terminal using the user name of postgres and the password of secret.

7. At the command prompt, type createdb sales and press Enter.

8. At the command prompt, type psql sales and press Enter to start the PostgreSQL utility.

9. At the sales=# prompt, type \l and press Enter to view the databases on your PostgreSQL server. The postgres database stores all information used internally by the PostgreSQL server, and the template databases are used when creating new databases. Note that your sales database is listed and uses the UTF-8 character set for

information.

10. At the sales=# prompt, type CREATE TABLE customer (Name char(20),Address char(40),Balance char(10)); and press Enter to create a customer table that has three fields (Name, Address, Balance).

11. At the sales=# prompt, type \d and press Enter to view the tables within your database. Is the customer database listed?

12. At the sales=# prompt, type \d customer and press Enter to view the fields within the customer table. How many characters are allowed in each of the three fields?

13. At the sales=# prompt, type INSERT INTO customer VALUES (‘Lily Bopeep’,‘123 Rutherford Lane’,‘526.80’); and press Enter to add a record to your table.

14. At the sales=# prompt, type INSERT INTO customer VALUES (‘Harvey Lipshitz’, ‘51 King Street’,‘122.19’); and press Enter to add a record to your table.

15. At the sales=# prompt, type INSERT INTO customer VALUES (‘John Escobar’,‘14-6919 Franklin Drive’,‘709.66’); and press Enter to add a record to your table.

16. At the sales=# prompt, type SELECT * from customer; and press Enter to view all records within your table.

17. At the sales=# prompt, type SELECT * from customter ORDER BY Balance DESC; and press Enter to view all records within your table in descending order by balance.

18. At the sales=# prompt, type SELECT * from customer WHERE Name = ‘Harvey Lipshitz’; and press Enter to view the record for Harvey Lipshitz.

19. At the sales=# prompt, type CREATE USER bob WITH PASSWORD ‘supersecret’; and press Enter to create a user account within PostgreSQL that can access the customer database.

20. At the sales=# prompt, type GRANT ALL PRIVILEGES ON customer TO bob and press Enter to grant SELECT, UPDATE, DELETE, and INSERT permission on the customer table to the bob user.

21. At the sales=# prompt, type \q and press Enter to quit the PostgreSQL utility.

22. Type exit and press Enter to log out of your shell.