1

Topic: Some simple requests

To all greetings, it is very necessary the help of kind and clever people) There is rather small job executable in oracle database 11g XE and SQL Developer, and time was specific to understand language at present is not present absolutely, is occupied in development of game and one application... Something wrote already, namely created tables, filled, made pair of requests. Under the link there will be a photo (in high quality), and here that wrote (only do not pay attention to titles of fields, over them especially did not think even)).
Photo: http://imageban.ru/show/2018/03/09/25b2 … 49b610/jpg
Already written (in a notepad saved for convenience of reading to):
1.===================================================================> tables
create table cure (
kod_tovara number (5) NOT NULL;
tovar_name varchar2 (60) NOT NULL;
ed_izm number (3) NOT NULL;
srok_godnosti number (3) NOT NULL;
data_vypuska date;
CONSTRAINT cure_pk PRIMARY KEY (kod_tovara)
);
create table postavshik (
kod_postavshika number (5) NOT NULL;
name_postavshika varchar2 (40) NOT NULL;
adres_postavshika varchar2 (60) NOT NULL;
tel_postavshika number (13) NOT NULL;
CONSTRAINT postavshik_pk PRIMARY KEY (kod_postavshika)
);
create table schet (
n_scheta number (5) NOT NULL;
data_postavki date;
kod_postavshika number (4) NOT NULL;
tsena_pokupki number (6) NOT NULL;
kod_tovara number (5) NOT NULL;
CONSTRAINT schet_fk FOREIGN KEY (kod_postavshika) REFERENCES postavshik (kod_postavshika);
CONSTRAINT fk_schet FOREIGN KEY (kod_tovara) REFERENCES cure (kod_tovara)
);
2.==============================================================================> the data
2.1.=============================================================================> cure
INSERT INTO cure (kod_tovara, tovar_name, ed_izm, srok_godnosti, data_vypuska) VALUES
(' 1000 ', ' the Energizer ', ' 90 ', ' 180 ', ' 10/22/2017 ');
INSERT INTO cure (kod_tovara, tovar_name, ed_izm, srok_godnosti, data_vypuska) VALUES
(' 1011 ', ' Antismertin ', ' 85 ', ' 34 ', ' 11/13/2017 ');
INSERT INTO cure (kod_tovara, tovar_name, ed_izm, srok_godnosti, data_vypuska) VALUES
(' 1200 ', ' Golovanebolin ', ' 85 ', ' 34 ', ' 11/13/2017 ');
INSERT INTO cure (kod_tovara, tovar_name, ed_izm, srok_godnosti, data_vypuska) VALUES
(' 1132 ', ' Tsitramon ', ' 32 ', ' 180 ', ' 12/17/2017 ');
INSERT INTO cure (kod_tovara, tovar_name, ed_izm, srok_godnosti, data_vypuska) VALUES
(' 1055 ', ' Validol ', ' 5 ', ' 210 ', ' 10/10/2017 ');
2.2.===============================================================================> postavshik
INSERT INTO postavshik (kod_postavshika, name_postavshika, adres_postavshika, tel_postavshika) VALUES
(322, ' Gazprom ', ' Soviet 5 ', 89879338251);
INSERT INTO postavshik (kod_postavshika, name_postavshika, adres_postavshika, tel_postavshika) VALUES
(401, ' Putintim ', ' Stable 18 ', 8769076543);
INSERT INTO postavshik (kod_postavshika, name_postavshika, adres_postavshika, tel_postavshika) VALUES
(213, ' Bayer ', ' German 45 ', 19411945);
INSERT INTO postavshik (kod_postavshika, name_postavshika, adres_postavshika, tel_postavshika) VALUES
(168, ' Urallekstroj ', ' Tank 50 ', 8680396830);
INSERT INTO postavshik (kod_postavshika, name_postavshika, adres_postavshika, tel_postavshika) VALUES
(565, ' Medgorkorp ', ' Botanical 8 ', 898467455);
2.3.==========================================================================================> schet
INSERT INTO schet (n_scheta, data_postavki, kod_postavshika, tsena_pokupki, kod_tovara, kol_pol_tovara) VALUES
(1200, ' 2/20/2016 ', 322, 22600, 1000, 20);
INSERT INTO schet (n_scheta, data_postavki, kod_postavshika, tsena_pokupki, kod_tovara, kol_pol_tovara) VALUES
(1248, ' 2/28/2016 ', 401, 23200, 1011, 30);
INSERT INTO schet (n_scheta, data_postavki, kod_postavshika, tsena_pokupki, kod_tovara, kol_pol_tovara) VALUES
(1120, ' 3/15/2016 ', 213, 31233, 1200, 40);
INSERT INTO schet (n_scheta, data_postavki, kod_postavshika, tsena_pokupki, kod_tovara, kol_pol_tovara) VALUES
(1300, ' 3/21/2016 ', 168, 45600, 1132, 50);
INSERT INTO schet (n_scheta, data_postavki, kod_postavshika, tsena_pokupki, kod_tovara, kol_pol_tovara) VALUES
(1349, ' 2/30/2016 ', 565, 31421, 1055, 60);
3.===========================================================================> with isp SELECT.
3.1.==========================================================================================> the total under each account
SELECT n_scheta, (kol_pol_tovara*tsena_pokupki) AS SUMS FROM schet;
3.3.=======================================================================> all medicines with certain working life
SELECT * from cure WHERE srok_godnosti> 100;
For minutes rummaging here operation on 10 probably, I will pay back the one who helps.

2

Re: Some simple requests

P.S In the code forgot to correct some small jambs, for example numbers in single quotes, at myself at a code dial-up in sql developer I removed single quotes.

3

Re: Some simple requests

Pliman wrote:

I will pay back the one who helps.

to You who do not have time for labs, - in a forum "operation".

4

Re: Some simple requests

SELECT n_scheta, sum (kol_pol_tovara*tsena_pokupki) AS SUMS FROM schet group by n_scheta

5

Re: Some simple requests

Pliman;
Write in tg from a profile, we consider