Question

1. Why would you use a GROUP BY clause in a SELECT statement? 2. Explain the difference between SELECT launch_dt, MAX(price) GROUP BY launch_dt ORDER BY launch_dt; And SELECT launch_dt, MAX(price) ORDER BY launch_dt; 3. Why would you use the HAVING clause in a SELECT statement? 4. What is the difference between a primary key and a foreign key in SQL? 5. Why would you join tables? What is the difference between inner join and an outer join? Explain using examples 6. Present next week your final project with the list of all tables and columns 7. Next week – we start with a short quiz

Answer 1

1.Group By clause is used for getting aggregate value like count of, sum of in columns with reference to a distinct column in a table.

2.By using,SELECT launch_dt, MAX(price) GROUP BY launch_dt ORDER BY launch_dt;

Then we will get the maximum price of the distinct launch_dt names and the maximum values will be displayed in alphabetical order based on launch_dt names.

By using,SELECT launch_dt, MAX(price) ORDER BY launch_dt;

Then we will get the maximum price of all the values and display only the single maximum price and launch_dt name.

3.Having clause is used when ever we use aggregate values like count and max and also using group by at that time we use this clause instead of where clause.

4.Primary Key: A primary key is a field or combination of fields that uniquely identify a record in a table.

Foreign Key: A foreign key is also called as referencing key is a key used to link two tables together. And you take the primary key field from one table and insert it into the other table where it becomes a foreign key.

5.Join is a temporary relationship between the common columns of a tables or the column sharing the common data.

Join is used to combine fields of two or more tables of related information.

Inner join:

Inner join is used to retrieve the rows of different tables that having matching values.

Ex:For the tables employ emp(empno,ename,job,sal,deptno) and department dept(deptno,dname,location)

select emp.empno,emp.ename,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno

Right outer join:

It will fetch all records from the right hand side table in a join,irrespective of the matching.

Ex:

select emp.ename,dept.dname from emp right outer join dept where emp.deptno=dept.deptno

Left outer join:

It will fetch all records from the left hand side table in a join,irrespective of the matching.

Ex:

select emp.ename,dept.dname from emp left outer join dept where emp.deptno=dept.deptno

Full outer join:

It will retrieve all the matching data and all additional non matching data from both left and right side of the tables.

Ex:

select emp.ename,dept.dname from emp full outer join dept where emp.deptno=dept.deptno