sql query classes

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. 

(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

 

6.      Create Table in SQL Server: 

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