Maharashtra Board Class 11 Information Technology Skill Oriented Practicals Chapter 6 DBMS PostgreSQL PDF Download

DBMS (PostgreSQL)

Create A Database In PostgreSQL

To create a database in PostgreSQL, the create database statement is used.

Syntax:

postgresql=#create database databasename ;

Example:

postgresql> create database college ;

Teacher's Note

A database is like a big cupboard where you keep all your files. PostgreSQL helps you make these cupboards. Just like your school keeps student records in a cupboard, PostgreSQL keeps data in a database.

Exam Trick

Remember: CREATE DATABASE is the command to make a new database. Think of it as making a new notebook to write in.

Points to Remember

PostgreSQL is a tool to store data.
CREATE DATABASE command makes a new database.
You write the database name after the command.
Always put a semicolon at the end.
Database is like a big storage box for all information.

To View Databases

To view databases, the \l command is used.

Syntax:

postgresql=#/l databases;

Example output:

Teacher's Note

The \l command shows you all the databases that are already made. It is like looking at the list of all notebooks in your bag.

Exam Trick

Remember: \l means list. So \l command will show you a list of all databases, just like \d shows list of tables.

Points to Remember

\l command shows all databases.
Each database has a name and owner.
postgres is the default database.
UTF8 is the encoding type used.
en_IN means English in India.

To Connect Database

To connect to a database, the \c command is used.

Syntax:

postgresql=#\c;

Example:

\c college;

postgres=# \c college;

You are now connected to database "college" as user "postgres".

college=#

Teacher's Note

The \c command helps you go inside a database, like opening one notebook from your bag. When you connect to a database, you can then work with the data inside it.

Exam Trick

Remember: \c means connect. \c college will open the college database for you to use.

Points to Remember

\c command connects you to a database.
After \c, write the database name.
The prompt changes to show you are connected.
college=# means you are now inside the college database.
Only then can you create tables and add data.

To Create Table

To create a table in a database, the Create table command is used.

Syntax:

databasename = # create table tablename (fieldname Datatype, fieldname Datatype);

Example:

college=# CREATE TABLE XI (Roll_no integer, Student_name text);

CREATE TABLE

college=#

Teacher's Note

A table is like a notebook with columns. In your school register, the columns are Roll Number, Name, and Marks. Similarly, in PostgreSQL, you make columns with names and data types.

Exam Trick

Remember: CREATE TABLE makes a table with columns. Always give the field name and data type together, like Roll_no integer.

Points to Remember

CREATE TABLE command makes a new table.
A table has many columns with different names.
Each column has a datatype like integer or text.
integer is for numbers.
text is for words and letters.

To Insert Data In Table

To insert data in a table, the insert into command is used.

Syntax:

databsename=# insert into tablename (field name)values(data1,'data1')

Example:

college=# INSERT INTO XI (Roll_no,Student_name) VALUES(101,'Sachin');

INSERT 0 1

college=#

Teacher's Note

INSERT INTO is like writing a new line in your notebook. When your teacher writes your name and roll number in the class register, that is like inserting data into a table.

Exam Trick

Remember: Put field names in brackets first, then put the values. Use single quotes for text data like 'Sachin'.

Points to Remember

INSERT INTO adds new data to a table.
You must write the field names first.
Then write the values in the same order.
Text data must be in single quotes.
Number data does not need quotes.

To View Inserted Data

To view inserted data, the select * from command is used.

Syntax:

database name=#select * from table name.

Example:

college=# SELECT * FROM XI;

roll_no | student_name

--------|---------------

101 | Sachin

(1 row)

Teacher's Note

SELECT * FROM is like opening your register and reading all the data. The * means show me all columns. It is the most common command you will use.

Exam Trick

Remember: SELECT * means show all data. SELECT is like reading. * means everything. FROM tells which table to read.

Points to Remember

SELECT * FROM shows all data in a table.
The * symbol means all columns.
You see all rows that were added.
The output shows data in a table format.
This command does not change any data.

To Update Table

To update a table, the UPDATE command is used.

Syntax:

databasename=# update table_name SET column_name=Value WHERE Reference_Column_name=Value

Example:

college=# UPDATE XI SET Roll_no = '1001' WHERE Student_name = 'Sachin';

UPDATE 1

college=#

Teacher's Note

UPDATE is used when you need to fix or change data. Like when a student's roll number is wrong in the register, the teacher uses an eraser to fix it. UPDATE does the same thing in the database.

Exam Trick

Remember: UPDATE changes data. SET tells what to change. WHERE tells which row to change. Always use WHERE, or all rows will change.

Points to Remember

UPDATE command changes data in a table.
SET tells which column to change.
WHERE tells which row to change.
Always use WHERE to pick the right row.
Without WHERE, all rows will be changed.

To Add Primary Key

To add a primary key to an already created table, you can use the following command or you can create a primary key during table creation.

Syntax:

ALTER TABLE tablename ADD PRIMARY KEY (column_name);

Example:

college=# ALTER TABLE XI ADD PRIMARY KEY (Roll_no);

ALTER TABLE

college=#

Teacher's Note

A primary key is like a student ID card number. No two students have the same ID number. Similarly, a primary key makes sure no two rows have the same value in that column.

Exam Trick

Remember: PRIMARY KEY = no two same values. It is like a unique name tag. Each student must have one, and no two can be the same.

Points to Remember

PRIMARY KEY is a special column that has unique values.
No two rows can have the same primary key value.
Each table should have one primary key.
PRIMARY KEY helps find data quickly.
ALTER TABLE is used to change a table after making it.

To Add Foreign Key

To add a foreign key while creating a table, you can use the following command or you can create a foreign key during table creation.

Syntax:

ALTER TABLE table_name ADD FOREIGN KEY (current_column_name) REFERENCES refered_table_name (referedtable_primarycolumn_name);

Example:

college=# CREATE TABLE Marks (record_no integer PRIMARY KEY, total_marks integer,result text,roll_no integer, FOREIGN KEY (roll_no) REFERENCES XI(Roll_no));

CREATE TABLE

college=#

One To One Relationship

Lets see the result of both table 'XI' and 'Marks' with one-to-one relationship.

college=# SELECT XI.Roll_no,XI.student_name,Marks.total_marks,Marks.result FROM XI,Marks where XI.Roll_no=Marks.roll_no;

roll_no | student_name | total_marks | result

--------|--------------|-------------|--------

1001 | Sachin | 230 | PASS

(1 row)

Teacher's Note

A foreign key connects two tables together. If the XI table has students and the Marks table has their marks, the foreign key connects them so you know which marks belong to which student.

Exam Trick

Remember: FOREIGN KEY = link between tables. REFERENCES means it points to another table, like how your address points to your house.

Points to Remember

FOREIGN KEY connects two tables together.
It must point to a PRIMARY KEY in another table.
One-to-one means one row in first table matches one row in second table.
REFERENCES tells which table and column to connect to.
Foreign keys help keep data organized and connected.

Do You Know?

\c Connect to database

\l List all the databases

\dt List all the tables from database

\d To view structure of table

This is a preview of the first 3 pages. To get the complete book, click below.