aa11

an anonymous user · November 25, 2022
 CREATE TABLE cus (
cid INTEGER PRIMARY KEY,
cname TEXT NOT NULL
);
INSERT INTO cus VALUES (1, 'John');
INSERT INTO cus VALUES (2,'Smith');
INSERT INTO cus VALUES (3,'Ricky' );
INSERT INTO cus VALUES (4, 'Walsh' );
INSERT INTO cus VALUES (5,'stefen' );
INSERT INTO cus VALUES (6, 'fleming');
INSERT INTO cus VALUES (7,'thomson');
INSERT INTO cus VALUES (8,'david' );

-- fetch some values
SELECT * FROM cus;
CREATE TABLE product (
pid INTEGER PRIMARY KEY,
pname TEXT NOT NULL,
pprice INTEGER
);
INSERT INTO product VALUES (1, 'television',19000);
INSERT INTO product VALUES (2, 'DVD',3600);
INSERT INTO product VALUES (3,'washing machine',7600 );
INSERT INTO product VALUES (4, 'computer',35900 );
INSERT INTO product VALUES (5,'Ipod',3210 );
INSERT INTO product VALUES (6, 'Panasonic Phone',2100);
INSERT INTO product VALUES (7,'Chair',360);
INSERT INTO product VALUES (8,'Table',490 );
INSERT INTO product VALUES (9,'Sound System',12050);
INSERT INTO product VALUES (10,'Home Theatre',19350 );
SELECT * FROM product;

CREATE TABLE orde (
oid INTEGER ,
cid INTEGER ,
odate date,
PRIMARY KEY(oid,cid)
);

INSERT INTO orde VALUES (1, 4, '2005-01-10');
INSERT INTO orde VALUES (2, 2, '2006-02-10');
INSERT INTO orde VALUES (3, 3, '2005-03-20');
INSERT INTO orde VALUES (4, 3, '2006-03-10');
INSERT INTO orde VALUES (5, 1, '2007-04-05');
INSERT INTO orde VALUES (6, 7, '2006-12-13');
INSERT INTO orde VALUES (7, 6, '2008-03-13');
INSERT INTO orde VALUES (8, 6, '2004-11-29');
INSERT INTO orde VALUES (9, 5, '2005-01-13');
INSERT INTO orde VALUES (10, 1, '2007-12-12');

SELECT * FROM orde;

CREATE TABLE ordet (
odid INTEGER ,
oid INTEGER ,
pid INTEGER ,
quantity INTEGER,
PRIMARY KEY(odid,oid,pid)
);
INSERT INTO ordet VALUES (1, 1, 3,1);
INSERT INTO ordet VALUES (2, 1, 2,3);
INSERT INTO ordet VALUES (3,2,10,2);
INSERT INTO ordet VALUES (4,3,7,10);
INSERT INTO ordet VALUES (5,3,4,2);
INSERT INTO ordet VALUES (6,3,5,4);
INSERT INTO ordet VALUES (7,4,3,1);
INSERT INTO ordet VALUES (8,5,1,2);
INSERT INTO ordet VALUES (9,5,2,1);
INSERT INTO ordet VALUES (10,6,5,1);
INSERT INTO ordet VALUES (11,7,6,1);
INSERT INTO ordet VALUES (12,8,10,2);
INSERT INTO ordet VALUES (13,8,3,1);
INSERT INTO ordet VALUES (14,9,10,3);
INSERT INTO ordet VALUES (15,10,1,1);
INSERT INTO ordet VALUES (16,10,10,1);
select * from ordet;
Select pr.pname,c.*
from cus c,product pr,ordet od, orde o
where c.cid=o.cid and od.pid=pr.pid and o.oid =od.oid
order by pr.pname;

Select o.oid,p.pname,o.odate,(p.pprice*od.quantity) as tprice 
from orde o,ordet od,product p
where o.oid =od.oid and p.pid =od.pid;

select distinct c.cid, c.cname from cus c, orde o where c.cid  not in ( select cid from orde);
select distinct p.pid,p.pname from product p,ordet o where p.pid not in (select pid from ordet);


Select c.cname,sum(p.pprice*od.quantity) as tprice 
from cus c, orde o,ordet od,product p
where c.cid=o.cid and o.oid =od.oid and p.pid =od.pid group by c.cname;

select c.*, min(o.odate) as fs,max(o.odate) as ls  from orde o ,cus c  ;
select ordet.oid,count(ordet.oid),orde.cid,count(orde.cid) as cust from ordet inner join orde on ordet.oid=orde.oid group by(ordet.oid) ;
select od.pid, max(mx)  in (select od.pid,count(od.pid) as mx from ordet od group by od.pid),ordet od  ;

select od.pid,count(od.pid) as mx from ordet od group by od.pid  ;
Output
(Run the program to view its output)

Comments

Please sign up or log in to contribute to the discussion.