TABLE JOINS AND INDEXES IN SQL || MYSQL || CBSE

 

TABLE JOINS AND INDEXES IN SQL

Short Answer Type Questions 

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

 

Q.1     Find out the Cartesian product of the table Orders and Parts.

Ans: SELECT * FROM Orders, Parts;

Q.2 What will be the equijoin of the Orders and Parts?

Ans: SELECT ord.orderdon,ord.name,ord.partnum,pr.partnum,description FROM orders ord, parts pr

WHERE ord.partnum=pr.partnum;

Q.3    Determine the total order amount for part1 „Road Bike‟.

Ans: SELECT SUM(ord.quantity*pr.price) ‗Total Amount‘ FROM orders ord, parts prWHERE ord.partnum=pr.partnum AND pr.description=‘Road Bike‘;

Q.4          Find out the order details of parts having description like „Road. . . . . .‟

Ans: SELECT * FROM orders WHERE prtnum = (SELECT partnum FROM parts WHERE description

LIKE ‗Road%‘;

Q.5     Given two tables – 

                                      Employee           PayTable

employee_id employee_id last_name salary first_name department

                                      middle_name supervisor 

marital_status

Find out the salary taken by all KUMAR, i.e. all those who have their last_name as

„KUMAR‟

Ans: SELECT e.employee_id, e.last_name, ep.salary FROM employee e, PayTable ep WHERE

e.employee_id=ep.employee_id AND e.last_name=‘KUMAR‘;

Q.6     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?

Ans: 12 rows and 6 rows 

Q.7     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 of this query?

Ans: 12 Rows (4 X 3)

Q.8  What are table joins?

Ans: 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. Q.9 Name some types of joins that you can create on tables. Ans: (i)            Equi Join 

              (iii)      Non-Equi-Join

(iii)        Natural Join

(iv)       Cross Join

(v)        Left, Right Joins

Q.10    What are table Indexes? How are they useful?

Ans: 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. 

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.

Q.11    What are the advantages and disadvantages of table-indexes?

Ans: 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.

Q.12    What is database index? Is it a table?

Ans: 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. 

Q.13    How many ways can you create index in?

Ans: There are two ways to create index  -  (i) At the time of table creation.

              (ii)               Creating an index on already existing table. 

Q.14 Why is it said that indexes should be created when needed?

Ans: (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 the ndex.

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

Ans: 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.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.

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

Ans: 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

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

Q.17 

 

 

Ans: (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;