SQL Tools(DROP , TRUNCATE)

DROP TABLE :-
DROP TABLE table_name

DROP DATABASE :-
DROP DATABASE database_name

TRUNCATE TABLE :-
We use truncate statement if we want to delete the values of the table not the table itself. Like :-

TRUNCATE TABLE table_name

DROP INDEX :-
DROP INDEX table_name.index_no

Read More

SQL Tools(Check , DEFAULT, CREATE INDEX)

Check :-

CREATE TABLE SHIP_Orders
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25),
@ORDER_ADDRESS VARCHAR(45),
CHECK(@O_ID>2)
)

DEFAULT :-

We can provide default values to the table like in the above table :-

CREATE TABLE SHIP_ORDERS
(
@O_ID INT NOT NULL,
@ORDER_NAME VARCHAR(25) DEFAULT 'SLITTING',
@ORDER_ADDRESS VARCHAR(45) DEFAULT 'DELHI AIRPORT',
CHECK(@O_ID>2)
)

TO ALTER DEFAULT VALUE :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME SET DEFAULT 'SPLIT'

TO DROP DEFAULT VALUES :-
ALTER TABLE SHIP_ORDERS
ALTER COLUMN @ORDER_NAME DROP DEFAULT

CREATE INDEX :-

CREATE INDEX index_name
ON table_name (column_name)

LIKE :-
CREATE INDEX index
ON SHIP_ORDERS (@ORDER_NAME,@ORDER_ADDRESS)

Read More

SQL Tools(Create DB, Create Table, UNION, UNION ALL, NOT NULL, UNIQUE, PRIMARY KEY, Foreign Key)

To Create Database :-
CREATE DATABASE Db_Name

To Create Table :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

if a column contains NOT NULL values then it means it does not allow the null values.

Unique :-

CREATE TABLE Person
(
@ID INT NOT NULL UNIQUE,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR
)

Another way to represent is and the way to define primary key :-

CREATE TABLE Person
(
@ID INT NOT NULL,
@Name VARCHAR(20),
@ADDRESS VARCHAR(255) NOT NULL,
@PS_NO SMALLINT,
@FIRST_CHAR CHAR,
UNIQUE(@ID),
PRIMARY KEY(@ID)
)

A primary key value can not have null values.

If more than one column has unique value then below way we will handle :-
UNIQUE(@ID, @ADDRESS)

FOREIGN KEY :-

If a table does not have primary key then it points to a primary key to another table then that key calls a foreign key for that table like :-

CREATE TABLE Person_Values
(
@P_ID INT NOT NULL,
@ID INT NOT NULL,
@P_Name VARCHAR(20),
@P_ADDRESS VARCHAR(255) NOT NULL,
@P_PS_NO SMALLINT,
@P_FIRST_CHAR CHAR,
FOREIGN KEY (@Id) REFERENCES Persons(@Id)
)

@ID is a primary key of Persons table and now working as a foreign key for Person_Values table.

UNION AND UNION ALL :-
Union will select the distinct values from both columns. In more general form Union with distinct values
Union All select all the values from selected columns.

UNION :-

SELECT column_name From table1
UNION
SELECT column_name From table2

UNION ALL :-

SELECT column_name From table1
UNION ALL
SELECT column_name From table2

Read More

SQL Tools( Count, MAX, MIN)

COUNT is a method which will count the total no of columns,total no of rows,total no of values.Like :-

SELECT COUNT(Column_Name) FROM Table_Name
This will count total no of rows where First_Name is Saurabh.
SELECT Count(*) FROM Table_Name WHERE First_Name = 'Saurabh'

SELECT COUNT(First_Name) AS NoOfStudents FROM STUDENTS
STUDENTS is a Table_Name

Max :-
SELECT MAX(Column1) From Table1
SELECT MAX(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth > 12202009

Min :-
SELECT MIN(Column1) From Table1
SELECT MIN(DateOfBirth) AS BirthDate FROM Employee WHERE DateOfBirth < 12202009

Read More

SQL Tools(SELECT INTO, INSERT INTO)

SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
The general SQL SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3,
INTO Table2 FROM Table1

This will create a Table2 same as Table1.

SQL INSERT INTO :-

1:- INSERT INTO Table1 VALUES (value1, value2, value3…)
2:- INSERT INTO Table1 (column1,column2,column3) VALUES(value1,value2,value3)

Read More