SQl 4lab

=========
1 часть
=========
1)----------------------------------
- - - - - - - - - - - - - - - - - - -

2)---------------------------------
CREATE DATABASE Parts_and_suppliers;

3)---------------------------------
SHOW DATABASES;
USE Parts_and_suppliers;

4)---------------------------------
CREATE TABLE S
(П CHAR(5) not null,
ИМЯ CHAR(20),
СТАТУС NUMERIC,
ГОРОД CHAR (20),
PRIMARY KEY (П));

5)---------------------------------
SHOW TABLES;

6)---------------------------------
CREATE TABLE P
(Д CHAR(5) not null,
НАЗВАНИЕ CHAR(20),
ЦВЕТ CHAR(20),
ВЕС NUMERIC,
ГОРОД CHAR (20),
PRIMARY KEY (Д));

CREATE TABLE SP
(П CHAR(5) not null,
Д CHAR(5) not null,
КОЛИЧЕСТВО NUMERIC,
PRIMARY KEY (Д, П));

7)---------------------------------
INSERT INTO S (П, ИМЯ, СТАТУС, ГОРОД) VALUES
('S1', 'Вениамин','20', 'Москва'),
('S2', 'Алексей', 10, 'Киев'),
('S3', 'Федор', 30, 'Киев'),
('S4', 'Игорь', 20, 'Москва'),
('S5', 'Вадим', 30, 'Минск');

INSERT INTO P (Д, Название, Цвет, Вес, Город) VALUES
('P1', 'Гайка', 'Красный', 12, 'Москва'),
('P2', 'Болт', 'Черный', 17, 'Киев'),
('P3', 'Винт', 'Голубой', 17, 'Ростов'),
('P4', 'Винт', 'Красный', 14, 'Москва'),
('P5', 'Шпилька', 'Голубой', 12, 'Киев'),
('P6', 'Палец', 'Красный', 19, 'Москва');

INSERT INTO SP (П, Д, Количество) VALUES
('S1', 'P1', 300),
('S2', 'P1', 300),
('S1', 'P2', 200),
('S2', 'P2', 400),
('S3', 'P2', 200),
('S4', 'P2', 200),
('S1', 'P3', 400),
('S1', 'P4', 200),
('S4', 'P4', 300),
('S1', 'P5', 100),
('S4', 'P5', 400),
('S1', 'P6', 100);

8)---------------------------------
SELECT p.Цвет, p.Город
FROM p
WHERE (((p.Город)<>"Киев")
And ((p.Вес)>10));

9)---------------------------------
SELECT DISTINCT p.Цвет, p.Город
FROM p
WHERE (((p.Город)<>"Киев")
And ((p.Вес)>10));

10, 11)---------------------------------
SELECT P.Д, P.ВЕС/1000 AS КГ
FROM P;

12)---------------------------------
SELECT *
FROM S;

13, 14)---------------------------------
SELECT S.п, S.имя, S.статус, S.город, P.Д, P.город
FROM S, P
WHERE S.город=P.город;

15, 16)---------------------------------
SELECT first.П AS ПA, second.П AS ПB, second.Город
FROM s AS first, s AS second
WHERE (((first.П)<second.П)
AND ((first.Город)=second.город));

17, 18)---------------------------------
SELECT COUNT(*) AS N
FROM S;

19, 20)---------------------------------
SELECT MAX(SP.КОЛИЧЕСТВО) AS MAXQ, MIN(SP.КОЛИЧЕСТВО) AS
MINQ
FROM SP
WHERE SP.Д='P2';

21, 22)---------------------------------
SELECT SP.Д, SUM(SP.КОЛИЧЕСТВО) AS ОБЩЕЕ_КОЛИЧЕСТВО
FROM SP
GROUP BY SP.Д;

или

SELECT P.Д, (SELECT SUM (SP.КОЛИЧЕСТВО)
FROM SP
WHERE SP.Д = P.Д) AS ОБЩЕЕ_КОЛИЧЕСТВО
FROM P;
(не работает)

23, 24)---------------------------------
SELECT S.П
FROM S
WHERE S.СТАТУС < ( SELECT MAX (S.СТАТУС)
FROM S);

25, 26)---------------------------------
SELECT DISTINCT S.ИМЯ
FROM S
WHERE EXISTS (SELECT *
FROM SP
WHERE SP.П = S.П AND SP.Д = 'P2');

27, 28)---------------------------------
SELECT DISTINCT S.ИМЯ
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE SP.П = S.П
AND SP.Д = 'P2');

29, 30)---------------------------------
SELECT DISTINCT S.ИМЯ
FROM S
WHERE NOT EXISTS
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE SP.П =S.П AND SP.Д =P.Д));

или

SELECT DISTINCT S.ИМЯ
FROM S
WHERE
(SELECT COUNT (SP.П)
FROM SP
WHERE SP.П=S.П) = (SELECT COUNT (P.Д) FROM P);
(не работает)

31, 32)---------------------------------
SELECT P.Д
FROM P
WHERE P.ВЕС >16
UNION
SELECT SP.Д
FROM SP
WHERE SP.П='S2';

33)---------------------------------

========
2 часть
========
CREATE TABLE J
(J CHAR(5) not null,
ИМЯ CHAR(5) ,
ГОРОД CHAR (20),
PRIMARY KEY (J));

CREATE TABLE SPJ
(П CHAR(5) not null,
Д CHAR(5) not null,
J CHAR(5) not null,
Количество int not null,
PRIMARY KEY (П, Д, J));

INSERT INTO J (J, Имя, Город) VALUES
('J1', 'Л1', 'Москва'),
('J2', 'Л2', 'Киев'),
('J3', 'Л3', 'Москва'),
('J4', 'К2', 'Ростов'),
('J5', 'К3', 'Киев'),
('J6', 'Ф1', 'Ростов'),
('J7', 'Ф2', 'Москва');

INSERT INTO SPJ (П, Д, J, Количество) VALUES
('S1', 'P1', 'J1', 200),
('S1', 'P1', 'J4', 100),
('S2', 'P3', 'J1', 400),
('S2', 'P3', 'J2', 200),
('S2', 'P3', 'J4', 500),
('S2', 'P3', 'J5', 600),
('S2', 'P3', 'J6', 400),
('S2', 'P3', 'J7', 800),
('S2', 'P5', 'J2', 100),
('S3', 'P3', 'J1', 200),
('S3', 'P4', 'J2', 500),
('S4', 'P4', 'J3', 300),
('S4', 'P6', 'J7', 300),
('S5', 'P1', 'J4', 100),
('S5', 'P2', 'J2', 200),
('S5', 'P2', 'J4', 100),
('S5', 'P3', 'J4', 200),
('S5', 'P4', 'J4', 800),
('S5', 'P5', 'J4', 400),
('S5', 'P5', 'J5', 500),
('S5', 'P5', 'J7', 100),
('S5', 'P6', 'J2', 200),
('S5', 'P6', 'J4', 500);


№№№№№№№№№№№№№№№№№№№№№№№№№№№№
для связки таблицы

ALTER TABLE `sp` ADD FOREIGN KEY (`П`) REFERENCES `p`(`П`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `sp` ADD FOREIGN KEY (`Д`) REFERENCES `p`(`Д`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `spj` ADD FOREIGN KEY (`П`) REFERENCES `s`(`П`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `spj` ADD FOREIGN KEY (`Д`) REFERENCES `p`(`Д`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `spj` ADD FOREIGN KEY (`J`) REFERENCES `j`(`J`) ON DELETE RESTRICT ON UPDATE RESTRICT;


==========
Вариант 2
==========

1)Получите полную информацию о всех проектах в Москве.
____________________________________________________
SELECT * FROM J
    WHERE J.Город = "Москва"


------------------------------------------------------------------

Пример_9: Получить все пары номеров поставщиков, таких, что оба
поставщика в каждой паре размещаются в одном и том же городе:
SELECT first.П AS ПA, second.П AS ПB, second.Город
FROM s AS first, s AS second
WHERE (((first.П)<second.П)
AND ((first.Город)=second.город));

2)Получите все такие тройки «номера поставщиков – номера деталей – номера проектов»,
    для которых никакие из двух
    выводимых поставщиков, деталей и проектов не размещены в одном
    городе.
    ____________________________________________________ 261, spj AS third
SELECT first.П AS ПA, first.Д AS ДА, first.J AS JА, second.П AS ПB, second.Д AS ДB, second.J AS JB
    FROM spj AS first, spj AS second
    JOIN s
    JOIN p
    JOIN j
        WHERE (((first.П)<second.П)
            AND ((first.Д)<second.Д)
            AND ((first.J)<second.J)
            AND ((first.Город)=second.город)
            AND ((first.Город)=second.город))
            AND ()
            AND ());