plt.
show0
QUTPUT
Output: -
Figure1
PreBoardMarks
BoardMarks
80
Marks 60
40
20
Alex Ani Javed Amnt
Karan
Name
sq1USE TEST;
se changed
SELECT FROM ITEM:
tecode itenane I price
111 refrigerator 90000
222 television 75000
333 computer 42900
444 Nashing sachine 27000
AroNS in set (0.00 sec)
ysql> SELECT FROM BRAND:
Iiten code brand name
111 LG
222 Sony
333 HCL
IFB
rows in set (0.00 sec)
ysqls SELECT I.ITEM_CODE, ITEM NAME, BRAND_ NAME FROM ITEN TBRAND BWHERE I.ITEM_CODE-[Link] _CODE AND PRICE BETHEEN 20000 AND 40000;
TECODEITEN NANE |BRAND NAME |
Nashing sachine| IFB
1 ro in set (e.00 sec)
Sa1 SELECT [Link], PRICE, BRAND_ NAME FROM ITE I,BRAD BHERE I.ITEN_CODE-[Link] _CODE AID ITEM NAME- COMPUTER
ITEN CODE PRICE BRAND NAME
333 I 42000 I HCL
L ON in set (0.00 sec)
sql>
Database changed
mysql> selectfrom sales;
| salesmanid name sales locationid |
anita singh arora 250000 102
[Link] 1300000 101
tina jaiswal 103
S4
gurdeep singh 1250000 102
simi faizal 1450000 103
5 rows in set (0.00 sec)
Ysql> selectfrom location;
Ilocationid | locationname
101 | delhi
102 | mumbai
103 | kolkata
104 | chennai
-+
rows in set (0.00 sec)
Dysql> select salesmanid, name,[Link], locationname
from sales s,location 1 where
salesmanid name
[Link];
| locationid locationname
anita singh arora 702 Mumbai
[Link] 101 delhi
tina jaiswal 103 kolkata
S5
gurdeep singh 102 mumbai
simi faizal 103 kolkata
5 rows in set (0.00 sec)
mysql> select name, sales, locationname from sales
s,location l where [Link]-l, locatlontd and sales >
name
1300000;
sales I locationname
tina jaiswal | 1400000 | kolkata
| simi faízal I1450000 | kolkata
2 rows in set (0.00 sec)
Type here to search
Dettop 21°C A D40) 0804
ENG 26-10-2024
C: Proarent File:.SCLSLSerer 1 bn [Link]
ysql> update sales set lOcationid 104 here salesmanids3;
Query OK, 1 roM affected (0.e3 sec)
ROSatched: 1 Changed: 1 Warnings
1 prinary key salesmanid
0&:04
Destop ii21C D0)9 ENG 26-10-2024
ysql> SELECT FROM GANES;
CGODE GNANE TYPE NUNBER PRICENEYSaHEDULE DATE
101 GARON BOARD INDOOR
102 BADNITON OUTDOOR
103 TABLE TENNIS INDOOR
105 CHESS INDOOR
LANN TENNIS OUTDOOR
5 ros in set (.00 sec)
ysql> SELECT GNANE, CGODE FRON GANES
GHANE
CARON BOARD 1e1 |
IBADNTON 102
TABLE TENNIS 103 |
CHESS 105
LAMN TENNIS 198 |
5 rOS in set (e.00 sec)
ysql> SELECT FROM GAMES WHERE PRICEMONEY > 7000:
CGODE GNANE | TYPE J NUMBER PRICEMONEY saHEDULE DATE
102 BADNITON CUTD0OR 12000 2003-12-12
103 TABLE TENNIS INDOOR 8000 2004-02-14
105 CHESS I INDOOR 9000 2004-01-01
188 LANN TENNIS OUTDOOR 25000 2004-03-19
rows in set (0.e1 sec)
ysgl> SELECT *FROM GAMES ORDER BY SCHEDULE DATE:
CGODE GNAME TYPE |NUMBER | PRICEMONEY sHEDULE DATE
102 BADMITON OUTDOOR 2 12000 | 2003-12-12
105 CHESS |INDOOR I 2 9000 2004-01-01
101 CAROM BOARD INDOOR 2 5000| 2004-01-23
103 TABLE TENNIS | INDOOR 4 8000 | 2004-02-14
108 LANN TENNIS OUTDOOR | 4 25000 | 2004-03-19
here to search
10/25,2024
AProoram ies (x6 MSOLMSOL Se
Dysql> SELECTFROM SALESMAN;
SNO SNAME ISALARY BONUS DATE_OFJOIN
AB1 BEENA NEHTA 30000 45 201910-29
A82 [Link] 25 2018-03-13
B93 NISHA THAKKAR 30000 2017-03-18
BO4 LEELA YADAV NULL 2018-12-31
Ces GAUTAM GOLA 20000 NULL
CO6 TRAPTI GARG 70000
1989-01-23
1987-06-15
DO7 NEENA SHARNA 28 1999-e3-18
rONS in set (0.00 sec)
ysql> SELECT SNAME, ROUND(BONUS,e)FROM SALESMAN:
SNAHE ROUND(BONUS, O)|
BEENA MEHTA
[Link] 25
NISHA THAKKAR 35
LEELA YADAV MULL
GAUTAM GOLA NULL
TRAPTI GARG
NEENA SHARMA 28
7 rowS in set (e.00 sec)
Dysql> SELECT IISTR, (SNAME, TA)FROM SALESMAN:
ERROR 1054 (42S22): Unknoun column INSTR in field list
ysql> SELECT INSTR(SNANE,"TA)FROM SALESMAN;
| INSTR(SNANE, TA) I
10
7 rows in set (0.01 sec)
Dysql> SELECT MID(SNAME,2,4) FROM SALESMAN;
| MID(SNAME, 2,4) |
EENA
LS
O Tvpe here to search
19C aze 521 P#
10/25/2024
am y)
ysql> SELECT MID(SNANE, 2,4) FRON SALESAN
DSNAME, 2,4) |
EENA
ISHA
EELA
AUTA
RAPT
EENA
7 roNS in set (0.01 sec)
Eysql> SELECT MONTHNAME (DATE_OF JOIN) FROM SALESMAN;
MONTHNANE (DATE_OF 30IN)I
Dctober
March
March
December
January
June
March
7 rowS in set (0.01 sec)
Dysql> SELECT DAYNAME(DATE OF JOIN)FROM SALESMAN;
DAYNAME(DATE_OF JOIN) I
Tuesday
Tuesday
Saturday
Monday
Monday
Monday
Thursday
7 rowS in set (0.00 sec)
ysql>
Type here to search
19C Haze 521 PM
10/25/2024
erven veeston
Mtabaie
P
ysql> select Rate 0ty Fron STORE MHERE
TteND-2004;
Rate"0ty |
880
1 row in set ([Link] sec)
ysql> select Iten, Snane From STORE
S,SUPPLIERS P WHERE [Link]=[Link] AND ItenNO-2006;
Item | Sname
Gel Pen Classic | Preniun Stationers
Gel Pen Classic Tetre Supply
2 roNS in set (e.09 sec)
ysql> select NAK(LastBuy) Fron STORE;
PAX(LastBuy) |
| 2010-82-24
1 rOw in set (e.0 sec)
ysql>
J Type here to searcth
b 19C Haze 6:59 AM
10/27/2024
Dysql> SELECT SUM(PRICENONEY), TYPE FRON GANES GROUP B
TSUM( PRICEMONEY) TYPE
22000 INDOOR
3700e OUTDOOR
2ros in set (0.02 sec)
Iye here to seatct 553 PNM
10/25/2024
SQL [STRUCTURED QUERY LANGUAGEI
SQL QUESTION 21
Consider atable SALESMAN with the following data:
SNO SNAME SALARY BONUS DATE OF JOIN
A01 BEENA MEHTA 29-10-2019
30000 45.23
K.L. SAHAY
A02 50000 25.34 13-03-2018
NISHA
B03 THAKKAR 30000 35.00 18-03-2017
LEELA YADAV
B04 80000 NULL 31-12-2018
GAUTAM GOLA
CO5 20000 NULL 31-01-1989
TRAPTI GARG
CO6 70000 12.37 15-06-1987
NEENA
D07 SHARMA 50000 27.89 18-03-1999
Write SQL queries using SQL functions to perform
the following operations:
(a) Display salesman name and bonus after rounding off to zero
decimal places.
(b) Display the position of occurrence of the string "ta" in salesman
(c) Display the four characters from salesman name starting from
second character.
(d) Display the month name for the date of join of salesman
(e) Display the name of the weekday for the date of join of
salesmnan.
SQL QUESTION 22
Consider the following table GÁMES. Write SQL commands for the
following statements.
TABLE:GAMES
GCODE GameName TYPE Number Prizemoney scheduledate
101 Carorm board Indoor 2 5000 23-jan-2004
102/ Badminton Outdoor 2 12000 12-dec-2003
103 Table Tennis Indoor 4 8000 14-feb-2004
105 Chess Indoor 2 9000
108
01-jan-2004
Lawn tennis Outdoor 25000 19-mar-2004
1To display the name of all GAMES with their GCodes
2To display details of those GAMES
more than 7000 which are having Prizemoney
3 To display the content of the GAMES
Schedule Date table in ascending order of
4. To display the sum of Prizemoney for each
type of GAMES
SQL QUESTION 23
Consider the following table STORE and SUPPLIERS and
answer the following parts of this question :
TABLE:STORE
ItemNo Item Scode Qty Rate LastBuy
60
Ball len 0.25
31-Jun-09
50 25 01-Feb-10
Gel len l'remiunn 21 150 12 24-Feb-10
Gel len Classic 21 250 20 11-Mar-09
2001 Eraser Small 22 220 6
2004
19-Jan-09
Eraser Big 22 110 8 02-Dec-09
2000 Ball Pen 0.5 21 180 18 03-Nov-09
TABLE:SUPPLIERS
Scode Sname
21 Premium Stationers
23 Soft Plastics
22 Tetra Supply
GIVE THE OUTPUT OF THE FOLLOWING SQL queries :
SELECT COUNT DISTINCT SCode) FROM Store ;
. ) SELECT Rate * Oty FROM Store WHERE ItemNo =2004 :
(ui) SELECT Item, Sname FROM Store S, Suppliers P
WHERE [Link] -[Link] AND ItemNo =2006;
(i) SELECT MAX (LastBuy) FROM Store;
SQL QUESTION 24
In aDatabase there are two
tables :
Table : Item Table : Brand
Item Code Item Name Price Item Code
111 Brand Name
Refrigerator 90,000 111 LG
Television 75,000
333 Sony
Computer 42,000 333 HCL
444
IVashing \Machine 27,000 444 IFB
write MYSQL queries for the following :
Irite MySQL queries for the folloxcng:
(i) lo display Item_Code, lt_Name and
betuen congBrani Name of thosN lems, whse Irc is
20000 and 4o00 eth cales inlis
() To display ltem Code. Pic ai Broni La
wach has ltem Name as "Comyuter".
SQL QUESTION 25
In aDatabase company there are two
tables given below :
SALESMANID
TABLE:SALÉS TABLE :LOCATION
NAME SALES LOCATIONID LOCATIONID
ANITA SINGH ABØRA 250000 102
LOCATIONNAME
YP. SINGH 101 Delhi
1300000 101
102 Mumbai
1INA JAISWAL 1400000 103
103 Kolkata
GURDEEP SINGH 1250000 102
104
S5 SIN1I FAIZAL 1450000 103
Chennai
Write SQL queries for the following:
() To display SalesmanlD, nanes of
salesmen, LocationlD with corresponding location
(2:) To display names of salesnen, sales and names.
than 1300000. corresponding location nnes who have achieved Sales more
(21) T display names of tlhose salesmen
who have 'SINGH' in their nanes.
(1) ldestifu Prinary key in the table
SALES. Give reasOn for your choice.
() 1ile SQL. command to change tle
SALES,
LocationlD to 104 of the Salesman
with ID as S3 in the table
CBSE OD LSE