INTERACTIVE SQL PART II

1. INTERACTIVE SQL PART II Prepared By: Mitali Sonar (Assistant Prof.)

2. Group by • Group rows based on distinct values for columns • divides a table into groups of rows • used in • conjunction with the aggregate functions • to group the result-set by one or more columns. • Find out how many students are there in each sub.

3. Find out the number of students for each value of marks. select count(marks) as "no of students", marks from student group by marks;

4. Find out the number of employees working in each department • Employee Select count(dept_name) as No. of employees, dept_name from employee group by dept_name

5. HAVING CLAUSE • Used to restrict the group • It imposes a condition on group by clause which filters groups Find out students group having count more than 1 select count(marks) as "no of students",marks from student group by marks having count(marks)>1;

6. list the product names of literacy category  that have winter_sales value more than 700 SELECT name, category, winter FROM salesWHERE category="Literary"GROUPBY winter HAVING winter > 700

7. Sub query (nested query) • A SQL statement that appear inside another SQL statement • Ex : - select … from ( select …from…..) • Syntax :- • Select column from table Where condition (select column from table Where condition) • Inner query executed first that returns certain rows • Next outer query is executed based on result of this inner query

8. SELECT category_name FROM categories WHERE category_id = ( SELECT MIN(category_id) from movies);

9. Employee (fname, lname, salary, dept_id) • Dept (dept_id, dept_name) • Find out employee detail working in finance department. • Select dept_id from dept where dept_name = ‘finance’ • Select fname,lname from employee where dept_id = • Select fname,lname from employee where dept_id = (Select dept_id from dept where dept_name = ‘finance’)

10. Creating a table using sub query • CREATE TABLE tablename AS SELECT-QUERY • This will create a new table and populate it with rows selected from other tables • CREATE TABLE temp AS Select eid,fname,lname, salary from employee WHERE dept_id =20; • Describe temp

11. Select * from temp;

12. Insert using sub query • Existing table can be populated with sub query • INSERT into TABLENAME (SELECT columnnames FROM tablename WHERE condition) • Ex :- INSERT INTO temp(eid, lname, fname) SELECT eid, lname, fname from EMPLOYEE WHERE dept_id=10; • Temp

13. Update using sub query • Updates can be performed using sub-query • Syntax :- UPDATE table-name • SET column-name = value • WHERE condition (SELECT SUBQUERY) • Ex:- update salary of employees using dept-id of finance department UPDATE employee SET salary = salary *1.10 WHERE dept_id = (SELECT dept_id FROM dept WHERE d_name = ‘Finance’)

14. Delete using sub-query • A row or rows from table can be deleted based on value returned by subquery • Syntax:- • DELETE FROM tablename WHERE condition (SELECT subquery) • Ex:- delete all employees in Accounting department using dept id. • DELETE FROM employee • WHERE dept_id = • (SELECT dept_id FROM department • where dept_name = ‘Accounting’);

15. Multiple row sub query • Multiple row sub query returns more than one row.

16. IN operator looks for at least one match from the list of values provided • Find out student details handled by computer dept. faculty. • Student • Faculty • select s_id, lastname, firstname, faculty_id from student where faculty_id in (select faculty_id from faculty where dept_name =‘computer’);

17. Ex:- IN • Identify items that are low (having quantity <20) in stock • Two Tables • Sales Order Items • Product • Select * From SalesOrderItems Where ProductID IN ( Select ID From Products Where Quantity < 20 )

18. ANY operator compare a value with any value in a list. • Ex:- • Find out the agent who worked for customer live in the city ‘AHMEDABAD'. • Agent • Customer SELECT ID,agent_name,working_area,commission FROM agents WHERE ID = ANY ( SELECT agent_id FROM customer WHERE city=‘Ahmedabad');

19. JOIN OPERATION • Working with multiple table • Manipulate data from all tables • Tables are joined on columns having same data type • combination of operations selection, projection and Cartesian product • Cross Join • Natural Join • Inner Join • Outer join • Full outer join • Left outer join • Right outer join

20. Cross join • Combines every row from left table to every row in right table • Not preferred :- may run for long time, • produces a huge result set that may not be useful CUSTOMERS ORDERS • Select id, name, amount, date from customers CROSS JOIN orders;

22. Natural Join • Most common type of join is a “natural join” (often just called “join”). R S conceptually is: • Compute R X S • The associated tables have one or more pairs of identically named columns • Select rows where attributes that appear in both relations have equal values • Select * FROM table1 NATURAL JOIN table2;

23. Natural Join • Select * from r1 NATURAL JOIN s1 R1 S1 R1 S1 =

24. Inner Join • creates a new result table • by combining column values of two tables (table1 and table2) • based upon the join-predicate. • SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON condition;

25. Customer Order Select id, name, amount, date from customers inner join orders on customers.Id = orders.Customer_id;

27. Outer join • returns all rows from both the participating tables which satisfy the join condition and • Selects those tuples that donot satisfies the join condition • Left Outer Join On two relations R and S • It selects matching tuples of both relations R and S • Along with unmatched tuples from relation R • Missing values in relation s is set to NULL. • R S Selected records with matching values Records of R S R

28. SELECT * FROM table1 LEFT JOIN table2 ON table1.common_filed = table2.common_field; Employee Department Select * from employee LEFT JOIN Department ON employee.Dept_Name = Department.Dept_Name

29. Left Outer Join

30. Right Outer Join • JoinOn two relations R and S • It selects matching tuples of both relations R and S • Along with unmatched tuples from relation S • Missing values in relation R is set to NULL • R S Records of S Selected records with matching values S R

31. SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name; Employee Department Select * from employee RIGHT JOIN Department ON employee.Dept_Name = Department.Dept_Name

32. Right Outer Join

33. Full Outer Join • JoinOn two relations R and S • It selects matching tuples of both relations R and S • Along with unmatched tuples from relation R and relation S • R S Records of S Selected records with matching values Records of R S R

34. Select * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; Employee Department Select * from employee FULL OUTER JOIN Department ON employee.Dept_Name = Department.Dept_Name

36. Semi join • Join on relation R and S contains tuples of R that participate in join operation of R and S • Only attributes of relation R is projected • Advantage • decreases the number of tuples to be handled to form join

37. Semi Join - EMP with PAY over the predicate EMP.TITLE = PAY.TITLE,