Joins and Set Operations Sumita arora IP Class 12 Type B Solution

Joins and Set Operations Sumita arora IP Type B Solution || Joins and Set Operations Sumita arora IP Type B Class 12 Solution || Joins and Set Operations IP Type B || Joins and Set Operations Information Practices || Joins and Set Operations Sumita arora Type B Information Practices Solution || Joins and Set Operations Type B Sumita arora Information Practices Class 12 Solution || Joins and Set Operations Type B Sumita arora Solution

Note :-  Please Click on Question to get Answer of that Question

Q1. In a Database, there are two tables given below : Write SQL Queries for the following :
(i) To display employee ids , names of employees, job ids with corresponding job titles.
(ii) To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
(iii) To display names and corresponding job titles of those employees who have ‘SINGH’ (anywhere) in their names.
(iv) Identify foreign key in the table EMPLOYEE. (v) Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table ‘EMPLOYEE’ .

Q2. Show the average salary for all departments with more than 3 people for a job.



Q3. Display only the jobs with maximum salary greater than or equal to 3000.



Q4. List the department names and the number of their employees.



Q5. List the employee names and the name of their departments.



Q6. What is join? How many different types of joins can you create in MySQL?



Q7. In a Database Karnataka_Sangam there are two tables with the instances given below:

Table: STUDENTS

Table: SPORTS

Write SQL queries for the following:
(i) To count how many addresses are not having NULL values in the address column of STUDENTS table?
(ii) To display Name, Class from STUDENTS table and the corresponding Grade from SPORTS table. (iii) To display Name of the student and their corresponding Coachnames from STUDENTS and SPORTS tables.


 
Q8. In a Database BANK there are two tables with a sample data given below:

Table: EMPLOYEE

Table: DEPARTMENT

Note.

• ENAME refers to Employee Name
• ADNAME refers to Department Name
• DEPT refers to Department Code
• HOD refers to Employee number (ENO) of the Head

Write SQL queries for the following:

(i) To display ENO, ENAME, SALARY and corresponding DNAME of all the employees whose age is between 25 and 35 (both values inclusive).
(ii) To display DNAME and corresponding ENAME from the tables DEPART- MENT and EMPLOYEE. Hint. HOD of the DEPARTMENT table should be matched with ENO of the EMPLOYEE table for getting the desired result.
(iii) To display ENAME, SALARY, ZONE and INCOME TAX (Note. Income Tax to be calculated as 30% of salary) of all the employees with appropriate column headings.



Q9. There are multiple ways to create Cartesian product of two tables in MySQL. Describe them.



Q10. How is a left join different from a natural join? Give example.



Q11. How is a cross join different from natural join? Give example.



Q12. A table "TRAINS" in a database has degree 3 and cardinality 8. What is the number of rows and columns in it?



Q13. In a database there are two tables "Product" and "Client" as shown below:

Table: Product

Table: Client

Write the commands in SQL queries for the following:
(i) To display the details of Product whose Price is in the range of 40 and 120 (Both values included).
(ii) To display the ClientName, City from table Client and ProductName and Price from table Product, with their corresponding matching P_ID.
(iii) To increase the Price of all the Products by 20.



Q14. In a Database School there are two tables Member and Division as shown below.

Table: Member

Table: Division

(i) Identify the foreign key in the table Member.
(ii) What output, you will get, when an equi-join query is executed to get the NAME from Table and corresponding from Division table?



Consider following tables to answer questions 15-20:

Salesman (salesman_id, name, city, commission) and
Customer (customer_id, cust_name, city, grade, salesman_id)


Q15. Write an SQL query to display all salesmen and customer located in Chennai.


Q16. Write an SQL query to display distinct salesman and their cities.


Q17. Write an SQL query to list all the salesmen who do not have customers in 'DELHI' and 'JAMMU".


Q18. Write an SQL query to list all the salesmen who have customers with grade 200 or higher.


Q19. Write an SQL query to list all the salesmen who have customers with grade 200 from city JAMMU'.


Q20. Write an SQL query to list all the customers whose salesmen have earned a commission of at least 15%



Consider the tables Member and Division of question 14 and answer questions 21-25


Q21. Write a SQL query to display all the divnos from both the tables.


Q22. Write a SQL query to display all the distinct divnos from both the tables.


Q23. Write a SQL query to display all the divnos in Member table but not in the Division.


Q24. Write a SQL query to display all the divnos in Division table but not in the Member table.


Q25. Write a SQL query to display common divnos from both the tables.