sql for etl testing

Download SQL for ETL Testing

Post on 06-May-2015

10.672 views

Category:

Education

4 download

Embed Size (px)

TRANSCRIPT

  • 1.P2CINFOTECH www.p2cinfotech.com Contact : +1-732-546-3607 Email id:training@p2cinfotech.com 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.

2. 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#. 3. 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 4. 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 5. 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 6. 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 7. 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 8. Foreign key creation: CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_namevarchar2(50) not null, contact_namevarchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) ); CREATE TABLE products ( product_id numeric(10) not null, supplier_id numeric(10) not null, CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ); +1-732-546-3607 9. 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_comp FOREIGN KEY (supplier_id, supplier_name) REFERENCES supplier (supplier_id, supplier_name) ); 10. 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; 11. P2CINFOTECH.COM +1-732-546-3607 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 12. P2CINFOTECH.COM +1-732-546-3607 Update Statement in table: UPDATE suppliers SET name = 'HP' WHERE name = 'IBM'; UPDATE suppliers SET supplier_name = (SELECT customers.name FROM customers WHERE customers. customer_id = suppliers. supplier_id); 13. P2CINFOTECH +1-732-546-3607 24. Delete Statement in table: 25 DELETE FROM suppliers WHERE supplier_name = 'IBM'; 26 DELETE FROM suppliers WHERE EXISTS ( select customers.name from customers wherecustomers.customer_id = suppliers.supplier_id andcustomers.customer_name = 'IBM' ); ************************** select statement 14. P2CINFOTECH.COM +1-732-546-3607 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%' 15. P2CINFOTECH.COM +1-732-546-3607 31. Arithmetic Operation: Operator Description = Equal Not equal > Greater than < Less than >= Greater than or equal 10000; Using UNION Clause: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA 19. P2CINFOTECH.COM +1-732-546-3607 UNION ALL Clause: SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA JOINS: Referring to Two Tables: SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID INNER JOIN: SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID; 20. P2CINFOTECH.COM +1-732-546-3607 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; 21. P2CINFOTECH.COM +1-732-546-3607 Subqueries: 1) Select distinct country from Northwind.dbo.Customers where country not in (select distinct country from Northwind.dbo.Suppliers); 2) Select top 1 OrderId, convert (char (10), OrderDate, 121) Last_Paris_Order, (Select convert (char (10), max (OrderDate), 121) from Northwind.dbo.Orders) Last_OrderDate, datediff(dd,OrderDate, (select Max(OrderDate) from Northwind.dbo.Orders)) Day_Diff fromNorthwind.dbo.Orders whereShipCity = 'Paris' order by OrderDatedesc; 22. P2CINFOTECH.COM +1-732-546-3607 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;