Scribd
Upload
10 views25 pages

SQL Program File

The document outlines various SQL operations including creating tables with constraints, inserting and updating records, querying tables with select statements, and performing set operations. It demonstrates the creation of views and stored procedures in PostgreSQL, showcasing examples of how to manipulate and retrieve data. The document serves as a comprehensive guide for executing basic database operations using SQL.

Uploaded by

patilruhi55
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
10 views25 pages

SQL Program File

The document outlines various SQL operations including creating tables with constraints, inserting and updating records, querying tables with select statements, and performing set operations. It demonstrates the creation of views and stored procedures in PostgreSQL, showcasing examples of how to manipulate and retrieve data. The document serves as a comprehensive guide for executing basic database operations using SQL.

Uploaded by

patilruhi55
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1.

To create one or more tables with following constraints, in


addition to the first two constraints (PK & FK).
a. Check constraint
b. Unique constraint
c. Not null constraint

postgres=# \c gunjandb;
You are now connected to database "gunjandb" as user "postgres".
gunjandb=# CREATE TABLE cust(
gunjandb(# id int PRIMARY KEY,
gunjandb(# name varchar(20) NOT NULL,
gunjandb(# city varchar(20) CHECK (city IN('Pune','Mumbai','Nasik','Goa')),
gunjandb(# email_id varchar(20) UNIQUE
gunjandb(# );
CREATE TABLE

gunjandb=# INSERT INTO cust(id,name,city,email_id)


gunjandb-# VALUES(1,'Ruhi','Pune','ruhi@[Link]'),
gunjandb-# (2,'Kalyani','Mumbai','kalyani@[Link]'),
gunjandb-# (3,'Darshan','Nasik','darshan@[Link]'),
gunjandb-# (4,'Nilesh','Goa','nilesh@[Link]');
INSERT 0 4

gunjandb=# select * from cust;


id | name | city | email_id
----+---------+--------+-------------------
1 | Ruhi | Pune | ruhi@[Link]
2 | Kalyani | Mumbai | kalyani@[Link]
3 | Darshan | Nasik | darshan@[Link]
4 | Nilesh | Goa | nilesh@[Link]
(4 rows)

gunjandb=# CREATE TABLE ord


gunjandb-# (ord_id int PRIMARY KEY,
gunjandb(# pro varchar(20) NOT NULL,
gunjandb(# id int references cust(id)
gunjandb(# );
CREATE TABLE

gunjandb=# select*from ord;


ord_id | pro | id
--------+-----+----
(0 rows)

gunjandb=# INSERT INTO ord(ord_id,pro,id)


gunjandb-# VALUES(111,'Books',1),
gunjandb-# (222,'Pen',2),
gunjandb-# (333,'Pencil',2);
INSERT 0 3

gunjandb=# select*from ord;


ord_id | pro | id
--------+--------+----
111 | Books | 1
222 | Pen | 2
333 | Pencil | 2
2. To drop a table, alter schema of a table, insert / update / delete
records using tables created in previous Assignments. ( use simple
forms of insert / update / delete statements)

postgres=# \c gunjandb;
You are now connected to database "gunjandb" as user "postgres".
gunjandb=# select*from cust;
id | name | city | email_id
----+---------+--------+-------------------
1 | Ruhi | Pune | ruhi@[Link]
2 | Kalyani | Mumbai | kalyani@[Link]
3 | Darshan | Nasik | darshan@[Link]
4 | Nilesh | Goa | nilesh@[Link]
(4 rows)

gunjandb=# INSERT INTO cust(id,name,city,email_id)


gunjandb-# VALUES (5,'Bhumi','Nasik','bhumi@[Link]'),
gunjandb-# (6,'Chaitali','Mumbai','chaitali@[Link]');
INSERT 0 2

gunjandb=# select*from cust;


id | name | city | email_id
----+----------+--------+--------------------
1 | Ruhi | Pune | ruhi@[Link]
2 | Kalyani | Mumbai | kalyani@[Link]
3 | Darshan | Nasik | darshan@[Link]
4 | Nilesh | Goa | nilesh@[Link]
5 | Bhumi | Nasik | bhumi@[Link]
6 | Chaitali | Mumbai | chaitali@[Link]
(6 rows)
gunjandb=# alter table cust
gunjandb-# add age integer;
ALTER TABLE

gunjandb=# select*from cust;


id | name | city | email_id | age
----+----------+--------+--------------------+-----
1 | Ruhi | Pune | ruhi@[Link] |
2 | Kalyani | Mumbai | kalyani@[Link] |
3 | Darshan | Nasik | darshan@[Link] |
4 | Nilesh | Goa | nilesh@[Link] |
5 | Bhumi | Nasik | bhumi@[Link] |
6 | Chaitali | Mumbai | chaitali@[Link] |
(6 rows)

gunjandb=# update cust


gunjandb-# set age =22
gunjandb-# where id>=6;
UPDATE 1

gunjandb=# select*from cust;


id | name | city | email_id | age
----+----------+--------+--------------------+-----
1 | Ruhi | Pune | ruhi@[Link] |
2 | Kalyani | Mumbai | kalyani@[Link] |
3 | Darshan | Nasik | darshan@[Link] |
4 | Nilesh | Goa | nilesh@[Link] |
5 | Bhumi | Nasik | bhumi@[Link] |
6 | Chaitali | Mumbai | chaitali@[Link] | 22
(6 rows)

gunjandb=# delete from cust where id=6;


DELETE 1

gunjandb=# select*from cust;


id | name | city | email_id | age
----+---------+--------+-------------------+-----
1 | Ruhi | Pune | ruhi@[Link] |
2 | Kalyani | Mumbai | kalyani@[Link] |
3 | Darshan | Nasik | darshan@[Link] |
4 | Nilesh | Goa | nilesh@[Link] |
5 | Bhumi | Nasik | bhumi@[Link] |
(5 rows)
3. To query the tables using simple form of select statement Select
from table [where order by ] Select from table [where group by <>
having <> order by <>].

gunjandb=# \c gunjan;
You are now connected to database "gunjan" as user "postgres".
gunjan=# CREATE TABLE stu
gunjan-# (roll int PRIMARY KEY,
gunjan(# name varchar(20),
gunjan(# marks int,
gunjan(# department varchar(20)
gunjan(# );
CREATE TABLE

gunjan=# INSERT INTO stu(roll,name,marks,department)


gunjan-# VALUES(10,'Ruhi',7,'Computer');
INSERT 0 1
gunjan=# INSERT INTO stu(roll,name,marks,department)
gunjan-# VALUES(11,'Kalyani',6,'Computer');
INSERT 0 1
gunjan=# INSERT INTO stu(roll,name,marks,department)
gunjan-# VALUES(12,'Darshan',23,'Computer');
INSERT 0 1

gunjan=# select*from stu;


roll | name | marks | department
------+---------+-------+------------
10 | Ruhi | 7 | Computer
11 | Kalyani | 6 | Computer
12 | Darshan | 23 | Computer
(3 rows)

gunjan=# select*from stu where marks<='6' order by roll ASC;


roll | name | marks | department
------+---------+-------+------------
11 | Kalyani | 6 | Computer
(1 row)

gunjan=# select count(department),department from stu group by department order


by department;
count | department
-------+------------
3 | Computer
(1 row)

gunjan=# select count(department),department from stu where roll<'10' group by


department having min(marks)>'7' order by department;
count | department
-------+------------
(0 rows)
[Link] query table, using set operations (union, intersect).

gunjandb=# CREATE TABLE TB1


gunjandb-# (sr_no int NOT NULL,
gunjandb(# rollno int NOT NULL,
gunjandb(# s_name varchar(20) NOT NULL
gunjandb(# );
CREATE TABLE

gunjandb=# SELECT*FROM TB1;


sr_no | rollno | s_name
-------+--------+--------
(0 rows)

gunjandb=# INSERT INTO TB1(sr_no,rollno,s_name)


gunjandb-# VALUES(1,11,'Ruhi'),
gunjandb-# (2,12,'Darshan'),
gunjandb-# (3,13,'Kalyani'),
gunjandb-# (4,14,'Bhumi');
INSERT 0 4

gunjandb=# select*from TB1;


sr_no | rollno | s_name
-------+--------+---------
1| 11 | Ruhi
2| 12 | Darshan
3| 13 | Kalyani
4| 14 | Bhumi
(4 rows)
gunjandb=# create table TB2
gunjandb-# (sr_no int NOT NULL,
gunjandb(# rollno int NOT NULL,
gunjandb(# s_name varchar(20)NOT NULL
gunjandb(# );
CREATE TABLE

gunjandb=# INSERT INTO TB2(sr_no,rollno,s_name)


gunjandb-# VALUES(1,6,'Nilesh'),
gunjandb-# (2,7,'Swapnil'),
gunjandb-# (3,8,'Chaitali'),
gunjandb-# (4,9,'Shital');
INSERT 0 4

gunjandb=# select*from TB2;


sr_no | rollno | s_name
-------+--------+----------
1| 6 | Nilesh
2| 7 | Swapnil
3| 8 | Chaitali
4| 9 | Shital
(4 rows)

gunjandb=# SELECT rollno,s_name from TB1 union select rollno,s_name from TB2;
rollno | s_name
--------+----------
8 | Chaitali
13 | Kalyani
7 | Swapnil
14 | Bhumi
9 | Shital
6 | Nilesh
11 | Ruhi
12 | Darshan
(8 rows)

gunjandb=# SELECT rollno,s_name from TB1 intersect select rollno,s_name from


TB2;
rollno | s_name
--------+--------
(0 rows)
[Link] query table using nested queries(use of ‘Except’, exists, not
exists, all clauses.

gunjandb=# DROP TABLE TB1;


DROP TABLE
gunjandb=# CREATE TABLE TB1
gunjandb-# (srno int PRIMARY KEY,
gunjandb(# rollno int,
gunjandb(# name varchar(20)
gunjandb(# );
CREATE TABLE

gunjandb=# DROP TABLE TB2;


DROP TABLE
gunjandb=# CREATE TABLE TB2
gunjandb-# (srno int PRIMARY KEY,
gunjandb(# rollno int,
gunjandb(# name varchar(20)
gunjandb(# );
CREATE TABLE

gunjandb=# select*from TB1;


srno | rollno | name
------+--------+------
(0 rows)

gunjandb=# INSERT INTO TB1(srno,rollno,name)


gunjandb-# values(1,10,'Ruhi');
INSERT 0 1
gunjandb=# INSERT INTO TB1(srno,rollno,name)
gunjandb-# VALUES(2,11,'Vishal');
INSERT 0 1

gunjandb=# INSERT INTO TB1(srno,rollno,name)


gunjandb-# Values(3,12,'Kalyani');
INSERT 0 1

gunjandb=# select*from TB1;


srno | rollno | name
------+--------+---------
1| 10 | Ruhi
2| 11 | Vishal
3| 12 | Kalyani
(3 rows)

gunjandb=# SELECT*FROM TB2;


srno | rollno | name
------+--------+------
(0 rows)

gunjandb=# INSERT INTO TB2(srno,rollno,name)


gunjandb-# VALUES(4,13,'Swapnil');
INSERT 0 1
gunjandb=# INSERT INTO TB2(srno,rollno,name)
gunjandb-# VALUES(5,14,'Darshan');
INSERT 0 1
gunjandb=# INSERT INTO TB2(srno,rollno,name)
gunjandb-# VALUES(6,15,'Tanu');
INSERT 0 1

gunjandb=# select*from TB2;


srno | rollno | name
------+--------+---------
4| 13 | Swapnil
5| 14 | Darshan
6| 15 | Tanu
(3 rows)

gunjandb=# select rollno,name from TB1 except select rollno,name from TB2;
rollno | name
--------+---------
10 | Ruhi
11 | Vishal
12 | Kalyani
(3 rows)

gunjandb=# select*from TB1 where exists(select*from TB2 where


[Link]=[Link]);
srno | rollno | name
------+--------+------
(0 rows)

gunjandb=# select*from TB1 where not exists(select*from TB2 where


[Link]=[Link]);
srno | rollno | name
------+--------+---------
1| 10 | Ruhi
2| 11 | Vishal
3| 12 | Kalyani
(3 rows)

gunjandb=# select name,rollno from TB1 where rollno<all(select rollno from TB1
where srno=2);
name | rollno
------+--------
Ruhi | 10
(1 row)
6. To Create views.

gunjandb=# CREATE TABLE student


gunjandb-# (rollno int PRIMARY KEY,
gunjandb(# name varchar(20),
gunjandb(# marks int,
gunjandb(# department varchar(20)
gunjandb(# );
CREATE TABLE

gunjandb=# select*from student;


rollno | name | marks | department
--------+------+-------+------------
(0 rows)

gunjandb=# INSERT INTO student(rollno,name,marks,department)


gunjandb-# VALUES(10,'Chaitali',203,'computer');
INSERT 0 1
gunjandb=# INSERT INTO student(rollno,name,marks,department)
gunjandb-# VALUES(11,'Krishna',253,'math');
INSERT 0 1
gunjandb=# INSERT INTO student(rollno,name,marks,department)
gunjandb-# VALUES(12,'Bhumi',343,'science');
INSERT 0 1
gunjandb=# select*from student;
rollno | name | marks | department
--------+----------+-------+------------
10 | Chaitali | 203 | computer
11 | Krishna | 253 | math
12 | Bhumi | 343 | science
(3 rows)

gunjandb=# create view student_view as


gunjandb-# select rollno,name from student;
CREATE VIEW

gunjandb=# select*from student_view;


rollno | name
--------+----------
10 | Chaitali
11 | Krishna
12 | Bhumi
(3 rows)

gunjandb=# alter view student_view rename to stu_V1;


ALTER VIEW
gunjandb=# select*from stu_V1;
rollno | name
--------+----------
10 | Chaitali
11 | Krishna
12 | Bhumi
(3 rows)
7. To create Stored Procedure
 A Simple Stored Procedure.
 A Stored Procedure with IN, OUT and IN/OUT parameter.

gunjandb=# CREATE OR REPLACE PROCEDURE greet(IN name VARCHAR(20))


gunjandb-# LANGUAGE plpgsql
gunjandb-# AS $$
gunjandb$# begin
gunjandb$# RAISE NOTICE'Good Morning %',name;
gunjandb$# END;
gunjandb$# $$;
CREATE PROCEDURE

gunjandb=# CALL greet('Ruhi');


NOTICE: Good Morning Ruhi
CALL

gunjandb=# CREATE OR REPLACE PROCEDURE sum_and_mul


gunjandb-# (IN a INT,IN b INT,OUT sum INT, OUT mul INT)
gunjandb-# LANGUAGE plpgsql
gunjandb-# AS $$
gunjandb$# BEGIN
gunjandb$# sum:=a+b;
gunjandb$# mul:=a*b;
gunjandb$# END;
gunjandb$# $$;
CREATE PROCEDURE
gunjandb=# CALL sum_and_mul(4,5,4,5);
sum | mul
-----+-----
9 | 20
(1 row)

gunjandb=# CALL sum_and_mul(10,2,10,2);


sum | mul
-----+-----
12 | 20
(1 row)
[Link] Function
 A Simple Stored Function
 A Stored Function that returns
 A Stored Function recursive

gunjandb=# CREATE OR REPLACE PROCEDURE newmethod


gunjandb-# (IN a INT,IN OUT b INT)
gunjandb-# LANGUAGE plpgsql
gunjandb-# AS $$
gunjandb$# BEGIN
gunjandb$# a:=a+b;
gunjandb$# b:=a*b;
gunjandb$# END;
gunjandb$# $$;
CREATE PROCEDURE

gunjandb=# CALL newmethod(8,8);


b
-----
128
(1 row)

gunjandb=# CALL newmethod(5,2);


b
----
14
(1 row)
[Link] Function
 A Simple Function
 A Stored Function that returns
 A Stored Function recursive

gunjandb=# CREATE OR REPLACE Function voting (age INT)


gunjandb-# Returns Void AS $$
gunjandb$# BEGIN
gunjandb$# IF age>=18 THEN
gunjandb$# RAISE NOTICE 'You can cast your vote now';
gunjandb$# ELSE
gunjandb$# RAISE NOTICE'Sorry,you are too young to vote';
gunjandb$# end if;
gunjandb$# end;
gunjandb$# $$ language plpgsql;
CREATE FUNCTION

CREATE FUNCTION
gunjandb=# select voting(20);
NOTICE: You can cast your vote now
voting
--------

(1 row)

gunjandb=# select voting(16);


NOTICE: Sorry,you are too young to vote
voting
--------
(1 row)

gunjandb=# create or replace function sum(a int,b int) returns int as $$


gunjandb$# declare
gunjandb$# s INT;
gunjandb$# begin
gunjandb$# s:=a+b;
gunjandb$# return s;
gunjandb$# end;
gunjandb$# $$ language plpgsql;
CREATE FUNCTION

gunjandb=# select sum(10,20);


sum
-----
30
(1 row)

gunjandb=# select sum(456+678);


sum
------
1134
(1 row)

gunjandb=# create or replace function fact(n int) returns int as $$


gunjandb$# begin
gunjandb$# if(n<=1)then
gunjandb$# return 1;
gunjandb$# else
gunjandb$# return n*fact(n-1);
gunjandb$# end if;
gunjandb$# end;
gunjandb$# $$ language plpgsql;
CREATE FUNCTION

gunjandb=# select fact(4);


fact
------
24
(1 row)

gunjandb=# select fact(9);


fact
--------
362880
(1 row)
[Link]
 A Simple Cursor.
 A Parameterize Cursor.

postgres=# \c gunjandb
You are now connected to database "gunjandb" as user "postgres".
gunjandb=# CREATE TABLE product(
gunjandb(# id INT,
gunjandb(# product_name VARCHAR(100),
gunjandb(# product_category_id INT,
gunjandb(# price INT
gunjandb(# );
CREATE TABLE

gunjandb=# INSERT INTO


product(id,product_name,product_category_id,price)Values(101,'Laptop',60,4500
0);
INSERT 0 1
gunjandb=# INSERT INTO
product(id,product_name,product_category_id,price)Values(107,'Mobile',70,1500
0);
INSERT 0 1
gunjandb=# INSERT INTO
product(id,product_name,product_category_id,price)Values(108,'Washing
Machine',80,30000);
INSERT 0 1
gunjandb=# INSERT INTO
product(id,product_name,product_category_id,price)Values(109,'Microwave',90,1
2000);
INSERT 0 1
gunjandb=# INSERT INTO
product(id,product_name,product_category_id,price)Values(110,'Ceiling
Fan',100,2500);
INSERT 0 1
gunjandb=# INSERT INTO
product(id,product_name,product_category_id,price)Values(111,'Air
Conditioner',110,38000);
INSERT 0 1
gunjandb=# select*from product;
id | product_name | product_category_id | price
-----+-----------------+---------------------+-------
101 | Laptop | 60 | 45000
107 | Mobile | 70 | 15000
108 | Washing Machine | 80 | 30000
109 | Microwave | 90 | 12000
110 | Ceiling Fan | 100 | 2500
111 | Air Conditioner | 110 | 38000
(6 rows)

gunjandb=# create or replace function t_cursore()


gunjandb-# RETURNS text
gunjandb-# LANGUAGE plpgsql AS $$
gunjandb$# DECLARE
gunjandb$# test_cursor CURSOR FOR
gunjandb$# SELECT id,product_name,price
gunjandb$# FROM product;
gunjandb$# currentID INT;
gunjandb$# currentProductName VARCHAR(100);
gunjandb$# currentPrice INT;
gunjandb$# BEGIN
gunjandb$# OPEN test_cursor;
gunjandb$# LOOP
gunjandb$# FETCH test_cursor INTO
currentID,currentProductName,currentprice;
gunjandb$# EXIT WHEN NOT FOUND;
gunjandb$# RAISE NOTICE'% %(ID:
%)',currentProductName,currentPrice,currentID;
gunjandb$# END LOOP;
gunjandb$# CLOSE test_cursor;
gunjandb$# RETURN'Done';
gunjandb$# END;
gunjandb$# $$;
CREATE FUNCTION
gunjandb=# select*from t_cursore();
NOTICE: Laptop 45000(ID:101)
NOTICE: Mobile 15000(ID:107)
NOTICE: Washing Machine 30000(ID:108)
NOTICE: Microwave 12000(ID:109)
NOTICE: Ceiling Fan 2500(ID:110)
NOTICE: Air Conditioner 38000(ID:111)
t_cursore
-----------
Done
(1 row)

You might also like