SQL

• Creating Database for Join:

CREATE DATABASE student;

• Creating Table - 1: class

CREATE TABLE class(
Roll_No int PRIMARY KEY,
Name varchar(30),
Address varchar(30),
Age int
);

• Inserting values in Table-1: class

INSERT INTO class VALUES
(1,'Harsh','Delhi',19),
(2,'Pratik','Bihar',18),
(3,'Riyanka','Siliguri',20),
(4,'Deep','Kolkata',19);

INSERT INTO class VALUES

(5,'Saptarhi','Ramnagar',19),
(6,'Dhanraj','Barabajar',20),
(7,'Rohit','Balughat',18),
(8,'Niraj','Alipur',19);

1
• Table-1:
class
SELECT * FROM class;

• Creating Table - 2: course

CREATE TABLE course
(Course_id int primary key,
Roll_No int
);

• Inserting values in Table-1: class

INSERT INTO course
VALUES (1,1), (2,2), (2,3), (3,4), (1,5), (4,9), (5,10), (4,11);

• Table-2: course
SELECT * FROM course;

2
 1. Joins in SQL

• Inner Join
SELECT class.Roll_No, class.Name,
class.Age,
course.course_id
FROM class
INNER JOIN course
ON class.Roll_No = course.Roll_No;

• Left Join

SELECT
class.Roll_No, class.Name, course.course_id
FROM class
LEFT JOIN course
ON class.Roll_No = course.Roll_No;

3
• Right Join
SELECT class.Roll_No, class.Name, course.Roll_No
FROM class
RIGHT JOIN course
ON class.Roll_No = course.Roll_No;

• Full Join
(SELECT

class.Roll_No,class.Name,class.Address, course.course_id
FROM class LEFT JOIN course
ON class.Roll_No = course.Roll_No)
UNION
(SELECT class.Roll_No, class.Name, class.Address,
course.course_id FROM class RIGHT JOIN course
ON class.Roll_No = course.Roll_No);

4
• Natural Join
SELECT class.Roll_No, class.Name,
class.Address,class.Age, course.course_id Natural
A Join B
FROM class
NATURAL JOIN course;

• Self Join
SELECT s1.first_name, s1.age, s1.city, s2.admission_no, s2.course,
s2.amount_paid
FROM stdd s1, fee s2
WHERE s1.admission_no <> s2.admission_no
AND s1.course != s2.course
ORDER BY s1.age;

5
6
• Cross Join A B
SELECT s1.admission_no , s1.first_name, s1.age,
1 A
s1.city,s2.course,s2.amount_paid 2 B
3 C
FROM STDD S1
CROSS JOIN fee s2;

7
8
 2. Subquerys in SQL

• Creating Database for subquery:

CREATE DATABASE subquery;

• Creating a table : employee

CREATE TABLE employee(
emp_id int, emp_name
varchar(30), emp_age
int, city varchar(30),
income int
);

• Inserting values in employee table

INSERT INTO employee VALUES
(101,'Peter',32,'Newyork',200000),
(102,'Mark',32,'California',300000),
(103,'Donald',40,'Arizona',1000000),
(104,'Obama',35,'Florida',5000000),
(105,'Linklon',32,'Georgia',250000),
(106,'Kane',45,'Alaska',450000),
(107,'Adam',35,'California',5000000),
(108,'Macculam',40,'Florida',350000),
(109,'Brayan',32,'Alaska',400000),
(110,'Stephen',40,'Arizona',600000),
(111,'Alexander',45,'Alaska',70000);

9
• Employee table SELECT *
FROM employee;

 Simple subquery
SELECT emp_name,city,income FROM employee
WHERE emp_id IN (SELECT emp_id FROM employee);

 Subquery with comparison Operator

SELECT * FROM employee
WHERE emp_id IN (SELECT emp_id FROM employee
WHERE income > 350000);

10
 SELECT emp_name, city, income FROM employee
WHERE income = (SELECT MAX(income) FROM employee);

 Subquery with NOT IN Operator

SELECT Name, City FROM std1
WHERE City NOT IN (
SELECT City FROM std2 WHERE City = 'Los Angeles');

3. Transactions in SQL
Transaction Control Language (TCL) is a subset of SQL used to manage
transactions within a database. Transactions are sequences of operations treated
as a single unit, ensuring data consistency and integrity.

11
• Table-1 : employee
SELECT * FROM employee;

• Table-2 : orders

SELECT * FROM orders;

 Begin Transaction

• Transaction with COMMIT Statement.

COMMIT: Permanently saves all changes made during a transaction

12
START TRANSACTION;

SELECT
@INCOME:=max(income) FROM
employee;

INSERT INTO employee VALUES(111,'Alexander',45,'California',70000);

INSERT INTO orders VALUES(6,'Printer',5654,'2020-01-10');

COMMIT;

SELECT * FROM employee;

SELECT * FROM orders;

13
• Transaction ROLLBACK Statement.

ROLLBACK: Reverts the database to its state before the transaction

or to a specified savepoint.

START TRANSACTION;

DELETE FROM orders;

SELECT * FROM orders;

ROLLBACK;

SELECT * FROM orders;

14
• Transaction with SAVEPOINT & RELEASE SAVEPOINT Statement.
SAVEPOINT: Sets a checkpoint within a transaction, allowing partial rollbacks.
RELEASE SAVEPOINT: Removes a savepoint, making it unavailable for rollback

START TRANSACTION;

INSERT INTO orders VALUES(9,'Ink',9876,'2020-12-12');

SAVEPOINT S1;

UPDATE orders
SET Product_Name = 'Scanner' WHERE order_id = 9;

SELECT * FROM orders;

15
 RELEASE SAVEPOINT S1;

COMMIT;

SELECT * FROM orders;

MongoDB
 CRUD Operations:
show dbs;

16
 use userdb;

show dbs;

db;

• Create
db.createCollection("users")

show collections

db.collectionName.insertOne({})

db.users.insertMany([{ name: "Dwight", age: 25},

{name:"Angela", age : 30,},{name:"jim", age: 29}])

17
• Read
db.users.find({age : {sgt :29}}, {name : 1, age:1})
db.users.findOne({name:"jim"})

• Update
db.users.updateOne({name:"Anjela"},
{$set: {email : 'angela@gmail.com'}})

db.users.updateMany({age:{$lt : 30}}, {$set: {name : 'Angela'}})

• Delete
db.users.deleteOne({name : "Angela"})
18
db.users.deleteMany({age : {$lt : 30}})

db.users.drop()

show collections

db.dropDatabase()

show dbs

PL/SQL
1. PL/SQL Comments:

19
DECLARE
message VARCHAR2(20) := 'Hello World';
BEGIN
/*
* PL/SQL executable statement(s)
*/
DBMS_OUTPUT.PUT_LINE (message);
END;
/

2. String Literals with Single Quotes:

DECLARE
message VARCHAR2(20) := 'That''s abc.com!';
BEGIN
DBMS_OUTPUT.PUT_LINE(message);
END;
/

3. PL/SQL Constants:
DECLARE
--constant declaration
pi CONSTANT NUMBER := 3.141592654;
--other declarations radius
NUMBER(5,2); dia
NUMBER(5,2); circumference
20
 NUMBER(7,2); area
NUMBER(10,2); BEGIN
radius := 9.5;
dia := radius*2;
circumference := 2.0 * pi * radius;
area := pi * radius * radius;
DBMS_OUTPUT.PUT_LINE('Radius: ' || radius);
DBMS_OUTPUT.PUT_LINE('Diameter: ' || dia);
DBMS_OUTPUT.PUT_LINE('Circumference: ' || umference);
DBMS_OUTPUT.PUT_LINE('Area: ' || area);
END;
/

4. Variable scope in PL/SQL:

DECLARE
-Global Variable um1
number := 96; um2
number := 86;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer Variable num1 : '||num1);
DBMS_OUTPUT.PUT_LINE('Outer Variable num2 : '||num2);
DECLARE

21
 -Local Variable num1
number := 195; num2
number := 185;
BEGIN
DBMS_OUTPUT.PUT_LINE('Inner Variable num1: '||num1);
DBMS_OUTPUT.PUT_LINE('Inner Variable num2: '||num2);
END;
END;
/

5. Assigning SQL Query Results to PL/SQL Variables

DECLARE
c_id student.id%type := 1; c_name
student.name%type; c_addr
student.address%type; c_sal
student.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM STUDENT
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE
('Customer '||c_name||' from '||c_addr||' earns '||c_sal);
END;
/
22
 ;

10. Arrays in PL/SQL:

DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER; names
namesarray; marks grades; total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav',
'Aziz'); marks:= grades(98, 97, 78, 87, 92); total :=
names.count;
dbms_output.put_line('Total '|| total || ' Students');

FOR i in 1 .. total LOOP

dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;

23
/

11. PL/SQL - Procedures

In PL/SQL, a procedure is a subprogram that performs a specific task,

such as modifying database data or performing calculations. Unlike a
function, a procedure does not return a value directly.

• IN and OUT Parameters in Procedure:

IN Parameter:

• It is used to pass values into the procedure.

• The value of the IN parameter cannot be modified within the
procedure.
OUT Parameter:
• It is used to return values from the procedure to the calling
environment.
• The value of the OUT parameter is modified inside the procedure
and is accessible outside after the procedure finishes executing.

IN OUT Parameter:
• It accepts an initial value when the procedure is called (like an IN
parameter).

24
 • It can also return a modified value to the calling environment (like
an OUT parameter).
• The value of the IN OUT parameter is both passed into and out of
the procedure, meaning it can be read and updated inside the
procedure.

• IN & OUT Mode Example 1

DECLARE
a number;
b number; c
number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END; BEGIN
a:= 23; b:=
45; findMin(a,
b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

• IN & OUT Mode Example 2

25
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS BEGIN
x := x * x;
END; BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/

12. PL/SQL - Functions

Functions − These subprograms return a single value; mainly used to

compute and return a value.
Procedures − These subprograms do not return a value directly; mainly
used to perform an action.

26
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;
RETURN total;
END;
/

DECLARE
c number(2); BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

DECLARE
a number;
b number; c
number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS z
number;
BEGIN

27
 IF x > y THEN
z:= x;
ELSE
z:= y;
END IF;
RETURN z;
END; BEGIN
a:= 23; b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

13. PL/SQL – Cursors

A cursor is a pointer to this context area. PL/SQL controls the context
area through a cursor. A cursor holds the rows (one or more) returned by
a SQL statement. The set of rows the cursor holds is referred to as the
active set.

There are two types of cursors

• Implicit cursors - Automatically created by Oracle when a SQL
query is executed in PL/SQL that returns a single row.
• Explicit cursors - Declared explicitly in the PL/SQL block to handle
queries that return multiple rows.

• Implicit Cursors
Implicit Cursors has attributes such as %FOUND, %ISOPEN,
%NOTFOUND, and %ROWCOUNT.
28
 %FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement

affected one or more rows or a SELECT INTO statement
returned one or more rows. Otherwise, it returns FALSE.

%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an
INSERT, UPDATE, or DELETE statement affected no rows, or
a SELECT INTO statement returned no rows. Otherwise, it
returns FALSE.

%ISOPEN
Always returns FALSE for implicit cursors, because Oracle
closes the SQL cursor automatically after executing its
associated SQL statement.
%ROWCOUNT

Returns the number of rows affected by an INSERT,

UPDATE, or DELETE statement, or returned by a SELECT
INTO statement.

Customer Table:

DECLARE total_rows
number(2); BEGIN
UPDATE customer

29
 SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN total_rows :=
sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/

• Explicit
Cursors
Steps to Use an Explicit Cursor:
1. Declare the Cursor: Define the cursor with a SQL query.
2. Open the Cursor: Initialize the cursor to execute the query.
3. Fetch Rows: Retrieve rows from the cursor into variables.
4. Close the Cursor: Release resources used by the cursor.

DECLARE
c_id customer.id%type;
c_name customer.name%type;
c_addr customer.address%type;
CURSOR c_customer is
SELECT id, name, address FROM customer;
BEGIN
OPEN c_customer;
LOOP

30
 FETCH c_customer into c_id, c_name, c_addr;
EXIT WHEN c_customer%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customer;
END;
/

14. Table Based Records

A record is a data structure that can hold data items of different kinds.
Records consist of different fields, similar to a row of a database table.

PL/SQL can handle the following types of records −

• Table-based
• Cursor-based records

• User-defined records

DECLARE
customer_rec customer%rowtype;
BEGIN
SELECT * into customer_rec
FROM customer
WHERE id = 5;

31
 dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' ||
customer_rec.address);
dbms_output.put_line('Customer Salary: ' ||
customer_rec.salary);
END;
/

15. PL/SQL - Exceptions

PL/SQL supports programmers to catch such conditions using

EXCEPTION block in the program and an appropriate action is
taken against the error condition.

There are two types of exceptions −

• System-defined exceptions

• User-defined exceptions

DECLARE
c_id customer.id%type := 8;
c_name customers.Name%type;
c_addr customer.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
32
 FROM customer
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/

16. PL/SQL - Triggers

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0) DECLARE
sal_diff number; BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

33
34