Sql Query classes
This is a free SQL query class. If you are interested to learn SQL Server Database, follow this SQL lesson topic one by one. The SQL Server lesson topic present in right side of this blog (Start from 2.Create a Database).
If you get problem while executing the lesson topic. Question me on below Facebook page I will help you. Question should be related to this lesson topic.
This course is designed basic to advance level. If you learn continuously in this blog, you can work in Sql Server as an Sql Database administrator.
If you get problem while executing the lesson topic. Question me on below Facebook page I will help you. Question should be related to this lesson topic.
This course is designed basic to advance level. If you learn continuously in this blog, you can work in Sql Server as an Sql Database administrator.
(Facebook page: https://www.facebook.com/Sql-training-online-116160423266346/)
Before starting this SQL query class, you must have SQL Server on your computer. The course prefers SQL Server 2005 or greater.
The course is designed from a very basic level. Anyone able to understand the SQL server database. The language is used very simple.
The SQL server is the most popular database. SQL database is used to store large data.
1. Creating Database:
Database is used storage the large relational data. It is
very useful to store large volume data.
In SQL Server DATABASE keyword is used to create a
database.
To create database in SQL Server syntax:
Create Database [DATABASE_NAME]
Description:
Create: Create is SQL function.
Database: It is a SQL function to create database.
[DATABASE_NAME]: Name of database.
Example of creating database in SQL Server
Create Database Product
Create Database Order
2. Database
rename:
How to change database name syntax:
<
sp_renamedb 'OLD_DATABASE_NAME','NEW_DATABASE_NAME'
Description:
sp_renamedb: It is a function to change SQL Server
database name.
OLD_DATABASE_NAME: It is a existing database name.
NEW_DATABASE_NAME: It is a new database name.
Example of how to change database name in SQL Server
sp_renamedb 'Product','ProductDet
sp_renamedb 'Order','OrderDet'
3. Deleting
database:
Syntax to drop database in SQL Server:
Drop DATABASE_NAME
Description:
Drop: It is a function to delete database in SQL server.
DATABASE_NAME: It is a name of database.
How to delete database in example:
Drop ProductDet
Drop OrderDet
4. USE
Command: Use command is used change database name through SQL Query command.
Syntax to change
database in SQL Server:
USE ['DABASE_NaME']
Description:
Use: It is a SQL
command.
'DATABASE_NAME'=It
is name of database.
Example:
Use 'ProductDet' Use 'OrderDet'
5. DataType: It specify the type of data. Like integer, Image, text etc.
DataTypes:
SN
|
Data type
|
Description
|
Max size
|
1
|
varchar(n)
|
character string
|
8,000 characters
|
2
|
varchar(max)
|
character string
|
1,073,741,824 characters
|
3
|
nvarchar
|
unicode string
|
4,000 characters
|
4
|
nvarchar(max)
|
unicode string
|
536,870,912 characters
|
5
|
char(n)
|
fixed character string
|
8,000 characters
|
6
|
nchar
|
fixed Unicode string
|
4,000 characters
|
7
|
text
|
character string
|
2GB of text data
|
8
|
ntext
|
unicode string
|
2GB of text data
|
9
|
binary(n)
|
Fixed binary string
|
8,000 bytes
|
10
|
varbinary
|
binary string
|
8,000 bytes
|
11
|
varbinary(max)
|
binary string
|
2GB
|
12
|
image
|
binary string
|
2GB
|
13
|
bit
|
can be 0, 1, or NULL
|
|
14
|
tinyint
|
whole numbers
|
from 0 to 255
|
15
|
smallint
|
whole numbers
|
between -32,768 and 32,767
|
16
|
int
|
whole numbers
|
between -2,147,483,648 and 2,147,483,647
|
17
|
float(n)
|
floating number
|
from -1.79E + 308 to 1.79E + 308.
|
18
|
datetime
|
date only
|
From January 1, 1753 to December 31, 9999
|
19
|
date
|
date only
|
From January 1, 0001 to December 31, 9999
|
20
|
time
|
time only
|
Table is a combination of row and column. It manage the record.
Syntax:
Create Table
TABLE_NAME
(
COLUMN_NAME
DataType Length,
COLUMN_NAME
DataType Length,
COLUMN_NAME
DataType Length,
COLUMN_NAME
DataType Length,
..............
..............
)
Description:
Create: It is
keyword.
Table: It is
Keyword.
TABLE_NAME: Name
of table.
COLUMN_NAME:
Name of column/field.
DataType: Type of
value.
Length: Length of
value can store.
Example: (A) Creating normal table in sql
Create Table ItemDetails
(
ItemCode Varchar(20),
ItemName Varchar(50),
ItemQuantity int,
ItemPrice float,
ManufactureDate date,
ExpiryDate Date,
ReceivedDate DateTime,
ProductImage Image
)
B) Create table in SQL Serve with NULL and NOT NULL Operator.
NULL : It
indicate the unknown value. It allows the NULL value in table.
NOT NULL: It
forces to insert the value in SQL Server Table.
Creating table in SQL Server with NULL And NOT NULL
Create Table ItemDetails
( ItemCode VarChar(20) NOT NULL, ItemName VarChar(50)NOT NULL, ItemQuantity int NOT NULL, ItemPrice float NULL, ManufactureDate Date NOT NULL, ExpiryDate Date NOT NULL, ReceivedDate datetime NOT NULL, Description VarChar(100)NOT NULL, Remarks VarChar(50)NULL, ProductImage image NULL, )
C. Create Table in SQL Server with PRIMARY KEY.
Primary Key: It does not allowed to insert duplicate
and NULL value in table.
Creating table in
SQL Server with PRIMARY KEY constraint
Create Table ItemDetails11
(
ItemCode VarChar(20) NOT NULL constraint pkItemCode Primary Key,
ItemName VarChar(50)NOT NULL,
ItemQuantity int NOT NULL,
ItemPrice float NULL,
ManufactureDate Date NOT NULL,
ExpiryDate Date NOT NULL,
ReceivedDate datetime NOT NULL,
Description VarChar(100)NOT NULL,
Remarks VarChar(50)NULL,
ProductImage image NULL,
)
[Note: pkItemCode is name of primary key. It may any.]
D. Creating Table with UNIQUE constraint.
UNIQUE: It same
as PRIMARY KEY but it allows one NULL value in table.
Example of creating table with UNIQUE
Create Table ItemDetails
(
ItemCode VarChar(20) NOT NULL constraint pkItemCode Primary Key,
ItemName VarChar(50)NOT NULL,
ItemQuantity int NOT NULL,
ItemPrice float NULL,
ManufactureDate Date NOT NULL,
ExpiryDate Date NOT NULL,
ReceivedDate datetime NOT NULL,
Description VarChar(100)NOT NULL,
Remarks VarChar(50)NULL,
ProductImage image NULL,
ImageId int CONSTRAINT unqImageId Unique,
SerialNo int CONSTRAINT unqserial Unique
)
E. Creating Table with FOREIGN KEY constraint.
FOREIGN KEY:
Foreign key constraint is used to refer another record.
Example of
creating table with FOREIGN KEY
Create Table ItemDetails ( ItemCode VarChar(20) NOT NULL constraint pkItemCode Primary Key, ItemName VarChar(50)NOT NULL, ItemQuantity int NOT NULL, ItemPrice float NULL, ManufactureDate Date NOT NULL, ExpiryDate Date NOT NULL, ReceivedDate datetime NOT NULL, Description VarChar(100)NOT NULL, Remarks VarChar(50)NULL, ProductImage image NULL, ImageId int CONSTRAINT unqImageId Unique, SerialNo int CONSTRAINT unqserial Unique ) Create Table OrderDetails ( OrderId VarChar(20)NOT NULL Constraint pkOrderId Primary key, ItemCode VarChar(20) NOT NULL References ItemDetails(ItemCode), ItemName VarChar(50)NOT NULL, OrderQuantity int NOT NULL, OrderDate DateTime NOT NULL, OrderBy VarChar(50)NOT NULL, OrderPhone VarChar(50)NOT NULL, OrderSerial int Constraint unqorderseial UNIQUE )
SQL Server
Best of Luck
Yes I need, but how can join?
ReplyDelete.
DeleteYou don't need to do anything to join. Follow the one by one topic. If you get problem about this topic then question me, I will answer you.
DeleteN
ReplyDelete;nice post.sql server developer training
ReplyDeletesql server developer online training
sql tainining
best institute for sql in hyderabad
nice post.sql server developer training
ReplyDeletesql server developer online training
sql tainining
best institute for sql in hyderabad