TalentJR apps
Textbook: Information Technology – Class 11 (Balbharati, official free PDF) · pages 113–134
Time to run real SQL. This skill set uses PostgreSQL, a free and powerful database server, through its psql shell: create, list, connect to and drop databases; create tables; insert, view and update rows; add primary and foreign keys; and link two tables one-to-one. The page works through all three practicals (hospital, school and employee–salary databases) with tested-style SQL, and also covers the book's appendix — installing Ubuntu, LibreOffice, PostgreSQL and GNUKhata.
PostgreSQL is a free, open-source relational database system. On Ubuntu it is installed with sudo apt-get update and sudo apt-get install postgresql postgresql-contrib; you then switch to the default postgres user (sudo -i -u postgres) and start the psql shell by typing psql. The prompt shows the current database, e.g. postgres=# or college=#.
Database commands: CREATE DATABASE name; makes a database, \l lists all databases, \c name connects to one, \dt lists its tables, \d table shows a table's structure, and DROP DATABASE name; deletes a database (you must first connect to a different one). Backslash commands are psql shortcuts and need no semicolon; SQL statements end with a semicolon.
Table commands: CREATE TABLE with column names and data types (integer, text, varchar, date, numeric); INSERT INTO … VALUES … adds rows; SELECT * FROM table; shows them; UPDATE … SET … WHERE … changes them; DROP TABLE name; deletes the table.
Keys and relationships: ALTER TABLE t ADD PRIMARY KEY (col); or PRIMARY KEY in the column definition; ALTER TABLE t ADD FOREIGN KEY (col) REFERENCES other(col); or FOREIGN KEY … REFERENCES … while creating. In the book's example, Marks.roll_no is both the primary key of Marks and a foreign key to XI.roll_no, so each student has at most one marks row — a one-to-one relationship — and a join shows both tables' data together.
Practicals: SOP 1 — hospital database with a patients table; SOP 2 — school database with a students table and 5 records; SOP 3 — split employee fields into Employee and Salary tables with primary keys, a foreign key and a one-to-one link. The appendix explains making an Ubuntu installer (DVD, Rufus, Etcher or dd), installing Ubuntu, LibreOffice, PostgreSQL and GNUKhata, plus minimum hardware.
Get a ✓ Verified tutor for “Skill Set 6 – DBMS (PostgreSQL)” — live online or at home. We aim to suggest up to 3 tutors within 24 hours.
Create a table book(id, title, price), add two books, raise the price of book 2 by ₹20 and show the result.
Answer: CREATE TABLE book (id integer PRIMARY KEY, title varchar(60), price numeric(7,2)); INSERT INTO book VALUES (1, 'Wings of Fire', 299.00), (2, 'Ignited Minds', 250.00); UPDATE book SET price = price + 20 WHERE id = 2; SELECT * FROM book; — shows 1 | Wings of Fire | 299.00 and 2 | Ignited Minds | 270.00 (2 rows).
Why does this fail, and how do you fix it? INSERT INTO marks VALUES (2002, 210, 'PASS', 1); when xi has only roll number 1001.
Answer: PostgreSQL reports a foreign key violation: 2002 is not present in xi.roll_no. Fix: first INSERT INTO xi VALUES (2002, 'Kavya'); then insert the marks row — the parent row must exist before the child row.
| Command | What it does | Example |
|---|---|---|
| CREATE DATABASE | Makes a new database | CREATE DATABASE hospital; |
| \l | Lists all databases | \l |
| \c | Connects to a database | \c hospital |
| \dt / \d | Lists tables / shows a table's structure | \d patients |
| CREATE TABLE | Makes a table with typed columns | CREATE TABLE xi (roll_no integer, student_name text); |
| INSERT INTO | Adds rows | INSERT INTO xi VALUES (101, 'Sachin'); |
| SELECT | Shows rows | SELECT * FROM xi; |
| UPDATE … SET … WHERE | Changes rows that match | UPDATE xi SET roll_no = 1001 WHERE student_name = 'Sachin'; |
| ALTER TABLE … ADD PRIMARY KEY | Adds a primary key | ALTER TABLE xi ADD PRIMARY KEY (roll_no); |
| ALTER TABLE … ADD FOREIGN KEY | Links to another table's key | ALTER TABLE marks ADD FOREIGN KEY (roll_no) REFERENCES xi (roll_no); |
| DROP TABLE / DROP DATABASE | Deletes a table / a database | DROP TABLE marks; DROP DATABASE college; |
Solve each one in your notebook first. Then open the solution and compare every step.
1. SOP 1: Create a database hospital with a table of patients: Patient_ID, Patient_Name, Address, Room_number, Doctor's_name, giving each a suitable data type.
Answer: Sample: sudo -i -u postgres → psql → CREATE DATABASE hospital; → \c hospital → CREATE TABLE patients (patient_id integer PRIMARY KEY, patient_name varchar(50), address text, room_number integer, doctor_name varchar(50)); → \d patients (check structure) → INSERT INTO patients VALUES (1, 'Ramesh Pawar', 'Miraj Road, Sangli', 101, 'Dr. S. Joshi'), (2, 'Fatima Shaikh', 'Camp, Pune', 102, 'Dr. A. Rao'); → SELECT * FROM patients; shows 2 rows. (Doctor's_name from the book becomes doctor_name, because a quote mark is not allowed in a plain identifier.)
2. SOP 2: Create a database School-master with a students table: student_ID, student_name, Address, Phone_number, Date_of_Birth; use suitable data types and enter at least 5 records.
Answer: Sample: CREATE DATABASE "School-master"; → \c "School-master" (or simply use school_master). CREATE TABLE students (student_id integer PRIMARY KEY, student_name varchar(50), address text, phone_number char(10), date_of_birth date); INSERT INTO students VALUES (1, 'Aditi Shinde', 'Kothrud, Pune', '9876500001', '2009-03-14'), (2, 'Rahul Patil', 'Satpur, Nashik', '9876500002', '2008-11-02'), (3, 'Zoya Khan', 'Civil Lines, Nagpur', '9876500003', '2009-01-25'), (4, 'Omkar Jadhav', 'Rajarampuri, Kolhapur', '9876500004', '2008-07-30'), (5, 'Sneha Iyer', 'Vashi, Navi Mumbai', '9876500005', '2009-05-09'); SELECT * FROM students; → (5 rows).
3. SOP 3: Fields EmpId, EmpName, EmpDepartment, SalaryId, SalaryAmount, Bonus. Split them into Employee and Salary tables, define primary and foreign keys and create a one-to-one relationship.
Answer: Sample: CREATE TABLE employee (empid integer PRIMARY KEY, empname varchar(50), empdepartment varchar(30)); CREATE TABLE salary (salaryid integer PRIMARY KEY, empid integer UNIQUE NOT NULL, salaryamount numeric(10,2), bonus numeric(10,2), FOREIGN KEY (empid) REFERENCES employee (empid)); — Primary keys: employee.empid and salary.salaryid. Foreign key: salary.empid → employee.empid. UNIQUE on salary.empid makes it one-to-one (one salary record per employee). Test: INSERT INTO employee VALUES (1, 'Kiran Desai', 'Accounts'); INSERT INTO salary VALUES (501, 1, 35000, 3000); SELECT e.empname, s.salaryamount, s.bonus FROM employee e JOIN salary s ON e.empid = s.empid; → Kiran Desai | 35000.00 | 3000.00. A second salary row with empid 1 is rejected (unique violation).
4. Write the psql commands to: list databases, connect to college, list its tables, see the structure of xi, and quit.
Answer: \l → \c college → \dt → \d xi → \q.
5. Using table xi(roll_no, student_name): add a primary key, insert Sachin with roll 101, change his roll number to 1001, display the table and finally delete the table.
Answer: ALTER TABLE xi ADD PRIMARY KEY (roll_no); INSERT INTO xi (roll_no, student_name) VALUES (101, 'Sachin'); UPDATE xi SET roll_no = 1001 WHERE student_name = 'Sachin'; SELECT * FROM xi; → 1001 | Sachin (1 row). DROP TABLE xi; (If marks refers to xi, drop marks first, or PostgreSQL refuses.)
6. Appendix: list the steps to make a bootable Ubuntu USB with the dd command, and two reasons a USB installer is better than a DVD.
Answer: Steps: (1) Ctrl+Alt+T to open a terminal and plug in the USB (4 GB or more). (2) lsblk — identify the USB by its size, e.g. sdb (the hard disk is usually sda). (3) sudo dd bs=4M if=ubuntu.iso of=/dev/sdb status=progress — enter the password; double-check of= or you may wipe your hard disk. (4) sync, wait, then remove the drive. Reasons: USB reads and writes much faster, so installation is quicker; it can be formatted and reused (less e-waste), whereas a DVD is wasted by a single write error and soon becomes outdated.
Read the question, answer it in your head, then tap the card to check. 20 cards.
15 questions. Tap an option — you will see at once if it is right and why.
1. The PostgreSQL shell program is called…
Answer: B. Start it after switching to the postgres user.
2. Which meta-command lists all databases?
Answer: D. \dt lists tables.
3. Which meta-command connects to a database?
Answer: C. \c dbname
4. Which meta-command shows a table's structure?
Answer: D. \d tablename
5. After INSERT of one row, psql replies…
Answer: A. 1 row inserted.
6. Which statement shows every row of patients?
Answer: A. * means all columns.
7. UPDATE without a WHERE clause…
Answer: A. Always add WHERE.
8. Which is the correct way to delete a table?
Answer: A. TABLE is required (the book omits it).
9. A date is written in PostgreSQL as…
Answer: B. ISO format in single quotes.
10. Which keyword links a column to another table?
Answer: A. FOREIGN KEY (col) REFERENCES t (col).
11. To make a foreign-key link one-to-one, the foreign key column should also be…
Answer: B. Otherwise it is one-to-many.
12. Which database name needs double quotes?
Answer: B. Hyphens are not allowed in plain names.
13. Before DROP DATABASE college; you must…
Answer: C. You cannot drop the one you are in.
14. Which is the recommended way to make an Ubuntu installer in the book?
Answer: D. Faster and reusable.
15. After writing an ISO with dd, you should run…
Answer: A. It flushes the write buffer.
Get a ✓ Verified tutor for “Skill Set 6 – DBMS (PostgreSQL)” — live online or at home. We aim to suggest up to 3 tutors within 24 hours.
Done with the notes? Revise Skill Set 6 – DBMS (PostgreSQL) the fun way: match key terms, beat the 60-second lightning round and spot true or false facts. Every game uses only this lesson, so each round is real exam revision.
⭐ 0 XP⏱ About 40 minutes (plus lab time)
Get a ✓ Verified tutor for “Skill Set 6 – DBMS (PostgreSQL)” — live online or at home. We aim to suggest up to 3 tutors within 24 hours.
Sign in free to see the answers to the textbook exercise of Skill Set 6 – DBMS (PostgreSQL).
Sign in to see solutionsStuck on a question? Type it, 🎤 speak it or 📷 snap a photo of it and get simple, step-by-step help with “Skill Set 6 DBMS (PostgreSQL)” — important questions, a quick quiz, revision notes — in English, मराठी or हिंदी.
Sign in with Google to ask the AI tutor. It's free — 20 questions a day, one TalentJR account.
Sign in to askPrefer a real teacher? TalentJR teachers take live online classes anywhere in India. Tell us what you need and our team will call you with timings and fees.