sql for etl testing

P2CINFOTECHwww.p2cinfotech.com

Contact : +1-732-546-3607

Email id:[email protected]

IT Online Training and Placement(QA, BA, QTP, JAVA, Mobile apps..)

Mock interviews.

100% job Placement assistance.

Free Training for Opt/MS Students.

Placement with one of our Fortune 500 clients.

Live Instructor Led Face2Face Online Training.

http://www.p2cinfotech.com/

SQL FOR ETL TESTING What is Web Services?

It is a middleware developed in XML. Exchange the data between multiple platforms.

What is web services? It is middleware technology developed in XML to exchange the data between multiple platforms and languages.

what is rest protocol ?REST stands for Representational State Transfer. (It is sometimes spelled "ReST".) EST is a lightweight alternative to mechanisms like RPC (Remote Procedure Calls) and Web Services (SOAP, WSDL, et al.). Later, we will see how much more simple REST is.Despite being simple, REST is fully-featured; there's basically nothing you can do in Web Services that can't be done with a RESTful architecture.REST is not a "standard". There will never be a W3C recommendation for REST, for example. And while there are REST programming frameworks, working with REST is so simple that you can often "roll your own" with standard library features in languages like Perl, Java, or C#.

P2cinfotech.com

In Interviews they ask do you have work experience in web based applications, does that mean having experience in web services in SOAPUI ? Ans: NO

What is synchronous web services? -- get response immediately.

What is asynchronous web services? --Response will be sent when the service is available.

What is SOAPUI? It is a tool to test the Web Services.

what is xml schema?-- XML schema is well defined xml structure. It is w3 standards.

+1-732-546-3607

P2cinfotech.com

9). In Interviews if they ask do you have work experience in web based applications, does that mean having experience in web services in soapUI?Ans: NO.

Create a table:

CREATE TABLE customer

(First_Namechar (50),

Last_Namechar(50),

Address char(50),

City char(50),

Country char(25),

Birth_Date date) ;

+1-732-546-3607

P2cinfotech.com

2. Add a column to a table:

ALTER TABLE customer ADD SO_INSURANCE_PROVIDER

Varchar2(35);

3. DROP a column to a table

ALTER TABLE customer DROP column SO_INSURANCE_PROVIDER

Varchar2(35);

4. Add a default value to a column

ALTER TABLE customer MODIFY SO_INSURANCE_PROVIDER

Varchar2(35) DEFAULT 'ABC Ins';

5. Renaming a table:

ALTER TABLE suppliers RENAME TO vendors;

+1-732-546-3607

P2cinfotech.com

6. Modifying column(s) in a table:

ALTER TABLE supplier MODIFY supplier_namevarchar2(100)

not null;

7. Drop column(s) in a table:

ALTER TABLE supplier DROP COLUMN supplier_name;

8. Primary key:

CREATE TABLE supplier

(

supplier_id numeric(10) not null,

supplier_namevarchar2(50) not null,

contact_namevarchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,

supplier_name));

+1-732-546-3607

P2cinfotech.com Add primary key: ALTER TABLE supplier add CONSTRAINT supplier_pk PRIMARY

KEY (supplier_id);

Drop primary key: ALTER TABLE supplier drop CONSTRAINT supplier_pk;

Disable primary key: ALTER TABLE supplier disable CONSTRAINT supplier_pk;

Enable primary key: ALTER TABLE supplier enable CONSTRAINT supplier_pk;

+1-732-546-3607

Foreign key creation:

CREATE TABLE supplier

(


supplier_namevarchar2(50) not null,

contact_namevarchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

CREATE TABLE products

(

product_id numeric(10) not null,


CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES supplier(supplier_id)

);

+1-732-546-3607

14. More than column :CREATE TABLE supplier(supplier_id numeric(10) not null,supplier_namevarchar2(50) not null,contact_namevarchar2(50),CONSTRAINT supplier_pk PRIMARY KEY (supplier_id,

supplier_name));CREATE TABLE products(Product_id numeric (10) not null,Supplier_id numeric (10) not null,supplier_name varchar2 (50) not null,CONSTRAINT fk_supplier_compFOREIGN KEY (supplier_id, supplier_name)REFERENCES supplier (supplier_id, supplier_name));

P2CINFOTECH.COM +1-732-546-3607

Alter foreign key:

ALTER TABLE products add CONSTRAINT fk_supplier FOREIGN KEY

(supplier_id) REFERENCES supplier (supplier_id);

Drop foreign key:

ALTER TABLE SALES_ORDER_LINE DROP FOREIGN KEY

FK_SALES_ORDER_LINE_PRODUCT

Check constraint:

ALTER TABLE EMPLOYEE ADD CONSTRAINT REVENUE CHECK

(SALARY + COMM > 25000)

Drop check constraint:

ALTER TABLE EMPLOYEE DROP CONSTRAINT REVENUE CHECK

(SALARY + COMM > 25000)

19. Drop Table:

DROP TABLE customer;


Truncate Statement:

Truncate table customer;

*********************************************************************End of DDL

Statements***********************************************************************

****************************************************************** DML Statements

***************************************************************************

Insert rows in table:

1) INSERT INTO Store_Information (store_name, Sales, Date)

VALUES ('Los Angeles', 900, 'Jan-10-1999')

2) INSERT INTO Store_Information (store_name, Sales, Date) SELECT

store_name, Sales, Date FROM Sales_Info WHERE Year

(Date) = 1998


Update Statement in table:UPDATE suppliers SET name = 'HP' WHERE

name = 'IBM';UPDATE suppliersSET supplier_name =(SELECT customers.nameFROM customersWHERE customers. customer_id = suppliers.

supplier_id);

P2CINFOTECH +1-732-546-3607

24. Delete Statement in table:25 DELETE FROM suppliers WHERE

supplier_name = 'IBM';26 DELETE FROM suppliersWHERE EXISTS( select customers.namefrom customerswherecustomers.customer_id =

suppliers.supplier_idandcustomers.customer_name = 'IBM' );************************** select statement


Select Statement in table:

1. SELECT LastName, FirstName FROM Persons;

2. SELECT * FROM Persons;

The SELECT DISTINCT Statement:

SELECT DISTINCT Company FROM Orders;

The WHERE Clause:

SELECT * FROM Persons WHERE City='Sandnes‘

Using LIKE

SELECT * FROM Persons WHERE FirstName LIKE 'O%'


31. Arithmetic Operation:Operator Description= Equal<> Not equal> Greater than< Less than>= Greater than or equal<= Less than or equalBETWEENBetween an inclusive rangeLIKE Search for a patternIN If you know the exact value youwant to return for at least one of thecolumns


BETWEEN ... AND:SELECT * FROM Persons WHERE LastName

BETWEEN 'Hansen' AND'Pettersen';INSELECT * FROM Persons WHERE LastName IN

('Hansen','Pettersen');Column Name AliasSELECT LastName AS Family, FirstName AS

Name FROM Persons


AND & ORSELECT * FROM Persons WHERE FirstName='Tove'

ANDLastName='Svendson'SELECT * FROM Persons WHERE firstname='Tove'

ORlastname='Svendson'ORDER BYSELECT Company, OrderNumber FROM Orders

ORDER BY CompanySELECT Company, OrderNumber FROM Orders

ORDER BY CompanyDESC, OrderNumber ASC


Group by Clause:

SELECT Company, SUM (Amount) FROM Sales GROUP BY Company;

Having Clause:

SELECT Company, SUM (Amount) FROM Sales GROUP BY Company

HAVING SUM (Amount)>10000;

Using UNION Clause:

SELECT E_Name FROM Employees_Norway

UNION

SELECT E_Name FROM Employees_USA


UNION ALL Clause:SELECT E_Name FROM Employees_NorwayUNION ALLSELECT E_Name FROM Employees_USA JOINS: Referring to Two Tables:SELECT Employees.Name, Orders.ProductFROM Employees, OrdersWHERE Employees.Employee_ID=Orders.Employee_ID INNER JOIN:SELECT Employees.Name, Orders.ProductFROM EmployeesINNER JOIN OrdersON Employees.Employee_ID=Orders.Employee_ID;


LEFT JOIN:

SELECT Employees.Name, Orders.Product

FROM Employees

LEFT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID;

RIGHT JOIN:

SELECT Employees.Name, Orders.Product

FROM Employees

RIGHT JOIN Orders

ON Employees.Employee_ID=Orders.Employee_ID;


Subqueries:1) Select distinct country from

Northwind.dbo.Customerswhere country not in (select distinct country fromNorthwind.dbo.Suppliers);2) Select top 1 OrderId, convert (char (10),OrderDate, 121) Last_Paris_Order,(Select convert (char (10), max (OrderDate), 121)

fromNorthwind.dbo.Orders) Last_OrderDate,datediff(dd,OrderDate,(select Max(OrderDate) from Northwind.dbo.Orders))Day_DifffromNorthwind.dbo.OrderswhereShipCity = 'Paris' order by OrderDatedesc;


Commit & Rollback Statements:1) UPDATE suppliers SET name = 'HP' WHERE

name = 'IBM';Commit;2) UPDATE suppliers SET name = 'HP' WHERE

name = 'IBM';Rollback;SavepointStatement:INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING',

301);

SAVEPOINT SAVEPOINT1;

INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520);

SAVEPOINT SAVEPOINT2;

sql for etl testing

Education