Week 10 Lab Notes

1. Start your VM from

https://labs.azure.com/

VM password : CompSys2025!

1. Start a Windows command console or powershell

2. Connect to PostgreSQL with

psql -U postgres

The password : CompSys2025!

1. Create a database

CREATE DATABASE your_db_name;

In your pqAdmin, you can also see the new created database

1. Connect to your database

1. Create an example Employee table

CREATE TABLE employee (
 ID char(5),
 name varchar(20) not null,
 dept_name  varchar(20),
 salary numeric(8,2),
 primary key (ID)
);
                             

You can also see the table in pgAdmin

1. Insert data into your table

INSERT INTO employee (id, name, dept_name, salary) 
VALUES ('P0001', ’Richard Robinson', 'Marketing', 60000.0);

Check if the data is inserted

SELECT * FROM employee; 

Try to insert more rows

INSERT INTO employee (id, name, dept_name, salary) values ('P0002', 'Mary Stuart', 'Human Resource', 40000.0);
INSERT INTO employee (id, name, dept_name, salary) values ('P0003', 'Henry Tudor', 'IT', 50000.0);
INSERT 0 1
INSERT INTO employee (id, name, dept_name, salary) values ('P0004', 'William Wordsworth', 'Public Relationship', 45000.0);
INSERT 0 1
INSERT INTO employee (id, name, dept_name, salary) values ('P0005', 'Ada Byron', 'IT', 65000.0);
INSERT 0 1

1. Query

Try to design your select statements, e.g.

SELECT * FROM employee WHERE salary BETWEEN 40000 AND 60000;

1. Update

e.g. raise Mary's salary with

UPDATE employee SET salary=52000 WHERE ID='P0002';

1. Delete a table

e.g. if Henry Tudor leaves

DELETE FROM employee WHERE name='Henry Tudor';

1. Delete a database

DROP your_database_name;

1. How to write a foreign key

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);