PostgreSQL commands you will use in your first week

Most DBMS lab work in PostgreSQL uses a small set of commands again and again. Learn these first and the rest of SQL becomes much easier to pick up.

Inside psql: look before you type

Commands that start with a backslash belong to psql itself, not to SQL. They do not need a semicolon.

• \l lists the databases on the server.
• \c labdb connects to the database called labdb.
• \dt lists the tables in the current database.
• \d students shows the columns, types and constraints of one table.
• \q quits psql. \? shows every backslash command.

Create a table and add rows

Give every table a primary key, and write the column types on purpose rather than making everything text.

CREATE TABLE students (
  roll_no  int PRIMARY KEY,
  name     text NOT NULL,
  branch   text NOT NULL,
  cgpa     numeric(4,2) CHECK (cgpa BETWEEN 0 AND 10)
);
INSERT INTO students VALUES
  (101, 'Asha', 'CSE', 8.70),
  (102, 'Ravi', 'ECE', 7.95),
  (103, 'Meena', 'CSE', 9.10);
SELECT name, cgpa FROM students WHERE branch = 'CSE' ORDER BY cgpa DESC;

Meena|9.10
Asha|8.70

Change and remove rows safely

UPDATE and DELETE change every row unless you add a WHERE clause. Run the same WHERE with SELECT first to see what you are about to touch.

CREATE TABLE marks (roll_no int, subject text, score int);
INSERT INTO marks VALUES (101, 'DBMS', 78), (102, 'DBMS', 64), (103, 'DBMS', 91);
SELECT roll_no FROM marks WHERE score < 70;
UPDATE marks SET score = score + 5 WHERE score < 70;
DELETE FROM marks WHERE roll_no = 103;
SELECT roll_no, score FROM marks ORDER BY roll_no;

102
101|78
102|69

Habits that save marks in the lab

• End every SQL statement with a semicolon; psql waits until it sees one.
• Strings use single quotes ('CSE'). Double quotes are for column or table names.
• Use \d tablename after CREATE TABLE to check you made what you meant.
• Practise with interview-style questions in UniCircle interview prep: databases and SQL.