InterviewSolution
Saved Bookmarks
InterviewSolution
This section includes InterviewSolutions, each offering curated multiple-choice questions to sharpen your knowledge and support exam preparation. Choose a topic below to get started.
| 1. |
Consider the table given below :SalespersonSalespersonNameAgeSalary1Ajay611400002Sunil34440003Chris34400004Amaaya4152000OrdersOrdersSalespersonIDAmount1025400020718000(i) The column in the "Salesperson"table is the ................. KEY. The SalespersonId column in the "Orders" table is a .................. KEY.(ii) Can the 'Salespersonld' be set as the primary key in table 'Orders'. Give reason. |
|
Answer» (i) Primary, Foreign (ii) No, as it may be repeated in Orders table. |
|
| 2. |
Write one similarity and one difference between CHAR and VARCHAR data types. |
||||||
|
Answer» Similarity : (i) Both are used for storing non-numeric data. (ii) Both can store 1 to 255 characters. (iii) Values must be enclosed in single quotes or double quotes. Difference :
|
|||||||
| 3. |
Mrs. Sharma is the class teacher of Class 'XII A'. She wants to create a table 'Student' to store details of her class. (i) Which of the following can be the attributes of Student table?(a) RollNo(b) "Amit"(c) Name(d) 25(ii) Name the Primary key of the table'Student'. State reason for choosing it. |
|
Answer» (i)(a) RollNo (b) Name (ii) primary key : RollNo as it will be unique for each student of the class. |
|
| 4. |
An attribute A of datatype varchar(20) has the value "Amit" The attribute B of datatype char(20) has value "Karanita". How many characters are occupied in attribute A? How many characters are occupied in attribute B? |
|
Answer» 4 characters are occupied in attribute A . 20 characters are occupied in attribute B. |
|
| 5. |
Srishti has created the following table with the name'Veterinary'. Column NameConstraint Animalld Primary Key VaccinationDateAnimalNameNot NullOwnerNameOne of the rows inserted is as follows :AnimalldVaccinationDateAnimalNameStoreldA1012015-02-12SheruAmit Sharma(i) What are the data type of columns Animalld and Vaccination Date in the table Veterinary ?(ii) Srishti is now trying to insert the following row :AnimalldVaccinationDateAnimalNameOwnerNameA1022015-08-09NULLAbhimanyu ShahWill she be able to successfully insert it ? Give reason. |
|
Answer» (i) Animalld = char VaccinationDate = Date 2015-02-12 (ii) No, it will not be inserted as AnimalName column can't have NULL values. |
|
| 6. |
Write commands in SQL for (i) and (ii) and output for (iii) given below :(i) To displays client names of clients, their phone numbers, partyId, and party decription who will have number of guests more than 50 for their parties.(ii) To displays Client Ids, their addresses, number of guests of those client who have 'Adarsh' anywhere in their addresses.ClientIdClientIdNameAddressPhoneNo of GuestsPartyIdSELECT Clientld, ClientName, NoofGuests, Description, Costperperson, From Client, Party WHERE Client.ParyId =Party.Partyld AND NoofGuests BETWEEN 50 AND 100; |
||||||||||||||||||||
|
Answer» (i) Select ClientName, Phone, p.partyld., partyDescription from party P, Client C where P. Partyld = C.Partyld and C. No of Guests > 50; (ii) Select ClientlD, Address, NoOfGuests from Client where Address like '% Adarsh %'; (iii)
|
|||||||||||||||||||||
| 7. |
What is "SQL"? |
|
Answer» SQL is structured Query Language. It is a non procedural language that is used to create manipulate and process the databases. |
|
| 8. |
Consider the tables given below which are linked with each other and maintains referential integrity : Table: SAPSAPIDItemCodeItemNameItemStoragelocationS10011001ReceiverW12-B3-R24S10021002TransponderW13-B7-R87S10031003Battery BankW21-B1-R87S10041004InverterW21-B11-R2S10051005GensetW22-B15-R16 Table: StoreStoreIDItemCodeStorelocationReceivedDate12011001Hauz Khas2016/05/2012021002Rajouri Garden2016/06/1412041003Rohini2016/05/0612051004Hauz Khaas2016/07/1512061005Rajendra Place2016/05/27With reference to the above given tables, write commands in SQL for (i) and (ii) and output for (iii) below:(0 To display the ItemCode, ItemName and ReceivedDate of all the items.(ii) To display SAPID, ItemName, ItemStoragelocation of all the items whose Receiveddate is after 2nd May 2016.(iii) SELECT sa.SAPID,ItemName, STOREID FROM SAP sa, Store st WHERE sa.ItemCode=st.ItemCode AND Storelocation = "Hauz Khas". |
|||||||||
|
Answer» (i) Select SAP.ItemCode, ItemName, ReceivedDate From SAP sa, Store st Where sa.ItemCode=st.ItemCode; (ii) Select sa.SAPlD,ItemName,ItemStoragelocation From SAP sa,Store st Where sa.ItemCode=st.ItemCocle and Received date >" 2016 / 05 / 02";
|
||||||||||
| 9. |
Consider the tables given below.SalespersonIdNameAgeSalary1Aiay611400002Sunil34440005Chris34400007Amaaya4152000 OrdersOrderIDSalespersonlDAmount10254000207180003014600040524000With reference to the above given tables, (is 86b) Write commands in SQL for (i) and (ii) and output for (iii) below :(i) To display SalespersonlD, names, orderids and order Amount of all salespersons.(ii) To display Names, salespersonslD and Order ID of those sales persons whose names start with A and sales amount is between 15000 and 20000.(iii) SELECT SalespersonlD, Name, Age, Amount FROM Salesperson, Orders WHERE Salesperson. SalespersonlD= Orders. SalespersonlD and Age between 30 and 45; |
|
Answer» (i) SELECT S.SalespersonlD, Name, OrderID, Amount FROM Salesperson S, Orders O WHERE S.SalespersonlD= O.SalespersonlD; (ii) SELECT Name, S.SalespersonlD, OrderID FROM Salesperson S, Orders O WHERE S. SalespersonlD=O. SalespersonlD AND Name LIKE "A%" AND Amount BETWEEN 15000 AND 20000; (iii) 2 Sunil 34 54000 5 Chris 34 24000, 7 Amaaya 41 18000 |
|
| 10. |
Consider the tables'Flights' & 'Fares' given below: Table: FLIGHTSFLCODESOURCEDESTINATIONNO_STOPSNO_FLIGHTSIC3O1MumbaiBangalore32IC799BangaloreKolkata83MC101DelhiVaranasi60IC3O2MumbaiKochi14AM812LucknowDelhi40MU499DelhiChennai33 Table: FARESFNOAIRLINESFARETAXIC301Indian Airlines942551C799Spice Jet884610MC101Deccan Airlines42107IC302Jet Airways138945AM1812Indian Airlines45006MU499Sahara120004With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below:(i) To display flight number source, airlines of those flights where fare is less than Rs. 10000.(ii) To count total no of Indian Airlines flights starting from various cities.(iii) SELECT FLIGFITS.FNO, NO_OF_FL, AIRLINES FROM FLIGHTS,FARES WHERE FLIGHTS. FNO = FARES.FNO AND SOURCE='DELHI'; |
|
Answer» (i) select flights.fno,source,airlines from flights,fares where flight.fno=fares.fno and fare < 10000; (ii) select sum(no_of_fl) from flights,fares where flights.fno=fares.fno group by source having Airlines ='Indian Airlines'; (iii) MC101 6 Deccan Airlines MU499 3 Sahara |
|
| 11. |
In a Database Multiplexes, there are two tables with the following data. Write MySQL queries for (i) to (iii), which are based on TicketDetails and AgentDetails : Table : TICKETDETAILS TcodeNameName TicketsA_CodeS001Meena7A01S002Vaani5A02S003Meena9A03S004Karish2A03S005Suraj1A04 Table: AGENT DETAILS AcodeANameA01Mr. RobinA02Mr. AyushA03Mr. TrilokA04Mr. John(i) To display Tcode, Name and Aname of all the records where the number of tickets sold is more than 5.(ii) To display total number o{ tickets booked by agent "Mr. Ayush".(iii) To display Acode, Aname and corresponding Tcode where Aname ends with 'k'. |
|
Answer» (i) SELECT Tcode, Name, AName from TicketDetails, AgentDetails where TicketDetails.ACode = AgentDetails.Acode and Tickets > 5. (ii) SELECT COUNT(Tickets) FROM TicketDetails, AgentDetails where TicketDetails.ACode = AgentDetails"Acode and AName like "Mr. Ayush"; (iii) SELECT Tcode, Name, AName from TicketDetails, AgentDetails where TicketDetails.ACode = AgentDetails.Acode and Aname like"%k"; |
|
| 12. |
In a database BANK, there are two tables with a sample data given below : Table : EMPLOYEEENo.NameSalaryZoneAgeGradeDept1Mona70000East40A102Muktar71000West45B203Nalini60000East26A104Sanaj65000South36A205Surya58000North30B30 Table: DEPARTMENTDepartmentDNameHOD10Computer120Economics230English5Write 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 inclusive)(ii) To display DNAME and corresponding ENAME from both tables. Hint: HOD of the DEpARTMENT table should be matched with ENO of the Employee table for getting desired result.(iii) To display ENAME, SALARY ZONE and INCOME TAX (30% of salary) of all the employees with appropriate column headings. |
|
Answer» (i) SELECT C.ENQ, C.ENAME, C.SALARY FROM EMPLOYEE C, DEPARTMENT D WHERE C.DEPT : D.DEpT AND C.AGE>:25 && C.AGE <:35; (ii) SELECT D.DNAME,C.ENAME FROM EMPLOYEE C, DEPARTMENT D WHEREC. DEPT=D.DEPT AND C.ENO : D.HOD; (iii) SELECT ENAME, SALARY ZONE,SALARY*0.3 AS "income tax" FROM EMPLOYEE; |
|
| 13. |
Observe the table 'Club' given below : clubMember_idMember_NameAddressAgeFeesM001SumitNew delhi 201000M002NishaGurgaon191500M003NiharikaNew Delhi212100M004SachinFaridabad181500 (i) What is the cardinality and degree of the above given table ?(ii) If a new column contact_no has been added and two more members have joined the club then how these changes will affect the degree and cardinality of above given table. |
|
Answer» (i) Cardinality: 4 Degree :5 (ii) Cardinality : 6 Degree : 6 |
|
| 14. |
In a Database, there are two tables given below: Table: EMPLOYEEEnployeeIDNameSaIesjobIDE1Samit Sinha1100000102E2Vijay Sigh Tomer1300000101E3Ajay Rajpal1400000103E4Mohit Ramnani1250000102E5Shailja Singh1450000103 Table : JOBJobID]obtitleSalary101President200000102Vice President125000103Administration Assistant80000104Accounting Manager70000105Accountant65000106Sales Manager80000Write SQL Queries for the following :(i) To display employee ids, names of employees, job ids with corresponding job titles.(ii) To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.(ii| To display names and corresponding job titles of those employee who have'SINGH' (anywhere) in their names.(iv) Identify foreign key in the table EMPLOYEE.(v) Write SQL command to change the JOBID to 104 of the Employee with ID as E4 in the table 'EMPLOYEE'. |
|
Answer» (i) SELECT EMPLOYEEID, NAME, E.JOBID, JOBTITLE FROM EMPLOYEE E, JOB J WHERE E.JOBID = JOBID; (ii) SELECT NAME,SALES, JOBTITLE FROM EMPLOYEE, JOB WHERE EMPLOYEE.JOBID = JOB.JOBID AND SALES > 1300000; (iii) SELECT NAME,JOBTITLE FROM EMPLOYEE, JOB WHERE EMPLOYEE.JOSID = ]OB. JOBlD AND NAME LIKE "%SINGIH%"; (iv) JOBID (v) UPDATE EMPLOYEE SET JOBID = 104 WHERE EMPLOYEEID _'E4'; |
|
| 15. |
In a Bank's database, there are two tables 'Customer_info' and 'Transaction_Detail' as shown below : Customer_InfoACC_NoCust_NameCust_AddCust_cityCust_phone1001001RamVasundhraEnclaveNew Delhi 87705576741001002KavitaPunjabi BaghNew Delhi77235452331001003RajCivil LinesAllahabad98721,365761001004SohanKrishna nagarKanpur9921305453 Transaction_DetailTrans_IDAcc_NoTransaction_TypeAmountT0011001001Credit5000T0021001002Credit10000T0031001003Debit2000T0041001004Credit6000T0051001005Credit4000(i) Is it possible to have primary key and foreign key in one table? Justify your answer.(ii) A table can have maximum how many primary keys and foreign keys? |
|
Answer» (i) Yes, it is possible to have primary key and foreign key column in one table. Primary key column is used to uniquely identify each record of the table, while foreign key column is used to maintain referential integrity. As in the above given table 'Transaction_Detail', Trans_Id column is a primary key column while Acc_No column may act as a foreign key column. (ii) A table can have maximum one Primary Key. A table can have any number of foreign Keys. |
|
| 16. |
Write one similarity and one difference between UNIQUE and PRIMARY KEY constraints. |
|
Answer» Both of them ensure uniqueness of values for a column or set of columns. Primary key cannot have NULL value but Unique key may be NULL. |
|
| 17. |
Distinguish between a Primary key and a Unique key in a table. |
|
Answer» Both of them ensure uniqueness of values for a column or set of columns. Primary key cannot have NULL value but Unique key may be NULL. |
|
| 18. |
Consider the table 'Organization' : Table: OrganizationOrgCodeSalaryC10113,000C1025,000C1047,000C1054,000(i) With SQL, how can you find the number of rows (records) in the "Organization" table?(ii) What output will be displayed by the following SQL statement: SELECT AVG (Salary) FROM Organization; |
|
Answer» (i) Select Count (*) from Organization; (ii) AVG (Salary) : 7250. |
|
| 19. |
Write the output of the following SQL queries :(i) SELECT MID ('VisitIndia',6,5); (ii) SELECT ROUND(89.387,2);(iii) SELECT INSTR (COMPUTER CLASS', 'PUT');(iv) SELECT DAY OF MONTH ('2016-01-20'); |
|
Answer» (i) India (ii) 89.39 (iii) 4 (iv) 20 |
|
| 20. |
Write the output of the following SQL queries :(i) SELECT MlD('BoardExarrunatton,2,4);(ii) SELECTROUND(67.246,2);(iii) SELECT INSTR('INFORMATTON FORM', 'FOR');(iv) SELECT DAYOFYEAR('2015-01-10'); |
|
Answer» (i) oard (ii) 67.25 (iii) 3 (iv) 10 |
|
| 21. |
Consider the tables given below :Table : FacultyTeacherIdNameAddressStatePhone NumberT101Savita SharmaA-151, Adarsh ViharDelhi991019564T102Deepak GhaiK-5/52,VikasMumbai893466448T103MahaLakshmiD-6Delhi981156568T704Simi AroraMumbai658777564Table : CourseCourseld SubjectTeaeherldFeeC101Introductory MathematicsT1014500C103PhysicsT1015000C104Introductory Computer ScienceT1024000C105Advance Computer ScienceT1046500(i) Which column is used to related the two tables?(ii) Is it possible to have a primary key and a foreign key both in one table? Justify your answer with the help of table given above |
|
Answer» (i) TeacherId. (ii) Yes,CourseId is the primary key and TeacherId is the foreign key. |
|
| 22. |
How is NULL value different from 0 (zero) value? |
|
Answer» If a column in a row has no value, then column is said to be null. Nulll : no value and so it is different from 0. |
|
| 23. |
Consider the tables given below :Table : CustomerCustIdNameCityItemldC101Amit KumarDelhi1105C104Michael DesouzaMumbai1103C105Abhay ChaddhaDelhi1101C106Raman KaurChennai1103Table: ItemItemIdItem NamePrice1101Computer350001102Laptop370001103Printer60001104Keyboard20001105Mouse475(i) Name the primary keys in both the tables.(ii) 'I103' data is present twice in column'Itemld' column in 'Customer' table - Is there any discrepancy? Give reason for your answer. |
|
Answer» (i) Customer - CustId Item - ItemId (ii) No. ItemId is the foreign key table and can have duplicates. |
|
| 24. |
Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the degree and cardinality of the Table CLIENT? |
|
Answer» Cardinality : 4 Degree : 3 |
|
| 25. |
What is a Foreign key? What is its importance? |
|
Answer» It is a column of a table which is the primary key of another table in the same database. It is used to enforce referential integrity of the data. |
|
| 26. |
There are two table T1 and T2 in a database. Cardinality and degree of T1 are 3 and 8 respectively. Cardinality and degree of T2 are 4 and 5 respectively. What will be the degree and Cardinality of their Cartesian product? |
|
Answer» Degree = T1. degree + T2. degree = 3 + 4 = 7 Cardinality = T1. Cardinality * T2. Cardinality = 8* 5 = 40. |
|
| 27. |
What effect does SET AUTOCOMMIT have in transactions ? |
|
Answer» If AUTOCOMMIT is set to 1, each SQL statement is considered a complete transaction and committed by default when it finishes. If AUTOCOMMIT is set to 0, the subsequent series of statements acts like a transaction and no transaction is committed until an explicit COMMIT statement is issued. |
|
| 28. |
Geetanjali had created a table "Customer" in the database "Test". Immediately after the successful creation of the database, she wrote the Rollback command to undo the creation of the table. Did she execute rollback successfully? Explain' |
|
Answer» A table in MYSQL is created using CREATE TABLE command which is a DDL command. A DDL command cannot be rolled back. hence, she needs to use the DROP TABLE command to remove the table. |
|
| 29. |
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» 12 rows and 6 columns. |
|
| 30. |
Table SCHOOL has 4 rows and 5 columns. What is the Cardinality and Degree of this table? |
|
Answer» Cardinality : 4 Degree : 5. |
|
| 31. |
Write the output of the following SQL queries :(i) SELECT MID('LeaningIsFan', 2,4);(ii) SELECT ROUND(76.384,2);(iii) SELECT INSTR('INFORMATTON FORM', 'RM');(iv) SELECT DAYOFYEAR ( '2015-01-30'); |
|
Answer» (i) ear (ii) 76.38 (iii) 5 (iv) 30 |
|
| 32. |
Do Primary Key column(s) of a table accept NULL values |
|
Answer» No, Primary Key column(s) of a table accept no NULL values. |
|
| 33. |
Mr. Sanghi created two tables with CITY as Primary key in Table1 and Foreign Key in Table 2. While inserting a row in Table2, Mr. Sanghi is not able to enter a value in the column CITY. What could be the possible reason for it? |
|
Answer» Mr. Sanghi should have tried to enter the name of CITY in Table 2 which is not present in Table 1. |
|
| 34. |
Write the output of the following SQL queries:(i) SELECT INSTR('INTERNATIONAL','NA');(ii) SELECT LENGTH(CONCAT('NETWORK' ,'ING'));(iii) SELECT ROUND(563. 345,-2) ;(iv) SELECT DAYOFYEAR(' 2014-01-30' ); |
|
Answer» (i.) 6 (ii.) 10 (iii.) 600 (iv.) 30 |
|
| 35. |
Write the output of the following SQL queries :(i) SELECT TRUNCATE(8.975,2);(ii) SELECT MID(HONESTY WINS' ,3,4);(iii) SELECT RIGHT(CONCAT('PRACTTCES', 'INF ORMATICS'),5);(iv) SELECT DAYOFMONTH(' 2015-01-16' ); |
|
Answer» (i) 8.97 (ii) NEST (iii) ATICS (iv) 16 |
|
| 36. |
Amit works as a database administrator in a Multinational bank. He wants to undo the changes made in the current transaction. Suggest him a suitable MySQL command for the same. |
|
Answer» MySQL command : ROLLBACK; |
|
| 37. |
There is a table T1 with combination of columns C1, C2, and C3 as its primary key? Is it possible to enter : (a) NULL values in any of these columns?(b) Duplicate values in any of these columns? |
|
Answer» No, NULL values and Duplicate values in any of these columns. |
|
| 38. |
Write the output of the following SQL queries.(i) SELECT ROUND (6.5675,2);(ii) SELECT TRUNCATE (5.3456,1);(iii) SELECT DAYOFMONTH ('2016-08-25');(iv) SELECT MID ('Computer', 2, 3); |
|
Answer» (i) 6.57 (ii) 5.3 (iii) 25 (iv) omp |
|
| 39. |
Write the output of the following statements.(i) SELECT ROUND (29.21,7), ROUND (32.76, 1)(ii) SELECT TRIM ('I love ...... MySQL ....');(iii) SELECT CONCAT ('I', 'am', 'a', 'teacher'); |
|
Answer» (i) 29.2 32.8 (ii) I love MySQL (Note : In between space is not removed) (iii) I am a teacher |
|
| 40. |
What will be the output of :(i) SELECT ROUND (124.44) + MOD (1200.87,3)(ii) SELECT (MOD (30.500, 5) + ROUND (100.50, 1) |
|
Answer» (i) 124.87 (ii) 101 |
|
| 41. |
How does DAYOFMONTH (), DAYOFWEEK() and DAYOFYEAR( ) work? |
|
Answer» DAYOFMONTH( ) returns the day of the month for a given date in the range 1 to 3). DAYOFWEEK ( ) returns the week day number (1 for Sunday, 2 for Monday .....). DAYOFYEAR ( ) returns a number stating the no. of days passed so far. (eg) 1. SELECT DAYOFMONTH ('2016-05-31',) 31 2. SELECT DAYOFWEEK ('2016-05-31') 3 3. SELECT DAYOFYEAR ('2016-05-31') 152. (31 + 29 + 31 + 30 + 31) |
|
| 42. |
How are NULL values treated by aggregate functions ? |
|
Answer» None of the aggregate functions takes NULL into consideration. NULL is simply ignored by all the aggregate functions. |
|
| 43. |
Why is it not allowed to give String and Date type arguments for SUM() and AVG() functions? Can we give these type of arguments for other functions ? |
|
Answer» String and dates are not real numbers that we calculate so Sum or Avg functions are not valid for them. |
|
| 44. |
How can we find the day name of a given date? |
|
Answer» DAYNAME( ) function returns the name of the week day from a date specified as a argument (eg) > SELECT DAYNAME ('1981-11.-19') Thursday |
|
| 45. |
What is the difference between CURDATE() and DATE() functions? |
|
Answer» CURDATE() returns the current date whereas DATE() extracts the date part or datetime expression. |
|
| 46. |
At the time of creation of table X, the database administrator specified Y as the Primary key. Later on he realized that instead of Y the combination of column P and Q should have been the primary key of the table. Based on this scenario, answer the following questions :(a) Is it possible to keep Y as well as the combination of P and Q as the primary key?(b) What statement(s) should be entered to change the primary key as per the requirement. |
|
Answer» (i) No - A table can have only one primary key. (ii) ALTER TABLE X DROP PRIMARY KEY ALTER TABLE X ADD PRIMARY KEY(PQ) |
|
| 47. |
Mention any 4 numberic functions in MySQL. |
|
Answer» ROUND( ), TRUNC( ), MOD(), SORT( ) |
|
| 48. |
Write the output of the following SQL queries :(i) SELECT RIGHT('software', 2);(ii) SELECT INSTR('twelve','lv');(iii) SELECT DAYOFMONTH(' 2014-03-01' );(iv) SELECT ROUND(75.987, 2); |
|
Answer» (i) re (ii) 3 (iii) SATURDAY (iv) 76.99 |
|
| 49. |
A numeric data field AMOUNT store a value 205.98. Round it off using MySQL to :(i) Upto 1 decimal place(ii) to a whole number. |
|
Answer» (i) SELECT ROUND (AMOUNT 1) (ii) SELECT ROUND (AMOUNT,0) |
|
| 50. |
What is the purpose of ORDER BY clause in MySQL? How is it different from GROUP BY clause? |
|
Answer» ORDER BY clause is used to sort a particular field in either ascending order or descending order. ORDER BY is used to order while GROUP BY is used to sort. |
|