PostgreSQL Objectives Solution

Install PostgreSQL server on a Linux host. Write SQL commands to query the database. Please pair program today!

Installation

Step 1. Run the following command from the terminal to install.

Sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

Step 2. Check ​postgresql.conf​is created. To check, type following command in your terminal

​ls /etc/postgresql/9.5/main

Step 3. Check postgres service is started or not. Type following command in your terminal

service postgresql status

If the status is not ​active, ​then type following command in your terminal

service postgresql start

To stop the service, type the following command

service postgresql stop

Step 4. Type following command to run your postgresql using postgres as a user.

sudo su postgres

Step 5. Open postgres shell, type following command

psql

Preprocessing:

After installation, by default postgres has one user ​‘postgres’.
Type following command to see all users:​\du

        1. Change the password:

ALTER USER postgres WITH PASSWORD ‘​your_new_password​’;

    2. Create a new user, if you want to create

CREATE USER ​your_user_name​WITH PASSWORD ‘​your_password​’;

    3. Give permission to your newly created user, type following command by replacing the bold words.

ALTER USER ​your_user_name​WITH SUPERUSER;

    4. If you want to drop a user, type DROP USER ​your_user_name

Note: Bold words are replaceable based on your choice. Please read everything before writing in your terminal.


Exercise 1. Make sure the server is working

sudo netstat -tap | grep postgresql

    2. Access PostgreSQL via the command line(where -U is for user) If you want to run using postgres as a username:

psql -U postgres -h localhost

Otherwise, you can use your newly created username as well by replacing postgres with your username.

Note:​It will ask a password, please type your password. It will be a hidden field. So, keep on typing. Your password will not be visible. So, keep on typing.

    3. Now inside the Postgresql command line, run the following: create database lab6;
    4. Now tell it to use that database:

\c lab6;

    5. Create a few tables to work with. It is easiest to add them to a file then load them from the SQL CLI. Using vim, type the following SQL code(red colored) into a file named db.sql.

        1. Store

create table if not exists store( id serial, sname varchar(40) not null, qty integer not null, price float not null, primary key (id) );

insert into store (sname, qty, price) values ('apple', 10, 1), ('pear', 5, 2), ('banana', 10, 1.5), ('lemon', 100, 0.1), ('orange', 50, 0.2);

2. Course

create table if not exists course ( id serial, cname varchar(4) not null, department_id integer not null, primary key (id) );

insert into course (cname, department_id) values ('111', 1), ('112', 1), ('250', 1), ('231', 1), ('111', 2), ('250', 3), ('111', 4);

3. Department

create table if not exists department ( id serial, name varchar(3) not null, primary key (id) ) ; insert into department (name) values ('CSC'), ('MTH'), ('EGR'), ('CHM');

4. Enrollment

create table if not exists enrollment ( id serial, count integer not null, PRIMARY KEY (id) ) ; insert into enrollment (count) values (40), (15), (10), (12), (60), (14), (200);


Create the tables and add the content from the data file:

show tables; {shows the current list of tables in the database} source db.sql {to execute the sql script on your database}

Run following command in your terminal.

Step 1. Open a new terminal

Step 2. Type​sudo su postgres​and enter password. Step 3. Type following command ​psql lab6 < db.sq​l

Note: enter the path of db.sql if you have saved it somewhere else.


Questions

Write out the query to do the following (test inside your VM). Create another text file with all your queries in it(from 1-14), and use the file extension .sql.

1. List all the rows in the store - sorted alphabetically by store name    .

    2. Then list only the first 3 rows in the store – sorted alphabetically.

    3. Then list the last 3 rows in the store – sorted alphabetically.

    4. List only the items name that are more than $1 per unit price from store.

    5. List all the items with their extended price as ‘extended_price’ (quantity * price)

    6. List the total cost of all the items in the store

    7. List all the CS classes.

    8. What is the total enrollment count over all the classes?

    9. How many different departments are there?

    10. Update department id to 3 for course name 112 in course table.

    11. Do the following alteration sequentially.

        a. Add a new column in enrollment table, ​drop_count​,as a text field using TEXT.

        b. Modify the datatype of ​drop_count​from TEXT to VARCHAR.

        c. Modify the datatype of ​drop_count ​from VARCHAR to INTEGER.
Hint​: There would be three commands. One for adding a column and other two for alterations.
Learn how to use ‘Alter’ Command.

    12. Update value in ​drop_count ​by taking 20% of count column from enrollment table from its respective row. Also, print the result.

Note​: You need to write the sql queries as well as the output for this question in your writeup.

Hint: Learn​ how to use inner queries.

    13. List the name of the CS classes so that they are output as “CSC111”, “CSC112”, etc... (in other words, concatenate department with class number.)

    14. List all the information in the database, where each class appears on one line, along with its department, and its enrollment and all the information are sorted based on department name.

Note​: You need to write the sql queries as well as the output for this question in your writeup.

    15. Do the following operations.

        a. Drop column ​drop_count ​from enrollment table
        b. Empty the entire enrollment table.

        c. Delete the enrollment table.

        d. Create a ​new_enrollment​table
Column Name
Datatype
Modifiers



id
integer
Primary key, Auto


Increment



department_name
varchar
Not null



count
integer
Not null



drop_count
integer






    e. Insert the following details in the table and find the department_name which has the highest count. Consider only the count column. Don’t consider drop_count in calculating the highest count.




id
department_name
count
drop_count




1
CSC
100
20




2
CHM
120
5




3
MTH
90
3




4
EGR
122
12




5
MTH
68
6




7
CSC
100
3




8
CHM
30
1





Hint​: Learn how to do auto-sequencing on a auto column. Here, you don’t have to create a new sequence for auto-sequencing. There are three kinds of auto-sequencing on a number. Learn how to use those.

Credit​: To get credit for this lab exercise, submit sql file. Make sure to include your partner’s name in the submission.

Guide:

\q​​--to quit
\c ​database​​--to connect to a different database
\d​​--to describe (list) what tables are in the database \d ​table​--to describe attributes of a table
\i ​filename​​--to run (include) a script file of SQL commands

\e ​filename​​--to edit an existing script and execute the commands \e​​--to edit the last SQL command and execute upon exit

\w ​filename​​--to write the last SQL command to a file \?​​--show the list of \postgres commands \h​​--show the list of SQLcommands
\h ​command​​--show syntax on this SQL command

How to Install PostgreSQL Admin Tool.

Step 1. Go to Linux/Ubuntu Software Tool and type pgadmin. Install the software.




























Step 2. After the installation, open the pgadmin tool. On the top right corner, you will find one plug in icon.


Step 3: It will open a pop-up, fill these informations and click on OK button.













































Step 4: Now, you can see your schema and tables.