Dwh && Bi Course Details

Cognos 8BI information Sites

http://www.cognoise.com
http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/cognos-8-bi-installation-1437733?cv=expanded#

http://books.google.co.in/books?id=A55Ng8EMGVQC&pg=PA6&lpg=PA6&dq=cognos+8bi++installation+steps&source=web&ots=Ox36-MKXEL&sig=j74I7_3EGeTXHWM10BWHpGWtOjo&hl=en&sa=X&oi=book_result&resnum=5&ct=result#PPA16,M1

http://cognosforums.com/forums/viewforum.php?f=8

http://www.prometricexam.com/BI0-132.htm

Cognos Installation steps

COGNOS CONFIGURATION

* BEFORE CONFIGURATION:

1. FIRST INSTALL THE SQL SERVER IN THE MIXED MODE (NOT IN WINDOWDS AUTHENTICATION MODE) WITH THE PASWWORD SA.
2. SECOND INSTALL THE COGNOS REPORTNET SOFTWARE CRN AND FWM ONE AFTER OTHER.

NOTE: THE OPRATING SYSTEM SHOULD CONTAIN IIS (INTERNET INFORMATION SERVICE) INSTALLED BEFORE ALL…!

* REPORTNET CONFIGURATION:

1. START->PROGRAM FILES->SQL SERVER->ENTERPRISE MANAGER.
2. ON LEFT HAND SIDE PANEL SELECT MICROSOFT SQL SERVER (EXPAND)-> SERVERGROUP (EXPAND)->WINDOWS NT SERVER (EXPAND).
3. SELECT DATABASES RIGHT CLICK ON IT->NEW DATABASE->NAME IT AS ‘CRN’-> COLLATION NAME AS ‘LATIN1-GENERAL-CI-AS’->CLICK OK->CRN DATABASE IS CREATED.
4. CREATE 2 MORE DATABASES WITH NAME GOSL AND GORT (TO CREATE GOSL, GORT FOLLOW THE ABOVE STEPS WHAT WE HAVE DONE FOR ‘CRN’)->3 NEW DATABASES ARE CREATED.
5. SELECT ‘GORT’ DATABASE RIGHT CLICK ON IT->SELECT ‘ALL TASKS’->CLICK ‘RESTORE DATABASE OPTION’->ONE DIALOG BOX WILL OPENS.
6. SELECT THE BUTTON ‘FROM THE DEVICES’->CLICK ‘SELECT DEVICES’->CLICK ‘ADD’ BUTTON->SELECT THE ‘BROWSE’ BUTTON.
7. PROVIDE THE PATH (C:\PROGAMFILES\COGNOS\CRN\WEBCONTENT\SAMPLES\DATABASE\SQL SERVER\GORT)->CLICK OK-> CLICK OK-> CLICK OK.
8. SELECT THE OPTIONS TAB-> SELECT THE CHECK BOX ‘FORCE RESTORE OVER THE EXISTING DATABASE’->UNDER PHYSICAL DATA FILES WE HAVE TO PROVIDE THE FOLLOWING PATHS INSTEAD OF EXISTING PATHS:
1. C:\PROGRAM FILES\MICROSOFT SQL SERVER\MS SQL\DATA\GORT.mdf
2. C:\PROGRAMFILES\MICROSOFT SQL SERVER\MS SQL\DATA\GORT_log.ldf
9. FOLLOW THE SAME STEPS FOR GOSL DATABASE ASLO (WHAT WE HAVE DONE FOR GORT)

* CONFIGURATION OF REPORT NET SERVER:

1. START->PROGRAM FILES COGNOS REPORTNET-> CLICK ON COGNOS CONFIGURATION.
2. IN THE EXPLORER WINDOW->CLICK ON CONTENT STORE->CORRESPONDING DATABASE PROPERTIES WILL DISPLAYS->PROVIDE DATABASE NAME AS ‘CRN’.
3. CLICK ON USER ID AND PASSWORK->ENTER ‘SA’ FOR USER ID AND PASSWORK->CLICK OK.
4. IN EXPLORER WINDOW CLICK ON COGNOS REPORT NET->HERE SET’DISPATCHED MAXIMUM MEMORY FROM 128 TO 384’-> AFTER THIS SAVE CHANCES MADE-> CLICK ON SAVE BUTTON ON TOOL BAR->CLOSE.
5. IN THIS EXPLORER ONLY WE HAVE TO START REPORT NET SERVER SERVICE FOR THAT-> WE WILL FINE ONE GREEN ARROW BUTTON PRESS THAT BUTTON-> AFTER PRESSING THAT BUTTON IT WILL AUTOMATICALLY CONFIGURES THE SERVER->AFTER COMPLETION OF THAT CLOSE THE EXPLORER.


* CREATION OF VIRTUAL DIRECTORIES:

1. START-> SETTINGS-> CONTROLPANEL->ADMINISTRATIVE TOOLS->INTERNET SERVICE MANAGER.
2. ON LEFT HAND SIDE THE MACHINE NAME WILL APPEAR-> OPEN IT-> EXPAND THE DEFAULT WEBSITE->RIGHT CLICK ON IT->SELECT NEW-> CLICK ON VIRTUAL DIRECTORY->NEXT->IT ASKS FOR ALIAS NAME-> GIVE IT AS ‘CRN’-> NEXT-> CLICK BROWSE->ENTER THE FOLLOWING PATH: (C:\PROGRAMFILES\COGNOS\CRN\WEBCONTENT)->NEXT->BY DEFAULT TWO CHECK BOXES ARE ACTIVATED->UNCHECK THE RUN SCRIPT DIALOG BOX->NEXT FINISH.
3. SELECT ‘CRN’ AND RIGHT CLICK ON IT->SELECT NEW->CLICK ON VIRTUAL DIRECTORY->NEXT-> ALIAS NAME AS ‘CGI-BIN’-> NEXT->PROVIDE FOLLOWING PATH NOW (C:\PROGRAM FILES\COGNOS\CRN\CGI-BIN)->NEXT-> BY DEFAULT TWO CHECK BOXES ARE ACTIVATED->THIS TIME WE NEED TO SELECT THE CHECK BOX ‘EXECUTE’ ALSO -> NEXT-> FINISH.
4. SELECT ‘CRN’ AND RIGHT CLICK ON IT->SELECT NEW->CLICK ON VIRTUAL DIRECTORY->NEXT-> ALIAS NAME AS ‘HELP’-> NEXT->PROVIDE FOLLOWING PATH NOW (C:\PROGRAM FILES\COGNOS\CRN\WEBCONTENT\DOCUMENTATION) ->NEXT-> BY DEFAULT TWO CHECK BOXES ARE ACTIVATED-> UNCHECK THE ‘RUN SCRIPT’ DIALOG BOX->NEXT-> FINISH.
5. UNDER ‘CRN’-> EXPAND SAMPLES FOLDER->SELECT IMAGES FOLDER->RIGHT CLICK ON IMAGES FOLDER->CLICK ON PROPERTIES->SELECT THE CHECK BOX CALLED ‘DIRECTORY BROWSE’ -> CLICK APPLY->OK.
6. TYPE THE FOLLOWING URL IN THE INTERNER EXPLORER (BROWSER)-> http:\localhost\crn.
7. CLICK ON DIRECTORY LINK -> CLICK ON DATASOURCES LINK-> CREATE NEW DATASOURCE (WE WILL FIND ONE DATABASE ICON AT THE RIGHT SIDE TOP OF THE TOOL BAR IN THE EXPLORER) -> PROVIDE NAME AS GOSALES -> CLICK NEXT.
8. SELECT TYPE AS MICROSOFT SQL SERVER -> PROVIDE SERVERNAME AS ‘LOCAL HOST’ AND DATABASE NAME AS ‘GOSL’->SELECT THE CHECK BOX USERID AND PASSWORD THEN ENTER USER ID AND PASSWORK AS ‘SA’ -> TEST-> CLICK OK-> CLICK OK-> CLOSE-> FINISH.
9. AFTER THIS YOU WILL FIND ONE DIALOG BOX (GOSALES DATASOURCE CREATED SUCESSFULLY) THEN CLICK OK
10. SIMILARLY CREATE DATASOURCE FOR GORT (PROVIDE THE NAME AS GORETAILERS) AND FOLLOW THE SAME STEPS WHAT WE HAVE DONE ABOVE FOR GOSL.
11. GO TO HOME PAGE AND CLOSE.

THE END

SQL QUERIES

TABLE SALESPEOPLE

SNUM SNAME CITY COMM
1001 Peel London .12
1002 Serres San Jose .13
1004 Motika London .11
1007 Rafkin Barcelona .15
1003 Axelrod New york .1


TABLE CUST

CNUM CNAME CITY RATING SNUM
2001 Hoffman London 100 1001
2002 Giovanne Rome 200 1003
2003 Liu San Jose 300 1002
2004 Grass Brelin 100 1002
2006 Clemens London 300 1007
2007 Pereira Rome 100 1004


ORDERS

ONUM AMT ODATE CNUM SNUM
3001 18.69 03-OCT-94 2008 1007
3003 767.19 03-OCT-94 2001 1001
3002 1900.10 03-OCT-94 2007 1004
3005 5160.45 03-OCT-94 2003 1002
3006 1098.16 04-OCT-94 2008 1007
3009 1713.23 04-OCT-94 2002 1003
3007 75.75 05-OCT-94 2004 1002
3008 4723.00 05-OCT-94 2006 1001
3010 1309.95 06-OCT-94 2004 1002
3011 9891.88 06-OCT-94 2006 1001













Problems :

1. Display snum,sname,city and comm of all salespeople.
Select snum, sname, city, comm
from salespeople;
2. Display all snum without duplicates from all orders.
Select distinct snum
from orders;
3. Display names and commissions of all salespeople in london.
Select sname,comm
from salespeople
where city = ‘London’;
4. All customers with rating of 100.
Select cname
from cust
where rating = 100;
5. Produce orderno, amount and date form all rows in the order table.
Select ordno, amt, odate
from orders;
6. All customers in San Jose, who have rating more than 200.
Select cname
from cust
where rating > 200;
7. All customers who were either located in San Jose or had a rating above 200.
Select cname
from cust
where city = ‘San Jose’ or
rating > 200;
8. All orders for more than $1000.
Select *
from orders
where amt > 1000;
9. Names and citires of all salespeople in london with commission above 0.10.
Select sname, city
from salepeople
where comm > 0.10 and
city = ‘London’;
10. All customers excluding those with rating <= 100 unless they are located in Rome.
Select cname
from cust
where rating <= 100 or
city = ‘Rome’;

11. All salespeople either in Barcelona or in london.
Select sname, city
from salespeople
where city in (‘Barcelona’,’London’);
12. All salespeople with commission between 0.10 and 0.12. (Boundary values should be excluded)
Select sname, comm
from salespeople
where comm > 0.10 and comm < 0.12;
13. All customers with NULL values in city column.
Select cname
from cust
where city is null;
14. All orders taken on Oct 3Rd and Oct 4th 1994.
Select *
from orders
where odate in (‘03-OCT-94’,’04-OCT-94’);
15. All customers serviced by peel or Motika.
Select cname
from cust, orders
where orders.cnum = cust.cnum and
orders.snum in ( select snum
from salespeople
where sname in 'Peel','Motika'));
16. All customers whose names begin with a letter from A to B.
Select cname
from cust
where cname like ‘A%’ or
cname like ‘B%’;
17. All orders except those with 0 or NULL value in amt field.
Select onum
from orders
where amt != 0 or
amt is not null;
18. Count the number of salespeople currently listing orders in the order table.
Select count(distinct snum)
from orders;
19. Largest order taken by each salesperson, datewise.
Select odate, snum, max(amt)
from orders
group by odate, snum
order by odate,snum;



20. Largest order taken by each salesperson with order value more than $3000.
Select odate, snum, max(amt)
from orders
where amt > 3000
group by odate, snum
order by odate,snum;
21. Which day had the hightest total amount ordered.
Select odate, amt, snum, cnum
from orders
where amt = (select max(amt)
from orders)
22. Count all orders for Oct 3rd.
Select count(*)
from orders
where odate = ‘03-OCT-94’;
23. Count the number of different non NULL city values in customers table.
Select count(distinct city)
from cust;
24. Select each customer’s smallest order.
Select cnum, min(amt)
from orders
group by cnum;
25. First customer in alphabetical order whose name begins with G.
Select min(cname)
from cust
where cname like ‘G%’;
26. Get the output like “ For dd/mm/yy there are ___ orders.
Select 'For ' || to_char(odate,'dd/mm/yy') || ' there are '||
count(*) || ' Orders'
from orders
group by odate;
27. Assume that each salesperson has a 12% commission. Produce order no., salesperson no., and amount of salesperson’s commission for that order.
Select onum, snum, amt, amt * 0.12
from orders
order by snum;
28. Find highest rating in each city. Put the output in this form. For the city (city), the highest rating is : (rating).
Select 'For the city (' || city || '), the highest rating is : (' ||
max(rating) || ')'
from cust
group by city;


29. Display the totals of orders for each day and place the results in descending order.
Select odate, count(onum)
from orders
group by odate
order by count(onum);
30. All combinations of salespeople and customers who shared a city. (ie same city).
Select sname, cname
from salespeople, cust
where salespeople.city = cust.city;
31. Name of all customers matched with the salespeople serving them.
Select cname, sname
from cust, salespeople
where cust.snum = salespeople.snum;
32. List each order number followed by the name of the customer who made the order.
Select onum, cname
from orders, cust
where orders.cnum = cust.cnum;
33. Names of salesperson and customer for each order after the order number.
Select onum, sname, cname
from orders, cust, salespeople
where orders.cnum = cust.cnum and
orders.snum = salespeople.snum;
34. Produce all customer serviced by salespeople with a commission above 12%.
Select cname, sname, comm
from cust, salespeople
where comm > 0.12 and
cust.snum = salespeople.snum;
35. Calculate the amount of the salesperson’s commission on each order with a rating above 100.
Select sname, amt * comm
from orders, cust, salespeople
where rating > 100 and
salespeople.snum = cust.snum and
salespeople.snum = orders.snum and
cust.cnum = orders.cnum
36. Find all pairs of customers having the same rating.
Select a.cname, b.cname,a.rating
from cust a, cust b
where a.rating = b.rating and
a.cnum != b.cnum
37. Find all pairs of customers having the same rating, each pair coming once only.
Select a.cname, b.cname,a.rating
from cust a, cust b
where a.rating = b.rating and
a.cnum != b.cnum and
a.cnum < b.cnum;
38. Policy is to assign three salesperson to each customers. Display all such combinations.
Select cname, sname
from salespeople, cust
where sname in ( select sname
from salespeople
where rownum <= 3)
order by cname;
39. Display all customers located in cities where salesman serres has customer.
Select cname
from cust
where city = ( select city
from cust, salespeople
where cust.snum = salespeople.snum and sname = 'Serres');

Select cname
from cust
where city in ( select city
from cust, orders
where cust.cnum = orders.cnum and
orders.snum in ( select snum
from salespeople
where sname = 'Serres'));
40. Find all pairs of customers served by single salesperson.
Select cname from cust
where snum in (select snum from cust
group by snum
having count(snum) > 1);

Select distinct a.cname
from cust a ,cust b
where a.snum = b.snum and a.rowid != b.rowid;






41. Produce all pairs of salespeople which are living in the same city. Exclude combinations of salespeople with themselves as well as duplicates with the order reversed.
Select a.sname, b.sname
from salespeople a, salespeople b
where a.snum > b.snum and
a.city = b.city;
42. Produce all pairs of orders by given customer, names that customers and eliminates duplicates.
Select c.cname, a.onum, b.onum
from orders a, orders b, cust c
where a.cnum = b.cnum and
a.onum > b.onum and
c.cnum = a.cnum;
43. Produce names and cities of all customers with the same rating as Hoffman.
Select cname, city
from cust
where rating = (select rating
from cust
where cname = 'Hoffman')
and cname != 'Hoffman';
44. Extract all the orders of Motika.
Select Onum
from orders
where snum = ( select snum
from salespeople
where sname = ‘Motika’);
45. All orders credited to the same salesperson who services Hoffman.
Select onum, sname, cname, amt
from orders a, salespeople b, cust c
where a.snum = b.snum and
a.cnum = c.cnum and
a.snum = ( select snum
from orders
where cnum = ( select cnum
from cust
where cname = 'Hoffman'));
46. All orders that are greater than the average for Oct 4.
Select *
from orders
where amt > ( select avg(amt)
from orders
where odate = '03-OCT-94');

47. Find average commission of salespeople in london.
Select avg(comm)
from salespeople
where city = ‘London’;
48. Find all orders attributed to salespeople servicing customers in london.
Select snum, cnum
from orders
where cnum in (select cnum
from cust
where city = 'London');
49. Extract commissions of all salespeople servicing customers in London.
Select comm
from salespeople
where snum in (select snum
from cust
where city = ‘London’);
50. Find all customers whose cnum is 1000 above the snum of serres.
Select cnum, cname from cust
where cnum > ( select snum+1000
from salespeople
where sname = 'Serres');
51. Count the customers with rating above San Jose’s average.
Select cnum, rating
from cust
where rating > ( select avg(rating)
from cust
where city = 'San Jose');
52. Obtain all orders for the customer named Cisnerous. (Assume you don’t know his customer no. (cnum)).
Select onum, odate
from orders

where cnum = ( select cnum
from cust
where cname = ‘Cisnerous’);
53. Produce the names and rating of all customers who have above average orders.
Select max(b.cname), max(b.rating), a.cnum
from orders a, cust b
where a.cnum = b.cnum
group by a.cnum
having count(a.cnum) > ( select avg(count(cnum))
from orders
group by cnum);
54. Find total amount in orders for each salesperson for whom this total is greater than the amount of the largest order in the table.
Select snum,sum(amt)
from orders
group by snum
having sum(amt) > ( select max(amt)
from orders);
55. Find all customers with order on 3rd Oct.
Select cname
from cust a, orders b
where a.cnum = b.cnum and
odate = ‘03-OCT-94’;
56. Find names and numbers of all salesperson who have more than one customer.
Select sname, snum
from salespeople
where snum in ( select snum
from cust
group by snum
having count(snum) > 1 );
57. Check if the correct salesperson was credited with each sale.
Select onum, a.cnum, a.snum, b.snum
from orders a, cust b
where a.cnum = b.cnum and
a.snum != b.snum;
58. Find all orders with above average amounts for their customers.
select onum, cnum, amt
from orders a
where amt > ( select avg(amt)
from orders b
where a.cnum = b.cnum
group by cnum);
59. Find the sums of the amounts from order table grouped by date, eliminating all those dates where the sum was not at least 2000 above the maximum amount.


Select odate, sum(amt)
from orders a
group by odate
having sum(amt) > ( select max(amt)
from orders b
where a.odate = b.odate
group by odate);
60. Find names and numbers of all customers with ratings equal to the maximum for their city.
Select a.cnum, a.cname
from cust a
where a.rating = ( select max(rating)
from cust b
where a.city = b.city);
61. Find all salespeople who have customers in their cities who they don’t service. ( Both way using Join and Correlated subquery.)
Select distinct cname
from cust a, salespeople b
where a.city = b.city and
a.snum != b.snum;

Select cname
from cust
where cname in ( select cname
from cust a, salespeople b
where a.city = b.city and
a.snum != b.snum );
62. Extract cnum,cname and city from customer table if and only if one or more of the customers in the table are located in San Jose.
Select * from cust
where 2 < (select count(*)
from cust
where city = 'San Jose');
63. Find salespeople no. who have multiple customers.
Select snum
from cust
group by snum
having count(*) > 1;

64. Find salespeople number, name and city who have multiple customers.
Select snum, sname, city
from salespeople
where snum in ( select snum
from cust
group by snum
having count(*) > 1);
65. Find salespeople who serve only one customer.
Select snum
from cust
group by snum
having count(*) = 1;

66. Extract rows of all salespeople with more than one current order.
Select snum, count(snum)
from orders
group by snum
having count(snum) > 1;
67. Find all salespeople who have customers with a rating of 300. (use EXISTS)
Select a.snum
from salespeople a
where exists ( select b.snum
from cust b
where b.rating = 300 and
a.snum = b.snum)
68. Find all salespeople who have customers with a rating of 300. (use Join).
Select a.snum
from salespeople a, cust b
where b.rating = 300 and
a.snum = b.snum;
69. Select all salespeople with customers located in their cities who are not assigned to them. (use EXISTS).
Select snum, sname
from salespeople
where exists ( select cnum
from cust
where salespeople.city = cust.city and
salespeople.snum != cust.snum);
70. Extract from customers table every customer assigned the a salesperson who currently has at least one other customer ( besides the customer being selected) with orders in order table.
Select a.cnum, max(c.cname)
from orders a, cust c
where a.cnum = c.cnum
group by a.cnum,a.snum
having count(*) < ( select count(*)
from orders b
where a.snum = b.snum)
order by a.cnum;
71. Find salespeople with customers located in their cities ( using both ANY and IN).
Select sname
from salespeople
where snum in ( select snum from cust

where salespeople.city = cust.city and
salespeople.snum = cust.snum);

Select sname
from salespeople
where snum = any ( select snum
from cust
where salespeople.city = cust.city and
salespeople.snum = cust.snum);
72. Find all salespeople for whom there are customers that follow them in alphabetical order. (Using ANY and EXISTS)
Select sname
from salespeople
where sname < any ( select cname
from cust
where salespeople.snum = cust.snum);

Select sname
from salespeople
where exists ( select cname
from cust
where salespeople.snum = cust.snum and
salespeople.sname < cust.cname);
73. Select customers who have a greater rating than any customer in rome.
Select a.cname
from cust a
where city = 'Rome' and
rating > ( select max(rating)
from cust
where city != 'Rome');
74. Select all orders that had amounts that were greater that atleast one of the orders from Oct 6th.
Select onum, amt
from orders
where odate != '06-oct-94' and
amt > ( select min(amt)
from orders
where odate = '06-oct-94');
75. Find all orders with amounts smaller than any amount for a customer in San Jose. (Both using ANY and without ANY)
Select onum, amt
from orders
where amt < any ( select amt
from orders, cust
where city = 'San Jose' and
orders.cnum = cust.cnum);




Select onum, amt
from orders
where amt < ( select max(amt)
from orders, cust
where city = 'San Jose' and
orders.cnum = cust.cnum);
76. Select those customers whose ratings are higher than every customer in Paris. ( Using both ALL and NOT EXISTS).
Select * from cust
where rating > any (select rating from cust
where city = 'Paris');

Select *
from cust a
where not exists ( select b.rating from cust b
where b.city != 'Paris' and
b.rating > a.rating);
77. Select all customers whose ratings are equal to or greater than ANY of the Seeres.
Select cname, sname
from cust, salespeople
where rating >= any ( select rating
from cust
where snum = (select snum
from salespeople
where sname = 'Serres'))
and sname != 'Serres'
and salespeople.snum(+) = cust.snum;
78. Find all salespeople who have no customers located in their city. ( Both using ANY and ALL)
Select sname
from salespeople
where snum in ( select snum
from cust
where salespeople.city != cust.city and
salespeople.snum = cust.snum);

Select sname
from salespeople
where snum = any ( select snum
from cust
where salespeople.city != cust.city and
salespeople.snum = cust.snum);
79. Find all orders for amounts greater than any for the customers in London.
Select onum, amt
from orders


where amt > any ( select amt
from orders, cust
where city = ‘London’ and
orders.cnum = cust.cnum);
80. Find all salespeople and customers located in london.
Select sname, cname
from cust, salespeople
where cust.city = 'London' and
salespeople.city = 'London' and
cust.snum = salespeople.snum;
81. For every salesperson, dates on which highest and lowest orders were brought.
Select a.amt, a.odate, b.amt, b.odate
from orders a, orders b
where (a.amt, b.amt) in (select max(amt), min(amt)
from orders
group by snum);
82. List all of the salespeople and indicate those who don’t have customers in their cities as well as those who do have.
Select snum, city, 'Customer Present'
from salespeople a
where exists ( select snum from cust
where a.snum = cust.snum and
a.city = cust.city)
UNION
select snum, city, 'Customer Not Present'
from salespeople a
where exists ( select snum from cust c
where a.snum = c.snum and
a.city != c.city and
c.snum not in ( select snum
from cust
where a.snum = cust.snum and
a.city = cust.city));
83. Append strings to the selected fields, indicating weather or not a given salesperson was matched to a customer in his city.
Select a.cname, decode(a.city,b.city,'Matched','Not Matched')
from cust a, salespeople b
where a.snum = b.snum;
84. Create a union of two queries that shows the names, cities and ratings of all customers. Those with a rating of 200 or greater will also have the words ‘High Rating’, while the others will have the words ‘Low Rating’.

Select cname, cities, rating, ‘Higher Rating’
from cust
where rating >= 200
UNION


Select cname, cities, rating, ‘Lower Rating’
from cust
where rating < 200;
85. Write command that produces the name and number of each salesperson and each customer with more than one current order. Put the result in alphabetical order.
Select 'Customer Number ' || cnum "Code ",count(*)
from orders
group by cnum
having count(*) > 1
UNION
select 'Salesperson Number '||snum,count(*)
from orders
group by snum
having count(*) > 1;
86. Form a union of three queries. Have the first select the snums of all salespeople in San Jose, then second the cnums of all customers in San Jose and the third the onums of all orders on Oct. 3. Retain duplicates between the last two queries, but eliminates and redundancies between either of them and the first.
Select 'Customer Number ' || cnum "Code "
from cust
where city = 'San Jose'
UNION
select 'Salesperson Number '||snum
from salespeople
where city = 'San Jose'
UNION ALL
select 'Order Number '|| onum
from Orders
where odate = '03-OCT-94';




87. Produce all the salesperson in London who had at least one customer there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum = salespeople.snum and
cust.city = 'London')
and city = ‘London’;
88. Produce all the salesperson in London who did not have customers there.
Select snum, sname
from salespeople
where snum in ( select snum
from cust
where cust.snum = salespeople.snum and
cust.city = 'London')
and city = 'London';

89. We want to see salespeople matched to their customers without excluding those salesperson who were not currently assigned to any customers. (User OUTER join and UNION)
Select sname, cname
from cust, salespeople
where cust.snum(+) = salespeople.snum;

Select sname, cname
from cust, salespeople
where cust.snum = salespeople.snum
UNION
select distinct sname, 'No Customer'
from cust, salespeople
where 0 = (select count(*) from cust
where cust.snum = salespeople.snum);

SQL Material

DATA ARE RAW FACTS
EX : CUSTOMER NAME IN BANK
TOTAL MARKS OBTAINED BY STUDENT

INFORMATION: AN ORGANIZED DATA IS CALLED INFORMATION
EX : CUSTOMER NAME WITH BALANCES.
STUDENT REG NO WITH TOTAL MARKS.

DATA HIERARCHY:
BIT IT MAY BE EITHER 0 OR 1
IT REPRESENTS THE SMALLEST PART OF INFORMATION THAT COMPUTER CAN PROCESS.

BYTE : IT IS GROUP OF 8 BITS IT REPRESENTS A DIGIT, LETTERS OR SPECIAL SYMBOL.
FILED: IT IS A GROUP OF CHARACTERS.

EMPNO ENAME SAL JOB
100 SMITH 800 CLERK

RECORD: IT IS A GROUP OF LOGICALLY RELATED FIELD.
FILE : GROUP OF LOGICALLY RELATED RECORDS OR IT IS A FLOW OF CHARACTERS.
DATABASE: IT IS A GROUP OF LOGICALLY RELATED DATA.
(OR)
IT IS THE COLLECTION OF DATA STORED IN A FILE ON DISC

CLASSIFICATION OF DATA BASE BASED ON SIZE
· NORMAL DATABASE:<10 GB
· LARGE DATABASE : 10 GB TO 100 GB
· VERY LARGE DATABASE : 100 GB TO 1000 GB
· EXTREMELY LARGE DATABASE :> 1000 GB

FILE MANAGEMENT SYSTEM
FILE : IT IS A STREAM OF CHARACTERS

DISADVANTAGES :
1. NOT SUPPORT VARIABLES DECLARATION TO STORE (OR) HOLD DATA.
2. WASTAGE OF MEMORY DUE TO DATA REDUNDANCY.
3. MULTIPLE USERS CANNOT ACCESS THE SINGLE FILE AT A TIME.
4. SEARCHING IS A DIFFICULT TASK.
5. INFORMATION ACCESS IS SLOW.

DATABASE MANAGEMENT SYSTEM (DBMS)
IT IS A SUIT OF SOFTWARE PROGRAM FOR CREATING, MAINTAINING & MANIPULATING THE DATA IN DATABASE. (OR)
IT IS A COLLECTION OF INTER RELATED DATA AND SET OF PROGRAM TO ACCESS THE DATA

IT ALLOWS THE ORGANIZATION TO STORE THE DATA IN ONE LOCATION FLOW, WHICH MULTIPLE USERS CAN ACCESS, THE DATA.


DIFFERENT DATABASE MODELS:
1. HIERARCHICAL MODEL
2. NETWORK MODEL
3. RELATIONAL MODEL






DATA IS ORGANIZED IN THE FORM OF TREE STRUCTURE WITH ONE LIMITATION THAT IS
“EVERY SUB NODE SHOULD HAVE ONLY ONE ROOT NODE

DRAWBACKS:
1. DATA REDUNDANCY
2. WASTAGE OF MEMORY
3.CROSS COMMUNICATION IS NOT POSSIBLE

2. NET WORK MODEL:
DATA IS ORGANIZED IN THE FORM OF ARBITRARY GRAPHS.
THERE IS NO GUARANTEE FOR ACCESS OF DATABASE WHENEVER THE DATABASE SIZE INCREASES.

CROSS COMMUNICATION IS POSSIBLE IN NDBMS.

IT CANNOT PROVIDE PROPER QUERY FACILITY, SO THAT WE HAVE TO BIG PROGRAMS EVEN FOR SMALL OPERATION.





3. RELATIONAL MODEL
IT USES THE COLLECTION OF TABLES TO REPRESENT BOTH THE DATA AND RELATIONSHIP AMONG THE DATA.

FEATURES OF RELATIONAL MODEL:
1. DATA IS STORED IN TABLES.
2. INTERSECTION OF ROWS AND COLUMNS WILL GIVE ONLY ONE VALUE.
3. RELATION AMONG DATA IS ESTABLISHED LOGICALLY.
4. THERE IS NO PHYSICAL LINK AMONG DATA
5. THERE IS NO DATA REDUNDANCY
6. HIGH SECURITY FOR DATA
7. IT SUPPORTS ANY TYPE OF DATA (EX: NUMBERS, NUMERIC, DATA, CHARACTER, DATE IMAGES ETC).
8. IT SUPPORTS NULL VALUES.
9. SUPPORT CODD RULES
10. IT SUPPORTS INTEGRITY CONSTRAINTS
11. MULTIPLE USERS CAN ACCESS DATA FROM ANY LOCATION.

NULL VALUE: IT IS UNKNOWN, UNASSIGNED AND UN COMPARABLE VALUE.
EX: 500 + NULL=NULL
ANY ARITHMETIC EXPRESSION CONTAINING NULL IS EVALUATED TO NULL
NULL IS NOT EQUAL TO ZERO OR NOT EQUAL TO SPACE.

RELATIONAL DATABSE: A DATABASE BASED ON RELATIONAL MODEL IS CALLED RELATIONAL DATABASE.
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS): IT IS A SUIT OF SOFTWARE PROGRAMS FOR CREATING, MARINATING AND MANIPULATING THE DATA IN THE RELATIONAL DATABASE.



DIFFERENT RDBMS PACKAGES:
Ø ORACLE
Ø SQL SERVER
Ø DB2
Ø SYBASE

SQL SERVER:
SQL SERVER 2000 IS AN RDBMS THAT USES TRANSACT-SQL TO SEND REQUESTS BETWEEN A CLIENT
COMPUTER AND A SQL SERVER 2000 COMPUTER. AN RDBMS INCLUDES DATABASES, THE DATABASE
ENGINE, AND THE APPLICATIONS THAT ARE NECESSARY TO MANAGE THE DATA AND THE COMPONENTS OF
THE RDBMS. THE RDBMS ORGANIZES DATA INTO RELATED ROWS AND COLUMNS WITHIN THE DATABASE.
THE RDBMS IS RESPONSIBLE FOR ENFORCING THE DATABASE STRUCTURE, INCLUDING THE FOLLOWING
TASKS:

■ MAINTAINING THE RELATIONSHIPS AMONG DATA IN THE DATABASE
■ ENSURING THAT DATA IS STORED CORRECTLY AND THAT THE RULES DEFINING DATA RELATIONSHIPS
ARE NOT VIOLATED
■ RECOVERING ALL DATA TO A POINT OF KNOWN CONSISTENCY IN CASE OF SYSTEM FAILURES


VERSIONS OF SQL SERVER:
1. IN 1993 MICRO SOFT AND SYBASE RELEASES 4.2 VERSION OF SQL SERVER FOR WINDOWS NT ENVIRONMENT.
2. 1995à MICROSOFT RELEASES SQL SERVER 6.0
3. 1996à MICROSOFT RELEASES SQL SERVER 6.5
4. 2000à MICROSOFT RELEASES SQL SERVER 2000
LATEST VERSION SQL SERVER 2005 NOT YET RELEASED.

FEATURES OF SQL SERVER 2000:
1. INTRODUCING SUPPORT FOR XML
2. USER DEFINED FUNCTION ARE INTRODUCED.
3. OLAP (ONLINE ANALYTICAL PROCESS) SERVICES AVAILABLE IN SQL SERVER 7.0 ARE NOW CALLED AS SQL SERVER 2000 ANALYSIS SERVICES
4. REPOSITORY COMPONENTS AVAILABLE IN SQL SERVER NOW CALLED META DATA SERVICES.

DATABASE SIZES:
· MS ACCESS à 2 GB
· SQL SERVER – 6.0 à 1 TB
· SQL SERVER – 7.0 à 1,048,516 TB 3
· SQL SERVER – 2000 à 1,048,516 TB 3

DATABASE IN SQL SERVER:
1) SYSTEM DATABASES
2) USERS DEFINED DATABASES
SYSTEM DATABASES: COMPLETE SYSTEM LEVEL INFORMATION SQL SERVER IS STORED IN SYSTEM DATABASES. SQL SERVER USES SYSTEM DATABASES TO OPERATE USER DATABASES.



USER DATABASES: THE DATABASES CREATED BY USER.


NORMALIZATION
ENTITY : A PERSON, PLACE OR EVENT ABOUT WHICH THE INFORMATION IS STORED IS CALLED ENTITY.
EX: EMPLOYEE, DEPARTMENT, STUDENT, CUSTOMER.
ATTRIBUTE: THE CHARACTERISTIC OR PROPERTY THAT DESCRIBES AN ENTITY IS CALLED ATTRIBUTE.
EX : EMPNO, ENAME, JOB ETC ARE ATTRIBUTE OF ENTITY EMPLOYEE
CUSTID, NAME, ADDRESS ARE ATTRIBUTE OF ENTITY CUSTOMER

FUNCTIONAL DEPENDENCY: IN A RELATION R, AN ATTRIBUTE B IS SAID TO BE FUNCTIONALLY DEPENDENT AN ATTRIBUTE A IF THE VALUE IN B IS UNIQUELY IDENTIFY BY THE VALUE IN A.
THE FUNCTION DEPENDENCY OF ‘B’ AN ‘A’ IS REPRESENTED AS FOLLOWS
R: AàB
EX: 1. SSN à NAME, ADDRESS, DATE OF BIRTH
IE A PERSON NAME, ADDRESS, DATE OF BIRTH
FUNCTIONALLY DEPENDENT ON SOCIAL SECURITY NUMBER(SSN)
2. VIN à MAKE, MODEL, COLOR
IE MAKE, MODEL & COLOR OF VEHICLE FUNCTIONALLY
DEPENDS ON VIN (VEHICLE IDENTIFICATION NUMBER)
PARTIAL FUNCTIONAL DEPENDENCY:
PARTIAL FUNCTIONAL DEPENDENCY IS THE FUNCTIONAL DEPENDENCY IN WHICH SOME NON-KEY COLUMNS DEPENDS ON PART OF KEY COLUMN OR PRIMARY COLUMN.

IN THE ABOVE TABLE EMP NAME, SAL DEPENDS ONLY ON EMP NO BUT THE COURSE COMPLETED DAT WILL DEPENDS ON BOTH EMP NO & COURSE TITLE TO IDENTIFY THE ROW UNIQUELY IN A TABLE EMP NO, COURSE TITLE SHOULD BE DECLARED AS PRIMARY KEY COLUMNS. BUT THE NON-KEY ATTRIBUTES NAME, SQL DEPENDS ONLY EMP NO, IE PART OF A KEY COLUMN THIS TYPE OF FUNCTIONAL DEPENDENCY IS CALLED IT PARTIAL FUNCTIONAL DEPENDENCY.


TRANSITIVE DEPENDENCY
FUNCTIONAL DEPENDENCY BETWEEN THE TWO NON-KEY ATTRIBUTES IS CALLED THE TRANSITIVE DEPENDENCY.

Ø IN THE ABOVE TABLE CUSTID IS IDENTIFIED AS KEY COLUMN
Ø BUT REGION DEPENDS ONLY ON SALESMAN, IS CALLED TRANSITIVE DEPENDENCY.
COLUMNS REGION AND SALESMAN IS CALLED TRANSITIVE DEPENDENCY.

NORMALIZATION ; IT IS THE PROCESS OF DECOMPOSING THE RELATIONS (TABLES) IN TO SMALLER AND WELL STRUCTERD RELATIONS TO MINIMIZE THE DATA REDUNDANCY.
(OR)
IT IS THE PROCESS OF DECIDING WHICH ATTRIBUTED HAS TO GROUPED TO AVOID DATA REDUNDANCY.
THERE ARE SOME RULES IN NORMALIZATION EACH RULE IS CALLED NORMAL FORM.

NORMAL FORMS:
1. FIRST NORMAL FORM (1NF)
2. SECOND NORMAL FORM (2NF)
3. THIRD NORMAL FORM (3NF)

FIRST NORMAL FORMS (1NF): MULTI VALUED ATTRIBUTED SHOULD BE REMOVED FROM THE TABLE.
EXPLANATION: IN THE ABOVE TABLE EMP COURSE TITLE, COURSE COMPLETED DATE ARE MULTI VALUED ATTRIBUTES SINCE AT THE INTERSECTIONS OF ROW AND COLUMN THEY WILL GIVE MOVE THAN ON VALUE. TO BRING THE ABOVE TABLE TO INF MULTI VALUED ATTRIBUTED SHOULD BE REMOVED BY FILLING THE EMPTY FIELD WITH SUITABLE DATA AS SHOWN BELOW.

SECOND NORMAL FORM (2NF) : THE RELATION SHOULD BE IN INF AND THE PARTIAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED.
IN THE ABOVE TABLE EMP NO, COURSE TITLE ARE IDENTIFIED AS KEY COLUMN BUT NON-KEY COL LIKE NAME AND SQL WILL DEPENDS.
TO BRING THE TABLE TO SECOND NORMAL FORM THE NORMAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLES AS SHOWN ABOVE.

THIRD NORMAL FORM (3NF): THE TABLE SHOULD BE IN THE 2NF AND TRANSITIVE DEPENDENCY SHOULD BE REMOVED.
EX: CUSTOMER
· CUSTID IS IDENTIFIED AS KEY-COLUMN BUT REGION DEPENDS ON SALESMAN.
· THE FUNCTIONAL DEPENDENCY BETWEEN NON-KEY ATTRIBUTES SALES MAN & REGION IS CALLED TRANSITIVE DEPENDENCY.
· TO BRING THE TABLE TO THIRD NORMAL FORM, TRANSITIVE DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLE AS SHOWN BELOW

SYSTEM AND SAMPLE DATABASE IN SQL SERVER:
WHEN SQL SERVER IS INSTALLED IN A SYSTEM SETUP WILL CREATES THE FOLLOWING SYSTEM AND SAMPLE DATABASE.

SYSTEM DATABASES:
1. MASTER
2. MODEL
3. TEMPDB
4. MSDB
SAMPLE DATABASES:
1. PUBS
2. NORTH WIND
1. MASTER DATABASE : IT CONTAINS SYSTEM LEVEL INFORMATION OF SQL SERVER. ALL LOGINS AND DATABASE INFORMATION IS STORED IN MASTER DATABASE.
2. MODEL DATABASE : IT IS A TEMPLATE FOR USER DATABASES. WHEN USES IS CREATED A DATABASE THE COMPLETE INFORMATION WHICH IS THERE IN MODEL DATA WILL BE COPIED INTO THE USER DATABASE.
3. TEMPDB: IT FOR HOLDING TEMPORARY OBJECTS WHICH ARE CREATED WHEN SQL SERVER STARTS.
4. MSDB: SQL SERVER AGENT FOR SCHEDULING JOBS AND ALERTS USES IT.

PUBS & NORTH WIND: SAMPLE DATABASES



SUB LANGUAGES IN T-SQL COMMANDS:
1. DDL (DATA DEFINITION LANGUAGE)
2. DML (DATA MANIPULATION LANGUAGE)
3. DCL (DATA CONTROL LANGUAGE)

Ø DDL : THESE ARE USED TO CREATE, ALTER AND DROP THE DATABASE OBJECTS.
COMMANDS 1.CREATE
2. ALTER
3. DROP
Ø DML : THESE ARE USED TO INSERT, MODIFY AND DELETE THE DATA IN DATABASE OBJECTS.

à THESE COMMANDS ARE ALSO USED TO RETRIEVE THE DATA IN DB OBJECTS
COMMANDS 1.INSERT
2. UPDATE
3. DELETE
4. SELECT
Ø DCL : THESE ARE USED TO GRAND OR REVOKE THE PERMISSIONS ON DATABASE OBJECTS TO
OTHER USERS
COMMANDS 1.GRANT
2. REVOKE
TABLE IS THE PRIMARY STORAGE UNIT FOR DATA IN DB. MAX NO OF COLUMNS ALLOWED FOR A TABLES ARE 1024, MAX AMOUNT OF DATA ALLOWED PER A ROW 8060 BYTES.
DATA TYPE : THE DATA TYPE SPECIFIES THE TYPE OF DATA THAT CAN BE STORED IN A VARIABLE OR COLUMN OF A TABLE.
DATA TYPE IN SQL SERVER ARE CLASSIFIED INTO TWO TYPES:
1. PREDEFINED DATA TYPES.
2. USER DEFINED DATA TYPES.
PRE DEFINED DATA TYPES:
(a) CHAR [(N)]àFIXED LENGTH DATA TYPE USED TO STORE CHARACTER DATA, STATIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
à DEFAULT SIZE OF N IS 1 BYTE.
EX: 1.@V1 CHAR (5)
2.@V2 CHAR (10)

(b) VARCHAR (N)àVARIABLE LENGTH DATA TYPE. DYNAMIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.

EX: 1. @ A VARCHAR (5)
MEMORY WILL BE ALLOCATED DYNAMICALLY
(c) NCHAR (N)àFIXED LENGTH OF CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000CHARACTERS.
(d) NVARCHAR (N)àVARIABLE LENGTH CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000 CHARACTERS.




NUMERIC DATA TYPE : USED TO STORE THE NUMERIC VALUES

DATATYPE SIZE
BIGINT 8 BYTES
INT 4 BYTES
SMALLINT 2 BYTES
TINYINT 1 BYTES
FLOAT 8 BYTES
REAL 4 BYTES
DATE DATA TYPE: USED TO STORE DATE AND TIME VALUES.

1. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1753 TO 31ST DECEMBER - 9999
à DEFAULT SIZE IS 8 BYTES
EX : IS DOJ DATE TIME.
2. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1990 TO 6TH JUNE - 1979
à DEFAULT SIZE IS 4 BYTES
SQL SERVER SERVICE MANAGER: BY USING THIS DBA CAN STOP, PAUSE OR START THE SERVER.
ENTERPRISE MANAGER: IT IS THE PRIMARY ADMINISTRATIVE TOOL FOR THE ADMINISTRATIVE TASK.
CREATING LOGINS: IN ENTERPRISE MANAGE.
SECURITYà LOGINS
↓ RIGHT CLICK ON THIS
à CLICK ON NEW LOGIN
à SELECT THE SQL SERVER AUTHENTICATION
NAME OF LOGIC
PASSWORD****
CLICK OK
VERIFYING THE PROPERTIES OF LOGIN: CLICK RIGHT CLICK ON LOGIN AND CLICK ON PROPERTIES
DEMO TABLES:
1. EMP TABLE
2. DEPT TABLE

EMP TABLE
EMPNO ENAME JOB MGR SAL COMM. HIREDATE DPTN
7369 SMITH CLERK 7902 800 NULL ----- 20
--- ---- --- --- --- --- ----- ---
--- ---- --- --- --- --- ---- ---
7934 MILLER CLERK 7506 1300 NULL --- 10




DEPT TABLE:
DEPT NO DNAME LOC
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


DDL COMMANDS
CREATE : IT IS USED TO CREATE DATABASE AND ITS OBJECTS LIKE TABLE, VIEWS ETC
à CREATE TABLE STUDENTS ( RNO INT, NAME VARCHAR (20), ADDRESS VARCHAR(50))
[SHIFT+HOME]—COMMANDS IS SELECT
F5à EXECUTE THE COMMAND
DESCRIBING THE STRUCTURE OF A TABLE:
SYNTAX : SP_HELP
EX: SP_HELP STUDENT
àCREATE TABLE DEPT (DEPTNO INT, DNAME VARCHAR(20), LOC VARCHAR(10))
à CREATE TABLE EMP (EMP INT, ENAME VARCHAR(20), JOB VARCHAR(10),SAL MONEY , DEPTNO INT )
à SP_HELP ‘EMP’ (DESCRIBES, STRUCTURE OF EMP TABLE)
à CREATE TABLE[CUST] (CUST ID INT, ENAME VARCHAR(2), SALESMAN CHAR(2))

ALTER: IT IS USED TO ALTER THE STRUCTURE OF EXISTING TABLES, IT IS MAINLY USED IN FOLLOWING CASES.
(1). WHEN USER WANTS TO ADD A COLUMN TO THE EXISTING COLUMN
(2). WHEN USER WANTS TO MODIFY THE COLUMN DEFINITION.
(3). WHEN USER WANTS TO DROP THE COLUMN IN THOSE SITUATIONS ARE USE ALTER TABLE
CREATE TABLE EMP(EMP NO INT, ENAME VARCHAR(20), JOB VARCHAR (10), MGR INT)

ADDING COLUMNS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD DATATYPE (SIZE), DATATYPE(SIZE)

· ALTER TABLE EMPL ADD HIRE_DATE DATE TIME
· ALTER TABLE EMPL ADD SAL INT COMM. IN, DEPTNO IN
· SP_HELP ‘EMP’

MOST RECENTLY ADDED COLUMN IS LAST COLUMN
MODIFYING THE COLUMN DEFINITION:
SYNTAX: ALTER TABLE ALTER COLUMN < COLUMN NAME> DATA TYPE (SIZE)
· ALTER TABLE EMPL ALTER COLUMN ENAME VARCHAR (30)
· ALTER TABLE EMPL ALTER COLUMN JOB VARCHAR (5)
· IT IS POSSIBLE IF COLUMN IS EMPTY (OR) SIZE IS SUFFICIENT FOR EXISTING OF COLUMNS
· ALTER TABLE EMPL ALTER COLUMN ENAME CHAR (20)
· IT IS VALID ENEN IF COLUMN CONSTAINS DATA.
· ALTER TABLE EMPL ALTER COLUMN EMPNO VARCHAR (20)
· IT IS VALID ONLY WHEN COLUMN IS EMPTY
· ALTER TABLE EMPL ALTER COLUMN JOB
· VALID WHEN COLUMN IS EMPTY IT IS VALID

DROPPING THE COLUMN FROM THE TABLE:
SYNTAX :
ALTER TABLE < TABLE_NAME> DROP COLUMN
à ONLY ONE COLUMN CAN BE DROPPED AT A TIME ONCE COLUMN IS DROPPED, THE DATA IN THE COLUMN WILL ALSO REMOVED.
à TABLE SHOULD HAVE AT LEAST ONE COLUMN IE IT IS NOT POSSIBLE TO DROP THE LAST COLUMN FROM THE TABLE.
à ALTER TABLE EMP DROP COLUMN EMPNO
àALTER TABLE DEPT DROP COLUMN LOC
àALTER TABLE DEPT DROP COLUMN DEPTNO
ERROR à SINCE LAST COLUMN CANNOT BE DROPPED FROM THE TABLE

RENAMING THE COLUMN OF TABLE

SYNTAX: SP_RENAME ‘TABLE. OLD-COLUMN-NAME’,’NEW-COLUMN-NAME’
EX: SP_RENAME ‘DEPT.DEPTNO’, ‘DEPT_NUMBER’
. SP_RENAME ‘EMP.SAL’, ‘SALARY’

RENAMING THE TABLE:
SYNTAX: SP_RENAME ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’
EX: SP_RENAME ‘DEPT’, ‘DEPT_TABLE’

DROPPING THE TABLE:
SYNTAX
DROP TABLE
DROP TABLE STUDENT
DROP TABLE EMP

DML COMMANDS:
1. INSERT: IT IS USED TO INSERT THE DATA INTO THE TABLES.
SYNTAX: INSERT INTO VALUES (VALUE1, VALUE2,….VALUE N)
DATE, VARCHAR, CHAR VALUES SHOULD BE IN SINGLE QUOTATION MARKS.
SINGLE QUOTATION MARKS ARE INVALID PER THE NUMERIC DATA.
àINSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK’)
àINSERT INTO DEPT VALUES(20,’RESEARCH’, ‘DALLAS’)
àINSERT INTO DEPT VALUES(30,’SALES’, ‘CHICAGO’)
àINSERT INTO DEPT VALUES(40,’OPERATIONS’, ‘BOSTON’)






DISPLAYING THE DATA IN THE TABLE:
à SELECT * FROM DEPT

DEPT NO DNAME 10C
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON




à INSERT INTO EMP VALUES(7369,’SMITH’, ‘CLERK’, 7902,’15-MAR-98’,800,100,20)
INSERTING NULL VALUES INTO A TABLE
SYNTAX:
METHOD1:
IMPLICIT METHOD:
SYNTAX
INSET INTO (COLUMN1, COLUMN2,COLUMN3) VALUES
(VALUES1, VALUES2, VALUES3)
EX: INSERT INTO DEPT(DEPTNO, DANME, LOC)VALUES(10, NEWYORK)
INSERT INTO DEPT(DEPT NO) VALUES (20)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NEWYORK
20 NULL NULL

EXPLICIT METHOD:
.EXPLICITLY USER SHOULD PROVIDE THE NULL VALUES FOR THE COLUMNS
SYNTAX:
INSERT INTO VALUES (VALUE1, VALUE2, VALUE3)
EX: INSERT INTO DEPT VALUES(30,NULL, NULL)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NULL
20 NULL NULL
30 NULL NULL
40 OPERATIONS NULL

OPERATORS IN SQL SERVER:
· ARITHMETIC OPERATORS
· COMPARISON OPERATORS
· LOGICAL OPERATORS
· ASSIGNMENT OPERATORS

ARITHMETIC OPERATORS: IT IS USED TO FORM AN EXPRESSION WITH NUMERIC AND DATE VALUES.
OPERATOR + - * / %
MEANING ADD SUB MUL DIV MODULAR

COMPARISON OPERATORS: THESE ARE USED TO CO,PARE ARE EXPRESSION WITH ANOTHER EXPRESSION.
OPERATOR MEANING
> GREATER THAN
>= GREATER THAN OR EQUAL
< LESS THAN
<= LESS THAN OR EQUAL TO
!< NOT LESS THAN
!> NOT GREATER THAN
<> (OR) != NOT EQUAL TO


LOGICAL OPERATORS: THERE ARE USED TO COMBINE THE RESULTS OF 2 EXPRESSIONS INTO SINGLE ONE.
OPERATOR MEANING
AND RETURNS TRUE IF BOTH THE EXPRESSIONS ARE TRUE
OR RETURNS TRUE IF ONE OF THE TWO EXPRESSIONS IS TRUE
NOT RETURNS TRUE IF RESULT IS FALSE OTHER WISE RETURN FALSE.

P Q P AND Q P OR Q NOT P NOT Q
1 1 1 1 0 0
1 0 0 1 0 1
0 1 0 1 1 0
0 0 0 0 1 1

1à TRUE
0à FALSE

ASSIGNMENT OPERATOR: ‘=’ IS THE ONLY ASSIGNMENT OPERATOR AVAILABLE IN SQL SERVER
‘WHERE’ CLAUSE: IT IS A CONDITIONAL CLAUSE USED TO IMPOSE THE CONDITIONS. IT IS USED WITH UPDATE AND SELECT STATEMENT.

UPDATE: USED TO MODIFY THE DATA IN THE TABLE.
SYNTAX: UPDATE SET COLUMN 1=VALUE 1[, COLUMN2= VALUE2….]

* MODIFY THE SALARIES OF ALL THE EMPLOYEES IN EMP TABLE
àUPDATE EMP SCT SAL=SAL+1000
à ALL EMPLOYEES SALARIES ARE UPDATED
à THIS TYPE OF UPDATION IS CALLED HIGH LEVEL UPDATION
*MODIFY THE SALARIES OF ALL MANAGERS
à UPDATE EMP SET SAL=SAL+500 WHERE JOB= ‘SALES MAN’ OR JOB=’ANALYST’
*MODIFY THE SALARIES OF ALL THE CLERKS WORKING IN 20TH DEPARTMENT
àUPDATE EMP SET SAL= SAL+300 WHERE JOB=’CLERK’ AND DEPTNO=20
*MODIFY THE SALARIES OF ALL THE ANALYSTS IN THE BOTH DEPT AND ALL THE MANAGERS IN 30TH DEPT
àUPDATES EMP SET SAL = SAL + 3000 WHERE JOB = ‘ANALYST’ AND DEPTNO = 20 OR JOB= ‘MANAGER’ AND DEPTNO= 30.

DELETE: IT IS USED TO DELETE THE DATA FROM THE TABLE.
SYNTAX:
DELETE FROM [WHERE]
àDELETE FROM DEPT
à ALL RECORDS IN DEPT TABLE ARE DELETED
à THIS IS CALLED HIGH LEVEL DELETION
àDELETE FROM EMP
à ALL RECORD IN EMP TABLE ARE DELETED.
*DELETE ALL CLERKS DETAILS FROM EMP TABLE:
à DELETE FROM EMP WHERE JOB=’CLERK’
· DELETE ‘SMITH’ DETAILS FROM EMP TABLE
àDELETE FROM EMP WHERE ENAME=’SMITH’
· DELETE ALL EMPLOYEE WORKING IN 20 DEPT
* DELETE FROM EMP WHERE DEPT NO=20.
TRUNCATE: IT IS ALSO USED TO DELETE THE DATA FROM THE TABLE .BY USING THIS COMMAND DATA CANNOT BE DELETED CONDITIONALLY. THIS STATEMENT DELETES THE DATA IN PAGE BY PAGE MANNER.
SYNTAX: TRUNCATE
àTRUNCATE EMP
àTRUNCATE DEPT
àTRUNCATE EMP WHERE DEPT NO= 30
ERROR

QUERY: IT IS A REQUEST FOR INFORMATION
SIMPLE SELECT: SELECT COMMAND IS USED TO RETRIEVE THE DATA FROM TABLE(S)
IT CANNOT MODIFY THE DATA IN THE TABLE.
SYNTAX:
SELECT COLUMN 1, COLUMN2, COLUMN 3, ……………. COLUMN N FROKM
SELECT * FROM
‘*’ MEANS ALL COLUMNS AND ALL ROWS IN TABLE
à SELECT EMPO, DEPT NO, ENAME, FROM EMP.
EMPNO DEPTNO ENAME
7369 20 SMITH
7499 30 ALLEN
7521 30 WORD
7934 10 MILLER
NOTE: THE ORDER OF THE COLUMNS IN THE OUTPUT DEPENDS ON THE ORDER OF THE COLUMNS IN THE SELECT STATEMENT
WRITE A QUERY TO DISPLAY THE COMPLETE DATA FROM EMP TABLE
à SELECT * FROM EMP

SELECT WITH WHERE CLAUSE: WHERE CLAUSE IS USED WITH SELECT TO RETRIEVE THE DATA ON THE CONDITION
SYNTAX: SELECT COLUMN 1, COLUMN 2, COLUMN FROM WHERE
(OR)
SELECT * FROM WHERE

WRITE A QUERY TO DISPLAY EMPNO, ENAME ,JOB FOR ALL MANAGERS.
àSELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB=’MANAGER’

WRITE A QUERY TO DISPLAY ALL DETAILS OF 30 DEPT SALES MANS
à SELECT * FROM EMP WHERE JOB= ‘SALES MAN’ AND DEPTNO = 30

WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEE WHO ARE GETTING MORE THAN 3000
à SELECT * FROM EMP WHERE SAL>3000

‘ORDER BY’ CLAUSE: IT IS USED TO DISPLAY THE DATA EITHER IN ASCENDING (OR) DESCENDING ORDER.
DEFAULT ORDER IS ASCENDING
IT ALWAYS APPEARS AT THE AND OF SELECT STATEMENT
IT CAN BE USED WITH ANY COLUMN

SELECT WITH ’WHERE’ AND ‘ORDER BY’ CLAUSES :
SYNTAX:
SELECT COL1, COL2 – COLUMN FROM [WHERE CONDITION] ORDER BY COLUMN[ASC]/DESC.
SELECT * FROM [WHERE] ORDER BY COLUMN[ASC] / DESC

WRITE A QUERY TO DISPLAY EMPNO ENAME, SAL FOR EMPLOYEES IN EMP TABLE IN ASCENDING ORDER SALARIES
à SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL [ASC]

WRITE A QUERY TO DISPLAY ALL EMPLOYEE DETAILS IN DESCENDING ORDER OF SALARIES
à SELECT * FROM EMP ORDER BY SAL DESC

WRITE A QUERY TO DISPLAY ALL ENAMES IN ASCENDING ORDER
à SELECT ENAME FROM EMP ORDER BY ENAME
ENAME
ADAMS
ALLEN
BLAKE
----
-----
-----
WARD
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEES IN DESCENDING ORDER OF HIREDATE
à SELECT * FROM EMP ORDER BY HIREDATE

WRITE A QUERY ENAME, SAL, JOB OF ALL 10 DEPT EMPLOYEES IN ASCENDING ORDER OF SALARIES
à SELECT EMPNO, JOB, SAL FROM EMP WHERE DEPTNO=10 ORDER BY SAL

ENAME JOB SAL
MILLER CLERK 1300
OLARK MANAGER 2450
KING PRESIDENT 5000
WRITE A QUERY TO DISPLAY ENAME , JOB, SAL, DEPTNO FOR ALL MANAGER IN DESCENDING ORDER OF SALARIES
à SELECT ENAME, JOB,DEPTNO, SAL FROM EMP WHERE JOB= ‘MANAGER’ ORDER BY SAL DESC

COLUMN ALIAS: IT IS A METHOD OF ASSIGNING THE TEMPORARY NAMES FOR THE COLUMNS WHILE DISPLAYING THE DATA.

ALIAS NAME IMMEDIATELY FOLLOWS THE COLUMN NAME WITH OPTIONAL ‘AS’ KEYWORD BETWEEN COLUMN NAME AND ALIAS NAME.
ALIAS NAME SHOULD BE IN SINGLE QUOTATION MARKS IF IT CONTAINS SPACES AND CHARACTERS OTHER THAN A TO Z, A TO Z, 0 TO 9, $, #, _(UNDER SCORE).

EX: SELECT EMPNO, SAL, COMM. AS COMMISSION FROM EMP
EMPNO SALARY COMMISSION
-------- ---------- ------------------
à SELECT DEPTNO, DNAME AS ‘DEPTNAME’, LOC LOCATION FROM DEPT
DEPTNO DEPTNAME LOCATION


SELECT STATEMENT WITH ARITHMETIC EXPRESSIONS:
WRITE A QUERY TO DISPLAY EMPNO, SAL AS SALARY PER MONTH, SALARY PER YEAR FOR ALL EMPLOYEES
à SELECT EMPNO, SAL ‘SALARY PER YEAR’ FROM AMP

WRITE A QUERY TO DISPLAY EMPNO HRA, DA, PLF & GROSS FOR EMPLOYEES IN EMP TABLE BY SAL AS BASIC.
à SELECT EMPNO, SAL, BASIC SAL *0.45 HRA, SAL*0.35 DA, SAL * 0.15 PF SAL + SAL *0.45+SAL * 0.35 – SAL*0.15 GROSS FROM EMP
EMPNO BASIC HRA DA PF GROSS


FUNCTIONS: FUNCTION IS A PRE DEFINED PROGRAM SEGMENT THAT CARRIES SPECIFIC AND WELL DEFINED TASK.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
3. ROW SET FUNCTIONS

1. SCALAR FUNCTIONS: THIS FUNCTIONS WILL ACTS AN SINGLE VALUE & RETURNS A SINGLE VALUE
CLASSIFICATION OF SCALAR FUNCTIONS:
1. MATHEMATICAL FUNCTIONS
2. STRING FUNCTIONS
3. DATE & TIME FUNCTIONS
4. SYSTEM FUNCTIONS
5. SECURITY FUNCTIONS
6. CURSOR FUNCTIONS
7. SYSTEM STASTICAL FUNCTIONS
8. TEXT AND IMAGE FUNCTIONS
9. CONFIGURATION FUNCTION.
MATHEMATICAL FUNCTIONS:
i) ABSOLUTE: IT RETURN THE ABSOLUTE VALUE OF N
à SELECT ABS(-10.65)
10.65


à SELECT ABS(18.69)
18.67


ii) POWER (M, N): IT RETURN THE M POWER N VALUE
à SELECT POWER(3,2)
9


à SELECT POWER(6,2)
36



iii) SQRT(N): IT RETURN THE SQUARE ROOT VALUE OF N
à SELECT SQRT(2)
1.414






iv) SQUARE (N): IT RETURN THE SQUARE VALUE OF N
à SELECT ABS(9)
81


à SELECT ABS(5)
25


v) ROUND(M, N): IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
à SELECT ROUND(15.143)
15


à SELECT ROUND(16.513)
16


à SELECT ROUND(16.816)
17


à SELECT ROUND(21.132,1)
21.1


à SELECT ROUND(25.143)
25



vi) CEILING: IT (CEILING) RETURNS THE SMALLEST INTEGER GREATER THAN ‘N’
à SELECT CEILING(15,13)
16


vii) FLOOR(N): IT RETURN THE LARGEST INTEGER LESS THAN ‘N’
à SELECT FLOOR(15.13)
14


STRING FUNCTIONS:
i) ASCII (CH): IT RETURNS THE ASCII VALUE OF GIVEN CHARACTER.
à SELECT ASCII(‘A’)
65


à SELECT ASCII(‘A’)
97


à SELECT ASCII(‘0’)
48


à SELECT ASCII(‘’)
32




ii) CHAR(N): IT RETURNS THE CHARACTER FOR GIVEN ASCII VALUE
à SELECT CHAR(65)
A



à SELECT CHAR(98)
B



iii) LOWER (STRING): IT CONVERTS THE UPPER CASE LETTERS IN STRING INTO LOWER CASES
à SELECT LOWER(‘ORACLE’)
ORACLE


iv) UPPER(STRING): IT CONVERTS THE LOWER CASES INTO UPPER CASES
à SELECT UPPER(ORACLE)
ORACLE



à SELECT ENAME, LOWER(ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
v) LEN(STRING): IT RETURNS THE LENGTH OF GIVEN STRING
à SELECT LEN(‘ORACLE’)
6



à SELECT LEN(‘ORACLE APPS’) SPACE ALSO CHARACTER
11


à SELECT ENAME, LEN(‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING) : IT WILL REVERSE THE GIVEN STRING
à SELECT REVERSE(‘ORACLE’)
ELCARO
àSELECT ENAME, REVERSE(ENAME) REV_ENAME FROM EMP
ENAME LENGTH
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING, ‘SEARCHING STRING’, ‘REPLACING STRING’)
à IT WILL REPLACE A SUB STRING IN ASTRING WITH GIVEN STRING
à SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)

LONG


à SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)

BLACK AND BLUE


SUB STRING (‘STRING’, ‘STARTING POSITION’, NO OF CHARACTERS REQUIRED);
IT WILL ENTRACT A SUBSTRING FLOW MAIN STRING
EX:à SELECT SUBSTRING (‘ORACLE’, 1, 3’)
ORA


à SELECT SUBSTRING (‘ORACLE’, 2, 2)

RA



àSELECT ENAME, SUBSTRING(ENAME,1,2) SUN_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL

DATE FUNCTIONS:
GET DATE (); IT RETURNS THE CURRENT DATE AND TIME
à SELECT GETDATE()
2005-4-5: 4:30.15.416

DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART, NUMBER, DATE): IT RETURNS THE DATE ACCORDING TO DATE PART.
à SELECT DATE (YY,1,’2005-3-15’)
2006-3-15



à SELECT DATEADD (MM,1,’1998-4-18’)
1998-4-18


à SELECT DATE ADD(DAY,1,’1999-04-26’)
2006-3-15




DATE DIFF(DATEPART, DATE 1, DATE 2): IT RETURNS THE DIFFERENCE BETWEEN THE DATES ACCORDING TO THE DATE PART
àSELECT DATE DIFF(YEAR, ‘1999-11-5’,’2000-11-15’)
SPECIAL OPERATORS IN SQL SERVER:
1. IN
2. NOT
3. BETWEEN
4. NOTBETWEEN
5. LIKE
6. NOT LIKE
7. IS NULL
1) IN(LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF CLERK, MANAGER AND ANALYST
A) SELECT * FROM EMP WHERE JOB IN(‘CLERK’, ‘MANAGER’,’ANALYST’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF 7369,7839,7466
A) SELECT * FROM EMP WHERE JOB IN(‘7369’,’7839’,’7466’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF SMITH, ALLEN, KING
A) SELECT * FROM EMP WHERE JOB IN(‘SMITH’, ‘ALLEN’, ‘KING’)
2) NOT IN(LIST OF VALUES): IT IS JUST OPPOSITE TO IN. IT DISPLAY ALL THE VALUES IN TABLE, OTHER THAN VALUES SPECIFIED IN LIST
àSELECT * FROM EMP WHERE JOB NOT IN (‘MANAGER’,CLERK’, ‘PRESEDENT’)
à OTHER THAN ‘MANAGER’, ‘CLERK’, ‘PRESEDENT’ JOBS DETAILS WILL BE DISPLAYED
3) BETWEEN: IT WILL DISPLAY THE VALUES SPECIFIED IN THE RANGE
EX: SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000
IT WILL DISPLAY ALL THE EMPLOYEES DETAILS WHO ARE GETTING SALARY MORE THAN OR EQUAL TO 2000 AND LESS THAN OR EQUAL TO 4000.
IT INCLUDES THE RANGE SPECIFIED, SO IT IS CALLED INCLUSIVE OPERATOR.

4) NOT BETWEEN: IT IS OPPOSITE TO ‘BETWEEN’ OPERATOR IT WILL DISPLAY THE VALUES OTHER THAN RANGE SPECIFIED.

Q) WRITE A QUERY TO DISPLAY AU DETAILS WHO ARE GETTING SALARIES LESS THAN 2000 AND MORE THAN 4000
A) SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
NOTE: NOT BETWEEN OPERATOR WILL NOT INCLUDE THE RANGE SPECIFIED, SO IT IS CALLED EXCLUSIVE OPERATOR.

5) LIKE:IT IS USED TO SEARCH FOR PATTERN IN CHARACTERS. IT ASSOCIATED WITH TWO CHARACTERS
(I) _ (UNDERSCORE)
(II) %
HERE % REPRESENTS ZERO OR MORE CHARACTERS AND UNDER SCORE REPRESENTS A SINGLE CHARACTER.
WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’
(B) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S’
ENAME
SMITH
SCOTT
WAQ TO DISPLAY NAMES END WITH ‘S’


(A) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
WAQ TO DISPLAY NAMES CONTAINING ‘LL’ OR TT
àSELECT ENAME FROM EMP WHERE ENAME LIKE %LL% (OR) ENAME LIKE ‘%TT%’
SCOTT
MILLER
ALLEN
WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND CHARACTER
(A) SELECT ENAME LIKE ‘_A%’
WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS
à SELECT ENAME FROM EMP WHERE ENAME LIKE’------‘

6) NOT LIKE: OPPOSITE TO LIKE OPERATOR.

7)IS NULL:IT IS USED TO TEST FOR NULL VALUES.
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE HAVING NULL COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NULL
WAQ TO DISPLAY THE EMPLOYEE WHO ARE NOT HAVING MANAGERS.
(A) SELECT * FROM EMP WHERE MGR IS NULL
8)IS NOT NULL:
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE GETTING COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NOT NULL
WAQ TODISPLAY THE EMPLOYEES WHO ARE HAVING THE MANAGERS
(a) SELECT * FROM EMP WHERE MGR IS NOT NULL
GROUP FUNCTIONS: THESE FUNCTIONS WILL ACTS ON GROUP OF VALUES AT A TIME AND RETURNS A SINGLE VALUES. GROUP FUNCTIONS WILL IGNORE THE NULL VALUES.
GROUP: SET OF RECORDS IN A TABLE
ENTIRE TABLE IS CONSIDERED AS A GROUP OR A TABLE CAN BE DIVIDED INTO NO., OF GROUPS
· SUM: RETURNS SUM OF VALUES IN COLUMN
· AVG: RETURNS THE AVERAGE VALUE FOR ALL VALUES IN COLUMB
· MIN: RETURNS THE MINIMUM VALUE FROM GIVEN COLUMN
· MAX: RETURNS THE MAXIMUM VALUE FROM GIVEN COLUMN
· COUNT: IT COUNT THE NUMBER OF VALUE OR RECORDS IN A GROUP. IT WILL TAKE THREE DIFFERENT PARAMETERS
COUNT(*)àIT CANOT NO OF RECORDS IN GROUP
COUNT(COLUMN_NAME): IT COUNTS NO., OF NOT NULL VALUES IN GIVEN COLUMN, INCLUDING DUPLICATE VALUES.
COUNT( DISTRICT COLUMN_NAME)à IT WILL COUNT NO., OF NOT NULL VALUES IN GIVEN COLUMN BUT IGNORE DUPLICATE VALUES.

TOTAL-SALARY AVERAGE MINIMUM MAXIMUM
29075 2073.21429 800 5000
EX: SELECT SUM (SAL) TOTAL_SAL, MIN(SAL) MIN_SAL MAX(SAL) MAX_SAL FROM EMP WHERE JOB=’CLERK’
TOTAL-SALARY MINIMUM MAXIMUM
29075 800 5000
SELECT SUM(SAL) SUM_SAL MAX(SAL) MAX_SAL FROM EMP WHERE DEPTNO=20
SUM_SAL MAX_SAL
10875 2975
SELECT MIN(SAL) MIN_SAL MAX(SAL), DEPT NO FROM EMP WHERE DEPTNO=30 (INVALID)
NOTE: SUM,.AVG FUNCTIONS WILL ACTS ONLY ON NUMERIC DATA BUT MIN, MAX FUNCTIONS WILL ACTS AN ANY TYPE OF DAYA
à SELECT MIN(ENAME) MIN_NAME, MAX(ENAME) MAX_ENAME FROM EMP
MIN_ENAME MAX_ENAME
ADAMS WARD
à SELECT MIN(HIRE DATE) MIN_DATE, MAX(HIREDATE) MAX_DATE FROM EMP
à SELECT COUNT (*) COUNT (DEPTNO ), COUNT (DISTINCT DEPT NO) FROM EMP
OUTPUT: 14 14 3
GROUP BY CLAUSE: IT IS USED TO DIVIDE THE TABLE INTO GROUPS
SYNTAX: SELECT , FROM [WHERE] GROUP BY GROUP_BY-EXPRESS[ORDER BY COLUMN ASC/DES]

WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPT NO
(A) SELECT DEPT NO, SUM(SAL) TOTAL_SAL, MIN(SAL) MM_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO.
WAQ TO DIVIDE THE TABLE INTO GROUP BASED ON DEPTNO AND DISPLAY 20,30 TH GROUP
(A) SELECT DEPTNO, SUM(SAL), SUM_SAL, AVG(SAL) AVG)SAL, COUNT(*) FROM EMP WHERE JOB IN (‘MANAGER’,’ANALYST’,’CLERK’) GROUP BY JOB

WRITE A QUERY TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO
(A) SELECT JOB, DEPTNO, MIN(SAL), MIN_SAL, MAX_SAL COUNT(*) FROM EMP GROUP BY JOB, DEPTNO.

HAVING CLAUSE: USED TO IMPOSE CONDITION ON GROUPED DATA
SYNTAX:à SELECT , GROUP FUNCTIONS FROM WHERE GROUP BY GROUP_BY_EXP HAVING ORDER BY COLUMN ASC/DES
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO. DISPLAY THE GROUP IF NO., OF RECORDS ARE MORE THAN 1
à SELECT JOB, DEPTNO, MIN(SAL) MIN_SAL, MAX(SAL), MAX_SAL, COUNT_*) FROM EMP GROUP BY JOB, DEPTNO HAVING COUNT(*)>1

JOB DEPTNO MIN-SAL MAX-SAL COUNT(*)
CLERK 20 800 1100 2

WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPTNO & DISPLAY THE GROUPS IF SUM (SAL)> 500
(A) SELECT DEPTNO, SUM(SAL), AVG(SAL) FROM EMP GROUPE BY DEPTNO HAVING SUM(SAL) > 500.

WAQ TO DIVIDE THE TABLE IN GROUP BASED ON JOB& XXX MANAGER, CLERK, SALESMAN IF SUM(SAL)> 4000
(A) SELECT JOB, SUM(SAL) FROM EMP WHERE JOB IN (MANAGER, CLERK, SALESMAN) GROUP BY JOB HAVING SUM (SAL)>4000
SET OPERATORS: SET OPERATORS ARE USED TO COMBINE THE OUTPUTS OF TWO OR MOR QUERIES AS A SINGLE ONE THE QUERIES COMBINED BY THE SET OPERATORS SHOULD HAVE SAME NUMBER OF COLUMSNS AND THEIR CORRESPONDING DATA TYPES SHOULD BE SAME
1. UNION ALL
2. UNION
1. UNION ALL: IT WILL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES IT WILL NOT ELIMINATE DUPLICATE VALUES.
EX: à SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT JOB FROM EMP WHERE DEPTNO = 200

2. UNION: IT WIL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES AND ELIMINATES THE DUPLICATE VALUES
EX: à SELECT JOB FROM EMP WHERE DEPTNO =10 UNION SELECT JOB FROM EMP WHERE DEPTNO =20.

DEPTNO DNAME, LOC , EMPNO ,ENAME ,JOB ,DEPTNO

JOIN: JOIN IS A QUERY THAT COMBINES THE ROWS FROM TWO OR MORE TABLE
TYPES OF JOIN
1. CROSS JOIN
2. INNER JOIN OR QUAL JOIN
3. OUTER JOIN
4. SELF JOIN
CROSS JOIN: IN CROSS JOIN EVERY ROW OF THE FIRST TABLE JOINS WITH EVERY ROW IN SECOND TABLE THE OUTPUT OF THE CROSS JOIN IS CALLED CARTESIAN PRODUCT

DEPT EMP
DNAME ENAME
ACCOUNTING SMITH
RESEARCH ALLEN
SALES
OPERATION MILLER
4 ROWS 14 ROWS
à SELECT ENAME, DNAME FROM EMP, DEPT
( OR)
à SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT
INNER JOIN OR EQUIJOIN:- A JOIN WHICH IS BASED ON EQUALITIES IS CALLED EQUL JOIN TABLES SHOULD HAVE COMMON COLUMN TO PERFORM EQUI JOIN

SYNTAX:-
SELECT TABLE 1, COLUMN 1, TABLE 2, COLUMN 2…… FROM TABLE, TABLE2
WHERE TABLE1 COLUMN = TABLE.COLUMN
(OR)
SELECT TABLE1, COLUMN1, TABLE 2, COLUMN 2,……….
INNER JOIN TABLE1, TABLE2, ON TABLE1. COLUMN = TABLE2. COLUMN

WAQ TO DISPLAY ENAME, JOB, DEPTNO DNAME, LOC FOR ALL EMPLOYEES IN EMP,DEPT TABLE,
A) SELECT ENAME, JOB, EMPDEPTNO, DNAME, LOC FROM EMP, DEPT, WHERE EMP DEPTNO = DET DEPTNO
WAQ TO DISPLAY ENAME, JOB,SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP. DEPTNO = DEPT. DEPTNO AND LOC= ‘NEWYORK’

3) OUTER JOIN: OUTER JOIN IS AN ENTENSION FOR THE EQUI JOIN. IT IS USEFUL TO RETRIVE UNMATCHED RECORDS ALONG WITH MATCHED RECORS.
I) LEFT OUTER JOIN:
SYNTAX:
SELECT TABLE1. COLUMN 1, TABLE 2. COLUMN2,.. FROM TABLE1, TABLE2 WHERE TABLE 1 COLUMN * + TABLE2.COLUMN.
à SELECT TABLE 1. COLUMN 1, TABLE 2. COLUMN2…..
FROM TABLE 1 LEFT OUTER JOIN TABLE 2 ON TABLE 1 COLUMN = TABLE2.COLUMN

IT WILL RETRIVES ALL THE MATCHED RECORDS FROM TABLE AND TABLE2 & UNMATCHED RECORDS FROM TABLE.
WAQ TO RETRIVE THE ALL MATCHED RECORDS AND UNMATCHED RECORDS FROM EMP TABLE
A) SELECT EMPNO, ENAME,EMP-DEPTNO, EDNO, DNAME, LOC ROM EMP, DEPT WHERE EMP DEPTNO *= DEPT.DEPTNO.
II) RIGHT OUTER JOIN:
à SELECT TABLE1.COLUMN1, TABLE.COLUMN2,-- FROM TABLE1, TABLE2
WHERE TABLE1. COLUMN *= TABLE2. COLUMN
à SELECT TABLE.COLUMN1, TABLE2.COLUMN2 TABLE 1 RIGHT OUTER JOIN TABLE ON TABLE1.COLUMN = TABLE 2. COLUMN.
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE 1 & TABLE2 AND UNMATCHED RECORDS FROM TABLE2 (TABLE RIGHT TO THE RIGHT OUTER JOIN KEY WORD)
WAQ TO RETRIEVE ALL MATCHED RECORDS FROM EMP AND DEPT TABLE AND UNMATCHED RECORDS FROM DEPT TABLE.
A) SELECT EMPNO, ENAME, EMP.DEPTNO.EDNO, DEPT.DEPTNO DNO, FROM EMP, DEPT WHERE EMP.DEPTNO=*DEPT.DEPTNO.
(OR)
SELECT EMPO,ENAME, EMP.DEPTNO EDNO, DEPT.DEPTNO DNO, DNAME FROM EMP RIGHT OUTER JOIN DEPT ON EMP. DEPTNO = DEPT.DEPTNO

EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS

FULL OUTERJOIN: SELECT TABLE,.COLUMN, TABLE2.COLUMB
FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN

IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE1 AND TABLE2 AND UNMATCHED RECORDS FROM BOTH TABLES
EX: SELECT EMPO, ENAME, EMP, DEPTNO ENDNO, DEPTNODNO, DNAME FROM EMP FULL OUTER JOIN DEPT ON EMP DEPTNO = DEPT DEPTMN.
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
100 RAM 80 NULL NULL


SELF JOIN : JOINING THE TABLE TO ITSELF IS CALLED SELF JOIN.

EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839


WAQ TO DISPLAY EMPLOYEE NAME AND HIS MANAGER NAME
SELECT WORK ENAME EMPLOYEE, MANAGER ENAME BOSS FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO.

WRITE A QUERY TO DISPLAY ALL EMPLOYEES WHOSE SALARY IS MORE THAN ALLEN SALARY
SELECT B.* FROM EMP A, EMP B WHERE A.ENAME = ‘ALLEN’ AND A.SAL
WAQ TO DISPLAY ALL EMPLOYEE DETAILS WHOSE JOB SAME AS SMITH JOB?
A) SELECT B * FROM EMP A, EMP A, EMPBA WHERE A ENAME AND A JO = B JOB

WAQ TO DISPLAY ALL DETAILS OF ALL EMPLOYEES WHO ARE SENIOR THAN THEIR OWN MGR?
A) SELECT B * FROM EMP A EMPB WHERE B MGR = A.EMP NO. AND B HIRE DATE < A. HIREDATE.

WAQ TO DISPLAY THE DETAILS OF ALL EMPLOYEES WHO ARE WORKING IN SMITH DEPT.
A) SELECT * FROM EMP WHERE ENAME =’SMITH’

à SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’
à SELECT * FROM EMP WHERE DEPTNO = 20.



SUB QUARRIES: SUB QUERY IS A SELECT STATEMENT IN ANOTHER SELECT STATEMENT.
SYNTAX:
SELECT FROM < TABLE_NAME> WHERE < COLUMN> OPERATOR (SELECT FROM )

FIRST INNER QUERY WILL BE EXECUTED BASED ON THE RESULT OF INNER QUERY OUTER QUERY WILL BE EXECUTED.

TYPES OF SUB QUERIES: 1) SINGLE ROW QUERY 2) MULTI ROW SUB QUERY.
SINGLE ROW SUB QUERY : A SUB QUERY WHICH RETURN ONLY ONE VALUE IS CALLED SINGLE ROW SUB QUERY.

EXAMPLES OF SINGLE ROW SUB- QUERY
WAQ TO DISPLAY ALL EMPLOYEES WHOSE JOB IS SAME AS
SELECT * FROM EMP. WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME= ‘FORD’)

WAQ TO DISPLAY ALL EMPLOYEES WHERE SALARIES ARE MORE THAN ‘SCOTT SALARY
A) SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME= ‘SCOTT’)

WAQ TO DISPLAY ALL EMPLOYEES WHOSE SALARIES ARE EQUAL TO MIN SALARY OF EMPTABLE.
A) SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP’)
SELECT DEPTNO, AVG(SAL), AVG_SAL FROM EMP GROUP BY DEPTNO

DEPTNO AVG_SAL
10 2179.666
20 2125
030 1566.66

WRITE A QUERY TO DISPLAY DEPTNO WITH AVG_SAL OF AVG_SAL IS MORE THAN AVG_SAL OF 20TH DEPT
A) SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =20)

WAQ TO DISPLAY ALL EMPLOYEE WHO ARE WORKING AS MANAGER TO OTHER EMPLOYEES
A) SELECT * FROM EMP WHER EMPNO= (SELECT DISTCT MGR FROM EMP)


SPECIAL OPERATORS WITH SUB_QUERIES: THE FOLLOWING SPECIAL OPERATIONS ARE USED WITH SUB_QUERIES, IF SUB QUERY RETURNS MORE THAN ONE VALUES.
1) IN
2) ANY
3) ALL
4) EXISTS
1) IN: (LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST.
A) SELECT * FROM EMP WHERE EMPNO IN (SELECT.DISTRICTMAGR FROM EMP)
2) ANY:
I)> ANY (LIST OF VALUES): IT PICKS THE MINIMUM VALUE FROM THE LIST AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE MORE THAN MIN VALUE IN THE LIST.
EX:
WAQ TO DISPLAY ALL EMPLOYEE DETAILS FROM EMP TABLE IF THEIR SAL MORE MIN SAL OF 10TH DEPT 1300
A) SELECT * FROM EMP MORE MIN SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10,2450,5000,1300)

II) < ANY(LIST OF VALUES): IT PICKS THE MAN VALUE FROM THE LIST AND IT DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MAN VALUE IN THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEES IF THEIR SALARY LESS THAN MANX SALARY OF 20TH DEPT.
A) SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20) 800,1100,2975,3000

(OR)
SELECT * FROM EMP WHERE SAL < (SELECT MAX (SAL) FROM EMP WHERE DEPTNO =20)

3) ALL:
I) > ALL (LIST OF VALUES): IT PICKS THE MAX VALUES FROM THE LIST AND IT WILL DISPLAY ALL VALUES FROM TABLE WHICH ARE MORE THAN MAX VALUES IS THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEE OF THEN SALARY IS MORE THAN MAX SAL OF 20 DEPT.
A) SELECT * FROM EMP WHERE SAL> ALL (SELECT SAL FROM EMP WHERE DEPTNO=20).

II) < ALL (LIST OF VALUES): IT PICK THE MIN VALUE FROM THE LIST AND (1300,2450,5000) AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MIN VALUES IN LIST.

WAQ TO DISPLAY ALL EMPLOYEE IF THE EMPLOYEE SAL IS LESS THAN MIN SAL OF 10TH DEPT
A) SELECT * FROM EMP WHERE SAL< ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10) 2450,1300,5000

a. >ANY à GREATER THAN MIN
b. c. >ANY à MORE THAN MANY
d. EXISTS: IT IS A BOOLEAN AN OPERATOR RETURNS SUCCESS OR FAILURE OF A QUERY.
SELECT JOB, SUM(SAL) SUM_SAL, MIN(SAL)MIN_SAL FROM EMP WHERE JOB=’MANAGER’ GROUP BY JOB AND EXISTS SELECT COUNT(*) FROM EMP WHERE JOB =’MANAGER’ GROUP BY JOB HAVING COUNT (*) > 2.

CO_RELATED SUB QUERIES: IF A SUB QUERY EVALUATED REPEATEDLY ONCE FOR EACH ROW SELECTED BY THE OUTER QUERY SUCH SUB QUERIES ARE CALLED CORRELATED SUB QUERY.
HERE OUTER QUERY WILL BE EXECUTED FIRST, BASED AN THE RESULT OF OUTER QUERY INNER QUERY WILL BE EXECUTED.
IF OUTER QUERY HAS RETRIEVED N RECORDS THE INNER QUERY HAS TO EXECUTE N TIMES.

WRITE QUERY TO DISPLAY EMPLOYEES DETAILS IF THE SALARY OF EMPLOYEE IS MORE THAN AVG_SAL OF THIS DEPT.
SELECT E.EMPNO, E.ENAME. E.SAL, E.DEPTNO FROM EMP E WHERE E.SAL> (SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO)

WAQ TO DISPLAY ALL THE EMPLOYEES WHO ARE SENIORS THAN THEIR OWN MANAGERS.
SELECT E.EMPO, E.ENAME, E.SAL, E.HIREDATE, E.MGR FROM EMP E.
WHERE E.HIREDATE<(SELECT HIREDATE FROM EMP WHERE EMP.EMPNO=E.MGR)
WAQ TO DISPLAY FIRST HIGHEST SALARY IN EMP TABLE
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE 0=(SELECT COUNT(*)) FROM EMP E WHERE EMP SAL
WAQ TODISPLAY 2ND HIGHEST SALARY IN EMP TABLE
SELECT EMPNO, ENAME, JOB,SAL, FROM EMP WHERE 1=(SELECT COUNT (*) ) FROM EMP E WHERE EMP SAL
DATA INTEGRITY:THE RELIABILITY AND ACCURACYOF DATA IS CALLED DATA INTEGRITY.
TYPES OF DATA INTEGRITY:
· ENTITY INTEGRITY
· DOMAIN INTEGRITY
· REFERENTIAL INTEGRITY
· USER DEFINED INTEGRITY

1) ENTITY INTEGRITY: BASIC IDEA BEHIND ENTITY INTEGRITY IS EACH TABLE SHOULD HAVE A COLUMN, THAN UNIQUELY IDENTIFIES ALL REMAINING COLUMNS.
2) DOMAIN INTEGRITY: DOMAIN INTEGRITY REFERS TO THE RANGE OF VALUES ALLOWED PER COLUMN OF A TABLE
3) REFERENTIAL INTEGRITY: REFERENTIAL INTEGRITY REFERS TO THE MAINTENANCE OF RELATIONSHIP BETWEEN TABLES.
4) USER DEFINED INTEGRITY: IT WILL ALLOW THE USER OR ADMINISTRATOR TO ENFORCE A NEW BUSINESS RULE WHICH WILL NOT COMES UNDER ABOVE 3 RULES.
CONSTRAINTS: IT IS A MECHANISM AUTOMATICALLY ACTIVATED WHEN USER PERFORMS DML OPERATIONS ON THE TABLE.

TYPES OF CONSTRAINTS:
1) UNIQUEà IT WILL NOT ALLOW DUPLICATE VALUES BUT ALLOWS NULL VALUES
2) NOT NULLà IT WILL NOT ALLOW THE NULL VALUES.
3) PRIMARYà IT IS A COMBINATION OF UNIQUE, NOT NULL AND CLUSTERED INDEX.
4) CHECKà IT WILL CHECK VALUES PROVIDED FOR COLUMN
5) FOREIGN KEYà IT IS USEFUL TO ESTABLISH PARENT / CHILD OR MASTER / DETAILED RELATION SHIP BETWEEN THE TABLES, FOREIGN KEY COLUMN OF THE CHILD TABLE IS AWAYS LINKED TO EITHER PRIMARY KEY COLUMN OR UNIQUE CONSTRAINT COLUMN OF THE PARENT TABLE.
6) DEFAULTà IT IS USEFUL TO INSERT TO DEFAULT VALUE INTO A COLUMN WHEN USER WILL NOT PROVIDE ANY VALUE WHILE INSERTING THE DATA INTO THE TABLE.

CONSTRAINTS CAN BE ADDED TO TABLE IN TWO LEVELS
1) COLUMN LEVEL 2) TABLE LEVEL
1) COLUMN LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED IMMEDIATELY OF FOR DEFINING THE COLUMN
à ONLY ONE PRIMARY KEY COLUMN IS ALLOWED PER TABLE.
EX:
CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR UNIQUE NO NULL, LOC VARCHAR(10) DEFAULT ‘HYD’)

CREATE TABLE EMP (EMPNO INT PRIMARY KEY, ENAME VARCHAR NOT NULL, JOB VARCHAR(10), MGR INT, HIREDATE DEFAULT GETDATE(), SAL INT CHECK (SAL>=1000), COMM INT, DEPTNO INT REFERENCES DEPT(DEPTNO) AN DEPT DELETE CASCADE.

ADVANTAGE OF ON DELETE CASCADE: WITH OUT SPECIFYING THE ON DELETE CASCADE IT IS NOT POSSIBLE TO DELETE THE RECORD IN THE BASE TABLE IF THERE ARE DEPENDENT RECORDS FROM THE CHILD TABLE FOR THAT RECORD
IF ON DELETE CASCADE IF USED WHEN THE RECORD IN THE BASE TABLE IS DELETED ALL THE DEPENDENT RECORDS IN THE CHILD TABLE WILL BE ALSO BE DROPPED.
à CREATE TABLE INCR(EMPNO INT REFERENCES EMP(EMPNO) ON DELETE CASCADE, AMOUNT DECIMAL (5,2), DATE DATETIME)

DISPLAYING THE NAMES OF THE CONSTRAINS:
SYNTAX: SP_HELP CONSTRAINT

ASSIGNING THE NAMES FOR THE CONSTRAINTS WHILE CREATING THE TABLE:
SYNTAX:à CREATE TABLE (COLUMN1 DATATYPE(SIZE)CONSTRAINT ,COLUMN2DATATYPE(SIZE) CONSTRAINT)
EX:
CREATE TABLE DEPT(DEPTNO INT CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY, DNAME VARCHAR(20) CONSTRAINT DEPT_DNM_UQ UNIQUE, LOC VARCHAR(10)).

à CREATE TABLE EMPL(EMPNO INT CONSTRAINT EMPL_EMPO_PK PRIMARY KEY, ENAME VARCHAR(20) NOT NULL, JOB VARCHAR(10) , MGR INT, SAL INT CONSTRAINT EMPL_SAL_CHK CHECK(SAL>=1000) COMM. INT, DEPTNO INT CONSTRAINT EMPL_DEPTNO_FK REFERENCES DEPT1(DEPTNO) ON DELETE CASCADE)

INSERTING VALUES INTO THE TABLE CONTAINING CONSTRAINTS:
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
INSERT INTO DEPT VALUES(10,’RESEARCH’, ‘DALLAS’)
ERROR DUE TO VIOLATION OF P.K. CONSTRAINT
INSERT INTO DEPT VALUES(10,’ RESEARCH’, ‘DALLAS’)
ERROR DUE TOVIOLATION OF P.K CONSTRAINT
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
ERROR DUE TO TO VIOLATION OF UNIQUE CONSTRAINT

ACTIVATING THE DEFAULT CONSTRAINTS:
INSERT INTO DEPT VALUES(20,’RESEARCH’, DEFAULT)
INSET INTO DEPT(DEPTNO, DNAME) VALUES (30, ‘SALES’)

SELECT * FROM DEPT

INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES (7369,’SMITH’,800)
ERROR DUE TO TO VIOLATION OF CHECK CONSTRAINT
TABLE LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED AFTER DECLARING ALL COLUMNS
CONSTRAINTS ADDED FOR EXISTING TABLE COMES UNDER TABLE LEVEL.
MORE THAN ON COLUMN PER TABLE CAN BE DECLARED AS EITHER PRIMARY KEY OR FOREIGN KEY COLUMNS
DEFAULT AND NOT NULL CONSTRAINTS ARE NOT ALLOWED.

CREATE TABLE DEPT(DEPTNO INT, DNAME, VARCHAR(20) NOT NULL , (10) VARCHAR(10) DEFAULT ‘HYD’ UNIQUE (DNAME), PRIMARY KEY(DEPTNO))

CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR 920) NOT NULL, JOB VARCHAR(10), MAGR INT, HIREDATE DATETIME, SAL INT COMM. INT,. PRIMARY KEY(EMPNO), CHECK(SAL>=1000), FOREIGN KEY(DEPTNO) REFERENCES DEPT (DEPTNO) AN DELETE CASCADE)

ADDING CONSTRAINTS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD CONSTRAINTS TYPE (COLUMN_NAME)

à CREATE EMP, DEPT TABLES WITHOUT CONSTRAINTS
à ALTER TABLE DEPT ADD PRIMARY KEY (DEPTNO)
à ALTER TABLE DEPT ADD UNIQUE (DNAME)
à ALTER TABLE EMP ADD PRIMARY KEY (DNAME)
à ALTER TABLE EMP ADD CHECK (SAL>=1000)
à ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.

ASSIGNING THE CONSTRAINTS FOR EXISTING TABLE WITH USER NAMES:
à CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(20) NOT NULL, 10C VARCHAR(10) CONSTRAINT DEPT_LOC_DF DEFAULT ‘HYD’, CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY(DEPTNO), CONSTRAINT DEPT_DNM_UQ UNIQUE(DNAME).

à CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, HIREDATE DATETIME, SAL INT COMM. INT, DEPTNO INT, CONSTRANT EMP_EMPNO_PK PRIMARY KEY(EMPNO), CONSTRAINT EMP SAL_CHK CHECK(SAL>=1000)
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE)

ADDING CONSTRAINTS FOR EXISTING TABLES WITH USER NAMES:
Q) CREATE EMP AND DEPT TABLE WITHOUT CONSTRAINTS
A) SYNTAX:
àALTER TABLE ADD CONSTRAINT CONSTRAINT TYPE (COLUMN)
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO).
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DNM_UQ UNIQUE (DNAME)
à ALTER TABLE DEPT ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO).
à ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_CHK CHECK (SAL>=1000)
à ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.

DROPPING THE CONSTRAINT:
SYNTAX:
ALTER TABLE DROP CONSTRAINT < CONSTRAINT_NAME>

à ALTER TABLE EMP DROP CONSTRAINT EMP_EMPNO_PK
à CREATE TABLE BANK (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), OPEN_BALANCE INT, OPEN_DATE DATETIME, PRIMARY KEY (ACNO, NAME))
à CREATE TABLE FROM (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), AMOUNT INT, TRAN_DATE DATETIME FOREIGN KEY (ACNO, NAME) REFERENCES BANK (ACNO, NAME) AN DELETE CASCADE)
ACNO NAME
100 ****
200 ****
300 ****
400 ****

.SELF REFERENTIAL INTEGRITY: THE FOREIGN KEY COLUMN OF THE TABLE IS REFERENCE TO THE PRIMARY COLUMN IN THE SAME TABLE
EX: CREATE TABLE EMP_EMP(ENAME INT, ENAME VARCHAR (20), JOB VARCHAR (10), MAGR INT, PRIMARY KEY (EMPNO), FOREIGN KEY (MGR) REFERENCES EMP_EMP(EMPNO)

EMPNO ENAME JOB MGR

à FIRST INSERT DATA INTO EMPNO, ENAME & JOB COLUMNS LATER UPDATE ‘MGR’ COLUMN
à ADDING NOT NULL CONSTRAINT FOR COLUMN OF A TABLE.
SYNTAX: ALTER TABLE ,TABLE_NAME> ALTER COLUMN DATA TYPE (SIZE) NOT NOT NULL
à ALTER TABLE DEPT ALTER COLUMN LOC VARCHAR (10) NOT NULL

DATA STORAGE: DATA OF EACH TABLE IS STORED IN THE COLLECTION OF DIFFERENT DATA PAGES, EACH PAGE WILL HAVE THE 96 BYTES PAGE HEADER. PAGE HEADER CONTAINS PREVIOUS POINTER, NEXT POINTER, AND OBJECT ID.
PREVIOUS POINTER POINTING TO PREVIOUS PAGE, NEXT POINTER POINTING TO NEXT PAGE.
PREVIOUS POINTER VALUE FOR FIST PAGE IS NULL
NEXT POINTER VALUE FOR LAST PAGE IS NULL

DATA ACCESS: SQL SERVER USES FOLLOWING TWO METHODS TO ACCESS DATA
1) TABLE SCAN 2) BY USING INDEX
TABLE SCAN: IT HAS TO START FROM THE BEGINNING OF THE TABLE AND SCAN EACH AND EVERY PAGE FOR THE REQUIRED DATA.
BY USING INDEX: IT WILL TRAVERSE THOUGH THE INDEX TREE STRUCTURE TO FIND THE REQUIRED DATA AND EXTRACT THE DATA THAT SATISFY THE QUERY CRITERIA.
INDEXED: INDEXED IN SQL SERVER IS SIMILAR TO INDEX IN ABOOK. INDEXED ARE USED TO IMPROVE THE PERFORMANCE OF QUERIES.

INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS
1) PRIMARY KEY COLUMN
2) FOREIGN KEY COLUMN: FREQUENTLY USED IN JOIN CONDITIONS.
3) COLUMN WHICH ARE FREQUENTLY USED IN WHERE CLAUSE
4) COLUMNS WHICH ARE USED TO RETRIEVE THE DATA IN SORTING ORDER.

INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:
THE COLUMNS WHICH ARE NOT USED FREQUENTLY USED IN WHERE CLAUSE.
COLUMNS CONTAINING THE DUPLICATE AND NULL VALUES
COLUMNS CONTAINING IMAGES, BINARY INFORMATION, AND TEXT INFORMATION.



TYPES OF INDEXEX:
1) CLUSTERED INDEX
2) NON-CLUSTERED INDEX
3) UNIQUE INDEX
4) COMPOSITE INDEX
1) CLUSTERED INDEX: ONLY ONE CLUSTERED INDEX IS ALLOWED FOR A TABLE. THE ORDER OF VALUES IN A TABLE ORDER OF VALUES IN INDEX IS ALSO SAME. WHEN CLUSTER INDEX IS CREATED ON TABLE DATA IS ARRANGED IN ASCENDING ORDER CLUSTER INDEX WILL OCCUPY 5% OF THE TABLE.
SYNTAX: CREATE CLUSTERED INDEX ON (COLUMNS)
à CREATE CLUSTERED INDEX EMP_CLINDEX ON EMP (EMPNO)
NON_CLUSTERED INDEX: IT IS THE DEFAULT INDEX CREATED BY THE SERVER THE PHYSICAL ORDER OF THE DATA IN THE TABLE IS DIFFERENT FROM THE ORDER OF THE VALUES IN INDEX.
MAX NO. OF NON-CLUSTERD INDEXED ALLOWED FOR TABLE IS 249

SYNTAX:
CREATE NON-CLUSTERED INDEX ON TABLE_NAME < COLUMNS>
CREATE NONCLUSTERED INDEX EMP_SAL ON EMP (DEPTNO, SAL)
UNIQUE INDEX : AN INDEX WITH UNIQUE CONSTRAINT. IT WILL NOT ALLOW DUPLICATE VALUES.
SYNTAX: CREATE UNIQUE INDEX ON < TABLENAME> (COLUMN)
à CREATE UNIQUE INDEX DEPT_INDEX ON DEPT (DNAME)

COMPOSITE INDEX: IF A UNIQUE INDEX IS CREATED ON MORE THAN ONE COLUMN IS CALLED COMPOSITE INDEX.
à CREATE UNIQUE INDEX DEPT_COMPOSITE ON DEPT (DEPTNO, DNAME)

TRANSCACTIONS:
1) AUTO COMMIT TRANSACTION
2) EXPLICIT TRANSACTION
3) IMPLICIT TRANSACATION

1) AUTO COMMIT TRANSATION: EVERY INDIVIDUAL TRANSACTION IS AN AUTO COMMIT TRANSACTION.
EX: UPDATE EMP SET SAL= SAL + 100 WHERE DEPTNO
2) EXPLICIT TRANSACTION: TRANSACTION BEGINS WITH BEGIN TRAN AND ENDS EXPLICITLY WITH EITHER COMMIT OF ROLLBACK
à BEGIN TRAN
INSET INTO DEPT VALUES (10,. ‘ACCOUNTING’, ‘NEWYORK’)
à ROLLBACK
INSERT OPERATION IS CANCELLED

3) IMPLICIT TRANSACTION:
à SET IMPLICIT TRANSACTIONS ON/OFF
àUPDATE EMP SET SAL = SAL + 1000 WHERE JOB = ‘MANAGER’
à DELETE FROM EMP WHERE DEPTNO = 10
à COMMIT

UPDATE AND DELETE OPERATIONS ARE CANCELLED

SAVE POINT: SAVE POINTS ARE LIKE PAPER MARKERS USED TO MARKER THE TRANSACTION.
SYNTAX: SAVE BEGIN FROM
à INSERT INTO EMP (EMPNO,ENAME) VALUES (100, ‘RAM’)
SAVE FROM A
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO=7839
SAVE FROM B
INSET INTO EMP (EMPNO, ENAME) VALUES (200,’***’)
DELETE FROM EMP WHERE EMPNO=7369
ROLLBACK FROM B
à DELETE AND INSERT WILL BE CANCELLED
ROLLBACK FROM A
à UPDATE WILL BE CANCELLED

COMMIT / ROLLBACK
-à INSET WILL BE SAVED / INT WILL BE CANCELLED



PROGRAMMING CONSTRUCTS: PROGRAMMING CONSTRUCTS ARE USED TO EXECUTE THE SET OF SQL STATEMENTS AS A UNIT..
1) IF – ELSE
2) BEGIN – END
3) WHILE
DECLARING VARIABLE: DECLARE @ VARIABLE DATATYPE, @ VARIABLE 2 DATATYPE
DECLARE @A INT, @ VJOB VARCHAR (20)
EX: SET @ A =5
SET @ B =10
SET @ VJOB =’CLERK’
DISPLAYING THE MESSAGE OR VALUE IN THE VARIABLE:
PRINT ‘MESSAGE’
OR
PRINT @ VARIABLE
WRITE A PROGRAM TO FIND THE SUM OF 2 NUMBERS
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ VJOB =@A+@B
PRINT THE SUM OF A&B IS MISTAKE CONVERT (VARCH, @C)
WRITE A PROGRAM TO FIND THE SIMPLE INTEREST
à DECLARE @ P INT, @N DECIMAL(5,2) @R DECIMAL (5,2), @ SI DECIMAL (5,2)
SET @ P =10000
SET @ N =15
SET @ R = 7.5
SET @ SL =(@P * @N * @R)/100
PRINT SIMPLE INTEREST IS + CONVERT (VARCHAR, @SI)

3) WRITE A PROGRAM TO SWAP THE VALUES IN THE VARIABLES.
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ C =@A
SET @ A =@B
SET @ B =@C
PRINT ‘ A=:’+ CONVERT (VARCHAR, @A)
PRINT ‘ B=:’+ CONVERT (VARCHAR, @B)

4) WRITE THE ABOVE PROGRAM WITHOUT USING THE THIRD VARIABLE

IF- ELSE: IT IS USED TO EXECUTE THE STATEMENTS BASED ON CONDITION.
SYNTAX : IF
< STATEMENT 1>
ELSE
< STATEMENT 2>
IF CONDITION IS TRUE THEN STATEMENT1 IS EXECUTED OTHERWISE STATEMENT 2 WILL BE EXECUTED
DECLARE @A INT, @B INT,
SET @ A =5
SET @ B =10
IF(@A>@B)
PRINT ‘B IS BIG’
ELSE
PRINT ‘B IS BIG OR EQUAL TO A’

DECLARE @A INT
SELECT @=COUNT(*) FROM EMP
IF (@A>0)
PRINT ‘TABLE WILL HAVE RECORDS’
ELSE
PRINT ‘NO RECORD IN THE TABLE
DECLARE @A VARCHAR(10)
SELECT @A= JOB FROM EMP WHERE EMPNO =7369
IF (@A=’CLERK’)
PRINT ‘EMPLOYEE WORKING AS CLERK
ELSE
PRINT ‘EMPLOYEE IS :’ +@A

WHILE:
SYNTAX: WHILE(CONDITION)

IF THE CONDITION IS TRUE THEN STATEMENTS UNDER WHICH WILL BE EXCLUDED

WRITE A PROGRAM TO PRINT THE FIRST 10 NO’S USING WHILE
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
PRINT @N
SET @N = @N+1
END
WRITE A PROGRAM TO PRINT ALL EVEN NO’S FROM 1 TO 10
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
IF (@N/2=0)
PRINT @N
SET @N = @N+1
END
VIEWS: VIEWS IS A LOGICAL TABLE BASED ON TABLE OR A VIEW VIEW WILL NOT HOLD NAY DATA OF ITS. IT IS JUST LIKE A WINDOW THROUGH WHICH WE CAN VIEW DATA IN THE TABLE.

TYPES OF VIEWS:
1) SIMPLE VIEW
2) COMPLEX VIEW
SIMPLE VIEW: A VIEW BASED ON SINGLE TABLE IS CALLED A SIMPLE VIEW.
· ALL DML ARE ALLOWED ON THE SIMPLE VIEW
· IT WILL NOT HAVE ANY ARITHMETIC EXPRESSIONS AND GROUP FUNCTIONS.

SYNTAX:
CREATE VIEW AS SELECT STATEMENT [WITH CHECK OPTION]
EX: SELECT * FROM EMP_VIEW
à IT WILL DISPLAY ALL DETAILS EMP TABLES.
INSERT INTO EMP _ VIEW (EMPNO, ENAME, DEPTNO) VALUES (100, RAM, 20)
à UPDATE EMP_VIEW SET SAL = SAL + 100
à DELETE FROM EMP_VIEW WHERE DEPTNO = 10
à ALL 10 DEPT EMPLOYEE DETAILS ARE DELETED FROM TABLE.

THE DML OPERATIONS PERFORMED ON THE VIEW WITH AFFECTS THE TABLE.
EX: CREATE VIEW V10 AS SELECT * FROM EMP WHERE DEPTNO – 10
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (200, ‘JAMES’,10)
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (30, ‘MORTA’,20)
à SELECT * FROM V10
à IT WILL DISPLAY ALL 100 DEPT RECORDS. IT WILL NOT DISPLAY RECENTLY INSERTED 20 DEPT RECORD SINCE IT IS FOCUSING ONLY ON 10 DEPT.
à SELECT * FROM EMP
à ALONG WITH OLD RECORDS NEWLY INSERTED RECORDS WILL ALSO BE DISPLAYED.
à UPDATE VIO SET SAL= SAL+ 1000
à ALL 10 DEPT EMPLOYEES.
SALARIES ARE MODIFIED

CREATING VIEW BASED ON ANOTHER VIEW:
DEPT NO DNAME LOC


à CREATE VIEW DEPT_V1 AS SELECT * FROM DEPT
à SELECT * FROM DEPT_V1



à CREATE VIEW DEPT_V2 AS SELECT DEPTNO, DNAME FROM DEPT_V1
à SELECT * FROM DEPT_V1
DEPT NO DNAME




à CREATE VIEW V1 AS SELECT EMPNO, ENAME, JOB, DEPT FROM EMP
à SELECT * FROM DEPT_V1
EMPNO ENAME JOB DEPTNO

à CREATE VIEW V2 AS SELECT EMPNO, ENAME, SAL FROM V1
CREATE VIEW WITH CHECK OPTION:

à CREATE VIEW V20 AS SELECT * FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION.
à SELECT * FROM V20
à IT WILL DISPLAY ONLY 20 DEPT EMPLOYEE DETAILS
à INSERT INTO V20 (EMPNO, ENAME,DEPTNO) VALUES (111,’ CHRIS’,10)
à THIS RECORDS WILL NOT BE INSERTED SINCE VIEW IS CREATED WILL CHECK OPTION

DISPLAYING THE SELECT STATEMENT ASSOCIATED WITH THE VIEW
SYNTAX:
SP_HELPTEXT
2) COMPLEX VIEWS: A VIEW BASED ON
I) ARITHMETIC EXPRESSIONS
II) GROUP FUNCTIONS
III) MORE THAN ONE TABLE IS CALLED COMPLEX VIEW

EX:
à CREATE VIEW EMP_GROUP AS SELECT DEPTNO, SUM(SAL) SUM_SAL, AVG(SAL) AVG_SAL, MIN(SAL), MIN_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO
à SELECT * FROM EMP_GROUP
DEPTNO SUM_SAL AVG_SAL MIN_SAL MAX_SAL


VIEW BASED ON ARITHMETIC EXPRESSION:
à CREATE VIEW EMP_ARTH AS EMPNO, SAL, BASIC, SAL+0.35, HRA, SAL+0.25, DAA+ 0.15,PF, SAL +0.35+SAL*0.25- SAL*0.15 GROSS FROM EMP

SELECT * FROM EMP_ARTH
DEPTNO BASIC HRA DA PF GROSS


VIEW BASED ON MORE THAN ONE TABLE:
à CREATE VIEW EMP_DEPT AS SELECT ENAME, JOB, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO.
à SELECT * FROM EMP_DEPT

ENAME JOB SAL DNAME LOC
SMITH CLERK 800 RESEARCH DALLAS
ALLEN SALESMAN 1600 SALES CHICAGO
MILLER CLERK 1300 ACCOUNTING NEWYORK

à INSERT INTO EMP_DEPT(ENAME, JOB, SAL, DNAME, LOC) VALUES (‘***’, ‘JI’, 1000, ‘IMPORT’, ‘HYD’)
à ERROR SINCE IT IS NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW
à INSET INTO EMP_DEPT (ENAME.JOB.SAL) VALUES (‘***’, ‘JI’, 1000)
à THIS IS VALID STATEMENT.

DROPPING THE VIEW
DROP VIEW
à DROP VIEW EMP DEPT

ALTERING THE VIEW:
ALTER VIEW AS SELECT STATEMENT

CURSORS:
RESULTS SET: SELECT STATEMENT RETURNS ALL THE ROWS WHICH SATISFIED THE CONDITION IN WHERE CLAUSE THE SET OF ROWS RETURNED BY THE SELECT ARE CALLED RESULT SET.
CURSOR: CURSOR IS A LOGICAL AREA USED FOR STORING TRANSACTION INFORMATION TEMPORARILY CURSOR IS THE DATABASE OBJECT USED TO PROCESS THE SET OF RECORDS INDIVIDUALLY, ON RECORD AT A TIME.

TYPES OF CURSOR:
1) STATIC CURSOR
2) DYNAMIC CURSOR
3) FORWARD ONLY CURSOR
4) KEY SET DRIVEN CURSOR

NECESSARY STEPS TO PROCESS CURSOR
1) DECLARE CURSOR
2) OPEN CURSOR
3) FETCH RECORD FROM THE CURSOR
4) CLOSE CURSOR
5) DEAL LOCATE CURSOR

1) DECLARE THE CURSOR:
SYNTAX: DECLARE CURSOR CURSOR TYPE FOR SELECT STATEMENT
EX: DECLARE EMP_CUR CURSOR STATIC FOR SELECT * FROM EMP
à DECLARE AUTHORS_CUR CURSOR DYNAMIC FOR SELECT * FROM AUTHORS
CURSOR DECLARATION THE NAME OF THE CURSOR AND SELECT STATEMENT ASSOCIATED WITH IT.

2) OPENING THE CURSOR:
SYNTAX: OPEN
OPEN EMP_CUR
OPEN AUTHORS_CUR
3) FETCH DATA FROM THE CURSOR: ONCE CURSOR IS OPENED RECORDS CAN BE FETCHED FROM IT
SYNTAX: FETCH FROM (OR)
FETCH FROM INTO
FETCH STATEMENT IS USED TO DISPLAY DATA ON SECREEN (OR ) DUMP THE DATA INTO VARIABLES.

KEYWORDS:
1) FIRST: USED TO FETCH THE FIRST RECORD
2) NEXT: USED TO FETCH RECORDS NEXT TO THE PREVIOUSLY FETCHED RECORD
3) LAST: USED TO FETCH THE LAST RECORD FROM THE RESULT SET
4) PRIOR : USED TO FETCH RECORDS BEFORE THE PREVIOUSLY FETCHED RECORD
5) ABSOLUTE N : IF N IS POSITIVE, IT FETCHES NTH RECORD FROM THE BEGINNING, IF ‘N’ IS NEGATIVE IS FETCHES NTH RECORD FROM THE LAST IF N=0, NO RECORD WILL BE FETCHED.
6) RELATIVE N: IT FETCHES NTH RECORD FROM THE PREVIOUSLY FETCHED RECORD.
IF N IS POSITIVE IT FETCHES NTH RECORD FOR PREVIOUSLY FETCHED RECORD
IF N IS NEGATIVE IT FETCHES NTH RECORD BEFORE FOR PREVIOUSLY FETCHED RECORD
IF N=0 SAME RECORD WILL BE FETCHED.

FETCHING: THE PROCESS OF RETRIEVING THE SINGLE RECORD FROM THE RESULT SET IS CALLED FETCHING
SCROLLING: THE PROCESS OF PERFORMING THE SERIES OF FETCHES EITHER IN FORWARD OR BACKWARD DIRECTION IS CALLED SCROLLING

DEFAULT CURSOR IS THE FORWARD ONLY CURSOR
FETCH NEXT IS THE ONLY FETCH STATEMENT VALID WITH FORWARD ONLY CURSOR
EX: FORWARD ONLY CURSOR:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR FOR SELECT * FROM AUTHORS
Þ OPEN AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ ERROR BECAUSE FETCH NEXT IS THE ONLY FETCH OPTION VALID WITH FORWARD ONLY CURSOR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IN THE TABLE WILL BE DISPLAYED

4) CLOSING THE CURSOR: CLOSE
EX: CLOSE EMP_CUR
CLOSE AUTHOR_CUR
IT IS ILLEGAL TO FETCH THE DATA FROM THE CLOSED CURSOR, DATA CAN BE FETCHED FROM THE CURSOR AFTER IT WAS OPENED

5) DEALLOCATE THE CURSOR:
SYNTAX: DEALLOCATE
EX: DEALLOCATE AUTHORS_CURS
NOTE: IT IS NOT POSSIBLE TO OPEN THE DEALLOCATED CURSOR

CURSOR WITH SCROLL OPTION: IF CURSOR IS DECLARED WITH SCROLL OPTION ALL FETCH OPERATIONS ARE VALID ON THE CURSOR.
EX:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR SCROLL FOR SELECT * FROM AUTHORS
Þ OPEN AUTHOR_CUR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IS FETCHED
Þ FETCH NEXT FROM AUTHORS_CUR
Þ RECORD NEXT TO THE 1ST RECORD IS FETCHED
Þ FETCH LAST FROM AUTHORS_CUR
Þ LAST RECORD IS FETCHEDFETCH PRIOR FROM AUTHORDS_CUR
Þ RECORD BEFORE THE LAST RECORD WILL BE FETCHED
Þ FETCH ABSOLUTE 5 FROM AUTHORS_CUR
Þ FETCH ABSOLUTE 2 FROM AUTHORS_CUR
Þ FETCH RELATIVE 0 FROM AUTHORS_CUR
Þ FETCH RELATIVE 5 FROM AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ FETCH PRIOR FROM AUTHORS_CUR

STATIC CURSOR:
Þ USE PUBS
Þ DECLARE DEPT_CUR CURSOR STATIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK

Þ UPDATE DEPT SET LOC =’HYD’ WHERE DEPTNO =10
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK

DML OPERATIONS PERFORMED ON THE ORIGINAL TABLE WILL NOT DISPLAYED BY CURSOR
WHEN CURSOR IS CLOSED AND REOPENED MODIFICATION WILL APPEARS
ALL FETCH OPTIONS ARE VALID WITH STATIC CURSOR

Þ SELECT * FROM DEPT
Þ MODIFICATIONS APPEARS
Þ CLOSE DEPT_CUR
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR


DYNAMIC CURSORS:
SYNTAX : DECLARE CURSOR DYNAMIC FOR SELECT STATEMENT.
IN CASE OF DYNAMIC CURSOR, THE OPERATIONS PEFORMED ON THE TABLE AFTER OPENING THE CURSOR WILL APPEARS THOUGH THE CURSOR.

Þ DECLARE DEPT_CUR CURSOR DYNAMIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ INSERT INTO DEPT VALUES (50, IMPORT’, ‘HYD’)
10 ACCOUNTING HYD
Þ FETCH FIRST FROM DEPT_CUR
Þ FETCH LAST FROM DEPT_CUR
10 IMPORT HYD
Þ FETCH PRIOR FROM DEPT_CUR

10 OPERATIONS BOSTON


*FETCH ABSOLUTE 2 FROM DEPT_CUR

KEY SET CURSOR:
Þ CREATE TABLE DEPT AND INSERT DATA INTO THE TABLE
Þ CREATE ON UNIQUE INDEX ON DEPTNO COLUMN
Þ DECLARE DEPT_KEYCUR CURSOR KEY SET FOR SELECT * FROM DEPT


KEY SET CURSOR:THE TABLE SHOULD HAVE AT LEAST ONE UNIQUE INDEX COLUMN TO CREATE THE KEY SET CURSOR
· WHEN CURSOR IS OPENED THE INDEX COLUMN OF THE TABLE IS TRANSFERRED TO THE NO-INDEX COLUMN CAN BE VISIBLE THOUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· THE MODIFICATION PEFORMED ON THE NON-INDEX COLUMNS CAN BE VISIBLE THROUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· IF THE TABLE WILL NOT HAVE ANY UNIQUE INDEX COLUMN IT AUTOMATICALLY CREATS THE STATIC CURSOR
· OPEN DEPT_KEYCUR
· UPDATE DEPT SET DNAME= ‘EXPORT’, LOC=’HYD’ WHERE DEPTNO = 10
· FETCH FIRST FROM DEPT_KEYCUR

10 EXPORT HYD

· UPDATE DEPT SET DEPTNO=50 WHERE DNAME = ‘OPERATIONS’ FETCH LAST FROM DEPT_KEYCUR
· NODATA IS DISPLAYED

SINCE THE MODIFICATION ON INDEX COLUMN WILL NOT BE VISIBLE THROUGH CUROSR.

TYPE WARNING: IF THIS KEYWORD IS USED WHILE DECLARING THE KEY SET CURSOR THE SERVER WILL DISPLAY MESSAGE IF IT FAILS TO CREATE KEYSET CURSOR
Þ DEPT TABLE WILL NOT HAVE ANY INDEX COLUMN
Þ DECLARE DEPT_KEYSET CURSOR KEYSET TYPE_ WARNING FOR SELECT * FROM DEPT
MESSAGE: CURSOR CREATED IS NOT OF REQUESTED TYPE I.E STATIC CURSOR IS CREATED.
NOTEL @@FETCH_STATUS: RETURNS THE STATUS OF LAST FETCH OPERATION THE POSSIBLE VALUES FOR @@ FETCH_STATUS ARE

1) 0à RECORD FETCHED SUCCESSFULLY
2) 1à FETCH OPERATIONS FAILS: YOU ARE BEYOND THE LAST RECORD OR BEFORE THE FIRST RECORD.

Ø FETCH FIRST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS

0

Ø FETCH PRIOR FROM DEPT_CUR
1


SINCE NO RECORD IS FETCHED
Ø FETCH LAST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0


Ø FETCH NEXT FROM DEPT_CUR
Ø PRINT @@FETCH_STATUS
1


SINCE NO RECORD IS FETCHED
Ø CREATE TABLEL (EMPNOT INT, ENAME VARCHAR(20))
Ø INSERT DATA
Ø DECLARE EMP1_CUR CURSOR FOR SELECT * FROM EMP1
Ø DECLARE @ VNO INT, @VNAME VARCHAR(20) OPE EMPL_CUR
FETCH NEXT FROM EMPL_CUR INTO @ VNO, @VNAME WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @ VNO
PRINT @ VNAME
FETCH NEXT FROM EMPL_CUR INTO @VNO, @VANME
END.
IF (@@ FETCH _STATUS =1)
PRINT ‘ALL RECORDS ARE FETCHED’




PROCEDURES
PROCEDURES : PROCEDURES IS A PRE_COMPILED COLLECTION OF T_SQL STATEMENTS PROCESSED AS A UNIT PROCEDURE WILL HAVE THE HEADER, DECLARATIVE SECTION AND EXECUTABLE SECTIONS.

TYPES OF PROCEDURES: 1) SYSTEM STORED PROCEDURES
2) USER DEFINED STORED PROCEDURES.

USED DEFINED STORED PROCEDURES: THE PROCEDURES CREATED BY THE USER ARE CALLED USED DEFINED STORED PROCEDURES.


SYNTAX: CREATE PROCEDURE / PROC
@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]
@PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE]
AS SELECT STATEMENT

WRITE A SIMPLE PROCEDURE TO THE COMPLETE DATA FROM EMP TABLE
A) à CREATE PROCEDURE EMP_DATA AS SELECT * FROM EMP
à EXECUTING THE PROCEDURE:
SYNTAX: EXEC
à
EXEC EMP_DATA
ALL EMPLOYEES DETAILS ARE DISPLAYED IN THE EMP TABLE
EX:
CREATE PROC SAL_DATA AS SELECT EMPNO, SAL BASIC ,SAL * 0.35HRA, SAL*0.25 DA, SAL *0.15 PF, SAL+SAL*0.35+ SAL – 0.15 GROSS FROM EMP
à EXEC SAL_DATA
EMPNO BASIC HRA DA PF GROSS


WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM AS DECLARE @A INT, @B INT, @C INT
SET @A=5
SET @B=10
SET @C=@A+@B
PRINT THE SUM IS ‘+’ CONVERT (VARCHAR, @C)
à EXEC ADD_NUM
THE SUM IS =15
WRITE A PROCEDURE TO FIND SIMPLE INTEREST?
A) CREATE PROC SIMPLE_INTEREST AS DECLARE @P INT, @N DECIMAL (5,2), @R DECIMAL (5,2), @5I DECIMAL (5,2)
à SET @SI = (@P*@N*@R*)/100
PRINT “SIMPLE INTEREST:”+CONVERT (VARCHAR, @SI)

WRITE A PROCEDURE TO FIND THE SUM OF TWO STRINGS
A) à CREATE PROC ADD_STRINGS AS DECLARE @A VARCHAR(20), @B VARCHAR(20), @C VARCHAR(20),
SET @A=’ORACLE’
SET @B=’APPLICATIONS’
SET @C=@A+’ ‘ +@B
PRINT @C
à EXEC ADD_STRINGS
ORACLE APPLICATIONS

WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM @A INT, @B INT, AS DECLARE @C INT
SET @C=@A+@B
PRINT THE ‘SUM OF A AND B IS ‘ + CONVERT (VARCHAR, @C )
à EXEC ADD_NUM 3,5
OUTPUT:: THE SUM OF A AND B IS 8

DECLARE @M INT, @N INT,
SET @M=5
SET @N=10 EXECUTE ALL THE ONCE
EXEC ADD_NUM@M,@N


INSERTING DATA INTO A TABLE USING PROCEDURE:
à CREATE PROC INSERT DATA
@VNO INT, @ VNAME VARCHAR (20). @LOC VARCHAR (10) AS INSERT INTO DEPT VALUES[@VNO, @VNAME.@LOC].

DEPTNO DNAME LOC
10 ACC NEW
20 RESE HYD
à EXEC INSERT_DATA 10, ‘ACC’, ‘NEW’
à SELECT * FROM DEPT.



WRITE A PROCEDURE TO DISPLAY DETAILS OF ON EMPLOYEE TAKE EMPO THE
à CREATE PRO EMP DETAILS
@VNO INT
AS SELECT * FROM EMP WHERE EMPO = @ VNO
à EXECUTE EMP_DETAILS 7369

WRITE A PROCEDURE TO FIND THE NET SAL SAL OF AN EMP CREATE PROC NET_SAL
@VNO INT AS
DECLARE @ VSAL INT @VCON INT, @NET INT
SELECT @ VSAL = SAL, @ VCOM FROM EMP WHERE
EMPO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
PRINT ‘NET SAL IS’ CONVERT (VARCHAR, @ VSAL)
END

WRITE A PROCEDURE TO FIND THE NET SAL SAL USING OUT PARAMETERS
A) à CREATE PROC NET SAL
@VNO INT , @NET INT OUTPUT
AS
DECLARE @ VSAL =SAL, @VCOM =COMM, FROM EMP WHERE EMPNO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
SET @ NET = @VSAL
END
ELSE
BEGIN
SET @NET= @VSNL + @COMM
END


EXECUTING PROC:
DECLARE @K INT
EXEC NET_SAL 7839, @ K OUTPUT
PRINT @K

ADMINISTRATOR LOGIN
CREATE PROC EMP_DEPT
SELECT ENAME, DNMAE FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GRANT EXEC ON EMP_DEPT TO RAM

CONNECT TO RAM LOGIN
EXEC EMP_DEPTà DATA IS DISPLAYED SINCE ADMINSTATOR HAS GIVEN EXECUTE PERMISSION ON EMP_DEP

ALTERING THE PROCEDURE:
SYNTAX: ALTER PROC @ PARA 1. DATATYPE @PARA 2 DATA TYPE (SIZE) AS SQL STATEMENT

DISPLAYING THE CODE OF EXISTING PROCEDURE:
SYNTAX: SP_TELPTAXT
EX: SP_TELP_TEXT EMP_DEPT

USER DEFINED FUNCTIONS: FUNCTIONS CREATED BY USER ARE CALLED USER DEFINED FUNCTIONS
TYPES OF USER DEFINED FUNCTIONS:
1) SCALAR VALUED FUNCTIONS
2) TABLE VALUED FUNCTIONS
SCALAR VALUED FUNCTIONS: THESE FUNCTIONS WILL RETURN A SCALAR VALUE TO THE CALLING ENVIRONMENT
SYNTAX: CREATE FUNCTION < FUNCTION_NAME> (PARA 1 DATA TYPE (SIZE), PARA 2 DATATYPE (SIZE)
RETURNS
AS
BEGIN

RETURN < SCALAR VALUE>
END



WRITE A FUNCTION TO FIND THE PRODUCT OF TWO NUMBERS
A) CREATE FUNCTION PROD_NUM
(@ A INT, @B INT)
RETURNS INT
DECLARE @ C INT
SET @C = @A * @B
RETURN(@C)
END

EXECUTING THE FUNCTION
à DECLARE @K INT
SET @K = DBO.PROD_NUM(3,5)
PRINT @K

WRITE FUNCTION TO FIND THE NET SALARY OF AN EMPLOYEE READ EMPNO THOUGH PARAMETER AND DISPLAY THE NET TO RETURN VALUE
A) CREATE FUNCTION NET_SAL
(@ VNO INT)
RETURNS INT
AS
BEGIN
DECLARE @ VSAL INT, @VCOM INT, @NET INT
SELECT @VSAL \ SAL, @VCOM=COM
FROM EMP WHERE EMPNO =@VNO
IF @ VCOM IS NULL
BEGIN
PRINT ‘COMMISION IS NULL’
SET @NET = @VSAL
END
ELSE
BEGIN
SET @ NET = @VSAL + @VCOM
END
RETURN (@NET)
DECLARE @ K INT
SET @K = NET_SAL (7839)
PRINT @K


2) TABLE VALUED FUNCTION: THESE FUNCTIONS WILL RETURN ENTIRE TABLE TO THE CALLING ENVIRONMENT.
SYNTAX:
CREATE FUNCTION
(PARA 1 DATA TYPE (SIZE)……….)
RETURNS TABLE
AS
BEGIN

RETURN (SELECT STATEMENT)
END

WRITE A FUNCTION TO RETURN ENTIRE DEPT TABLE
A) à CREATE FUNCTION DEPT_TAB()
RETURNS TABLE
AS
BEGIN
RETURN (SELECT * FROM DEPT)
END
à EXECUTING THE FUNCTION
à SELECT * FROM DEPT_TABLE()
DEPT DNAME LOC



à CREATE FUNCTION EMP_DEPT()
RETURN TABLE
AS BEGIN
RETURN(SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP DEPTNO = DEPT DEPTNO)
END
à SELECT * FROM EMP_DEPT()
ENAME DNAME
SMITH RESEARCH
MILLER ACCOUNTING

CREATING DEFAULT:
SYNTAX:
CREATE DEFAULT AS
EX:
à CREATE DEFAULT FILL_COLUMN AS ‘UNKNOWN’

BINDING THE DEFAULT FOR COLUMNS:
SYNTAX: SP_BINDDEFAULT FILL_COLUMN, ‘DEPT.LOC’
SP_BINDEFAULT FILL_COLUMN, ‘EMP.JOB’
à INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(10,ACCOUNTING’)
à SELECT * FROM DEPT WHERE DEPTNO =10
DEPTNO DNAME LOC
10 ACCOUNTING UNKNOWN
à INSERT INTO EMP (EMPNO, ENAME, SAL)
VALUES (100, ‘PHILLIS’, 5000)
à SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO =100
EMPNO ENAME JOB
100 PHILLIS 5000
UNBINDING THE DEFAULT FROM THE BINDING COLUMN
SYNTAX: SP_UNBINDEFAULT
SP_UNBINDEFAULT ‘ DEPT.LOC’
SP_UNBINDEFAULT ‘ EMP.JOB’

DROPPING THE DEFAULT:
SYNTAX: DROP DEFAULT
DROP DEFAULT FILL_COLUMN

CREATING RULES:
RANGE RULE:
CREATE RULE RANGE_RULE AS @ RANGE >=10 AND @ RANGE <= 60
BINDING THE RULE:
SP_BINDEFAULT ,‘ TABLE.COLUMN’
SP_BINDEFAULT RANGE_RULE, ‘DEPT.DEPTNO’
INSERT INTO DEPT(DEPTNO) VALUES (70)

ERROR

LIST RULE:
à CREATE RULE LIST_RULE AS @ LIST IN (‘ACCOUNTING’, ‘SALES’, ‘OPERATIONS’, RESEARCH’, ‘EDU’)
à SP_BINDRULE LIST_RULE, ‘DEPT_DNAME’
à INSERT INTO DEPT(DEPTNO, DNAME) VALUES (20, IMPORT’)
ERROR SINCE DANME VALUES IS OUT OF LIST

UNBINDING THE RULES:
à SP_UNBIND RULE DEPT.DEPTNO
à SP_UNBIND RULE DEPT.DNAME

DROPPING THE RULES:
à DROP RULE
à DROP RULE ROLE_NAME
à DROP RULE LIST_NAME

TRIGGERS
TRIGGERS: TRIGGER IS A TYPE OF STORED PROCEDURE THAT IMPLICITLY THAT IMPLICITLY EXECUTED WHEN USER PEFORMS DML OPERATION ON THE TABLE.
IT WILL NOT ACCEPT ANY PARAMETERS

TYPES OF TRIGGERS:
!) INSERT TRIGGER
2) DELETE TRIGGER
3) UPDATE TRIGGER
SYNTAX: CREATE TRIGGER ON FOR/ AFTER, INSERT,/ UPDATE/ DELETE AS SQL STATEMENT.
I
INSERT TRIGGER: THIS TRIGGER FIRES WHEN USER PERFORMS INSERT OPERATION ON THE TABLE. WHEN USER INSERT A RECORD INTO THE TABLE THE TEMPORARY TABLE CALLED INSERTED IS CREATED
THE NEWLY INSERTED RECORD IS ALSO STORED IN INSERTED TABLE TEMPORARILY
à CREATE TRIGGER
FOR INSERT
AS
BEGIN
PRINT ‘TRIGGER T1 FIRE’
END

à CREATE TRIGGER2 ON DEPT
FOR
AS
PRINT ‘TRIGGER T2 FIRE’
END

à CREATE TRIGGER3 ON DEPT
FOR INSERT
AS
PRINT ‘TRIGGER T3 FIRE’
END
à INSERT INTO DEPT(DEPTNO) VALUES(50)
TRIGGER T1 FIRED
TRIGGER T2 FIRED
TRIGGER T3 FIRED

CHANGING THE FIRING ORDER OF TRIGGERS:
SYNTAX: SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘NAME_OF_TRIGGER’,
@ ORDER = ‘FIRST/LAST’
@ STMTYPE = ‘INSERT’/UPDATE/DELETE’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T3’
@ ORDER = ‘FIRST’
@ STMTYPE = ‘INSERT’

à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T1’
@ ORDER = ‘LAST’
@ STMTYPE = ‘INSERT’
à INSERT INTO DEPT (DEPTNO) VALUES (60)
TRIGGER T3 FIRED
ON DEPT
FOR UPDATE
AS
BEGIN
INSERT INTO DEPT_INSERT SELECT * FROM INSERT
INSERT INTO DEPT_DELETE SELECT * FROM DELETED
END
à UPDATE DEPT
SET DNAME = ‘EXPORT’
LOC = ‘SEC’ WHERE DEPTNO =10

à SELECT * FROM DEPT_INSERT
DEPTNO DNAME LOC
10 EXPORT SEC

à SELECT * FROM DEPT_DELETE
DEPTNO DNAME LOC
10 ACCOUNTING NEWYORK

INSTEAD OF TRIGGERS: THESE TRIGGER ARE MAINLY CREATED FOR VIEWS
SYNTAX: CREATE TRIGGER TRIGGER_NAME ON < VIEW_NAME>
INSTEAD OF INSERT/ UPDATE ./ DELETE
AS
SQL STATEMENT
à CREATE VIEW V10
AS
SELECT * FROM EMP WHERE DEPTNO =10
Þ SELECT * FROM V10
Þ IT WILL DISPLAY AN 10TH DEPT EMPLOYEES DETAILS
Þ CREATE TRIGGER V10_TRG
INSEAT OF INSERT
AS
UPDATE V10 SET SAL=SAL +500
Þ INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (100, ‘CHAD’, 10)
Þ SELECT * FROM V10
Þ INSTEAD RECORDS WILL NOT APPEARS BUT SALARIES ARE MODIFIED DUE INSTEAD OF TRIGGER
Þ CREATE VIEW DEPT_VIEW
AS
SELECT * FROM DEPT
Þ CREATE TRIGGER DEPT_TR
AS
ON DEPT_VIEW
FOR INSERT
AS
DELETE FROM DEPT_VIEW
Þ INSERT INTO DEPT_VIEW DATA DELETED DUE TO INSTEAD OF TRIGGER.
Þ CREATE VIEW EMP_DEPT
AS
SELECT EMPNO, ENAME, DEPT,.DEPTNO, DNAME FROM EMP,
DEPT WHERE EM.DEPTNO =DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME


à SELECT * FROM EMP_DEPT




ERROR
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).

SINCE NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW.

à CREATE TRIGGER EMP DEPT_TRG
ON EMP_DEPT
FOR INSERT
AS
BEGIN
INSERT INTO EMP (EMPNO,ENAME) SELECT EMPNO, ENAME FROM INSERTED.
INSERT INTO DEPT(DEPTNO, DNAME) SELECT DEPTNO, DNAME FROM INSERTED
END.

à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
à SELECT * FROM EMP
à NEW RECORD WILL IS DISPLAYED ALONG WITH OLD RECORD
à SELECT * FROM DEPT
à NEW RECORD WILL BE DISPLAYED ALONG WITH OLD RECORDS



DISPLAYING THE CODE OF TRIGGER:

SYNTAX: SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘EMP_DEPT’

DISPLAYING THE TRIGGERS INFORMATION FOR THE TABLE.
SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘DEPT’

DCL COMMANDS: THESE COMMANDS ARE USED GRANT OR REVOKE THE PERMISSION ON DATABASE OBJECTS TO OTHER USERS.

1) GRANT: USED TO GRANT THE PERMISSIONS ON DATA OBJECTS TO USERS ACCOUNTS
ADMINISTRATOR TOOLS
GRANT SELECT ON DEPT TO RAM
GRANT INSERT, SELECT ON EMP TO RAM123

RAM/ RAM 123 LOGIN
SELECT * FROM DEPT
à DEPT DATA IS DISPLAYED
INSERT INTO DEPT VALUES (50, ‘****’, ‘DDDD’)
à ERROR SINCE NO INSERT PERMISSION TO RAM
SELECT * FROM EMP
INSERT INTO EMP (EMPNO, ENAME) VALUES (400, ‘CHRIS’)
à 1 RECORD IN INSERTED
UPDATE EMP SET EMPNO =111 WHERE ENAME = ‘SMITH’
à ERROR SINCE NO UPDATE PERMISSION TO RAM

ADMINISTRATOR LOGIN:
REVOKE: USED TO TAKE BACK THE PERMISSIONS GIVEN TO THE USERS
REVOKE SELECT ON DEPT FROM RAM
REVOKE SELECT, INSERT AN EMP FROM RAM

ADMINISTRATOR LOGIN
GRANTING PERMISSION WITH GRANT OPTION:
GRANT SELECT ON DEPT TO RAM WITH GRANT OPTION

RAM LOGIN:
SELECT * FROM DEPT
à DATA WILL BE DISPLAYED
GRANT SELECT ON DEPT TO NTTS

NTTS LOGIN
SELECT * FROM DEPT


ADMINISTRATOR LOGIN
REVOKE SELECT ON DEPT FROM RAM CASCADE
à NOW BOTH RAM AND NTTS WILL LOOSE THE PERMISSION ON DEPT
à SP_WHO à IT WILL DISPLAY THE STATUS OF LOGINS.

DATA ARE RAW FACTS
EX : CUSTOMER NAME IN BANK
TOTAL MARKS OBTAINED BY STUDENT

INFORMATION: AN ORGANIZED DATA IS CALLED INFORMATION
EX : CUSTOMER NAME WITH BALANCES.
STUDENT REG NO WITH TOTAL MARKS.

DATA HIERARCHY:
BIT IT MAY BE EITHER 0 OR 1
IT REPRESENTS THE SMALLEST PART OF INFORMATION THAT COMPUTER CAN PROCESS.

BYTE : IT IS GROUP OF 8 BITS IT REPRESENTS A DIGIT, LETTERS OR SPECIAL SYMBOL.
FILED: IT IS A GROUP OF CHARACTERS.

EMPNO ENAME SAL JOB
100 SMITH 800 CLERK

RECORD: IT IS A GROUP OF LOGICALLY RELATED FIELD.
FILE : GROUP OF LOGICALLY RELATED RECORDS OR IT IS A FLOW OF CHARACTERS.
DATABASE: IT IS A GROUP OF LOGICALLY RELATED DATA.
(OR)
IT IS THE COLLECTION OF DATA STORED IN A FILE ON DISC

CLASSIFICATION OF DATA BASE BASED ON SIZE
· NORMAL DATABASE:<10 GB
· LARGE DATABASE : 10 GB TO 100 GB
· VERY LARGE DATABASE : 100 GB TO 1000 GB
· EXTREMELY LARGE DATABASE :> 1000 GB

FILE MANAGEMENT SYSTEM
FILE : IT IS A STREAM OF CHARACTERS

DISADVANTAGES :
1. NOT SUPPORT VARIABLES DECLARATION TO STORE (OR) HOLD DATA.
2. WASTAGE OF MEMORY DUE TO DATA REDUNDANCY.
3. MULTIPLE USERS CANNOT ACCESS THE SINGLE FILE AT A TIME.
4. SEARCHING IS A DIFFICULT TASK.
5. INFORMATION ACCESS IS SLOW.

DATABASE MANAGEMENT SYSTEM (DBMS)
IT IS A SUIT OF SOFTWARE PROGRAM FOR CREATING, MAINTAINING & MANIPULATING THE DATA IN DATABASE. (OR)
IT IS A COLLECTION OF INTER RELATED DATA AND SET OF PROGRAM TO ACCESS THE DATA

IT ALLOWS THE ORGANIZATION TO STORE THE DATA IN ONE LOCATION FLOW, WHICH MULTIPLE USERS CAN ACCESS, THE DATA.


DIFFERENT DATABASE MODELS:
1. HIERARCHICAL MODEL
2. NETWORK MODEL
3. RELATIONAL MODEL






DATA IS ORGANIZED IN THE FORM OF TREE STRUCTURE WITH ONE LIMITATION THAT IS
“EVERY SUB NODE SHOULD HAVE ONLY ONE ROOT NODE

DRAWBACKS:
1. DATA REDUNDANCY
2. WASTAGE OF MEMORY
3.CROSS COMMUNICATION IS NOT POSSIBLE

2. NET WORK MODEL:
DATA IS ORGANIZED IN THE FORM OF ARBITRARY GRAPHS.
THERE IS NO GUARANTEE FOR ACCESS OF DATABASE WHENEVER THE DATABASE SIZE INCREASES.

CROSS COMMUNICATION IS POSSIBLE IN NDBMS.

IT CANNOT PROVIDE PROPER QUERY FACILITY, SO THAT WE HAVE TO BIG PROGRAMS EVEN FOR SMALL OPERATION.





3. RELATIONAL MODEL
IT USES THE COLLECTION OF TABLES TO REPRESENT BOTH THE DATA AND RELATIONSHIP AMONG THE DATA.

FEATURES OF RELATIONAL MODEL:
1. DATA IS STORED IN TABLES.
2. INTERSECTION OF ROWS AND COLUMNS WILL GIVE ONLY ONE VALUE.
3. RELATION AMONG DATA IS ESTABLISHED LOGICALLY.
4. THERE IS NO PHYSICAL LINK AMONG DATA
5. THERE IS NO DATA REDUNDANCY
6. HIGH SECURITY FOR DATA
7. IT SUPPORTS ANY TYPE OF DATA (EX: NUMBERS, NUMERIC, DATA, CHARACTER, DATE IMAGES ETC).
8. IT SUPPORTS NULL VALUES.
9. SUPPORT CODD RULES
10. IT SUPPORTS INTEGRITY CONSTRAINTS
11. MULTIPLE USERS CAN ACCESS DATA FROM ANY LOCATION.

NULL VALUE: IT IS UNKNOWN, UNASSIGNED AND UN COMPARABLE VALUE.
EX: 500 + NULL=NULL
ANY ARITHMETIC EXPRESSION CONTAINING NULL IS EVALUATED TO NULL
NULL IS NOT EQUAL TO ZERO OR NOT EQUAL TO SPACE.

RELATIONAL DATABSE: A DATABASE BASED ON RELATIONAL MODEL IS CALLED RELATIONAL DATABASE.
RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS): IT IS A SUIT OF SOFTWARE PROGRAMS FOR CREATING, MARINATING AND MANIPULATING THE DATA IN THE RELATIONAL DATABASE.



DIFFERENT RDBMS PACKAGES:
Ø ORACLE
Ø SQL SERVER
Ø DB2
Ø SYBASE

SQL SERVER:
SQL SERVER 2000 IS AN RDBMS THAT USES TRANSACT-SQL TO SEND REQUESTS BETWEEN A CLIENT
COMPUTER AND A SQL SERVER 2000 COMPUTER. AN RDBMS INCLUDES DATABASES, THE DATABASE
ENGINE, AND THE APPLICATIONS THAT ARE NECESSARY TO MANAGE THE DATA AND THE COMPONENTS OF
THE RDBMS. THE RDBMS ORGANIZES DATA INTO RELATED ROWS AND COLUMNS WITHIN THE DATABASE.
THE RDBMS IS RESPONSIBLE FOR ENFORCING THE DATABASE STRUCTURE, INCLUDING THE FOLLOWING
TASKS:

■ MAINTAINING THE RELATIONSHIPS AMONG DATA IN THE DATABASE
■ ENSURING THAT DATA IS STORED CORRECTLY AND THAT THE RULES DEFINING DATA RELATIONSHIPS
ARE NOT VIOLATED
■ RECOVERING ALL DATA TO A POINT OF KNOWN CONSISTENCY IN CASE OF SYSTEM FAILURES


VERSIONS OF SQL SERVER:
1. IN 1993 MICRO SOFT AND SYBASE RELEASES 4.2 VERSION OF SQL SERVER FOR WINDOWS NT ENVIRONMENT.
2. 1995à MICROSOFT RELEASES SQL SERVER 6.0
3. 1996à MICROSOFT RELEASES SQL SERVER 6.5
4. 2000à MICROSOFT RELEASES SQL SERVER 2000
LATEST VERSION SQL SERVER 2005 NOT YET RELEASED.

FEATURES OF SQL SERVER 2000:
1. INTRODUCING SUPPORT FOR XML
2. USER DEFINED FUNCTION ARE INTRODUCED.
3. OLAP (ONLINE ANALYTICAL PROCESS) SERVICES AVAILABLE IN SQL SERVER 7.0 ARE NOW CALLED AS SQL SERVER 2000 ANALYSIS SERVICES
4. REPOSITORY COMPONENTS AVAILABLE IN SQL SERVER NOW CALLED META DATA SERVICES.

DATABASE SIZES:
· MS ACCESS à 2 GB
· SQL SERVER – 6.0 à 1 TB
· SQL SERVER – 7.0 à 1,048,516 TB 3
· SQL SERVER – 2000 à 1,048,516 TB 3

DATABASE IN SQL SERVER:
1) SYSTEM DATABASES
2) USERS DEFINED DATABASES
SYSTEM DATABASES: COMPLETE SYSTEM LEVEL INFORMATION SQL SERVER IS STORED IN SYSTEM DATABASES. SQL SERVER USES SYSTEM DATABASES TO OPERATE USER DATABASES.



USER DATABASES: THE DATABASES CREATED BY USER.


NORMALIZATION
ENTITY : A PERSON, PLACE OR EVENT ABOUT WHICH THE INFORMATION IS STORED IS CALLED ENTITY.
EX: EMPLOYEE, DEPARTMENT, STUDENT, CUSTOMER.
ATTRIBUTE: THE CHARACTERISTIC OR PROPERTY THAT DESCRIBES AN ENTITY IS CALLED ATTRIBUTE.
EX : EMPNO, ENAME, JOB ETC ARE ATTRIBUTE OF ENTITY EMPLOYEE
CUSTID, NAME, ADDRESS ARE ATTRIBUTE OF ENTITY CUSTOMER

FUNCTIONAL DEPENDENCY: IN A RELATION R, AN ATTRIBUTE B IS SAID TO BE FUNCTIONALLY DEPENDENT AN ATTRIBUTE A IF THE VALUE IN B IS UNIQUELY IDENTIFY BY THE VALUE IN A.
THE FUNCTION DEPENDENCY OF ‘B’ AN ‘A’ IS REPRESENTED AS FOLLOWS
R: AàB
EX: 1. SSN à NAME, ADDRESS, DATE OF BIRTH
IE A PERSON NAME, ADDRESS, DATE OF BIRTH
FUNCTIONALLY DEPENDENT ON SOCIAL SECURITY NUMBER(SSN)
2. VIN à MAKE, MODEL, COLOR
IE MAKE, MODEL & COLOR OF VEHICLE FUNCTIONALLY
DEPENDS ON VIN (VEHICLE IDENTIFICATION NUMBER)
PARTIAL FUNCTIONAL DEPENDENCY:
PARTIAL FUNCTIONAL DEPENDENCY IS THE FUNCTIONAL DEPENDENCY IN WHICH SOME NON-KEY COLUMNS DEPENDS ON PART OF KEY COLUMN OR PRIMARY COLUMN.

IN THE ABOVE TABLE EMP NAME, SAL DEPENDS ONLY ON EMP NO BUT THE COURSE COMPLETED DAT WILL DEPENDS ON BOTH EMP NO & COURSE TITLE TO IDENTIFY THE ROW UNIQUELY IN A TABLE EMP NO, COURSE TITLE SHOULD BE DECLARED AS PRIMARY KEY COLUMNS. BUT THE NON-KEY ATTRIBUTES NAME, SQL DEPENDS ONLY EMP NO, IE PART OF A KEY COLUMN THIS TYPE OF FUNCTIONAL DEPENDENCY IS CALLED IT PARTIAL FUNCTIONAL DEPENDENCY.


TRANSITIVE DEPENDENCY
FUNCTIONAL DEPENDENCY BETWEEN THE TWO NON-KEY ATTRIBUTES IS CALLED THE TRANSITIVE DEPENDENCY.

Ø IN THE ABOVE TABLE CUSTID IS IDENTIFIED AS KEY COLUMN
Ø BUT REGION DEPENDS ONLY ON SALESMAN, IS CALLED TRANSITIVE DEPENDENCY.
COLUMNS REGION AND SALESMAN IS CALLED TRANSITIVE DEPENDENCY.

NORMALIZATION ; IT IS THE PROCESS OF DECOMPOSING THE RELATIONS (TABLES) IN TO SMALLER AND WELL STRUCTERD RELATIONS TO MINIMIZE THE DATA REDUNDANCY.
(OR)
IT IS THE PROCESS OF DECIDING WHICH ATTRIBUTED HAS TO GROUPED TO AVOID DATA REDUNDANCY.
THERE ARE SOME RULES IN NORMALIZATION EACH RULE IS CALLED NORMAL FORM.

NORMAL FORMS:
1. FIRST NORMAL FORM (1NF)
2. SECOND NORMAL FORM (2NF)
3. THIRD NORMAL FORM (3NF)

FIRST NORMAL FORMS (1NF): MULTI VALUED ATTRIBUTED SHOULD BE REMOVED FROM THE TABLE.
EXPLANATION: IN THE ABOVE TABLE EMP COURSE TITLE, COURSE COMPLETED DATE ARE MULTI VALUED ATTRIBUTES SINCE AT THE INTERSECTIONS OF ROW AND COLUMN THEY WILL GIVE MOVE THAN ON VALUE. TO BRING THE ABOVE TABLE TO INF MULTI VALUED ATTRIBUTED SHOULD BE REMOVED BY FILLING THE EMPTY FIELD WITH SUITABLE DATA AS SHOWN BELOW.

SECOND NORMAL FORM (2NF) : THE RELATION SHOULD BE IN INF AND THE PARTIAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED.
IN THE ABOVE TABLE EMP NO, COURSE TITLE ARE IDENTIFIED AS KEY COLUMN BUT NON-KEY COL LIKE NAME AND SQL WILL DEPENDS.
TO BRING THE TABLE TO SECOND NORMAL FORM THE NORMAL FUNCTIONAL DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLES AS SHOWN ABOVE.

THIRD NORMAL FORM (3NF): THE TABLE SHOULD BE IN THE 2NF AND TRANSITIVE DEPENDENCY SHOULD BE REMOVED.
EX: CUSTOMER
· CUSTID IS IDENTIFIED AS KEY-COLUMN BUT REGION DEPENDS ON SALESMAN.
· THE FUNCTIONAL DEPENDENCY BETWEEN NON-KEY ATTRIBUTES SALES MAN & REGION IS CALLED TRANSITIVE DEPENDENCY.
· TO BRING THE TABLE TO THIRD NORMAL FORM, TRANSITIVE DEPENDENCY SHOULD BE REMOVED BY DIVIDING THE TABLE INTO TWO TABLE AS SHOWN BELOW

SYSTEM AND SAMPLE DATABASE IN SQL SERVER:
WHEN SQL SERVER IS INSTALLED IN A SYSTEM SETUP WILL CREATES THE FOLLOWING SYSTEM AND SAMPLE DATABASE.

SYSTEM DATABASES:
1. MASTER
2. MODEL
3. TEMPDB
4. MSDB
SAMPLE DATABASES:
1. PUBS
2. NORTH WIND
1. MASTER DATABASE : IT CONTAINS SYSTEM LEVEL INFORMATION OF SQL SERVER. ALL LOGINS AND DATABASE INFORMATION IS STORED IN MASTER DATABASE.
2. MODEL DATABASE : IT IS A TEMPLATE FOR USER DATABASES. WHEN USES IS CREATED A DATABASE THE COMPLETE INFORMATION WHICH IS THERE IN MODEL DATA WILL BE COPIED INTO THE USER DATABASE.
3. TEMPDB: IT FOR HOLDING TEMPORARY OBJECTS WHICH ARE CREATED WHEN SQL SERVER STARTS.
4. MSDB: SQL SERVER AGENT FOR SCHEDULING JOBS AND ALERTS USES IT.

PUBS & NORTH WIND: SAMPLE DATABASES



SUB LANGUAGES IN T-SQL COMMANDS:
1. DDL (DATA DEFINITION LANGUAGE)
2. DML (DATA MANIPULATION LANGUAGE)
3. DCL (DATA CONTROL LANGUAGE)

Ø DDL : THESE ARE USED TO CREATE, ALTER AND DROP THE DATABASE OBJECTS.
COMMANDS 1.CREATE
2. ALTER
3. DROP
Ø DML : THESE ARE USED TO INSERT, MODIFY AND DELETE THE DATA IN DATABASE OBJECTS.

à THESE COMMANDS ARE ALSO USED TO RETRIEVE THE DATA IN DB OBJECTS
COMMANDS 1.INSERT
2. UPDATE
3. DELETE
4. SELECT
Ø DCL : THESE ARE USED TO GRAND OR REVOKE THE PERMISSIONS ON DATABASE OBJECTS TO
OTHER USERS
COMMANDS 1.GRANT
2. REVOKE
TABLE IS THE PRIMARY STORAGE UNIT FOR DATA IN DB. MAX NO OF COLUMNS ALLOWED FOR A TABLES ARE 1024, MAX AMOUNT OF DATA ALLOWED PER A ROW 8060 BYTES.
DATA TYPE : THE DATA TYPE SPECIFIES THE TYPE OF DATA THAT CAN BE STORED IN A VARIABLE OR COLUMN OF A TABLE.
DATA TYPE IN SQL SERVER ARE CLASSIFIED INTO TWO TYPES:
1. PREDEFINED DATA TYPES.
2. USER DEFINED DATA TYPES.
PRE DEFINED DATA TYPES:
(a) CHAR [(N)]àFIXED LENGTH DATA TYPE USED TO STORE CHARACTER DATA, STATIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.
à DEFAULT SIZE OF N IS 1 BYTE.
EX: 1.@V1 CHAR (5)
2.@V2 CHAR (10)

(b) VARCHAR (N)àVARIABLE LENGTH DATA TYPE. DYNAMIC MEMORY ALLOCATION
à IT CAN STORE DATA UP TO 8000CHARACTERS.

EX: 1. @ A VARCHAR (5)
MEMORY WILL BE ALLOCATED DYNAMICALLY
(c) NCHAR (N)àFIXED LENGTH OF CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000CHARACTERS.
(d) NVARCHAR (N)àVARIABLE LENGTH CHARACTER DATA TYPE
à IT CAN STORE DATA UP TO 4000 CHARACTERS.




NUMERIC DATA TYPE : USED TO STORE THE NUMERIC VALUES

DATATYPE SIZE
BIGINT 8 BYTES
INT 4 BYTES
SMALLINT 2 BYTES
TINYINT 1 BYTES
FLOAT 8 BYTES
REAL 4 BYTES
DATE DATA TYPE: USED TO STORE DATE AND TIME VALUES.

1. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1753 TO 31ST DECEMBER - 9999
à DEFAULT SIZE IS 8 BYTES
EX : IS DOJ DATE TIME.
2. DATE TIME: à USED TO STORE DATE VALUES FROM 1ST JANUARY -1990 TO 6TH JUNE - 1979
à DEFAULT SIZE IS 4 BYTES
SQL SERVER SERVICE MANAGER: BY USING THIS DBA CAN STOP, PAUSE OR START THE SERVER.
ENTERPRISE MANAGER: IT IS THE PRIMARY ADMINISTRATIVE TOOL FOR THE ADMINISTRATIVE TASK.
CREATING LOGINS: IN ENTERPRISE MANAGE.
SECURITYà LOGINS
↓ RIGHT CLICK ON THIS
à CLICK ON NEW LOGIN
à SELECT THE SQL SERVER AUTHENTICATION
NAME OF LOGIC
PASSWORD****
CLICK OK
VERIFYING THE PROPERTIES OF LOGIN: CLICK RIGHT CLICK ON LOGIN AND CLICK ON PROPERTIES
DEMO TABLES:
1. EMP TABLE
2. DEPT TABLE

EMP TABLE
EMPNO ENAME JOB MGR SAL COMM. HIREDATE DPTN
7369 SMITH CLERK 7902 800 NULL ----- 20
--- ---- --- --- --- --- ----- ---
--- ---- --- --- --- --- ---- ---
7934 MILLER CLERK 7506 1300 NULL --- 10




DEPT TABLE:
DEPT NO DNAME LOC
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


DDL COMMANDS
CREATE : IT IS USED TO CREATE DATABASE AND ITS OBJECTS LIKE TABLE, VIEWS ETC
à CREATE TABLE STUDENTS ( RNO INT, NAME VARCHAR (20), ADDRESS VARCHAR(50))
[SHIFT+HOME]—COMMANDS IS SELECT
F5à EXECUTE THE COMMAND
DESCRIBING THE STRUCTURE OF A TABLE:
SYNTAX : SP_HELP
EX: SP_HELP STUDENT
àCREATE TABLE DEPT (DEPTNO INT, DNAME VARCHAR(20), LOC VARCHAR(10))
à CREATE TABLE EMP (EMP INT, ENAME VARCHAR(20), JOB VARCHAR(10),SAL MONEY , DEPTNO INT )
à SP_HELP ‘EMP’ (DESCRIBES, STRUCTURE OF EMP TABLE)
à CREATE TABLE[CUST] (CUST ID INT, ENAME VARCHAR(2), SALESMAN CHAR(2))

ALTER: IT IS USED TO ALTER THE STRUCTURE OF EXISTING TABLES, IT IS MAINLY USED IN FOLLOWING CASES.
(1). WHEN USER WANTS TO ADD A COLUMN TO THE EXISTING COLUMN
(2). WHEN USER WANTS TO MODIFY THE COLUMN DEFINITION.
(3). WHEN USER WANTS TO DROP THE COLUMN IN THOSE SITUATIONS ARE USE ALTER TABLE
CREATE TABLE EMP(EMP NO INT, ENAME VARCHAR(20), JOB VARCHAR (10), MGR INT)

ADDING COLUMNS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD DATATYPE (SIZE), DATATYPE(SIZE)

· ALTER TABLE EMPL ADD HIRE_DATE DATE TIME
· ALTER TABLE EMPL ADD SAL INT COMM. IN, DEPTNO IN
· SP_HELP ‘EMP’

MOST RECENTLY ADDED COLUMN IS LAST COLUMN
MODIFYING THE COLUMN DEFINITION:
SYNTAX: ALTER TABLE ALTER COLUMN < COLUMN NAME> DATA TYPE (SIZE)
· ALTER TABLE EMPL ALTER COLUMN ENAME VARCHAR (30)
· ALTER TABLE EMPL ALTER COLUMN JOB VARCHAR (5)
· IT IS POSSIBLE IF COLUMN IS EMPTY (OR) SIZE IS SUFFICIENT FOR EXISTING OF COLUMNS
· ALTER TABLE EMPL ALTER COLUMN ENAME CHAR (20)
· IT IS VALID ENEN IF COLUMN CONSTAINS DATA.
· ALTER TABLE EMPL ALTER COLUMN EMPNO VARCHAR (20)
· IT IS VALID ONLY WHEN COLUMN IS EMPTY
· ALTER TABLE EMPL ALTER COLUMN JOB
· VALID WHEN COLUMN IS EMPTY IT IS VALID

DROPPING THE COLUMN FROM THE TABLE:
SYNTAX :
ALTER TABLE < TABLE_NAME> DROP COLUMN
à ONLY ONE COLUMN CAN BE DROPPED AT A TIME ONCE COLUMN IS DROPPED, THE DATA IN THE COLUMN WILL ALSO REMOVED.
à TABLE SHOULD HAVE AT LEAST ONE COLUMN IE IT IS NOT POSSIBLE TO DROP THE LAST COLUMN FROM THE TABLE.
à ALTER TABLE EMP DROP COLUMN EMPNO
àALTER TABLE DEPT DROP COLUMN LOC
àALTER TABLE DEPT DROP COLUMN DEPTNO
ERROR à SINCE LAST COLUMN CANNOT BE DROPPED FROM THE TABLE

RENAMING THE COLUMN OF TABLE

SYNTAX: SP_RENAME ‘TABLE. OLD-COLUMN-NAME’,’NEW-COLUMN-NAME’
EX: SP_RENAME ‘DEPT.DEPTNO’, ‘DEPT_NUMBER’
. SP_RENAME ‘EMP.SAL’, ‘SALARY’

RENAMING THE TABLE:
SYNTAX: SP_RENAME ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’
EX: SP_RENAME ‘DEPT’, ‘DEPT_TABLE’

DROPPING THE TABLE:
SYNTAX
DROP TABLE
DROP TABLE STUDENT
DROP TABLE EMP

DML COMMANDS:
1. INSERT: IT IS USED TO INSERT THE DATA INTO THE TABLES.
SYNTAX: INSERT INTO VALUES (VALUE1, VALUE2,….VALUE N)
DATE, VARCHAR, CHAR VALUES SHOULD BE IN SINGLE QUOTATION MARKS.
SINGLE QUOTATION MARKS ARE INVALID PER THE NUMERIC DATA.
àINSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK’)
àINSERT INTO DEPT VALUES(20,’RESEARCH’, ‘DALLAS’)
àINSERT INTO DEPT VALUES(30,’SALES’, ‘CHICAGO’)
àINSERT INTO DEPT VALUES(40,’OPERATIONS’, ‘BOSTON’)






DISPLAYING THE DATA IN THE TABLE:
à SELECT * FROM DEPT

DEPT NO DNAME 10C
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON




à INSERT INTO EMP VALUES(7369,’SMITH’, ‘CLERK’, 7902,’15-MAR-98’,800,100,20)
INSERTING NULL VALUES INTO A TABLE
SYNTAX:
METHOD1:
IMPLICIT METHOD:
SYNTAX
INSET INTO (COLUMN1, COLUMN2,COLUMN3) VALUES
(VALUES1, VALUES2, VALUES3)
EX: INSERT INTO DEPT(DEPTNO, DANME, LOC)VALUES(10, NEWYORK)
INSERT INTO DEPT(DEPT NO) VALUES (20)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NEWYORK
20 NULL NULL

EXPLICIT METHOD:
.EXPLICITLY USER SHOULD PROVIDE THE NULL VALUES FOR THE COLUMNS
SYNTAX:
INSERT INTO VALUES (VALUE1, VALUE2, VALUE3)
EX: INSERT INTO DEPT VALUES(30,NULL, NULL)
SELECT * FROM DEPT
DEPT NO DNAME 10C
10 NULL NULL
20 NULL NULL
30 NULL NULL
40 OPERATIONS NULL

OPERATORS IN SQL SERVER:
· ARITHMETIC OPERATORS
· COMPARISON OPERATORS
· LOGICAL OPERATORS
· ASSIGNMENT OPERATORS

ARITHMETIC OPERATORS: IT IS USED TO FORM AN EXPRESSION WITH NUMERIC AND DATE VALUES.
OPERATOR + - * / %
MEANING ADD SUB MUL DIV MODULAR

COMPARISON OPERATORS: THESE ARE USED TO CO,PARE ARE EXPRESSION WITH ANOTHER EXPRESSION.
OPERATOR MEANING
> GREATER THAN
>= GREATER THAN OR EQUAL
< LESS THAN
<= LESS THAN OR EQUAL TO
!< NOT LESS THAN
!> NOT GREATER THAN
<> (OR) != NOT EQUAL TO


LOGICAL OPERATORS: THERE ARE USED TO COMBINE THE RESULTS OF 2 EXPRESSIONS INTO SINGLE ONE.
OPERATOR MEANING
AND RETURNS TRUE IF BOTH THE EXPRESSIONS ARE TRUE
OR RETURNS TRUE IF ONE OF THE TWO EXPRESSIONS IS TRUE
NOT RETURNS TRUE IF RESULT IS FALSE OTHER WISE RETURN FALSE.

P Q P AND Q P OR Q NOT P NOT Q
1 1 1 1 0 0
1 0 0 1 0 1
0 1 0 1 1 0
0 0 0 0 1 1

1à TRUE
0à FALSE

ASSIGNMENT OPERATOR: ‘=’ IS THE ONLY ASSIGNMENT OPERATOR AVAILABLE IN SQL SERVER
‘WHERE’ CLAUSE: IT IS A CONDITIONAL CLAUSE USED TO IMPOSE THE CONDITIONS. IT IS USED WITH UPDATE AND SELECT STATEMENT.

UPDATE: USED TO MODIFY THE DATA IN THE TABLE.
SYNTAX: UPDATE SET COLUMN 1=VALUE 1[, COLUMN2= VALUE2….]

* MODIFY THE SALARIES OF ALL THE EMPLOYEES IN EMP TABLE
àUPDATE EMP SCT SAL=SAL+1000
à ALL EMPLOYEES SALARIES ARE UPDATED
à THIS TYPE OF UPDATION IS CALLED HIGH LEVEL UPDATION
*MODIFY THE SALARIES OF ALL MANAGERS
à UPDATE EMP SET SAL=SAL+500 WHERE JOB= ‘SALES MAN’ OR JOB=’ANALYST’
*MODIFY THE SALARIES OF ALL THE CLERKS WORKING IN 20TH DEPARTMENT
àUPDATE EMP SET SAL= SAL+300 WHERE JOB=’CLERK’ AND DEPTNO=20
*MODIFY THE SALARIES OF ALL THE ANALYSTS IN THE BOTH DEPT AND ALL THE MANAGERS IN 30TH DEPT
àUPDATES EMP SET SAL = SAL + 3000 WHERE JOB = ‘ANALYST’ AND DEPTNO = 20 OR JOB= ‘MANAGER’ AND DEPTNO= 30.

DELETE: IT IS USED TO DELETE THE DATA FROM THE TABLE.
SYNTAX:
DELETE FROM [WHERE]
àDELETE FROM DEPT
à ALL RECORDS IN DEPT TABLE ARE DELETED
à THIS IS CALLED HIGH LEVEL DELETION
àDELETE FROM EMP
à ALL RECORD IN EMP TABLE ARE DELETED.
*DELETE ALL CLERKS DETAILS FROM EMP TABLE:
à DELETE FROM EMP WHERE JOB=’CLERK’
· DELETE ‘SMITH’ DETAILS FROM EMP TABLE
àDELETE FROM EMP WHERE ENAME=’SMITH’
· DELETE ALL EMPLOYEE WORKING IN 20 DEPT
* DELETE FROM EMP WHERE DEPT NO=20.
TRUNCATE: IT IS ALSO USED TO DELETE THE DATA FROM THE TABLE .BY USING THIS COMMAND DATA CANNOT BE DELETED CONDITIONALLY. THIS STATEMENT DELETES THE DATA IN PAGE BY PAGE MANNER.
SYNTAX: TRUNCATE
àTRUNCATE EMP
àTRUNCATE DEPT
àTRUNCATE EMP WHERE DEPT NO= 30
ERROR

QUERY: IT IS A REQUEST FOR INFORMATION
SIMPLE SELECT: SELECT COMMAND IS USED TO RETRIEVE THE DATA FROM TABLE(S)
IT CANNOT MODIFY THE DATA IN THE TABLE.
SYNTAX:
SELECT COLUMN 1, COLUMN2, COLUMN 3, ……………. COLUMN N FROKM
SELECT * FROM
‘*’ MEANS ALL COLUMNS AND ALL ROWS IN TABLE
à SELECT EMPO, DEPT NO, ENAME, FROM EMP.
EMPNO DEPTNO ENAME
7369 20 SMITH
7499 30 ALLEN
7521 30 WORD
7934 10 MILLER
NOTE: THE ORDER OF THE COLUMNS IN THE OUTPUT DEPENDS ON THE ORDER OF THE COLUMNS IN THE SELECT STATEMENT
WRITE A QUERY TO DISPLAY THE COMPLETE DATA FROM EMP TABLE
à SELECT * FROM EMP

SELECT WITH WHERE CLAUSE: WHERE CLAUSE IS USED WITH SELECT TO RETRIEVE THE DATA ON THE CONDITION
SYNTAX: SELECT COLUMN 1, COLUMN 2, COLUMN FROM WHERE
(OR)
SELECT * FROM WHERE

WRITE A QUERY TO DISPLAY EMPNO, ENAME ,JOB FOR ALL MANAGERS.
àSELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB=’MANAGER’

WRITE A QUERY TO DISPLAY ALL DETAILS OF 30 DEPT SALES MANS
à SELECT * FROM EMP WHERE JOB= ‘SALES MAN’ AND DEPTNO = 30

WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEE WHO ARE GETTING MORE THAN 3000
à SELECT * FROM EMP WHERE SAL>3000

‘ORDER BY’ CLAUSE: IT IS USED TO DISPLAY THE DATA EITHER IN ASCENDING (OR) DESCENDING ORDER.
DEFAULT ORDER IS ASCENDING
IT ALWAYS APPEARS AT THE AND OF SELECT STATEMENT
IT CAN BE USED WITH ANY COLUMN

SELECT WITH ’WHERE’ AND ‘ORDER BY’ CLAUSES :
SYNTAX:
SELECT COL1, COL2 – COLUMN FROM [WHERE CONDITION] ORDER BY COLUMN[ASC]/DESC.
SELECT * FROM [WHERE] ORDER BY COLUMN[ASC] / DESC

WRITE A QUERY TO DISPLAY EMPNO ENAME, SAL FOR EMPLOYEES IN EMP TABLE IN ASCENDING ORDER SALARIES
à SELECT EMPNO, ENAME, SAL FROM EMP ORDER BY SAL [ASC]

WRITE A QUERY TO DISPLAY ALL EMPLOYEE DETAILS IN DESCENDING ORDER OF SALARIES
à SELECT * FROM EMP ORDER BY SAL DESC

WRITE A QUERY TO DISPLAY ALL ENAMES IN ASCENDING ORDER
à SELECT ENAME FROM EMP ORDER BY ENAME
ENAME
ADAMS
ALLEN
BLAKE
----
-----
-----
WARD
WRITE A QUERY TO DISPLAY ALL DETAILS OF ALL EMPLOYEES IN DESCENDING ORDER OF HIREDATE
à SELECT * FROM EMP ORDER BY HIREDATE

WRITE A QUERY ENAME, SAL, JOB OF ALL 10 DEPT EMPLOYEES IN ASCENDING ORDER OF SALARIES
à SELECT EMPNO, JOB, SAL FROM EMP WHERE DEPTNO=10 ORDER BY SAL

ENAME JOB SAL
MILLER CLERK 1300
OLARK MANAGER 2450
KING PRESIDENT 5000
WRITE A QUERY TO DISPLAY ENAME , JOB, SAL, DEPTNO FOR ALL MANAGER IN DESCENDING ORDER OF SALARIES
à SELECT ENAME, JOB,DEPTNO, SAL FROM EMP WHERE JOB= ‘MANAGER’ ORDER BY SAL DESC

COLUMN ALIAS: IT IS A METHOD OF ASSIGNING THE TEMPORARY NAMES FOR THE COLUMNS WHILE DISPLAYING THE DATA.

ALIAS NAME IMMEDIATELY FOLLOWS THE COLUMN NAME WITH OPTIONAL ‘AS’ KEYWORD BETWEEN COLUMN NAME AND ALIAS NAME.
ALIAS NAME SHOULD BE IN SINGLE QUOTATION MARKS IF IT CONTAINS SPACES AND CHARACTERS OTHER THAN A TO Z, A TO Z, 0 TO 9, $, #, _(UNDER SCORE).

EX: SELECT EMPNO, SAL, COMM. AS COMMISSION FROM EMP
EMPNO SALARY COMMISSION
-------- ---------- ------------------
à SELECT DEPTNO, DNAME AS ‘DEPTNAME’, LOC LOCATION FROM DEPT
DEPTNO DEPTNAME LOCATION


SELECT STATEMENT WITH ARITHMETIC EXPRESSIONS:
WRITE A QUERY TO DISPLAY EMPNO, SAL AS SALARY PER MONTH, SALARY PER YEAR FOR ALL EMPLOYEES
à SELECT EMPNO, SAL ‘SALARY PER YEAR’ FROM AMP

WRITE A QUERY TO DISPLAY EMPNO HRA, DA, PLF & GROSS FOR EMPLOYEES IN EMP TABLE BY SAL AS BASIC.
à SELECT EMPNO, SAL, BASIC SAL *0.45 HRA, SAL*0.35 DA, SAL * 0.15 PF SAL + SAL *0.45+SAL * 0.35 – SAL*0.15 GROSS FROM EMP
EMPNO BASIC HRA DA PF GROSS


FUNCTIONS: FUNCTION IS A PRE DEFINED PROGRAM SEGMENT THAT CARRIES SPECIFIC AND WELL DEFINED TASK.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
3. ROW SET FUNCTIONS

1. SCALAR FUNCTIONS: THIS FUNCTIONS WILL ACTS AN SINGLE VALUE & RETURNS A SINGLE VALUE
CLASSIFICATION OF SCALAR FUNCTIONS:
1. MATHEMATICAL FUNCTIONS
2. STRING FUNCTIONS
3. DATE & TIME FUNCTIONS
4. SYSTEM FUNCTIONS
5. SECURITY FUNCTIONS
6. CURSOR FUNCTIONS
7. SYSTEM STASTICAL FUNCTIONS
8. TEXT AND IMAGE FUNCTIONS
9. CONFIGURATION FUNCTION.
MATHEMATICAL FUNCTIONS:
i) ABSOLUTE: IT RETURN THE ABSOLUTE VALUE OF N
à SELECT ABS(-10.65)
10.65


à SELECT ABS(18.69)
18.67


ii) POWER (M, N): IT RETURN THE M POWER N VALUE
à SELECT POWER(3,2)
9


à SELECT POWER(6,2)
36



iii) SQRT(N): IT RETURN THE SQUARE ROOT VALUE OF N
à SELECT SQRT(2)
1.414






iv) SQUARE (N): IT RETURN THE SQUARE VALUE OF N
à SELECT ABS(9)
81


à SELECT ABS(5)
25


v) ROUND(M, N): IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.
à SELECT ROUND(15.143)
15


à SELECT ROUND(16.513)
16


à SELECT ROUND(16.816)
17


à SELECT ROUND(21.132,1)
21.1


à SELECT ROUND(25.143)
25



vi) CEILING: IT (CEILING) RETURNS THE SMALLEST INTEGER GREATER THAN ‘N’
à SELECT CEILING(15,13)
16


vii) FLOOR(N): IT RETURN THE LARGEST INTEGER LESS THAN ‘N’
à SELECT FLOOR(15.13)
14


STRING FUNCTIONS:
i) ASCII (CH): IT RETURNS THE ASCII VALUE OF GIVEN CHARACTER.
à SELECT ASCII(‘A’)
65


à SELECT ASCII(‘A’)
97


à SELECT ASCII(‘0’)
48


à SELECT ASCII(‘’)
32




ii) CHAR(N): IT RETURNS THE CHARACTER FOR GIVEN ASCII VALUE
à SELECT CHAR(65)
A



à SELECT CHAR(98)
B



iii) LOWER (STRING): IT CONVERTS THE UPPER CASE LETTERS IN STRING INTO LOWER CASES
à SELECT LOWER(‘ORACLE’)
ORACLE


iv) UPPER(STRING): IT CONVERTS THE LOWER CASES INTO UPPER CASES
à SELECT UPPER(ORACLE)
ORACLE



à SELECT ENAME, LOWER(ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
v) LEN(STRING): IT RETURNS THE LENGTH OF GIVEN STRING
à SELECT LEN(‘ORACLE’)
6



à SELECT LEN(‘ORACLE APPS’) SPACE ALSO CHARACTER
11


à SELECT ENAME, LEN(‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING) : IT WILL REVERSE THE GIVEN STRING
à SELECT REVERSE(‘ORACLE’)
ELCARO
àSELECT ENAME, REVERSE(ENAME) REV_ENAME FROM EMP
ENAME LENGTH
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING, ‘SEARCHING STRING’, ‘REPLACING STRING’)
à IT WILL REPLACE A SUB STRING IN ASTRING WITH GIVEN STRING
à SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)

LONG


à SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)

BLACK AND BLUE


SUB STRING (‘STRING’, ‘STARTING POSITION’, NO OF CHARACTERS REQUIRED);
IT WILL ENTRACT A SUBSTRING FLOW MAIN STRING
EX:à SELECT SUBSTRING (‘ORACLE’, 1, 3’)
ORA


à SELECT SUBSTRING (‘ORACLE’, 2, 2)

RA



àSELECT ENAME, SUBSTRING(ENAME,1,2) SUN_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL

DATE FUNCTIONS:
GET DATE (); IT RETURNS THE CURRENT DATE AND TIME
à SELECT GETDATE()
2005-4-5: 4:30.15.416

DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART, NUMBER, DATE): IT RETURNS THE DATE ACCORDING TO DATE PART.
à SELECT DATE (YY,1,’2005-3-15’)
2006-3-15



à SELECT DATEADD (MM,1,’1998-4-18’)
1998-4-18


à SELECT DATE ADD(DAY,1,’1999-04-26’)
2006-3-15




DATE DIFF(DATEPART, DATE 1, DATE 2): IT RETURNS THE DIFFERENCE BETWEEN THE DATES ACCORDING TO THE DATE PART
àSELECT DATE DIFF(YEAR, ‘1999-11-5’,’2000-11-15’)
SPECIAL OPERATORS IN SQL SERVER:
1. IN
2. NOT
3. BETWEEN
4. NOTBETWEEN
5. LIKE
6. NOT LIKE
7. IS NULL
1) IN(LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF CLERK, MANAGER AND ANALYST
A) SELECT * FROM EMP WHERE JOB IN(‘CLERK’, ‘MANAGER’,’ANALYST’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF 7369,7839,7466
A) SELECT * FROM EMP WHERE JOB IN(‘7369’,’7839’,’7466’)
Q) WRITE A QUERY TO DISPLAY ALL DETAILS OF SMITH, ALLEN, KING
A) SELECT * FROM EMP WHERE JOB IN(‘SMITH’, ‘ALLEN’, ‘KING’)
2) NOT IN(LIST OF VALUES): IT IS JUST OPPOSITE TO IN. IT DISPLAY ALL THE VALUES IN TABLE, OTHER THAN VALUES SPECIFIED IN LIST
àSELECT * FROM EMP WHERE JOB NOT IN (‘MANAGER’,CLERK’, ‘PRESEDENT’)
à OTHER THAN ‘MANAGER’, ‘CLERK’, ‘PRESEDENT’ JOBS DETAILS WILL BE DISPLAYED
3) BETWEEN: IT WILL DISPLAY THE VALUES SPECIFIED IN THE RANGE
EX: SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000
IT WILL DISPLAY ALL THE EMPLOYEES DETAILS WHO ARE GETTING SALARY MORE THAN OR EQUAL TO 2000 AND LESS THAN OR EQUAL TO 4000.
IT INCLUDES THE RANGE SPECIFIED, SO IT IS CALLED INCLUSIVE OPERATOR.

4) NOT BETWEEN: IT IS OPPOSITE TO ‘BETWEEN’ OPERATOR IT WILL DISPLAY THE VALUES OTHER THAN RANGE SPECIFIED.

Q) WRITE A QUERY TO DISPLAY AU DETAILS WHO ARE GETTING SALARIES LESS THAN 2000 AND MORE THAN 4000
A) SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
NOTE: NOT BETWEEN OPERATOR WILL NOT INCLUDE THE RANGE SPECIFIED, SO IT IS CALLED EXCLUSIVE OPERATOR.

5) LIKE:IT IS USED TO SEARCH FOR PATTERN IN CHARACTERS. IT ASSOCIATED WITH TWO CHARACTERS
(I) _ (UNDERSCORE)
(II) %
HERE % REPRESENTS ZERO OR MORE CHARACTERS AND UNDER SCORE REPRESENTS A SINGLE CHARACTER.
WAQ TO DISPLAY THE NAMES BEGIN WITH ‘S’
(B) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S’
ENAME
SMITH
SCOTT
WAQ TO DISPLAY NAMES END WITH ‘S’


(A) SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
WAQ TO DISPLAY NAMES CONTAINING ‘LL’ OR TT
àSELECT ENAME FROM EMP WHERE ENAME LIKE %LL% (OR) ENAME LIKE ‘%TT%’
SCOTT
MILLER
ALLEN
WAQ TO DISPLAY NAMES CONTAINING ‘A’ AS SECOND CHARACTER
(A) SELECT ENAME LIKE ‘_A%’
WAQ TO DISPLAY NAME CONTAINING 5 CHARACTERS
à SELECT ENAME FROM EMP WHERE ENAME LIKE’------‘

6) NOT LIKE: OPPOSITE TO LIKE OPERATOR.

7)IS NULL:IT IS USED TO TEST FOR NULL VALUES.
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE HAVING NULL COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NULL
WAQ TO DISPLAY THE EMPLOYEE WHO ARE NOT HAVING MANAGERS.
(A) SELECT * FROM EMP WHERE MGR IS NULL
8)IS NOT NULL:
WAQ TO DISPLAY ALL EMPLOYEES WHO ARE GETTING COMMISSION
(A) SELECT * FROM EMP WHERE COMM. IS NOT NULL
WAQ TODISPLAY THE EMPLOYEES WHO ARE HAVING THE MANAGERS
(a) SELECT * FROM EMP WHERE MGR IS NOT NULL
GROUP FUNCTIONS: THESE FUNCTIONS WILL ACTS ON GROUP OF VALUES AT A TIME AND RETURNS A SINGLE VALUES. GROUP FUNCTIONS WILL IGNORE THE NULL VALUES.
GROUP: SET OF RECORDS IN A TABLE
ENTIRE TABLE IS CONSIDERED AS A GROUP OR A TABLE CAN BE DIVIDED INTO NO., OF GROUPS
· SUM: RETURNS SUM OF VALUES IN COLUMN
· AVG: RETURNS THE AVERAGE VALUE FOR ALL VALUES IN COLUMB
· MIN: RETURNS THE MINIMUM VALUE FROM GIVEN COLUMN
· MAX: RETURNS THE MAXIMUM VALUE FROM GIVEN COLUMN
· COUNT: IT COUNT THE NUMBER OF VALUE OR RECORDS IN A GROUP. IT WILL TAKE THREE DIFFERENT PARAMETERS
COUNT(*)àIT CANOT NO OF RECORDS IN GROUP
COUNT(COLUMN_NAME): IT COUNTS NO., OF NOT NULL VALUES IN GIVEN COLUMN, INCLUDING DUPLICATE VALUES.
COUNT( DISTRICT COLUMN_NAME)à IT WILL COUNT NO., OF NOT NULL VALUES IN GIVEN COLUMN BUT IGNORE DUPLICATE VALUES.

TOTAL-SALARY AVERAGE MINIMUM MAXIMUM
29075 2073.21429 800 5000
EX: SELECT SUM (SAL) TOTAL_SAL, MIN(SAL) MIN_SAL MAX(SAL) MAX_SAL FROM EMP WHERE JOB=’CLERK’
TOTAL-SALARY MINIMUM MAXIMUM
29075 800 5000
SELECT SUM(SAL) SUM_SAL MAX(SAL) MAX_SAL FROM EMP WHERE DEPTNO=20
SUM_SAL MAX_SAL
10875 2975
SELECT MIN(SAL) MIN_SAL MAX(SAL), DEPT NO FROM EMP WHERE DEPTNO=30 (INVALID)
NOTE: SUM,.AVG FUNCTIONS WILL ACTS ONLY ON NUMERIC DATA BUT MIN, MAX FUNCTIONS WILL ACTS AN ANY TYPE OF DAYA
à SELECT MIN(ENAME) MIN_NAME, MAX(ENAME) MAX_ENAME FROM EMP
MIN_ENAME MAX_ENAME
ADAMS WARD
à SELECT MIN(HIRE DATE) MIN_DATE, MAX(HIREDATE) MAX_DATE FROM EMP
à SELECT COUNT (*) COUNT (DEPTNO ), COUNT (DISTINCT DEPT NO) FROM EMP
OUTPUT: 14 14 3
GROUP BY CLAUSE: IT IS USED TO DIVIDE THE TABLE INTO GROUPS
SYNTAX: SELECT , FROM [WHERE] GROUP BY GROUP_BY-EXPRESS[ORDER BY COLUMN ASC/DES]

WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPT NO
(A) SELECT DEPT NO, SUM(SAL) TOTAL_SAL, MIN(SAL) MM_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO.
WAQ TO DIVIDE THE TABLE INTO GROUP BASED ON DEPTNO AND DISPLAY 20,30 TH GROUP
(A) SELECT DEPTNO, SUM(SAL), SUM_SAL, AVG(SAL) AVG)SAL, COUNT(*) FROM EMP WHERE JOB IN (‘MANAGER’,’ANALYST’,’CLERK’) GROUP BY JOB

WRITE A QUERY TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO
(A) SELECT JOB, DEPTNO, MIN(SAL), MIN_SAL, MAX_SAL COUNT(*) FROM EMP GROUP BY JOB, DEPTNO.

HAVING CLAUSE: USED TO IMPOSE CONDITION ON GROUPED DATA
SYNTAX:à SELECT , GROUP FUNCTIONS FROM WHERE GROUP BY GROUP_BY_EXP HAVING ORDER BY COLUMN ASC/DES
WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON JOB AND DEPTNO. DISPLAY THE GROUP IF NO., OF RECORDS ARE MORE THAN 1
à SELECT JOB, DEPTNO, MIN(SAL) MIN_SAL, MAX(SAL), MAX_SAL, COUNT_*) FROM EMP GROUP BY JOB, DEPTNO HAVING COUNT(*)>1

JOB DEPTNO MIN-SAL MAX-SAL COUNT(*)
CLERK 20 800 1100 2

WAQ TO DIVIDE THE TABLE INTO GROUPS BASED ON DEPTNO & DISPLAY THE GROUPS IF SUM (SAL)> 500
(A) SELECT DEPTNO, SUM(SAL), AVG(SAL) FROM EMP GROUPE BY DEPTNO HAVING SUM(SAL) > 500.

WAQ TO DIVIDE THE TABLE IN GROUP BASED ON JOB& XXX MANAGER, CLERK, SALESMAN IF SUM(SAL)> 4000
(A) SELECT JOB, SUM(SAL) FROM EMP WHERE JOB IN (MANAGER, CLERK, SALESMAN) GROUP BY JOB HAVING SUM (SAL)>4000
SET OPERATORS: SET OPERATORS ARE USED TO COMBINE THE OUTPUTS OF TWO OR MOR QUERIES AS A SINGLE ONE THE QUERIES COMBINED BY THE SET OPERATORS SHOULD HAVE SAME NUMBER OF COLUMSNS AND THEIR CORRESPONDING DATA TYPES SHOULD BE SAME
1. UNION ALL
2. UNION
1. UNION ALL: IT WILL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES IT WILL NOT ELIMINATE DUPLICATE VALUES.
EX: à SELECT JOB FROM EMP WHERE DEPTNO = 10 UNION ALL SELECT JOB FROM EMP WHERE DEPTNO = 200

2. UNION: IT WIL COMBINE THE OUTPUTS OF TWO OR MORE QUERIES AND ELIMINATES THE DUPLICATE VALUES
EX: à SELECT JOB FROM EMP WHERE DEPTNO =10 UNION SELECT JOB FROM EMP WHERE DEPTNO =20.

DEPTNO DNAME, LOC , EMPNO ,ENAME ,JOB ,DEPTNO

JOIN: JOIN IS A QUERY THAT COMBINES THE ROWS FROM TWO OR MORE TABLE
TYPES OF JOIN
1. CROSS JOIN
2. INNER JOIN OR QUAL JOIN
3. OUTER JOIN
4. SELF JOIN
CROSS JOIN: IN CROSS JOIN EVERY ROW OF THE FIRST TABLE JOINS WITH EVERY ROW IN SECOND TABLE THE OUTPUT OF THE CROSS JOIN IS CALLED CARTESIAN PRODUCT

DEPT EMP
DNAME ENAME
ACCOUNTING SMITH
RESEARCH ALLEN
SALES
OPERATION MILLER
4 ROWS 14 ROWS
à SELECT ENAME, DNAME FROM EMP, DEPT
( OR)
à SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT
INNER JOIN OR EQUIJOIN:- A JOIN WHICH IS BASED ON EQUALITIES IS CALLED EQUL JOIN TABLES SHOULD HAVE COMMON COLUMN TO PERFORM EQUI JOIN

SYNTAX:-
SELECT TABLE 1, COLUMN 1, TABLE 2, COLUMN 2…… FROM TABLE, TABLE2
WHERE TABLE1 COLUMN = TABLE.COLUMN
(OR)
SELECT TABLE1, COLUMN1, TABLE 2, COLUMN 2,……….
INNER JOIN TABLE1, TABLE2, ON TABLE1. COLUMN = TABLE2. COLUMN

WAQ TO DISPLAY ENAME, JOB, DEPTNO DNAME, LOC FOR ALL EMPLOYEES IN EMP,DEPT TABLE,
A) SELECT ENAME, JOB, EMPDEPTNO, DNAME, LOC FROM EMP, DEPT, WHERE EMP DEPTNO = DET DEPTNO
WAQ TO DISPLAY ENAME, JOB,SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP. DEPTNO = DEPT. DEPTNO AND LOC= ‘NEWYORK’

3) OUTER JOIN: OUTER JOIN IS AN ENTENSION FOR THE EQUI JOIN. IT IS USEFUL TO RETRIVE UNMATCHED RECORDS ALONG WITH MATCHED RECORS.
I) LEFT OUTER JOIN:
SYNTAX:
SELECT TABLE1. COLUMN 1, TABLE 2. COLUMN2,.. FROM TABLE1, TABLE2 WHERE TABLE 1 COLUMN * + TABLE2.COLUMN.
à SELECT TABLE 1. COLUMN 1, TABLE 2. COLUMN2…..
FROM TABLE 1 LEFT OUTER JOIN TABLE 2 ON TABLE 1 COLUMN = TABLE2.COLUMN

IT WILL RETRIVES ALL THE MATCHED RECORDS FROM TABLE AND TABLE2 & UNMATCHED RECORDS FROM TABLE.
WAQ TO RETRIVE THE ALL MATCHED RECORDS AND UNMATCHED RECORDS FROM EMP TABLE
A) SELECT EMPNO, ENAME,EMP-DEPTNO, EDNO, DNAME, LOC ROM EMP, DEPT WHERE EMP DEPTNO *= DEPT.DEPTNO.
II) RIGHT OUTER JOIN:
à SELECT TABLE1.COLUMN1, TABLE.COLUMN2,-- FROM TABLE1, TABLE2
WHERE TABLE1. COLUMN *= TABLE2. COLUMN
à SELECT TABLE.COLUMN1, TABLE2.COLUMN2 TABLE 1 RIGHT OUTER JOIN TABLE ON TABLE1.COLUMN = TABLE 2. COLUMN.
IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE 1 & TABLE2 AND UNMATCHED RECORDS FROM TABLE2 (TABLE RIGHT TO THE RIGHT OUTER JOIN KEY WORD)
WAQ TO RETRIEVE ALL MATCHED RECORDS FROM EMP AND DEPT TABLE AND UNMATCHED RECORDS FROM DEPT TABLE.
A) SELECT EMPNO, ENAME, EMP.DEPTNO.EDNO, DEPT.DEPTNO DNO, FROM EMP, DEPT WHERE EMP.DEPTNO=*DEPT.DEPTNO.
(OR)
SELECT EMPO,ENAME, EMP.DEPTNO EDNO, DEPT.DEPTNO DNO, DNAME FROM EMP RIGHT OUTER JOIN DEPT ON EMP. DEPTNO = DEPT.DEPTNO

EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS

FULL OUTERJOIN: SELECT TABLE,.COLUMN, TABLE2.COLUMB
FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN = TABLE2.COLUMN

IT WILL RETRIEVE ALL THE MATCHED RECORDS FROM TABLE1 AND TABLE2 AND UNMATCHED RECORDS FROM BOTH TABLES
EX: SELECT EMPO, ENAME, EMP, DEPTNO ENDNO, DEPTNODNO, DNAME FROM EMP FULL OUTER JOIN DEPT ON EMP DEPTNO = DEPT DEPTMN.
EMPNO ENAME EDNO DNO DNAME
7369 SMITH 20 20 RESEARCH
7934 MILLER 10 10 ACCOUNTING
NULL NULL NULL 40 OPERATIONS
100 RAM 80 NULL NULL


SELF JOIN : JOINING THE TABLE TO ITSELF IS CALLED SELF JOIN.

EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7698 JONES 7839
7839 KING ---
7902 FORD 7839


WAQ TO DISPLAY EMPLOYEE NAME AND HIS MANAGER NAME
SELECT WORK ENAME EMPLOYEE, MANAGER ENAME BOSS FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO.

WRITE A QUERY TO DISPLAY ALL EMPLOYEES WHOSE SALARY IS MORE THAN ALLEN SALARY
SELECT B.* FROM EMP A, EMP B WHERE A.ENAME = ‘ALLEN’ AND A.SAL
WAQ TO DISPLAY ALL EMPLOYEE DETAILS WHOSE JOB SAME AS SMITH JOB?
A) SELECT B * FROM EMP A, EMP A, EMPBA WHERE A ENAME AND A JO = B JOB

WAQ TO DISPLAY ALL DETAILS OF ALL EMPLOYEES WHO ARE SENIOR THAN THEIR OWN MGR?
A) SELECT B * FROM EMP A EMPB WHERE B MGR = A.EMP NO. AND B HIRE DATE < A. HIREDATE.

WAQ TO DISPLAY THE DETAILS OF ALL EMPLOYEES WHO ARE WORKING IN SMITH DEPT.
A) SELECT * FROM EMP WHERE ENAME =’SMITH’

à SELECT DEPTNO FROM EMP WHERE ENAME = ‘SMITH’
à SELECT * FROM EMP WHERE DEPTNO = 20.



SUB QUARRIES: SUB QUERY IS A SELECT STATEMENT IN ANOTHER SELECT STATEMENT.
SYNTAX:
SELECT FROM < TABLE_NAME> WHERE < COLUMN> OPERATOR (SELECT FROM )

FIRST INNER QUERY WILL BE EXECUTED BASED ON THE RESULT OF INNER QUERY OUTER QUERY WILL BE EXECUTED.

TYPES OF SUB QUERIES: 1) SINGLE ROW QUERY 2) MULTI ROW SUB QUERY.
SINGLE ROW SUB QUERY : A SUB QUERY WHICH RETURN ONLY ONE VALUE IS CALLED SINGLE ROW SUB QUERY.

EXAMPLES OF SINGLE ROW SUB- QUERY
WAQ TO DISPLAY ALL EMPLOYEES WHOSE JOB IS SAME AS
SELECT * FROM EMP. WHERE JOB=(SELECT JOB FROM EMP WHERE ENAME= ‘FORD’)

WAQ TO DISPLAY ALL EMPLOYEES WHERE SALARIES ARE MORE THAN ‘SCOTT SALARY
A) SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME= ‘SCOTT’)

WAQ TO DISPLAY ALL EMPLOYEES WHOSE SALARIES ARE EQUAL TO MIN SALARY OF EMPTABLE.
A) SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP’)
SELECT DEPTNO, AVG(SAL), AVG_SAL FROM EMP GROUP BY DEPTNO

DEPTNO AVG_SAL
10 2179.666
20 2125
030 1566.66

WRITE A QUERY TO DISPLAY DEPTNO WITH AVG_SAL OF AVG_SAL IS MORE THAN AVG_SAL OF 20TH DEPT
A) SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =20)

WAQ TO DISPLAY ALL EMPLOYEE WHO ARE WORKING AS MANAGER TO OTHER EMPLOYEES
A) SELECT * FROM EMP WHER EMPNO= (SELECT DISTCT MGR FROM EMP)


SPECIAL OPERATORS WITH SUB_QUERIES: THE FOLLOWING SPECIAL OPERATIONS ARE USED WITH SUB_QUERIES, IF SUB QUERY RETURNS MORE THAN ONE VALUES.
1) IN
2) ANY
3) ALL
4) EXISTS
1) IN: (LIST OF VALUES): IT PICKS THE VALUES FROM THE LIST.
A) SELECT * FROM EMP WHERE EMPNO IN (SELECT.DISTRICTMAGR FROM EMP)
2) ANY:
I)> ANY (LIST OF VALUES): IT PICKS THE MINIMUM VALUE FROM THE LIST AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE MORE THAN MIN VALUE IN THE LIST.
EX:
WAQ TO DISPLAY ALL EMPLOYEE DETAILS FROM EMP TABLE IF THEIR SAL MORE MIN SAL OF 10TH DEPT 1300
A) SELECT * FROM EMP MORE MIN SAL> ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10,2450,5000,1300)

II) < ANY(LIST OF VALUES): IT PICKS THE MAN VALUE FROM THE LIST AND IT DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MAN VALUE IN THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEES IF THEIR SALARY LESS THAN MANX SALARY OF 20TH DEPT.
A) SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 20) 800,1100,2975,3000

(OR)
SELECT * FROM EMP WHERE SAL < (SELECT MAX (SAL) FROM EMP WHERE DEPTNO =20)

3) ALL:
I) > ALL (LIST OF VALUES): IT PICKS THE MAX VALUES FROM THE LIST AND IT WILL DISPLAY ALL VALUES FROM TABLE WHICH ARE MORE THAN MAX VALUES IS THE LIST.
EX: WAQ TO DISPLAY ALL EMPLOYEE OF THEN SALARY IS MORE THAN MAX SAL OF 20 DEPT.
A) SELECT * FROM EMP WHERE SAL> ALL (SELECT SAL FROM EMP WHERE DEPTNO=20).

II) < ALL (LIST OF VALUES): IT PICK THE MIN VALUE FROM THE LIST AND (1300,2450,5000) AND IT WILL DISPLAY ALL THE VALUES FROM TABLE WHICH ARE LESS THAN MIN VALUES IN LIST.

WAQ TO DISPLAY ALL EMPLOYEE IF THE EMPLOYEE SAL IS LESS THAN MIN SAL OF 10TH DEPT
A) SELECT * FROM EMP WHERE SAL< ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10) 2450,1300,5000

a. >ANY à GREATER THAN MIN
b. c. >ANY à MORE THAN MANY
d. EXISTS: IT IS A BOOLEAN AN OPERATOR RETURNS SUCCESS OR FAILURE OF A QUERY.
SELECT JOB, SUM(SAL) SUM_SAL, MIN(SAL)MIN_SAL FROM EMP WHERE JOB=’MANAGER’ GROUP BY JOB AND EXISTS SELECT COUNT(*) FROM EMP WHERE JOB =’MANAGER’ GROUP BY JOB HAVING COUNT (*) > 2.

CO_RELATED SUB QUERIES: IF A SUB QUERY EVALUATED REPEATEDLY ONCE FOR EACH ROW SELECTED BY THE OUTER QUERY SUCH SUB QUERIES ARE CALLED CORRELATED SUB QUERY.
HERE OUTER QUERY WILL BE EXECUTED FIRST, BASED AN THE RESULT OF OUTER QUERY INNER QUERY WILL BE EXECUTED.
IF OUTER QUERY HAS RETRIEVED N RECORDS THE INNER QUERY HAS TO EXECUTE N TIMES.

WRITE QUERY TO DISPLAY EMPLOYEES DETAILS IF THE SALARY OF EMPLOYEE IS MORE THAN AVG_SAL OF THIS DEPT.
SELECT E.EMPNO, E.ENAME. E.SAL, E.DEPTNO FROM EMP E WHERE E.SAL> (SELECT AVG(SAL) FROM EMP WHERE EMP.DEPTNO=E.DEPTNO)

WAQ TO DISPLAY ALL THE EMPLOYEES WHO ARE SENIORS THAN THEIR OWN MANAGERS.
SELECT E.EMPO, E.ENAME, E.SAL, E.HIREDATE, E.MGR FROM EMP E.
WHERE E.HIREDATE<(SELECT HIREDATE FROM EMP WHERE EMP.EMPNO=E.MGR)
WAQ TO DISPLAY FIRST HIGHEST SALARY IN EMP TABLE
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE 0=(SELECT COUNT(*)) FROM EMP E WHERE EMP SAL
WAQ TODISPLAY 2ND HIGHEST SALARY IN EMP TABLE
SELECT EMPNO, ENAME, JOB,SAL, FROM EMP WHERE 1=(SELECT COUNT (*) ) FROM EMP E WHERE EMP SAL
DATA INTEGRITY:THE RELIABILITY AND ACCURACYOF DATA IS CALLED DATA INTEGRITY.
TYPES OF DATA INTEGRITY:
· ENTITY INTEGRITY
· DOMAIN INTEGRITY
· REFERENTIAL INTEGRITY
· USER DEFINED INTEGRITY

1) ENTITY INTEGRITY: BASIC IDEA BEHIND ENTITY INTEGRITY IS EACH TABLE SHOULD HAVE A COLUMN, THAN UNIQUELY IDENTIFIES ALL REMAINING COLUMNS.
2) DOMAIN INTEGRITY: DOMAIN INTEGRITY REFERS TO THE RANGE OF VALUES ALLOWED PER COLUMN OF A TABLE
3) REFERENTIAL INTEGRITY: REFERENTIAL INTEGRITY REFERS TO THE MAINTENANCE OF RELATIONSHIP BETWEEN TABLES.
4) USER DEFINED INTEGRITY: IT WILL ALLOW THE USER OR ADMINISTRATOR TO ENFORCE A NEW BUSINESS RULE WHICH WILL NOT COMES UNDER ABOVE 3 RULES.
CONSTRAINTS: IT IS A MECHANISM AUTOMATICALLY ACTIVATED WHEN USER PERFORMS DML OPERATIONS ON THE TABLE.

TYPES OF CONSTRAINTS:
1) UNIQUEà IT WILL NOT ALLOW DUPLICATE VALUES BUT ALLOWS NULL VALUES
2) NOT NULLà IT WILL NOT ALLOW THE NULL VALUES.
3) PRIMARYà IT IS A COMBINATION OF UNIQUE, NOT NULL AND CLUSTERED INDEX.
4) CHECKà IT WILL CHECK VALUES PROVIDED FOR COLUMN
5) FOREIGN KEYà IT IS USEFUL TO ESTABLISH PARENT / CHILD OR MASTER / DETAILED RELATION SHIP BETWEEN THE TABLES, FOREIGN KEY COLUMN OF THE CHILD TABLE IS AWAYS LINKED TO EITHER PRIMARY KEY COLUMN OR UNIQUE CONSTRAINT COLUMN OF THE PARENT TABLE.
6) DEFAULTà IT IS USEFUL TO INSERT TO DEFAULT VALUE INTO A COLUMN WHEN USER WILL NOT PROVIDE ANY VALUE WHILE INSERTING THE DATA INTO THE TABLE.

CONSTRAINTS CAN BE ADDED TO TABLE IN TWO LEVELS
1) COLUMN LEVEL 2) TABLE LEVEL
1) COLUMN LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED IMMEDIATELY OF FOR DEFINING THE COLUMN
à ONLY ONE PRIMARY KEY COLUMN IS ALLOWED PER TABLE.
EX:
CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY, DNAME VARCHAR UNIQUE NO NULL, LOC VARCHAR(10) DEFAULT ‘HYD’)

CREATE TABLE EMP (EMPNO INT PRIMARY KEY, ENAME VARCHAR NOT NULL, JOB VARCHAR(10), MGR INT, HIREDATE DEFAULT GETDATE(), SAL INT CHECK (SAL>=1000), COMM INT, DEPTNO INT REFERENCES DEPT(DEPTNO) AN DEPT DELETE CASCADE.

ADVANTAGE OF ON DELETE CASCADE: WITH OUT SPECIFYING THE ON DELETE CASCADE IT IS NOT POSSIBLE TO DELETE THE RECORD IN THE BASE TABLE IF THERE ARE DEPENDENT RECORDS FROM THE CHILD TABLE FOR THAT RECORD
IF ON DELETE CASCADE IF USED WHEN THE RECORD IN THE BASE TABLE IS DELETED ALL THE DEPENDENT RECORDS IN THE CHILD TABLE WILL BE ALSO BE DROPPED.
à CREATE TABLE INCR(EMPNO INT REFERENCES EMP(EMPNO) ON DELETE CASCADE, AMOUNT DECIMAL (5,2), DATE DATETIME)

DISPLAYING THE NAMES OF THE CONSTRAINS:
SYNTAX: SP_HELP CONSTRAINT

ASSIGNING THE NAMES FOR THE CONSTRAINTS WHILE CREATING THE TABLE:
SYNTAX:à CREATE TABLE (COLUMN1 DATATYPE(SIZE)CONSTRAINT ,COLUMN2DATATYPE(SIZE) CONSTRAINT)
EX:
CREATE TABLE DEPT(DEPTNO INT CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY, DNAME VARCHAR(20) CONSTRAINT DEPT_DNM_UQ UNIQUE, LOC VARCHAR(10)).

à CREATE TABLE EMPL(EMPNO INT CONSTRAINT EMPL_EMPO_PK PRIMARY KEY, ENAME VARCHAR(20) NOT NULL, JOB VARCHAR(10) , MGR INT, SAL INT CONSTRAINT EMPL_SAL_CHK CHECK(SAL>=1000) COMM. INT, DEPTNO INT CONSTRAINT EMPL_DEPTNO_FK REFERENCES DEPT1(DEPTNO) ON DELETE CASCADE)

INSERTING VALUES INTO THE TABLE CONTAINING CONSTRAINTS:
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
INSERT INTO DEPT VALUES(10,’RESEARCH’, ‘DALLAS’)
ERROR DUE TO VIOLATION OF P.K. CONSTRAINT
INSERT INTO DEPT VALUES(10,’ RESEARCH’, ‘DALLAS’)
ERROR DUE TOVIOLATION OF P.K CONSTRAINT
INSERT INTO DEPT VALUES(10,’ACCOUNTING’, ‘NEWYORK)
ERROR DUE TO TO VIOLATION OF UNIQUE CONSTRAINT

ACTIVATING THE DEFAULT CONSTRAINTS:
INSERT INTO DEPT VALUES(20,’RESEARCH’, DEFAULT)
INSET INTO DEPT(DEPTNO, DNAME) VALUES (30, ‘SALES’)

SELECT * FROM DEPT

INSERT INTO EMP(EMPNO, ENAME, SAL) VALUES (7369,’SMITH’,800)
ERROR DUE TO TO VIOLATION OF CHECK CONSTRAINT
TABLE LEVEL CONSTRAINTS: HERE CONSTRAINTS ARE ADDED AFTER DECLARING ALL COLUMNS
CONSTRAINTS ADDED FOR EXISTING TABLE COMES UNDER TABLE LEVEL.
MORE THAN ON COLUMN PER TABLE CAN BE DECLARED AS EITHER PRIMARY KEY OR FOREIGN KEY COLUMNS
DEFAULT AND NOT NULL CONSTRAINTS ARE NOT ALLOWED.

CREATE TABLE DEPT(DEPTNO INT, DNAME, VARCHAR(20) NOT NULL , (10) VARCHAR(10) DEFAULT ‘HYD’ UNIQUE (DNAME), PRIMARY KEY(DEPTNO))

CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR 920) NOT NULL, JOB VARCHAR(10), MAGR INT, HIREDATE DATETIME, SAL INT COMM. INT,. PRIMARY KEY(EMPNO), CHECK(SAL>=1000), FOREIGN KEY(DEPTNO) REFERENCES DEPT (DEPTNO) AN DELETE CASCADE)

ADDING CONSTRAINTS FOR EXISTING TABLE:
SYNTAX : ALTER TABLE ADD CONSTRAINTS TYPE (COLUMN_NAME)

à CREATE EMP, DEPT TABLES WITHOUT CONSTRAINTS
à ALTER TABLE DEPT ADD PRIMARY KEY (DEPTNO)
à ALTER TABLE DEPT ADD UNIQUE (DNAME)
à ALTER TABLE EMP ADD PRIMARY KEY (DNAME)
à ALTER TABLE EMP ADD CHECK (SAL>=1000)
à ALTER TABLE EMP ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.

ASSIGNING THE CONSTRAINTS FOR EXISTING TABLE WITH USER NAMES:
à CREATE TABLE DEPT(DEPTNO INT, DNAME VARCHAR(20) NOT NULL, 10C VARCHAR(10) CONSTRAINT DEPT_LOC_DF DEFAULT ‘HYD’, CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY(DEPTNO), CONSTRAINT DEPT_DNM_UQ UNIQUE(DNAME).

à CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20), JOB VARCHAR(10), MGR INT, HIREDATE DATETIME, SAL INT COMM. INT, DEPTNO INT, CONSTRANT EMP_EMPNO_PK PRIMARY KEY(EMPNO), CONSTRAINT EMP SAL_CHK CHECK(SAL>=1000)
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE)

ADDING CONSTRAINTS FOR EXISTING TABLES WITH USER NAMES:
Q) CREATE EMP AND DEPT TABLE WITHOUT CONSTRAINTS
A) SYNTAX:
àALTER TABLE ADD CONSTRAINT CONSTRAINT TYPE (COLUMN)
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DEPT_DEPTNO_PK PRIMARY KEY (DEPTNO).
à ALTER TABLE DEPT ADD CONSTRAINT DEPT_DNM_UQ UNIQUE (DNAME)
à ALTER TABLE DEPT ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO).
à ALTER TABLE EMP ADD CONSTRAINT EMP_SAL_CHK CHECK (SAL>=1000)
à ALTER TABLE EMP ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO) ON DELETE CASCADE.

DROPPING THE CONSTRAINT:
SYNTAX:
ALTER TABLE DROP CONSTRAINT < CONSTRAINT_NAME>

à ALTER TABLE EMP DROP CONSTRAINT EMP_EMPNO_PK
à CREATE TABLE BANK (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), OPEN_BALANCE INT, OPEN_DATE DATETIME, PRIMARY KEY (ACNO, NAME))
à CREATE TABLE FROM (ACNO INT, NAME VARCHAR(20) ACC_TYPE CHAR(1), AMOUNT INT, TRAN_DATE DATETIME FOREIGN KEY (ACNO, NAME) REFERENCES BANK (ACNO, NAME) AN DELETE CASCADE)
ACNO NAME
100 ****
200 ****
300 ****
400 ****

.SELF REFERENTIAL INTEGRITY: THE FOREIGN KEY COLUMN OF THE TABLE IS REFERENCE TO THE PRIMARY COLUMN IN THE SAME TABLE
EX: CREATE TABLE EMP_EMP(ENAME INT, ENAME VARCHAR (20), JOB VARCHAR (10), MAGR INT, PRIMARY KEY (EMPNO), FOREIGN KEY (MGR) REFERENCES EMP_EMP(EMPNO)

EMPNO ENAME JOB MGR

à FIRST INSERT DATA INTO EMPNO, ENAME & JOB COLUMNS LATER UPDATE ‘MGR’ COLUMN
à ADDING NOT NULL CONSTRAINT FOR COLUMN OF A TABLE.
SYNTAX: ALTER TABLE ,TABLE_NAME> ALTER COLUMN DATA TYPE (SIZE) NOT NOT NULL
à ALTER TABLE DEPT ALTER COLUMN LOC VARCHAR (10) NOT NULL

DATA STORAGE: DATA OF EACH TABLE IS STORED IN THE COLLECTION OF DIFFERENT DATA PAGES, EACH PAGE WILL HAVE THE 96 BYTES PAGE HEADER. PAGE HEADER CONTAINS PREVIOUS POINTER, NEXT POINTER, AND OBJECT ID.
PREVIOUS POINTER POINTING TO PREVIOUS PAGE, NEXT POINTER POINTING TO NEXT PAGE.
PREVIOUS POINTER VALUE FOR FIST PAGE IS NULL
NEXT POINTER VALUE FOR LAST PAGE IS NULL

DATA ACCESS: SQL SERVER USES FOLLOWING TWO METHODS TO ACCESS DATA
1) TABLE SCAN 2) BY USING INDEX
TABLE SCAN: IT HAS TO START FROM THE BEGINNING OF THE TABLE AND SCAN EACH AND EVERY PAGE FOR THE REQUIRED DATA.
BY USING INDEX: IT WILL TRAVERSE THOUGH THE INDEX TREE STRUCTURE TO FIND THE REQUIRED DATA AND EXTRACT THE DATA THAT SATISFY THE QUERY CRITERIA.
INDEXED: INDEXED IN SQL SERVER IS SIMILAR TO INDEX IN ABOOK. INDEXED ARE USED TO IMPROVE THE PERFORMANCE OF QUERIES.

INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS
1) PRIMARY KEY COLUMN
2) FOREIGN KEY COLUMN: FREQUENTLY USED IN JOIN CONDITIONS.
3) COLUMN WHICH ARE FREQUENTLY USED IN WHERE CLAUSE
4) COLUMNS WHICH ARE USED TO RETRIEVE THE DATA IN SORTING ORDER.

INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:
THE COLUMNS WHICH ARE NOT USED FREQUENTLY USED IN WHERE CLAUSE.
COLUMNS CONTAINING THE DUPLICATE AND NULL VALUES
COLUMNS CONTAINING IMAGES, BINARY INFORMATION, AND TEXT INFORMATION.



TYPES OF INDEXEX:
1) CLUSTERED INDEX
2) NON-CLUSTERED INDEX
3) UNIQUE INDEX
4) COMPOSITE INDEX
1) CLUSTERED INDEX: ONLY ONE CLUSTERED INDEX IS ALLOWED FOR A TABLE. THE ORDER OF VALUES IN A TABLE ORDER OF VALUES IN INDEX IS ALSO SAME. WHEN CLUSTER INDEX IS CREATED ON TABLE DATA IS ARRANGED IN ASCENDING ORDER CLUSTER INDEX WILL OCCUPY 5% OF THE TABLE.
SYNTAX: CREATE CLUSTERED INDEX ON (COLUMNS)
à CREATE CLUSTERED INDEX EMP_CLINDEX ON EMP (EMPNO)
NON_CLUSTERED INDEX: IT IS THE DEFAULT INDEX CREATED BY THE SERVER THE PHYSICAL ORDER OF THE DATA IN THE TABLE IS DIFFERENT FROM THE ORDER OF THE VALUES IN INDEX.
MAX NO. OF NON-CLUSTERD INDEXED ALLOWED FOR TABLE IS 249

SYNTAX:
CREATE NON-CLUSTERED INDEX ON TABLE_NAME < COLUMNS>
CREATE NONCLUSTERED INDEX EMP_SAL ON EMP (DEPTNO, SAL)
UNIQUE INDEX : AN INDEX WITH UNIQUE CONSTRAINT. IT WILL NOT ALLOW DUPLICATE VALUES.
SYNTAX: CREATE UNIQUE INDEX ON < TABLENAME> (COLUMN)
à CREATE UNIQUE INDEX DEPT_INDEX ON DEPT (DNAME)

COMPOSITE INDEX: IF A UNIQUE INDEX IS CREATED ON MORE THAN ONE COLUMN IS CALLED COMPOSITE INDEX.
à CREATE UNIQUE INDEX DEPT_COMPOSITE ON DEPT (DEPTNO, DNAME)

TRANSCACTIONS:
1) AUTO COMMIT TRANSACTION
2) EXPLICIT TRANSACTION
3) IMPLICIT TRANSACATION

1) AUTO COMMIT TRANSATION: EVERY INDIVIDUAL TRANSACTION IS AN AUTO COMMIT TRANSACTION.
EX: UPDATE EMP SET SAL= SAL + 100 WHERE DEPTNO
2) EXPLICIT TRANSACTION: TRANSACTION BEGINS WITH BEGIN TRAN AND ENDS EXPLICITLY WITH EITHER COMMIT OF ROLLBACK
à BEGIN TRAN
INSET INTO DEPT VALUES (10,. ‘ACCOUNTING’, ‘NEWYORK’)
à ROLLBACK
INSERT OPERATION IS CANCELLED

3) IMPLICIT TRANSACTION:
à SET IMPLICIT TRANSACTIONS ON/OFF
àUPDATE EMP SET SAL = SAL + 1000 WHERE JOB = ‘MANAGER’
à DELETE FROM EMP WHERE DEPTNO = 10
à COMMIT

UPDATE AND DELETE OPERATIONS ARE CANCELLED

SAVE POINT: SAVE POINTS ARE LIKE PAPER MARKERS USED TO MARKER THE TRANSACTION.
SYNTAX: SAVE BEGIN FROM
à INSERT INTO EMP (EMPNO,ENAME) VALUES (100, ‘RAM’)
SAVE FROM A
UPDATE EMP SET SAL = SAL + 100 WHERE EMPNO=7839
SAVE FROM B
INSET INTO EMP (EMPNO, ENAME) VALUES (200,’***’)
DELETE FROM EMP WHERE EMPNO=7369
ROLLBACK FROM B
à DELETE AND INSERT WILL BE CANCELLED
ROLLBACK FROM A
à UPDATE WILL BE CANCELLED

COMMIT / ROLLBACK
-à INSET WILL BE SAVED / INT WILL BE CANCELLED



PROGRAMMING CONSTRUCTS: PROGRAMMING CONSTRUCTS ARE USED TO EXECUTE THE SET OF SQL STATEMENTS AS A UNIT..
1) IF – ELSE
2) BEGIN – END
3) WHILE
DECLARING VARIABLE: DECLARE @ VARIABLE DATATYPE, @ VARIABLE 2 DATATYPE
DECLARE @A INT, @ VJOB VARCHAR (20)
EX: SET @ A =5
SET @ B =10
SET @ VJOB =’CLERK’
DISPLAYING THE MESSAGE OR VALUE IN THE VARIABLE:
PRINT ‘MESSAGE’
OR
PRINT @ VARIABLE
WRITE A PROGRAM TO FIND THE SUM OF 2 NUMBERS
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ VJOB =@A+@B
PRINT THE SUM OF A&B IS MISTAKE CONVERT (VARCH, @C)
WRITE A PROGRAM TO FIND THE SIMPLE INTEREST
à DECLARE @ P INT, @N DECIMAL(5,2) @R DECIMAL (5,2), @ SI DECIMAL (5,2)
SET @ P =10000
SET @ N =15
SET @ R = 7.5
SET @ SL =(@P * @N * @R)/100
PRINT SIMPLE INTEREST IS + CONVERT (VARCHAR, @SI)

3) WRITE A PROGRAM TO SWAP THE VALUES IN THE VARIABLES.
à DECLARE @A INT, @B INT, @C INT
SET @ A =5
SET @ B =10
SET @ C =@A
SET @ A =@B
SET @ B =@C
PRINT ‘ A=:’+ CONVERT (VARCHAR, @A)
PRINT ‘ B=:’+ CONVERT (VARCHAR, @B)

4) WRITE THE ABOVE PROGRAM WITHOUT USING THE THIRD VARIABLE

IF- ELSE: IT IS USED TO EXECUTE THE STATEMENTS BASED ON CONDITION.
SYNTAX : IF
< STATEMENT 1>
ELSE
< STATEMENT 2>
IF CONDITION IS TRUE THEN STATEMENT1 IS EXECUTED OTHERWISE STATEMENT 2 WILL BE EXECUTED
DECLARE @A INT, @B INT,
SET @ A =5
SET @ B =10
IF(@A>@B)
PRINT ‘B IS BIG’
ELSE
PRINT ‘B IS BIG OR EQUAL TO A’

DECLARE @A INT
SELECT @=COUNT(*) FROM EMP
IF (@A>0)
PRINT ‘TABLE WILL HAVE RECORDS’
ELSE
PRINT ‘NO RECORD IN THE TABLE
DECLARE @A VARCHAR(10)
SELECT @A= JOB FROM EMP WHERE EMPNO =7369
IF (@A=’CLERK’)
PRINT ‘EMPLOYEE WORKING AS CLERK
ELSE
PRINT ‘EMPLOYEE IS :’ +@A

WHILE:
SYNTAX: WHILE(CONDITION)

IF THE CONDITION IS TRUE THEN STATEMENTS UNDER WHICH WILL BE EXCLUDED

WRITE A PROGRAM TO PRINT THE FIRST 10 NO’S USING WHILE
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
PRINT @N
SET @N = @N+1
END
WRITE A PROGRAM TO PRINT ALL EVEN NO’S FROM 1 TO 10
à DECLARE @N INT,
SET @ N =1
WHILE(@N<=10)
BEGIN
IF (@N/2=0)
PRINT @N
SET @N = @N+1
END
VIEWS: VIEWS IS A LOGICAL TABLE BASED ON TABLE OR A VIEW VIEW WILL NOT HOLD NAY DATA OF ITS. IT IS JUST LIKE A WINDOW THROUGH WHICH WE CAN VIEW DATA IN THE TABLE.

TYPES OF VIEWS:
1) SIMPLE VIEW
2) COMPLEX VIEW
SIMPLE VIEW: A VIEW BASED ON SINGLE TABLE IS CALLED A SIMPLE VIEW.
· ALL DML ARE ALLOWED ON THE SIMPLE VIEW
· IT WILL NOT HAVE ANY ARITHMETIC EXPRESSIONS AND GROUP FUNCTIONS.

SYNTAX:
CREATE VIEW AS SELECT STATEMENT [WITH CHECK OPTION]
EX: SELECT * FROM EMP_VIEW
à IT WILL DISPLAY ALL DETAILS EMP TABLES.
INSERT INTO EMP _ VIEW (EMPNO, ENAME, DEPTNO) VALUES (100, RAM, 20)
à UPDATE EMP_VIEW SET SAL = SAL + 100
à DELETE FROM EMP_VIEW WHERE DEPTNO = 10
à ALL 10 DEPT EMPLOYEE DETAILS ARE DELETED FROM TABLE.

THE DML OPERATIONS PERFORMED ON THE VIEW WITH AFFECTS THE TABLE.
EX: CREATE VIEW V10 AS SELECT * FROM EMP WHERE DEPTNO – 10
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (200, ‘JAMES’,10)
à INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (30, ‘MORTA’,20)
à SELECT * FROM V10
à IT WILL DISPLAY ALL 100 DEPT RECORDS. IT WILL NOT DISPLAY RECENTLY INSERTED 20 DEPT RECORD SINCE IT IS FOCUSING ONLY ON 10 DEPT.
à SELECT * FROM EMP
à ALONG WITH OLD RECORDS NEWLY INSERTED RECORDS WILL ALSO BE DISPLAYED.
à UPDATE VIO SET SAL= SAL+ 1000
à ALL 10 DEPT EMPLOYEES.
SALARIES ARE MODIFIED

CREATING VIEW BASED ON ANOTHER VIEW:
DEPT NO DNAME LOC


à CREATE VIEW DEPT_V1 AS SELECT * FROM DEPT
à SELECT * FROM DEPT_V1



à CREATE VIEW DEPT_V2 AS SELECT DEPTNO, DNAME FROM DEPT_V1
à SELECT * FROM DEPT_V1
DEPT NO DNAME




à CREATE VIEW V1 AS SELECT EMPNO, ENAME, JOB, DEPT FROM EMP
à SELECT * FROM DEPT_V1
EMPNO ENAME JOB DEPTNO

à CREATE VIEW V2 AS SELECT EMPNO, ENAME, SAL FROM V1
CREATE VIEW WITH CHECK OPTION:

à CREATE VIEW V20 AS SELECT * FROM EMP WHERE DEPTNO=20 WITH CHECK OPTION.
à SELECT * FROM V20
à IT WILL DISPLAY ONLY 20 DEPT EMPLOYEE DETAILS
à INSERT INTO V20 (EMPNO, ENAME,DEPTNO) VALUES (111,’ CHRIS’,10)
à THIS RECORDS WILL NOT BE INSERTED SINCE VIEW IS CREATED WILL CHECK OPTION

DISPLAYING THE SELECT STATEMENT ASSOCIATED WITH THE VIEW
SYNTAX:
SP_HELPTEXT
2) COMPLEX VIEWS: A VIEW BASED ON
I) ARITHMETIC EXPRESSIONS
II) GROUP FUNCTIONS
III) MORE THAN ONE TABLE IS CALLED COMPLEX VIEW

EX:
à CREATE VIEW EMP_GROUP AS SELECT DEPTNO, SUM(SAL) SUM_SAL, AVG(SAL) AVG_SAL, MIN(SAL), MIN_SAL, MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO
à SELECT * FROM EMP_GROUP
DEPTNO SUM_SAL AVG_SAL MIN_SAL MAX_SAL


VIEW BASED ON ARITHMETIC EXPRESSION:
à CREATE VIEW EMP_ARTH AS EMPNO, SAL, BASIC, SAL+0.35, HRA, SAL+0.25, DAA+ 0.15,PF, SAL +0.35+SAL*0.25- SAL*0.15 GROSS FROM EMP

SELECT * FROM EMP_ARTH
DEPTNO BASIC HRA DA PF GROSS


VIEW BASED ON MORE THAN ONE TABLE:
à CREATE VIEW EMP_DEPT AS SELECT ENAME, JOB, SAL, DNAME, LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO.
à SELECT * FROM EMP_DEPT

ENAME JOB SAL DNAME LOC
SMITH CLERK 800 RESEARCH DALLAS
ALLEN SALESMAN 1600 SALES CHICAGO
MILLER CLERK 1300 ACCOUNTING NEWYORK

à INSERT INTO EMP_DEPT(ENAME, JOB, SAL, DNAME, LOC) VALUES (‘***’, ‘JI’, 1000, ‘IMPORT’, ‘HYD’)
à ERROR SINCE IT IS NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW
à INSET INTO EMP_DEPT (ENAME.JOB.SAL) VALUES (‘***’, ‘JI’, 1000)
à THIS IS VALID STATEMENT.

DROPPING THE VIEW
DROP VIEW
à DROP VIEW EMP DEPT

ALTERING THE VIEW:
ALTER VIEW AS SELECT STATEMENT

CURSORS:
RESULTS SET: SELECT STATEMENT RETURNS ALL THE ROWS WHICH SATISFIED THE CONDITION IN WHERE CLAUSE THE SET OF ROWS RETURNED BY THE SELECT ARE CALLED RESULT SET.
CURSOR: CURSOR IS A LOGICAL AREA USED FOR STORING TRANSACTION INFORMATION TEMPORARILY CURSOR IS THE DATABASE OBJECT USED TO PROCESS THE SET OF RECORDS INDIVIDUALLY, ON RECORD AT A TIME.

TYPES OF CURSOR:
1) STATIC CURSOR
2) DYNAMIC CURSOR
3) FORWARD ONLY CURSOR
4) KEY SET DRIVEN CURSOR

NECESSARY STEPS TO PROCESS CURSOR
1) DECLARE CURSOR
2) OPEN CURSOR
3) FETCH RECORD FROM THE CURSOR
4) CLOSE CURSOR
5) DEAL LOCATE CURSOR

1) DECLARE THE CURSOR:
SYNTAX: DECLARE CURSOR CURSOR TYPE FOR SELECT STATEMENT
EX: DECLARE EMP_CUR CURSOR STATIC FOR SELECT * FROM EMP
à DECLARE AUTHORS_CUR CURSOR DYNAMIC FOR SELECT * FROM AUTHORS
CURSOR DECLARATION THE NAME OF THE CURSOR AND SELECT STATEMENT ASSOCIATED WITH IT.

2) OPENING THE CURSOR:
SYNTAX: OPEN
OPEN EMP_CUR
OPEN AUTHORS_CUR
3) FETCH DATA FROM THE CURSOR: ONCE CURSOR IS OPENED RECORDS CAN BE FETCHED FROM IT
SYNTAX: FETCH FROM (OR)
FETCH FROM INTO
FETCH STATEMENT IS USED TO DISPLAY DATA ON SECREEN (OR ) DUMP THE DATA INTO VARIABLES.

KEYWORDS:
1) FIRST: USED TO FETCH THE FIRST RECORD
2) NEXT: USED TO FETCH RECORDS NEXT TO THE PREVIOUSLY FETCHED RECORD
3) LAST: USED TO FETCH THE LAST RECORD FROM THE RESULT SET
4) PRIOR : USED TO FETCH RECORDS BEFORE THE PREVIOUSLY FETCHED RECORD
5) ABSOLUTE N : IF N IS POSITIVE, IT FETCHES NTH RECORD FROM THE BEGINNING, IF ‘N’ IS NEGATIVE IS FETCHES NTH RECORD FROM THE LAST IF N=0, NO RECORD WILL BE FETCHED.
6) RELATIVE N: IT FETCHES NTH RECORD FROM THE PREVIOUSLY FETCHED RECORD.
IF N IS POSITIVE IT FETCHES NTH RECORD FOR PREVIOUSLY FETCHED RECORD
IF N IS NEGATIVE IT FETCHES NTH RECORD BEFORE FOR PREVIOUSLY FETCHED RECORD
IF N=0 SAME RECORD WILL BE FETCHED.

FETCHING: THE PROCESS OF RETRIEVING THE SINGLE RECORD FROM THE RESULT SET IS CALLED FETCHING
SCROLLING: THE PROCESS OF PERFORMING THE SERIES OF FETCHES EITHER IN FORWARD OR BACKWARD DIRECTION IS CALLED SCROLLING

DEFAULT CURSOR IS THE FORWARD ONLY CURSOR
FETCH NEXT IS THE ONLY FETCH STATEMENT VALID WITH FORWARD ONLY CURSOR
EX: FORWARD ONLY CURSOR:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR FOR SELECT * FROM AUTHORS
Þ OPEN AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ ERROR BECAUSE FETCH NEXT IS THE ONLY FETCH OPTION VALID WITH FORWARD ONLY CURSOR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IN THE TABLE WILL BE DISPLAYED

4) CLOSING THE CURSOR: CLOSE
EX: CLOSE EMP_CUR
CLOSE AUTHOR_CUR
IT IS ILLEGAL TO FETCH THE DATA FROM THE CLOSED CURSOR, DATA CAN BE FETCHED FROM THE CURSOR AFTER IT WAS OPENED

5) DEALLOCATE THE CURSOR:
SYNTAX: DEALLOCATE
EX: DEALLOCATE AUTHORS_CURS
NOTE: IT IS NOT POSSIBLE TO OPEN THE DEALLOCATED CURSOR

CURSOR WITH SCROLL OPTION: IF CURSOR IS DECLARED WITH SCROLL OPTION ALL FETCH OPERATIONS ARE VALID ON THE CURSOR.
EX:
Þ USE PUBS
Þ DECLARE AUTHORS_CUR CURSOR SCROLL FOR SELECT * FROM AUTHORS
Þ OPEN AUTHOR_CUR
Þ FETCH NEXT FROM AUTHORS_CUR
Þ FIRST RECORD IS FETCHED
Þ FETCH NEXT FROM AUTHORS_CUR
Þ RECORD NEXT TO THE 1ST RECORD IS FETCHED
Þ FETCH LAST FROM AUTHORS_CUR
Þ LAST RECORD IS FETCHEDFETCH PRIOR FROM AUTHORDS_CUR
Þ RECORD BEFORE THE LAST RECORD WILL BE FETCHED
Þ FETCH ABSOLUTE 5 FROM AUTHORS_CUR
Þ FETCH ABSOLUTE 2 FROM AUTHORS_CUR
Þ FETCH RELATIVE 0 FROM AUTHORS_CUR
Þ FETCH RELATIVE 5 FROM AUTHORS_CUR
Þ FETCH FIRST FROM AUTHORS_CUR
Þ FETCH PRIOR FROM AUTHORS_CUR

STATIC CURSOR:
Þ USE PUBS
Þ DECLARE DEPT_CUR CURSOR STATIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK

Þ UPDATE DEPT SET LOC =’HYD’ WHERE DEPTNO =10
Þ FETCH FIRST FROM DEPT_CUR
10 ACCOUNTING NEWYORK

DML OPERATIONS PERFORMED ON THE ORIGINAL TABLE WILL NOT DISPLAYED BY CURSOR
WHEN CURSOR IS CLOSED AND REOPENED MODIFICATION WILL APPEARS
ALL FETCH OPTIONS ARE VALID WITH STATIC CURSOR

Þ SELECT * FROM DEPT
Þ MODIFICATIONS APPEARS
Þ CLOSE DEPT_CUR
Þ OPEN DEPT_CUR
Þ FETCH FIRST FROM DEPT_CUR


DYNAMIC CURSORS:
SYNTAX : DECLARE CURSOR DYNAMIC FOR SELECT STATEMENT.
IN CASE OF DYNAMIC CURSOR, THE OPERATIONS PEFORMED ON THE TABLE AFTER OPENING THE CURSOR WILL APPEARS THOUGH THE CURSOR.

Þ DECLARE DEPT_CUR CURSOR DYNAMIC FOR SELECT * FROM DEPT
Þ OPEN DEPT_CUR
Þ INSERT INTO DEPT VALUES (50, IMPORT’, ‘HYD’)
10 ACCOUNTING HYD
Þ FETCH FIRST FROM DEPT_CUR
Þ FETCH LAST FROM DEPT_CUR
10 IMPORT HYD
Þ FETCH PRIOR FROM DEPT_CUR

10 OPERATIONS BOSTON


*FETCH ABSOLUTE 2 FROM DEPT_CUR

KEY SET CURSOR:
Þ CREATE TABLE DEPT AND INSERT DATA INTO THE TABLE
Þ CREATE ON UNIQUE INDEX ON DEPTNO COLUMN
Þ DECLARE DEPT_KEYCUR CURSOR KEY SET FOR SELECT * FROM DEPT


KEY SET CURSOR:THE TABLE SHOULD HAVE AT LEAST ONE UNIQUE INDEX COLUMN TO CREATE THE KEY SET CURSOR
· WHEN CURSOR IS OPENED THE INDEX COLUMN OF THE TABLE IS TRANSFERRED TO THE NO-INDEX COLUMN CAN BE VISIBLE THOUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· THE MODIFICATION PEFORMED ON THE NON-INDEX COLUMNS CAN BE VISIBLE THROUGH CURSOR BUT THE MODIFICATION ON THE INDEX COLUMN WILL NOT APPEARS TO THE CURSOR
· IF THE TABLE WILL NOT HAVE ANY UNIQUE INDEX COLUMN IT AUTOMATICALLY CREATS THE STATIC CURSOR
· OPEN DEPT_KEYCUR
· UPDATE DEPT SET DNAME= ‘EXPORT’, LOC=’HYD’ WHERE DEPTNO = 10
· FETCH FIRST FROM DEPT_KEYCUR

10 EXPORT HYD

· UPDATE DEPT SET DEPTNO=50 WHERE DNAME = ‘OPERATIONS’ FETCH LAST FROM DEPT_KEYCUR
· NODATA IS DISPLAYED

SINCE THE MODIFICATION ON INDEX COLUMN WILL NOT BE VISIBLE THROUGH CUROSR.

TYPE WARNING: IF THIS KEYWORD IS USED WHILE DECLARING THE KEY SET CURSOR THE SERVER WILL DISPLAY MESSAGE IF IT FAILS TO CREATE KEYSET CURSOR
Þ DEPT TABLE WILL NOT HAVE ANY INDEX COLUMN
Þ DECLARE DEPT_KEYSET CURSOR KEYSET TYPE_ WARNING FOR SELECT * FROM DEPT
MESSAGE: CURSOR CREATED IS NOT OF REQUESTED TYPE I.E STATIC CURSOR IS CREATED.
NOTEL @@FETCH_STATUS: RETURNS THE STATUS OF LAST FETCH OPERATION THE POSSIBLE VALUES FOR @@ FETCH_STATUS ARE

1) 0à RECORD FETCHED SUCCESSFULLY
2) 1à FETCH OPERATIONS FAILS: YOU ARE BEYOND THE LAST RECORD OR BEFORE THE FIRST RECORD.

Ø FETCH FIRST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS

0

Ø FETCH PRIOR FROM DEPT_CUR
1


SINCE NO RECORD IS FETCHED
Ø FETCH LAST FROM DEPT_CUR
Ø PRINT @@ FETCH_STATUS
0


Ø FETCH NEXT FROM DEPT_CUR
Ø PRINT @@FETCH_STATUS
1


SINCE NO RECORD IS FETCHED
Ø CREATE TABLEL (EMPNOT INT, ENAME VARCHAR(20))
Ø INSERT DATA
Ø DECLARE EMP1_CUR CURSOR FOR SELECT * FROM EMP1
Ø DECLARE @ VNO INT, @VNAME VARCHAR(20) OPE EMPL_CUR
FETCH NEXT FROM EMPL_CUR INTO @ VNO, @VNAME WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @ VNO
PRINT @ VNAME
FETCH NEXT FROM EMPL_CUR INTO @VNO, @VANME
END.
IF (@@ FETCH _STATUS =1)
PRINT ‘ALL RECORDS ARE FETCHED’




PROCEDURES
PROCEDURES : PROCEDURES IS A PRE_COMPILED COLLECTION OF T_SQL STATEMENTS PROCESSED AS A UNIT PROCEDURE WILL HAVE THE HEADER, DECLARATIVE SECTION AND EXECUTABLE SECTIONS.

TYPES OF PROCEDURES: 1) SYSTEM STORED PROCEDURES
2) USER DEFINED STORED PROCEDURES.

USED DEFINED STORED PROCEDURES: THE PROCEDURES CREATED BY THE USER ARE CALLED USED DEFINED STORED PROCEDURES.


SYNTAX: CREATE PROCEDURE / PROC
@PARA 1 DATATYPE (SIZE)[=DEFAULT_VALUE][OUTPUT]
@PARA 2 DATATYPE (SIZE)[=DEFAULT_VALUE][VALUE]
AS SELECT STATEMENT

WRITE A SIMPLE PROCEDURE TO THE COMPLETE DATA FROM EMP TABLE
A) à CREATE PROCEDURE EMP_DATA AS SELECT * FROM EMP
à EXECUTING THE PROCEDURE:
SYNTAX: EXEC
à
EXEC EMP_DATA
ALL EMPLOYEES DETAILS ARE DISPLAYED IN THE EMP TABLE
EX:
CREATE PROC SAL_DATA AS SELECT EMPNO, SAL BASIC ,SAL * 0.35HRA, SAL*0.25 DA, SAL *0.15 PF, SAL+SAL*0.35+ SAL – 0.15 GROSS FROM EMP
à EXEC SAL_DATA
EMPNO BASIC HRA DA PF GROSS


WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM AS DECLARE @A INT, @B INT, @C INT
SET @A=5
SET @B=10
SET @C=@A+@B
PRINT THE SUM IS ‘+’ CONVERT (VARCHAR, @C)
à EXEC ADD_NUM
THE SUM IS =15
WRITE A PROCEDURE TO FIND SIMPLE INTEREST?
A) CREATE PROC SIMPLE_INTEREST AS DECLARE @P INT, @N DECIMAL (5,2), @R DECIMAL (5,2), @5I DECIMAL (5,2)
à SET @SI = (@P*@N*@R*)/100
PRINT “SIMPLE INTEREST:”+CONVERT (VARCHAR, @SI)

WRITE A PROCEDURE TO FIND THE SUM OF TWO STRINGS
A) à CREATE PROC ADD_STRINGS AS DECLARE @A VARCHAR(20), @B VARCHAR(20), @C VARCHAR(20),
SET @A=’ORACLE’
SET @B=’APPLICATIONS’
SET @C=@A+’ ‘ +@B
PRINT @C
à EXEC ADD_STRINGS
ORACLE APPLICATIONS

WRITE A PROCEDURE TO FIND THE SUM OF TWO NO’S
A) à CREATE PROC ADD_NUM @A INT, @B INT, AS DECLARE @C INT
SET @C=@A+@B
PRINT THE ‘SUM OF A AND B IS ‘ + CONVERT (VARCHAR, @C )
à EXEC ADD_NUM 3,5
OUTPUT:: THE SUM OF A AND B IS 8

DECLARE @M INT, @N INT,
SET @M=5
SET @N=10 EXECUTE ALL THE ONCE
EXEC ADD_NUM@M,@N


INSERTING DATA INTO A TABLE USING PROCEDURE:
à CREATE PROC INSERT DATA
@VNO INT, @ VNAME VARCHAR (20). @LOC VARCHAR (10) AS INSERT INTO DEPT VALUES[@VNO, @VNAME.@LOC].

DEPTNO DNAME LOC
10 ACC NEW
20 RESE HYD
à EXEC INSERT_DATA 10, ‘ACC’, ‘NEW’
à SELECT * FROM DEPT.



WRITE A PROCEDURE TO DISPLAY DETAILS OF ON EMPLOYEE TAKE EMPO THE
à CREATE PRO EMP DETAILS
@VNO INT
AS SELECT * FROM EMP WHERE EMPO = @ VNO
à EXECUTE EMP_DETAILS 7369

WRITE A PROCEDURE TO FIND THE NET SAL SAL OF AN EMP CREATE PROC NET_SAL
@VNO INT AS
DECLARE @ VSAL INT @VCON INT, @NET INT
SELECT @ VSAL = SAL, @ VCOM FROM EMP WHERE
EMPO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
PRINT ‘NET SAL IS’ CONVERT (VARCHAR, @ VSAL)
END

WRITE A PROCEDURE TO FIND THE NET SAL SAL USING OUT PARAMETERS
A) à CREATE PROC NET SAL
@VNO INT , @NET INT OUTPUT
AS
DECLARE @ VSAL =SAL, @VCOM =COMM, FROM EMP WHERE EMPNO = @ VNO
IF @VCOM IS NULL
BEGIN
PRINT ‘COMM IS NULL’
SET @ NET = @VSAL
END
ELSE
BEGIN
SET @NET= @VSNL + @COMM
END


EXECUTING PROC:
DECLARE @K INT
EXEC NET_SAL 7839, @ K OUTPUT
PRINT @K

ADMINISTRATOR LOGIN
CREATE PROC EMP_DEPT
SELECT ENAME, DNMAE FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
GRANT EXEC ON EMP_DEPT TO RAM

CONNECT TO RAM LOGIN
EXEC EMP_DEPTà DATA IS DISPLAYED SINCE ADMINSTATOR HAS GIVEN EXECUTE PERMISSION ON EMP_DEP

ALTERING THE PROCEDURE:
SYNTAX: ALTER PROC @ PARA 1. DATATYPE @PARA 2 DATA TYPE (SIZE) AS SQL STATEMENT

DISPLAYING THE CODE OF EXISTING PROCEDURE:
SYNTAX: SP_TELPTAXT
EX: SP_TELP_TEXT EMP_DEPT

USER DEFINED FUNCTIONS: FUNCTIONS CREATED BY USER ARE CALLED USER DEFINED FUNCTIONS
TYPES OF USER DEFINED FUNCTIONS:
1) SCALAR VALUED FUNCTIONS
2) TABLE VALUED FUNCTIONS
SCALAR VALUED FUNCTIONS: THESE FUNCTIONS WILL RETURN A SCALAR VALUE TO THE CALLING ENVIRONMENT
SYNTAX: CREATE FUNCTION < FUNCTION_NAME> (PARA 1 DATA TYPE (SIZE), PARA 2 DATATYPE (SIZE)
RETURNS
AS
BEGIN

RETURN < SCALAR VALUE>
END



WRITE A FUNCTION TO FIND THE PRODUCT OF TWO NUMBERS
A) CREATE FUNCTION PROD_NUM
(@ A INT, @B INT)
RETURNS INT
DECLARE @ C INT
SET @C = @A * @B
RETURN(@C)
END

EXECUTING THE FUNCTION
à DECLARE @K INT
SET @K = DBO.PROD_NUM(3,5)
PRINT @K

WRITE FUNCTION TO FIND THE NET SALARY OF AN EMPLOYEE READ EMPNO THOUGH PARAMETER AND DISPLAY THE NET TO RETURN VALUE
A) CREATE FUNCTION NET_SAL
(@ VNO INT)
RETURNS INT
AS
BEGIN
DECLARE @ VSAL INT, @VCOM INT, @NET INT
SELECT @VSAL \ SAL, @VCOM=COM
FROM EMP WHERE EMPNO =@VNO
IF @ VCOM IS NULL
BEGIN
PRINT ‘COMMISION IS NULL’
SET @NET = @VSAL
END
ELSE
BEGIN
SET @ NET = @VSAL + @VCOM
END
RETURN (@NET)
DECLARE @ K INT
SET @K = NET_SAL (7839)
PRINT @K


2) TABLE VALUED FUNCTION: THESE FUNCTIONS WILL RETURN ENTIRE TABLE TO THE CALLING ENVIRONMENT.
SYNTAX:
CREATE FUNCTION
(PARA 1 DATA TYPE (SIZE)……….)
RETURNS TABLE
AS
BEGIN

RETURN (SELECT STATEMENT)
END

WRITE A FUNCTION TO RETURN ENTIRE DEPT TABLE
A) à CREATE FUNCTION DEPT_TAB()
RETURNS TABLE
AS
BEGIN
RETURN (SELECT * FROM DEPT)
END
à EXECUTING THE FUNCTION
à SELECT * FROM DEPT_TABLE()
DEPT DNAME LOC



à CREATE FUNCTION EMP_DEPT()
RETURN TABLE
AS BEGIN
RETURN(SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP DEPTNO = DEPT DEPTNO)
END
à SELECT * FROM EMP_DEPT()
ENAME DNAME
SMITH RESEARCH
MILLER ACCOUNTING

CREATING DEFAULT:
SYNTAX:
CREATE DEFAULT AS
EX:
à CREATE DEFAULT FILL_COLUMN AS ‘UNKNOWN’

BINDING THE DEFAULT FOR COLUMNS:
SYNTAX: SP_BINDDEFAULT FILL_COLUMN, ‘DEPT.LOC’
SP_BINDEFAULT FILL_COLUMN, ‘EMP.JOB’
à INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(10,ACCOUNTING’)
à SELECT * FROM DEPT WHERE DEPTNO =10
DEPTNO DNAME LOC
10 ACCOUNTING UNKNOWN
à INSERT INTO EMP (EMPNO, ENAME, SAL)
VALUES (100, ‘PHILLIS’, 5000)
à SELECT EMPNO, ENAME, JOB FROM EMP WHERE EMPNO =100
EMPNO ENAME JOB
100 PHILLIS 5000
UNBINDING THE DEFAULT FROM THE BINDING COLUMN
SYNTAX: SP_UNBINDEFAULT
SP_UNBINDEFAULT ‘ DEPT.LOC’
SP_UNBINDEFAULT ‘ EMP.JOB’

DROPPING THE DEFAULT:
SYNTAX: DROP DEFAULT
DROP DEFAULT FILL_COLUMN

CREATING RULES:
RANGE RULE:
CREATE RULE RANGE_RULE AS @ RANGE >=10 AND @ RANGE <= 60
BINDING THE RULE:
SP_BINDEFAULT ,‘ TABLE.COLUMN’
SP_BINDEFAULT RANGE_RULE, ‘DEPT.DEPTNO’
INSERT INTO DEPT(DEPTNO) VALUES (70)

ERROR

LIST RULE:
à CREATE RULE LIST_RULE AS @ LIST IN (‘ACCOUNTING’, ‘SALES’, ‘OPERATIONS’, RESEARCH’, ‘EDU’)
à SP_BINDRULE LIST_RULE, ‘DEPT_DNAME’
à INSERT INTO DEPT(DEPTNO, DNAME) VALUES (20, IMPORT’)
ERROR SINCE DANME VALUES IS OUT OF LIST

UNBINDING THE RULES:
à SP_UNBIND RULE DEPT.DEPTNO
à SP_UNBIND RULE DEPT.DNAME

DROPPING THE RULES:
à DROP RULE
à DROP RULE ROLE_NAME
à DROP RULE LIST_NAME

TRIGGERS
TRIGGERS: TRIGGER IS A TYPE OF STORED PROCEDURE THAT IMPLICITLY THAT IMPLICITLY EXECUTED WHEN USER PEFORMS DML OPERATION ON THE TABLE.
IT WILL NOT ACCEPT ANY PARAMETERS

TYPES OF TRIGGERS:
!) INSERT TRIGGER
2) DELETE TRIGGER
3) UPDATE TRIGGER
SYNTAX: CREATE TRIGGER ON FOR/ AFTER, INSERT,/ UPDATE/ DELETE AS SQL STATEMENT.
I
INSERT TRIGGER: THIS TRIGGER FIRES WHEN USER PERFORMS INSERT OPERATION ON THE TABLE. WHEN USER INSERT A RECORD INTO THE TABLE THE TEMPORARY TABLE CALLED INSERTED IS CREATED
THE NEWLY INSERTED RECORD IS ALSO STORED IN INSERTED TABLE TEMPORARILY
à CREATE TRIGGER
FOR INSERT
AS
BEGIN
PRINT ‘TRIGGER T1 FIRE’
END

à CREATE TRIGGER2 ON DEPT
FOR
AS
PRINT ‘TRIGGER T2 FIRE’
END

à CREATE TRIGGER3 ON DEPT
FOR INSERT
AS
PRINT ‘TRIGGER T3 FIRE’
END
à INSERT INTO DEPT(DEPTNO) VALUES(50)
TRIGGER T1 FIRED
TRIGGER T2 FIRED
TRIGGER T3 FIRED

CHANGING THE FIRING ORDER OF TRIGGERS:
SYNTAX: SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘NAME_OF_TRIGGER’,
@ ORDER = ‘FIRST/LAST’
@ STMTYPE = ‘INSERT’/UPDATE/DELETE’
à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T3’
@ ORDER = ‘FIRST’
@ STMTYPE = ‘INSERT’

à SP_SETTRIGGER ORDER @ TRIGGER NAME = ‘T1’
@ ORDER = ‘LAST’
@ STMTYPE = ‘INSERT’
à INSERT INTO DEPT (DEPTNO) VALUES (60)
TRIGGER T3 FIRED
ON DEPT
FOR UPDATE
AS
BEGIN
INSERT INTO DEPT_INSERT SELECT * FROM INSERT
INSERT INTO DEPT_DELETE SELECT * FROM DELETED
END
à UPDATE DEPT
SET DNAME = ‘EXPORT’
LOC = ‘SEC’ WHERE DEPTNO =10

à SELECT * FROM DEPT_INSERT
DEPTNO DNAME LOC
10 EXPORT SEC

à SELECT * FROM DEPT_DELETE
DEPTNO DNAME LOC
10 ACCOUNTING NEWYORK

INSTEAD OF TRIGGERS: THESE TRIGGER ARE MAINLY CREATED FOR VIEWS
SYNTAX: CREATE TRIGGER TRIGGER_NAME ON < VIEW_NAME>
INSTEAD OF INSERT/ UPDATE ./ DELETE
AS
SQL STATEMENT
à CREATE VIEW V10
AS
SELECT * FROM EMP WHERE DEPTNO =10
Þ SELECT * FROM V10
Þ IT WILL DISPLAY AN 10TH DEPT EMPLOYEES DETAILS
Þ CREATE TRIGGER V10_TRG
INSEAT OF INSERT
AS
UPDATE V10 SET SAL=SAL +500
Þ INSERT INTO V10 (EMPNO, ENAME, DEPTNO) VALUES (100, ‘CHAD’, 10)
Þ SELECT * FROM V10
Þ INSTEAD RECORDS WILL NOT APPEARS BUT SALARIES ARE MODIFIED DUE INSTEAD OF TRIGGER
Þ CREATE VIEW DEPT_VIEW
AS
SELECT * FROM DEPT
Þ CREATE TRIGGER DEPT_TR
AS
ON DEPT_VIEW
FOR INSERT
AS
DELETE FROM DEPT_VIEW
Þ INSERT INTO DEPT_VIEW DATA DELETED DUE TO INSTEAD OF TRIGGER.
Þ CREATE VIEW EMP_DEPT
AS
SELECT EMPNO, ENAME, DEPT,.DEPTNO, DNAME FROM EMP,
DEPT WHERE EM.DEPTNO =DEPT.DEPTNO

EMPNO ENAME DEPTNO DNAME


à SELECT * FROM EMP_DEPT




ERROR
à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).

SINCE NOT POSSIBLE TO INSERT DATA INTO TWO TABLES THOUGH SINGLE VIEW.

à CREATE TRIGGER EMP DEPT_TRG
ON EMP_DEPT
FOR INSERT
AS
BEGIN
INSERT INTO EMP (EMPNO,ENAME) SELECT EMPNO, ENAME FROM INSERTED.
INSERT INTO DEPT(DEPTNO, DNAME) SELECT DEPTNO, DNAME FROM INSERTED
END.

à INSERT INTO EMP_DEPT (EMPNO, ENAME, DEPTNO, DNAME) VALUES (100, ‘CHAD’, 50, ‘IMPORT’).
à SELECT * FROM EMP
à NEW RECORD WILL IS DISPLAYED ALONG WITH OLD RECORD
à SELECT * FROM DEPT
à NEW RECORD WILL BE DISPLAYED ALONG WITH OLD RECORDS



DISPLAYING THE CODE OF TRIGGER:

SYNTAX: SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘EMP_DEPT’

DISPLAYING THE TRIGGERS INFORMATION FOR THE TABLE.
SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘DEPT’

DCL COMMANDS: THESE COMMANDS ARE USED GRANT OR REVOKE THE PERMISSION ON DATABASE OBJECTS TO OTHER USERS.

1) GRANT: USED TO GRANT THE PERMISSIONS ON DATA OBJECTS TO USERS ACCOUNTS
ADMINISTRATOR TOOLS
GRANT SELECT ON DEPT TO RAM
GRANT INSERT, SELECT ON EMP TO RAM123

RAM/ RAM 123 LOGIN
SELECT * FROM DEPT
à DEPT DATA IS DISPLAYED
INSERT INTO DEPT VALUES (50, ‘****’, ‘DDDD’)
à ERROR SINCE NO INSERT PERMISSION TO RAM
SELECT * FROM EMP
INSERT INTO EMP (EMPNO, ENAME) VALUES (400, ‘CHRIS’)
à 1 RECORD IN INSERTED
UPDATE EMP SET EMPNO =111 WHERE ENAME = ‘SMITH’
à ERROR SINCE NO UPDATE PERMISSION TO RAM

ADMINISTRATOR LOGIN:
REVOKE: USED TO TAKE BACK THE PERMISSIONS GIVEN TO THE USERS
REVOKE SELECT ON DEPT FROM RAM
REVOKE SELECT, INSERT AN EMP FROM RAM

ADMINISTRATOR LOGIN
GRANTING PERMISSION WITH GRANT OPTION:
GRANT SELECT ON DEPT TO RAM WITH GRANT OPTION

RAM LOGIN:
SELECT * FROM DEPT
à DATA WILL BE DISPLAYED
GRANT SELECT ON DEPT TO NTTS

NTTS LOGIN
SELECT * FROM DEPT


ADMINISTRATOR LOGIN
REVOKE SELECT ON DEPT FROM RAM CASCADE
à NOW BOTH RAM AND NTTS WILL LOOSE THE PERMISSION ON DEPT
à SP_WHO à IT WILL DISPLAY THE STATUS OF LOGINS.

Subscribe to: Posts (Atom)

Total Pageviews

Pages

• Home

Abinitio Archive

• ►  2012 (4)
  • ►  September (3)
  • ►  August (1)

• ►  2011 (8)
  • ►  November (2)
  • ►  July (1)
  • ►  June (4)
  • ►  May (1)

• ►  2010 (6)
  • ►  September (1)
  • ►  August (5)

• ►  2009 (31)
  • ►  November (2)
  • ►  August (17)
  • ►  July (7)
  • ►  May (1)
  • ►  April (1)
  • ►  February (1)
  • ►  January (2)

• ▼  2008 (55)
  • ►  December (3)
  • ►  November (13)
  • ▼  October (13)
    • Dwh && Bi Course Details
    • Cognos 8BI information Sites
    • Cognos Installation steps
    • SQL QUERIES
    • SQL Material
    • SQL Material
    • MYSQl Interview QUESTIONS
    • DWH INFORMATION
    • DWH INFORMATION
    • DWH RELATED LINKS && UNIX
    • FAQ FROM ALL && COOL INTERVIEWS.com
    • Ab Initio - Data Parallel and Chasing the Partitio...
    • QTP Studing MAterial
  • ►  September (25)
  • ►  June (1)

Followers

About Me

Abi

Almost in all the Interviews... this will be the first question dropped from HR's. " Tell me about urself ?" or "Tell me about ur profile?" But normally everyone struggles here.... wondering from where we need to start ? From schooling ? or After my Degree ?, Should I tell about my family here (becoz sometimes HR's will have another question "Tell me abt ur Family?"), I am here one of those confused persons..... But I could also assume that here the HR may see our way of speech, the way we deliver, the body language etc. BEST ANSWER: Start with the present and tell why you are well qualified for the position. Remember that the key to all successful interviewing is to match your qualifications to what the interviewer is looking for. In other words you must sell what the buyer is buying.

View my complete profile

TECHNOLOGIES

• Aptitude Test, Employment Screening, Assessment Testing, Online Personality Test - Brainbench

  -
• bulk.resource.org

  -
• Cognos BI

  -
• DOWNLOAD FREE ENGINEERING LECTURES NOTES, PPT, PDF, SLIDES, EBOOKS, SEMINARS

  Data Structures Algorithms and Applications - Data Structures, Algorithms, and Applications in Java by Sartaj Sahni Download Slides: Powerpoint presentations used in the lectures are available fro...

  12 years ago
• Free online tutorials at 1Keydata

  -
• How I Got a 770 in GMAT

  Links for 2011-10-16 [Digg] - - Animals Shrink as Earth Warms All kinds of organisms may get smaller because of climate change, write scientists, who worry this could spell...

  13 years ago
• ITtoolbox - Professional IT Community

  -
• Justcog Forums - Powered by vBulletin

  -

Chandrasekhar. Watermark theme. Powered by Blogger.