Post Graduate Government College, Sector 11,

Chandigarh

Practical File
Database Management System

Submitted to : - Submitted by : -
Prof. Chetan Kumar Name - Chirag
Gupta Sir Roll no. - 4243/19
Class - BCA 4th Semester

1|Page
Sr. No. Title Page no. Remark
1. Introduction of Data Base Management 4
System
2. Show Databases, Create Databases, 5
Use Statement
3. Create Table, Describe Statement, 6
Insertion Table
4. Select Query, Where Clause, 6-7
Comparison Operators
5. Order By Clause, Delete, Rename 8-9
Command
6. Drop, Truncate, Update Tables 9-10
Statement
7. Count, Avg, Sum Function 10-11
8. Arithmetic Operator Command 12-13
9. Logical Operator Command 13-14
10. Between and Is Operator 14-15
11 Alter, Drop, Modify Table Statement 15-16
12. Change Data Type, Alter Table – Add 16-17
Column
13. Group By Statement, Sum 17-18

14. Having Clause 18

15. Primary Key On Create Tables & Alter 18-20
Table & Drop
16. Foreign Key Constraint Create & Alter 20-22
table & Drop
17. Unique Constraint On Create & Alter 22-23
Table & Drop
18. Not Null Create & Alter Tables 23-24
19. Check Create & Alter & Drop Table 24-26

20. Default Create & Alter Table 26-27

21. Auto Increment Field & Gender Enum 27-28

& Lower Case
22. Dual Table & Ascii & Char_length & 28-29
Concat
23. Left, Right, Mid, Trim, Strcmp, Abs, 29-31
Ceil & Floor

2|Page
Sr. No. Title Page no. Remark
24. Greatest, Mod, Rand(Random No.), 31-32
Sqrt(Squar Root) & pow(Power)

25. Truncate, Isnull, Date, Extract, Date 32-36

Add, Date Diff, Date sub, Cast, Date
Formate, Convert & Distinct Function

26. Join, Inner, Equal, Natural, Left, Right, 36-41

Union & Union All Operator, Full,
Cross Join

27. Views Create & Drop 41-42

28. Create Or Replace Views, Updatable, 42-44

Not Updatable
29. Subquery 34-45

30. Exists & Note Exists Operators 45

31. From Clause 45-46

32. Rollup 46

33. PL/SQL Introduction 47

34. How To Display A Message Using 47

PL/SQL
35. Variables, Assignment & Constant 48
Number
36. Existing Table Fetch Data In Pl Sql 49

37. %TYPE & %ROWTYPE 49

3|Page
Data -: In simple words, data can be facts related to any object in consideration. For
example, your name, age, height, weight, etc. are some data related to you. A picture,
image, file, pdf, etc. can also be considered data.

Database -: A database is an organized collection of facts. In other words, we can say

that it is a collection of information arranged and presented to serve an assigned purpose.

Components

1. DDL (Data Definition Language):- It is a set of SQL commands used to

create, modify and delete database structures but not data. They are normally used
by the DBA not by user to a limited extent, a database designer or application
developer. These statements are immediate i.e. they are not susceptible to
ROLLBACK commands. It should also be noted that if several DML statements for
example UPDATES are executed then issuing any DDL command would COMMIT
all the updates as every DDL command implicitly issues a COMMIT command to
the database. Anybody using DDL must have the CREATE object privilege and a
table space area in which to create objects.

Examples: - CREATE, ALTER, DROP, TRUNCATE, COMMENT etc.

2. DML (Data Manipulation Language): - It is the area of SQL that allows

changing data within the database.

Examples: - INSERT, UPDATE, DELETE etc.

3. DCL (Data Control Language) :- It is the component of SQL statement that

control access to data and to the database. Occasionally DCL statements are grouped
with DML statements.

Examples: - COMMIT, SAVEPOINT, ROLLBACK etc.

4. DQL (Data Query Language):- It is the component of SQL statement that

allows getting data from the database and imposing ordering upon it. It includes the
SELECT statement. This command is the heart of SQL. It allows getting the data
out of the database perform operations with it. When a SELECT is fired against a
table or tables the results is compiled into a further temporary table, which is
displayed or perhaps received by the program i.e. a front-end.

Examples:- SELECT retrieve data from the database.

4|Page
 SQL COMMANDS

✓ Show Databases Command -: The most common way to get a list of the
MySQL databases is by using the MySQL client to connect to the MySQL server
and run the SHOW DATABASES command.

Syntax -: show databases;

Example :-

✓ Create Databases -:
Example :-

✓ USE -: The USE statement tells MySQL to use the named database as the default
(current) database for subsequent statements. This statement requires some
privilege for the database or some object within it.
The named database remains the default until the end of the session or
another USE statement is issued:
Syntax -:
USE db_name;
Example :-

5|Page
 ✓ Create Table -: A table is basic unit of storage. It is composed of rows and
columns. To create a table we will name the table and the columns of the table.
It must begin with a letter and can be up to 30 characters long.
It must not be duplicate and not any reserved word.

Syntax to create a table is -:

CREATE TABLE tablename (column_name1 datatype (size), column_name2 datatype
(size) …);
Example is
CREATE TABLE STUDENT(Roll_no int,Name varchar(15),AGE smallint);

✓ Describe Statement -: As the name suggests, DESCRIBE is used to describe

something. Since in database we have tables, that’s why we use DESCRIBE or
DESC(both are same) command to describe the structure of a table.

Syntax:
DESCRIBE one;
OR
DESC one;
Example :

✓ The Insertion Of Data Into Table :- Once a table is created, the most
natural thing to do is load this with data to be manipulated later i.e. to insert the
rows in a table. The data in a table can be inserted in three ways.
Syntax:-
INSERT INTO <table name >(<columnname1>,<columnname2>) VALUES(<expression1>,<expression 2>);
OR
INSERT INTO <tablename>VALUES(<expression1 >,<expression2> );
OR
INSERT INTO <tablename> VALUES(‘<&columnname1>’ ,’<&columnname2>’);

Example :-
6|Page
 ✓ Select Query -: SELECT QUERY is used to fetch the data from the MySQL
database. Databases store data for later retrieval. The purpose of MySQL Select is
to return from the database tables, one or more rows that match a given criteria.
Select query can be used in scripting language like PHP, Ruby, or you can execute
it via the command prompt.
Syntax:-
Select colname1,colname2,colname3,……. From tablename;
Select * from tablename;
Example :-

✓ Where Clause -: We have seen the SQL SELECT command to fetch data from
a MySQL table. We can use a conditional clause called the WHERE Clause to
filter out the results. Using this WHERE clause, we can specify a selection criteria
to select the required records from a table.
Syntax:-
Select colname1,colname2,colname3,……. From tablename where condition;

Example :-

Comparison Operators

Operator Description Example

= Checks if the values of the two operands are equal or not, if yes, (A = B) is not true.
then the condition becomes true.
!= Checks if the values of the two operands are equal or not, if the (A != B) is true.
values are not equal then the condition becomes true.
> Checks if the value of the left operand is greater than the value of (A > B) is not true.
the right operand, if yes, then the condition becomes true.
< Checks if the value of the left operand is less than the value of the (A < B) is true.

7|Page
 right operand, if yes then the condition becomes true.
>= Checks if the value of the left operand is greater than or equal to (A >= B) is not true.
the value of the right operand, if yes, then the condition becomes
true.
<= Checks if the value of the left operand is less than or equal to the (A <= B) is true.
value of the right operand, if yes, then the condition becomes true.

✓ Order By Clause -: The Order By Keyword Is Used To Sort The Result-Set In

Ascending Or Descending Order.
The Order By Keyword Sorts The Records In Ascending Order By Default. To
Sort The Records In Descending Order, Use The Desc Keyword.
Syntax:-
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ...
ASC|DESC;
Example :-

Example :-

✓ DELETE -: The DELETE statement is used to delete existing records in a table.

Syntax:-
DELETE FROM table_name WHERE condition;
Example :-
Delete from student;
All data delete.

8|Page
Example :-

✓ Rename Command -: The rename command is used to change the name of an

existing database object(like Table,Column) to a new name.
Renaming a table does not make it to lose any data is contained within it.
Syntax:-
RENAME TABLE current_table_name TO new_table_name ;

Example :

✓ Drop Table Statement -: The DROP TABLE statement is used to drop an

existing table in a database.
Note: Be careful before dropping a table. Deleting a table will result in loss of complete
information stored in the table!

Syntax:-
Drop Table Name;
Example :-

✓ Truncate Table -: The TRUNCATE TABLE statement is used to delete the

data inside a table, but not the table itself.

Syntax:-
TRUNCATE TABLE table_name;
Example :-

9|Page
 ✓ Update Statement -: The UPDATE statement is used to modify the existing
records in a table.
Note: Be careful when updating records in a table! Notice the WHERE clause in the
UPDATE statement. The WHERE clause specifies which record(s) that should be
updated. If you omit the WHERE clause, all records in the table will be updated!
Syntax:-
UPDATE table_name SET column1 =new value1, column2 = newvalue2, ... WHERE
condition;
Example :-

✓ Count() Function -: The COUNT() function returns the number of rows that
matches a specified criterion.

10 | P a g e
Syntax:-
SELECT COUNT(column_name) FROM table_name WHERE condition;
Example :-

Note -: group functions ignore null values.

Example :-

✓ Avg() Function -: The AVG() function returns the average value of a numeric
column.
Syntax -:
SELECT AVG(column_name) FROM table_name WHERE condition;
Example :-

✓ SUM() function -: The SUM() function returns the total sum of a numeric
column.
Syntax -:
SELECT SUM(column_name) FROM table_name WHERE condition;
Example :-

11 | P a g e
 Arithmetic operator command

Operator Description
+ Add

- Subtract

* Multiply
/ Divide

% Modulo

Example -:

12 | P a g e
 Logical Operators

Operator Description
ALL TRUE if all of the subquery values meet the condition
AND TRUE if all the conditions separated by AND is TRUE
ANY TRUE if any of the subquery values meet the condition
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of
expressions

LIKE TRUE if the operand matches a pattern

NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is TRUE
SOME TRUE if any of the subquery values meet the condition

Example -:

13 | P a g e
 ✓ Between Operator -: The BETWEEN operator selects values within a given
range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Syntax-:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN
value1 AND value2;

Example -:
14 | P a g e
 ✓ Is Operator –: In databases, NULL is unknown, not applicable or missing
information, therefore, you cannot use the comparison operators (=, >,<, etc.,) to
check whether a value is NULL or not.
Syntax-:
SELECT * FROM table_name WHERE column_name is (not) null;
Example -:

✓ Alter Table Statement -: The ALTER TABLE statement is used to add,

delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an
existing table.
Keyword-: add,drop,modify.
Syntax-:
ALTER TABLE table_name ADD (column_name datatype, column_name datatype,…..
column_name datatype);
Note -: by default store null value.
Add Example -:

15 | P a g e
Drop Example -:

Modify Example -:

✓ Change Data Type -:

Example -:

✓ Alter Table - Add Column -: To add a column in a table.

syntax:
ALTER TABLE table_name ADD column_name datatype;
16 | P a g e
Example -:

✓ Group By Statement -: The GROUP BY statement groups rows that have

the same values into summary rows, like "find the number of customers in each
country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(),
MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Syntax-:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY
column_name(s) ORDER BY column_name(s);
Example -:

SUM -: select sum(salary),city from emp group by city;

Example -:

17 | P a g e
 ✓ HAVING Clause -: The HAVING clause was added to SQL because the
WHERE keyword cannot be used with aggregate functions.
Syntax-:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY
column_name(s) HAVING condition ORDER BY column_name(s);
Example -:

✓ Primary Key On Create Table -: Creates a PRIMARY KEY on the "ID"

column when the "Persons" table is created:

Syntax-:
• CREATE TABLE Persons (ID int NOT NULL, LastName varchar(255) NOT
NULL, FirstName varchar(255),Age int,PRIMARY KEY (ID));

• CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY,LastName

varchar(255) NOT NULL,FirstName varchar(255),Age int);

18 | P a g e
 • CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT
NULL,FirstName varchar(255),Age int,CONSTRAINT PK_Persons PRIMARY
KEY (ID,LastName));
Example -:

Multiple Columns Primary Key -:

Example -:

✓ Primary Key On Alter Table -: To create a PRIMARY KEY constraint on

the "ID" column when the table is already created
Syntax-:
• ALTER TABLE Persons ADD PRIMARY KEY (ID);
• ALTER TABLE Persons ADD CONSTRAINT PK_table PRIMARY KEY
(ID,LastName);
• ALTER TABLE Persons DROP PRIMARY KEY;
• ALTER TABLE Persons DROP CONSTRAINT PK_ Persons;

Example -:

19 | P a g e
Drop Primary Key Example -:

✓ Foreign Key Constraint -: The FOREIGN KEY constraint is used to prevent

actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the
primary key is called the referenced or parent table.

Syntax-:
• CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT
NULL, PersonID int,PRIMARY KEY (OrderID),FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID));

• CREATE TABLE Orders (OrderID int NOT NULL,OrderNumber int NOT

NULL, PersonID int,PRIMARY KEY (OrderID),CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)REFERENCES Persons(PersonID));
Example -:

20 | P a g e
 ✓ Foreign Key On Alter Table -: To create a FOREIGN KEY constraint on the
"PersonID" column when the "Orders" table is already created

Syntax-:
• ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES
Persons(PersonID);

• ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY

(PersonID) REFERENCES Persons(PersonID);
Example -:

✓ Drop A Foreign Key Constraint -:

Syntax-:
• ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;
• ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;

Example -:

21 | P a g e
 ✓ Unique Constraint On Create Table -:
• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.

Syntax-:
• CREATE TABLE Persons(ID int NOT NULL UNIQUE,LastName varchar(255)
NOT NULL,FirstName varchar(255),Age int);

• CREATE TABLE Persons(ID int NOT NULL,LastName varchar(255) NOT

NULL,
• FirstName varchar(255),Age int,UNIQUE (ID));

• CREATE TABLE Persons(ID int NOT NULL,LastName varchar(255) NOT

NULL,
• Firstname varchar(255),Age int,CONSTRAINT UC_Person UNIQUE
(ID,LastName));
Example -:

• Unique Constraint On Alter Table -:To Create A Unique Constraint On

The "Id" Column When The Table Is Already Created.

22 | P a g e
 Syntax-:
• ALTER TABLE Persons ADD UNIQUE (ID);

• ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE

(ID,LastName);
Example -:

✓ DROP a UNIQUE Constraint -:

Syntax-:
• ALTER TABLE Persons DROP INDEX UC_Person;

• ALTER TABLE Persons DROP CONSTRAINT UC_Person;

Example -:

✓ Not Null On Create Table -: The "ID", "LastName", and "FirstName"

columns will NOT accept NULL values when the "Persons" table is created:
• By default, a column can hold NULL values.
• The Not NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you cannot insert
a new record, or update a record without adding a value to this field.

Syntax-:
CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,Age int);

23 | P a g e
Example -:

✓ Not Null On Alter Table -: To create a NOT NULL constraint on the "Age"
column when the "Persons" table is already created, use the following SQL:

Syntax-:
ALTER TABLE Persons MODIFY Age int NOT NULL;
Example -:

✓ Check On Create Table -:

• The CHECK constraint is used to limit the value range that can be placed in a
column.
• If you define a CHECK constraint on a column it will allow only certain values for
this column.
• If you define a CHECK constraint on a table it can limit the values in certain
columns based on values in other columns in the row.
Creates a CHECK constraint on the "Age" column when the "Persons" table is created.
The CHECK constraint ensures that the age of a person must be 18, or older:

Syntax-:
CREATE TABLE Persons (ID int NOT NULL,LastName varchar(255) NOTNULL,
FirstName varchar(255),Age int,CHECK (Age>=18));

24 | P a g e
Example -:

✓ Check On Alter Table -: To create a CHECK constraint on the "Age" column

when the table is already created

Syntax-:
ALTER TABLE Persons ADD CHECK (Age>=18);
Example -:

✓ Drop A Check Constraint -: To drop a CHECK constraint.

Syntax-:
ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;
25 | P a g e
Example -:

✓ DEFAULT on CREATE TABLE -: The sets a DEFAULT value for the

"City" column when the "Persons" table is created:
• The DEFAULT constraint is used to set a default value for a column.
• The default value will be added to all new records, if no other value is
specified.

Syntax-:
CREATE TABLE Persons (ID int NOT NULL,LastName varchar(10) NOT NULL,
FirstName varchar(10),Age int,City varchar(255) DEFAULT 'Sandnes');
Example -:

✓ Default On Alter Table -: To create a DEFAULT constraint on the "City"

column when the table is already created

Syntax-:
ALTER TABLE table_name ALTER col_name SET DEFAULT 'name';

26 | P a g e
Example -:

✓ Auto Increment Field -: Auto-increment allows a unique number to be

generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically
every time a new record is inserted.
The following SQL statement defines the "Personid" column to be an auto-increment
primary key field in the "Persons" table:

Syntax-:
CREATE TABLE Persons (Personid int NOT NULL AUTO_INCREMENT,LastName
varchar(255) NOT NULL,FirstName varchar(255),Age int,PRIMARY KEY (Personid));
Example -:

✓ Gender enum -:

Syntax-:
Create table st2 (gender enum(“male”,”fenale”));

27 | P a g e
Example -:

✓ Lower case –:
Syntax-:
Select lcase(col_name) from table_name;
Update -: update table_name set col_name=lcase(cal_name);
Example -:

✓ Dual table -:
Syntax-:
Select ucase(“hello”) from dual;
28 | P a g e
Example -:

✓ Ascii -:
Syntax-:
Select ascii(“v”) from dual;
Example -:

✓ Char_length -:
Syntax-:
Select char_length(‘vikash’) from dual;
Example -:

✓ Concat -:
Syntax-:
Select concat(‘vikash’,’sharma’) from dual;
Example -:

✓ Left, Right and Mid -:

Syntax-:
Select left(‘vikash’,4) from dual;
Select right(‘vikash’,4) from dual;
29 | P a g e
Select mid(‘vikash’,3,2) from dual;
Example -:

✓ Trim -: left and right side space remove.

Syntax-:
Select trim(‘ Vikash ’) from dual;
Example -:

✓ Strcmp -: compare string

Syntax-:
Select strcmp(‘Vikash’,’vikash) from dual;
Example -:

30 | P a g e
 ✓ Abs, ceil and floor -:
Syntax-:
Select abs(-4255) from dual;
Select ceil(12.5) from dual;
Select floor(12.5) from dual;
Example -:

✓ Greatest, mod, rand(random number), sqrt(squar root) and

pow(power) -:
Syntax-:
Select greatest(99,105,11,12,13) from dual;
Select mod(12,3) from dual;
Select pow(12,2) from dual;
Select rand() from dual;
Select sqrt(25) from dual;
Example -:

31 | P a g e
 ✓ Round function -:Two decimal places round.
Syntax-:
Select round(25.374896) from dual;
Example -:

✓ Truncate function -: decimal poit all truncate .

Syntax-:
Select truncate(25.374896) from dual;

Example -:

✓ IsNull function -:
Syntax-:
Select isnull(class) from dual;

Example -:
32 | P a g e
 ✓ Date function -:
Syntax-:
Select now() from dual;
Select curdate() from dual;
Select curtime() from dual;
Select date(now()) from dual;
Example -:

➢ Extract function -:
Syntax-:
Select extract(day from now()) from dual;

33 | P a g e
Example -:

➢ Date add function -:

Syntax-:
Select date_add(now(),interval 1 year) from dual;
Example -:

➢ Date diff function -:

Syntax-:
Select datediff(now(),”2022-06-11”) from dual;
Example -:

➢ Date sub function -:

Syntax-:
Select date_sub(now(),interval 1 year) from dual;
Example -:

➢ Cast function -:
Syntax-:
Select cast(“2”, as decimal) from dual;

34 | P a g e
Example -:

➢ Date format function -:

Syntax-:
Select date_format(now(),”%d %m %y”) from dual;
Example -:

➢ Convert function -:
Syntax-:
Select convert(“2” ,as decimal) from dual;
Example -:

➢ Distinct function -:
Syntax-:
Select distinct(class) from table_name;
Example -:

35 | P a g e
 ➢ JOIN -: A JOIN clause is used to combine rows from two or more tables, based
on a related column between them.
Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in
the "Customers" table. The relationship between the two tables above is the
"CustomerID" column.

Different Types of JOINs

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table

INNER JOIN -:
Syntax-:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM 1table_name
INNER JOIN 2table_name ON Orders.CustomerID=Customers.CustomerID;
36 | P a g e
Example -:

EQUAL JOIN (=) -:

37 | P a g e
NATURAL JOIN -:

Syntax-:
SELECT * FROM 1table_name Natural JOIN 2table name;

Example -:

LEFT JOIN -: The LEFT JOIN keyword returns all records from the left table
(table1), and the matching records from the right table (table2). The result is 0 records
from the right side, if there is no match.

Syntax-:
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON
table1.column_name = table2.column_name;
Example -:

RIGHT JOIN -: The RIGHT JOIN keyword returns all records from the right table
(table2), and the matching records from the left table (table1). The result is 0 records
from the left side, if there is no match.

Syntax-:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON
table1.column_name = table2.column_name;
Example -:

38 | P a g e
 ➢ UNION OPERATOR -: The UNION operator is used to combine the result-
set of two or more SELECT statements.
• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order
Syntax-:
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM
table2;
Example -:

UNION ALL -: The UNION operator selects only distinct values by default. To allow
duplicate values, use UNION ALL:
Syntax-:
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s)
FROM table2;
Example -:

39 | P a g e
FULL OUTER JOIN -: The FULL OUTER JOIN keyword returns all records when
there is a match in left (table1) or right (table2) table records.

Tip : FULL OUTER JOIN and FULL JOIN are the same.

Syntax-:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON
table1.column_name = table2.column_name WHERE condition;

Or

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name =

table2.column_name
UNION
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name =
table2.column_name;

Example -:

40 | P a g e
CROSS JOIN -:
Example -:

• VIEWS -: Views Are Virtual Tables That Do Not Store Any Data Of Their Own
But Display Data Stored In Other Tables. In Other Words, Views Are Nothing But
Sql Queries. A View Can Contain All Or A Few Rows From A Table. A Mysql
View Can Show Data From One Table Or Many Tables.

Syntax-:
CREATE VIEW `view_name` AS SELECT statement;
Example -:

41 | P a g e
DROP VIEW -:
Example -:

CREATE OR REPLACE VIEW -:

Example -:

select view_definition,table_name from information_schema.views where

table_name='view2';
Example -:

42 | P a g e
desc information_schema.views;
select table_schema,table_name,definer from information_schema.views;
Example -:

select view_definition,table_name from information_schema.views where

table_name='view2';
Example -:

NOT UPDATABLE -:
Example -:

create view view4 as select STUDEnt.rollno,fees.fees from student

inner join fees on student.rollno=fees.rollno;
Example -:

43 | P a g e
SUBQUERY -:
Syntax-:
select name from student where rollno=(select rollno from fees where
fees=10000);
• Single row subquery (=, not in)
• Multirow subquery (in, not in, any[<, >, = in], all[<, >)
• Multicolumn subquery
• Correlated subquery
Example -:

44 | P a g e
Exists and not exists operators -:

➢ From Clause -:
Syntax-:
select max(rollno) from (select rollno from fees) as sample;
select max(rollno) from (select rollno from fees) as sample;
select (rollno) from (select rollno from fees) as sample;
Example -:

45 | P a g e
ROLLUP -: The ROLLUP in MySQL is a modifier used to produce the summary
output, including extra rows that represent super-aggregate (higher-level) summary
operations. It enables us to sum-up the output at multiple levels of analysis using a single
query.
Syntax-:
select city,gender,sum(salary) from emp group by city,gender with rollup;
select gender,sum(salary) from emp group by gender with rollup;
Example -:

46 | P a g e
 PL/SQL Introduction

PL/SQL is a block structured language that enables developers to combine the power of
SQL with procedural statements.All the statements of a block are passed to oracle engine
all at once which increases processing speed and decreases the traffic.

Typically, each block performs a logical action in the program. A block has the following
structure:

• DECLARE
declaration statements;

• BEGIN
executable statements

• EXCEPTIONS
exception handling statements

• END;

• Declare section starts with DECLARE keyword in which variables, constants,

records as cursors can be declared which stores data temporarily. It basically
consists definition of PL/SQL identifiers. This part of the code is optional.

• Execution section starts with BEGIN and ends with END keyword.This is a
mandatory section and here the program logic is written to perform any task like
loops and conditional statements. It supports all DML commands, DDL commands
and SQL*PLUS built-in functions as well.

• Exception section starts with EXCEPTION keyword.This section is optional which

contains statements that are executed when a run-time error occurs. Any
exceptions can be handled in this section.

➢ Let us see an example to see how to display a message using PL/SQL :

Example-:

47 | P a g e
 ➢ Variables -:
Syntax for declaration of variables:
variable_name datatype [NOT NULL := value ];

Example-:

➢ Assignment Operator
Example-:

➢ Constant Number
Example-:

48 | P a g e
 ➢ Existing Table Fetch Data In Pl Sql
Example-:

➢ %TYPE -:
Example-:

➢ %ROWTYPE -:
Example-:

49 | P a g e