Q1 Compare the correlated and uncorrelated subqueries Q2 Use

Q1. Compare the correlated and uncorrelated subqueries.

Q2. Use examples to compare the inner join and left outer join operators. Give one example for each operator, and compare the syntax and the differences in the output.

Q3.List three SQL wildcards and describe how they are used in SQL queries.

Solution

1ans)

Correlated subquery is a subquery it depends on the values obtainde by outer querry inorder to complete.We can say that a subquery depends on values of other query results.

example:select empid,empname from emp where sal>(select max(sal) from emp where dept=101);

here :outer query is select empid,empname from emp where sal;

inner query is:select max(sal) from emp where dept=101;

Uncorrelated subquery:it different and opposite to Correlated subquery.here in Uncorellated subquery the inner subquery donent depend on the outer query.

example:

select company from dept where LISTED_ON_EXCHANGE = (select ric from Market where city=\'delhi\');

Inner join:suppose we have 2 tables say table A and table B .thus we can retrive both the table rows if they hava a match in between them this is called INNER JOIN.

example:select emp.empid,emp.empname ,dept.deptname from emp inner join dept on emp.id=dept.id .

LEFT JOIN:left join retrives all the data or rows from left table and matched rows in right tabla and the remaing rows will be NULL in right side table where it has no match.LEFT join is also called as LEFT OUTER join.

example:

SELECT cust.custid, sales.salesid
FROM cust
LEFT JOIN sales
ON cust.custid=sales.custid
ORDER BY cust.custName;

and + operator indicates an Outer join.to perform outer join in two or more tables.

select city.name,county.id from city and county

3ans)wild cards;

these are used for substition of any other charecter in a string.

sql wild cards are used within sql like operator.

%--it is used for substituting one or more charecters

example:select empname from emp where empname like \'s%\';

retrives name of employees whose names startes with s.

_--it is used for substition of single charecter

example:select empname from emp where empname like \'_a%;

it retrives a name where name second charecter is a