FAQ on Indexes in SQL

FAQ on Indexes in SQL

28. What is Index? It's purpose?
Indexes in databases are similar to indexes in books. In a database,
an index allows the database program to find data in a table without
scanning the entire table. An index in a database is a list of values
in a table with the storage locations of rows in the table that
contain each value. Indexes can be created on either a single column
or a combination of columns in a table and are implemented in the form
of B-trees. An index contains an entry with one or more columns (the
search key) from each row in a table. A B-tree is sorted on the search
key, and can be searched efficiently on any leading subset of the

search key. For example, an index on columns A, B, C can be searched
efficiently on A, on A, B, and A, B, C.

29. Explain about Clustered and non clustered index? How to choose
between a Clustered Index and a Non-Clustered Index?
There are clustered and nonclustered indexes. 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 nonclustered 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 nodes of a nonclustered index does not consist
of the data pages. Instead, the leaf nodes contain index rows.
Consider using a clustered index for:
o Columns that contain a large number of distinct values.
o Queries that return a range of values using operators such as
BETWEEN, >, >=, <, and <=. o Columns that are accessed sequentially. o Queries that return large result sets. Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences: o The data rows are not sorted and stored in order based on their non-clustered keys. o The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value. o Per table only 249 non clustered indexes. 30. Disadvantage of index? Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. 31. Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages? A: Only 1 clustered index is possible.


32. How can I enforce to use particular index?
You can use index hint (index=) after the table name.
SELECT au_lname FROM authors (index=aunmind)

33. What is Index Tuning?
One of the hardest tasks facing database administrators is the
selection of appropriate columns for non-clustered indexes. You should
consider creating non-clustered indexes on any columns that are
frequently referenced in the WHERE clauses of SQL statements. Other
good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that
cover all of the columns used by certain frequently issued queries.
These queries are referred to as "covered queries" and experience
excellent performance gains.
Index Tuning is the process of finding appropriate column for
non-clustered indexes.
SQL Server provides a wonderful facility known as the Index Tuning
Wizard which greatly enhances the index selection process.

34. Difference between Index defrag and Index rebuild?
When you create an index in the database, the index information used
by queries is stored in index pages. The sequential index pages are
chained together by pointers from one page to the next. When changes
are made to the data that affect the index, the information in the
index can become scattered in the database. Rebuilding an index
reorganizes the storage of the index data (and table data in the case
of a clustered index) to remove fragmentation. This can improve disk
performance by reducing the number of page reads required to obtain
the requested data
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the
specified table or view.
**

35. What is sorting and what is the difference between sorting &
clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to
8,060 bytes. This will happen by the time when we retrieve data from

database. Clustered indexes physically sorting data, while
inserting/updating the table.

36. What are statistics, under what circumstances they go out of date,
how do you update them?
Statistics determine the selectivity of the indexes. If an indexed
column has unique values then the selectivity of that index is more,
as opposed to an index with non-unique values. Query optimizer uses
these indexes in determining whether to choose an index or not while
executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added,
changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE
statement and then repopulated
3) Database is upgraded from a previous version

37. What is fillfactor? What is the use of it ? What happens when we
ignore it? When you should use low fill factor?
When you create a clustered index, the data in the table is stored in
the data pages of the database according to the order of the values in
the indexed columns. When new rows of data are inserted into the table
or the values in the indexed columns are changed, Microsoft® SQL
Server™ 2000 may have to reorganize the storage of the data in the
table to make room for the new row and maintain the ordered storage of
the data. This also applies to nonclustered indexes. When data is
added or changed, SQL Server may have to reorganize the storage of the
data in the nonclustered index pages. When a new row is added to a
full index page, SQL Server moves approximately half the rows to a new
page to make room for the new row. This reorganization is known as a
page split. Page splitting can impair performance and fragment the
storage of the data in a table.
When creating an index, you can specify a fill factor to leave extra

gaps and reserve a percentage of free space on each leaf level page of
the index to accommodate future expansion in the storage of the
table's data and reduce the potential for page splits. The fill factor
value is a percentage from 0 to 100 that specifies how much to fill
the data pages after the index is created. A value of 100 means the
pages will be full and will take the least amount of storage space.
This setting should be used only when there will be no changes to the
data, for example, on a read-only table. A lower value leaves more
empty space on the data pages, which reduces the need to split data
pages as indexes grow but requires more storage space. This setting is
more appropriate when there will be changes to the data in the table.

Read More

SQL Queries FAQ

SQL Queries FAQ

T-SQL Queries
1. 2 tables
Employee Phone
empid
empname
salary
mgrid empid
phnumber

2. Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))


3. Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))

4. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

5. Display all managers from the table. (manager id is same as emp id)
SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee))

6. Write a Select statement to list the Employee Name, Manager Name
under a particular manager?
SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid

7. 2 tables emp and phone.
emp fields are - empid, name
Ph fields are - empid, ph (office, mobile, home). Select all employees
who doesn't have any ph nos.
SELECT *
FROM employee LEFT OUTER JOIN
phone ON employee.empid = phone.empid
WHERE (phone.office IS NULL OR phone.office = ' ')
AND (phone.mobile IS NULL OR phone.mobile = ' ')
AND (phone.home IS NULL OR phone.home = ' ')

8. Find employee who is living in more than one city.
Two Tables:Emp City
Empid Empid
empName City
Salary
SELECT empname, fname, lname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city
GROUP BY empid
HAVING COUNT(empid) > 1))

9. Find all employees who is living in the same city. (table is same
as above)
SELECT fname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city a
WHERE city IN
(SELECT city
FROM city b
GROUP BY city
HAVING COUNT(city) > 1)))

10. There is a table named MovieTable with three columns - moviename,
person and role. Write a query which gets the movie details where Mr.

Amitabh and Mr. Vinod acted and their role is actor.
SELECT DISTINCT m1.moviename
FROM MovieTable m1 INNER JOIN
MovieTable m2 ON m1.moviename = m2.moviename
WHERE (m1.person = 'amitabh' AND m2.person = 'vinod' OR
m2.person = 'amitabh' AND m1.person = 'vinod') AND (m1.role = 'actor')
AND (m2.role = 'actor')
ORDER BY m1.moviename

11. There are two employee tables named emp1 and emp2. Both contains
same structure (salary details). But Emp2 salary details are incorrect
and emp1 salary details are correct. So, write a query which corrects
salary details of the table emp2
update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid

12. Given a Table named "Students" which contains studentid, subjectid
and marks. Where there are 10 subjects and 50 students. Write a Query
to find out the Maximum marks obtained in each subject.

13. In this same tables now write a SQL Query to get the studentid
also to combine with previous results.

14. Three tables – student , course, marks – how do go @ finding name
of the students who got max marks in the diff courses.
SELECT student.name, course.name AS coursename, marks.sid, marks.mark
FROM marks INNER JOIN
student ON marks.sid = student.sid INNER JOIN
course ON marks.cid = course.cid
WHERE (marks.mark =
(SELECT MAX(Mark)
FROM Marks MaxMark
WHERE MaxMark.cID = Marks.cID))

15. There is a table day_temp which has three columns dayid, day and
temperature. How do I write a query to get the difference of
temperature among each other for seven days of a week?
SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference
FROM day_temp a INNER JOIN
day_temp b ON a.dayid = b.dayid + 1

OR
Select a.day, a.degree-b.degree from temperature a, temperature b
where a.id=b.id+1

16. There is a table which contains the names like this. a1, a2, a3,
a3, a4, a1, a1, a2 and their salaries. Write a query to get grand
total salary, and total salaries of individual employees in one query.
SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid

17. How to know how many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

18. Find duplicate rows in a table? OR I have a table with one column
which has many records which are not distinct. I need to find the
distinct values from that column and number of times it's repeated.
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)

19. How to delete the rows which are duplicate (don't delete both
duplicate records).
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND
b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND
b.age1 = a.age1) > 1
SET ROWCOUNT 0

20. How to find 6th highest salary
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

21. Find top salary among two tables
SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC

22. Write a query to convert all the letters in a word to upper case
SELECT UPPER('test')

23. Write a query to round up the values of a number. For example even
if the user enters 7.1 it should be rounded up to 8.
SELECT CEILING (7.1)

24. Write a SQL Query to find first day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1,
GETDATE())) AS FirstDay
Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

25. Table A contains column1 which is primary key and has 2 values (1,
2) and Table B contains column1 which is primary key and has 2 values
(2, 3). Write a query which returns the values that are not common for
the tables and the query should return one column with 2 records.
SELECT a.col1
FROM a, b
WHERE a.col1 <>
(SELECT b.col1
FROM a, b
WHERE a.col1 = b.col1)
UNION
SELECT b.col1
FROM a, b
WHERE b.col1 <>
(SELECT a.col1
FROM a, b
WHERE a.col1 = b.col1)

26. There are 3 tables Titles, Authors and Title-Authors. Write the
query to get the author name and the number of books written by that
author, the result should start from the author who has written the
maximum number of books and end with the author who has written the
minimum number of books.

27.
UPDATE emp_master
SET emp_sal =
CASE
WHEN emp_sal > 0 AND emp_sal <= 20000 THEN (emp_sal * 1.01) WHEN emp_sal > 20000 THEN (emp_sal * 1.02)
END

Read More

SQL Injection Problem, Example and Solution for Preventing

SQL Injection Problem, Example and Solution for Preventing: 

What is SQL Injection Problem? SQL injection is a strategy for attacking databases.


Example of SQL Injection Problem:An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'


It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --


When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''


This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.


How to Prevent SQL Injection Problem. 
There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere.

Read More

Asp.net Connectivity with SQL Server 2005

Asp.net Connectivity with SQL Server 2005
You will get number of queries on forums for connecting application to database. Most of time they are unable to connect because of incorrect connection string.


Step 1: Adding connection string code to web.config file


Syntax for connection string

;Initial catalog =[dbname];user id=[username];password=[password];" />

Example for connection string


Here, its an example of Windows Authentication Mode

Step 2: Writing Code


Lets write code to display data in gridview.

{
string sqlconn = ConfigurationManager.AppSettings["conn"].ToString();
SqlConnection conn = new SqlConnection(sqlconn);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", sqlconn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}

Solution for everyone, anytime

For all who are facing problem while connection application to backend, I would
suggest them to connect the application SqlDataSource Control and configure the
datasource through wizard, so that a misprint of character in your connection
string can be avoided and you can be on work without posting query on forum.

Finally Exception that you might face while connecting to .net application with sql server 2005 on windows vista operating system.

Asp.net connectivity with Sql Server 2005 is same as connecting to Sql Server
2000, but you can be put into trouble due to incorrect connection string and may
receive following error exception


The user is not associated with a
trusted SQL Server connection.


A connection was successfully
established with the server, but then an error occurred during the login
process.


To resolve the above error, check that connection string
you have used for connectivity is correct.

Read More

SQL OPERATORS

The Operators Supported by SQL*Plus are as under
1) Comparision Operators
2) Logical Operators
3) Arithmetic Operators
4) Operators used to negate conditionsThey are discussed in brief as under.

SQL COMPARISION OPERATORSComparision Operators as the name sujjests are used to test values in SQL statement.
The comparision operators are as under
* Equality (=)
* Non-Equality (<>)
* Greater-than (>)
* Less-than (<) * Greater-than or equal to (>=)
* Less-than or equal to (<=) All Operators Works according to their names.

For example:-

1)SQL> select * from emp
where
emp_name='RAHUL';
->It will select all details of employee whose name is rahul.

2) SQL> select emp_name from emp
where
emp_age > 50;
-> It will list names of those employee whose age is above 50 yrs.

3) SQL> select emp_name from emp
where
emp_job = 'ACCOUNTANT' OR emp_job= 'MUNIM';
->It will list names of those employee whose status is munim or accountant in the company.
->Note, if any of condition satisfies than the record will display as they are joint using or operator(discussed later).


SQL LOGICAL OPERATORS-> Logical operators are those operators that are use SQL keywords to make comparision instead of symbols.
->A Logical operator is used to combine the results of two conditions.
The Logical Operators are AND, OR and NOT.
They also covers
* LIKE
* BETWEEN
* IN
* IS NULL
* ALL and ANY

AND- AND operator display records only when both conditions are satisfied.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs and name is sajid

OR- OR operator display records on matching of any condition.
eg:
SQL>select * from emp
where
emp_age < emp_name="'SAJID';">The above eg shows rows for those employee whose age is less than 50 yrs or name is sajid.

NOT- explained under negate operators.

LIKE
->LIKE operator is used to search a character pattern, without knowing exact character value.
->The LIKE operator used wildcard operators to compare a value to similar values.
->There are two wildcards used in conjunction with LIKE operator.
( % ) The percent sign-The percent sign represents Zero, one
or multiple characters.
( _ ) The Underscore-The underscore represents single
number or characters.
->These symbols can be used in combinations.
eg:
1)SQL> select * from emp
where
emp_name LIKE 'A%';
->Finds any name that start with A and continues for any length.

2) SQL>select * from emp
where
emp_name LIKE '%A';
->Finds any name that ends with A.

3) SQL>select * from emp
where
emp_name LIKE '%A%';
->Finds any name that have A in any position.

4) SQL>select * from emp
where
emp_salary LIKE '3___5';
->Finds any value in a five digit number that starts with 3 and end with 5.

5) SQL>select * from emp
where
emp_salary LIKE '_5%5';
->Finds any values that have a 5 in the second position and ends with a 5.


BETWEEN->The BETWEEN operator is an extension of comparision operator as to make more user friendly.
->The BETWEEN operator is used to search for values whose minimum and maximum values are given. Maximum and minimum values are included as a part of search.
-> A Between operator can be used with both character and
numeric datatypes. However, one cannot mix the data types.
eg:
1)SQL>select * from emp
where
emp_salary BETWEEN 5000 AND 10000;
->It searches for those employee whose salary is lies between 5000 and 10000, including the values 5000 and 10000.


IN->The IN operator search the value from a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
IN ('ACCOUNTANT','PROGRAMMER','OPERATOR');
->It will display all details of those employee whose status in company is either accountant, programmer or operator.Note IN operator is basically use to remove multiple OR operators. The above statement using OR operator will be
emp_job='ACCOUNTANT' OR emp_job='PROGRAMMER' OR emp_job='OPERATOR';
Note here emp_job is repeated three times which is not efficient way of generating query, so using IN operator we can reduce our burden and also make our statement more efficient.

2)SQL>select * from emp
where
emp_depart_no IN ('5','10','15','20');
->It will display details of those employee whose department number is either 5,10,15 or 20.


IS NULL->IS NULL operator is used to compare a value with a NULL value.
eg:
->Let search for the employee who haven't have their email-id
1) SQL> select * fromemp
where
email_id IS NULL;
->It will display records of those employee who haven't created their email-id.
NOTE:- If you tried NULL operator to use with equality operator it will not give proper result.consider above eg. if you modify above statement using equality operator it will display improper result.
email_id = NULL does not find a NULL value. You will be prompted with the message no rows selected even though there are rows with a NULL values.

2)SQL>select * from emp
where
emp_salary IS NULL;
->It will display records of those employee whose pay field is NULL. This is consider as invalid's records and should be deleted immediately.


ALL and ANY
ALL->The ALL operator compares a value to all values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ALL
(select emp_salary from emp where emp_job = 'OPERATOR');
->It will compares salary value to see whether it is greater than all salaries of the employee whose status is operator.

2)SQL>select * from emp
where
emp_age <= ALL (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to all employee's age who is staying in CA.

ANY->The ANY operator compares a value to any values in another value set.
eg:
1)SQL>select * from emp
where
emp_salary > ANY
(select emp_salary from emp where emp_job = 'PROGRAMMER');
->It will compares salary value to see whether it is greater than ANY salaries of the employee whose status is programmer.

2)SQL>select * from emp
where
emp_age <= ANY (select emp_age from emp where emp_state='CA'); ->It will compares age value to see whether it is less than or equal to any employee's age who is staying in CA.


SQL ARITHMETIC OPERATORS
->The arithmetic operators are mentioned as under
1) Addition ( + )
2) Subtraction ( - )
3) Multiplication ( * )
4) Division ( / )They work same as we have been taught so far.
->Let consider usage of arithmetic operator by following result table It contains field like rno, stname, std, div, totsub, totmarks.
where rno is roll number, stname is student name, std is standard, div is division, totsub is total subjects and totmarks is total marks.

1) To find percentage of all student
SQL>select rno,stname,(totmarks / totsub) as percentage
from result;
->Here help of division operator is taken to get percentage.

CONCEPT OF ALIASES
Aliases - Aliases is a temporary name assign to the field created at
execution of statement or field already there in a table.

consider the above example, field is created at the execution of statement and the name percentage is aliases for it.
eg:
1)SQL>select ename as employee_name from emp;
->here field is already there in a table and temporary name is created to display field title more user friendly.
->Note aliases is not compulsory on a field it is used just to interface more user friendly with sql statements.

Now back to the discussion of arithmetic operators,
continue with the examples

2)SQL>select prodname as Product_Name,qty as Quantity,
price as Price_per _unit,(qty*price) as Amount
from product
where
prodname = 'SCREW';
->Here query contains aliases Product_Name for prodname, Quantity for qty, Price_per_unit for price and Amount for (qty*price)
->This query display product name, quantity, price and amount for records whose prodname is screw.
->Here Amount is a field which will be created at the time of execution. It uses the multiplication operator.

Similarly we use arithmetic operators according to our requirement but the only point to note is that while using multiple operators operators works according to the precedence.
* and / have higher priority than + and - operator, but we can make priority of + and - higher than * and / by adding parenthesis to the statement.


SQL NEGATE OPERATORS
The Negate Operators reverses the meaning of the logical operators with which it is used.
We have explained some NOT operators which are mentioned as under.
* NOT EQUAL ( <> ) or ( != ).
* NOT BETWEEN
* IS NOT NULL
* NOT LIKE
* NOT IN
NOT EQUAL->Not equal works exactly oposite to the equal operator.
eg:
1)SQL>select * from emp
where
emp_job <> 'ACCOUNTANT';
->Here all records are selected excluding those whose status is accountant in company.

2)SQL>select * from emp
where
emp_salary != 5000;
->Selects all records excluding those whose salary is 5000.
NOTE:- Operator ( <> ) and ( != ) works same.

NOT BETWEEN-> This operator is used negate the between operator.
eg:
1)SQL>select * from emp
where
emp_salary NOT BETWEEN 2000 and 3000.
->It will select all records excluding those whose salary is between 2000 and 3000, including 2000 and 3000.

IS NOT NULL->It will check whether the selected field is not empty(null).
eg:
1)SQL>select * from emp
where
email_id IS NOT NULL;
->Selects records of those employee who have their email-id.

NOT LIKE->The NOT LIKE operator used wildcard operators to compare a value that is not similar.
->It supports both wildcard character that are mentioned in like operators.
eg:
1)SQL> select * from emp
where
emp_name NOT LIKE 'A%';
->Finds any name that do not start with A and continues for any length.

2) SQL>select * from emp
where
emp_name NOT LIKE '%A';
->Finds any name that do not ends with A.

3) SQL>select * from emp
where
emp_salary NOT LIKE '1___0';
->Finds any value in a five digit number that do not starts with 1 and end with 0.

NOT IN->The NOT IN operator search the value that is not mentioned in a predetermined list, if any of the value matches than row will be displayed.
eg:
1)SQL>select * from emp
where
emp_job
NOT IN ('PROGRAMMER','OPERATOR');
->It will display all details of employee excluding those whose status in company is either programmer or operator.

2)SQL>select * from emp
where
emp_depart_no NOT IN ('5','10','15','20');
->It will display details of employee excluding those whose department number is either 5,10,15 or 20.

Read More

TRANSACTION CONTROL LANGUAGE (TCL)

TRANSACTION:-Collection of operation that forms a single logical unit ofwork are called Transactions.

In other words, Transactions are units or sequences of work accomplished in logical order, whether in a manual fashion by a user or automatically by some sort of a database program. In a relational database using SQL, transactions are accomplished using the DML commands, which are already discussed.
A transaction can either be one DML statement or a group of statements. When managing groups of transactions, each designated group of transactions must be successful as one entity or none of them will be successful.
The Following list describes the nature of transactions:
->All transactions have a begining and an end.
->A transaction can be saved or undone.
->If a transaction fails in the middle, no part of the transaction can be saved to the database.




TRANSACTIONAL CONTROLTransactional control is the ability to manage various transactions that may occur within a relational database management system. (Note keep in mind that transaction is group of DML statements).
When a transaction is executed and completes successfully, the target table is not immediately changed, although it may appear so according to the output. When a transaction successfully completes, there are transactional control commands that are used to finalize the transaction.
There are three commands used to control transactions:
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
When transaction has completed, it is not actually taken changes on database, the changes reflects are temporary and are discarded or saved by issuing transaction control commands. Explanatory figure is drawn as under.











TRANSACTIONAL CONTROL COMMANDS
1) COMMIT Command
->The commit command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax
commit [work];


The keyword commit is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.


example
SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To allow changes permanently on database commit command is used.


SQL> COMMIT WORK;
->The above command will made changes permanently on database, since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.




2) ROLLBACK Command->The rollback command is the transactional control command used to undo transactions that have not already been saved to the database. The rollback command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.


Syntax
SQL>rollback [work];


The keyword rollback is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.


example
SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To discards changes made on database rollback command is used.


SQL> ROLLBACK WORK;
->The above command will discards changes made on database,since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.




3) SAVEPOINT Command
->A savepoint is a point in a transaction that you can roll the transaction back to without rolling back the entire transaction.
->Practical example
consider that a person walking and after passing some distance the road is split into two tracks. The person were not sure to choose which track, so before randomly selecting one track he make a signal flag, so that if the track was not the right one he can rollback to signal flag and select the right track. In this example the signal flag becomes the savepoint. Explanatory figure is as under.







Syntax
SQL>SAVEPOINT 
->Savepoint name should be explanatory.

example
->Before deleting the records of employee whose age is above 75, we are not sure that whether we are given work to actually delete the records of employee whose age is above 75yrs or 80yrs. So before proceding further we should create savepoint here if we are been order later than it might create loss of information.
SQL>savepoint orignally;

SQL>delete from emp
where
emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area.

->After some time we are given order to increase employee salary to 10%. We can increase by generating following command. But before that we will make savepoint to our data so incase if the top level management change their mind and order's no increment should be given than we have can simply pass rollback entry achieve present state.
SQL>savepoint increm_sal;

SQL>update emp
set salary=salary + (salary*10);
->It will Increase salary of employee by 10%.

->After sometime top level management decided that salary of only programmer should be increased by 10% than only we have to do is just to pass entry of rollback before salary is updated.

SQL>rollback to increm_sal;
->It will rollback the changes made to emp_salary now we can update salary records for employee who is programmer. If we have dout than we can put savepoint, otherwise savepoint is not compulsory.

SQL>update emp
set salary=salary + (salary*10);
where
emp_status='PROGRAMMER';
->It will increase salary of only programmers.

If all the changes have been taken place and now we have decided that no further changes require and we have to made changes to apply on database permanently than we can simply generate commit command to reflect changes permanently on database.

SQL>commit work;

Read More

DATA CONTROL LANGUAGE (DCL)

INTRODUCTION to DCL

• DATA CONTROL LANGUAGE is known as DCL.
• DCL Statement is used for securing the database.
• DCL Statement control access to database.
• As data is important part of whole database system we must take proper steps to check that no invalid user access the data and invalidate the information created by us. To kept such a kind of watch we must have to execute certain DCL statement.
• Two main DCL statement are Grant and Revoke.

GRANT Statement- Grant privilege(Rights which are to be allocated) is used when we want our database to share with other users, with certain type of right granted to him. Consider that if we want our enduser to have only access privilege to our database, we can grant it by executing command.
- Grant privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.


Syntax:
GRANT PRIVILEGES ON
TO 

Example
1)SQL> grant select on
emp to endusers;
- here emp is the table_name whose access right is being allocated to the user who logged on as endusers.
11)SQL>grant insert,select,delete on
emp to operators;
- here user who logged on as operators are granted access, insertion and deletion right on the database.
111)SQL>grant insert (empno, ename, job) on
emp to endusers;
- In some case, we require to hide the information to particular users, this can be achived by grant as in the above command we want to hide the detail of employee salary to endusers, so by executing above command we can hide the information regarding empsalary to the endusers.


REVOKE Statement- Revoke privilege(Rights which are to be de-allocated) is used when we want our database to stop sharing the information with other users, with certain type of right revoked to him. Consider that if we want our operators to have only access privilege to our database, we can revoke it by executing command.
- Revoke privilege is assigned not only on table object, but also views, synonyms, indexes, sequences,etc.

Syntax
REVOKE PRIVILEGES ON
FROM 

example
1)SQL> revoke insert, delete on
emp from operators;

Read More

DATA MANIPULATION LANGUAGE (DML)

INTRODUCTION to DML

By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.


There are basically two types of DML
i>Procedural DMLs->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs->It requires a user to specify what data is needed without specifying how to get it.


As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.




The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.






SELECT Statement
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.


syntax:
SELECT [DISTINCT ALL] FROM 
WHERE

->keyword ALL is optional by default it consider ALL rows in table.

example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the columns which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.


INSERT Statement->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)

syntax:-
INSERT INTO
(........)
VALUES
(<&Fieldname1>......<&FieldnameN>)
->Where the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.

examples:
1)SQL>Insert into emp
(empno,ename,job)
values
('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp
(empno,job)
values
(&empno,'&amp;job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values
(&empno,'&amp;ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp
(empno,ename,job)
values
('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.


UPDATE Statement
->Update command is used to modify content of table, that to under satisfaction of particular criteria.

syntax:
UPDATE 
SET = 
WHERE
;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.

example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where
job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary + (salary * 10)
where
job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.


DELETE Statement
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.

syntax:
DELETE FROM 
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.

example:
1)SQL>Delete from emp
Where
empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where
job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.

Read More