What is an inner join in SQL?

What is an inner join in SQL?

Inner Join:-

It returns only matching rows from both the tables.

Syntax:-

Select a.column1,a.column2,b.column1,b.column2

from table1 as a INNER JOIN table2 as b

on a.Common_field = b.Common_field

Example:-

SELECT a.OrderID, b.CustomerName
FROM Orders as a
INNER JOIN Customers as b

ON a.CustomerID = b.CustomerID;

What are the functions of the MS SQL Server database?

What are the functions of the MS SQL Server database?

->Function is a database object in the SQL server. Basically, it is a set of sql Settlement that accepts only input parameter, performs and returns the result.

->Function can return only a single value or label.

->We can’t use the function to insert, update, delete records in the database table.

Function Types:-

1)Scalar Function:-

The function perform calculation on a input value and return a single value.

example:-

Round(),MID(),LCASE(),UCASE() etc.

2)Aggregate Function:-

Aggregate function operator on a collection of values and returns single value.

i)MAX():-

Select Max(Couloumn_name) as Alias name from table

ii)MIN():-

Select MIN(Couloumn_name) as Alias name from table

iii)AVG():-

Select AVG(Couloumn_name) as Alias name from table

iv)COUNT:-

Select Count(Couloumn_name) as Alias name from table

v)SUM:-

Select SUM(Couloumn_name) as Alias name from table

vi)LOWER():-

Select LOWER(Couloumn_name) as Alias name from table

vii)UPPER:-

Select UPPER(Couloumn_name) as Alias name from table

viii)LTRIM():-

Select LTRIM(‘ vijay’)

ix)RTRIM():-

Select RTRIM(‘ vijay’)

x)Length Aggregate Fucnction:-

Select Coloumn_name, LEN(Couloumn_name) as Alias name from table where id=’1′

How do I create a new table with another column from existing 2 tables where values be filled according to certain conditions?

How do I create a new table with another column from existing 2 tables where values be filled according to certain conditions?

The syntax for creating a new table with another column

1)create table by copy all column from antother table

syntax:-

create Table New_tableName

as

select column1,column2,column3 from existing table.

2)create table by copy selected column from another table

syntax:-

create Table New_tableName as

select column1,column2,column3 from oldtable1,oldtable2,oldtable3

example:-

create Table Employee as

select Company.CID,,Company.Address,Department.Depat from Company,Department

more

What is the SQL left join keyword?

LEFT JOIN IN SQL SERVER

Left Join:- It returns all records from the left table and matching records from the right table.

Syntax:-

Select a.Coloumn1,a.Coloumn2,b.Coloumn1,b.Coloumn2

from Table1 as a LEFT JOIN Table2 as b

Where a.CommonField= b.CommonField

Example:-

Select a.Name,a.Department,b.CompanyName,b.Degination

from Employee as a LEFT JOIN Company as b

Where a.EmplyeeId= b.CompanyID

What is the max size of varchar in SQL Server?

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

Varchar(n):-

1)Non-Unicode Variable Length character data type.

2)It can store a maximum of 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). Optional Parameter n value can be from 1 to 8000.

3)It takes 1 byte per character.

Varchar(max):-

1)Non-Unicode large Variable Length character data type.

2)It can store a maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).

3)It takes 1 byte per character

What is Semijoin in SQL?

What IS Semi Join IN SQL SERVER?

A semi-join between two tables returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Example:-

Let assume we have two table table1(Employee) and table2 (Department).

now table 1 has 3 fields Empid, EmpName, Department and table 2 has 2 field DepartmentName, Manager.

SELECT * FROM Employee WHERE EXISTS (

SELECT 1

FROM Dept

WHERE Employee.DeptName = Dept.DeptName

)




more

Foreign key in SQL server

What is foreign key SQL SERVER constraint?

->A Foreign key is used to link two tables.

->Foreign key is a one table point to another table.

->Foreign key can accept multiple NULL values.

->We can have more than one Foreign key in a table.

How to create Foreign Key in SQL SERVER by Query?

 CREATE TABLE Employee(

    EmpId int NOT NULL PRIMARY KEY identity(1,1),

    EmpName Varchar(200),

    Department varchar(200),

    CompanyId int FOREIGN KEY REFERENCES Company (CId)

);

where:-

 Employee - Table name 2

Company - Table Name 1

CId - Table 1 Column name(Primary key)

CompanyId - table 2 Column name(Foreign Key)

How to set Foreign key in SQL SERVER For Existing Table Made Simple.

Foreign Key

For More Sql Server Constraints

Click Here

Primary Key

What is a Primary Key?

->The Primary key constraints uniquely identify each record in a database.

->It contains a unique value.

->A Primary key column cannot be a NULL value.

Syntax

create table table_name(Id int identifier Primary Key, Name varchar(200), Roll_number int)

Types of SQL Constraints

How many types of SQL Constraints?

There are basically 6 types of SQL Constraints.

1)PRIMARY KEY

2)FOREIGN KEY

3)NOT NULL

4)UNIQUE

5)CHECK

6)DEFAULT

Constraints in SQL Server?

What Are SQL constraints?

->SQL constraints are the rule which is applied

 to the table column to store valid data and 

prevents the user from storing invalid data 

into table columns.

->SQL Constraints are part of a database 

schema definition.

->We can create constraints on single or 

multiple columns of any table.

Command in SQL Server?

Types of command in SQL Server?

1) Data Definition Language(DDL)

Data definition statements are used to define the database structure or table.

(i)CREATE :- Create new database/table
(ii)ALTER :- Modifies the structure of database / table.
(iii)DROP:- Deletes a database / table.
(iv) TRUNCATE:- Remove all the records from the table.
(v)RENAME:-Rename the database/table.

2) Data Manipulating Language(DML)

Data manipulating statements are used for managing data within the table.

(i)SELECT:- Retrieve data from a table.
(ii)INSERT: -Insert data into table.
(iii)UPDATE:-Update/Modify existing records in a table.
(iv)DELETE:- Delete the records from the table.

3)Data Control Language.


(i)GRANT:- Giver user access privileges database.
(ii)REVOKE: -Withdraw user access privileges given by using the GRANT command.


4)Transaction Control Language(TCL)

TCL commands deal with the transaction within the database.

(i)COMMIT: -commits a transaction.
(ii)ROLLBACK:-rollbacks a transaction in case of any error occurs.
(iii)SAVE-POINT:- sets a savepoint within a transaction.
(iv)SET TRANSACTION:- specify characteristics for the transaction.

5)Data Query Language

(i)SELECT:- It is used to retrieve data from the database.



Image Source: Google

How to Create Table in SQL SERVER?

How to Create a Table?


There are two ways to create Table

(i) With SQL Command

(ii)Without SQL Command

So, let's go with First way

(i) Create Table using SQL Command:-

Syntax:- 

Create Table table_name(Column_name1 datatype,column_name2 datatype,....)

Example:-

Create table Employee(Employee_Id Int,Employee_Name varchar(100), Employee_Department varchar(200) )

(ii) Without SQL Command:-

Step 1:-Right click on the table. Select New then select Table.

Step 2:- Enter Column name and datatype

Step 3:- Then Save the table.

Step 4:- Click OK.

How to Create database in SQL Server?

How to Create a Database?


There are two ways to create a database 

(i) With SQL Command

(ii)Without SQL Command

So, let's go with First way

(i) Create Database using SQL Command:-

Syntax:- 

Create Database DatabaseName

Example:-

Create Database Vijay



(ii) Without SQL Command:-

Step 1:-Open your SQL Server.

Step 2:- Open Object Explorer.

Step 3:- Right-click on database. Select New Database.

Step 4:- Enter your database Name.

Step 5:- Click ok.

DDL & DML Commands

What are DDL Commands?


->The Full style of DDL is Data Definition Language.

->The DDL manage table and index structure.

->Its basic work is.  
                    (i)Create an object in the database.

                    (ii)Drop deletes an obj in the database.

                    (iii)Alter modifiers the structured an existing table.

What is DML Commands?

->The full form of DML is Data manipulating Language.

->It is used for Insert, Deletes, Update records in a table.

Table and Field in SQL Server

What is a table? 

->A table is a set of records the are organized in a model with Column and Rows.

->Column can be organized as vertical direction.

->Rows can be organized as the horizontal direction.

Table and Field in SQL Server

What is the field?

A table has some Columns, that's column is called field.

Example:-

In the above figure.
The filed in the STUDENT table consist of ID, STDENT_NAME, ROLL_NUMBER

RDBMS Concepts

What is RDBMS?
RDBMS concepts
-> The Full form of RDBMS is Relational Database Management System.

->RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM     DB2, Oracle, MySQL, and Microsoft Access.

->RDBMS is a Database Management System.
ep

What is Database?

What is a database?

->A database is a collection of information and records that is 
organized so it is often simply accessed, managed and updated.
                                                or
->Database is nothing however associate organized sort of knowledge for straightforward access, storing, retrieval and managing of data

Feature of SQL Server?

features of SQL server 

There are the following reasons to use SQL.

->SQL can execute queries against database.

->SQL can retrieve data from the database.

->SQL can insert records in the database.

->SQL can update records in the database.

->SQL can delete records in the database.

->SQL can create a new database.

->SQL can create a new table in a database.

->SQL can create Stored procedur e in a database

->SQL can create View in a database

->SQL can insert records in a database.

what is sql server?

what is SQL server:-

->The full sort of SQL is the Structured Query Language.

->It pronounced as "S-Q-L" or some individuals says "See-Quel".

->SQL is used to communicate with a database.

->It is used for storing, manipulating, and reverting data from the database.

->SQL is used for SELECT, INSERT, UPDATE, DELETE the records from the database.

->SQL is a language for database just like C, C++, C#, and Java are languages for general-purpose programming.