CBSE Class 11 Computer Table Joins And Indexes In Sql Worksheet

Short Answer Type Questions

Consider the following tables and give the commands based on the asked queries from Q1. To Q.4. 

Question: What will be the equijoin of the Orders and Parts?
Answer: SELECT ord.orderdon,ord.name,ord.partnum,pr.partnum,description FROM orders ord, parts pr WHERE ord.partnum=pr.partnum;

 Question: Find out the order details of parts having description like „Road. . . . . .‟
Answer: SELECT * FROM orders WHERE prtnum = (SELECT partnum FROM parts WHERE description LIKE ‗Road%‘;

Question: Determine the total order amount for part1 „Road Bike‟.
Answer: SELECT SUM(ord.quantity*pr.price) ‗Total Amount‘ FROM orders ord, parts prWHERE ord.partnum=pr.partnum AND pr.description=‘Road Bike‘;

Question: Given two tables –
Employee               Pay Table
employee_id          employee_id
last_name              salary
first_name              department
middle_name           supervisor
marital_status           

Question: Find out the salary taken by all KUMAR, i.e. all those who have their last_name as
„KUMAR‟
Answer: SELECT e.employee_id, e.last_name, ep.salary FROM employee e, Pay Table ep WHERE e.employee_id=ep.employee_id AND e.last_name=‘KUMAR‘;

Question: Table Employee has 4 records ad Table Dept has 3 records in it. Mr. Jain wants to display all information stored in both of these related tables. He forgot to specify equi-join condition in the query. How many rows will get displayed on execution ofthis query?
Answer: 12 Rows (4 X 3)

Question: What are table joins?
Answer: An SQL join clause – corresponding to a join operation in relational algebra – combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

Question: A table FLIGHT has 4 rows and 2 columns and another table AIR HOSTESS 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: 12 rows and 6 rows

Question: Name some types of joins that you can create on tables.
Answer: (i) Equi Join
(iii) Non-Equi-Join
(iii) Natural Join
(iv) Cross Join
(v) Left, Right Joins

Question: What are the advantages and disadvantages of table-indexes?
Answer: Advantages:
(i) Indexes are used to make the search fast.
(ii) Queries work with indexes very efficiently
(iii) Indexes are very much helpful in sorting
(iv) Index guaranties in uniquely identifying the records in the databases.

Disadvantages:
(i) The capability of insert, update and delete command reduces. As the index table‘s
updating is an overburden.
(ii) It consumes more memory.

Question: What is database index? Is it a table?
Answer: Table index is a kind of data structure which is used to fetch the data from database very fast.
An index keeps the location in index field in actual table, in ordered form. This is also a table
which stores the location of records within it.

Question: How many ways can you create index in?
Answer: There are two ways to create index –
(i) At the time of table creation.
(ii) Creating an index on already existing table.

Question: What are table Indexes? How are they useful?
Answer: Table index is a kind of data structure which is used to fetch the data from database very fast.An index keeps the location in index field in actual table, in ordered form. This is also a tablewhich stores the location of records within it.
Indexes are used to make the search fast. Queries work with indexes very efficiently. Indexes are very much helpful in sorting. Index guaranties in uniquely identifying the records in the databases.

Question: How is a cross join different from natural join? Give example.
Answer: A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. A NATURAL JOIN can be an INNERjoin, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along Ensure the following specification in cre with CROSS JOIN.This kind of result is called as Cartesian Product. If WHERE clause is used with CROSS JOIN, it functions like an INNERJOIN

Question: How is a left join different from natural join? Give example.
Answer: A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on thecommon columns in the two tables being joined. Common columns are columns that have thesame name in both tables. A NATURAL JOIN can be an INNERjoin, a LEFT OUTER join, or a RIGHT OUTER join.
The default is INNER join. And the LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Question: Why is it said that indexes should be created when needed?
Answer: (i) The capability of insert, update and delete command reduces. As the index table‘s updating is an overburden.
(ii) It consumes more memory. That‘s why it is said that when there is a need then create thendex. 

Question:

Answer: (i) mysql>SELECT * FROM Product WHERE Price between 40 and 120;
(ii) mysql> SELECT c.ClietnName, c.City, p.ProductName, p.Price FROM Product p, Client c
WHERE p.P_ID=c.P_ID;
(iii) mysql> UPDATE Product set Price=Price+20;