• +91 9971497814
  • info@interviewmaterial.com

Chapter 12- Structured Query Language Interview Questions Answers

Question 1 :
Differentiate between delete and drop table command ?

Answer 1 :

DELETE command is used to remove information from a particular row or rows. If used without any condition, it will delete all row information but not the structure of the table. It is a DML command. DROP table command is used to remove the entire structure of the table and information. It is a DDL command.

Question 2 :
What is the use of wildcard ?

Answer 2 :

The wildcard operators are used with the LIKE operator to search a value similar to a specific pattern in a column. There are 2 wildcard operators.
% – represents 0,1 or many characters
– = represents a single number or character

Question 3 :
Write SQL query to add a column total price with datatype numeric and size 10, 2 in a table product.

Answer 3 :

ALTER TABLE product ADD total price number

Question 4 :
While creating table ‘customer’, Rahula forgot to add column ‘price’. Which command is used to add new column in the table. Write the command to implement the same.

Answer 4 :

ALTER TABLE customer ADD price number(10,2)

Question 5 :
Deepika wants to remove all rows from the table BANK. But he needs to maintain the structure of the table. Which command is used to implement the same ?

Answer 5 :

DELETE FROM BANK

Question 6 :
Sonal needs to display name of teachers, who have “0” as the third character in their name. She wrote the following query.
Select name from teacher where name = “$$0?”; But the query isn’t producing the result. Identify the problem.

Answer 6 :

The wildcards are incorrect. The corrected query is SELECT name FROM teacher WHERE name
LIKE’ _ _  0%’

Question 7 :

Write SQL queries for(i) to (iv) and find outputs for SQL queries (v) to (viii), which are based onthe tables.

                                     Table: VEHICLE

CODE

   VTYPE

PERKM

101

VOLVO BUS

160

102

AC DELUXE BUS

150

103

ORDINARY BUS

90

105

SUV

40

104

CAR

20

Note :

1.    PERKM is FreightCharges per Kilometer.

2.    VTYPE is VehicleType.

                                                   Table: TRAVEL

No.

NAME

TDATE

KM

CODE

NOP

101

Janish Kin

2015-11-13

200

101

32

103

Vedika Sahai

2016-04-21

100

103

45

105

Tarun Ram

2016-03-23

350

102

42

 102

John Fen

      2016-02-13

     90

     102

      40

107

     Ahmed Khan

      2015-01-10

     75

     104

       2

104

Raveena

       2016-05-28

     80

     105

       4

106

Kripal Anya

2016-02-06

200

101

25

Note:

·        NO is Traveller Number

·        KM is KilometerTravelled

·        NOP is number oftravellers travelled in vehicle.

·        TDATE is Travel Date

1.   To display NO, NAME,TDATE from the table TRAVEL in descending order of NO.

2.   To display the NAME ofall the travellers from the table TRAVEL who are travelling by vehicle withcode 101 or 102.

3.   To display the NO andNAME of those travellers from the table TRAVEL who travelled between ‘2015-12­31’and ‘2015-04-01’.

4.   To display all thedetails from table TRAVEL for the travellers, who have travelled distance morethan 100 KM in ascending order of NOP  .

5.   SELECT COUNT (*), CODEFROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;

6.   SELECT DISTINCT CODEFROM TRAVEL;

7.   SELECT A. CODE,NAME,VTYPE

FROM TRAVEL A,VEHICLEB
WHERE A.CODE=B.CODE AND ‘KM<90;
8.SELECT NAME, KM*PERKM
FROM TRAVEL A,VEHICLE B
WHERE A.CODE=B. CODE AND A.CODE=’105’;

 

Answer 7 :

1.     Select NO,Name, TDATE from TRAVEL order by NO desc

2.   Select NAME fromTRAVEL, where CODE in (101, 102)

3.   Select NO, NAME fromTRAVEL where TDATE between ’2015-12-31′ and ‘2015-04-01’.

4.   Select * from TRAVELwhere KM > 100 order by NOP.

5.    

COUNT (*)

CODE

2

101

2

102

6.

DISTANCE (CODE)

101

103

102

104

105

7.

CODE

NAME

VTYPE

104

Ahmed khan

CAR

105

Raveena

SUV

8.

NAME KM*PERKM
Tarun Ram 14000

 

Question 8 :

Consider the followingtables FACULTY and COURSES. Write SQL commands for the statements (i) to (v)and give outputs for SQL queries (vi) to (vii)
FACULTY

F_ID

Fname

Lname

Hire_date

Salary

102

Amit

Mishra

12-10-1998

12000

103

Nitin

Vyas

24-12-1994

8000

104

Rakshit

Soni

18-5-2001

14000

105

Rashmi

Malhotra

11-9-2004

11000

106

Sulekha

Srivastava

5-6-2006

10000

COURSES

C_ID

FJD

Cname

C21

102

Grid Computing

40000

C22

106

System Design

16000

C23

104

Computer Security

8000

C24

106

Human Biology

15000

C25

102

Computer Network

20000

C26

105

Visual Basic

6000

(i) To display detailsof those Faculties whose salary is greater than 12000.

 

Answer 8 :

Select * from faculty
where salry > 12000;
(ii) To display the details of courses whose fees is in th range of 15000 to 50000(both values included).
Аnswer:
Select * from Courses
where fees between 15000 and 50000;
(iii)To increase the fees of all courses by 500 of “System Design” Course.
Update courses set fees = fees + 500
where Cname = “System Design”;
(iv)To display details of those courses which are taught by ‘Sulekha’ in descending order of courses.
Аnswer:
Select * from faculty fac, courses cour
where fac.f_id = cour.f_id and fac.fname = ‘Sulekha’ order by cname desc;
(v)Select COUNT (DISTINCT F_ID) from COURSES;
Аnswer:
4
(vi)Select MIN (Salary) from FACULTY, COURSES where COURSES.F_ID = FACULTY.FJD;
Аnswer:
6000

Question 9 :

Write SQL commands forthe queries (i) to (iv) and output for (v) & (viii) based on a tableCOMPANY and CUSTOMER

CID

NAME

CITY

PRODUCTNAME

111

SONY

DELHI

TV

222

NOKIA

MUMBAI

MOBILE

333

ONIDA

DELHI

TV

444

SONY

MUMBAI

MOBILE

555

BLACKBERRY

MADRAS

MOBILE

666

DELL

DELHI

LAPTOP

 

CUSTID

NAME

PRICE

QTY

CID

101

ROHAN SHARMA

70,000

20

222

102

DEEPAK KUMAR

50,000

10

666

103

MOHAN KUMAR

30,000

5

111

104

SAHIL BANSAL

35,000

3

333

105

NEHA SONI

25,000

7

444

106

SONAL AGGARWAL

20,000

5

333

107

ARUN SINGH

50,000

15

666

1.   To display thosecompany name which are having prize less than 30000.

2.   To display the name ofthe companies in reverse alphabetical order.

3.   To increase the prizeby 1000 for those customer whose name starts with’S?

4.   To add one more columntotalprice with decimal(10,2) to the table customer

5.   SELECT COUNTO ,CITYFROM COMPANY GROUP BY CITY;

6.   SELECT MIN(PRICE),MAX(PRICE) FROM CUSTOMER WHERE QTY>10;

7.   SELECT AVG(QTY) FROMCUSTOMER WHERE NAME LIKE “%r%;

8.   SELECTPRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE COMPANY.CID=CUSTOMER.CIDAND PRODU CTN AME=”MOBILE”;

 

Answer 9 :

1. To displaythose company name which are having prize less than 30000.
SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER. CID AND    PRICE< 30000
2.To display the name of the companies in reverse alphabetical order.
SELECT NAME FROM COMPANY
ORDER BY NAME DESC?;
3.To increase the prize by 1000 for those customer whose name starts with “S”
UPDATE CUSTOMER
SET PRICE = PRICE + 1000;
WHERE NAME LIKE ‘S%’;
4.To add one more column totalprice with decimal(10,2) to the table customer
ALTER TABLE CUSTOMER
ADD TOTALPRICE DECIMAL(10,2);
5.SELECT COUNT(*) ,CITY FROM COMPANY GROUP BY CITY;

3

  DELHI

2

MUMBAI

1

MADRAS

6.SELECT MIN(PRICE),MAX(PRICE) FROM
CUSTOMER WHERE QTY> 10;
50000,70000
7.SELECT AVG(QTY) FROM CUSTOMER
WHERE NAME LIKE“%r%;                       [
8.SELECT PRODUCTNAME, CITY, PRICE FROM COMPANY, CUSTOMER WHERECOMPANY.CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;

MOBILE

MUMBAI

70000

MOBILE

MUMBAI

25000

 

Question 10 :

Consider the followingtables SCHOOL and ADMIN and answer this question :
Table : SCHOOL

CODE

TEACHERNAME

SUBJECT

DOJ

PERIODS

EXPERIENCE

1001

Ravi Shankar

English

12/3/2000

24

10

1009

Priya Rai

Physics

03/09/1998

26 ,

12

1203

Lisa Anand

English

09/04/2000

27

5

1045

Yashraj

Maths

24/08/2000

24

15

1123

Ganan

Physics

16/07/1999

28

3

1167

Harish B

Chemistry

19/10/1999

27

5

1215

Umesh

Physics

11/05/1998

22

16

Table : Admin

Code

Gender

Designation

1001

Male

Vice Principal

1009

Female

Coordinator

1203

Female

Coordinator

1045

Male

HOD

1123

Male

Senior Teacher

1167

Male

Senior Teacher

1215

Male

HOD

Write SQL statementsfor the following :

1.   To displayTEACHERNAME, PERIODS of all teachers whose periods are more than 25.

2.   To display all theinformation from the table SCHOOL in descending order of experience.

3.   To display DESIGNATIONwithout duplicate entries from the table ADMIN.

4.   To displayTEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN ofMale teachers.

 

Answer 10 :

1. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
SELECT TEACHERNAME, PERIODS
FROM SCHOOL WHERE PERIODS >25.
2.To display all the information from the table SCHOOL in descending order of experience.

SELECT * FROM SCHOOL;
3.To display DESIGNATION without duplicate entries from the table ADMIN.
SELECT DISTINCT DESIGNATION FROM ADMIN;
4.To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers.
SELECT TEACHERNAME.CODE
DESIGNATION FROM SCHOOL.CODE = ADMIN.CODE
WHERE GENDER = MALE;


Selected

 

Chapter 12- Structured Query Language Contributors

krishan

Share your email for latest updates

Name:
Email:

Our partners