SQL
SQL or Structured Query Language is a powerful language that
performs the functions of data manipulation(DML), data definition(DDL) and data
control or data authorization(DAL/DCL).
A Non procedural language - the capability to act on a set
of data and the lack of need to know how
to retrieve it.
An SQL can perform the functions of more than a procedure.
The De Facto Standard query language for Relational Database
Systems (RDBMS)
Very flexible
FEATURES AND TYPES
• SQL
is coded without data-navigational instructions.
• The
optimal access paths are determined by the DBMS. This is advantageous because
the database knows better how it has stored data than the user.
Data Definition Language (DDL)
Create,
Alter and Drop
Data Manipulation Language (DML)
Select,
Insert, Update and Delete
Data Control Language
(DCL)
Grant
and Revoke
OPERATIONS
The following are the Operations that can be performed by an SQL on the database
tables :
- Select
- Project
- Union
- Intersection
- Difference
- Join
- Divide
SELECT & PROJECT
QUERIES
Select retrieves a specific number of rows from a table
Projection operation retrieves a specified subset of
columns(but all rows) from the table
E.g.. : select
cust_name, cust_no from customer where cust_id=C123;
The where clause defines the Predicates for the SQL
operation.
The above where clause can have multiple conditions using
AND & OR.
Many other clauses can be used in conjunction with the WHERE
clause to code the required predicate, some are :-
• Between
/ Not Between
• In
/ Not In
• Like
/ Not Like
• IS
NULL / IS NOT NULL
select query can be nested for complex queries.
E.g. SELECT CUST_NO, CUST_ADDR FROM CUSTOMER
WHERE
ORDER_NO = (SELECT ORDER_NO FROM ORDER WHERE
NO_PRODUCTS =5);
JOIN
OUTER JOIN : For one or more tables being joined,
both matching and non-matching rows are returned. Duplicate columns may be eliminated
The non-matching
columns will have nulls in them.
INNER JOIN: Here there is a possibility one or more
of the rows from either or both tables being joined will not be included in the
table that results from the join operation
STORED PROCEDURES
A stored procedure is a subroutine available to applications
accessing a relational database system.
Stored procedures (sometimes called a proc, sproc, StoPro,
StoredProc, or SP) are actually stored in the database data dictionary.
Stored procedures must be invoked using the CALL statement:
CALL procedure(...) or
EXECUTE procedure(...)
There are several advantages of using stored procedures instead of
standard SQL:
- Stored
procedures allow a lot more flexibility offering capabilities such as
conditional logic.
- Since
stored procedures are stored within the DBMS, bandwidth and execution time
are reduced. This is because a single stored procedure can execute a
complex set of SQL statements.
- SQL
Server pre-compiles stored procedures such that they execute optimally.
- Client
developers are abstracted from complex designs. They would simply need to
know the stored procedure's name and the type of data it returns.
TRIGGERS
A database trigger is procedural code that is automatically
executed in response to certain events on a particular table or view in a
database.
The trigger is mostly used for keeping the integrity of the
information on the database.
For example, when a new record (representing a new worker) is
added to the employees table, new records should be created also in the tables
of the taxes, vacations, and salaries.
Microsoft SQL Server supports triggers either after or
instead of an insert, update, or delete operation.
They can be set on tables and views with the constraint that
a view can be referenced only by an INSTEAD OF trigger.
• Drop
table
• Create
table
• Alter
table
• Login
events
WHEN TO USE TRIGGERS
1.When we want to insert data into
multiple tables at same time.
2. when we want to write some constraints.
3. for security purpose we also use triggers
2. when we want to write some constraints.
3. for security purpose we also use triggers
0 comments:
Post a Comment