Rules to create SQL Sub Queries

Rules to create SQL Sub Queries

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

• Subqueries must be enclosed within parentheses.
• A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
• The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.
• Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
• The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
• A subquery cannot be immediately enclosed in a set function.
• An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery. 

To Know More On Subquery Please check 

http://mysqlfacts.blogspot.in/2016/01/what-is-sub-queries-in-sql-server.html

SQL HAVING CLAUSE

The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax: 

SELECT ...

FROM ...

WHERE ...

GROUP BY ...

HAVING ...

ORDER BY ...

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Configuring session state on SQL server

There are 2 steps for setting up the ASPState database and the formation setting in your web.config.

you need to use ASP_RegSQl.exe from a command prompt. The script file and the aspregsql EXE are found at

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727.

Formation Steps:

1     Open a command prompt and locate the following path:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 based on your OS version and .NET version

      Use the following statement:

a.       Using default ASPState database and SQL security

aspnet_regsql -S serverName -U UserName -P Password -ssadd -sstype p

b.       Using default ASPState database and windows security

aspnet_regsql -S serverName -E -ssadd -sstype p

c.       Using custom database and SQL security

aspnet_regsql -d TableName -S serverName -U UserName -P Password -ssadd -sstype c

         1) t - Stores session data in the SQL Server tempdb database. This is the default. If you store session data in the tempdb database, the session data is lost if SQL Server is restarted.

         2)   p - Stores session data in the ASPState database instead of in the tempdb database.

         3)  c - Stores session data in a custom database. If you specify the c option, you must also include the name of the custom database using the -d option.

     In your configuration file [web.config]:

       A) Using default SQL security:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring="Data Source=Server;User ID=sa;Password=sa;"

cookieless="false">

      B) Using default windows security:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring=" Data Source=Server;Integrated-Security=SSPI;”

cookieless="false">

      C) Custom database name:

<sessionstate mode="SQLServer" timeout="60" allowcustomsqldatabase="true"

sqlconnectionstring=" Data Source=Server; Initial Catalog = tblname; User ID=sa;Password=sa;”

cookieless="false">

NOTE: Things to lookout for when using SQL to store the session info in a web form situation:

    a)  The machine key among the servers' needs to be the same as AspState Session info is encrypted using the machine key.

    b) The application path to your websites on all machines needs to be constant as well.

10 things you CAN NOT do in SQL Server.

There are few things you cannot do in SQL server. Surprised?? Try some of following points. Please add your comments to the blog after trying.

1. You cannot add data and log files in master and model database.
2. You cannot add User Defined File Groups in master, model and tempdb database.
3. You cannot change the recovery model of tempdb database.
4. You cannot take transaction log backups of master database even if the recovery model of master database is changed to Full.
5. You cannot take any backup of tempdb database.
6. You cannot rename any system database.
7. You cannot allocate Transaction log files under File Groups
8. You cannot change the file group of a file once it is set.
9. You cannot create a new database if model database is in use
10. You cannot change the owner of master, model, tempdb or distribution databas

SQL SERVER: commonly used command

SQL SERVER: commonly used command

 Commands to create, modify, and extract data from databases. The following table lists several of the most commonly-used command.

Command	Use To
CREATE DATABASE	Create a new database. CREATE DATABASE EmpMaster;
USE	Specify the database to work in. USE EmpMaster;
SHOW TABLES	Displays a list of tables in a database. SHOW TABLES EmpMaster;
CREATE TABLE	Add a table to the database and create the attributes for records to be added to the table. CREATE TABLE EmpDetails...
INSERT INTO	Add records to the table. A value must be specified for each attribute in the table. To leave an attribute blank, place two commas together. INSERT INTO EmpDetails VALUES ...
DESCRIBE	See a description of a database object. DESCRIBE EmpDetails;
SELECT	Retrieve information from a table. Clauses include: SELECT Name,Address, Salery from EmpDetails...
	WHERE filters using data from a specified field.
	SORT BY sorts displayed data based on an attribute name.
	GROUP BYdetermines how information in a list is grouped.
UPDATE	Change the values in a record. UPDATE EmpDetails Set Name='Anil' where EmpId=15;
DELETE FROM	Remove records from a table. DELETE FROM  EmpDetails WHERE EmpId=15;
ALTER TABLE	Add, change or remove attributes from tables. ALTER TABLE EmpDetails  ADD COLUMN PostCode;
DROP TABLE	Delete an existing table. DROP TABLE EmpDetails;