SQL TYPES
1. SQL Types,
DDL,DML (http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands)
3. Index, Join,
View, Trigger, Union, SP, Functions
4. Cursor,
Transaction
5. Sql server
diff (Version)
6. Dbms vs
rdbms
7. Function vs
sp
8. Subquery
9. Delete in
view?
10. Index &
its types
11. Constraints
& its syntex (composite key)
12. .Queries
(without using fn)
13. Self,Composite
Key
14. Union Vs
Union All
15. Composite
key, Candidate Key
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
- CREATE
- to create objects in the database
- ALTER
- alters the structure of the database
- DROP
- delete objects from the database
- TRUNCATE
- remove all records from a table, including all spaces allocated for the
records are removed
- COMMENT
- add comments to the data dictionary
- RENAME
- rename an object
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
- SELECT
- retrieve data from the a database
- INSERT
- insert data into a table
- UPDATE
- updates existing data within a table
- DELETE
- deletes all records from a table, the space for the records remain
- MERGE
- UPSERT operation (insert or update)
- CALL
- call a PL/SQL or Java subprogram
- EXPLAIN
PLAN - explain access path to data
- LOCK
TABLE - control concurrency
Data Control Language (DCL) statements. Some examples:
- GRANT
- gives user's access privileges to database
- REVOKE
- withdraw access privileges given with the GRANT command
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
- COMMIT
- save work done
- SAVEPOINT
- identify a point in a transaction to which you can later roll back
- ROLLBACK
- restore database to original since the last COMMIT
- SET TRANSACTION
- Change transaction options like isolation level and what rollback
segment to use
Constraints are the rules
enforced on data columns on table. These are used to limit the type of data
that can go into a table. This ensures the accuracy and reliability of the data
in the database.
Constraints could be column
level or table level. Column level constraints are applied only to one column,
whereas table level constraints are applied to the whole table.
Following
are commonly used constraints available in SQL. These constraints have already
been discussed in SQL -
RDBMS Concepts chapter
but its worth to revise them at this point.
·
CHECK Constraint: The
CHECK constraint ensures that all values in a column satisfy certain
conditions.
Constraints can be specified
when a table is created with the CREATE TABLE statement or you can use ALTER
TABLE statement to create constraints even after the table is created.
Dropping
Constraints:
Any constraint that you have
defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT
option.
For example, to drop the
primary key constraint in the EMPLOYEES table, you can use the following
command:
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
Some implementations may
provide shortcuts for dropping certain constraints. For example, to drop the
primary key constraint for a table in Oracle, you can use the following
command:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
Some implementations allow you
to disable constraints. Instead of permanently dropping a constraint from the
database, you may want to temporarily disable the constraint and then enable it
later.
Integrity
Constraints:
Integrity constraints are used
to ensure accuracy and consistency of data in a relational database. Data
integrity is handled in a relational database through the concept of
referential integrity.
There
are many types of integrity constraints that play a role in referential
integrity (RI). These constraints include Primary Key, Foreign Key, Unique
Constraints and other constraints mentioned above.
Index :-
Introduction:
Here I will explain about SQL Indexes and different types of
indexes and advantages of indexes in SQL Server.
Description:
An index can be created in a table to increase the performance of
application and we can get the data more quickly and efficiently. Let’s see an
example to illustrate this point suppose now we are reading book in that I need
to check the information for dbmanagement to get this information I need to search each page of the book because
I don’t know in which page that word information exists it’s time taken
process. Instead of reading the each page of book to get that particular word
information if I check the index of book (Glossary) it is much quicker for us
to get the pages which contains the information withdbmanagement word. By using second method we can save lot of time and we can
get information in efficient way.
This same principle applies for retrieving data from a database
table. Without an SQL Index, the database system reads through the entire table
to locate the desired information. If we set the proper index in place, the
database system first go through the index to find out where to retrieve the
data, and then go to that location directly to get the needed data. This is
much faster due to the SQL Index. Creating
and removing indexes on table will not show any effect on application because
indexes operate behind the scenes.
Syntax to Create SQL Index in table:
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
Example to create Index on table
CREATE INDEX SampleIndex ON UserInformation (UserName)
The above statement is used to create an index named “SampleIndex” on the “UserName” column in the “UserInformation” table
If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
Example of creating SQL Index on multiple columns
CREATE INDEX SampleIndex ON UserInformation (UserName,FirstName)
To Drop Index on table use the below statement
DROP INDEX TABLE_NAME.INDEX_NAME
In SQL we are having two types of indexes are there
1) Clustered
Index
2) Non-Clustered
Index
Clustered Index
Only 1 allowed per table
physically rearranges the data in the table to confirm to the index constraints
for use on columns that are frequently searched for ranges of data for use on
columns with low selectivity.
Non-Clustered Index
Up to 249 allowed per table
creates a separate list of key values with pointers to the location of the data
in the data pages For use on columns that are searched for single values For
use on columns with high selectivity
A clustered index is a special
type of index that reorders the way records in the table are physically stored.
Therefore table can have only one clustered index. The leaf nodes of a
clustered index contain the data pages. A non-clustered index is a special type
of index in which the logical order of the index does not match the physical
stored order of the rows on disk. The leaf node of a non-clustered index does
not consist of the data pages. Instead, the leaf nodes contain index rows.
Joins :-
SQL Joins
SQL
Joins are used to relate information in different tables. A Join condition is a
part of the sql query that retrieves rows from two or more tables. A SQL Join
condition is used in the SQL WHERE Clause of select, update, delete statements.
The Syntax for joining two tables is:
SELECT col1, col2, col3...FROM table_name1, table_name2WHERE table_name1.col2 = table_name2.col1;
If a sql
join condition is omitted or if it is invalid the join operation will result in
a Cartesian product. The Cartesian product returns a number of rows equal to
the product of all rows in all the tables being joined. For example, if the
first table has 20 rows and the second table has 10 rows, the result will be 20
* 10, or 200 rows. This query takes a long time to execute.
Lets use
the below two tables to explain the sql join conditions.
database table "product";
product_id
|
product_name
|
supplier_name
|
unit_price
|
100
|
Camera
|
Nikon
|
300
|
101
|
Television
|
Onida
|
100
|
102
|
Refrigerator
|
Vediocon
|
150
|
103
|
Ipod
|
Apple
|
75
|
104
|
Mobile
|
Nokia
|
50
|
database table "order_items";
order_id
|
product_id
|
total_units
|
customer
|
5100
|
104
|
30
|
Infosys
|
5101
|
102
|
5
|
Satyam
|
5102
|
103
|
25
|
Wipro
|
5103
|
101
|
10
|
TCS
|
SQL
Joins can be classified into Equi join and Non Equi join.
1) SQL Equi joins
It is a
simple sql join condition which uses the equal sign as the comparison operator.
Two types of equi joins are SQL Outer join and SQL Inner join.
For example: You can
get the information about a customer who purchased a product and the quantity
of product.
2) SQL Non equi joins
It is a
sql join condition which makes use of some comparison operator other than the
equal sign like >, <, >=, <=
1) SQL Equi Joins:
An equi-join is further classified into two categories:
a) SQL Inner Join
b) SQL Outer Join
a) SQL Inner Join
b) SQL Outer Join
a) SQL Inner Join:
All the
rows returned by the sql query satisfy the sql join condition specified.
For example: If you
want to display the product information for each order the query will be as
given below. Since you are retrieving the data from two tables, you need to
identify the common column between these two tables, which is theproduct_id.
The
query for this type of sql joins would be like,
SELECT order_id, product_name, unit_price,
supplier_name, total_unitsFROM product, order_itemsWHERE order_items.product_id =
product.product_id;
The
columns must be referenced by the table name in the join condition, because
product_id is a column in both the tables and needs a way to be identified.
This avoids ambiguity in using the columns in the SQL SELECT statement.
The
number of join conditions is (n-1), if there are more than two tables joined in
a query where 'n' is the number of tables involved. The rule must be true to
avoid Cartesian product.
We can
also use aliases to reference the column name, then the above query would be
like,
SELECT o.order_id, p.product_name,
p.unit_price, p.supplier_name,
o.total_unitsFROM product p, order_items WHERE o.product_id = p.product_id;
b) SQL Outer Join:
This sql
join condition returns all rows from both tables which satisfy the join condition
along with rows which do not satisfy the join condition from one of the tables.
The sql outer join operator in Oracle is ( + ) and is used on one side of the
join condition only.
The
syntax differs for different RDBMS implementation. Few of them represent the
join conditions as "sql left outer join", "sql right outer
join".
If you
want to display all the product data along with order items data, with null
values displayed for order items if a product has no order item, the sql query
for outer join would be as shown below:
SELECT p.product_id, p.product_name,
o.order_id, o.total_unitsFROM order_items o, product p WHERE o.product_id (+) = p.product_id;
The
output would be like,
product_id
|
product_name
|
order_id
|
total_units
|
-------------
|
-------------
|
-------------
|
-------------
|
100
|
Camera
|
||
101
|
Television
|
5103
|
10
|
102
|
Refrigerator
|
5101
|
5
|
103
|
Ipod
|
5102
|
25
|
104
|
Mobile
|
5100
|
30
|
NOTE:If the (+) operator is used in
the left side of the join condition it is equivalent to left outer join. If
used on the right side of the join condition it is equivalent to right outer
join.
SQL Self Join:
A Self
Join is a type of sql join which is used to join a table to itself,
particularly when the table has a FOREIGN KEY that references its own PRIMARY
KEY. It is necessary to ensure that the join statement defines an alias for
both copies of the table to avoid column ambiguity.
The
below query is an example of a self join,
SELECT a.sales_person_id, a.name,
a.manager_id, b.sales_person_id, b.nameFROM sales_person a, sales_person bWHERE a.manager_id = b.sales_person_id;
2) SQL Non Equi Join:
A Non
Equi Join is a SQL Join whose condition is established using all comparison
operators except the equal (=) operator. Like >=, <=, <, >
For example: If you
want to find the names of students who are not studying either Economics, the
sql query would be like, (lets use student_details table defined earlier.)
SELECT first_name, last_name, subjectFROM student_detailsWHERE subject != 'Economics'
The
output would be something like,
first_name last_name subject Bhagwat Anajali Maths Gowda Shekar Maths Sharma Rahul Science Fleming Stephen Science
Views:-
Triggers
:-
Union
:-
Stored
Procedure VS Functions:-
Functions :-
Cursor
:-
Let me say one thing: DON'T use cursors. They should be your preferred way of killing the performance of an entire system. Most beginners use cursors and don't realize the performance hit they have. They use memory; they lock tables in weird ways, and they are slow. Worst of all, they defeat most of the performance optimization your DBA can do. Did you know that every FETCH being executed has about the same performance of executing a SELECT? This means that if your cursor has 10,000 records, it will execute about 10,000 SELECTs! If you can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster. Beginner SQL programmers find in cursors a comfortable and familiar way of coding. Well, unfortunately this lead to bad performance. The whole purpose of SQL is specifying what you want, not how it should be done. I've once rewritten a cursor-based stored procedure and substituted some code for a pair of traditional SQL queries. The table had only 100,000 records and the stored procedure used to take 40 minutes to process. You should see the face of the poor programmer when the new stored procedure took 10 seconds to run! Sometimes it's even faster to create a small application that gets all the data, proccess it and update the server. T-SQL was not done with loop performance in mind. If you are reading this article, I need to mention: there is no good use for cursors; I have never seen cursors being well used, except for DBA work. And good DBAs, most of the time, know what they are doing. But, if you are reading this, you are not a DBA, right?
declare cur cursor for
select id from tbl
open cur
declare @id int
fetch next from cur into @id
while (@@FETCH_STATUS = 0)
begin
print(@id)
fetch next from cur into @id
end
close cur
deallocate cur
Transactions
, Commit, Rollback :-
|
Comments
Post a Comment