The BETWEEN and NOT BETWEEN Operator In SQL

BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

Example:

Table (tbl_StuDetails):

S_Id	LastName	FirstName	Age	DOB
1	Verma	Ram	18	02-Apr-1992
2	Sharma	Mohan	23	02-Dec-1987
3	Singh	Hari	20	02-Apr-1990

Now we want to select the Student with age  between 15 and 20 from the table above.


We use the following SELECT statement:

SELECT * FROM tbl_StuDetails WHERE age BETWEEN 15 and 20

The result-set will look like this:

s_Id	LastName	FirstName	Age	DOB
1	Verma	Ram	18	02-Apr-1992
3	Singh	Hari	20	02-Apr-1990

Note: The BETWEEN operator takes initialised values.

---

NOT BETWEEN Operator


To display the Students outside the range in the previous example, use NOT BETWEEN:

SELECT * FROM tbl_StuDetails WHERE age NOT BETWEEN 15 AND 20

The result-set will look like this:

S_Id	LastName	FirstName	Age	DOB
2	Sharma	Mohan	23	02-Dec-1987

Shortcut Keys in SQL Server

Shortcut keys are very important aspects for fast accessing in SQL. Fillowing keys are used as shortsuts in SQL  Server.

Description

Shortcuts

Clear all bookmarks. CTRL+SHIFT+F2 Save All CTRL+SHIFT+S Go To line CTRL+G Display Solution Explorer CTRL+ALT+L Previous Child Window CTRL+SHIFT+TAB Next Child Window CTRL+TAB Full Screen Mode SHIFT+ALT+ENTER Comment a Text CTRL+K, CTRL+C UnComment a Text CTRL+K,CTRL+U Close a menu or Dialog Box ESC Display Query Designer CTRL+SHIFT+Q Display Registered Server CTRL+ALT+G Insert or remove a bookmark (toggle). CTRL+F2 Move to next bookmark. F2 Move to previous bookmark. SHIFT+F2 Cancel a query. ALT+BREAK Connect or Open File. CTRL+O Disconnect Connections. CTRL+F4 Disconnect Connection and close child window. CTRL+F4 Database object information. ALT+F1 Clear the active Editor pane. CTRL+SHIFT+ DEL Comment out code. CTRL+SHIFT+C Copy. You can also use CTRL+INSERT. CTRL+C Cut. You can also use SHIFT+DEL. CTRL+X Decrease indent. SHIFT+TAB Delete through the end of a line in the Editor pane. CTRL+DEL Find. CTRL+F Go to a line number. CTRL+G Increase indent. TAB Make selection lowercase. CTRL+SHIFT+L Make selection uppercase. CTRL+SHIFT+U Paste. You can also use SHIFT+INSERT. CTRL+V Remove comments. CTRL+SHIFT+R Repeat last search or find next. F3 Replace. CTRL+H Select all. CTRL+A Undo. CTRL+Z Execute a query. You can also use CTRL+E. F5 Help for SQL Query Analyzer. F1 Help for the selected Transact-SQL statement. SHIFT+F1 Switch between query and result panes. F6 Switch panes. SHIFT+F6 Window Selector. CTRL+W New Query window or Create new file. CTRL+N Object Explorer (show/hide). F8 Object Search. F4 Parse the query and check syntax. CTRL+F5 Print. CTRL+P Display results in grid format. CTRL+D Display results in text format. CTRL+T Move the splitter. CTRL+B Save results to file. CTRL+SHIFT+F Show Results pane (toggle). CTRL+R Save. CTRL+S Insert a template. CTRL+SHIFT+INSERT Replace template parameters. CTRL+SHIFT+M Display estimated execution plan. CTRL+L Display execution plan (toggle ON/OFF). CTRL+K Index Tuning Wizard. CTRL+I Show client statistics CTRL+SHIFT+S Show server trace. CTRL+SHIFT+T Use database CTRL+U Cancel Executing Query ALT+BREAK Toggle Between Query and Results Pan F6 List members in Intellisense CTRL+SPACE Display Context Menu SHIFT+F10

                       

SELECT INTO Statement In SQL

The SELECT INTO statement selects data from one table and inserts it into a different table. The SELECT INTO statement is most often used to create backup copies of tables.

Syntax:

We can select all columns into the new table:

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename

Example:

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
We use the following SQL statement:

SELECT * INTO Persons_Backup FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

---

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.
The following SQL statement creates a "Persons_Backup" table with only the persons who lives in the city "Sandnes":

SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes'

---

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id

Thanks

ALTER TABLE Statement SQL

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Syntax for Rename a Table:

ALTER TABLE table_name RENAME TO new_table_name;

Example:

ALTER TABLE suppliers RENAME TO vendors;

This will rename the suppliers table to vendors.

---

Syntax for Add New Column:

ALTER TABLE table_name ADD column_name column-Datatype;

Example:

ALTER TABLE supplier ADD supplier_name  varchar(50);

Add Multiple Columns

ALTER TABLE supplier ADD (supplier_name varchar(50), city varchar(45) );

---

Modifying existing Column in Table:


Syntax:

ALTER TABLE table_name  MODIFY column_name column_type;

Example:

ALTER TABLE supplier  MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

---

Modifying multiple existing Columns in Table:


Syntax:

ALTER TABLE table_name MODIFY (column_1column_type, column_2column_type, ... column_n column_type );

Example:

ALTER TABLE supplier MODIFY (supplier_name varchar(100) not null, city varchar(75) );

---

DROP COLUMNS FROM TABLE:


Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE supplier DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

---

Rename column(s) in a table:

Syntax:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Example:

ALTER TABLE supplier RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Thanks