1.
Table:SBOP
[Link]. Name Balance Date of open Transaction
SB-1 Mr. Anil 15000.00 2011-02-24 7
SB-2 Mr. Amit 23567.89 8
SB-3 Mrs. Shakshi 45000.00 2012-02-04 5
SB-4 Mr. Gopal 23812.35 2013-09-22
SB-5 Mr. Dennis 63459.80 2009-11-10 15
SB-6 Mrs. Rani 30000.00 2012-04-17 9
SB-7 Mr. Shyam 40000.00 2010-05-24 12
i) To display [Link]., Name and date of open of account
holder having Transaction more than 8.
>> SELECT [Link]., Name, Date of open FROM SBOP
WHERE Transaction >8;
ii) To display Name, balance, transaction of those who
have opened their Account between 2009-11-10 and
[Link] the query in descending order of
date of open.
>> SELECT Name, Balance, Transaction FROM SBOP
WHERE Date of open BETWEEN‘2009-11-10’ AND ‘2012-
04-17’ ORDER BY Date of open ;
iii) To display details of those whose name contain ‘i’ as
any character.
>> SELECT * FROM SBOP WHERE Name Like “% i %”;
iv) To delete records of those person whose date of open
is after 2010-05-24.
>> DELETE FROM SBOP WHERE Date of open > ‘2010-05-
24’;
v) To add primary key constraint to the table using Acc.
No column.
>> ALTER TABLE SBOP
ADD CONSTRAINT PRIMARY KEY ([Link]) ;
2. Table:Exam
Ad. No SName Percentage Class Stream
R001 Sushant 90.2 12A Science
R002 Vaidyanath 80.5 12B Humanities
R003 Maira 68.9 12B Science
R004 Nihal 96.0 12A Commerce
R005 Ruhan 88.9 12B Commerce
R006 Aakash 87.0 12B Science
R007 Reema 75.2 12C Humanities
i) To display all information of the student of humanities
in descending order of percentage.
>> SELECT * FROM EXAM WHERE Stream=”humanities”
ORDER BY percentage Desc ;
ii) To display [Link], Name, percentage and stream of
those student whose name is less than 6 characters
long.
>> SELECT [Link], SName, percentage FROM EXAM
WHERE LENGTH(SName) < 6;
iii) Write query to round off the percentage of student of
science and commerce students.
>> SELECT ROUND (Percentage) FROM EXAM WHERE
Stream = ‘Science’ or Stream=’commerce’
Iv) To delete class column from exam table.
>> ALTER TABLE EXAM
DROP COLUMN Class ;
V) To increase percentage by 2% of all the science
students.
>> UPDATE EXAM
SET Percentage = Percentage +2
WHERE Stream=” Science “;
[Link]: CLUB.
Coach-id CName Age Sports DOA Pay Gender
1 Siya 24 Karate 27-03-2018 1000 F
2 Ravina 34 Karate 20-01-2018 1200 F
3 Karan 34 Squash 19-02-2019 1900 M
4 Shagun 33 Basketball 01-01-2018 2000 F
5 Keshav 36 Swimming 12-01-2019 800 M
6 Zubin 29 Swimming 24-02-2019 750 M
7 Aashi 39 Squash 20-02-2019 2100 F
8 Kush 37 Karate 22-02-2018 1000 M
9 Tarun 28 Swimming 13-01-2019 1100 M
10 Zareen 37 Basketball 18-02-2019 1700 F
Î) To display total amount paid by coaches of swimming and
squash.
>> SELECT SUM(Pay) FROM CLUB WHERE Sport=’swimming'
or sport=’sqaush’;
ii) To count total no. of coaches in each sport.
>> SELECT COUNT(*), sports FROM CLUB GROUP BY Sports
;
Iii) To display the Name , sports and pay of coaches who paid
more than 1500 in ascending order of names.
>>> SELECT CName, sports, pay FROM CLUB WHERE Pay
>1500 order by CName asc;
iv) To display name ,amount paid by youngest coach in
the club.
>> SELECT CName, pay FROM CLUB WHERE
Age=MIN(AGE) ;
V) To calculate , sports, average amount paid for the sport
having more than one coach.
>> SELECT COUNT (*) , AVG(PAY) FROM CLUB GROUP BY
Sports HAVING COUNT(*) >1;