--------Задание 2------------
-- 1)
SELECT ROUND(AMT)
FROM ORDERS

--2)
SELECT SNAME, DECODE(CITY,NULL,'Не задан',CITY)
FROM Sales

--3)
SELECT ('Рейтинг покупателя ' || UPPER(cname) || ' равен ' || rating ||
CASE
    WHEN rating > 200 THEN ' (Высокий)'
    WHEN rating = 200 THEN ' (Средний)'
    ELSE ' (Низкий)'
END) AS Smth
FROM Clients


--------Задание 3------------
--1)
SELECT COUNT(*)
FROM Orders

--2)
SELECT AVG(LENGTH(CNAME)) FROM
(SELECT  DISTINCT CNAME
FROM Clients)

))
FROM Clients


--3)
SELECT ODATE, MAX(AMT)
FROM Orders
GROUP BY ODATE
ORDER BY MAX(AMT) DESC

--4)
SELECT CITY, SUM(RATING)
FROM Clients
GROUP BY CITY
HAVING SUM(RATING) > 100

--------Задание 4------------
--1)
SELECT DISTINCT s.SNAME, c.CNAME
FROM Sales s
JOIN Clients c
ON s.SNUM = c.SNUM

--2)
SELECT *
FROM Sales s
JOIN Clients c
ON s.SNUM = c.SNUM
WHERE c.CITY IN ('London','Moscow','Sochi')

--3)
SELECT *
FROM Sales s
JOIN Clients c
ON s.SNUM = c.SNUM
WHERE s.CITY = c.CITY

--4)
SELECT s.SNAME, SUM(o.AMT)
FROM Sales s
JOIN Orders o
ON s.SNUM = o.SNUM
GROUP BY s.SNAME
ORDER BY SUM(o.AMT) DESC

-- Orders - Заказы
-- Clients - Клиенты
-- Sales - Продавцы

-----------5--------------------
--1)
WITH
    s AS (SELECT SNUM
    FROM Sales
    WHERE CITY = 'London')
SELECT *
FROM Orders o
WHERE o.SNUM IN (SELECT * FROM s)

--2)
WITH
    s AS (SELECT SNUM
    FROM Sales
    WHERE CITY IN ('London','Springfield'))
SELECT *
FROM Orders o
WHERE o.SNUM IN (SELECT * FROM s)

--3.1)
WITH
    cte1 AS (SELECT SNUM, AVG(COMM) AS COMMAVG
    FROM Sales
    GROUP BY SNUM
    ORDER BY SNUM)
SELECT *
FROM Sales s
WHERE COMM > (SELECT COMMAVG FROM cte1 WHERE s.SNUM = cte1.SNUM)

--3.2)
WITH
    cte1 AS (SELECT AVG(COMM) AS COMMAVG
    FROM Sales)
SELECT *
FROM Sales s
WHERE COMM > (SELECT COMMAVG FROM cte1)

--4)
WITH
    cte1 AS (SELECT CITY, COUNT(*) AS num1 FROM Sales GROUP BY CITY ORDER BY City),
    cte2 AS (SELECT CITY, COUNT(*) AS num2 FROM Clients GROUP BY CITY ORDER BY City),
    cte3 AS (SELECT * FROM cte1 UNION SELECT * FROM cte2)
SELECT CITY, SUM(NUM1)
FROM cte3
GROUP BY CITY
ORDER BY CITY

--5)
SELECT Sales.*, CITY
FROM Sales
ORDER BY SNAME


WITH
    cte1 AS (
              SELECT ROW_NUMBER() OVER (ORDER BY SNAME) AS rownumber, Sales.*
              FROM SALES
            )
SELECT SNUM, SNAME, CITY, COMM
FROM cte1
WHERE rownumber IN (2,3,4)


-----------6--------------------


--1)
SELECT SNAME, SNUM
FROM Sales s
WHERE EXISTS (SELECT SNUM
              FROM Clients c
              WHERE s.SNUM = c.SNUM)

--2)
SELECT SNAME
FROM Sales s1
WHERE COMM > (SELECT AVG(COMM) AS COMMAVG
                FROM Sales s2
                WHERE s1.CITY = s2.CITY)
ORDER BY CITY

--3)
SELECT DISTINCT SNAME, SNUM
FROM Sales s
WHERE EXISTS (SELECT *
              FROM Clients c
              WHERE s.CITY = c.CITY
              AND s.SNUM != c.SNUM)
ORDER BY SNAME

--4)
SELECT DISTINCT SNAME, s.SNUM
FROM Sales s
JOIN Clients c
ON s.CITY = c.CITY
WHERE s.SNUM != c.SNUM
ORDER BY SNAME

--5)
WITH
    etc1 AS (SELECT ODATE, AVG(AMT) AS AVGAMT
             FROM Orders o2
             GROUP BY ODATE)
SELECT o1.ONUM, o1.AMT - o2.AVGAMT AS disp
FROM Orders o1, etc1 o2
WHERE o1.ODATE = o2.ODATE AND o1.ONUM IS NOT NULL
ORDER BY ONUM

---------7--------
--1)
UPDATE Clients
SET CITY = UPPER(CITY)
WHERE CITY LIKE 'S%'

COMMIT

ROLLBACK

SELECT *
FROM Clients
WHERE CITY LIKE 'S%'


--2)
SELECT *
FROM Sales
ORDER BY SNUM

INSERT INTO Sales
VALUES (1, 'Alex', 'Anapa', 1)

COMMIT

DELETE

ROLLBACK

DELETE
FROM Sales
WHERE SNAME = 'Alex'

--3)
SELECT *
FROM Sales
WHERE CITY IS NULL
AND LENGTH(SNAME) > 8

DELETE
FROM Sales
WHERE CITY IS NULL
AND LENGTH(SNAME) > 8

--------8------------
--1)
CREATE TABLE myTable AS
SELECT *
FROM Sales
WHERE SNUM IS NOT NULL
AND SNAME IS NOT NULL
AND CITY IS NOT NULL
AND COMM IS NOT NULL

DROP TABLE myTable

SELECT *
FROM myTable

--2)
CREATE VIEW viewMyTable AS
SELECT sname, city
FROM myTable



--3)
DROP TABLE myTable

--4)
SELECT *
FROM viewMyTable

--5)
--5.1)
CREATE TABLE myTable AS
SELECT *
FROM Sales
WHERE SNUM IS NOT NULL
AND SNAME IS NOT NULL
AND CITY IS NOT NULL
AND COMM IS NOT NULL

--5.2)
SELECT *
FROM viewMyTable

------9----------
DESC Clients
--1)
CREATE TABLE C
(CNUM,
CNAME,
CITY,
RATING,
SNUM) AS
SELECT *
FROM Clients c
GROUP BY CNUM, CNAME, CITY, RATING,SNUM
ORDER BY CNUM

SELECT DISTINCT *
FROM Clients c

SELECT *
FROM C

DROP TABLE C

--2)
CREATE TABLE S
(
SNUM,
SNAME,
CITY,
COMM
) AS
SELECT *
FROM Sales
GROUP BY SNUM, SNAME, CITY, COMM
ORDER BY SNUM

SELECT *
FROM S

--3)
SELECT *
FROM S

-- Delete NULLs in snum
DELETE FROM S
WHERE SNUM IS NULL

-- Delete duplicates in snum
SELECT DISTINCT SNUM
FROM Sales
ORDER BY SNUM

ALTER TABLE S
ADD PRIMARY KEY (SNUM);




--1)
CREATE TABLE C
(CNUM,
CNAME,
CITY,
RATING,
SNUM) AS
(SELECT DISTINCT *
FROM Clients c)

SELECT *
FROM C

--2)
CREATE TABLE S
(
SNUM,
SNAME,
CITY,
COMM
) AS
(SELECT DISTINCT *
FROM Sales s)

SELECT *
FROM Sales s
ORDER BY SNUM

SELECT *
FROM S
ORDER BY SNUM

DROP TABLE S

--3)
-- Delete NULLs in snum
DELETE FROM S
WHERE SNUM IS NULL

SELECT *
FROM S
ORDER BY SNUM

-- Delete duplicates in snum
DELETE FROM S
WHERE ROWID NOT IN
(SELECT MIN(ROWID)
FROM S
GROUP BY SNUM)


ALTER TABLE S
ADD PRIMARY KEY (SNUM);

--4)

SELECT *
FROM C
ORDER BY SNUM

DELETE FROM C
WHERE SNUM NOT IN
(SELECT SNUM
FROM S)

ALTER TABLE C
ADD FOREIGN KEY (SNUM)
REFERENCES S (SNUM)