sql_requests.txt

1 
SELECT * 
 FROM DEPARTMENT
 WHERE LOCATION = 'Monterey';

2
SELECT *
 FROM DEPARTMENT
 WHERE DEPT_NO = 110;

3
SELECT JOB_TITLE, JOB_REQUIREMENT, MIN_SALARY, MAX_SALARY
 FROM JOB
 WHERE (JOB_COUNTRY = 'Japan');

4
SELECT HIRE_DATE
 FROM EMPLOYEE
 WHERE HIRE_DATE <= '31.12.1995' AND HIRE_DATE >= '01.01.1992';

5
SELECT JOB_TITLE
 FROM JOB
 WHERE (MAX_SALARY > 150000);

6
SELECT ITEM_TYPE
 FROM SALES
 WHERE (DISCOUNT > 20);

7
SELECT FIRST_NAME, LAST_NAME, SALARY
 FROM EMPLOYEE
 WHERE SALARY <= 150000 AND SALARY >= 100000
 ORDER BY HIRE_DATE;

8
SELECT FIRST_NAME, LAST_NAME, SALARY
 FROM EMPLOYEE
 WHERE SALARY <= 80000 AND SALARY >= 50000;

9
SELECT SUM(PROJECTED_BUDGET)
 FROM PROJ_DEPT_BUDGET
 WHERE PROJ_ID = 'MKTPR' AND FISCAL_YEAR=1995;

10
SELECT SUM(TOTAL_VALUE)
 FROM SALES
 WHERE EXTRACT(YEAR FROM ORDER_DATE) = 1992;


57
SELECT PROJ_NAME
 FROM PROJECT
 WHERE PROJ_NAME LIKE '%Map%';

58
SELECT LAST_NAME, FIRST_NAME
 FROM EMPLOYEE
 WHERE LAST_NAME STARTING WITH 'Jo';

59
SELECT LAST_NAME, FIRST_NAME
 FROM EMPLOYEE
 WHERE LAST_NAME like '%son';

60
SELECT DEPARTMENT
 FROM DEPARTMENT
 WHERE DEPARTMENT like '%Office%';


1
SELECT JOB_COUNTRY, COUNT(FIRST_NAME)
 FROM EMPLOYEE
 GROUP BY JOB_COUNTRY;

2
SELECT COUNTRY, COUNT(CUSTOMER)
 FROM CUSTOMER
 GROUP BY COUNTRY;

3
SELECT JOB_CODE, EXTRACT(YEAR FROM HIRE_DATE) AS HIREYEAR, COUNT(FIRST_NAME)
 FROM EMPLOYEE
 GROUP BY JOB_CODE, HIREYEAR;

4
SELECT JOB_COUNTRY, EXTRACT(YEAR FROM HIRE_DATE) AS HIREYEAR, COUNT(FIRST_NAME)
 FROM EMPLOYEE
 GROUP BY JOB_COUNTRY, HIREYEAR;

5
SELECT JOB_CODE, EXTRACT(YEAR FROM HIRE_DATE) AS HIREYEAR, COUNT(FIRST_NAME)
 FROM EMPLOYEE
 GROUP BY JOB_CODE, HIREYEAR
 HAVING JOB_CODE = 'Eng';

10
SELECT substring(FIRST_NAME FROM 1 FOR 1), COUNT(*)
 FROM EMPLOYEE
 GROUP BY substring(FIRST_NAME FROM 1 FOR 1);

11
SELECT DEPT_NO, COUNT(FIRST_NAME)
 FROM EMPLOYEE
 GROUP BY DEPT_NO
 HAVING COUNT(FIRST_NAME) <= 2;


1
SELECT COUNT(EMP_NO), C.COUNTRY
 FROM EMPLOYEE E
 JOIN JOB J USING (JOB_CODE, job_grade, job_country)
 RIGHT JOIN COUNTRY C ON (C.COUNTRY=E.job_country) AND J.JOB_TITLE='Engineer'
 group by C.COUNTRY;

2
SELECT COUNT(CUST_NO), C.COUNTRY
 FROM CUSTOMER CU
 RIGHT JOIN COUNTRY C ON (C.COUNTRY=CU.COUNTRY)
 group by C.COUNTRY;

3
SELECT FULL_NAME, JOB_TITLE, MIN_SALARY, MAX_SALARY
 FROM EMPLOYEE
 JOIN JOB USING (JOB_CODE, job_grade, job_country);

4
SELECT MNGR_NO,  AVG(SALARY), COUNT(EMP_NO)
 FROM EMPLOYEE
 JOIN DEPARTMENT USING (DEPT_NO)
 GROUP BY MNGR_NO;

5
SELECT HEAD_DEPT, COUNT(DEPARTMENT), SUM (PROJECTED_BUDGET)
 FROM DEPARTMENT
 LEFT JOIN proj_dept_budget USING (DEPT_NO)
 group by HEAD_DEPT;

6
SELECT CUSTOMER, EXTRACT (YEAR FROM ORDER_DATE)
 FROM CUSTOMER
 JOIN SALES USING (CUST_NO)
 WHERE (ORDER_DATE)<=(SHIP_DATE);


1
select FULL_NAME, salary, JOB_COUNTRY
FROM EMPLOYEE
WHERE (JOB_COUNTRY='Canada' OR JOB_COUNTRY='England')

3
SELECT PROJ_NAME, DEPT_NO, PROJECTED_BUDGET
 FROM PROJECT
 JOIN PROJ_DEPT_BUDGET USING (PROJ_ID)
 WHERE PROJECTED_BUDGET<1000000


1
with t1 as ( select JOB_COUNTRY as country, min(SALARY) as min_salary, max(SALARY) as max_salary
  from EMPLOYEE  where JOB_COUNTRY = 'Canada' or JOB_COUNTRY='England' group by JOB_COUNTRY)
select FIRST_NAME, SALARY, country, max_salary, min_salary 
  from EMPLOYEE 
  inner join t1 on t1.country = JOB_COUNTRY


2
with t1 as (select DEPARTMENT.DEPT_NO, PROJ_ID, MNGR_NO
  from EMPLOYEE
  inner join EMPLOYEE_PROJECT EP on EMPLOYEE.EMP_NO = EP.EMP_NO
  inner join DEPARTMENT on EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO
  where extract(YEAR from HIRE_DATE) = 1994)
select PROJECT.PROJ_ID, PROJ_NAME, DEPARTMENT.DEPT_NO, DEPARTMENT.DEPARTMENT, EMPLOYEE.EMP_NO, EMPLOYEE.FULL_NAME 
  from PROJECT
  left join t1 on t1.PROJ_ID = PROJECT.PROJ_ID
  left join DEPARTMENT on t1.DEPT_NO = DEPARTMENT.DEPT_NO
  left join EMPLOYEE on t1.MNGR_NO = EMPLOYEE.EMP_NO


4
select DEPARTMENT 
  from DEPARTMENT 
  where BUDGET > (select budget from DEPARTMENT where DEPT_NO = 130)


1
select * 
  from AGENT 
  where ID_AG in (select ID_AG from OPERATION 
  inner join GOODS G on OPERATION.ID_GOODS = G.ID_GOODS where NOMENCLATURE = 'Папки')


2
select * 
  from GOODS 
  where ID_GOODS not in (SELECT OPERATION.ID_GOODS from OPERATION)


1
INSERT INTO GOODS (ID_GOODS, NOMENCLATURE, MEASURE)
  VALUES ('T16', 'Краски', 'уп')

INSERT INTO GOODS (ID_GOODS, NOMENCLATURE, MEASURE)
  VALUES ('T18', 'Скотч', 'шт')

INSERT INTO WAREHOUSE (ID_WH, NAME, TOWN)
  VALUES ('C9', 'Склад на Северном', 'Азов')


2
INSERT INTO OPERATION (ID, ID_GOODS, ID_AG, ID_WH, TYPEOP, QUANTITY, PRICE, OP_DATE)
  SELECT NOMENCLATURE
  FROM GOODS
  UNION
  SELECT NAME_AG
  FROM AGENT
  UNION
  SELECT NAME
  FROM WAREHOUSE


3
UPDATE OPERATION
  SET PRICE=3000, QUANTITY=20
  WHERE  TYPEOP='A' AND ID_AG='p3'


4
UPDATE OPERATION
  SET PRICE=PRICE*1.2
  WHERE exists
  (SELECT *
  FROM OPERATION
  JOIN AGENT USING (ID_AG)
  WHERE NAME_AG='Астра')


5
DELETE FROM AGENT AG
    WHERE NOT EXISTS
    (SELECT ID_AG
    FROM OPERATION
    WHERE AG.id_ag=ID_AG)


6
DELETE FROM WAREHOUSE wh 
  WHERE wh.ID_WH NOT IN SELECT gwh.ID_WH FROM GOODS_WH gwh)
  AND wh.ID_WH NOT IN  (SELECT op.ID_WH FROM OPERATION op)


1
create or alter procedure INSERT_WAREHOUSE
 (NEW_ID type of column WAREHOUSE.id_wh,
  NEW_NAME type of column WAREHOUSE.name,
  NEW_TOWN type of column WAREHOUSE.town)
as
  begin
  insert into WAREHOUSE
  (id_wh, name, town)
     values (:NEW_ID, :NEW_NAME, :NEW_TOWN);
  end


create or alter procedure INSERT_AGENT
 (NEW_ID type of column AGENT.id_ag,
  NEW_NAME type of column AGENT.name_ag,
  NEW_TOWN type of column AGENT.town,
  NEW_PHONE type of column AGENT.phone)
as
  begin
  insert into AGENT
  (id_ag, name_ag, town, phone)
     values (:NEW_ID, :NEW_NAME, :NEW_TOWN, :NEW_PHONE);
  end

EXECUTE PROCEDURE INSERT_AGENT
  ('p25', 'Astel', 'Ростов', '277-45-78');


2
create or alter procedure REST_INSERT
  (GOODS type of column GOODS.NOMENCLATURE,
   WH type of column WAREHOUSE.NAME,
   Q type of column GOODS_WH.QUANTITY)
AS
   declare variable IDG type of column GOODS.ID_GOODS;
   declare variable IDW type of column WAREHOUSE.ID_WH;
     BEGIN
       idg = (select A.id_goods  from goods A where A.nomenclature = :goods);
       idw = (select W.id_wh   from warehouse W where W.name=:wh);
       insert into goods_wh
          values (null, :idg, :idw, :q);
     END


3
create or alter procedure REST_DELETE
  (GOODS type of column GOODS.NOMENCLATURE,
   WH type of column WAREHOUSE.NAME)

AS
   declare variable IDG type of column GOODS.ID_GOODS;
   declare variable IDW type of column WAREHOUSE.ID_WH;

BEGIN
     idg = (select A.id_goods  from goods A where A.nomenclature = :goods);
       if (idg is null) then
        begin
          insert into log_file (inform)
            values ('ошибка в названии товара '||:goods);
          exit;
        end

      idw = (select W.id_wh   from warehouse W where W.name=:wh);
       if (idw is null) then
        begin
          insert into log_file (inform)
            values ('ошибка в названии склада '||:wh);
          exit;
        end

      DELETE FROM goods_wh GW
      WHERE :IDG=GW.id_goods AND :IDW=GW.id_wh;
      insert into log_file (inform) values ('остаток удалён');
END

execute procedure REST_DELETE
    ('Открытка','Склад 2')

2
create or alter procedure goodsbyagent (
    agent type of column agent.name_ag)
returns (
    goods type of column goods.nomenclature,
    cnt integer)
as
declare variable id type of column agent.id_ag;
begin
  /* procedure text */
  id = (select a.id_ag
        from agent a
        where a.name_ag = :agent);
  if (id is null) then
  begin
    in autonomous transaction do
      insert into log_file (inform)
      values ('в бд нет агента ' || :agent);
    exception error;
  end
  for select g.nomenclature, count(o.id)
      from operation o
      join goods g using (id_goods)
      where o.id_ag = :id
      group by g.nomenclature
      into :goods, :cnt
  do
    suspend;
end

select *
from goodsbyagent('Надежный')