Basic SQL Queries used in Testing

SQL stands for Structured Query Language which is used to managing data in relational database management systems.

1. To check which database is using currently use:  SELECT database();

2. use databasename to select any database;

3. Select * from tablename; to retrieve all data from a particular table

4. Select * from tablename where condition; to retrieve  data for specific Condition

Joins : These are used to retrieve data from two or more than two tables based on some common attributes in these tables.

Use these commands to create two sample tables:

CREATE TABLE school
(
SectionID INT,
SectionName VARCHAR(20)
);
CREATE TABLE teacher
(
FirstName VARCHAR(20),
SectionID INT
);
 

Insert this data in above tables:

---------------------------------------------------------------------------------------------INSERT INTO school(SectionID, SectionName) VALUES(10, 'Science');INSERT INTO school(SectionID, SectionName) VALUES(11, 'Sports');INSERT INTO school(SectionID, SectionName) VALUES(12, 'Commerce');INSERT INTO school(SectionID, SectionName) VALUES(14, 'Arts');
INSERT INTO teacher(FirstName, SectionID) VALUES('Amit', 10);INSERT INTO teacher(FirstName, SectionID)  VALUES('Deepak', 11);INSERT INTO teacher(FirstName, SectionID)  VALUES('Stuti', 12);INSERT INTO teacher(FirstName, SectionID)  VALUES('Devendra', 14);INSERT INTO teacher(FirstName, SectionID)  VALUES('Dinesh', 14);INSERT INTO teacher(FirstName, SectionID)  VALUES('Jay', NULL);

INSERT INTO teacher(FirstName, SectionID)  VALUES('Mukesh', 11);

---------------------------------------------------------------------------------------------

1. Cross Join: CROSS Join returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table.

Query:

a) select * from school CROSS JOIN teacher; will have 1st school row with all teacher row and then 2 school row than all teacher rows

can also used as SELECT * FROM school,teacher;

b) select * from teacher CROSS JOIN school;will have 1st teacher row with all school row and then 2 teacher row than all school rows

2. Inner Join: Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate.

The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. This can include duplicate data from another table for corresponding matching condition in first table.

SELECT * FROM school  INNER JOIN teacher ON school.SectionID = teacher.SectionID;

or we can write it as : SELECT * FROM teacher, school WHERE teacher.SectionID = school.SectionID;

3. Equi Join: An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join.

Above query of inner join can be used in this example also as those have = inthem.

If columns in an equi-join have the same name, an optional shorthand notation for expressing equi-joins, by way of the USING construct: Select * FROM teacher INNER JOIN school USING (SectionID);

Any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join

"1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
2. Equi join only have equality (=) operator in the join condition.
3. Equi join can be an Inner join, Left Outer join, Right Outer join
4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL."

4.Outer Join:An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).

a)Left Outer Join:The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B)

SELECT * FROM teacher LEFT OUTER JOIN school ON teacher.SectionID = school.SectionID;

b)Right Outer Join:A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.

SELECT * FROM teacher Right OUTER JOIN school ON teacher.SectionID = school.SectionID;

5. Natural Join:A natural join is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns.

SELECT * FROM teacher NATURAL JOIN school;