JOINS and SET Operations

What is unrestricted join ?

Answer

The unrestricted join of two tables is a relation that contains all possible concatenations of all rows of both the participating relations (tables).

What is join ?

Answer

A join is a query that combines rows from two or more tables based on a condition.

How is equi-join different from non-equi-join?

Answer

In an equi-join, the values in the columns being joined are compared for equality using the "=" operator, and all columns from the joined tables are included in the result set, even if they are identical. On the other hand, a non-equi-join specifies a relationship other than equality between the columns using operators like <, >, <=, >=, !=, or <>.

What set operations can you perform through SQL ?

Answer

In SQL, set operations include UNION, INTERSECTION, and MINUS to combine the rows from two or more tables.

How is UNION different from UNION ALL ?

Answer

In SQL, UNION ALL retains all duplicate rows from tables in the final output, while UNION removes duplicate rows from the final output.

How is INTERSECT different from MINUS operation ?

Answer

In SQL, the INTERSECT operator returns rows common to two tables, while the MINUS (EXCEPT) operator retrieves unique rows from the first table that are not in the second table.

A ............... is a query that retrieves rows from more than one table or view :

1. Start
2. End
3. Join
4. All of these

Answer

Join

Reason — A join is a query that combines rows from two or more tables or views. In a join-query, more than one table are listed in FROM clause.

A condition given in a Join query is referred to as ............... .

1. Join in SQL
2. Join condition
3. Join in SQL & Condition
4. None of these

Answer

Join condition

Reason — A condition specified in a JOIN query that determines how the rows from two tables are combined is referred to as a "join condition."

Which of the following is not a join type ?

1. Empty join
2. Natural join
3. Equi-join
4. Right join

Answer

Empty join

Reason — The join types in SQL are natural join, equi-join, right join, inner join, left join, and non-equi-join.

Which product is returned in a join query having no join condition ?

1. Equi-join
2. Cartesian
3. Both Equijoins and Cartesian
4. Natural

Answer

Cartesian

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

The Cartesian product is also called ............... join.

1. Equi-join
2. Natural
3. Unrestricted Join
4. Restricted Join

Answer

Unrestricted Join

Reason — A Cartesian product is called an unrestricted join because all possible combinations of rows from multiple tables are created without any conditions.

In which type of join, the join condition contains an equality operator ?

1. Equi-join
2. Natural
3. Left Join
4. Right Join

Answer

Equi-join

Reason — An equi-join compares columns from two or more tables for equality using the "=" operator in the join condition.

In which type of join, duplicate columns are there ?

1. Equi-join
2. Natural
3. Left Join
4. Right Join

Answer

Equi-join

Reason — In an equi-join, all columns from the joining tables appear in the output, even if they are identical. This can result in duplicate columns in the output table.

In which type of join, no duplicate columns are there ?

1. Equi-join
2. Natural
3. Left Join
4. Right Join

Answer

Natural

Reason — In a natural join, no duplicate columns appear in the output table. This is because a natural join is a type of join in which only one of the identical columns (coming from the joined tables) is included in the output.

With SELECT statement used for joins, the USING subclause produces ............... join.

1. Equi-join
2. Natural
3. Left Join
4. Right Join

Answer

Natural

Reason — The USING subclause in a SELECT statement produces a natural join, where columns with the same name in the joined tables are automatically used as join conditions, resulting in a natural join operation.

With SELECT statement used for joins, the ON subclause produces ............... join.

1. Equi-join
2. Natural
3. Left Join
4. Right Join

Answer

Equi-join

Reason — The ON subclause in a SELECT statement produces an equi-join, where columns from the joined tables are compared for equality using the specified conditions in the ON clause.

What is the correct statement for describing the UNION operation ?

1. It combines the rows of any two different queries
2. It combines the unique rows of two different queries which have the same set of attributes in the select clause
3. It combines the rows of two different queries which have the same condition in the where clause
4. It gives the Cartesian product of the results of any two queries

Answer

It combines the unique rows of two different queries which have the same set of attributes in the select clause

Reason — The UNION operation in SQL combines the results of two queries into a single result set, including only distinct rows from both queries. The queries must have the same number of columns having similar data types and order.

What is the correct statement for describing the INTERSECT operation?

1. It returns the common values from the results of any two different queries
2. It returns the common rows of two different queries which have the same set of attributes in the select clause
3. It returns the common rows of two different queries which have the same condition in the where clause
4. None of these

Answer

It returns the common rows of two different queries which have the same set of attributes in the select clause

Reason — 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.

What is the correct statement for describing the EXCEPT operation ?

1. It excludes all the rows present in both the queries
2. It includes the rows of the second query but excludes the results of the first query
3. It includes the rows of the first query but excludes the results of the second query
4. It includes all the rows of both queries but removes duplicates

Answer

It includes the rows of the first query but excludes the results of the second query

Reason — The EXCEPT (or MINUS) operator in SQL is used to retrieve unique rows from the first query, specifically those rows that exist in the first query but not in the second query.

What is the other name of MINUS operator?

1. UNION
2. UNION ALL
3. EXCEPT
4. INTERSECT

Answer

EXCEPT

Reason — The other name for the MINUS operator in SQL is EXCEPT.

Examine the structure of the EMPL and DEPT tables:

Table : EMPL

Table : DEPT

Evaluate this SQL statement :

SELECT employee_id, e.department_id, department_ name, salary 
FROM EMPL e, DEPT d
WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement ?

(a)

SELECT employee_id, department_id, department_name, salary 
FROM EMPL
WHERE department_id MATCHES department_id of DEPT;

(b)

SELECT employee_id, department_id, department_name,salary 
FROM EMPL
NATURAL JOIN DEPT;

(c)

SELECT employee_id, d.department_id, department_name,salary
FROM EMPL e 
JOIN DEPT d 
ON e.department_id = d.department_id;

(d)

SELECT employee_id, department_id, department_name,salary
FROM EMPL 
JOIN DEPT 
USING (e. department _id, d.department_id);

Answer

SELECT employee_id, d.department_id, department_name, salary
FROM EMPL e 
JOIN DEPT d 
ON e.department_id = d.department_id;

Reason — Both SQL statements are equivalent as they perform an inner join between the EMPL and DEPT tables based on their department_id columns. The first SQL statement uses implicit join syntax with a WHERE clause to specify the join condition, while the second SQL statement uses explicit join syntax with the JOIN keyword and ON clause.

An SQL join clause combines records from two or more tables in a database.

An equi-join is a specific type of join that uses only equality comparisons in the join-condition.

Left join selects all data starting from the left table and matching rows in the right table.

Right join is a reversed version of the left join.

Inner join produces a data set that includes only those rows from the left table which have matching rows from the right table.

An INTERSECT query returns the intersection of 2 or more tables.

UNION ALL returns all rows from both the SELECT queries and it does not remove duplicate rows between the various SELECT statements.

UNION returns all rows from both the SELECT queries after removing duplicate rows between the two SELECT statements.

The MINUS operation combines results of two SELECT statements and returns only those rows in the final result, which belong to the first set of the result.

The INTERSECT operation can be simulated in MySQL using INNER JOIN.

Non-equi-join is the name of Natural join.

Answer

False

Reason — A non-equi-join is a query that specifies a relationship other than equality between columns, often using comparison operators like >, <, >=, <=, etc. On the other hand, a join in which only one of the identical columns from joined tables exists is called a natural join.

Natural join contains the duplicate columns.

Answer

False

Reason — Natural join is a join in which only one of the identical columns from joined tables exists.

Equi-join contains the duplicate columns.

Answer

True

Reason — In an equi-join, all columns from the joining tables are included in the output, even if they are identical, which can lead to duplicate columns in the output.

Inner Join combines only the matching rows from the two tables.

Answer

True

Reason — An inner join in SQL combines only the rows from two tables that have matching values in the specified columns, based on the join condition. Rows that do not have matching values in both tables are excluded from the result set.

A join condition can only use equality for comparison.

Answer

False

Reason — A join condition can use various types of comparisons, including equality (=) and other logical operators such as "<>", "<", ">", "<=", ">=".

Left join has all the rows from the Left table.

Answer

True

Reason — A left join includes all rows from the left table, regardless of whether there are matches in the right table. For unmatched rows from the left table, NULL values are shown in the columns of the right table.

Outer join has all the rows from both the tables.

Answer

True

Reason — The outer join returns all the rows from both tables being joined, including any rows that do not have a match in the other table.

MINUS operator is also called EXCEPT operator.

Answer

True

Reason — MINUS operator is also called EXCEPT operator as both are used to return rows that are in the first table and not in the second table.

UNION returns unique rows.

Answer

True

Reason — The UNION operator returns all unique rows from the SELECT queries.

UNION and UNION ALL produce similar results.

Answer

False

Reason — UNION ALL will retain all the duplicate rows from tables in the final output, while UNION will remove the duplicate rows and include only distinct rows in the final output.

Assertion. Cartesian product and joins are related.

Reason. Cartesian product is a join without any condition.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The Cartesian product and joins are related because a Cartesian product is a join without any condition. While a join is a query that combines rows from two or more tables based on a specified condition, a Cartesian product (or cross join) pairs every row from one table with every row from another, resulting in a set that includes all possible combinations of rows.

Assertion. The cardinality of a cartesian product can be predetermined, unlike joins.

Reason. The cardinality of a cartesian product is cardinality of table 1 x cardinality of table 2.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The cardinality of a Cartesian product can be predetermined because it is the product of the cardinalities of the two participating tables, i.e., n1 * n2 where n1 and n2 are cardinalities of the two participating tables. This is unlike joins, where the cardinality depends on the specific conditions used to match the rows from the involved tables.

Assertion. There must be some identical columns in tables in order to get cartesian product.

Reason. The cartesian product returns all possible combinations of rows from the participating tables.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is false but R is true.

Explanation
The cartesian product does not require any identical columns, it pairs each row from one table with every row from another table, creating all possible combinations of rows and returning them.

Assertion. The join in which columns are compared for equality, is called an Equi-join.

Reason. The join condition can only compare columns with equal sign.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
In an equi-join, the values in the columns being joined are compared for equality. The join condition can use various types of comparisons, including equality (=) and other logical operators such as "<>", "<", ">", "<=", ">=".

Assertion. The join, in which columns are compared for equality, is called an Equi-join.

Reason. In non-equi joins, the columns are compared with non-equality conditional operators.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
The join in which columns are compared for equality is called an Equi-join. A non-equi-join involves comparing columns with non-equality conditional operators, such as '<>', '<', '>', '<=', and '>=', to specify relationships other than equality between the columns.

Assertion. 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.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
In 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.

Assertion. The UNION clause combines the rows of the participating tables, removing the duplicate rows.

Reason. To retain the duplicate rows in a UNION query, UNION ALL is used.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The UNION clause combines the rows of the participating tables, removing duplicate rows from the final output. On the other hand, UNION ALL retains all duplicate rows from the tables in the final output.

Assertion. The MINUS and INTERSECT operators are similar.

Reason. The MINUS operator returns only the rows from the first result set while INTERSECT returns the common rows of both the result sets.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is false but R is true.

Explanation
The MINUS operator returns the rows that are only in the first result set but not in the second, while the INTERSECT operator returns all rows that are in both result sets. Hence, the MINUS and INTERSECT operators are different.

What is Cartesian product ? How is it related to join ?

Answer

The Cartesian product of two tables is a relation that contains all possible concatenations of all rows of both the participating relations (tables).

The Cartesian product and joins are related because a Cartesian product, also known as a cross join, is a very basic type of join without any condition. It pairs every row from one table with every row from another, creating a complete combination of all rows. Meanwhile, a join is a query that combines rows from two or more tables based on a specified condition.

What is a join ? How is natural join different from an equi-join ?

Answer

A join is a query that combines rows from two or more tables based on a condition.

The join in which only one of the identical columns from the joined tables exists is called a natural join, while the join in which columns are compared for equality is called an equi-join.

What is a table alias ? What is the purpose of table alias ?

Answer

A table alias is a temporary label given along with table name in the FROM clause.

Table aliases are used to give a temporary name to a table within a SQL query, making it easier to read and reference columns, especially when working with long table names, self-joins, or multiple references to the same table.

Define an equi-join. What is non-equi-join ?

Answer

The join, in which columns are compared for equality, is called equi-join. The comparison operator used is "=".

A non-equi-join is a query that specifies some relationship other than equality between the columns. The comparison operators used are '<>', '<', '>', '<=', and '>='.

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

Answer

A join is a query that combines rows from two or more tables based on a condition.

In MySQL, we can create two main types of joins: unrestricted joins, which include the Cross Join or Cartesian Product, and restricted joins, which include Equi Join, Non-Equi Join, Natural Join, Left Join, and Right Join.

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

Answer

The two ways to create cartesian product of two tables in MySQL are as follows:

1. Creating Cartesian Product Without Join Condition:

SELECT * 
FROM table1 JOIN table2;

2. Creating Cartesian Product Using CROSS JOIN Clause:

SELECT * 
FROM table1
CROSS JOIN table2;

How is a left join different from a natural join ?

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.

How is a cross join different from natural join ?

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.

Can you join two tables without using the keyword JOIN ?

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;

What is the difference between ON and USING join-clauses ?

Answer

The difference between ON and USING sub-clauses of JOIN clause of SELECT is that ON clause requires a complete join-condition whereas USING clause requires just the name of a join field. USING subclause produces natural join whereas ON clause produces equi-join.

A table STUDENT has 4 rows and 2 columns and another table TEACHER has 3 rows and 4 columns. How many rows and columns will be there if we obtain the Cartesian product of these two tables ?

Answer

To obtain the Cartesian product of two tables, we multiply the number of rows in the first table by the number of rows in the second table, and the resulting table will have the sum of columns from both tables.

The Cartesian product of "STUDENT" and "TEACHER" will have 4 * 3 = 12 rows and 2 + 4 = 6 columns.

Therefore, the resulting Cartesian product will have 12 rows and 6 columns.

What does UNION do ?

Answer

The SQL UNION operator is used to combine the results of two or more queries into a single result set. It removes duplicate rows from the final output.

Are UNION and UNION ALL the same ? Why ?

Answer

No, UNION and UNION ALL are not same. UNION ALL will retain all the duplicate rows from tables in the final output while UNION will remove the duplicate rows from the final output.

What does INTERSECT do ?

Answer

In SQL, INTERSECT operation returns all rows that are in both result sets.

What does MINUS operation do ?

Answer

The MINUS operator returns the rows that are only in the first result set but not in the second result set.

In a Database, there are two tables given below :

Table : EMPLOYEE

Table : JOB

Write SQL Queries for the following :

1. To display employee ids, names of employees, job ids with corresponding job titles.

2. To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.

3. To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.

4. Identify foreign key in the table EMPLOYEE.

5. Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

Answer

1.

SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;

+------------+-------------------+-------+--------------------------+
| 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;

+---------------+---------+--------------------------+
| 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%';

+-------------------+--------------------------+
| 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';

SELECT * 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 |
+------------+-------------------+---------+-------+

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

Table: Empl

Answer

SELECT job, AVG(Sal) AS AvgSalary 
FROM Empl 
GROUP BY job HAVING COUNT(*) > 3;

+----------+-----------+
| job      | AvgSalary |
+----------+-----------+
| CLERK    |    1037.5 |
| SALESMAN |      1400 |
+----------+-----------+

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

Table: Empl

Answer

SELECT Job 
FROM Empl 
GROUP BY Job HAVING MAX(Sal) >= 3000;

+-----------+
| Job       |
+-----------+
| PRESIDENT |
| ANALYST   |
+-----------+

Schemas 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.

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;

Schemas 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.

Answer

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

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

Answer

A join is a query that combines rows from two or more tables based on a condition.

In MySQL, we can create two main types of joins: unrestricted joins, which include the Cross Join or Cartesian Product, and restricted joins, which include Equi Join, Non-Equi Join, Natural Join, Left Join, and Right Join.

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.

Answer

(i)

SELECT COUNT(ADDRESS) FROM STUDENTS;

+----------------+
| COUNT(ADDRESS) |
+----------------+
|              4 |
+----------------+

(ii)

SELECT S.NAME, S.CLASS, SP.GRADE 
FROM STUDENTS S, SPORTS SP 
WHERE S.ADMNO = SP.ADMNO;

+-------+-------+-------+
| NAME  | CLASS | GRADE |
+-------+-------+-------+
| MEENA |    12 | A     |
| VANI  |    10 | B     |
| MEENA |    12 | B     |
| SURAJ |    11 | A     |
+-------+-------+-------+

(iii)

SELECT S.NAME, SP.COACHNAME 
FROM STUDENTS S, SPORTS SP 
WHERE S.ADMNO = SP.ADMNO;

+-------+--------------+
| NAME  | COACHNAME    |
+-------+--------------+
| MEENA | MR.GOVARDHAN |
| VANI  | MR.TEWANI    |
| MEENA | MS.CHADHA    |
| SURAJ | MR.RAVI      |
+-------+--------------+

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

Table : EMPLOYEE

Table : DEPARTMENT

Note.

• ENAME refers to Employee Name
• DNAME refers to Department Name
• DEPT refers to Department Code
• HOD 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.

Answer

(i)

SELECT E.ENO, E.ENAME, E.SALARY, D.DNAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT = D.DEPT AND (E.AGE BETWEEN 25 AND 35);

+-----+--------+--------+-----------+
| ENO | ENAME  | SALARY | DNAME     |
+-----+--------+--------+-----------+
|   3 | NALINI |  60000 | COMPUTERS |
|   5 | SURYA  |  58000 | ENGLISH   |
+-----+--------+--------+-----------+

(ii)

SELECT D.DNAME, E.ENAME
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.ENO = D.HOD;

+-----------+---------+
| DNAME     | ENAME   |
+-----------+---------+
| COMPUTERS | MONA    |
| ECONOMICS | MUKHTAR |
| ENGLISH   | SURYA   |
+-----------+---------+

(iii)

SELECT ENAME, SALARY, ZONE, (SALARY * 30/100) AS INCOME_TAX
FROM EMPLOYEE;

+---------+--------+-------+------------+
| ENAME   | SALARY | ZONE  | INCOME_TAX |
+---------+--------+-------+------------+
| MONA    |  70000 | EAST  | 21000.0000 |
| MUKHTAR |  71000 | WEST  | 21300.0000 |
| NALINI  |  60000 | EAST  | 18000.0000 |
| SANAJ   |  65000 | SOUTH | 19500.0000 |
| SURYA   |  58000 | NORTH | 17400.0000 |
+---------+--------+-------+------------+

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

Answer

The two ways to create cartesian product of two tables in MySQL are as follows:

1. Creating Cartesian Product Without Join Condition:

SELECT * 
FROM table1 JOIN table2;

2. Creating Cartesian Product Using CROSS JOIN Clause:

SELECT * 
FROM table1
CROSS JOIN table2;

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

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.

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

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.

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

Answer

The "TRAINS" table in the database has a degree of 3, indicating it contains three attributes or columns and it has a cardinality of 8, indicating it consists of eight rows or tuples. Therefore, the "TRAINS" table has 8 rows and 3 columns.

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.

Answer

(i)

SELECT * 
FROM PRODUCT 
WHERE PRICE BETWEEN 40 AND 120;

+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ          |    40 |
| P004 | SHAMPOO     | TAP          |    95 |
+------+-------------+--------------+-------+

(ii)

SELECT C.CLIENTNAME, C.CITY, P.PRODUCTNAME, P.PRICE 
FROM CLIENT C, PRODUCT P 
WHERE C.P_ID = P.P_ID;

+---------------+-----------+---------------+-------+
| CLIENTNAME    | CITY      | PRODUCTNAME   | PRICE |
+---------------+-----------+---------------+-------+
| DREAMZ DISNEY | NEW DELHI | SANITIZER     |    35 |
| LIFE LINE INC | MUMBAI    | LENS SOLUTION |   350 |
| 98.4          | NEW DELHI | MOISTURISER   |    40 |
| APPOLO        | BANGALORE | BATH SOAP     |    25 |
+---------------+-----------+---------------+-------+

(iii)

UPDATE PRODUCT 
SET PRICE = PRICE + 20;

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 Member Table and corresponding from Division table ?

Answer

(i) The foreign key in the "Member" table is the "Divno" column, which references the "Divno" column in the "Division" table.

(ii)

SELECT m.Name, d.Divname 
FROM Member m JOIN Division d ON m.Divno = d.Divno;

+----------+---------+
| Name     | Divname |
+----------+---------+
| SHANKHYA | Media   |
| SUNISH   | Dance   |
+----------+---------+

Consider 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.

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');

Consider 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.

Answer

SELECT DISTINCT salesman_id, name, city
FROM Salesman;

Consider 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'.

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')
);

Consider 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.

Answer

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

Consider 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'.

Answer

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

Consider 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%.

Answer

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

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

Table : Member

Table : Division

Answer

SELECT DIVNO FROM MEMBER 
UNION ALL
SELECT DIVNO FROM DIVISION;

+-------+
| DIVNO |
+-------+
|    10 |
|    20 |
|    50 |
|    10 |
|    20 |
|    30 |
+-------+

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

Table : Member

Table : Division

Answer

SELECT DISTINCT DIVNO FROM MEMBER 
UNION 
SELECT DISTINCT DIVNO FROM DIVISION;

+-------+
| DIVNO |
+-------+
|    10 |
|    20 |
|    50 |
|    30 |
+-------+

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

Table : Member

Table : Division

Answer

SELECT m.Divno 
FROM Member m 
LEFT JOIN Division d ON m.Divno = d.Divno 
WHERE d.Divno IS NULL;

+-------+
| Divno |
+-------+
|    50 |
+-------+

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

Table : Member

Table : Division

Answer

SELECT D.Divno
FROM Division D
LEFT JOIN Member M ON D.Divno = M.Divno
WHERE M.Divno IS NULL;

+-------+
| Divno |
+-------+
|    30 |
+-------+

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

Table : Member

Table : Division

Answer

SELECT Divno 
FROM Division NATURAL JOIN Member;

+-------+
| Divno |
+-------+
|    10 |
|    20 |
+-------+

Consider the following tables TRANSPORTER and DRIVER and answer the questions that follow :

Table : TRANSPORTER

Table : DRIVER

(i) Can the column 'PHONE' be set as the primary key of the table DRIVER ? Give reason(s).

Or

Identify the Primary key in the table 'DRIVER'. Give reason(s) for your choice.

(ii) In the PHONE column of "DRIVER" table, each phone number is taking same number of bytes (being padded with spaces to specified length) irrespective of the number of characters actually stored in that column. Which data type has been used for PHONE column ?

Or

Identify data type and size to be used for column DRIVERID.

Answer

(i) No, the column 'PHONE' cannot be set as the primary key of the table "DRIVER" because it contains NULL values and primary keys must have unique, non-NULL values for each row.

Or

The primary key in the 'DRIVER' table is 'DRIVERID' because it uniquely identifies each driver, and it does not allow null values, ensuring that each row can be uniquely identified.

(ii) The data type used for the "PHONE" column is "CHAR", which is a fixed-length data type. "CHAR" pads the values with spaces to ensure each value takes up the same amount of storage space.

Or

The data type and size for the column "DRIVERID" would be "VARCHAR(4)" to accommodate the alphanumeric values of length 4.

With reference to the below given tables, write commands in SQL for (i) to (iii) :

Table : TRANSPORTER

Table : DRIVER

(i) To display OrderId, Item being transported, DriverId and names of Drivers for all the orders that are being transported by drivers with 'A' grade.

(ii) To display DriverId, Names of drivers and Travel dates for drivers who are travelling after 1st March, 2019.

Or

How many rows will be there in Cartesian product of the two tables in consideration here ?

(iii) To display Orderld, Driverld and names of Drivers for all the orders that are transporting TELEVISION.

Answer

(i)

SELECT T.ORDERID, T.ITEM, D.DRIVERID, D.DRIVERNAME 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND D.DRIVERGRADE = 'A';

+---------+--------------+----------+--------------+
| ORDERID | ITEM         | DRIVERID | DRIVERNAME   |
+---------+--------------+----------+--------------+
|    1121 | REFRIGERATOR | D101     | RADHEY SHYAM |
|    1122 | TELEVISION   | D101     | RADHEY SHYAM |
+---------+--------------+----------+--------------+

(ii)

SELECT D.DRIVERID, D.DRIVERNAME, T.TRANSPORTDATE 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND T.TRANSPORTDATE > '2019-03-01';

+----------+--------------+---------------+
| DRIVERID | DRIVERNAME   | TRANSPORTDATE |
+----------+--------------+---------------+
| D103     | TIMSY YADAV  | 2019-03-19    |
| D101     | RADHEY SHYAM | 2019-04-12    |
| D101     | RADHEY SHYAM | 2019-05-06    |
| D103     | TIMSY YADAV  | 2019-05-07    |
| D102     | JAGAT SINGH  | 2019-06-15    |
+----------+--------------+---------------+

Or

The number of rows in the Cartesian product of the two tables "TRANSPORTER" and "DRIVER" would be the product of the number of rows in each table. In this case, since the "TRANSPORTER" table has 5 rows and the "DRIVER" table has 4 rows, the Cartesian product would have 5 * 4 = 20 rows.

(iii)

SELECT T.ORDERED, D.DRIVERID, D.DRIVERNAME 
FROM TRANSPORTER T, DRIVER D 
WHERE T.DRIVERID = D.DRIVERID AND T.ITEM = 'TELEVISION';

+---------+----------+--------------+
| ORDERED | DRIVERID | DRIVERNAME   |
+---------+----------+--------------+
|    1120 | D103     | TIMSY YADAV  |
|    1122 | D101     | RADHEY SHYAM |
+---------+----------+--------------+