SQL Views

Disclaimer: This presentation is prepared by trainees of
baabtra as a part of mentoring program. This is not official
document of baabtra –Mentoring Partner
Baabtra-Mentoring Partner is the mentoring division of baabte System Technologies Pvt .
Ltd

Views, Triggers, Functions- Calling a function, return type,
Stored Procedures, Indexing and Joins

VIEWS

• The view is a virtual table. It does not physically exist. Rather, it is created by a
query joining one or more tables.
• A view contains rows and columns, just like a real table
• The fields in a view are fields from one or more real tables in the database

Creating an SQL VIEW
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

View Creation - Example

• View Creation - Example
CREATE VIEW sup_orders
AS SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id and suppliers.supplier_name = 'IBM';

• This View (Create statement) would create a virtual table based on the result set of the select
statement. You can now query the view as follows

SELECT * FROM sup_orders;

Updating VIEW

• You can modify the definition of a VIEW without dropping it by using the following
syntax
CREATE OR REPLACE VIEW view_name
AS SELECT columns
FROM table
WHERE predicates;

• View Modify - Example
CREATE or REPLACE VIEW sup_orders
AS SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';

Dropping VIEW
• The syntax for dropping a VIEW :

DROP VIEW view_name;

• View Drop - Example

DROP VIEW sup_orders;

Question: Can you update the data in an view?
Answer : A view is created by joining one or more tables. When you update record(s) in a view, it
updates the records in the underlying tables that make up the View.
So, yes, you can update the data in View providing you have the proper privileges to the
underlying tables.
Question: Does the SQL View exist if the table is dropped from the database?
Answer: Yes, View continues to exist even after one of the tables (that the SQL View is based on) is
dropped from the database. However, if you try to query the View after the table has been
dropped, you will receive a message indicating that the View has errors.

TRIGGER
• What is a Trigger?

A trigger is a block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table. A trigger is triggered automatically
when an associated DML statement is executed.
• Syntax of Triggers

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition) ;

TRIGGER

• The syntax for a dropping a Trigger is:

DROP TRIGGER trigger_name ON tbl_name;

• The syntax for a disabling a Trigger is:

ALTER TRIGGER trigger_name DISABLE;

• The syntax for a enabling a Trigger is:

ALTER TRIGGER trigger_name ENABLE;

Example

• Creating Trigger

CREATE TRIGGER deleted_detailss
BEFORE
DELETE on tbl_customer
FOR EACH ROW
EXECUTE PROCEDURE customerss_delete();

• Drop Trigger

drop trigger delete_details on tbl_customer;

Functions

• A function is a group of statements that executes upon request
• Python provides many built-in functions and allows programmers to define their own
functions
• A request to execute a function is known as a function call
• When a function is called, it may be passed arguments that specify data upon which
the function performs its computation
• Functions defined within class statements are also called methods
The def Statement
• The def statement is the most common way to define a function
Syntax
def function-name(parameters):
statement(s)

Functions

The return keyword
• A function is created to do a specific task
• Often there is a result from such a task
• The return keyword is used to return values from a function
• A function may or may not return a value
• If a function does not have a return keyword, it will send a None value

Example
def showMessage(msg):
print msg

def cube(x):
return x * x * x
def main():
x = cube(3)
print x
showMessage("Computation finished.")
print showMessage("Ready.")
main()

output:
baabtra@baabtra-desktop:~$ python ppt.py
27
Computation finished.
Ready.
None

Calling Functions

• A function call is an expression with the following syntax:
function-object(arguments)
• function-object It is most often the function's name.
• The parentheses denote the function-call operation itself.
• Arguments, in the simplest case, is a series of zero or more expressions separated by
commas (,), giving values for the function's corresponding formal parameters

Calling Functions

total = 0;
# Function definition is here
def sum( arg1, arg2 ):
# Add both the parameters and return them."
total = arg1 + arg2;
# Here total is local variable.
print "Inside the function local total : ", total
return total;

def main():
# Now you can call sum function
sum( 10, 20 );
print "Outside the function global total : ", total
main()

output:
baabtra@baabtra-desktop:~$ python ppt2.py
Inside the function local total : 30
Outside the function global total : 0

Advantage

The advantages of using functions are:
• Reducing duplication of code
• Improving clarity of the code
• Reuse of code
• Information hiding

INDEX

• The CREATE INDEX statement is used to create indexes in tables

• Indexes allow the database application to find data fast; without reading the whole
table

• An index can be created in a table to find data more quickly and efficiently

• The users cannot see the indexes, they are just used to speed up searches/queries

INDEX

• CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name);

• CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

• UNIQUE indicates that the combination of values in the indexed columns must be
unique

INDEX

• Rename an Index
The syntax for renaming an index is:

ALTER INDEX index_name RENAME TO new_index_name;

• Drop an Index
The syntax for dropping an index is:

DROP INDEX index_name;

JOINS
• Joins are used to query data from two or more tables, based on a relationship
between certain columns in these tables

• Types of Joins

LEFT JOIN: Return all rows from the left table, even if there are no matches in the
right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in
the left table
FULL JOIN: Return rows when there is a match in one of the tables

INNER JOIN
• The INNER JOIN keyword returns rows when there is at least one match in both
tables

• INNER JOIN Syntax
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

• If there are rows in “table_name1 " that do not have matches in " table_name2 ",
those rows will NOT be listed

LEFT JOIN

• The LEFT JOIN keyword returns all rows from the left table (table_name1), even if
ther are no matches in the right table (table_name2)

• LEFT JOIN Syntax
FROM table_name1
LEFT JOIN table_name2

RIGHT JOIN
• The RIGHT JOIN keyword returns all the rows from the right table (table_name2),
even if there are no matches in the left table (table_name1)

• RIGHT JOIN Syntax
FROM table_name1
RIGHT JOIN table_name2

FULL JOIN

• The FULL JOIN keyword return rows when there is a match in one of the tables

FULL JOIN Syntax
FROM table_name1
FULL JOIN table_name2

• The FULL JOIN keyword returns all the rows from the left table (Table1), and all the rows
from the right table (Table2). If there are rows in " Table1 " that do not have matches in "
Table2", or if there are rows in " Table2" that do not have matches in " Table1 ", those rows
will be listed as well.

stored procedure

• In a database management system (DBMS), a stored procedure is a set of Structured Query
Language (SQL) statements with an assigned name that's stored in the database in compiled
form so that it can be shared by a number of programs.

• preserving data integrity (information is entered in a consistent manner)

Data integrity means the correctness and consistency of data
Enforcing data integrity ensures the quality of the data in the database
Consider following two examples of data integriry in a database

1) If an employee is entered with an employee_id value of 123, the database should not allow
another employee to have an ID with the same value

2) If you have an employee_rating column intended to have values ranging from 1 to 5, the
database should not accept a value of 6

Example

CREATE OR REPLACE FUNCTION insert_tbl_customer(int,text,date,text,int,boolean)
RETURNS void AS
$delimiter$
INSERT INTO tbl_customer (pk_int_cu_id, vchr_cname, dat_dob, vchr_email,bint_phone,bln_sex)
VALUES ($1,$2,$3,$4,$5,$6);
$delimiter$
LANGUAGE SQL;

select insert_tbl_customer(201,'john','1990-10-5','b@gmail',94,true);

If this presentation helped you, please visit our page
facebook.com/baabtra and like it.
Thanks in advance.
www.baabtra.com | www.massbaab.com |www.baabte.com

SQL Views

More Related Content

What's hot

Similar to SQL Views

More from baabtra.com - No. 1 supplier of quality freshers

Recently uploaded

SQL Views