Software Applications Programming Errors and solutions.

Structured query language The common commands and methods used in it are grouped into the following categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Logical Connectors, Comparison operators, Aggregate functions, Joins, Sub query, Relational operators, Where clause etc. Data definition language covers SQL commands that create and destroy database tables. Data manipulation language covers SQL commands that create, change or destroy the information in a database table or the structure of the table. Data control language covers protection to the database such as granting access to the tables. Comparison operators cover those operators that allow for comparison between two items. Aggregate function deals with those standard SQL functions that allow programmers to narrow the search of a database. Joins category covers those items that allow information from two or more tables to be related. SQL Query Processing

Difference between primary key and unique key A table can have only one primary key but multiple unique keys. unique key may have null value which is not allowed in primary. simply primary key is unique + not null where as unique key Field contains any number of NULL values. Find primary key, index_name ,index key  using sp_Help command in sql How to find primary key, index_name and index_key in the same query For a particular table and also want to group by index_name use sysindexes table to get the output you want use the stored procedure sp_helpindex [object name] sp_help 'table name' Null and Not Null value in Primary and unique keys in sql Primary key should contain unique value and not null because if attribute value is null than how should maintain the uniqueness.. one more think i want to add that Primary key is a type of constraint and foreign key is also a type of constraint and pk is null then u can't use the null value as a foreign key. S

Summary: re-indexes all the tables using DBCC BREINDEX command. This goes and re-indexes all the tables and takes about 7 minutes and make an improvement of 10% Details: USE DbName_1 --Enter the name of the database you want to reindex totally DECLARE @TableName varchar(255)  DECLARE TableCursor CURSOR FOR  SELECT YourTableName FROM information_schema.tables  WHERE table_type = 'base table'  OPEN TableCursor  FETCH NEXT FROM TableCursor INTO @TableName  WHILE @@FETCH_STATUS = 0  BEGIN  DBCC DBREINDEX(@TableName,' ',100)  FETCH NEXT FROM TableCursor INTO @TableName  END  CLOSE TableCursor  DEALLOCATE TableCursor ReIndexing All Database Tables at once in SQL Database Server This is a simple three line command for Re-indexing All Database Tables at once in SQL Database Server. This command is very helpful to improve your database performance by re indexing tables. Its Update Statistics on Tables. USE YouDatabaseName GO EXEC sp_MSforeachtable @co

Example of self join query in sql. Summary : If you are looking for information like self join query mean table used self as join let us see following example to get more clear. Hear I used  right join with self join. Example of self join query in sql,right join,self join,join query itself. select * from emp where empno not in (select e.empno from emp e right join dept d on e.empno = d.empno) How to use self join in sql select query Example of self join: To find the IDs which have a different department: select distinct A.id  from myTable A join myTable B on A.id = B.id where A.dept <> B.dept Example of join select query in sql Query to get a list of all students who have a certain checklist item (MFST) with a status of Initiated where all other checklist items have a status of Completed. Should I be using Exists or should I be using someting else? SELECT A.COMMON_ID,         B.AID_YEAR,         C.CHKLST_ITEM_CD,         C.ITEM_STATUS FROM PS_PERSON

You can write following way to Search string in stored procedures of SQL. Syscomments and sysobject are sql system table. Sysobject table stored all object of sql like stored procedure,table name etc. declare @SearchStr      varchar(100) set @searchstr = 'String to search for' SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name' FROM  syscomments c       INNER JOIN       sysobjects o       ON c.id = o.id WHERE c.text LIKE '%' + @SearchStr + '%'  and       encrypted = 0 I hope you will able to understand this SP. Sending mail through SQL Stored proceduare You can write following sql script in stored procedure to Send mail through SQL Stored procedure. @SenderNm varchar(100), @Senderemail varchar(100), @RecipientNm varchar(100), @Recipientemail varchar(100), @Subject varchar(200), @Body varchar(8000)AS SET nocount on declare @oMail int --Object reference declare @resultcode int EXEC @resultcode = s

Restore database through query in sql Just try to execute following query to restore database using query analyzer or from network location. RESTORE DATABASE testdb FROM DISK = 'C:\folder1\testdb.bak' WITH REPLACE, MOVE 'testdb_Data' TO 'C:\folder1\testdb.mdf', MOVE 'testdb_Log' TO 'C:\folder1\testdb.LDF' If you want to restore database by code then you can try following SQL Code to restore database . Use master go RESTORE DATABASE [SID] FROM disk = N'\\SERVERNAME\SFILE.bak' WITH FILE = 1, NORECOVERY, REPLACE, NOUNLOAD, STATS = 10 go RESTORE LOG [SID] FROM DISK = N'\\SERVERNAME\SFILE.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO sp_change_users_login @Action='Report' Attach mdf data base to new database in sql To Attach mdf data base to new database in sql You can follow following steps. 1 Go to enterprise manager, expand tree till Database 2 Right click on

Query to delete duplicate records from table using sql select * from test where exists (select * from test as t where test.id=t.id having count(*)>1 and min(t.id) !=test.id.); How to Remove Duplicates from a Single Column SQL Table To Remove Duplicates from a Single Column Table in sql you can use temparary table concept and can try this way lock tables Table1 write; drop temporary table if exists TestTmp; begin work; create temporary table TestTmp select distinct * from Table1; delete from Table1; insert into Table1 select * from TestTmp; unlock tables; commit; delete all the duplicate rows in the table of SQL database If the table is not having an unique column you can use the below: DELETE FROM _Table1 WHERE _Table1.ID IN -- List 1 - all rows that have duplicates (SELECT F.ID FROM _Table1 AS F WHERE Exists (SELECT Field1, Field2, Count(ID) FROM _Table1 WHERE _Table1.Field1 = F.Field1 AND _Table1.Field2 = F.Fi

Set Auto Increment of column which is varchar type in sql If you want to Set Auto Increment of column which is varchar type in sql table then you can do that by using following way CREATE TABLE StudentTable ( Regid int IDENTITY(1,1) NOT NULL , StudentCol nvarchar(3) DEFAULT 'STD' , StudentId AS (StudentCol+ REPLICATE('0', 10-LEN(CONVERT(nvarchar(100),Regid )) ,OtherColumnsYouNeed nvarchar(MAX) )

Example of To_Char function in sql By following example we could know how to use to_char function in sql select query. To get number of student who registered on particular date. Select Stdname ,to_char(regdate ,'day') day from student where to_char(regdate ,'day')='saturday' or to_char(regdate ,'day')='monday';

This is a Example of dynamic SQL. You can wrtie following code to check how dynamic sql works. column1 := 'custno'; column2 := 'custname'; t_table1 := 'cust_test'; t_table2 := 'cust'; testval1 cust.custno%type; t_valeu2 cust.custname%type; t_resultestval1 cust.custno%type; t_resultestval2 cust.custname%type; execsqlcodetest := 'select '||column1||','||column2||' from '||table2||' where ('||column1||','||column2||') in (select '||column1||','||column2||' from '||table1|| ' where '||column1||' like %:b1% and '||column2||' like %:b2%)'; execute immediate execsqlcodetest using testval1, testval2 into t_resultestval1, t_resultestval2;

 convert date in 'MM/DD/YYYY HH:MI A.M.' format using MS sql select CONVERT(nvarchar(30), GETDATE(), 101) + ' ' + CONVERT(nvarchar(30), GETDATE(), 114) format sequence 101 = mm/dd/yyyy 108 = hh:mi:ss 114 = hh:mi:ss:mmm(24h)

SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- Database size. Following code you can write in stored procedure or you can directly run on query browser to get all table size in selected database. EXEC sp_spaceused-- Table row counts and sizes. CREATE TABLE #test ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *FROM #test-- # of rows. SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #test DROP TABLE #test I hope this code will help you a lot on database administration.

SQL Injection Attack  It is a basically a trick to inject SQL command or query as a input mainly in the form of the POST or GET method in the web pages. Most of the websites takes parameter from the form and make SQL query to the database. For a example, in a product detail page of php, it basically takes a parameter product_id from a GET method and get the detail from database using SQL query. With SQL injection attack, a intruder can send a crafted SQL query from the URL of the product detail page and that could possibly do lots of damage to the database. And even in worse scenario, it could even drop the database table as well.e SQL injection is a technique often used to attack a website. This is done by including portions of SQL statements in a web form entry field .

How to Delete duplicate enteries in your table  If there are lot of records which are duplicate and you want to delete then you can use this query. Lets EmpId is column to determine duplicate then use this query. delete from table where rowid not in(select min(rowid) from table group by EmpId); I hope it will help you.

A very simple difference between these two is A Procedure is used to perform an action! A function is used to compute something and then return it to the calling environment. The question is about the out argument. Both have certain usages: Like if you want that the returned value should be returned in an expression: a := b * tax(c); Tax must be a function with a value returning , you cannot use out parameter in such context, moreover when u want your function to return single value in your select statement you cannot use out parameter. On the other hand if you want to return multiple values the function returning value will not be sufficient because a function can return one and only one value.

Difference between  alias and synonym in sql Both are different in all manner not only syntactically Synonym is 1. It is a database object 2. It may be Public or private 3. It is permanent until dropped 4. create [public] synonyms syn_name for [Existed database object]; like create synonym emp1  for hr.employees; Alias is 1. It is not a database object. 2. It will alway private to query 3. It temporary to that query.It to used to provide a name It is generally used to provide a meaning-full heading or column name or table name that is long or inconvenient to type in Query 4. take a example select rownum as rank,e.* from emp1 e; here rank and e is alias where as emp1 is synonym

Insert image using sql create or replace procedure load(filename varchar2) as f_lob bfile; b_lob blob; begin insert into images values(filename,substr(filename,instr(filename,'.')+1),empty_blob()) return content into b_lob; f_lob:=BFILENAME('PAVAN',filename); dbms_lob.fileopen(f_lob,dbms_lob.file_readonly); dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob)); dbms_lob.fileclose(f_lob); commit; end;

Following are Steps to install AdventureWorks database in sql server. 1. Open Object Explorer, if necessary, in SQL Server Management Studio by choosing View ->Object Explorer. 2. Expand the node for the relevant SQL Server 2005 instance and then expand the Databases node. 3. Right-click the AdventureWorks node and choose Tasks➪Copy Database. The Copy Database Wizard runs. 4. Click Next. 5. On the Select a Source Server screen, specify the location of the server and the authentication method to use. Click Next. 6. Select a destination server and click Next. 7. On the Select a Transfer Method screen, select a method to use to copy the database. (Because this is only sample data, you can use the faster detach and attach method.) Click Next. 8. On the Select Databases screen, enable the check box for the AdventureWorks database. Click Next. 9. On the Configure Destination Database screen, name the copy AdventureWorks_new. Click Next, click Next, and click Finish.

SQL Server Data Types Numeric data types: Can be anything from very small to extremely large numbers. Also included are specific formats such as monetary amounts, float point numbers, numbers with a known number of decimal points, whole numbers, and so on. Date and time data types: Allows the direct input of date and time values. This is usually in a default format, such as mm/dd/yyyy hh:mm, or something similar. String data types: Can be fixed length strings, variable length strings, or even very large text objects. Text objects may or may not be stored in binary format. Strings normally accept alphanumeric data. Alphanumeric characters are letters, numbers, and special characters (anything on your keyboard that is not a letter or a number). Binary data types: Typically used to store large objects, including images, sound files, video, even very large textual values like documents. SQL Server 2005 allows storage of similarly related Microsoft product types, such as directl

Master The master database contains system tables that define the behavior of your SQL Server 2005 system. For example, each database that you add is recorded in a system table, and you can access information about those databases by executing a SELECT statement on the sys.databases catalog view. Similarly, information about all system stored procedures is stored in the master database. model Each time you create a new database, the model database is used as the template for the database you’re creating. It’s possible, if you’re planning to create multiple databases with the same customizations, to make those customizations once in the model database. msdb SQL Agent uses the msdb database to store information about tasks. tempdb The tempdb database is the scratch pad for the SQL Server system. A new tempdb database is created each time you start SQL Server 2005. Similarly, the tempdb database is discarded when you shut SQL Server down, so if you use the tempdb database to sto