24. Different types of SQL keys?

Types of SQL Keys:

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. 

Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. 

Example: We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defined Keys-

CREATE TABLE Department
(
DeptID int PRIMARY KEY,
Name varchar (50) NOT NULL,
Address varchar (200) NOT NULL, )
CREATE TABLE Student
(
ID int PRIMARY KEY,
RollNo varchar(10) NOT NULL,
Name varchar(50) NOT NULL,
EnrollNo varchar(50) UNIQUE,
Address varchar(200) NOT NULL,
DeptID int FOREIGN KEY REFERENCES Department(DeptID)
)

23. Difference between Union and Joins?

The UNION operator is used to combine the result-set of two or more SELECT statements.

Note that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Orders Table

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Customers Table

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

Notice that the "CustomerID" column in the "Orders" table refers to the customer in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_ name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

If we run the SQL statement (that contains an INNER JOIN) below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

22. Write a SQL to find the third highest salary ?

In Oracle:

select * from

         (select * from

                         (select emp_name, sal from employee order by sal desc)
          where rownum < 4 order by sal)
where rownum = 1;

In SQL Server using TOP keyword:

SELECT TOP 1 Salary FROM

           ( SELECT DISTINCT TOP N Salary 

             FROM Employee 

             ORDER BY Salary DESC ) 

AS Emp ORDER BY Salary;

21. What is a Web Service and How does it work?

A web service is any piece of software that makes itself available over the internet and uses a standardized XML messaging system. XML is used to encode all communications to a web service. For example, a client invokes a web service by sending an XML message, then waits for a corresponding XML response. Because all communication is in XML, web services are not tied to any one operating system or programming language--Java can talk with Perl; Windows applications can talk with Unix applications.

To summarize, a complete web service is, therefore, any service that:

• Is available over the Internet or private (intranet) networks
• Uses a standardized XML messaging system
• Is not tied to any one operating system or programming language
• Is self-describing via a common XML grammar

Components of Web Services:

The basic Web services platform is XML + HTTP. All the standard Web Services works using following components:

• SOAP (Simple Object Access Protocol)
• UDDI (Universal Description, Discovery and Integration)
• WSDL (Web Services Description Language)

How Does it Work?

You can build a Java-based Web Service on Solaris that is accessible from your Visual Basic program that runs on Windows. You can also use C# to build new Web Services on Windows that can be invoked from your Web application that is based on JavaServer Pages (JSP) and runs on Linux.

An Example:

Consider a simple account-management and order-processing system. The accounting personnel use a client application built with Visual Basic or JSP to create new accounts and enter new customer orders.The processing logic for this system is written in Java and resides on a Solaris machine, which also interacts with a database to store the information.

The steps illustrated above are as follows:

• The client program bundles the account registration information into a SOAP message.
• This SOAP message is sent to the Web Service as the body of an HTTP POST request.
• The Web Service unpacks the SOAP request and converts it into a command that the application can understand. The application processes the information as required and responds with a new unique account number for that customer.
• Next, the Web Service packages up the response into another SOAP message, which it sends back to the client program in response to its HTTP request.
• The client program unpacks the SOAP message to obtain the results of the account registration process. For further details regarding the implementation of Web Services technology, read about the Cape Clear product set and review the product components.

20. Why do we use Stubs and Drivers?

Stubs, dummy modules, which are always distinguished as "called programs", are handled in integration testing (top down approach); and are used when sub programs are under construction.

Stubs are considered as the dummy modules that always simulate the low level modules.

Drivers are also considered as the form of dummy modules which are always distinguished as "calling programs”, that is handled in bottom up integration testing, it is only used when main programs are under construction.

Drivers are considered as the dummy modules that always simulate the high level modules.

Example of Stubs and Drivers is given below:-

For Example: we have 3 modules login, home, and user module. Login module is ready and need to test it, but we call functions from home and user (which is not ready). To test at a selective module we write a short dummy piece of a code which simulates home and user, which will return values for Login, this piece of dummy code is always called Stubs and it is used in a top down integration. 

Considering the same Example above: If we have Home and User modules get ready and Login module is not ready, and we need to test Home and User modules Which return values from Login module, So to extract the values from Login module We write a Short Piece of Dummy code for login which returns value for home and user, So these pieces of code is always called Drivers and it is used in Bottom Up Integration.

So it is fine from the above example that Stubs act “called” functions in top down integration. Drivers are “calling” Functions in bottom up integration.

19. Difference between System and Integration Testing?

System Integration Testing(SIT) is testing after combining two or more parts, e.g. 2 modules that should work together in the future, like a GUI and a DB or logic something. So you test integrity of modules with each other or in a bigger system (can also be a new feature that you test after being added into the system to know if it's still working like in unit test).

System Testing is also called Process Testing and describes the Test of the whole System, e.g. putting all tested modules together. This could be at a webshop: website-frontend + logic + db + ordering-api. Testing would include if the order done in the front-end (on the website) is correctly passed through all stages to the ordering-api.

"System testing" is a high level testing, and "integration testing" is a lower level testing. 

Integration testing is completed first, not the system testing.

In other words, upon completion of integration testing, system testing is started, and not vice versa.

For integration testing, test cases are developed with the express purpose of exercising the interfaces between the components.

For system testing, the complete system is configured in a controlled environment, and test cases are developed to simulate real life scenarios that occur in a simulated real life test environment.

The purpose of integration testing is to ensure distinct components of the application still work in accordance to customer requirements.

The purpose of system testing is to validate an application's accuracy and completeness in performing the functions as designed, and to test all functions of the system that are required in real life.

18. Difference between Static and Dynamic Testing?

Under Static Testing code is not executed. Rather it checks the code, requirement documents, and design documents manually to find errors. Hence,the name “static”.

Main objective of this testing is to improve the quality of software products by finding errors in early stages of the development cycle. This testing is also called as Non-execution technique or verification testing.

Static testing involves manual or automated reviews of the documents. This review, is done during initial phase of testing to catch defect early in STLC. It examines work documents and provides review comments.

Work document can be of following:

· Requirement specifications

· Design document

· Source Code

· Test Plans

· Test Cases

· Test Scripts

Under Dynamic Testing code is executed. It checks for functional behavior of software system , memory/cpu usage and overall performance of the system. Hence the name “Dynamic”

Main objective of this testing is to confirm that the software product works in conformance with the business requirements. This testing is also called as Execution technique or validation testing.

Dynamic testing executes the software and validates the output with the expected outcome. Dynamic testing is performed at all levels of testing and it can be either black or white box testing.

No.	Static Testing	Dynamic Testing
1	It's testing of without executing of the Software.	It's testing that involves the execution of the Software.
2	In Static Testing software are examined manually and some Static analysis tool used.	In Dynamic Testing software executed by giving set of inputs,examined  it's output and compared what is expected.
3	Static Testing can start early in the life cycle.Eg: By Verifying User Requirements.	Dynamic testing can start after development of software components.
4	Types of defect find in Static testing are : Missing requirements, Desgin defect ,Syntax Error etc.	Types of defect find in dynamic testing are : Variables not constant ,checking if output from the expected values.
5	Types of Static Testing: Review ,Inspection , Walk-through.	Types of Dynamic Testing: Unit testing, Integartion testing, System Testing, Acceptance Testing.
6	Static Testing find bug before you compile.	Dynamic testing find bug after compilation, linking.
7	Static Testing is about prevention.	Dynamic Testing is about cure.
8	Static Testing is most cost effective than Dynamic Testing.	Dynamic Testing not Cost effective as compare to Static Testing
9	Static Testing done in the verification stage.	Dynamic Testing done in validation stage.
10	Static Testing gives 100% statement coverage.	Dynamic Testing does not give 100% statement coverage.