MS SQL Server
Primary Database create two ways
[ graphically and using query ]
.MDF Master data file
.LDF transaction log data file
Alter - Means edit table by query
Constraint-
The default value will be added to all new records, if no other value is specified, including NULL.
Altering an existing column to add a default constraint:
[ ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME } ]
[ alter table tblperson
add constraint DF_tblperson_genderID
Default 3 for genderID ]
DELETE- Q- Delete from tblgender where ID =2
SQL constraints are used to specify rules for the data in a table.
*CHECK CONSTRAINTS - CHECK constraints are used to limit the range of the values, that can be entered for a column
*The general formula for adding check constraint in SQL :
[ALTER TABLE {TABLE_NAME}
ADD CONSTRAINT {CONSTRAIN_NAME } CHECK (BOOLEAN_EXPRESSION)]
TO drop the check constraint :
[ALTER TABLE TBLPERSON
DROP CONSTRAINT CK_tblperson_age]
* IDENTITY COLUMN
IF a column is marked as an identity column, then the values for this column are automatically generated,
when you insert a new row into the table.
Create table tbl_person
(
personid int identity(1,1)primary ley,
name mvarchar(20)
)
1.frist turn on identity insert -SET identity_insert tblperson on
2. in the insert query specify the colunm list
insert into tblperson (personid,name) values(2,'umesh')
*IF YOU HAVE DELETED ALL THE ROWS IN A TABLE, and you want to reset the identity column values, use the DBCCCHECKIDENT command.
DBCCCHECKIDENT('tbl_person')RESEED,0)NIQUE
* UNIQUE KEY:-
We use the UNIQUE constraint to enforce the uniqueness of a column
i.e.
the column should not allow any duplicate values. we can add a Unique constraint through the designer or using a query
To create the unique key using a query:
Alter table table_name
Add constraint_name unique(Column_name)
Both primary key and unique key are used to enforce, the uniqueness of a column. so when do you chose one over the other?
A table can have, only one primary key. if you want to enforce uniqueness on 2 or more columns, then we use unique key constraints.
*what is the difference between primary and unique key constraints?
1.A table can have only one primary key, but more than one unique key
2.Primary key does not allow nulls, whereas unique key allows one null.
right-click o table - right-click on column name- and select indexes/keys option.
* SELECT statement:-
The SELECT Statment is used to select or retrieve data from database tables.
1.SELECT Statement can be made of two parts: SELECT CLAUSE and FROM CLAUSE (After FROM clause, we specify table name)
2.SELECT DISTINCT Statement is used to return unique Rows in results. SELECT DISTINCT Removes duplicates ROWS in a result set.
query=
SELECT DISTINCT (column name),(column name)
FROM (table)Tblperson
*WHERE CLAUSE:-
The WHERE clause is used to filter ROWS of the table.
It is used to extract only those Rows that fulfilled a specified condition.
<> , != not equal express
filter data
*ORDER BY KEYWORD
The order by keyword is used to sort the result in ascending or descending order.
-The Order by keyword sorts the result in ascending order by default Asc keyword may be used to sort the
result in ascending order.
-To sort the result in descending order, the DESC keyword is used.
*LIKE Operator
Like special operator is used in a WHERE clause to search for a specified pattern in a column.
Like Operator: per cent sign%, underscore _
GROUP BY CLAUSE:-
Group by clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions. it is always used in conjunction with one or more aggregate functions.
q. [ select city sum(salary) as total salary
from tbl_employe group by the city]
* JOINS
Join in SQL servers are used to retrieve data from 2 or more related tables.
in general, tables related to each other using foreign key constraints
Types:-inner, outer, cross
1] INNER JOIN:-returns only the matching rows. Non-matching rows are eliminated.
2] left join:- returns all the matching rows + non-matching rows from the left table
3] right join:-returns all the matching rows + non-matching rows from the right table
4] CROSS JOINS
PRODUCES THE CARTESIAN PRODUCT OF THE 2 TABLES INVOLVED IN THE JOIN.
[
Select ...ColumnList
From ...LeftTable
jointype... RightTable
on ...JoinCondition
]
* UNION OPERATORS:-
UNION- REMOVE DUPLICATED ROWS, WHEREAS UNION ALL DOES NOT
UNION HAS TO PERFORM DISTINCT SORT TO REMOVE DUPLICATE,
WHICH MAKES IT LESS FASTER THAN UNION ALL
{ ORDER BY CLAUSE USE SORTING TABLE }
* STORED PROCEDURES
right, the query many times and save it as a stored procedure and call by its name.
A Stored Procedure is a set of ( SQL) statements with an assigned name,
which are stored in a relational database management system as a group, so they can be reused
and shared by multiple programs.
Type of stored Procedure.
--System Stored Procedure.
--User-Defined Stored Procedure.
[ #spGetEmploye for exicute ]
*Part 22:-
* STRING FUNCTION
ASCII (CHAR Expression) -return give char express
CHAR (INT Exprsn) - convert int code to char
RTRIM, LTRIM(CHAR Exprsn) - REMOVE Blanks on RIGHT, LEFT Side
Lower and Upper (char expert) - Convert lover to upper and upper to lower char expression
LEN (String expert):- return the total char
built-in string Functions
LEFT
RIGHT
CHARINDEX()
SUBSTRING()
*AND, OR, NOT Logical operators
AND, OR AND NOT Operators are used after the WHERE clause.
-The AND & OR operators are used to filter rows based on more than one condition.
-The AND operator displays a row if all the conditions separated by AND are TRUE.
-The OR operator displays a row if any of the conditions separated by OR is TRUE.
-The NOT operator displays a row if the condition is NOT TRUE.
*BETWEEN SPECIAL OPERATOR
The BETWEEN operator selects ROWS/Values within a given range.
The values can be numbers, texts, or dates.
The BETWEEN operator is inclusive begin and end values are included
IN Operator
-IN will select the rows if any of the values in the condition matched.
-IN operator is a shorthand for multiple OR conditions.
* PART:- 24
*REPLICATE FUNCTION ( REPEATS THE GIVEN STRING)
*SPACE FUNCTION ( RETURN THE NUM OF SPACE)
select first name + SPACE(5) + Lastname as full name
from tbl_Employe
*REPLACE FUNCTION (REPLACE THE STRING VALUE) .COM - .NET
select Email, replace (Email,'.com','.net') as ConvertedEmail
from tblEmploye
*STUFF FUNCTION (REPLACEMENT EXPRN ) ABC@AAA -TO- A***AAA
Select firstname, lastname, Email ,
stuff (Email,2,3,'****') as stuffedemail
from tblEmploye
* DATE/TIME FUNCTION
UTC -COORDINATES UNIVERSAL TIME
DATE , TIME ,SMALLDATETIME ,DATEITIME, DATETIME2, DATETIMEOFFSET
* ISDATE - Checks if the value is a valid date-time or DateTime
DATE, MONTH, YEAR - RETURN THE 'DAY/MONTH/YEAR NUMBER OF THE MONTH' OF THE GIVEN DATE
DATETIME, TIME NAME FUNCTION
*
DatePart:-date name() returns the nvarchar and integer
DateAdd:-increasing or decreasing the days to a given date
DateDiff:- diff between the two dates
* CAST & CONVERT FUNCTION - ONE DATATYPE TO ANOTHER
ONLY CHANGING DATE FORMAT
IN CONVERT WE USE STYLE FORMAT
CAST (Expression as data_type[(length)])
CONVERT (Data_type [(length)],expression[style])
PART:-29 - MATHEMATICAL FUNCTION
1.ABS (NUM FUN)-ABS stands for absolute and returns, the abs num
2.CEILING AND FLOOR
EX.
SELECT CEILING (15.5)
SELECT CEILING (-15.5)
SELECT FLOOR (15.5)
SELECT FLOOR (-15.5)
3.POWER (EXPRN, POWER)
SELECT POWER (2,3)
4.SQUARE (NUM)
SELECT SQUARE (9)--81
5.SQRT (NUM)
SELECT SQRT (81)--9
6. RAND - RETURNS a random float number betn 0 and 1 .
SELECT RAND(1)
SELECT FLOOR (RAND()*100)
* If you found this article useful, share it.*
ASP.NET MVC Question Answer Link
No comments:
Post a Comment