Question 1

Question 6Assertion. There can be joins where all records from one of tables appear irrespective of their matching rows in the other table.Reason. In LEFT JOIN, all rows from the left table appear in the result along with matching rows from the right table and NULL for the non-matching rows ; and same for the RIGHT JOIN where all rows came from the right table appear, along with only matching rows from the left table.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationIn certain types of joins, such as LEFT JOIN and RIGHT JOIN, all records from one table appear in the result set regardless of whether they have matching rows in the other table. In a LEFT JOIN, all rows from the left table are included along with matching rows from the right table and NULL values for non-matching rows in the right table. Similarly, in a RIGHT JOIN, all rows from the right table are included along with matching rows from the left table and NULL values for non-matching rows in the left table.

Question 2

Question 12What is the correct statement for describing the INTERSECT operation?It returns the common values from the results of any two different queriesIt returns the common rows of two different queries which have the same set of attributes in the select clauseIt returns the common rows of two different queries which have the same condition in the where clauseNone of these

Accepted Answer

It returns the common rows of two different queries which have the same set of attributes in the select clauseReason — The INTERSECT operation in SQL returns common rows from different queries. These queries must have the same number of columns with the same data types in corresponding positions in the SELECT clause.

Question 3

Question 9Can you join two tables without using the keyword JOIN ?

Accepted Answer

Yes, we can join two tables without using the JOIN keyword. This can be done using the comma (,) operator in the FROM clause to combine tables and using the WHERE clause to specify the join condition. The syntax to perform a JOIN between tableA and tableB on the common column "column1" is as follows:SELECT * FROM tableA, tableB WHERE tableA.column1 = tableB.column1;

Question 4

Question 1In a Database, there are two tables given below :Table : EMPLOYEEEMPLOYEEIDNAMESALESJOBIDE1SUMIT SINHA1100000102E2VIJAY SINGH TOMAR1300000101E3AJAY RAJPAL1400000103E4MOHIT RAMNANI1250000102E5SHAILJA SINGH1450000103Table : JOBJOBIDJOBTITLESALARY101President200000102Vice President125000103Administration Assistant80000104Accounting Manager70000105Accountant65000106Sales Manager80000Write SQL Queries for the following :To display employee ids, names of employees, job ids with corresponding job titles.To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.Identify foreign key in the table EMPLOYEE.Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

Accepted Answer

1.SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;Output+------------+-------------------+-------+--------------------------+
| EMPLOYEEID | NAME              | JOBID | JOBTITLE                 |
+------------+-------------------+-------+--------------------------+
| E1         | SUMIT SINHA       |   102 | VICE PRESIDENT           |
| E2         | VIJAY SINGH TOMAR |   101 | PRESIDENT                |
| E3         | AJAY RAJPAL       |   103 | ADMINISTARTION ASSISTANT |
| E4         | MOHIT RAMNANI     |   102 | VICE PRESIDENT           |
| E5         | SHAILJA SINGH     |   103 | ADMINISTARTION ASSISTANT |
+------------+-------------------+-------+--------------------------+
2.SELECT EMPLOYEE.NAME, EMPLOYEE.SALES, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.SALES > 1300000;Output+---------------+---------+--------------------------+
| NAME          | SALES   | JOBTITLE                 |
+---------------+---------+--------------------------+
| AJAY RAJPAL   | 1400000 | ADMINISTARTION ASSISTANT |
| SHAILJA SINGH | 1450000 | ADMINISTARTION ASSISTANT |
+---------------+---------+--------------------------+
3.SELECT EMPLOYEE.NAME, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.NAME LIKE '%SINGH%';Output+-------------------+--------------------------+
| NAME              | JOBTITLE                 |
+-------------------+--------------------------+
| VIJAY SINGH TOMAR | PRESIDENT                |
| SHAILJA SINGH     | ADMINISTARTION ASSISTANT |
+-------------------+--------------------------+
4. In the given tables, EMPLOYEE and JOB, the JOBID column in the EMPLOYEE table is a foreign key referencing the JOBID column in the JOB table.5.UPDATE EMPLOYEE
SET JOBID = 104
WHERE EMPLOYEEID = 'E4';OutputSELECT * FROM EMPLOYEE ;+------------+-------------------+---------+-------+
| EMPLOYEEID | NAME              | SALES   | JOBID |
+------------+-------------------+---------+-------+
| E1         | SUMIT AINHA       | 1100000 |   102 |
| E2         | VIJAY SINGH TOMAR | 1300000 |   101 |
| E3         | AJAY RAJPAL       | 1400000 |   103 |
| E4         | MOHIT RAMNANI     | 1250000 |   104 |
| E5         | SHAILJA SINGH     | 1450000 |   103 |
+------------+-------------------+---------+-------+

Question 5

Question 2Show the average salary for all departments with more than 3 people for a job.Table: EmplEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO8369SMITHCLERK89021990-12-18800NULL208499ANYASALESMAN86981991-02-201600300308521SETHSALESMAN86981991-02-221250500308566MAHADEVANMANAGER88391991-04-022985NULL208654MOMINSALESMAN86981991-09-2812501400308698BINAMANAGER88391991-05-012850NULL308839AMIRPRESIDENTNULL1991-11-185000NULL108844KULDEEPSALESMAN86981991-09-0815000308882SHIAVNSHMANAGER88391991-06-092450NULL108886ANOOPCLERK88881993-01-121100NULL208888SCOTTANALYST85661992-12-093000NULL208900JATINCLERK86981991-12-03950NULL308902FAKIRANALYST85661991-12-033000NULL208934MITACLERK88821992-01-231300NULL10

Accepted Answer

SELECT job, AVG(Sal) AS AvgSalary 
FROM Empl 
GROUP BY job HAVING COUNT(*) > 3;Output+----------+-----------+
| job      | AvgSalary |
+----------+-----------+
| CLERK    |    1037.5 |
| SALESMAN |      1400 |
+----------+-----------+

Question 6

Question 3Display only the jobs with maximum salary greater than or equal to 3000.Table: EmplEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO8369SMITHCLERK89021990-12-18800NULL208499ANYASALESMAN86981991-02-201600300308521SETHSALESMAN86981991-02-221250500308566MAHADEVANMANAGER88391991-04-022985NULL208654MOMINSALESMAN86981991-09-2812501400308698BINAMANAGER88391991-05-012850NULL308839AMIRPRESIDENTNULL1991-11-185000NULL108844KULDEEPSALESMAN86981991-09-0815000308882SHIAVNSHMANAGER88391991-06-092450NULL108886ANOOPCLERK88881993-01-121100NULL208888SCOTTANALYST85661992-12-093000NULL208900JATINCLERK86981991-12-03950NULL308902FAKIRANALYST85661991-12-033000NULL208934MITACLERK88821992-01-231300NULL10

Accepted Answer

SELECT Job 
FROM Empl 
GROUP BY Job HAVING MAX(Sal) >= 3000;Output+-----------+
| Job       |
+-----------+
| PRESIDENT |
| ANALYST   |
+-----------+

Question 7

Question 4Schemas of tables EMPL, Dept, SalaryGrade are being shown below :EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)
List the department names and the number of their employees.

Accepted Answer

SELECT d.DeptName AS Department_Name, COUNT(e.EMPNO) AS Number_of_Employees
FROM DEPT d, EMPL e
WHERE d.Deptno = e.DEPTNO
GROUP BY d.DeptName;

Question 8

Question 5Schemas of tables EMPL, Dept, SalaryGrade are being shown below :EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)
List the employee names and the name of their departments.

Accepted Answer

SELECT e.ENAME AS Employee_Name, d.DeptName AS Department_Name
FROM EMPL e, DEPT d
WHERE e.DEPTNO = d.Deptno;

Question 9

Question 7In a Database Karnataka_Sangam there are two tables with the instances given below :Table : STUDENTSADMNONAMECLASSSECRNADDRESSPHONE1211Meena12D4A-2632456781212Vani10D1B-2554567891213Meena12A11214Karish10B3AB-23445678901215Suraj11C2ZW124345677Table : SPORTSADMNOGAMECOACHNAMEGRADE1215CricketMr. RaviA1213VollyballMs. ChadhaB1211VollyballMr. GovardhanA1212Basket BallMr. TewaniBWrite 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.

Accepted Answer

Question 10

Question 8In a Database BANK there are two tables with a sample data given below :Table : EMPLOYEEENOENAMESALARYZONEAGEGRADEDEPT1Mona70000East40A102Muktar71000West45B203Nalini60000East26A104Sanaj65000South36A205Surya58000North30B30Table : DEPARTMENTDEPTDNAMEHOD10Computers120Economics230English5Note.ENAME refers to Employee NameDNAME refers to Department NameDEPT refers to Department CodeHOD refers to Employee number (ENO) of the Head of the Dept.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 DEPARTMENT 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.

Accepted Answer

Question 11

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

Accepted Answer

In a LEFT JOIN, all rows from the left table are included along with matching rows from the right table, and NULL values are used for non-matching rows in the right table. Conversely, a natural join is a join where only one of the identical columns from the joined tables exists.1. LEFT JOIN Example:SELECT name, lastname
FROM empl LEFT JOIN dept ON empl.id = dept.id;The result of this query would return name and lastname values from the empl table and all available values from the dept table. NULL is returned for non-existing values in dept table.2. NATURAL JOIN Example:SELECT * FROM empl NATURAL JOIN dept;The query returns all columns from empl and dept tables, with rows having matching values in their common columns.

Question 12

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

Accepted Answer

A cross join is a very basic type of join that simply pairs every row from one table with every row from another table. On the other hand, a natural join is a join where only one of the identical columns from the joined tables exists.1. CROSS JOIN Example:SELECT *
FROM students
CROSS JOIN subjects;This CROSS JOIN will pair every row from the students table with every row from the subjects table, resulting in a Cartesian product of the two tables.2. NATURAL JOIN Example:SELECT *
FROM students
NATURAL JOIN subjects;This NATURAL JOIN will automatically match the common columns and return rows where the values match.

Question 13

Question 13In a database there are two tables "Product" and "Client" as shown below :Table : ProductP_IDProductNameManufacturePriceP001MoisturiserXYZ40P002SanitizerLAC35P003Bath SoapCOP25P004ShampooTAP95P005Lens SolutionCOP350Table : ClientC_IDClientNameCityP_ID01Dreamz DisneyNew DelhiP00205Life Line IncMumbaiP0051298.4New DelhiP00115AppoloBangloreP003Write 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.

Accepted Answer

Question 14

Question 14In a Database School there are two tables Member and Division as shown below.Table : MemberEmpIdNamePayDivno1001Shankhya34000101003Ridhima32000501002Sunish4500020Table : DivisionDivnoDivnameLocation10MediaTF0220DanceFF0230ProductionSF01(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 Member Table and corresponding from Division table ?

Accepted Answer

Question 15

Question 15Consider following tables to answer below question :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to display all salesmen and customer located in Chennai.

Accepted Answer

SELECT s.name, s.city, c.cust_name, c.city
FROM Salesman s, Customer c 
WHERE s.salesman_id = c.salesman_id AND 
(s.city = 'Chennai' AND c.city = 'Chennai');

Question 16

Question 16Consider following tables to answer below question :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to display distinct salesman and their cities.

Accepted Answer

SELECT DISTINCT salesman_id, name, city
FROM Salesman;

Source: This question is from JOINS and SET Operations, Informatics Practices &mdash; Class 12, CBSE Board.
Key Concepts CoveredThis question tests your understanding of the following concepts from the chapter JOINS and SET Operations: Question, Consider, Tables, Salesman, City, Commission. These are fundamental topics in Informatics Practices that students are expected to master as part of the CBSE Class 12 curriculum.A thorough understanding of these concepts will help you answer similar questions confidently in your CBSE examinations. These topics are frequently tested in both objective and subjective sections of Informatics Practices papers. We recommend revising the relevant section of your textbook alongside practising these solved examples to build a strong foundation.
How to Approach This QuestionRead the question carefully and identify what is being asked. Break down complex questions into smaller parts. Use the terminology and concepts discussed in this chapter. Structure your answer logically — begin with a definition or key statement, then provide supporting details. Review your answer to ensure it addresses all parts of the question completely.
Key Points to RememberRead the question carefully and identify all parts before answering.
Use the terminology specific to this subject and chapter.
Review the textbook content for this chapter before attempting questions.
Practice writing concise, well-structured answers within time limits.
Related Questions from JOINS and SET OperationsQ1: Question 1 In a Database, there are two tables given below : Table : EMPLOYEE EMPLOYEEID NAME SALES...
Q10: Question 10 How is a left join different from a natural join ? Give example.
Q11: Question 11 How is a cross join different from natural join ? Give example.
Q12: Question 12 What is the correct statement for describing the INTERSECT operation? It returns the...
Q13: Question 13 In a database there are two tables "Product" and "Client" as shown below : Table :...
Practice more questions from JOINS and SET Operations &mdash; Informatics Practices, Class 12 CBSE

Question 17

Question 17Consider following tables to answer below question :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to list all the salesmen who do not have customers in 'DELHI' and 'JAMMU'.

Accepted Answer

SELECT s.name
FROM Salesman s
WHERE s.salesman_id NOT IN (
  SELECT DISTINCT c.salesman_id
  FROM Customer c
  WHERE c.city IN ('DELHI', 'JAMMU')
);

Question 18

Question 18Consider following tables to answer below question :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to list all the salesmen who have customers with grade 200 or higher.

Accepted Answer

SELECT s.name
FROM Salesman s, Customer c  
WHERE s.salesman_id = c.salesman_id AND c.grade >= 200;

Question 19

Question 19Consider following tables to answer below question :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to list all the salesmen who have customers with grade 200 from city 'JAMMU'.

Accepted Answer

SELECT s.name
FROM Salesman s, Customer c  
WHERE s.salesman_id = c.salesman_id AND (c.grade = 200 AND c.city = 'JAMMU');

Question 20

Question 20Consider following tables to answer questions 15-20 :Salesman (salesman_id, name, city, commission ) and

Customer (customer_id, cust_name, city, grade, salesman_id )
Write an SQL query to list all the customers whose salesmen have earned a commission of at least 15%.

Accepted Answer

SELECT c.cust_name
FROM Customer c, Salesman s
WHERE c.salesman_id = s.salesman_id
AND commission >= 0.15 ;

JOINS and SET Operations

Assertions And Reasons

Multiple Choice Questions

Type A Short Answer Questionsconceptual Questions

Type B Application Based Questions