Skip to main content

Posts

How To Compute MAX(AVG(Field)) in SQL

Suppose you have the following table You need to find which Department has the maximum average salary in a single query you can easy ly find average value of each department using aggregate function AVG() But how to select only the department having the maximum average salary.In SQL SERVER USE    select top 1 DeptName,MAX(MyAvg) AVARAGE_SALARY FROM(   select AVG(salary) as MyAvg,DeptName  from [SalaryTable] group by Deptname   ) temp   GROUP BY DeptName   order by AVARAGE_SALARY  desc 

HTTP Error 404.17 - Not Found The requested content appears to be script and will not be served by the static file handler.

Register asp.net again....will solve the issue. Go to Visual Studio Command Prompt, And register asp.net as windows\microsoft.net\Framework[.Net version num]\aspnet_regiis.exe -i AND I had this issue with Windows Server 2012 with ASP .NET 4.5 you can't use aspnet_regiis.exe, and just have to install ASP .NET 4.5 via the Add Roles and Features Wizard: and use correct application pool

Loop through all the tables within a database to perform specific task using sp_MSforeachtable in SQL Server 2012 /2008

Beginning There are some situation when we need to perform a specific task allmost every table within a database so we need to loop through all tables in a database.Let we have to truncate those tables having name with a perticular prefix or we want to count rows of each table. this article will give a tips to do this task easily using 'sp_MSforeachtable' Stored Procedure. Details 'sp_MSforeachtable' Stored Procedure is an undocumented procedure takes place in 'master' database Syntax of using 'sp_MSforeachtable' use [database_name] exec sp_MSforeachtable @command @command is the command that has to be applied in each table Example To create a list of all tables with their row count you may create a user defined stored procedure as follows CREATE PROCEDURE [dbo].[usp_RowCountForAllTables] AS BEGIN         SET NOCOUNT ON; DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ; INSERT INTO @TableRowCounts ([TableNa...

Search Entire Database for a value or part of a value in SQL Server using Stored Procedure

In my recent job where I am acting as a Jr. Software Engineer .This company works for Educational institution hence their product EMS(Education Management System) .Once I was asked to find a ID of a student in entire database (which has a at least 300 tables)  and this type of task has to perform frequently .So I decided to write a Stored Procedure And it works fine. Code is Given Below: USE [yourdbname] GO /****** Object: StoredProcedure [dbo].[SearchAllTables] Script Date: 1/21/2015 11:52:21 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @...

FTP(File Transfer Protocol ) configuration and testing in Windows Server 2008 R2

First of all you have to know  what the FTP is " File Transfer Protocol ( FTP ) is a standard network protocol used to transfer files from one host or to another host over a TCP -based network". FTP Installation & Configuration: Step 1: Install the Web Server role with the IIS Management Console and FTP Server role services: Step 2: Add a new FTP Site Step 3: Setup the site with the default bindings and choose Allow SSL to avoid deploying a certificate:     Step 4: Configure user permissions and basic or anonymous permission. If your server is connected to your domain you can specify domain users, otherwise they must be local user accounts: Note: Finally you’ll have to configure your server’s firewall rules to allow access.Disregard any existing FTP firewall rules; although they should be enabled, they don’t actually allow access! Run Allow a Program Through Windows Firewall and grant access to C:\Windows\System32\svchost.exe T...

A little bit about Integrity Constraints (Advance SQL)

I've been asked today by one of my younger brother that " How can we create a table using SQL command in Oracle 10g   such that a column must accept those values that start with a specific character e.g. A. " Then I replied him to use Integrity Constraints . Since he was not familiar about  Integrity Constraints  hence he wanted to get the complete solution of his real problem. His Problem statement was: "Create cust table which contains cno having pk(primary key),cname and occupation where data values inserted for cno must start with the capital letter C and cname should be in uppercase. " Integrity Constraints are those which ensure that all the changes made to the Database by authorized users don't result in a loss of data consistency. A list of Integrity Constraints are given below Primary Key Foreign Key Unique  Check Not Null We will use Check  Integrity Constraint to solve the problem. Syntax: column_name data_type(size) CHECK(l...