CREATE TABLE salesdepartment
( item VARCHAR(50),
sale number(10)
);
INSERT INTO salesdepartment
(item, sale)
VALUES
('computer', 58);
SELECT item, SUM(sale) AS "TS"
FROM salesdepartment
GROUP BY item;
SELECT item, COUNT(sale) AS "TS"
FROM salesdepartment
GROUP BY item;
SELECT item, min(sale) AS "TS"
FROM salesdepartment
GROUP BY item;
SELECT item, MAX(sale) AS "TS"
FROM salesdepartment
GROUP BY item;
SELECT item, AVG(sale) AS "TS"
FROM salesdepartment
GROUP BY item;
SELECT zone,
MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY zone
HAVING MIN(salary) < '790 USD';
-------------------------------------
CREATE TABLE suppliers
( sup_id number(10),
sup_name varchar2(50),
sup_num number(15)
);
CREATE TABLE shopkeepers
( shop_id number(10),
shop_name varchar2(50),
shop_num number(15)
);
Insert all
INTO suppliers (sup_id, sup_name, sup_num) VALUES (1,'Salahuddin', 03145879546)
INTO suppliers (sup_id, sup_name, sup_num) VALUES (2,'Raza', 03145879985)
INTO suppliers (sup_id, sup_name, sup_num) VALUES (3,'Asif', 03145872134)
INTO suppliers (sup_id, sup_name, sup_num) VALUES (4,'adeel', 03145858795)
INTO suppliers (sup_id, sup_name, sup_num) VALUES (5,'waqar', 031458658947)
SELECT * FROM dual;
Insert all
INTO shopkeepers (shop_id, shop_name, shop_num) VALUES (1,'Abid', 03145872314)
INTO shopkeepers (shop_id, shop_name, shop_num) VALUES (2,'Rashid', 03335872314)
INTO shopkeepers (shop_id, shop_name, shop_num) VALUES (3,'Majid', 03165872314)
INTO shopkeepers (shop_id, shop_name, shop_num) VALUES (4,'Hafiz', 03235872314)
INTO shopkeepers (shop_id, shop_name, shop_num) VALUES (5,'Zahid', 03045872314)
SELECT * FROM dual;
SELECT sup_name
FROM suppliers
UNION
SELECT shop_name
FROM shopkeepers;
SELECT sup_id, sup_name
FROM suppliers
WHERE sup_id <= 3
UNION
SELECT shop_id, shop_name
FROM shopkeepers
WHERE shop_name = 'zahid'
ORDER BY 1;
SELECT sup_id
FROM suppliers
INTERSECT
SELECT shop_id
FROM shopkeepers;
SELECT zone,
MAX(salaries) AS "Highest salary"
FROM employees
GROUP BY zone
HAVING MAX(salaries) > 199000;