SQL Wildcards

SQL Wildcards

SQL wildcards are used for searching one or more characters from table in a database. The SQL wildcards substitute one or more character, whenever there is a search for data in a table of the database. The Wildcards is must be used with LIKE operator. 

  There are many wildcards are used in SQL database, Here in which some important wildcards are given below:

1.     ‘%’ wildcards:

     ‘%’ allows you to match any string of any length that means a substitute for zero or more characters.

Example:    

Now we want to select the User whose name that starts with "ar" from the "userlogininfo" table.

 Select Query:    select username from userlogininfo where UserName like 'ar%' Output:

ArunKumar

Arunsingh

2.     ‘_’ wildcards:

‘_ ‘allows you to match on a single character that means ‘_’is looking for only one character.

 Example: 

 Now we want to select the user with a first name that starts with any character, followed by "runsingh" from the "userlogininfo" table.

 Select Query:  select username from userlogininfo where UserName like _runsingh'

Output:

                Arunsingh

3.     [charlist] wildcards:

[charlist] wildcards are used for any single character in charlist.

   Example:

Now we want to select the user with a name that starts with "a" or "v" from the "userlogininfo" table.

  Select Query:  select username from userlogininfo where UserName like '[av]%'

Output:

                ArunKumar

Arunsingh

Varunsingh

Aggregate function in SQL Server

Aggregate function in SQL Server

SQL aggregate functions are used to sum, count, get the average, get the minimum and get the maximum values from a column or from a sub-set of column values. SQL aggregate functions return a single value, calculated from values in a column.

Kinds of Aggregate function:

·         AVG() - Returns the average value

·         COUNT() - Returns the number of rows

·         MAX() - Returns the largest value

·         MIN() - Returns the smallest value

·         SUM() - Returns the sum

In this article illustrates an example on 'SQL Aggregate Queries'. To understand example we create a table 'Student ' with required field names and datatypes.

Table create query

create table Student

(

       Id varchar(4),

       Name varchar(15),

       Class  varchar(10),

       Subject_Id varchar(2),

       Marks int

);

Student

AVG ( )

The SQL  AVG () function is used to find average, in above table if we want to find average of student marks we can find easily average of students marks  with the help of AVG () function as shown below:

Query

select id, avg(Marks)as 'Average' from Student group by (Id)

Result

In above screen shot you can see average is showing of each student.

COUNT ( )

The SQL COUNT () function returns the number of rows in a table satisfying the criteria. If we want to count how many student records available in table we will use the following SQL COUNT expression:

Query

select Id, count(id)as 'Total Paper' from Student group by (Id)

Result

In above screen shot you can see each student attempted how many papers.

MAX ( )

The SQL MAX () function is used to return maximum record values from table.

Query

Select id,Max(Marks)as'Maximum Numbers'  from Student group by(Id)

Result

In above screen shot you can see each student maximum numbers.

MIN ( )

The SQL MAX () function is used to return minimum record values from table.

Query

Select id,MIN(Marks)as'Minimum Numbers' from Student group by(Id)

In above screen shot you can see each student minimum numbers.

SUM ( )

The SUM () function return the sum of marks records as total marks from 'Student' table with the specified criteria in group by clause.

Query

Select id,sum(Marks)as 'Total Marks' from Student group by(Id)

Result

In above screen shot you can see each student total marks.

Working with Views in Database

Working with Views in Database

Whenever you want to provide different types of restriction to different user such as different-2 users can see different records in same table then we can use the concept of views. Views can help in simplifying query execution when the query involves retrieving data from multiple tables by applying join.

“A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data.”

Views ensure data security by applying certain types of restriction on user table such as

Ø  Specific rows of a table by using where clause.

Ø  Specific columns of a table base on certain condition.

Ø  Rows filled by using joins.

Ø  Subset of another view or a subset of views and tables.

Syntax for creating View

create view view_name as

select_statement

where

     create is a keyword which is used to create a database object

     view is a keyword which indicates that object that is created is a view

                view_name  is any valid name to your view as is a keyword select_statement is a select statement which is valid combination of join, subquery etc.

Some example which demonstrate the use of view

create view studentView as

select sid,sname from student

create view studentView1 as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

Executing view that was early created

1) select * from studentView

2)      select * from studentView1

Creating an index view by using create index statement

By default when we create any view then index is not created on that view. You can create index on the view as well as you create index on table by using create index statement.

Example to demonstrate creating index on view

Before we can create index on view be have to bound view to the schema at the time of creation.

1)      Binding view to the schema by using following statement

alter view studentView1 with schemabinding as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

2)      Creating index on view

create unique clustered index sid_student on studentView1(sid)

Altering Views

When you want to change the structure of the underlying tables such as adding new columns then we use alter commands. You can modify view without affecting its dependent objects.

Syntax for modifying view by using alter command

alter view view_name

as select_statement

Example of altering view

alter view studentView1 with schemabinding as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

Deleting Views from database

The syntax of the drop view statement is:

                drop view view_name

Example which demonstrate use of droping view

drop view studentView1

Renaming Views

At times for security permission we need to rename an existing view. For renaming an existing view use sp_rename system stored procedure.

Syntax for renaming views

sp_rename old_view_name, new_view_name

where

     old_view_name represents name of the old view that you want to rename

     new_view_name represents name of new view that you want

Example of renaming views

sp_rename studentView1, stdView1

While renaming view mention following things

Ø  The view should be in current database

Ø  The new name for the view must follow the rules for identifier

Ø  No any other view stored in the database with new name

Ø  The view can only be renamed by its owner

Whenever you want to provide different types of restriction to different user such as different-2 users can see different records in same table then we can use the concept of views. Views can help in simplifying query execution when the query involves retrieving data from multiple tables by applying join.

“A view is a virtual table, which provides access to a subset of columns from one or more tables. It is a query stored as an object in the database, which does not have its own data.”

Views ensure data security by applying certain types of restriction on user table such as

Ø  Specific rows of a table by using where clause.

Ø  Specific columns of a table base on certain condition.

Ø  Rows filled by using joins.

Ø  Subset of another view or a subset of views and tables.

Syntax for creating View

create view view_name as

select_statement

where

     create is a keyword which is used to create a database object

     view is a keyword which indicates that object that is created is a view

                view_name  is any valid name to your view as is a keyword select_statement is a select statement which is valid combination of join, subquery etc.

Some example which demonstrate the use of view

create view studentView as

select sid,sname from student

create view studentView1 as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

Executing view that was early created

1) select * from studentView

2)      select * from studentView1

Creating an index view by using create index statement

By default when we create any view then index is not created on that view. You can create index on the view as well as you create index on table by using create index statement.

Example to demonstrate creating index on view

Before we can create index on view be have to bound view to the schema at the time of creation.

1)      Binding view to the schema by using following statement

alter view studentView1 with schemabinding as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

2)      Creating index on view

create unique clustered index sid_student on studentView1(sid)

Altering Views

When you want to change the structure of the underlying tables such as adding new columns then we use alter commands. You can modify view without affecting its dependent objects.

Syntax for modifying view by using alter command

alter view view_name

as select_statement

Example of altering view

alter view studentView1 with schemabinding as

select sid,sname,scity from student

where sid between 'S0001' and 'S0005'

Deleting Views from database

The syntax of the drop view statement is:

                drop view view_name

Example which demonstrate use of droping view

drop view studentView1

Renaming Views

At times for security permission we need to rename an existing view. For renaming an existing view use sp_rename system stored procedure.

Syntax for renaming views

sp_rename old_view_name, new_view_name

where

     old_view_name represents name of the old view that you want to rename

     new_view_name represents name of new view that you want

Example of renaming views

sp_rename studentView1, stdView1

While renaming view mention following things

Ø  The view should be in current database

Ø  The new name for the view must follow the rules for identifier

Ø  No any other view stored in the database with new name

Ø  The view can only be renamed by its owner