Creating 2 tables of team and player:
create table team(team_id int, player_id int, joining_date date);
create table player(player_id int, player_name varchar(30), team_name varchar(20), country varchar(20));
Inserting values to the table:
insert into team values(1, 101, '09-02-2025'), (2,102, '10-02-2025'), (3, 103, '31-01-2025'), (4,104, '29-12-2024');
insert into player values(101, 'yashasvi', 'CSK', 'India'), (102, 'Joe Root', 'RR', 'England'), (103, 'Hardik', 'DD','Srilanka'), (104, 'Jos', 'RCB', 'South Africa'), (105, 'Rohit', 'MI', 'India');
After creating table:
team_id | player_id | joining_date
---------+-----------+--------------
1 | 101 | 2025-02-09
2 | 102 | 2025-02-10
3 | 103 | 2025-01-31
4 | 104 | 2024-12-29
(4 rows)
player_id | player_name | team_name | country
-----------+-------------+-----------+--------------
101 | yashasvi | CSK | India
102 | Joe Root | RR | England
103 | Hardik | DD | Srilanka
104 | Jos | RCB | South Africa
105 | Rohit | MI | India
(5 rows)
JOIN
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Types of Joins
Here are the different types of the Joins in PostgreSQL:
- INNER JOIN: Returns records that have matching values in both tables
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- FULL JOIN: Returns all records when there is a match in either left or right table.
Retrieving Team and Player Information with INNER JOIN
movie=# select team.team_id, player.player_name from team inner join player on team.player_id = player.player_id;
team_id | player_name
---------+-------------
1 | yashasvi
2 | Joe Root
3 | Hardik
4 | Jos
(4 rows)
Retrieving All Players with Their Teams Using RIGHT JOIN
movie=# select team.team_id, player.player_name from team right join player on team.player_id = player.player_id;
team_id | player_name
---------+-------------
1 | yashasvi
2 | Joe Root
3 | Hardik
4 | Jos
| Rohit
(5 rows)
Retrieving Teams and Associated Players Using LEFT JOIN with a Filter
movie=# select team.team_id, player.player_name from team left join player on team.player_id = player.player_id where team.player_id is not null;
team_id | player_name
---------+-------------
1 | yashasvi
2 | Joe Root
3 | Hardik
4 | Jos
(4 rows)
Retrieving Teams Without Matching Players Using LEFT JOIN
movie=# select team.team_id, player.player_name from team left join player on team.player_id = player.player_id where team.player_id is null;
team_id | player_name
---------+-------------
(0 rows)
Retrieving All Teams and Players Using FULL OUTER JOIN
movie=# select team.team_id, player.player_name from team full outer join player on team.player_id = player.player_id;
team_id | player_name
---------+-------------
1 | yashasvi
2 | Joe Root
3 | Hardik
4 | Jos
| Rohit
(5 rows)
Retrieving All Data from Teams and Players Using FULL OUTER JOIN
movie=# select * from team full outer join player on team.player_id = player.player_id;
team_id | player_id | joining_date | player_id | player_name | team_name | country
---------+-----------+--------------+-----------+-------------+-----------+--------------
1 | 101 | 2025-02-09 | 101 | yashasvi | CSK | India
2 | 102 | 2025-02-10 | 102 | Joe Root | RR | England
3 | 103 | 2025-01-31 | 103 | Hardik | DD | Srilanka
4 | 104 | 2024-12-29 | 104 | Jos | RCB | South Africa
| | | 105 | Rohit | MI | India
(5 rows)
Retrieving Player and Team Information Across Multiple Joins
select player.player_name, team.team_name
from Player
join team
on player.player_id = team.player_id
join ipl_team
on ipl_team.player_id = team.player_id;
Retrieving Unique Player IDs from Both Tables Using UNION
movie=# select player_id from team
union
select player_id from player;
player_id
-----------
105
102
101
103
104
(5 rows)
Retrieving All Player IDs (Including Duplicates) Using UNION ALL
select player_id from team
union all
select player_id from player;
player_id
-----------
101
102
103
104
101
102
103
104
105
(9 rows)
Retrieving Common Player IDs from Both Tables Using INTERSECT
movie=# select player_id from team
intersect
select player_id from player;
player_id
-----------
101
103
104
102
(4 rows)
Retrieving Common Player IDs from Both Tables Using INTERSECT with ORDER BY
movie=# select player_id from team
intersect
select player_id from player order by player_id;
player_id
-----------
101
102
103
104
(4 rows)
Constraints
--> Constraints are the rules enforced on data columns on table.
--> These are used to prevent invalid data from being entered into the database.
--> This ensures the accuracy and reliability of the data in the database.
The following are commonly used constraints available in PostgreSQL.
- NOT NULL Constraint − Ensures that a column cannot have NULL value.
- UNIQUE Constraint − Ensures that all values in a column are different.
- PRIMARY Key − Uniquely identifies each row/record in a database table.
- FOREIGN Key − Constrains data based on columns in other tables.
- CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.
Table Creation and Data Insertion with Constraints
employee=# create table players
(player_id int, player_name varchar(20), score int check(score>0));
CREATE TABLE
employee=# insert into players values(101, 'rohit', 112);
INSERT 0 1
employee=# insert into players values(102, 'virat', -10);
ERROR: new row for relation "players" violates check constraint "players_score_check"
DETAIL: Failing row contains (102, virat, -10).
Table Creation with Multiple Constraints and Data Validation
CREATE TABLE cricket_players(player_id integer, player_name varchar(20), score integer check (score>=0), balls integer CONSTRAINT balls_faced CHECK (balls>0), CHECK (score>=balls));
Output:
INSERT INTO cricket_players VALUES (101, 'Sachin', 50, 45);
INSERT 0 1
INSERT INTO cricket_players VALUES (102, 'Virat', 30, 35);
ERROR: new row for relation "cricket_players" violates check constraint "cricket_players_check"
DETAIL: Failing row contains (102, Virat, 30, 35).
Table Creation with NOT NULL and CHECK Constraints on Price
CREATE TABLE products(product_id int not null, product_name varchar(20) not null, price numeric NOT NULL CHECK (PRICE>0));
insert into products values(null, null, null);
Output:
ERROR: NULL value in column "product_id" violates NOT NULL constraint
ERROR: NULL value in column "product_name" violates NOT NULL constraint
ERROR: NULL value in column "price" violates NOT NULL constraint
Creating a Table with a UNIQUE Constraint
create table student(student_id integer unique, name text);
Creating a Table with a Composite UNIQUE Constraint
create table students(student_id integer, student_name text, student_mobile integer, unique(student_id, student_mobile));
Primary Key
A Primary Key is a column or a combination of columns that uniquely identifies each row in a table.
- Must be unique (no duplicate values).
- cannot have NULL values.
- Each table can have only one primary key.
Error message will be :
ERROR: duplicate key value violates unique constraint ___
DETAIL: Key ____ already exists.
Creating a Table with a PRIMARY KEY Constraint
create table book
(book_id integer PRIMARY KEY, name text, author text);
Foreign Key
A Foreign Key is a column or a set of columns that establishes a relationship between two tables by referencing the Primary Key of another table.
Syntax:
FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)
Top comments (0)