Day 37 - Joins and Constraints in database

Create a Table for Storing Team Information:

employee=# create table team(team_id int, player_id int, joining_date date);

CREATE TABLE

Insert Team Information with Joining Dates:

employee=# 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 0 4

Create a Table for Storing player Information:

employee=# create table player(player_id int, player_name varchar(30), team_name varchar(20), country varchar(20));

CREATE TABLE

Insert Player Information:

employee=# 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');

INSERT 0 5

Display All Data from the Player Table:

employee=# select * from player;

 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)

Display All Data from the team Table:

employee=# select * from team;

 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)

Joins:

In SQL, JOINs are used to combine rows from two or more tables based on a related column. Here are the different types of joins commonly used in databases:

Inner Join Query to Fetch Team ID and Corresponding Player Name:

employee=# 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)

Right Join to Display Player Names with Team IDs:

employee=# 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)

Left Join to Fetch Teams and Players with Non-NULL Player IDs:

employee=# 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)

Left Join to Find Teams Without Assigned Players:

employee=# 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)

Full Outer Join to Fetch All Teams Ids and Players names:

employee=# 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)

Full Outer Join to Display All Data from Team and Player Tables:

employee=# select *  from team full outer join player on team.player_id = player.player_id;

Retrieve Player Names and Team Names for Players in the IPL Using Joins:

employee=# 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;

Retrieve Unique Player IDs from Team and Player Tables Using UNION:

The UNION operator combines the results of two queries and eliminates duplicate values

employee=# select player_id from team
union
select player_id from player; 
 player_id 
-----------
       105
       102
       101
       103
       104
(5 rows)

Retrieve All Player IDs from Team and Player Tables Using UNION ALL:

This operator combines the results of two queries but does not remove duplicates.

employee=# 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)

Retrieve Common Player IDs Between Team and Player Tables Using INTERSECT:

This operator returns the common player_ids that exist in both the team and player tables. It only includes those player_ids that appear in both result sets.

employee=# select player_id from team
intersect
select player_id from player;
 player_id 
-----------
       101
       103
       104
       102
(4 rows)

Retrieve Common Player IDs Between Team and Player Tables, Ordered by Player ID:

employee=# select player_id from team
intersect
select player_id from player order by player_id;
 player_id 
-----------
       101
       102
       103
       104
(4 rows)

Constraints:

In SQL, constraints are rules that are applied to columns in a table to enforce data integrity.

Constraints ensure that the data entered into a table is accurate, reliable, and follows the business rules or logic defined for the database.

UNIQUE: Ensures all values in a column are unique.
PRIMARY KEY: Combines NOT NULL and UNIQUE, uniquely identifies each row.
FOREIGN KEY: Ensures referential integrity between tables.
CHECK: Validates values in a column based on a condition.
DEFAULT: Specifies a default value if none is provided.
INDEX: Optimizes query performance (not strictly a constraint but related).

Create Players Table with Score Constraint Greater Than 0:

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).

Create Cricket Players Table with Constraints on Score, Balls Faced, and Score vs Balls Relationship:

employee=# 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));

CREATE TABLE

employee=# insert into cricket_players values(101,'raina',100,50);

INSERT 0 1

employee=# insert into cricket_players values(102,'rai',0,1);

ERROR:  new row for relation "cricket_players" violates check constraint "cricket_players_check"
DETAIL:  Failing row contains (102, rai, 0, 1).

employee=# insert into cricket_players values(103,'ra',10,1);

INSERT 0 1

employee=# select * from cricket_players;

 player_id | player_name | score | balls 
-----------+-------------+-------+-------
       101 | raina       |   100 |    50
       103 | ra          |    10 |     1
(2 rows)

Create Products Table with Constraints on Product ID, Name, and Price:

employee=# CREATE TABLE products(product_id int not null, product_name varchar(20) not null, price numeric NOT NULL CHECK (PRICE>0));

CREATE TABLE

employee=# insert into products values(11, null, 100);

ERROR:  null value in column "product_name" of relation "products" violates not-null constraint
DETAIL:  Failing row contains (11, null, 100).

employee=# insert into products values(11, 'soap', 100);

INSERT 0 1

employee=# select * from products;

 product_id | product_name | price 
------------+--------------+-------
         11 | soap         |   100
(1 row)

Create Student Table with Unique Student IDs:

employee=# create table student(student_id integer unique, name text);

CREATE TABLE

employee=# insert into student values(101,'bala');

INSERT 0 1

employee=# insert into student values(101,'aala');

ERROR:  duplicate key value violates unique constraint "student_student_id_key"
DETAIL:  Key (student_id)=(101) already exists.

employee=# select * from student;

 student_id | name 
------------+------
        101 | bala
(1 row)

Create Students Table with Unique Combination of Student ID and Mobile:

employee=# create table students(student_id integer, student_name text, student_mobile integer, unique(student_id, student_mobile));

CREATE TABLE

Create Book Table with Primary Key on Book ID:

employee=# create table book 
(book_id integer PRIMARY KEY, name text, author text);

CREATE TABLE

employee=# insert into book values(0,'database','muthu');

INSERT 0 1

employee=# insert into book values(1,'database','muthu');

INSERT 0 1

employee=# select * from book;

 book_id |   name   | author 
---------+----------+--------
       0 | database | muthu
       1 | database | muthu
(2 rows)

employee=# insert into book values(1,'database','muthu');

ERROR:  duplicate key value violates unique constraint "book_pkey"
DETAIL:  Key (book_id)=(1) already exists.