 /* EKANSH KANOT - 235416 */

/*
DROP TABLE train_passenger CASCADE;
DROP TABLE passenger CASCADE;
DROP TABLE train CASCADE:
*/

--CREATION

CREATE TABLE train(train_no INT PRIMARY KEY, train_name VARCHAR(20), depart_time TIME, arrival_time TIME, source_stn VARCHAR(20), dest_stn VARCHAR(20), no_of_res_bogies INT, bogie_capacity INT); 
\d train

CREATE TABLE passenger (passenger_id INT, passenger_name VARCHAR(28), address VARCHAR(36), age INT,gender CHAR(1));
\d passenger

CREATE TABLE train_passenger(t_no INT,p_id INT, ticket_no INT, CONSTRAINT CK PRIMARY KEY(t_no,p_id,ticket_no), bogie_no INT,no_of_berths INT, dates DATE, ticket_amt decimal(7,2), status CHAR(10) CHECK(status IN ('W','C')));
\d train_passenger

--INSERTION
--TRAIN
INSERT INTO train VALUES (1001,'abcd','20:12:23','01:54:48','pune','mumbai',8,3);
INSERT INTO train VALUES (1002,'efgh','10:32:10','22:45:47','pune','shahada', 8,5);
INSERT INTO train VALUES (1003,'hijk','04:04:04','13:55:49','pune','goa',8,4); 
INSERT INTO train VALUES (1004,'mпор','21:14:55','09:45:50','mumbai','pune',3,7);

SELECT * FROM train;

--PASSENGER
INSERT INTO passenger VALUES (11,'Ekansh','KN',19,'M');
INSERT INTO passenger VALUES (22,'Tanushree','warje',19,'F');
INSERT INTO passenger VALUES (33,'Janki','sangmwadi',19,'F'); 
INSERT INTO passenger VALUES (44,'bhagyam','FC', 19,'M');

SELECT * FROM passenger;

--TRAIN_PASSENGER
INSERT INTO train_passenger VALUES (1001,11,111,2,3,'2023-06-20',5000.4,'W');
INSERT INTO train_passenger VALUES (1002,22,222,3,1,'2023-03-02',2500.2,'C');
INSERT INTO train_passenger VALUES (1001,33,333,5,4,'2023-06-20',7500.16,'C');
INSERT INTO train_passenger VALUES (1004,44,444,6,1,'2023-04-23',2506.2,'W');

SELECT * FROM train_passenger;

--A6 QUERIES
SELECT passenger_name FROM passenger, train_passenger WHERE passenger.passenger_id= train_passenger.p_id AND train_passenger.dates='2023-03-02' AND train_passenger.status = 'C'; 
SELECT FROM passenger, train_passenger WHERE passenger.passenger_id = train_passenger.p_id AND train_passenger.status ='W';
DELETE FROM train WHERE no_of_res_bogies = 3 AND source_stn= 'mumbai' AND dest_stn = 'pune';

SELECT * FROM train;

--FUNCTIONS--
--Q1--
--DROP FUNCTION IF EXISTS t_amt; 
CREATE OR REPLACE FUNCTION t_amt (VARCHAR, DATE) RETURNS FLOAT AS'
DECLARE
      N ALIAS FOR $1;
      D ALIAS FOR $2;
      amt FLOAT;
BEGIN
      SELECT SUM(ticket_amt) INTO amt FROM train, train_passenger WHERE train.train_no = train_passenger.t_no AND train.train_no =(SELECT train_no FROM train WHERE train_name = $1) AND train_passenger.dates = $2;
      RETURN amt;
END; 
'LANGUAGE'plpgsql';
SELECT t_amt('abcd','2023-06-20');

--Q2--
--DROP FUNCTION IF EXISTS up_status;
CREATE OR REPLACE FUNCTION up_status (VARCHAR) RETURNS VOID AS'
DECLARE
      name_passenger ALIAS FOR $1;
BEGIN
      UPDATE train_passenger SET status = ''C'' WHERE train_passenger.status =''W'' AND p_id = (SELECT passenger_id FROM passenger WHERE passenger_name = $1);
END;
'LANGUAGE'plpgsql';
SELECT up_status('Ekansh');
SELECT * FROM train_passenger;

--EXCEPTION-HANDELING
--Q1--
--DROP FUNCTION IF EXISTS status;
CREATE OR REPLACE FUNCTION status (INTEGER) RETURNS CHAR AS'
DECLARE
      pass_id ALIAS FOR $1;
      tic_status CHAR(1);
BEGIN
      IF pass_id NOT IN (SELECT passenger_id FROM passenger) THEN
        RAISE EXCEPTION ''Invalid passenger id: %'',pass_id;
      ELSE
        SELECT status INTO tic_status FROM train_passenger WHERE p_Id = $1;
      END IF;
      RETURN tic_status;
END;
'LANGUAGE'plpgsql';

SELECT status(33);
--SELECT status(122133);
--SELECT status(34563);

--Q2--
--DROP FUNCTION IF EXISTS dest;
CREATE OR REPLACE FUNCTION dest (VARCHAR) RETURNS INTEGER AS'
DECLARE
        des ALIAS FOR $1;
        total_t INTEGER;

BEGIN
      IF des NOT IN(SELECT dest_stn FROM train) THEN
        RAISE NOTICE ''Invalid Destination Station: %'',des;
      ELSE 
        SELECT COUNT(dest_stn) INTO total_t FROM train WHERE dest_stn = $1;
        RETURN total_t;
      END IF;
END;
'LANGUAGE'plpgsql';
SELECT dest('mumbai');
SELECT dest('goa');
--SELECT dest('indore');

--VIEWS--
--Q1--
--DROP VIEW IF EXISTS bogie_cap;
CREATE OR REPLACE VIEW bogie_cap AS SELECT * FROM train WHERE bogie_capacity = (SELECT MAX(bogie_capacity) FROM train);
SELECT * FROM bogie_cap;

--Q2--
--DROP VIEW IF EXISTS t_details;
CREATE OR REPLACE VIEW t_details AS SELECT train_passenger.* FROM passenger,train,train_passenger WHERE passenger.passenger_id = train_passenger.p_id AND train.train_no = train_passenger.t_no and train_passenger.p_id = (SELECT passenger_id FROM passenger WHERE passenger_name = 'Ekansh' ) AND train_passenger.t_no = (SELECT train_no FROM train WHERE train_name='abcd');
SELECT * FROM t_details;

--CURSORS--
--Q1--
--DROP FUNCTION IF EXISTS confrim_book;
CREATE OR REPLACE FUNCTION confrim_book(DATE) RETURNS VOID AS'
DECLARE
        t_date ALIAS FOR $1;
        C_train CURSOR FOR SELECT COUNT(status) FROM train_passenger WHERE train_passenger.status = ''C'' AND train_passenger.dates = $1;
        count_status INTEGER;
BEGIN
        OPEN C_train;
        LOOP
          FETCH C_train INTO count_status;
          EXIT WHEN NOT FOUND;
          IF FOUND THEN
            RAISE NOTICE '' NUMBER OF CONFIRMED TICKETS ARE: %'',count_status;
          END IF;
        END LOOP;
        CLOSE C_train;
END;
'language'plpgsql';
SELECT confrim_book('2023-06-20');
SELECT confrim_book('2024-12-02');

--Q2--
--DROP FUNCTION IF EXISTS male_passenger;
CREATE OR REPLACE FUNCTION male_passenger() RETURNS VOID AS'
DECLARE
        C_pass CURSOR FOR SELECT * FROM passenger WHERE gender = ''M'';
        m_details passenger%ROWTYPE;
BEGIN
      OPEN C_pass;
      LOOP
        FETCH C_pass INTO m_details;
        EXIT WHEN NOT FOUND;
        IF FOUND THEN 
          RAISE NOTICE '' DETAILS OF MALE PASSENGER: %'',m_details;
        END IF;
      END LOOP;
      CLOSE C_pass;
END;
'language'plpgsql';
SELECT male_passenger();

--TRIGGER--
--Q1--
CREATE OR REPLACE FUNCTION check_capacity() RETURNS TRIGGER AS'
BEGIN 
      IF NEW.bogie_capacity > 0 THEN 
        RETURN NEW;
      ELSE 
        RAISE EXCEPTION '' BOGIE CAPACITY CAN NOT BE NEGATIVE'';
      END IF;
END;
'language'plpgsql';
CREATE OR REPLACE TRIGGER validate_bogie BEFORE INSERT ON train FOR EACH ROW EXECUTE PROCEDURE check_capacity();
INSERT INTO train VALUES (1005,'adfs','10:32:10','22:45:47','pune','nandurbar', 8,5);
--INSERT INTO train VALUES (1006,'flkj','04:32:10','20:45:43','pune','mumbai', 8,0);

--Q2--
select * from train_passenger where ticket_no = 111;
CREATE OR REPLACE FUNCTION status_change() RETURNS TRIGGER AS'
BEGIN
      IF NEW.status = OLD.status THEN 
        RAISE NOTICE '' STATUS IS ALREADY %'',OLD.status;
        RETURN OLD;
      ELSE
        RAISE NOTICE '' STATUS IS CHANGED TO %'',NEW.status;
        RETURN NEW;
      END IF;
END;
'language'plpgsql';
CREATE OR REPLACE TRIGGER change_status BEFORE UPDATE ON train_passenger FOR EACH ROW EXECUTE PROCEDURE status_change();
UPDATE train_passenger SET status = 'C' WHERE ticket_no = 111;
SELECT * FROM train_passenger;
UPDATE train_passenger SET status = 'W' WHERE ticket_no = 111;
SELECT * FROM train_passenger;

SELECT * FROM  passenger, train_passenger, train WHERE train_passenger.p_id = passenger.passenger_id AND passenger.passenger_id =(SELECT passenger_id FROM passenger WHERE passenger_name = 'Ekansh' ); 
/* THANK YOU :) */

created 1 year ago by Ekansh Kanot

PostgreSQL online editor

Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.

About PostgreSQL

PostgreSQL is a open source relational database system and is also knows as Postgres.

Key Features:

Postgres is not only free and open-source but also it is highly extensible.
Custom Data types and funtions from various programming languaues can be introduced and the good part is compiling entire database is not required.
ACID(Atomicity, Consistency, Isolation, Durability) compliant.
First DBMS which implemented Multi-version concurrency control (MVCC) feature.
It's the default database server for MacOS.
It supports all major operating systems like Linux, Windows, OpenBSD,FreeBSD etc.

Syntax help

1. CREATE

CREATE command is used to create a table, schema or an index.

Syntax:

         CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

2. ALTER

ALTER command is used to add, modify or delete columns or constraints from the database table.

Syntax

ALTER TABLE Table_name ADD column_name datatype;

3. TRUNCATE:

TRUNCATE command is used to delete the data present in the table but this will not delete the table.

Syntax

TRUNCATE table table_name;

4. DROP

DROP command is used to delete the table along with its data.

Syntax

DROP TABLE table_name;

5. RENAME

RENAME command is used to rename the table name.

Syntax

ALTER TABLE table_name1 RENAME to new_table_name1;

6. INSERT

INSERT Statement is used to insert new records into the database table.

Syntax

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

7. SELECT

Select statement is used to select data from database tables.

Syntax:

SELECT column1, column2, ...
FROM table_name;

8. UPDATE

UPDATE statement is used to modify the existing values of records present in the database table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

9. DELETE

DELETE statement is used to delete the existing records present in the database table.

Syntax

DELETE FROM table_name where condition;