108 + Interview Questions in RELATIONAL DATABASE IN GENERAL AWARENESS Page 3 InterviewSolution

101.	Consider the table EXAM given below. Write SQL commands for (i) to (iv) and output for (v) to (vii). Table : EXAMS.No.NameStipendSubjectAverageDivision1Karan400English68First2Aman600Mathematics72First3]aved500Accounts67First4Bishakha200Informatics55Second5Sugandha400History35Third6Suparna550Geography45Third(i) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.(ii) To display a report listing NAME, SUBJECT and Annual stipend received assuming that the Stipend column has monthly stipend.(iii) To count the number of students who have either accounts or informatics as subject.(iv) To insert a new row in the table: 6, Mohan, 500, English, 73, Second.(v) SELECT AVG(Stipend) from EXAM where DIVISION = "THIRD";(vi) SELECT COUNT(DISTINCT Subject) FROM EXAM;(vii) SELECT MlN(Average) from EXAM where Subject ="English";
Answer» (i) SELECT NAME FROM Exam WHERE Division ="First" ORDER By Name; (ii) SELECT NAME, SUBJECT, STIPEND * 12 FROM EXAM; (iii) SELECT COUNT(*) FROM EXAM WHERE SUBJECT IN ('Accounts','Informatics'); (iv) INSERT INTO EXAM VALUES (6,"Mohan"' 500, "English", 73, "SECOND"); (v) 475 (vi) 6 (vii) 68

101.

Consider the table EXAM given below. Write SQL commands for (i) to (iv) and output for (v) to (vii). Table : EXAMS.No.NameStipendSubjectAverageDivision1Karan400English68First2Aman600Mathematics72First3]aved500Accounts67First4Bishakha200Informatics55Second5Sugandha400History35Third6Suparna550Geography45Third(i) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.(ii) To display a report listing NAME, SUBJECT and Annual stipend received assuming that the Stipend column has monthly stipend.(iii) To count the number of students who have either accounts or informatics as subject.(iv) To insert a new row in the table: 6, Mohan, 500, English, 73, Second.(v) SELECT AVG(Stipend) from EXAM where DIVISION = "THIRD";(vi) SELECT COUNT(DISTINCT Subject) FROM EXAM;(vii) SELECT MlN(Average) from EXAM where Subject ="English";

Answer»

(i) SELECT NAME FROM Exam WHERE Division ="First" ORDER By Name;

(ii) SELECT NAME, SUBJECT, STIPEND * 12 FROM EXAM;

(iii) SELECT COUNT(*) FROM EXAM WHERE SUBJECT IN ('Accounts','Informatics');

(iv) INSERT INTO EXAM VALUES (6,"Mohan"' 500, "English", 73, "SECOND");

(v) 475

(vi) 6

(vii) 68

Discussion

102.	Table "Emp" is shown below Write commands in SQL for (i) to (iv) and output for (v) and (vi)IDNAMEAGEADDRESSSALARYPHONE1Siddharth25A-4, Ashok Vihar, Delhi62000981107566562Chavi23B-21, Model Town, Mumbai71000991134239893Karan26KC-24, North Avenue, Bhopal65000981053935784Raunaq22A-152, Gomti Nagar, Lucknow89000991013935765Kunal27B-5/45, Uday Park, Delhi8000097653455654(i) To display list of all employees below 25 years old.(ii) To list names and respective salaries in descending order of salary.(iii) To count the number of employees with names starting with 'K'(iv) To list names and addresses of those persons who have 'Delhi' in their address.(v) SELECT Name, Salary FROM Emp where salary between 50000 and 70000;(vi) SELECT Name, phone from emp where phone like '99%';
Answer» OUTPUT (v) Siddharth 62000 Karan 65000 (vi) Chavi 99713423989 Raunaq 99101393576

102.

Table "Emp" is shown below Write commands in SQL for (i) to (iv) and output for (v) and (vi)IDNAMEAGEADDRESSSALARYPHONE1Siddharth25A-4, Ashok Vihar, Delhi62000981107566562Chavi23B-21, Model Town, Mumbai71000991134239893Karan26KC-24, North Avenue, Bhopal65000981053935784Raunaq22A-152, Gomti Nagar, Lucknow89000991013935765Kunal27B-5/45, Uday Park, Delhi8000097653455654(i) To display list of all employees below 25 years old.(ii) To list names and respective salaries in descending order of salary.(iii) To count the number of employees with names starting with 'K'(iv) To list names and addresses of those persons who have 'Delhi' in their address.(v) SELECT Name, Salary FROM Emp where salary between 50000 and 70000;(vi) SELECT Name, phone from emp where phone like '99%';

Answer»

OUTPUT

(v) Siddharth 62000 Karan 65000

(vi) Chavi 99713423989 Raunaq 99101393576

Discussion

103.	Consider the table SHOPPE given below. Write Command in MySQL for (i) to (iv) and output for (v) to (vii). Table : SHOPPECodeItemCompanyQtyCityQty102BiscuitHide & Seek100Delhi10.00103JamKishan110Kolkata25.00101CoffeeNestle200Kolkata55.00106SauceMaggi56Mumbai55.00107CakeBritania72Delhi10.00104MaggiNestle150Mumbai10.00105ChocolateCadbury170Delhi25.00(i) To display names of the items whose name starts with 'C' in ascending order of Price.(ii) To display code, Item name and City of the products whose quantity is less than 100.(iii) To count distinct Company from the table.(iv) To insert a new row in the table Shoppe '110'; Pizza', 'Papa Jones', 120, "Kolkata", 50.0(v) SELECT Item FROM SHOPPE where Item IN (''Jam" ,"Coffee");(vi) Select COUNT(distinct(City)) from SHOPPE;(vii) Select MIN(Qty) from SHOPPE where City ="Mumba";
Answer» (i) SELECT Item FROM SHOPPE Where Item like 'c%' order by price; (ii) SELECT Code, Item, City from Shoppe Where Qty < 100; (iii) SELECT Count(Distinct(Company)) from SHOPPE; (iv) Insert Into SHOPPE Values (110, 'pizza', 'papa Jones',120,'kolkata',50.0); (v) Item jam Coffee (vi) 3 (vii) MIN(QTY) 56

103.

Consider the table SHOPPE given below. Write Command in MySQL for (i) to (iv) and output for (v) to (vii). Table : SHOPPECodeItemCompanyQtyCityQty102BiscuitHide & Seek100Delhi10.00103JamKishan110Kolkata25.00101CoffeeNestle200Kolkata55.00106SauceMaggi56Mumbai55.00107CakeBritania72Delhi10.00104MaggiNestle150Mumbai10.00105ChocolateCadbury170Delhi25.00(i) To display names of the items whose name starts with 'C' in ascending order of Price.(ii) To display code, Item name and City of the products whose quantity is less than 100.(iii) To count distinct Company from the table.(iv) To insert a new row in the table Shoppe '110'; Pizza', 'Papa Jones', 120, "Kolkata", 50.0(v) SELECT Item FROM SHOPPE where Item IN (''Jam" ,"Coffee");(vi) Select COUNT(distinct(City)) from SHOPPE;(vii) Select MIN(Qty) from SHOPPE where City ="Mumba";

Answer»

(i) SELECT Item FROM SHOPPE Where Item like 'c%' order by price;

(ii) SELECT Code, Item, City from Shoppe Where Qty < 100;

(iii) SELECT Count(Distinct(Company)) from SHOPPE;

(iv) Insert Into SHOPPE Values (110, 'pizza', 'papa Jones',120,'kolkata',50.0);

(v) Item jam Coffee

(vi) 3

(vii) MIN(QTY) 56

Discussion

104.

Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv) and output for (v) to (viii). Table : FITNESSPCODEPNAMEPRICEMANUFACTURERP1Treadmill21000CoscoreP2Bike20000AoneP3Cross Tiainer14000ReliableP4Multi Gym34000CoscoreP5Massage Chair5500RegroseneP6Belly Vibrator Belt6500Ambaway(i) To display the names of all the products with price more than 20000.(ii) To display the names of all the products by the manufacturer "Aone".(iii) To change the price data of all the productsby applying 25% discount reduction.(iv) To add a new row for product with details: "P7","Vibro Exerciset", 28000,'Aone".(V) SELECT * FROM FITNESS WHERE MANUFACTURERNAME, LIKE " % e" .(vi) SELECT COUNT((DISTINCT)MANUFACTURER) FROM FITNESS.(vii) SELECT MAX(PRICE) FROM FITNESS.

Answer»

(i) SELECT PNAME, PRICE FROM FITNESS WHERE PRICE > 2000;

(ii) SELECT PNAME FROM FITNESS WHERE MANUFACTURER = "Aone";

(iii) UPDATE FITNESS SET PRICE=PRICE * (PRICE*0.25);

(iv) INSERT INTO FITNESS VALUES("P7", "Vibro Exerciser", 28000, "Aone");

(v) Error: There is no column MANUFACTURER NAME

PCODE	PNAME	PRICE	MANUFACTURER
P1	Treadmill	21000	Coscore
P2	Bike	20000	Aone
P3	Cross Trainer	14000	Reliable
P4	Multi Gym	34000	Coscore
P5	Massage Chair	5500	Regrosene

(vi) COUNT(DISTINCT(MANUFACTURER)) 5

(vii) MAX(PRICE) 6500

Discussion

105.	Rewrite the following SQL statement after correcting error(s). Underline the corrections made. INSERT IN STUDENT(RNO,MARKS) VALUE (5, 78.5);
Answer» INSERT INTO STUDENT(RNO, MARKS) VALUES (5,78.5);

Discussion

106.

Differentiate between Alternate key and Candidate key.

Answer»

Alternate Key	Candidate Key
A key that can act as a primary key but is not selected as primary key	A key that can be set as Primary key is called a candidate key.

Discussion

107.	Table Employee has 4 records and 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?
Answer» 12 (Cartesian product).

Discussion

108.	In a database School, there are two tables Employee and Dept as shown below. Table : EmployeeEmpIDNameBalDeptno.T001Vishakha3400010T001Mridul3200050T001Manish4s00020 Table : DeptDeptno.DNameLocationlD10LightsHHO220DanceFF0230ProductionABOl(i) Identify the foreign key in the table Employee.(ii) What output, will you get, when an equi-join query is executed to get the NAME from Employee Table and corresponding DNAME from Dept table?
Answer» (i) Deptno. (ii) Vishakha Lights.

108.

In a database School, there are two tables Employee and Dept as shown below. Table : EmployeeEmpIDNameBalDeptno.T001Vishakha3400010T001Mridul3200050T001Manish4s00020 Table : DeptDeptno.DNameLocationlD10LightsHHO220DanceFF0230ProductionABOl(i) Identify the foreign key in the table Employee.(ii) What output, will you get, when an equi-join query is executed to get the NAME from Employee Table and corresponding DNAME from Dept table?

Answer»

(i) Deptno.

(ii) Vishakha Lights.

Discussion

Explore topic-wise InterviewSolutions in .

Rewrite the following SQL statement after correcting error(s). Underline the corrections made. INSERT IN STUDENT(RNO,MARKS) VALUE (5, 78.5);

Differentiate between Alternate key and Candidate key.

Table Employee has 4 records and 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?