Inserting Data Into a SQL Database using VB.NET
VB.NET CONNECTIVITY CODING AND C# CONNECTIVITY CODING FOR INSERTION
Updating Data in a SQL Database
VB.NET CONNECTIVITY CODING AND C# CONNECTIVITY CODING FOR UPDATION
Deleting Data in a SQL Database
VB.NET CONNECTIVITY CODING AND C# CONNECTIVITY CODING FOR DELETION
Accessing Data with ASP.NET
Binding controls of repeater,Data list, Data grid
ASP.NET State Management
ASP.NET State Management Session,View state, Application State
Index for ASP.NET
Index for ASP.NET IN MSDN
19 June 2010
Difference Between ASP.NET Server Controls,HTML Server Controls and HTML Intrinsic Controls
Difference Between ASP.NET Server Controls,HTML Server Controls and HTML Intrinsic Controls
ASP.NET Server Controls
Advantages:
1. ASP .NET Server Controls can however detect the target browser's capabilities and render themselves accordingly. No issues for compatibility issues of Browsers i.e page that might be used by both HTML 3.2 and HTML 4.0 browsers code to be written by you.
2. Newer set of controls that can be used in the same manner as any HTMl control like Calender controls. (No need of Activex Control for doing this which would then bring up issues of Browser compatibility).
3. Processing would be done at the server side. In built functionality to check for few values(with Validation controls) so no need to choose between scripting language which would be incompatible with few browsers.
4. ASP .NET Server Controls have an object model different from the traditional HTML and even provide a set of properties and methods that can change the outlook and behavior of the controls.
5. ASP .NET Server Controls have higher level of abstraction. An output of an ASP .NET server control can be the result of many HTML tags that combine together to produce that control and its events.
Disadvantages:
1. The control of the code is inbuilt with the web server controls so you have no much of direct control on these controls
2. Migration of ASP to any ASP.NET application is difficult. Its equivalent to rewriting your new application
HTML Server Controls
Advantages:
1. The HTML Server Controls follow the HTML-centric object model. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting. Processing would be done at client as well as server depending on your code.
3. Migration of the ASP project thought not very easy can be done by giving each intrinsic HTML control a runat = server to make it HTML Server side control.
4. The HTML Server Controls have no mechanism of identifying the capabilities of the client browser accessing the current page.
5. A HTML Server Control has similar abstraction with its corresponding HTML tag and offers no abstraction.
Disadvantages:
1. You would need to code for the browser compatibility.
HTML Intrinsic Controls
Advantages:
1. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting
Disadvantages:
1. You would need to code for the browser compatibility
ASP.NET Server Controls
Advantages:
1. ASP .NET Server Controls can however detect the target browser's capabilities and render themselves accordingly. No issues for compatibility issues of Browsers i.e page that might be used by both HTML 3.2 and HTML 4.0 browsers code to be written by you.
2. Newer set of controls that can be used in the same manner as any HTMl control like Calender controls. (No need of Activex Control for doing this which would then bring up issues of Browser compatibility).
3. Processing would be done at the server side. In built functionality to check for few values(with Validation controls) so no need to choose between scripting language which would be incompatible with few browsers.
4. ASP .NET Server Controls have an object model different from the traditional HTML and even provide a set of properties and methods that can change the outlook and behavior of the controls.
5. ASP .NET Server Controls have higher level of abstraction. An output of an ASP .NET server control can be the result of many HTML tags that combine together to produce that control and its events.
Disadvantages:
1. The control of the code is inbuilt with the web server controls so you have no much of direct control on these controls
2. Migration of ASP to any ASP.NET application is difficult. Its equivalent to rewriting your new application
HTML Server Controls
Advantages:
1. The HTML Server Controls follow the HTML-centric object model. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting. Processing would be done at client as well as server depending on your code.
3. Migration of the ASP project thought not very easy can be done by giving each intrinsic HTML control a runat = server to make it HTML Server side control.
4. The HTML Server Controls have no mechanism of identifying the capabilities of the client browser accessing the current page.
5. A HTML Server Control has similar abstraction with its corresponding HTML tag and offers no abstraction.
Disadvantages:
1. You would need to code for the browser compatibility.
HTML Intrinsic Controls
Advantages:
1. Model similar to HTML
2. Here the controls can be made to interact with Client side scripting
Disadvantages:
1. You would need to code for the browser compatibility
Dotnet faq
Dotnet faq
DataReaders
In ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.
Supporting Multiple Resultsets
The DataReader supports access to multiple resultsets, one at a time, in the order they are retrieved.
DataAdapters
A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.
DataSet
The DataSet, which is an in-memory cache of data retrieved from a data source, is a major component of the ADO.NET architecture. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects.
Stored procedure default return value
A stored procedure may return a single integer value, but it can return many output parameters. When a stored procedure encounters a RETURN statement, it exits immediately to the calling code. The default return value is 0,
Non zero return value usually represents failure of some sorts
Why would you want to use serialization?
The two most important reasons are to persist the state of an object to a storage medium so an exact copy can be recreated at a later stage, and to send the object by value from one application domain to another. For example, serialization is used to save session state in ASP.NET and to copy objects to the clipboard in Windows Forms. It is also used by remoting to pass objects by value from one application domain to another.
DataReaders
In ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, and (by default) storing only one row at a time in memory, reducing system overhead.
Supporting Multiple Resultsets
The DataReader supports access to multiple resultsets, one at a time, in the order they are retrieved.
DataAdapters
A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.
DataSet
The DataSet, which is an in-memory cache of data retrieved from a data source, is a major component of the ADO.NET architecture. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects.
Stored procedure default return value
A stored procedure may return a single integer value, but it can return many output parameters. When a stored procedure encounters a RETURN statement, it exits immediately to the calling code. The default return value is 0,
Non zero return value usually represents failure of some sorts
Why would you want to use serialization?
The two most important reasons are to persist the state of an object to a storage medium so an exact copy can be recreated at a later stage, and to send the object by value from one application domain to another. For example, serialization is used to save session state in ASP.NET and to copy objects to the clipboard in Windows Forms. It is also used by remoting to pass objects by value from one application domain to another.
SQL SERVER FAQ AND QUERIES
SQL SERVER FAQ AND QUERIES
1 a.What is normalization? -
Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
1.b What is a Stored Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
2.What is a trigger?
- Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
3.What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
4.What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
5.What is the basic difference between clustered and a non-clustered index?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
6.What are cursors?
Well cursors help us to do an operation on a set of data that we retrieve by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retrieval one by one and remove rows which have duplicate values.
7.Can you tell me the difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
8.Can we use Truncate command on a table which is referenced by FOREIGN KEY? -
No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
9.What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
10.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
11.What is a Join in SQL Server? -
Join actually puts data from two or more tables into a single result set.
12.Can you explain the types of Joins that we can have with Sql Server? - There are three types of joins: Inner Join, Outer Join, Cross Join
13.What are the authentication modes in SQL Server? - Windows mode and mixed mode (SQL & Windows).
14.What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.
15.What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
16.Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table.
If the table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called
composite key.
17.What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction.
18.What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers.
19.What are triggers?
How many triggers you can have on a table? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place
on a table.
1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
2. What operator performs pattern matching?
LIKE operator
3. What operator tests column for the absence of data?
IS NULL operator
4. Which command executes the contents of a specified file?
START or @
5. What is the parameter substitution symbol used with INSERT INTO command?
&
6. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
7. What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
True
10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all
11. What command is used to get back the privileges offered by the GRANT command?
REVOKE
12. Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
13. Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
15. What is the difference between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
16. What command is used to create a table by copying the structure of another table?
Answer :
CREATE TABLE .. AS SELECT command
Explanation :
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
TROUBLETHETROUBLE
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
Answer :
NO
Explanation :
The query checks whether a given string is a numerical digit.
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
20. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
21. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
22. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
24. What is the value of ‘comm’ and ‘sal’ after executing the following query if the initial value of ‘sal’ is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 1000
25. What is the use of DESC in SQL?
Answer :
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
26. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
27. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
28. What is the output of the following query?
SELECT TRUNC(1234.5678,-2) FROM DUAL;
1200
SQL – QUERIES
I. SCHEMAS
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)
LEGEND :
PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1
QUERIES :
1. Find out the selling cost average for packages developed in Oracle.
2. Display the names, ages and experience of all programmers.
3. Display the names of those who have done the PGDCA course.
4. What is the highest number of copies sold by a package?
5. Display the names and date of birth of all programmers born in April.
6. Display the lowest course fee.
7. How many programmers have done the DCA course.
8. How much revenue has been earned through the sale of packages developed in C.
9. Display the details of software developed by Rakesh.
10. How many programmers studied at Pentafour.
11. Display the details of packages whose sales crossed the 5000 mark.
12. Find out the number of copies which should be sold in order to recover the development cost of each package.
13. Display the details of packages for which the development cost has been recovered.
14. What is the price of costliest software developed in VB?
15. How many packages were developed in Oracle ?
16. How many programmers studied at PRAGATHI?
17. How many programmers paid 10000 to 15000 for the course?
18. What is the average course fee?
19. Display the details of programmers knowing C.
20. How many programmers know either C or Pascal?
21. How many programmers don’t know C and C++?
22. How old is the oldest male programmer?
23. What is the average age of female programmers?
24. Calculate the experience in years for each programmer and display along with their names in descending order.
25. Who are the programmers who celebrate their birthdays during the current month?
26. How many female programmers are there?
27. What are the languages known by the male programmers?
28. What is the average salary?
29. How many people draw 5000 to 7500?
30. Display the details of those who don’t know C, C++ or Pascal.
31. Display the costliest package developed by each programmer.
32. Produce the following output for all the male programmers
KEYS:
1. SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
2. SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) "AGE", TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) "EXPERIENCE" FROM PROGRAMMER;
3. SELECT PNAME FROM STUDIES WHERE COURSE = 'PGDCA';
4. SELECT MAX(SOLD) FROM SOFTWARE;
5. SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE '%APR%';
6. SELECT MIN(CCOST) FROM STUDIES;
7. SELECT COUNT(*) FROM STUDIES WHERE COURSE = 'DCA';
8. SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'C';
9. SELECT * FROM SOFTWARE WHERE PNAME = 'RAKESH';
10. SELECT * FROM STUDIES WHERE SPLACE = 'PENTAFOUR';
11. SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;
12. SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;
13. SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;
14. SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'VB';
15. SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
16. SELECT COUNT(*) FROM STUDIES WHERE SPLACE = 'PRAGATHI';
17. SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;
18. SELECT AVG(CCOST) FROM STUDIES;
19. SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';
20. SELECT * FROM PROGRAMMER WHERE PROF1 IN ('C','PASCAL') OR PROF2 IN ('C','PASCAL');
21. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++') AND PROF2 NOT IN ('C','C++');
22. SELECT TRUNC(MAX(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'M';
23. SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'F';
24. SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMER ORDER BY PNAME DESC;
25. SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') = TO_CHAR(SYSDATE,'MON');
26. SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = 'F';
27. SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = 'M';
28. SELECT AVG(SAL) FROM PROGRAMMER;
29. SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;
30. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++','PASCAL') AND PROF2 NOT IN ('C','C++','PASCAL');
31. SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOST) FROM SOFTWARE GROUP BY PNAME);
32.SELECT 'Mr.' || PNAME || ' - has ' || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'M' UNION SELECT 'Ms.' || PNAME || ' - has ' || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'F';
II . SCHEMA :
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
1. List all the employees who have at least one person reporting to them.
2. List the employee details if and only if more than 10 employees are present in department no 10.
3. List the name of the employees with their immediate higher authority.
4. List all the employees who do not manage any one.
5. List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.
6. List the details of the employee earning more than the highest paid manager.
7. List the highest salary paid for each job.
8. Find the most recently hired employee in each department.
9. In which year did most people join the company? Display the year and the number of employees.
10. Which department has the highest annual remuneration bill?
11. Write a query to display a ‘*’ against the row of the most recently hired employee.
12. Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
13. Find the nth maximum salary.
14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
15. Write a query to list the length of service of the employees (of the form n years and m months).
KEYS:
1. SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR; or SELECT ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);
2. SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);
3. SELECT A.ENAME "EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
4. SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
5. SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
6. SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'MANAGER' );
7. SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
8. SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
9. SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES" FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY') HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'));
10. SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) "COMPENSATION" FROM EMP GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);
11. SELECT ENAME, HIREDATE, LPAD('*',8) "RECENTLY HIRED" FROM EMP WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(' ',15) "RECENTLY HIRED" FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);
12. SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
13. SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
14. SELECT * FROM EMP A WHERE A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND A.ROWID!=MIN (ROWID));
15. SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE" FROM EMP;
SQL SERVER FAQ AND QUERIES http://royalarun.blogspot.com/
1 a.What is normalization? -
Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
1.b What is a Stored Procedure?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
2.What is a trigger?
- Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
3.What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
4.What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
5.What is the basic difference between clustered and a non-clustered index?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
6.What are cursors?
Well cursors help us to do an operation on a set of data that we retrieve by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retrieval one by one and remove rows which have duplicate values.
7.Can you tell me the difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
8.Can we use Truncate command on a table which is referenced by FOREIGN KEY? -
No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
9.What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
10.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
11.What is a Join in SQL Server? -
Join actually puts data from two or more tables into a single result set.
12.Can you explain the types of Joins that we can have with Sql Server? - There are three types of joins: Inner Join, Outer Join, Cross Join
13.What are the authentication modes in SQL Server? - Windows mode and mixed mode (SQL & Windows).
14.What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.
15.What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
16.Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table.
If the table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called
composite key.
17.What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction.
18.What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers.
19.What are triggers?
How many triggers you can have on a table? How to invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place
on a table.
1. Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL)
2. What operator performs pattern matching?
LIKE operator
3. What operator tests column for the absence of data?
IS NULL operator
4. Which command executes the contents of a specified file?
START
5. What is the parameter substitution symbol used with INSERT INTO command?
&
6. Which command displays the SQL command in the SQL buffer, and then executes it?
RUN
7. What are the wildcards used for pattern matching?
_ for single character substitution and % for multi-character substitution
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
True
10. What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all
11. What command is used to get back the privileges offered by the GRANT command?
REVOKE
12. Which system tables contain information on privileges granted and privileges obtained?
USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD
13. Which system table contains information on constraints on all the tables created?
USER_CONSTRAINTS
14. TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ?
Both will result in deleting all the rows in the table EMP.
15. What is the difference between TRUNCATE and DELETE commands?
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.
16. What command is used to create a table by copying the structure of another table?
Answer :
CREATE TABLE .. AS SELECT command
Explanation :
To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
TROUBLETHETROUBLE
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
Answer :
NO
Explanation :
The query checks whether a given string is a numerical digit.
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
20. Which date function is used to find the difference between two dates?
MONTHS_BETWEEN
21. Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
22. What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
23. What is the use of the DROP option in the ALTER TABLE command?
It is used to drop constraints specified on the table.
24. What is the value of ‘comm’ and ‘sal’ after executing the following query if the initial value of ‘sal’ is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 1000
25. What is the use of DESC in SQL?
Answer :
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.
26. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
27. Which function is used to find the largest integer less than or equal to a specific value?
FLOOR
28. What is the output of the following query?
SELECT TRUNC(1234.5678,-2) FROM DUAL;
1200
SQL – QUERIES
I. SCHEMAS
Table 1 : STUDIES
PNAME (VARCHAR), SPLACE (VARCHAR), COURSE (VARCHAR), CCOST (NUMBER)
Table 2 : SOFTWARE
PNAME (VARCHAR), TITLE (VARCHAR), DEVIN (VARCHAR), SCOST (NUMBER), DCOST (NUMBER), SOLD (NUMBER)
Table 3 : PROGRAMMER
PNAME (VARCHAR), DOB (DATE), DOJ (DATE), SEX (CHAR), PROF1 (VARCHAR), PROF2 (VARCHAR), SAL (NUMBER)
LEGEND :
PNAME – Programmer Name, SPLACE – Study Place, CCOST – Course Cost, DEVIN – Developed in, SCOST – Software Cost, DCOST – Development Cost, PROF1 – Proficiency 1
QUERIES :
1. Find out the selling cost average for packages developed in Oracle.
2. Display the names, ages and experience of all programmers.
3. Display the names of those who have done the PGDCA course.
4. What is the highest number of copies sold by a package?
5. Display the names and date of birth of all programmers born in April.
6. Display the lowest course fee.
7. How many programmers have done the DCA course.
8. How much revenue has been earned through the sale of packages developed in C.
9. Display the details of software developed by Rakesh.
10. How many programmers studied at Pentafour.
11. Display the details of packages whose sales crossed the 5000 mark.
12. Find out the number of copies which should be sold in order to recover the development cost of each package.
13. Display the details of packages for which the development cost has been recovered.
14. What is the price of costliest software developed in VB?
15. How many packages were developed in Oracle ?
16. How many programmers studied at PRAGATHI?
17. How many programmers paid 10000 to 15000 for the course?
18. What is the average course fee?
19. Display the details of programmers knowing C.
20. How many programmers know either C or Pascal?
21. How many programmers don’t know C and C++?
22. How old is the oldest male programmer?
23. What is the average age of female programmers?
24. Calculate the experience in years for each programmer and display along with their names in descending order.
25. Who are the programmers who celebrate their birthdays during the current month?
26. How many female programmers are there?
27. What are the languages known by the male programmers?
28. What is the average salary?
29. How many people draw 5000 to 7500?
30. Display the details of those who don’t know C, C++ or Pascal.
31. Display the costliest package developed by each programmer.
32. Produce the following output for all the male programmers
KEYS:
1. SELECT AVG(SCOST) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
2. SELECT PNAME,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) "AGE", TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) "EXPERIENCE" FROM PROGRAMMER;
3. SELECT PNAME FROM STUDIES WHERE COURSE = 'PGDCA';
4. SELECT MAX(SOLD) FROM SOFTWARE;
5. SELECT PNAME, DOB FROM PROGRAMMER WHERE DOB LIKE '%APR%';
6. SELECT MIN(CCOST) FROM STUDIES;
7. SELECT COUNT(*) FROM STUDIES WHERE COURSE = 'DCA';
8. SELECT SUM(SCOST*SOLD-DCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'C';
9. SELECT * FROM SOFTWARE WHERE PNAME = 'RAKESH';
10. SELECT * FROM STUDIES WHERE SPLACE = 'PENTAFOUR';
11. SELECT * FROM SOFTWARE WHERE SCOST*SOLD-DCOST > 5000;
12. SELECT CEIL(DCOST/SCOST) FROM SOFTWARE;
13. SELECT * FROM SOFTWARE WHERE SCOST*SOLD >= DCOST;
14. SELECT MAX(SCOST) FROM SOFTWARE GROUP BY DEVIN HAVING DEVIN = 'VB';
15. SELECT COUNT(*) FROM SOFTWARE WHERE DEVIN = 'ORACLE';
16. SELECT COUNT(*) FROM STUDIES WHERE SPLACE = 'PRAGATHI';
17. SELECT COUNT(*) FROM STUDIES WHERE CCOST BETWEEN 10000 AND 15000;
18. SELECT AVG(CCOST) FROM STUDIES;
19. SELECT * FROM PROGRAMMER WHERE PROF1 = 'C' OR PROF2 = 'C';
20. SELECT * FROM PROGRAMMER WHERE PROF1 IN ('C','PASCAL') OR PROF2 IN ('C','PASCAL');
21. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++') AND PROF2 NOT IN ('C','C++');
22. SELECT TRUNC(MAX(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'M';
23. SELECT TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)) FROM PROGRAMMER WHERE SEX = 'F';
24. SELECT PNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) FROM PROGRAMMER ORDER BY PNAME DESC;
25. SELECT PNAME FROM PROGRAMMER WHERE TO_CHAR(DOB,'MON') = TO_CHAR(SYSDATE,'MON');
26. SELECT COUNT(*) FROM PROGRAMMER WHERE SEX = 'F';
27. SELECT DISTINCT(PROF1) FROM PROGRAMMER WHERE SEX = 'M';
28. SELECT AVG(SAL) FROM PROGRAMMER;
29. SELECT COUNT(*) FROM PROGRAMMER WHERE SAL BETWEEN 5000 AND 7500;
30. SELECT * FROM PROGRAMMER WHERE PROF1 NOT IN ('C','C++','PASCAL') AND PROF2 NOT IN ('C','C++','PASCAL');
31. SELECT PNAME,TITLE,SCOST FROM SOFTWARE WHERE SCOST IN (SELECT MAX(SCOST) FROM SOFTWARE GROUP BY PNAME);
32.SELECT 'Mr.' || PNAME || ' - has ' || TRUNC(MONTHS_BETWEEN(SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'M' UNION SELECT 'Ms.' || PNAME || ' - has ' || TRUNC (MONTHS_BETWEEN (SYSDATE,DOJ)/12) || ' years of experience' “Programmer” FROM PROGRAMMER WHERE SEX = 'F';
II . SCHEMA :
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a foreign key.
QUERIES
1. List all the employees who have at least one person reporting to them.
2. List the employee details if and only if more than 10 employees are present in department no 10.
3. List the name of the employees with their immediate higher authority.
4. List all the employees who do not manage any one.
5. List the employee details whose salary is greater than the lowest salary of an employee belonging to deptno 20.
6. List the details of the employee earning more than the highest paid manager.
7. List the highest salary paid for each job.
8. Find the most recently hired employee in each department.
9. In which year did most people join the company? Display the year and the number of employees.
10. Which department has the highest annual remuneration bill?
11. Write a query to display a ‘*’ against the row of the most recently hired employee.
12. Write a correlated sub-query to list out the employees who earn more than the average salary of their department.
13. Find the nth maximum salary.
14. Select the duplicate records (Records, which are inserted, that already exist) in the EMP table.
15. Write a query to list the length of service of the employees (of the form n years and m months).
KEYS:
1. SELECT DISTINCT(A.ENAME) FROM EMP A, EMP B WHERE A.EMPNO = B.MGR; or SELECT ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP);
2. SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(EMPNO)>10 AND DEPTNO=10);
3. SELECT A.ENAME "EMPLOYEE", B.ENAME "REPORTS TO" FROM EMP A, EMP B WHERE A.MGR=B.EMPNO;
4. SELECT * FROM EMP WHERE EMPNO IN ( SELECT EMPNO FROM EMP MINUS SELECT MGR FROM EMP);
5. SELECT * FROM EMP WHERE SAL > ( SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=20);
6. SELECT * FROM EMP WHERE SAL > ( SELECT MAX(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'MANAGER' );
7. SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;
8. SELECT * FROM EMP WHERE (DEPTNO, HIREDATE) IN (SELECT DEPTNO, MAX(HIREDATE) FROM EMP GROUP BY DEPTNO);
9. SELECT TO_CHAR(HIREDATE,'YYYY') "YEAR", COUNT(EMPNO) "NO. OF EMPLOYEES" FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY') HAVING COUNT(EMPNO) = (SELECT MAX(COUNT(EMPNO)) FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY'));
10. SELECT DEPTNO, LPAD(SUM(12*(SAL+NVL(COMM,0))),15) "COMPENSATION" FROM EMP GROUP BY DEPTNO HAVING SUM( 12*(SAL+NVL(COMM,0))) = (SELECT MAX(SUM(12*(SAL+NVL(COMM,0)))) FROM EMP GROUP BY DEPTNO);
11. SELECT ENAME, HIREDATE, LPAD('*',8) "RECENTLY HIRED" FROM EMP WHERE HIREDATE = (SELECT MAX(HIREDATE) FROM EMP) UNION SELECT ENAME NAME, HIREDATE, LPAD(' ',15) "RECENTLY HIRED" FROM EMP WHERE HIREDATE != (SELECT MAX(HIREDATE) FROM EMP);
12. SELECT ENAME,SAL FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP F WHERE E.DEPTNO = F.DEPTNO);
13. SELECT ENAME, SAL FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT(SAL)) FROM EMP B WHERE A.SAL<=B.SAL);
14. SELECT * FROM EMP A WHERE A.EMPNO IN (SELECT EMPNO FROM EMP GROUP BY EMPNO HAVING COUNT(EMPNO)>1) AND A.ROWID!=MIN (ROWID));
15. SELECT ENAME "EMPLOYEE",TO_CHAR(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||' YEARS '|| TO_CHAR(TRUNC(MOD(MONTHS_BETWEEN (SYSDATE, HIREDATE),12)))||' MONTHS ' "LENGTH OF SERVICE" FROM EMP;
SQL SERVER FAQ AND QUERIES http://royalarun.blogspot.com/
16 June 2010
ASP.net advantage ASP.NET VS ASP
ASP.net advantage ASP.NET VS ASP
New in ASP.NET
Better Programmable controls
Event-driven programming
XML-based components
User authentication, with accounts and roles
Higher scalability
Increased performance - Compiled code
Easier configuration and deployment
Not fully ASP compatible
--------------------------------------------------------------------------------
Language Support
ASP.NET uses ADO.NET.
ASP.NET supports full Visual Basic, not VBScript.
ASP.NET supports C# (C sharp) and C++.
ASP.NET supports JScript.
--------------------------------------------------------------------------------
ASP.NET Controls
ASP.NET contains a large set of HTML controls. Almost all HTML elements on a page can be defined as ASP.NET control objects that can be controlled by scripts.
ASP.NET also contains a new set of object-oriented input controls, like programmable list-boxes and validation controls.
A new data grid control supports sorting, data paging, and everything you can expect from a dataset control.
--------------------------------------------------------------------------------
Event Aware Controls
All ASP.NET objects on a Web page can expose events that can be processed by ASP.NET code.
Load, Click and Change events handled by code makes coding much simpler and much better organized.
--------------------------------------------------------------------------------
ASP.NET Components
ASP.NET components are heavily based on XML. Like the new AD Rotator, that uses XML to store advertisement information and configuration.
--------------------------------------------------------------------------------
User Authentication
ASP.NET supports form-based user authentication, cookie management, and automatic redirecting of unauthorized logins.
--------------------------------------------------------------------------------
User Accounts and Roles
ASP.NET allows user accounts and roles, to give each user (with a given role) access to different server code and executables.
--------------------------------------------------------------------------------
High Scalability
Much has been done with ASP.NET to provide greater scalability.
Server-to-server communication has been greatly enhanced, making it possible to scale an application over several servers. One example of this is the ability to run XML parsers, XSL transformations and even resource hungry session objects on other servers.
--------------------------------------------------------------------------------
Compiled Code
The first request for an ASP.NET page on the server will compile the ASP.NET code and keep a cached copy in memory. The result of this is greatly increased performance.
--------------------------------------------------------------------------------
Easy Configuration
Configuration of ASP.NET is done with plain text files.
Configuration files can be uploaded or changed while the application is running. No need to restart the server. No more metabase or registry puzzle.
--------------------------------------------------------------------------------
Easy Deployment
No more server-restart to deploy or replace compiled code. ASP.NET simply redirects all new requests to the new code.
--------------------------------------------------------------------------------
Compatibility
ASP.NET is not fully compatible with earlier versions of ASP, so most of the old ASP code will need some changes to run under ASP.NET.
To overcome this problem, ASP.NET uses a new file extension ".aspx". This will make ASP.NET applications able to run side by side with standard ASP applications on the same server.
New in ASP.NET
Better Programmable controls
Event-driven programming
XML-based components
User authentication, with accounts and roles
Higher scalability
Increased performance - Compiled code
Easier configuration and deployment
Not fully ASP compatible
--------------------------------------------------------------------------------
Language Support
ASP.NET uses ADO.NET.
ASP.NET supports full Visual Basic, not VBScript.
ASP.NET supports C# (C sharp) and C++.
ASP.NET supports JScript.
--------------------------------------------------------------------------------
ASP.NET Controls
ASP.NET contains a large set of HTML controls. Almost all HTML elements on a page can be defined as ASP.NET control objects that can be controlled by scripts.
ASP.NET also contains a new set of object-oriented input controls, like programmable list-boxes and validation controls.
A new data grid control supports sorting, data paging, and everything you can expect from a dataset control.
--------------------------------------------------------------------------------
Event Aware Controls
All ASP.NET objects on a Web page can expose events that can be processed by ASP.NET code.
Load, Click and Change events handled by code makes coding much simpler and much better organized.
--------------------------------------------------------------------------------
ASP.NET Components
ASP.NET components are heavily based on XML. Like the new AD Rotator, that uses XML to store advertisement information and configuration.
--------------------------------------------------------------------------------
User Authentication
ASP.NET supports form-based user authentication, cookie management, and automatic redirecting of unauthorized logins.
--------------------------------------------------------------------------------
User Accounts and Roles
ASP.NET allows user accounts and roles, to give each user (with a given role) access to different server code and executables.
--------------------------------------------------------------------------------
High Scalability
Much has been done with ASP.NET to provide greater scalability.
Server-to-server communication has been greatly enhanced, making it possible to scale an application over several servers. One example of this is the ability to run XML parsers, XSL transformations and even resource hungry session objects on other servers.
--------------------------------------------------------------------------------
Compiled Code
The first request for an ASP.NET page on the server will compile the ASP.NET code and keep a cached copy in memory. The result of this is greatly increased performance.
--------------------------------------------------------------------------------
Easy Configuration
Configuration of ASP.NET is done with plain text files.
Configuration files can be uploaded or changed while the application is running. No need to restart the server. No more metabase or registry puzzle.
--------------------------------------------------------------------------------
Easy Deployment
No more server-restart to deploy or replace compiled code. ASP.NET simply redirects all new requests to the new code.
--------------------------------------------------------------------------------
Compatibility
ASP.NET is not fully compatible with earlier versions of ASP, so most of the old ASP code will need some changes to run under ASP.NET.
To overcome this problem, ASP.NET uses a new file extension ".aspx". This will make ASP.NET applications able to run side by side with standard ASP applications on the same server.
11 June 2010
PRIMARY KEY Constraints Primary key and Composite Primary key
PRIMARY KEY Constraints Primary key and Composite Primary key
A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.
A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.
When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.
If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite PRIMARY KEY constraint for this table. This makes sure that that the combination of ProductID and VendorID is unique
When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which vendors supply which products, you can use a three-way join between the Purchasing.Vendor table, the Production.Product table, and the Purchasing.ProductVendor table. Because ProductVendor contains both the ProductID and VendorID columns, both the Product table and the Vendor table can be accessed by their relationship to ProductVendor.
A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.
A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.
When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.
If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.
As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite PRIMARY KEY constraint for this table. This makes sure that that the combination of ProductID and VendorID is unique
When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which vendors supply which products, you can use a three-way join between the Purchasing.Vendor table, the Production.Product table, and the Purchasing.ProductVendor table. Because ProductVendor contains both the ProductID and VendorID columns, both the Product table and the Vendor table can be accessed by their relationship to ProductVendor.
10 June 2010
What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.
What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar ?
ExecuteReader : Use for accessing data. It provides a forward-only,read-only,connected recordset.
ExecuteNonQuery : Use for data manipulation, such as Insert, Update, Delete.
ExecuteScalar : Use for retrieving 1 row 1 col. value., i.e. Single value. eg: for retrieving aggregate function. It is faster than other ways of retrieving a single value from Database.
ExecuteReader : Use for accessing data. It provides a forward-only,read-only,connected recordset.
ExecuteNonQuery : Use for data manipulation, such as Insert, Update, Delete.
ExecuteScalar : Use for retrieving 1 row 1 col. value., i.e. Single value. eg: for retrieving aggregate function. It is faster than other ways of retrieving a single value from Database.
2 June 2010
view state in asp.net royalarun.blogspot.com
View State in Asp.net - royalarun.blogspot.com
Microsoft® ASP.NET view state is the technique used by an ASP.NET Web page to persist changes to the state of a Web Form across postbacks.
Microsoft® ASP.NET view state is the technique used by an ASP.NET Web page to persist changes to the state of a Web Form across postbacks.
Difference Between VB.Net and C#.Net - royalarun.blogspot.com
Difference Between VB.Net and C#.Net
major differences between them :-
Advantages VB.NET :-
a).Has support for optional parameters which makes COM interoperability much easy.
b).With Option Strict off late binding is supported.Legacy VB functionalities can be used by using Microsoft.VisualBasic namespace.
c).Has the WITH construct which is not in C#.
d).The VB.NET part of Visual Studio .NET compiles your code in the background.
While this is considered an advantage for small projects, people creating very large projects have found that the IDE slows down considerably as the project gets larger.
Advantages of C#
a).XML documentation is generated from source code but this is now been incorporated in Whidbey.
b).Operator overloading which is not in current VB.NET but is been introduced in
Whidbey.
c).Use of this statement makes unmanaged resource disposal simple.
d).Access to Unsafe code. This allows pointer arithmetic etc, and can improve
performance in some situations. However, it is not to be used lightly, as a lot of the normal safety of C# is lost (as the name implies).This is the major difference that you can access unmanaged code in C# and not in VB.NET.
It is easier for Visual Basic 6.0 developers to use Visual Basic .NET and for C++/Java programmers to use Visual C# .NET. The existing experience of a programmer far outweighs the small differences between the two languages.
major differences between them :-
Advantages VB.NET :-
a).Has support for optional parameters which makes COM interoperability much easy.
b).With Option Strict off late binding is supported.Legacy VB functionalities can be used by using Microsoft.VisualBasic namespace.
c).Has the WITH construct which is not in C#.
d).The VB.NET part of Visual Studio .NET compiles your code in the background.
While this is considered an advantage for small projects, people creating very large projects have found that the IDE slows down considerably as the project gets larger.
Advantages of C#
a).XML documentation is generated from source code but this is now been incorporated in Whidbey.
b).Operator overloading which is not in current VB.NET but is been introduced in
Whidbey.
c).Use of this statement makes unmanaged resource disposal simple.
d).Access to Unsafe code. This allows pointer arithmetic etc, and can improve
performance in some situations. However, it is not to be used lightly, as a lot of the normal safety of C# is lost (as the name implies).This is the major difference that you can access unmanaged code in C# and not in VB.NET.
It is easier for Visual Basic 6.0 developers to use Visual Basic .NET and for C++/Java programmers to use Visual C# .NET. The existing experience of a programmer far outweighs the small differences between the two languages.
Subscribe to:
Posts (Atom)