SQL SERVER 2008-DB
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROLLBack:
--------------
begin transaction
declare @var varchar(100)
update student set sal=900 where sid=400
rollback transaction
error: no correspoding begin transcation found
simple rolback:
--------------
begin transaction
drop table student
rollback transaction
ALTER COLUMN NAME
-----------------
ALTER TABLE student DROP COLUMN sal
IF it depends upon the any constraint then we need drop the constraint from table then only it will allow to delete student column
ALTER TABLE student DROP CONSTRAINT DF__student__sal__09DE7BCC
adding primary key constraint for existing table
---------------------------------------------------
alter table student1 add constraint pk_student1 primary key(stno)
before assinging pk we need TO ASSIGN not null constraint: alter table student1 alter column stno int not null;
select syntax:
---------------
SELECT SUM(salary) FROM student
where sid>200
GROUP BY salary
HAVING salary>=200
order:
where condition
groupfunction(column) according to group by column
having condition on groupfunction(column)
->order: after where condition
fisrt groups all salaries then sum(salary) of grouped salaru
then having condition will evolutes
To add constraints:
--------------------
->alter table datatype means we can alter/modify only datatype or size or columnName but not constraint
for constraint modification:
we need to drop existing constraint
also need to modify column to not null before assigning primary key constraint like: ALTER TABLE CUSTOMERS alter column id int not null
to drop any constraint: alter table customers drop constraint pkcust_id
to add constraint: alter table customers add constraint pkcust_id primary key (id)
TO ADD unique constraint: ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
TO ADD default constraint(for keyword): ALTER TABLE CUSTOMERS ADD CONSTRAINT pkcust_id DEFAULT 500.00 for salary
JOINS:
-----------------------------------------------------
-----------------------------------------------------
join/inner join:
----------------
->only matched cust.id=ord.id id's are displyed
->it wont dispaly NULL valuesfor unmatched id's
left outer join/ left join:
-----------------
->left(total) and right(if unmatched date then displays NULL values)
->No of Rows= No of Rows in Left Table
right outer join/ right join:
-------------------
->left(if unmatched date then displays NULL values) and right(total)
->No of Rows= No of Rows in right Table
full join/ full outer join:
----------------------------
->total no of matched/unmatched Rows if unmatched data is there then displays NULL vlaues either at Left/right end
->ex: left table(10 rows) and right table(6 rows)
out of these total 16 rows 3 rows are matched accroding to id then
total(16)-matched(3)= 13 rows will be displayed with NULL values for unmatched ids
Self join:
-------------
select * from customers cust join customers cu ON cust.id=cu.id
-> joining with the same table itself is nothing but self join
cross join/cartesian join:
--------------------------
-> Dont have ON condition
-> product of fist table * second table
->SYTX: SELECT * from customers cross join orders
-> dont have ON condition
UNION ALL:(duplicates also)
-----------------
-> first select statement column names= second select statement column names (must be same and equal no of columns in both select statements)
so, under the first select statement column names the results are as follows
fist select statement result in the first row then
second select statement result in the second row ...etc.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Union(no duplicates)
------------------------
union: it wont diplay duplicates where union all will dispaly duplicates also
sames as union all functionally except duplicates
******************************************************************
1. How I find a particular column name within all tables of SQL server datbase.
----------------------------------------------------------
select table_name,column_name from information_schema.columns
where column_name like '%profile_name%'
2. How to search stored procedures containing a particular text?
----------------------------------------------------------
select routine_name, routine_definition
from information_schema.routines
where routine_definition like '%tax_id%'
and routine_type='procedure'
****************************************************************************
DATABASE COMMANDS QUICK REVIEW:
-----------------------------------------------------------------------------
Database:
---------
-Normalization: Organizing the data in databse
-Atomic(individual column data), functional dependency, duplicates, null(partial dependency)
-Common column between 2-tables helpful for the from the Relation mostly Id's
-Joins: to extract the data from morethan one table we will use joins concept
DDL:
Create
Alter
Rename
drop
truncate
DML:
insert
Delete
update
TL:
grant
revoke
operators:
Exists ex: where exists (select query)
Not
In
Like
And/or
between cond1 And Cond2
Is Null : to check null
Unique
Datatypes:
Int
numeric
Money/currency
DateTime
date
time
varchar
varchar2
nvarchar
Pettern Matching to search:
Percentile % : zero more matches
Underscore _ :single character match
select command limits:
TOP / ROWNUM(in oracle) ex: where RowNum<=10
LIMIT
Colunmn AS alias_column_name
As alias_table_name
UNION: should have same no of columns/datatypes of both the select queires
UINION ALL: allows duplicates also
-The ANY and ALL operators are used with a WHERE or HAVING clause
ex: WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
ex: WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
ISNULL(): Retruns specified value when it is null ISNULL( Number, 00);
Constraints:
---------------
-Check constraint: limits the values by condition
-Default constraint: to specify the default value for the column ex: for date default value would be GETDATE()
-AutoIncrement : used to increment value automatically useful for id's
-Index: to get the data fastly from DB
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROLLBack:
--------------
begin transaction
declare @var varchar(100)
update student set sal=900 where sid=400
rollback transaction
error: no correspoding begin transcation found
simple rolback:
--------------
begin transaction
drop table student
rollback transaction
ALTER COLUMN NAME
-----------------
ALTER TABLE student DROP COLUMN sal
IF it depends upon the any constraint then we need drop the constraint from table then only it will allow to delete student column
ALTER TABLE student DROP CONSTRAINT DF__student__sal__09DE7BCC
adding primary key constraint for existing table
---------------------------------------------------
alter table student1 add constraint pk_student1 primary key(stno)
before assinging pk we need TO ASSIGN not null constraint: alter table student1 alter column stno int not null;
select syntax:
---------------
SELECT SUM(salary) FROM student
where sid>200
GROUP BY salary
HAVING salary>=200
order:
where condition
groupfunction(column) according to group by column
having condition on groupfunction(column)
->order: after where condition
fisrt groups all salaries then sum(salary) of grouped salaru
then having condition will evolutes
To add constraints:
--------------------
->alter table datatype means we can alter/modify only datatype or size or columnName but not constraint
for constraint modification:
we need to drop existing constraint
also need to modify column to not null before assigning primary key constraint like: ALTER TABLE CUSTOMERS alter column id int not null
to drop any constraint: alter table customers drop constraint pkcust_id
to add constraint: alter table customers add constraint pkcust_id primary key (id)
TO ADD unique constraint: ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
TO ADD default constraint(for keyword): ALTER TABLE CUSTOMERS ADD CONSTRAINT pkcust_id DEFAULT 500.00 for salary
JOINS:
-----------------------------------------------------
-----------------------------------------------------
join/inner join:
----------------
->only matched cust.id=ord.id id's are displyed
->it wont dispaly NULL valuesfor unmatched id's
left outer join/ left join:
-----------------
->left(total) and right(if unmatched date then displays NULL values)
->No of Rows= No of Rows in Left Table
right outer join/ right join:
-------------------
->left(if unmatched date then displays NULL values) and right(total)
->No of Rows= No of Rows in right Table
full join/ full outer join:
----------------------------
->total no of matched/unmatched Rows if unmatched data is there then displays NULL vlaues either at Left/right end
->ex: left table(10 rows) and right table(6 rows)
out of these total 16 rows 3 rows are matched accroding to id then
total(16)-matched(3)= 13 rows will be displayed with NULL values for unmatched ids
Self join:
-------------
select * from customers cust join customers cu ON cust.id=cu.id
-> joining with the same table itself is nothing but self join
cross join/cartesian join:
--------------------------
-> Dont have ON condition
-> product of fist table * second table
->SYTX: SELECT * from customers cross join orders
-> dont have ON condition
UNION ALL:(duplicates also)
-----------------
-> first select statement column names= second select statement column names (must be same and equal no of columns in both select statements)
so, under the first select statement column names the results are as follows
fist select statement result in the first row then
second select statement result in the second row ...etc.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Union(no duplicates)
------------------------
union: it wont diplay duplicates where union all will dispaly duplicates also
sames as union all functionally except duplicates
******************************************************************
1. How I find a particular column name within all tables of SQL server datbase.
----------------------------------------------------------
select table_name,column_name from information_schema.columns
where column_name like '%profile_name%'
2. How to search stored procedures containing a particular text?
----------------------------------------------------------
select routine_name, routine_definition
from information_schema.routines
where routine_definition like '%tax_id%'
and routine_type='procedure'
****************************************************************************
DATABASE COMMANDS QUICK REVIEW:
-----------------------------------------------------------------------------
Database:
---------
-Normalization: Organizing the data in databse
-Atomic(individual column data), functional dependency, duplicates, null(partial dependency)
-Common column between 2-tables helpful for the from the Relation mostly Id's
-Joins: to extract the data from morethan one table we will use joins concept
DDL:
Create
Alter
Rename
drop
truncate
DML:
insert
Delete
update
TL:
grant
revoke
operators:
Exists ex: where exists (select query)
Not
In
Like
And/or
between cond1 And Cond2
Is Null : to check null
Unique
Datatypes:
Int
numeric
Money/currency
DateTime
date
time
varchar
varchar2
nvarchar
Pettern Matching to search:
Percentile % : zero more matches
Underscore _ :single character match
select command limits:
TOP / ROWNUM(in oracle) ex: where RowNum<=10
LIMIT
Colunmn AS alias_column_name
As alias_table_name
UNION: should have same no of columns/datatypes of both the select queires
UINION ALL: allows duplicates also
-The ANY and ALL operators are used with a WHERE or HAVING clause
ex: WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
ex: WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);
ISNULL(): Retruns specified value when it is null ISNULL( Number, 00);
Constraints:
---------------
-Check constraint: limits the values by condition
-Default constraint: to specify the default value for the column ex: for date default value would be GETDATE()
-AutoIncrement : used to increment value automatically useful for id's
-Index: to get the data fastly from DB
No comments:
Post a Comment