apigee questions

App need to stop only specific endpoint

Flow diagram

Advertisements
Standard

kendo ui tasks

  1. tool tip —with customize result
  2. kendo grid —with all crud operation
  3. routing
  4. services and module and factory
  5. hitting the crud operation calls
Standard

All Sql

  • Local temporary tables  are visible when there is a connection, and are deleted when the connection is closed.

CREATE TABLE #<tablename>

  • Global temporary tables  are visible to all users, and are deleted when the connection that created it is closed.
CREATE TABLE ##<tablename>

constrints in sql

  • Not null
  • Unique
  • Check
  • Primary
  • Foreign

Distinct

Count

Between

Like

In

And

Or

Not

Order by

SELECT * FROM Customers
WHERE Country=’Germany’ AND ROWNUM <= 3;

SELECT * FROM Customers
WHERE Country=’Germany’
LIMIT 3;

Aggregate functions:

Min,max,avg,count,sum

 

 

 

 

 

 

LIKE Operator Description
WHERE CustomerName LIKE ‘a%’ Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’ Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’ Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’ Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’ Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’ Finds any values that starts with “a” and ends with “o”

 

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘a%’;

SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

SELECT * FROM Customers
WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

Joins:

Inner join

Left outer join

Right outer join

Full outer join

Cross join

Self join

create table employe(empid int primary key,ename varchar(50),deptno int)

create table dept(deptno int,dname varchar(50))

 

insert into employe values(101,’sai’,10),(102,’chathu’,20),(103,’dady’,30),(104,’candy’,40),(105,’aadhya’,50)

insert into dept values(10,’hyd’),(20,’mdrs’),(30,’kdp’),(60,’pdtr’)

select e.*,d.* from employe e inner join dept d on e.deptno=d.deptno

select e.*,d.* from employe e left outer join dept d on e.deptno=d.deptno

select e.*,d.* from employe e right outer join dept d on e.deptno=d.deptno

select e.*,d.* from employe e cross join dept d

select e.*,d.* from employe e full join dept d on e.deptno=d.deptno

 

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

  • HAVING filters records that work on summarized GROUP BY results.
  • HAVING applies to summarized group records, whereas WHERE applies to individual records.
  • Only the groups that meet the HAVING criteria will be returned.
  • HAVING requires that a GROUP BY clause is present.
  • WHERE and HAVING can be in the same query.

Drop,truncate and delete

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries

CREATE INDEX idx_lastname
ON Persons (LastName);

DROP INDEX index_name ON table_name;

Views:

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:+

  • To focus, simplify, and customize the perception each user has of the database.
  • As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
  • To provide a backward compatible interface to emulate a table whose schema has changed.
  • ENCRYPTION
    Applies to: SQL Server 2008 through SQL Server 2017 and Azure SQL Database.+
  • Encrypts the entries in syscommentsthat contain the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
  • SCHEMABINDING
    Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statementmust include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
  • Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

Transactions:

Transactions have the following four standard properties, usually referred to by the acronym ACID.

  • Atomicity− ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
  • Consistency− ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation− enables transactions to operate independently of and transparent to each other.
  • Durability− ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

The following commands are used to control transactions.

  • COMMIT− to save the changes.
  • ROLLBACK− to roll back the changes.
  • SAVEPOINT− creates points within the groups of transactions in which to ROLLBACK.
  • SET TRANSACTION− Places a name on a transaction.

 

createtable Person

(

PersonIDnvarchar(5)primary keynot null,

FirstNamenvarchar(10)not null,

Companynvarchar(15)

)

 

createtable PersonDetails

(

PersonIDnvarchar(5)FOREIGN KEYREFERENCES dbo.Person(PersonID),

Addressnvarchar(30)

)

createprocedure sp_Trans_Test

@newpersonidnvarchar(5),

@newfirstnamenvarchar(10)

@newcompanynamenvarchar(15),

@oldpersonidnvarchar(5)

as

declare @inserrint

declare @delerrint

declare @maxerrint

 

set @maxerr = 0

 

BEGINTRANSACTION

— Add a person

insertinto person (personid, firstname, company)

values(@newpersonid, @newfirstname, @newcompanyname)

 

— Save error number returned from Insert statement

set @inserr = @@error

if @inserr > @maxerr

set @maxerr = @inserr

 

— Delete a person

deletefrom person

where personid = @oldpersonid

 

— Save error number returned from Delete statement

set @delerr = @@error

if @delerr > @maxerr

set @maxerr = @delerr

— If an error occurred, roll back

if @maxerr <> 0

begin

ROLLBACK

print’Transaction rolled back’

end

else

begin

COMMIT

print’Transaction committed’

end

print’INSERT error number:’+ cast(@inserras nvarchar(8))

print’DELETE error number:’+ cast(@delerras nvarchar(8))

return @maxerr

Triggers

Cursors:

A cursor is a temporary work area created in system memory when an SQL statement is executed. A cursor is a set of rows together with a pointer that identifies a current row. It is a database object to retrieve data from a result set one row at a time. It is useful when we want to manipulate the record of a table in a singleton method, in other words one row at a time. In other words, a cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Types of Cursors

There are the following two types of Cursors:

  1. Implicit Cursor
  2. Explicit Cursor

Implicit Cursor

These types of cursors are generated and used by the system during the manipulation of a DML query (INSERT, UPDATE and DELETE). An implicit cursor is also generated by the system when a single row is selected by a SELECT command.

Explicit Cursor

This type of cursor is generated by the user using a SELECT command. An explicit cursor contains more than one row, but only one row can be processed at a time. An explicit cursor moves one by one over the records. An explicit cursor uses a pointer that holds the record of a row. After fetching a row, the cursor pointer moves to the next row.

Main components of Cursors

Each cursor contains the followings 5 parts:

  1. Declare Cursor:In this part we declare variables and return a set of values.
  2. Open:This is the entering part of the cursor.
  3. Fetch:Used to retrieve the data row by row from a cursor.
  4. Close:This is an exit part of the cursor and used to close a cursor.
  5. Deallocate:In this part we delete the cursor definition and release all the system resources associated with the cursor.

Syntax of a Cursor

  1. DECLARE@Variable  nvarchar(50)  /* Declare All Required Variables */
  2. DECLARECursor_Name CURSOR       /* Declare Cursor Name*/
  3. [LOCAL | GLOBAL]               /* Define  Cursor Scope  */
  4. [FORWARD_ONLY | SCROLL]                /* Define  Movement Direction  of Cursor  */
  5. [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD] /* Define basic type of cursor   */
  6. [  SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]    /*   Define Locks  */
  7. OPEN Cursor_Name               /* Open Cursor  */
  8. FETCH NEXT FROM Cursor_Name    /*  Fetch data From Cursor  */
  9. Implement SQL QUery
  10. CLOSE Cursor_Name              /*  Clsoe The Cursor  */
  11. DEALLOCATECursor_Name          /* Deallocate all resources and Memory */

Now we will explain 4 important terminologies of cursors.

Cursor Scope

Microsoft SQL Server supports the GLOBAL and LOCAL keywords on the DECLARE CURSOR statement to define the scope of the cursor name.

  1. GLOBAL:specifies that the cursor name is global to the connection.
  2. LOCAL:specifies that the cursor name is local to the Stored Procedure, trigger or query that holds the cursor.

Data Fetch Option in Cursors

Microsoft SQL Server supports the following two fetch options for data:

  1. FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row.
  2. SCROLL:It provides 6 options to fetch the data (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).

Types of cursors

Microsoft SQL Server supports the following 4 types of cursors.

  1. STATIC CURSOR:A static cursor populates the result set during cursor creation and the query result is cached for the lifetime of the cursor. A static cursor can move forward and backward.
  2. FAST_FORWARD:This is the default type of cursor. It is identical to the static except that you can only scroll forward.
  3. DYNAMIC:In a dynamic cursor, additions and deletions are visible for others in the data source while the cursor is open.
  4. KEYSET:This is similar to a dynamic cursor except we can’t see records others add. If another user deletes a record, it is inaccessible from our record set.

Types of Locks

Locking is the process by which a DBMS restricts access to a row in a multi-user environment. When a row or column is exclusively locked, other users are not permitted to access the locked data until the lock is released. It is used for data integrity. This ensures that two users cannot simultaneously update the same column in a row.

Microsoft SQL Server supports the following three types of Locks.

  1. READ ONLY:Specifies that the cursor cannot be updated.
  2. SCROLL_LOCKS: Provides data integrity into the cursor. It specifies that the cursor will lock the rows as they are read into the cursor to ensure that updates or deletes made using the cursor will succeed.
  3. OPTIMISTIC:Specifies that the cursor does not lock rows as they are read into the cursor. So, the updates or deletes made using the cursor will not succeed if the row has been updated outside the cursor.

First we create a table as in the following:

  1. GO
  2. CREATETABLE [dbo].[Employee](
  3. [Emp_ID] [int] NOT NULL,
  4. [Emp_Name] [nvarchar](50) NOT NULL,
  5. [Emp_Salary] [int] NOT NULL,
  6. [Emp_City] [nvarchar](50) NOT NULL,
  7. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  8. (
  9. [Emp_ID] ASC
  10. )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
  11. ON[PRIMARY]
  12. GO

Now insert some values into the table as in the following:

  1. Insertinto Employee
  2. Select1,’Pankaj’,25000,’Alwar’ Union All
  3. Select2,’Rahul’,26000,’Alwar’ Union All
  4. Select3,’Sandeep’,25000,’Alwar’ Union All
  5. Select4,’Sanjeev’,24000,’Alwar’ Union All
  6. Select5,’Neeraj’,28000,’Alwar’ Union All
  7. Select6,’Naru’,20000,’Alwar’ Union All
  8. Select7,’Omi’,23000,’Alwar’

Select all values from the table as in the following:

Example 1

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. LOCALFORWARD_ONLY  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. WHILE @@FETCH_STATUS = 0
  12. BEGIN
  13. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  14. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  15. END
  16. CLOSEEMP_CURSOR
  17. DEALLOCATEEMP_CURSOR

Output

EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar
EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000 EMP_CITY Alwar
EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar
EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
EMP_ID: 5 EMP_NAME Neeraj EMP_SALARY 28000 EMP_CITY Alwar
EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar
EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar

This is a simple example of a cursor that prints the value of a table.

Example 2 (SCROLL)

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. LOCALSCROLL  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. FETCHRELATIVE 3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  12. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  13. FETCHABSOLUTE  3 FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  14. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  15. FETCHFIRST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  16. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  17. FETCHLAST FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  18. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  19. FETCHPRIOR FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  20. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  21. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  22. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  23. CLOSEEMP_CURSOR
  24. DEALLOCATEEMP_CURSOR

Output:

EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
EMP_ID: 3 EMP_NAME Sandeep EMP_SALARY 25000 EMP_CITY Alwar
EMP_ID: 1 EMP_NAME Pankaj EMP_SALARY 25000 EMP_CITY Alwar
EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar
EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar
EMP_ID: 7 EMP_NAME Omi EMP_SALARY 23000 EMP_CITY Alwar

In this example we will use SCROLL to fetch the data. This example contains all six modes of SCROLL (FIRST, LAST, PRIOR, NEXT, RELATIVE and ABSOLUTE).

Example 3 (STATIC CURSOR)

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. STATIC  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. WHILE @@FETCH_STATUS = 0
  12. BEGIN
  13. If @EMP_ID%2=0
  14. BEGIN
  15. PRINT  ‘EMP_ID: ‘+ CONVERT(NVARCHAR(MAX),@EMP_ID)+  ‘  EMP_NAME ‘+@EMP_NAME +’  EMP_SALARY ‘  +CONVERT(NVARCHAR(MAX),@EMP_SALARY)  +  ‘  EMP_CITY ‘ +@EMP_CITY
  16. END
  17. FETCHFROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  18. END
  19. CLOSEEMP_CURSOR
  20. DEALLOCATEEMP_CURSOR

Output

EMP_ID: 2 EMP_NAME Rahul EMP_SALARY 26000  EMP_CITY Alwar
EMP_ID: 4 EMP_NAME Sanjeev EMP_SALARY 24000 EMP_CITY Alwar
EMP_ID: 6 EMP_NAME Naru EMP_SALARY 20000 EMP_CITY Alwar

Example 4

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. STATIC  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. WHILE @@FETCH_STATUS = 0
  12. BEGIN
  13. If @EMP_ID%2=0
  14. BEGIN
  15. UPDATEEmployee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
  16. END
  17. FETCHFROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  18. END
  19. CLOSEEMP_CURSOR
  20. DEALLOCATEEMP_CURSOR

Output

Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.

When executing this cursor, we will get an error because the static cursor does not allow modifications in data.

Example 5 (DYNAMIC CURSOR)

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. DYNAMIC  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. WHILE @@FETCH_STATUS = 0
  12. BEGIN
  13. If @EMP_ID%2=0
  14. BEGIN
  15. UPDATEEmployee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
  16. END
  17. FETCHFROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  18. END
  19. CLOSEEMP_CURSOR
  20. DEALLOCATEEMP_CURSOR
  21. SELECTFROM Employee

Output

Example 6

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR CURSOR
  7. FAST_FORWARD  FOR
  8. SELECTFROM Employee
  9. OPENEMP_CURSOR
  10. FETCHNEXT FROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  11. WHILE @@FETCH_STATUS = 0
  12. BEGIN
  13. If @EMP_ID%2=0
  14. BEGIN
  15. UPDATEEmployee SET Emp_Salary=15000 WHERE CURRENT OF EMP_CURSOR
  16. END
  17. FETCHFROM EMP_CURSOR INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  18. END
  19. CLOSEEMP_CURSOR
  20. DEALLOCATEEMP_CURSOR

Output

Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.
Msg 16929, Level 16, State 1, Line 16
The cursor is READ ONLY.
The statement has been terminated.

A FAST_FORWARD cursor also reads as a static cursor. We cannot modify data in a FAST_FORWARD cursor.

Example 7

  1. SETNOCOUNT ON
  2. DECLARE@EMP_ID INT
  3. DECLARE@EMP_NAME NVARCHAR(MAX)
  4. DECLARE@EMP_SALARY INT
  5. DECLARE@EMP_CITY NVARCHAR(MAX)
  6. DECLAREEMP_CURSOR1 CURSOR
  7. KEYSET scroll
  8. FOR
  9. SELECTEMP_ID ,EMP_NAME,EMP_SALARY,EMP_CITY FROM Employee  order by Emp_Id
  10. OPENEMP_CURSOR1
  11. IF @@CURSOR_ROWS > 0
  12. BEGIN
  13. FETCHNEXT FROM EMP_CURSOR1 INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  14. WHILE @@FETCH_STATUS = 0
  15. BEGIN
  16. If @EMP_ID%2=0
  17. UPDATEEmployee SET EMP_NAME=’PANKAJ KUMAR CHOUDHARY’ WHERE CURRENT OF EMP_CURSOR1
  18. FETCHNEXT FROM EMP_CURSOR1 INTO  @EMP_ID ,@EMP_NAME,@EMP_SALARY,@EMP_CITY
  19. END
  20. END
  21. CLOSEEMP_CURSOR1
  22. DEALLOCATEEMP_CURSOR1
  23. SETNOCOUNT OFF
  24. SELECTFROM Employee

Output

  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • How to Delete Duplicate Rows?

 

 

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MI:SS
  • SMALLDATETIME – format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP – format: a unique number
  • SQL Server String Functions
Function Description
ASCII Returns the number code that represents the specific character
CHAR Returns the ASCII character based on the number code
CHARINDEX Returns the location of a substring in a string
CONCAT Concatenates two or more strings together
Concat with + Concatenates two or more strings together
DATALENGTH Returns the length of an expression (in bytes)
LEFT Extracts a substring from a string (starting from left)
LEN Returns the length of the specified string
LOWER Converts a string to lower-case
LTRIM Removes leading spaces from a string
NCHAR Returns the Unicode character based on the number code
PATINDEX Returns the location of a pattern in a string
REPLACE Replaces a sequence of characters in a string with another set of characters
RIGHT Extracts a substring from a string (starting from right)
RTRIM Removes trailing spaces from a string
SPACE Returns a string with a specified number of spaces
STR Returns a string representation of a number
STUFF Deletes a sequence of characters from a string and then inserts another sequence of characters into the string, starting at a specified position
SUBSTRING Extracts a substring from a string
UPPER Converts a string to upper-case

SQL Server Numeric Functions

Function Description
ABS Returns the absolute value of a number
AVG Returns the average value of an expression
CEILING Returns the smallest integer value that is greater than or equal to a number
COUNT Returns the count of an expression
FLOOR Returns the largest integer value that is equal to or less than a number
MAX Returns the maximum value of an expression
MIN Returns the minimum value of an expression
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SUM Returns the summed value of an expression

SQL Server Date Functions

Function Description
CURRENT_TIMESTAMP Returns the current date and time
DATEADD Returns a date after a certain time/date interval has been added
DATEDIFF Returns the difference between two date values, based on the interval specified
DATENAME Returns a specified part of a given date, as a string value
DATEPART Returns a specified part of a given date, as an integer value
DAY Returns the day of the month (from 1 to 31) for a given date
GETDATE Returns the current date and time
GETUTCDATE Returns the current UTC date and time
MONTH Returns the month (from 1 to 12) for a given date
YEAR Returns the year (as a four-digit number) for a given date

SQL Server Conversion Functions

Function Description
CAST Converts an expression from one data type to another
CONVERT Converts an expression from one data type to another

SQL Server Advanced Functions

Function Description
COALESCE Returns the first non-null expression in a list
CURRENT_USER Returns the name of the current user in the SQL Server database
ISDATE Returns 1 if the expression is a valid date, otherwise 0
ISNULL Lets you return an alternative value when an expression is NULL
ISNUMERIC Returns 1 if the expression is a valid number, otherwise 0
NULLIF Compares two expressions
SESSION_USER Returns the user name of the current session in the SQL Server database
SESSIONPROPERTY Returns the setting for a specified option of a session
SYSTEM_USER Returns the login name information for the current user in the SQL Server database
USER_NAME Returns the user name in the SQL Server database

SQL Arithmetic Operators

Operator Description Example
+ Add Try it
Subtract Try it
* Multiply Try it
/ Divide Try it
% Modulo Try it

SQL Bitwise Operators

Operator Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

SQL Comparison Operators

Operator Description Example
= Equal to Try it
> Greater than Try it
< Less than Try it
>= Greater than or equal to Try it
<= Less than or equal to Try it
<> Not equal to Try it

SQL Compound Operators

Operator Description
+= Add equals
-= Subtract equals
*= Multiply equals
/= Divide equals
%= Modulo equals
&= Bitwise AND equals
^-= Bitwise exclusive equals
|*= Bitwise OR equals

SQL Logical Operators

Operator Description Example
ALL TRUE if all of the subquery values meet the condition Try it
AND TRUE if all the conditions separated by AND is TRUE Try it
ANY TRUE if any of the subquery values meet the condition Try it
BETWEEN TRUE if the operand is within the range of comparisons Try it
EXISTS TRUE if the subquery returns one or more records Try it
IN TRUE if the operand is equal to one of a list of expressions Try it
LIKE TRUE if the operand matches a pattern Try it
NOT Displays a record if the condition(s) is NOT TRUE Try it
OR TRUE if any of the conditions separated by OR is TRUE Try it
SOME TRUE if any of the subquery values meet the condition

SQL Data Types

Each column in a database table is required to have a name and a data type.

An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

Note: Data types might have different names in different database. And even if the name is the same, the size and other details may be different! Always check the documentation!

MySQL Data Types

In MySQL there are three main data types: text, number, and date.

Text data types:

Data type Description
CHAR(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT Holds a string with a maximum length of 255 characters
TEXT Holds a string with a maximum length of 65,535 characters
BLOB For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.) Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.

Note: The values are sorted in the order you enter them.

You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)

SET Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number data types:

Data type Description
TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date data types:

Data type Description
DATE() A date. Format: YYYY-MM-DD

Note: The supported range is from ‘1000-01-01’ to ‘9999-12-31’

DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MI:SS

Note: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC

TIME() A time. Format: HH:MI:SS

Note: The supported range is from ‘-838:59:59’ to ‘838:59:59’

YEAR() A year in two-digit or four-digit format.

Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.

SQL Server Data Types

String data types:

Data type Description Max size Storage
char(n) Fixed width character string 8,000 characters Defined width
varchar(n) Variable width character string 8,000 characters 2 bytes + number of chars
varchar(max) Variable width character string 1,073,741,824 characters 2 bytes + number of chars
text Variable width character string 2GB of text data 4 bytes + number of chars
nchar Fixed width Unicode string 4,000 characters Defined width x 2
nvarchar Variable width Unicode string 4,000 characters  
nvarchar(max) Variable width Unicode string 536,870,912 characters  
ntext Variable width Unicode string 2GB of text data  
binary(n) Fixed width binary string 8,000 bytes  
varbinary Variable width binary string 8,000 bytes  
varbinary(max) Variable width binary string 2GB  
image Variable width binary string 2GB  

Number data types:

Data type Description Storage
bit Integer that can be 0, 1, or NULL  
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
numeric(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes
money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

4 or 8 bytes
real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date data types:

Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable  

Other data types:

Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing

Microsoft Access Data Types

Data type Description Storage
Text Use for text or combinations of text and numbers. 255 characters maximum  
Memo Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable  
Byte Allows whole numbers from 0 to 255 1 byte
Integer Allows whole numbers between -32,768 and 32,767 2 bytes
Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
Single Single precision floating-point. Will handle most decimals 4 bytes
Double Double precision floating-point. Will handle most decimals 8 bytes
Currency Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country’s currency to use 8 bytes
AutoNumber AutoNumber fields automatically give each record its own number, usually starting at 1 4 bytes
Date/Time Use for dates and times 8 bytes
Yes/No A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields 1 bit
Ole Object Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink Contain links to other files, including web pages  
Lookup Wizard Let you type a list of options, which can then be chosen from a drop-down list 4 bytes

 

Standard

throw, throw ex, throw new ex

throw; rethrows the original exception and preserves its original stack trace.

throw ex; throws the original exception but resets the stack trace, destroying all stack trace information until your catch block.

NEVER write throw ex;

throw new Exception(ex.Message); is even worse. It creates a brand new Exception instance, losing the original stack trace of the exception, as well as its type. (eg, IOException).
In addition, some exceptions hold additional information (eg, ArgumentException.ParamName).
throw new Exception(ex.Message); will destroy this information too.

In certain cases, you may want to wrap all exceptions in a custom exception object, so that you can provide additional information about what the code was doing when the exception was thrown.

To do this, define a new class that inherits Exceptionadd all four exception constructors, and optionally an additional constructor that takes an InnerException as well as additional information, and throw your new exception class, passing ex as the InnerException parameter. By passing the original InnerException, you preserve all of the original exception’s properties, including the stack trace.

Standard