Query Manipulation and Joins
1. What are the
different clauses used in the SELECT statement?
The SELECT statement is used in a query for retrieving data from tables.
2. What is the
correct order for the GROUP BY and HAVING clauses in SELECT statement?
1. SELECT
2. GROUP BY
3. HAVING
3. What are the
functions of the following clauses?
· ORDER BY – is used to sort the rows returned by a query (SELECT <select list> FROM <table name> ORDER BY <column> [ASC / DESC], where the ORDER BY clause specify the order of sorting the data as ascending – ASC, or descending – DESC. By default, the sort order is ASC.
· DISTINCT – is used with the SELECT statement to eliminate duplicate rows from query results (SELECT DISTINCT <Select list> FROM <table name>, where the DISTINCT clause eliminates duplicate records from the query results).
· WHERE – is used to retrieve only the records that satisfy a specific condition (SELECT <select list> FROM <table name> WHERE <condition>, where <condition> specifies the condition for retrieving the rows).
4. What is the
LIKE operator?
The LIKE operator is used to match values with a character pattern.
5. What are the
three types of operators you can use in SQL?
· Conditional – there are six operators used in DML for conditional selection of data from tables (=, >, <, >=, <=, <> or !=, !>, !<)
· Logical – are used to perform logical comparisons between sets of two or more conditions (AND, OR, NOR, BETWEEN, IN, LIKE)
· Arithmetic – are used to perform mathematical operations (+, -, /, *)
6. What is the
function of the IN and BETWEEN operators?
The IN and BETWEEN operators are used to specify conditions for selecting data within a range.
· IN operator is used to check whether a value is equal to any item in the list,
· BETWEEN operator is used to check whether the value is within the two specified value ranges, the high range and the low range.
7. What are the
aggregate functions?
Aggregate functions are ideally suited for performing many calculations, such as calculating commissions, generating detailed reports, and generating statistical totals. Some aggregate functions used in DML are: SUM(), COUNT(), MAX() and MIN(), and AVG().
8. What is the
functioning of a correlated sub-query?
A correlated sub-query is a form of nested sub-query that is evaluated once for each candidate row processed by the outer query. A correlated sub-query uses a value from a column specified in the outer query.
Using Correlated Sub-Query: SELECT
* FROM Employee e
WHERE mSalary >
(SELECT AVG (mSalary) FROM
Employee WHERE vDeptID
= e. vDeptID)
9. What is the
nested sub-query?
A sub-query containing more than one sub-query is called a nested sub-query. There is no limit on the number of sub-queries that can be used inside a nested sub-query.
10. What are the two basic conditions required for
performing a successful join operation?
The columns must be of the same data type and have similar data or a common key in both the tables.
11. What are the three basic types of joins in SQL?
· Inner join – is the simplest and most commonly used join. An inner join combines two or more tables and returns only the rows that satisfy the join condition.
· Self join – involves a more advanced concept that other joins. A join is said to be a self join when one row in a table correlates with the other rows in the same table.
· Outer join – compares the rows in the tables and returns the missing record if there are no matches for the rows in one table with the corresponding table to be joined. If there is no match, a NULL value is returned.
12. Write a query to list all the employees by name and ID along with their seniors’ names and IDs.
SELECT emp1.cEmployeeLName / / ‘ senior
is
‘ / / emp2. cEmployeeLName
“Employees and Their Seniors”
FROM Employee emp1 INNER JOIN
Employee emp2
ON emp1.vSenior = emp2. vEmployeeID
13. What is the difference between a left outer join and
a right outer join?
· Left Outer Join – ensures the inclusion of all rows from the first table and the matching rows from the second table,
· Right Outer Join – includes all rows from the second table and the matching rows from the first table.
