The RDBMS is basis of SQL which is used to access data from the databases. Often when we are writing program for an application we come across the DBMS i.e. database management system which follows a relational model. It is also known as RDBMS relational database management system. These RDBMS contains languages like SQL Server, Oracle, etc.

So here in this part we are going to discuss about a little basics of SQL Server and what are the different types of query’s, joins, clauses used for accessing the data from the database.

SQL:

SQL is a Structured Query Language. It is used to access and manipulate the databases. SQL Server is an RDBMS database program. Now this SQL can perform many things such as:

  • It can execute queries against database.
  • It can retrieve data from database.
  • It can insert new records into the database.
  • It can update records in a database.
  • It can delete records in a database.
  • It can create new databases.
  • It can create new tables in database.

Now we will see some of the concepts which helps us in accessing database.

Table:

A table is a collection of related data entries and consist rows & columns.

Select:

A Select is a command which extracts or selects data from database. The result is stored in the Result Table. For example,

Select EmployeeNameFromEmployeetable;

Select *:

This select command selects all the data from the database. For example,

Select * From Employeetable;

Distinct:

This distinct keyword helps us to select distinct values from the database. For example,

Select Distinct EmployeeNameFromEmployeetable;

Inner Join:

The inner join keyword helps us to select all the matching data from both the tables. For example,

Select * From Employeetable inner join Departmenttable

ON Employeetable = Departmenttable;

Also another example,

Select EmployeeName, City FromEmployeetable inner join Departmenttable

ONEmployeetable.EmployeeName, Employeetable.City =

Departmenttable.EmployeeName, Departmenttable.City;

Left Join:

The Left Join keyword helps us to select all the data from the left table and select only matching data from the right table. For example,

Select EmployeeNameFromEmployeetable left join Departmenttable

ON Employeetable.EmployeeName = Departmenttable.EmployeeName;

Also another example,

Select * From Employeetable left join Departmenttable

ON Employeetable = Departmenttable;

Right Join:

The right join keyword helps us to select all the data from the right table and select only matching data from the left table. For example,

Select EmployeeAddressFromEmployeetable right join Departmenttable

ON Employeetable.EmployeeAddress = Departmenttable.EmployeeAddress;

Also another example,

Select * From Employeetable right join Departmenttable

ON Employeetable = Departmenttable;

Union:

The union keyword or command helps us to combine/select the data from two or more tables or two or more select statements. It only selects distinct data from both tables. For example,

Select City FromEmployeetable

Union

Select City FromDepartmenttable;

Union All:

This union all keyword or command does the same function of combining/selecting data from two select statements or two tables but the difference is it selects all the data. For example,

Select City FromEmployeetable

Union all

Select City FromDepartmenttable;

Where:

The WHERE clause is used to filter the records or the data. For example,

Select * From Employeetable where EmployeeName = ‘Sam’;

(all the non-numerical data should be put in Single inverted comma)

Select * From Employeetable where EmployeeID = 7;

(all the numerical data can be written directly)

Like:

The LIKE operator is used to search for a specific pattern in a column or we can say that it is used in WHERE clause to search for specific pattern in a column. For example,

Select * From Employeetable where EmployeeName like ‘%a’;

(it will search for pattern where employeename ends with letter ‘a’)

Select * From Employeetable where EmployeeName like ‘v%’;

(it will search for pattern where employeename starts with letter ‘v’)

Select * From Employeetable where EmployeeName like ‘v%a’;

(it will search for pattern where employeename starts with letter ‘v’ and ends with letter ‘a’)

OrderBy:

The OrderBy keyword is used to sort the record or data by one or more columns. It sorts the records or data in ascending order by default or we can use ASC keyword. But if we want to sort the record or data in descending order then we have to use DESC keyword. For example,

Select * From Employeetable Order ByEmployeeID;

Select * From Employeetable Order ByEmployeeID DESC;

Also another example,

Select * From Employeetable Order ByEmployeeID, EmployeeName;

Select * From Employeetable Order ByEmployeeID ASC, EmployeeName DESC;

(here ASC means sorting in Ascending order & DESC means sorting in Descending order)

Group By:

The Group By statement is used to group the result or data by one or more column. For Example,

Select EmployeeSalaryFromEmployeetable

Where EmployeeSalary = 20,000

Group ByEmployeeSalary;

Having:

The HAVING clause is used to select the records or data with aggregate function like count, Avg, max, min, etc. For example,

Select EmployeeSalary, Min(EmployeeSalary) From Employeetable

Group ByEmployeeSalary

Having Min(EmployeeSalary) = 15,000;

 Aliases:

Aliases are used to give temporary name to the database table or a column in a table. They are used to make Database table name or column name more readable. For example,

Select EmployeeName as EmpNameFromEmployeetable;

(readable/alias column name)

Select EmployeeNameFromEmployeetable as Emptbl;

(readable/alias database table name)

Advertisements