Important SQL Questions

Q1. Consider the following tables STUDENT and STREAM.
Write SQL commands for the

statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
Table: STUDENT
SCODE
101
102
103
105
108
109
110
113
115
120
NAME
Amit
Arjun
Zaheer
Gagan
Kumar
Rajesh
Naveen
Ajay
Kapil
Gurdeep
AGE
16
13
14
15
13
17
13
16
14
15
STRCDE
1
3
2
5
6
5
3
2
3
2
POINTS
6
4
1
2
8
8
9
3
2
6
GRADE
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Table: STREAM
STRCDE
1
2
3
4
5
6
7
STRNAME
SCIENCE+COMP
SCIENCE+BIO
SCIENCE+ECO
COMMERCE+MATHS
COMMERCE+SOCIO
ARTS+MATHS
ARTS+SOCIO
(i)
To display the name of streams in alphabetical order from table STREAM.
(ii)
To display the number of students whose POINTS are more than 5.
(iii) To update GRADE to A for all those students who are getting more than 8 as
POINTS
(iv)ARTS+MATHS stream is no more available.Make necessary change in table STREAM
(v)SELECT SUM(POINTS) FROM STUDENT where AGE>14
(vi)SELECT STRCDE,MAX(POINTS) from STUDENT group by STRCDE having SCODE
between 105 and 130
(vii)SELECT AVG(AGE) FROM STUDENT where SCODE in(102,105,110,120);
(viii)SELECT COUNT(STRNAME) from STREAM where STRNAME like SCI%;
Q2. Consider the following table PREPAID. Write SQL commands for the statements (i) to (iv)
and give outputs for SQL queries (v) to (viii).
Table: PREPAID
Cname
Model
Sita
Ritesh
Reena
Meetali
Ramanuj
Anupam
Sai Ram
Deepa
Karan
Jayant
Nokia
Nokia
Samsung
Nokia
Samsung
LG
Nokia
LG
Sony
Siemen
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
Connectio
n
Airtel
Hutch
Hutch
Indicom
Idea
Hutch
Indicom
Reliance
Idea
Airtel
Activation_Date
Validity (in days)
Amount (in Rs.)
04/06/2004
12/02/2005
14/05/2004
24/09/2004
16/10/2004
18/01/2005
10/12/2004
08/03/2005
30/01/2005
02/02/2005
365
60
365
180
180
60
180
30
90
60
3300
575
3030
890
900
600
890
300
800
560
To display Cname and Amount of Customers having Samsung model and Hutch
connection, arranged in descending order of Amount.
To display different types of connection available.
To display sum of validity of each type of connection grouping.
To display Cname,connection and date of expiry.
(where date of expiry is date of activation+validity)
SELECT COUNT(DISTINCT MODEL) FROM PREPAID;

SELECT MAX(ACTIVATION),MIN(ACTIVATION) FROM PREPAID;
SELECT AVG(VALIDITY) FROM PREPAID
WHERE MODEL=LG;
(viii) SELECT MAX(AMOUNT) FROM PREPAID
WHERE CONNECTION=Idea;
Q3. Consider the following tables WORKERS and DESIG. Write SQL commands for the
statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
Table: WORKERS
W_ID
102
105
144
210
255
300
335
403
451
FIRSTNAME
Sam
Sarah
Manila
George
Mary
Robert
Henry
Ronny
Pat
LASTNAME
Tones
Ackerman
Sengupta
Smith
Jones
Samuel
Williams
Lee
Thompson
ADDRESS
33 Elm St.
440 U.S. 110
24 Friends Street
83 First Street
842 Vine Ave.
9 Fifth Cross
12 Moore Street
121 Harriston St.
11 Red Road
CITY
Paris
New York
New Delhi
Howard
Losantiville
Washington
Boston
New York
Paris
Table: DESIG
W_ID
102
105
144
210
255
300
335
400
451
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(viii)
SALARY
75000
85000
70000
75000
50000
45000
40000
32000
28000
BENEFITS
15000
25000
15000
12500
12000
10000
10000
7500
7500
DESIGNATION
Manager
Director
Manager
Manager
Clerk
Clerk
Clerk
Salesman
Salesman
To display W_ID,Firstname,Address and City of all employees living in New York

from the table WORKERS.
To display the content of WORKERS table in ascending order of LASTNAME.
To display the Firstname,Lastname and Total Salary of all Clerks from the tables
WORKERS and DESIG, where Total Salary is calculated as Salary Benefits.
To display the Minimum salary among Managers and Clerks from the table DESIG.
SELECT FIRSTNAME,SALARY FROM WORKERS,DESIG
WHERE DESIGNATION=Manager AND WORKERS.W_ID=DESIG.W_ID;
SELECT COUNT(DISTINCT DESIGNATION) FROM DESIG;
SELECT DESIGNATION,SUM(SALARY) FROM DESIG
GROUP BY DESIGNATION HAVING COUNT(*)<3;
SELECT SUM(BENEFITS) FROM WORKERS
WHERE DESIGNATION=Salesman
Q4. Consider the following tables. Write SQL commands for the statements (i) to (iv) and give
outputs for SQL queries (v) to (viii).
Table: SENDER
SenderID
ND01
MU02
MU15
ND50
SenderName
R Jain
H Sinha
S Jha
T Prasad
SenderAddress
2,ABC Appts
12,Newtown
27/A,Park Street
122-K,SDA
SenderCity
New Delhi
Mumbai
Mumbai
New Delhi
Table: RECIPIENT
RecID
KO05
ND08
MU19
MU32
ND48
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(viii)
SenderID
ND01
MU02
ND01
MU15
ND50
RecName
R Bajpayee
S Mahajan
H Singh
P K Swamy
S Tripathi
RecAddress
5,Central Avenue
116,A Vihar
2A, Andheri East
B5, CS Terminus
13,B1 D, Mayur
Vihar
RecCity
Kolkata
New Delhi
Mumbai
Mumbai
New Delhi
To display the names of all Senders from Mumbai.

To display the RecID,SenderName,SenderAddress,RecName,RecAddress for every
Recipient.
To display the Recipient details in ascending order of RecName.
To display the number of Recipients from each city
SELECT DISTINCT SenderCity FROM Sender;
SELECT A.SenderName,B.RecName FROM Sender A,Recipient B
WHERE A.SenderID=B.SenderID AND B.RecCity=Mumbai;
SELECT RecName,RecAddress FROM Recipient
WHERE RecCity NOT IN(Mumbai,Kolkata);
SELECT RecID,RecName FROM Recipent
WHERE SenderID=MU02 OR SenderID=ND50;
Q5. Consider the following tables CONSIGNOR and CONSIGNEE. Write SQL commands for
the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
Table: CONSIGNOR
CnorID
ND01
ND02
MU15
MU50
CnorName
R Singhal
Amit Kumar
R Kohli
S Kaur
CnorAddress
24, ABC Enclave
123,Palm Avenue
5/A,South Street
27-K Westend
City
New Delhi
New Delhi
Mumbai
Mumbai
Table: CONSIGNEE
CneeID
MU05
CnorID
ND01
ND08
KO19
ND02
MU15
CneeName
Rahul
Kishore
P Dhingra
A P Roy
MU32
ND48
ND02
MU50
S Mittal
B.P. Jain
(i)
(ii)
(iii)
(iv)
(v)
(vi)
(vii)
(viii)
CneeAddress
5,Park Avenue
CneeCity
Mumbai
16/J,Moore Eclave
2A, Central
Avenue
P 245,AB Colony
13,Block D,A
Vihar
New Delhi
Kolkata
Mumbai
New Delhi
To display the names of all Consignors from Mumbai.

To display the CneeID,CnorName,CnorAddress,CneeName,CneeAddress for every
Consignee.
To display Consignee details in ascending order of CneeName
To display the number of consignors from each city.
SELECT DISTINCT City FROM CONSIGNEE;
SELECT A.CnorName,B.CneeName FROM CONSIGNOR A,CONSIGNEE B
WHERE A.CnorID=B.CnorID AND B.CneeCity=Mumbai;
SELECT CneeName,CneeAddress FROM CONSIGNEE
WHERE CneeCity NOT IN(Mumbai,Kolkata);
SELECT CneeID,CneeName FROM CONSIGNEE
WHERE CnorID=MU15 OR CnorID=ND01;

Important SQL Questions

Uploaded by

Copyright:

Available Formats

Important SQL Questions

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Important SQL Questions

Uploaded by

Copyright:

Available Formats

Q1. Consider the following tables STUDENT and STREAM.

Write SQL commands for the

Validity (in days)

Amount (in Rs.)

SELECT COUNT(DISTINCT MODEL) FROM PREPAID;

To display W_ID,Firstname,Address and City of all employees living in New York

To display the names of all Senders from Mumbai.

To display the names of all Consignors from Mumbai.

You might also like