SQL PROGRAM

FILE

NAME- KAINAT MALHOTRA

CLASS-12 B
ROLL NO.-
SACHDEVA PUBLIC SCHOOL
[Link] a sql statement to insert the following info into item table.

mysql> create table item

-> (icode int(4) primary key,
-> iname varchar(20),
-> type varchar(15) not null,
-> size int(2),
-> cost decimal(10,2),
-> margin decimal(5,2),
-> qty int(5));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into item

-> values(1001,"school canvas","school",6,132.50,2.0,1200);
Query OK, 1 row affected (0.05 sec)

mysql> insert into item

-> values(1002,"school canvas","school",7,155.50,2.0,800);
Query OK, 1 row affected (0.02 sec)
mysql> insert into item
-> values(1003,"school canvas","school",8,145.50,2.0,600);
Query OK, 1 row affected (0.03 sec)

mysql> insert into item

-> values(1011,"school canvas","school",6,232.50,2.0,2200);
Query OK, 1 row affected (0.08 sec)

mysql> insert into item

-> values(1012,"school leather","school",6,232.50,2.0,1280);
Query OK, 1 row affected (0.03 sec)

mysql> insert into item

-> values(1013,"school leather","school",8,320.23,null,1100);
Query OK, 1 row affected (0.05 sec)

mysql> insert into item

-> values(1101,"galaxy","office",7,640.03,3.0,200);
Query OK, 1 row affected (0.03 sec)

mysql> insert into item

-> values(1102,"galaxy","office",8,712.65,3.0,500);
Query OK, 1 row affected (0.03 sec)

mysql> insert into item

-> values(1103,"galaxy","office",8,720.65,3.0,400);
Query OK, 1 row affected (0.02 sec)

mysql> insert into item

-> values(1201,"tracker","sports",8,700.00,null,280);
Query OK, 1 row affected (0.02 sec)

mysql> insert into item

-> values(1202,"tracker","sports",7,745.00,3.50,null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into item

-> values(1203,"tracker","sports",6,800.00,3.5,600);
Query OK, 1 row affected (0.09 sec)
mysql> insert into item
-> values(1204,"tracker","sports",9,850.00,3.5,null);
Query OK, 1 row affected (0.03 sec)

Q)-to display full info on the output screen.

mysql> select*
-> from item;
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1003 | school canvas | school | 8 | 145.50 | 2.00 | 600 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1102 | galaxy | office | 8 | 712.65 | 3.00 | 500 |
| 1103 | galaxy | office | 8 | 720.65 | 3.00 | 400 |
| 1201 | tracker | sports | 8 | 700.00 | NULL | 280 |
| 1202 | tracker | sports | 7 | 745.00 | 3.50 | NULL |
| 1203 | tracker | sports | 6 | 800.00 | 3.50 | 600 |
| 1204 | tracker | sports | 9 | 850.00 | 3.50 | NULL |
+-------+----------------+--------+------+--------+--------+------+
13 rows in set (0.00 sec)

Q)-to display iname , type and quantity of all item.

mysql> select iname,type,qty
-> from item;
+----------------+--------+------+
| iname | type | qty |
+----------------+--------+------+
| school canvas | school | 1200 |
| school canvas | school | 800 |
| school canvas | school | 600 |
| school canvas | school | 2200 |
| school leather | school | 1280 |
| school leather | school | 1100 |
| galaxy | office | 200 |
| galaxy | office | 500 |
| galaxy | office | 400 |
| tracker | sports | 280 |
| tracker | sports | NULL |
| tracker | sports | 600 |
| tracker | sports | NULL |
+----------------+--------+------+
13 rows in set (0.00 sec)

Q)-to display iname, type ,size of those where margin is not inserted.
mysql> select iname,type,qty
-> from item
-> where margin is null;
+----------------+--------+------+
| iname | type | qty |
+----------------+--------+------+
| school leather | school | 1100 |
| tracker | sports | 280 |
+----------------+--------+------+
2 rows in set (0.00 sec)

Q)-to display school type item info.

mysql> select*
-> from item
-> where type="school";
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1003 | school canvas | school | 8 | 145.50 | 2.00 | 600 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
+-------+----------------+--------+------+--------+--------+------+
6 rows in set (0.07 sec)
Q)-to display all info in ascending order based upon cost.
mysql> select*
-> from item
-> order by cost;
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1003 | school canvas | school | 8 | 145.50 | 2.00 | 600 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1201 | tracker | sports | 8 | 700.00 | NULL | 280 |
| 1102 | galaxy | office | 8 | 712.65 | 3.00 | 500 |
| 1103 | galaxy | office | 8 | 720.65 | 3.00 | 400 |
| 1202 | tracker | sports | 7 | 745.00 | 3.50 | NULL |
| 1203 | tracker | sports | 6 | 800.00 | 3.50 | 600 |
| 1204 | tracker | sports | 9 | 850.00 | 3.50 | NULL |
+-------+----------------+--------+------+--------+--------+------+
13 rows in set (0.00 sec)

Q)-to display item name in descending order.

mysql> select iname
-> from item
-> order by iname desc;
+----------------+
| iname |
+----------------+
| tracker |
| tracker |
| tracker |
| tracker |
| school leather |
| school leather |
| school canvas |
| school canvas |
| school canvas |
| school canvas |
| galaxy |
| galaxy |
| galaxy |
+----------------+
13 rows in set (0.00 sec)

Q)-to display item name and price whose price is less than 300.
mysql> select iname,type
-> from item
-> where cost<300;
+----------------+--------+
| iname | type |
+----------------+--------+
| school canvas | school |
| school canvas | school |
| school canvas | school |
| school canvas | school |
| school leather | school |
+----------------+--------+
5 rows in set (0.00 sec)

Q)-to display item info whose price in b/w 300 and 700.
mysql> select*
-> from item
-> where cost between 300 and 700;
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1201 | tracker | sports | 8 | 700.00 | NULL | 280 |
+-------+----------------+--------+------+--------+--------+------+
3 rows in set (0.00 sec)
Q)-to display name , price and quantity whose price is more than 500 and
type is office.
mysql> select iname,cost,qty
-> from item
-> where cost>500 and type='office';
+--------+--------+------+
| iname | cost | qty |
+--------+--------+------+
| galaxy | 640.03 | 200 |
| galaxy | 712.65 | 500 |
| galaxy | 720.65 | 400 |
+--------+--------+------+
3 rows in set (0.00 sec)

Q)-to display office and school item info.

mysql> select*
-> from item
-> where type='school' or type='office';
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1003 | school canvas | school | 8 | 145.50 | 2.00 | 600 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1102 | galaxy | office | 8 | 712.65 | 3.00 | 500 |
| 1103 | galaxy | office | 8 | 720.65 | 3.00 | 400 |
+-------+----------------+--------+------+--------+--------+------+
9 rows in set (0.00 sec)

Q)-to display 6,7,9 size info.

mysql> select*
-> from item
-> where size in(6,7,9);
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1202 | tracker | sports | 7 | 745.00 | 3.50 | NULL |
| 1203 | tracker | sports | 6 | 800.00 | 3.50 | 600 |
| 1204 | tracker | sports | 9 | 850.00 | 3.50 | NULL |
+-------+----------------+--------+------+--------+--------+------+
8 rows in set (0.00 sec)

Q)-to display price and quantity whose item name start with s.
mysql> select cost,qty
-> from item
-> where iname like"s%";
+--------+------+
| cost | qty |
+--------+------+
| 132.50 | 1200 |
| 155.50 | 800 |
| 145.50 | 600 |
| 232.50 | 2200 |
| 232.50 | 1280 |
| 320.23 | 1100 |
+--------+------+
6 rows in set (0.00 sec)

Q)-to display item name whose name end with r.

mysql> select iname from item
-> where iname like"%r";
+----------------+
| iname |
+----------------+
| school leather |
| school leather |
| tracker |
| tracker |
| tracker |
| tracker |
+----------------+
6 rows in set (0.00 sec)

Q)-to display item name,price and qty wherename consist of letter a.

mysql> select iname,cost,qty
-> from item
-> where iname like"%a%";
+----------------+--------+------+
| iname | cost | qty |
+----------------+--------+------+
| school canvas | 132.50 | 1200 |
| school canvas | 155.50 | 800 |
| school canvas | 145.50 | 600 |
| school canvas | 232.50 | 2200 |
| school leather | 232.50 | 1280 |
| school leather | 320.23 | 1100 |
| galaxy | 640.03 | 200 |
| galaxy | 712.65 | 500 |
| galaxy | 720.65 | 400 |
| tracker | 700.00 | 280 |
| tracker | 745.00 | NULL |
| tracker | 800.00 | 600 |
| tracker | 850.00 | NULL |
+----------------+--------+------+
13 rows in set (0.00 sec)
Q)- to display item name whose quantity is not null.
mysql> select iname
-> from item
-> where qty is not null;
+----------------+
| iname |
+----------------+
| school canvas |
| school canvas |
| school canvas |
| school canvas |
| school leather |
| school leather |
| galaxy |
| galaxy |
| galaxy |
| tracker |
| tracker |
+----------------+
11 rows in set (0.00 sec)

Q)-to display item info in descending order of name and ascending order of price.
mysql> select*
-> from item
-> order by iname desc,cost;
+-------+----------------+--------+------+--------+--------+------+
| icode | iname | type | size | cost | margin | qty |
+-------+----------------+--------+------+--------+--------+------+
| 1201 | tracker | sports | 8 | 700.00 | NULL | 280 |
| 1202 | tracker | sports | 7 | 745.00 | 3.50 | NULL |
| 1203 | tracker | sports | 6 | 800.00 | 3.50 | 600 |
| 1204 | tracker | sports | 9 | 850.00 | 3.50 | NULL |
| 1012 | school leather | school | 6 | 232.50 | 2.00 | 1280 |
| 1013 | school leather | school | 8 | 320.23 | NULL | 1100 |
| 1001 | school canvas | school | 6 | 132.50 | 2.00 | 1200 |
| 1003 | school canvas | school | 8 | 145.50 | 2.00 | 600 |
| 1002 | school canvas | school | 7 | 155.50 | 2.00 | 800 |
| 1011 | school canvas | school | 6 | 232.50 | 2.00 | 2200 |
| 1101 | galaxy | office | 7 | 640.03 | 3.00 | 200 |
| 1102 | galaxy | office | 8 | 712.65 | 3.00 | 500 |
| 1103 | galaxy | office | 8 | 720.65 | 3.00 | 400 |
+-------+----------------+--------+------+--------+--------+------+
13 rows in set (0.00 sec)

Q)-to increase margin by 1.

mysql> update item
-> set margin=margin+1;
Query OK, 11 rows affected (0.03 sec)
Rows matched: 13 Changed: 11 Warnings: 0
Q)-increase cost by 20 whose type is school.
mysql> update item
-> set cost=cost+20
-> where type='school';
Query OK, 6 rows affected (0.04 sec)
Rows matched: 6 Changed: 6 Warnings: 0

Q)-to add total price

mysql> alter table item
-> add totalprice numeric(10,2);
Query OK, 13 rows affected (0.25 sec)
Records: 13 Duplicates: 0 Warnings: 0
Q)to find total price.
mysql> update item
-> set totalprice=cost*qty;
Query OK, 11 rows affected (0.03 sec)
Rows matched: 13 Changed: 11 Warnings: 0

Q) to see distinctive type

mysql> select distinct type
-> from item
-> ;
+--------+
| type |
+--------+
| school |
| office |
| sports |
+--------+
3 rows in set (0.00 sec)
Q)to count distinctive types
mysql> select count(distinct type)
-> from item;
+----------------------+
| count(distinct type) |
+----------------------+
| 3|
+----------------------+
1 row in set (0.00 sec)
Q)-to find minimum and maximum price by type.
mysql> select min(cost),max(cost),type
-> from item
-> group by type;
+-----------+-----------+--------+
| min(cost) | max(cost) | type |
+-----------+-----------+--------+
| 640.03 | 720.65 | office |
| 152.50 | 340.23 | school |
| 700.00 | 850.00 | sports |
+-----------+-----------+--------+
3 rows in set (0.00 sec)

Q)-to count number of item in iname.

mysql> select count(*),iname
-> from item;
+----------+---------------+
| count(*) | iname |
+----------+---------------+
| 13 | school canvas |
+----------+---------------+
1 row in set (0.00 sec)
Q) To find sum and avg cost grouped by size.
mysql> select sum(cost),avg(cost),size
-> from item
-> group by size;
+-----------+------------+------+
| sum(cost) | avg(cost) | size |
+-----------+------------+------+
| 1457.50 | 364.375000 | 6 |
| 1560.53 | 520.176667 | 7 |
| 2639.03 | 527.806000 | 8 |
| 850.00 | 850.000000 | 9 |
+-----------+------------+------+

4 rows in set (0.00 sec)

SPORTS
ADMNO Game Coach name Grade
1324 Cricket Narendra A
1364 Volleyball [Link] A
1364 Volleyball [Link] A
1271 Volleyball [Link] B
1434 Basket ball [Link] B
1461 Cricket Narendra B
2328 Basket ball [Link] A
2371 Basket ball [Link] A
1271 Basket ball [Link] A
1434 Cricket Narendra A
2328 Cricket Narendra B
1364 Basket ball [Link] B
Student Table:-
mysql> create table student

-> (admno integer(4) primary key,

-> name varchar(20),

-> class integer(2),

-> sec char(1),

-> rno integer(3),

-> address varchar(25),

-> phone

varchar(22));

Query OK, 0 rows

affected (0.24

sec)
mysql> insert

into student

-> values(1271,"utkarsh madan",12,'c','c-32 punjabi

bagh',4356154); ERROR 1136 (21S01): Column count

doesn't match value count at row 1 mysql> insert into

student

-> values(1271,"utkarsh madan",12,'c',1,'c-32

punjabi bagh',4356154); Query OK, 1 row affected

(0.03 sec) mysql> insert into student

-> values(1324,"naresh sharma",10,'a',2,"31,mohan nagar",435654); Query OK, 1

row affected (0.02 sec) mysql> insert into student

-> values(1325,"[Link]",10,'a',2,'12/21

chandnagar',145654); Query OK, 1 row

affected (0.04 sec) mysql> insert into

student

->

values(1328,'sumedha',10,'b',23,'59,moti

nagar',4135654); Query OK, 1 row

affected (0.03 sec) mysql> insert into

student

-> values(1364,'subya

akyhar',11,'b',13,'12,janakpuri',null);

Query OK, 1 row affected (0.03 sec)

mysql> insert into student

 ->

values(1434,'varuna',12,'b',21,'

69,rohini',null); Query OK, 1

row affected (0.02 sec) mysql>

insert into student

-> values(1461,'david dsouza',11,'b',1,'d-34

modeltown','243554,98787665'); Query OK, 1 row

affected (0.02 sec) mysql> insert into student

-> values(2324,'satinder

singh',12,'c',1,'1/2-gulmohar park',143654);

Query OK, 1 row affected (0.04 sec) mysql>

insert into student

-> values(2328,'peter

jones',10,'a',18,'21/32b,vishalenclave',24356154);

Query OK, 1 row affected (0.05 sec) mysql> insert

into student

-> values(2371,'rohini mehta',11,'c',12,'37,raja garden','435654,6765787');

Query OK, 1 row affected (0.04 sec)

Sports table:-

mysql> create table sports

-> (admno integer(4) not null,

-> game varchar(20),

-> coachname varchar(20),

-> grade char(1),

 -> foreign key(admno)

references student(admno));

Query OK, 0 rows affected (0.33

sec) mysql> insert into sports

->

values(1324,'cricket','n

arendra','a'); Query OK,

1 row affected (0.05

sec) mysql> insert into

sports

->

values(1364,'volleball','

[Link]','a'); Query

OK, 1 row affected

(0.03 sec) mysql> insert

into sports

->

values(1271,'volleball','

[Link]','b'); Query

OK, 1 row affected

(0.02 sec) mysql> insert

into sports

->

values(1434,'basketball','
[Link]','b'); Query

OK, 1 row affected (0.03

sec) mysql> insert into

sports

->

values(1461,'cricket','n

arendra','b'); Query OK,

1 row affected (0.03

sec) mysql> insert into

sports

->

values(2328,'basketball','

[Link]','a'); Query

OK, 1 row affected (0.04

sec) mysql> insert into

sports

-> values(2371,'basketball','[Link]','a');

Query OK, 1 row affected (0.06 sec)

mysql> insert into sports

->

values(2371,'basketball','

[Link]','a'); Query

OK, 1 row affected (0.06

sec) mysql> insert into

sports

->

values(1271,'basketball','

[Link]','a'); Query

OK, 1 row affected (0.03

sec) mysql> insert into

sports

->

values(1434,'cricket','n

arendra','a'); Query OK,

1 row affected (0.04

sec) mysql> insert into

sports

->

values(2328,'cricket','n

arendra','b'); Query OK,

1 row affected (0.02

sec) mysql> insert into

sports

-> values(1364,'basketball','[Link]','b');

Query OK, 1 row affected (0.03 sec)

Q1)-display the details of the students of cricket team.
mysql> select student.*

-> from student,sports

-> where [Link]=[Link] and game='cricket';

Q.2)-display the admission number,name,class,[Link] roll

number of the students whose grade in sprots table is ‘A’.
mysql> select [Link],name,class,sec,rno

-> from student,sports

-> where [Link]=[Link] and grade='a';

Q.3)-display the name and phone number of the students of class 12
who are play some game.
mysql> select name,phone

-> from student,sports

-> where [Link]=[Link] and class=12;

Q4.)-display the names and phone of the students of the

students whose grade is ‘A’ and whose coach is narendra.
mysql> select name,phone

-> from student,sports

-> where [Link]=[Link] and coachname='narendra' and grade='a';