In [1]:
use raj
yes
In [8]:
select id, name, sum(amount) TOTAL_SALES
from   CUSTOMERS, ORDERS
where  id = customer_id
group by id, name;
id name TOTAL_SALES
3 kaushik 16960
2 Khilan 2120
4 Chaitali 10120
6 Komal 6560
5 Hardik 16760
In [15]:
select id, name, count(oid) NUMBER_OF_ORDERS
from CUSTOMERS, ORDERS
where id = customer_id
group by id, name;
id name NUMBER_OF_ORDERS
2 Khilan 2
3 kaushik 7
4 Chaitali 4
5 Hardik 6
6 Komal 1
In [16]:
select id, name, count(oid) NUMBER_OF_ORDERS
from CUSTOMERS, ORDERS
where id = customer_id
group by id, name
having count(oid) > 2;
id name NUMBER_OF_ORDERS
3 kaushik 7
4 Chaitali 4
5 Hardik 6
In [13]:
select count(*) from customers
count(*)
8
In [17]:
show tables
Tables_in_raj
appt
BUILDING
call1
CUSTOMERS
MEDIA
mytable
ORDERS
ROOM
ROOMMEDIA
temp2
In [19]:
select bcode, sum(cap) NUM_SEATS
from   room
group by bcode;
bcode NUM_SEATS
25PP 144
ADHOLD 2655
ARTS 341
CLSO 3083
COE 85
COMMON 200
LANGDL 3906
LIBSO 212
NSC 572
PIED 200
PSC 852
SPARKS 1267
URBAN 736
In [20]:
select bcode from building
bcode
25PP
ADHOLD
ARTS
CLSO
COE
COMMON
KELL
LANGDL
LIBSO
NSC
PIED
PSC
SPARKS
URBAN
In [21]:
select bcode, 0 
from   building 
where  bcode not in 
       (select distinct bcode 
        from   room);
bcode 0
KELL 0
In [24]:
select distinct bcode 
from   room;
bcode
25PP
ADHOLD
ARTS
CLSO
COE
COMMON
LANGDL
LIBSO
NSC
PIED
PSC
SPARKS
URBAN
In [28]:
select bcode, sum(cap) NUM_SEATS
from   room
group by bcode
union
select bcode, 0 
from   building 
where  bcode not in 
       (select distinct bcode 
        from   room);
bcode NUM_SEATS
25PP 144
ADHOLD 2655
ARTS 341
CLSO 3083
COE 85
COMMON 200
LANGDL 3906
LIBSO 212
NSC 572
PIED 200
PSC 852
SPARKS 1267
URBAN 736
KELL 0
In [29]:
SELECT ID, NAME, SALARY
FROM   CUSTOMERS
WHERE  SALARY > 2000 AND age < 25;
ID NAME SALARY
6 Komal 4500.00
7 Muffy 10000.00
In [31]:
select * from customers
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
8 Raj 60 Atlanta 80000.00
In [ ]:
In [ ]: