áá±áá¬áá±á·á áºáá±á¬áááᯠSQL áá¬áá¬á áá¬ážááᯠá¡áá¯á¶ážááŒá¯ááá·áº áááºá áẠDBMSs áá»á¬ážá ááœáŸááºážááá¯ážáá¬ážáááºááŸá¬ ááŒá¬áá«ááŒá®á áá±á«áºáá±á«ááºáá¬áá±á¬ áá»áá¯ážááœá²áá»á¬ážááᯠNoSQL áá¯áá±á«áºáááºá áááºážááá¯á·ááẠá€á á»á±ážááœááºááœáẠáááºážááá¯á·á¡ááœáẠáá áºáá±áá¬áá¬ááᯠááœááºážáá¯ááá¯ááºáá²á·áá±á¬áºáááºáž áááºááœááºáá±á¬ DBMS áá»á¬ážááẠááá±áá±á¬á·áá² áááºážááá¯á·ááááºááœááºáá»ááºáá»á¬ážá¡ááœáẠáááºáááºáááºááŒáœá áœá¬ á¡áá¯á¶ážááŒá¯áá±ááŒáá«áááºá
áá®áá±á¬ááºážáá«ážááŸá¬ functional database áá
áºáá¯áá²á· ááá±á¬ááá¬ážááᯠáá±á¬áºááŒáá»ááºáá«áááºá ááá¯áá¬ážáááºááá¯ááºá
á±áááºá áááºážááᯠclassical relational model ááŸáá·áº ááŸáá¯ááºážááŸááºááŒááºážááŒáá·áº áááºážááᯠáá¯ááºáá±á¬ááºáá«áááºá á¡ááºáá¬áááºáá±á«áºááŸá SQL á
ááºážáááºááŸá¯á¡áá»áá¯ážáá»áá¯ážá០ááŒá¿áá¬áá»á¬ážááᯠááá°áá¬á¡ááŒá
Ạá¡áá¯á¶ážááŒá¯áá«áááºá
áááá«ááºáž
áááºá ááºáá±áá¬áá±á·á áºáá»á¬ážááẠááá¬ážáá»á¬ážááŸáá·áº á¡ááœááºáá»á¬ážáá±á«áºááœáẠáá¯ááºáá±á¬ááºáááºá áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá±áá¬áá±á·á áºáá áºáá¯ááœááºá á¡áááºážáá»á¬ážááŸáá·áº áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠá¡áá®ážáá®áž á¡áá¯á¶ážááŒá¯áááºááŒá áºáááºá N ááá¯ááºáá»á¬ážáá«áá±á¬ ááá¬ážáá áºáá¯ááŸá á¡ááœááºáá áºáá¯ááᯠN ááá·áºáááºáá»ááºáá»á¬ážá áá¯ááºáá±á¬ááºáá»ááºá¡ááŒá Ạááá¯ááºá á¬ážááŒá¯áááºááŒá áºáááºá ááá¬ážáá»á¬ážááŒá¬áž áááºáá¶áá±ážá¡á á¬ážá áá»áááºáááºááŸá¯ááŒá¯áá¯ááºáá¬ážááá·áº class á á¡áá¬ááá¹áá¯áá»á¬ážááᯠááŒááºáá±ážááá·áº áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠá¡áá¯á¶ážááŒá¯áááºááŒá áºáááºá JOIN á¡á á¬áž áá¯ááºáá±á¬ááºáá»ááºááœá²á·á ááºážááŸá¯ááᯠá¡áá¯á¶ážááŒá¯áá«áááºá
á¡áá¯ááºáá»á¬ážááᯠááá¯ááºááá¯ááºáááœáŸá±á·áá®á áá»áœááºá¯ááºááẠááá¯ááááºážáá±á¬á·áá»á áºá áá¯ááºáááºážáá¬áááºááᯠáá±á¬áºááŒáá«áááºá DDL á¡ááœáẠáá»áœááºá¯ááºááẠPostgreSQL syntax ááá¯áá¯á¶ážáá«áááºá functional áá»á¬ážá¡ááœááºáááºážáááá¯ááºááá¯áẠsyntax ááŸááááºá
ááá¬ážááœááºáá»á¬áž
á¡áááºááŸáá·áº á á»á±ážááŸá¯ááºážá¡ááœááºáá»á¬ážáá«áá±á¬ ááá¯ážááŸááºážáá±á¬ Sku á¡áá¬ááá¹áá¯
áááºá ááºááŸá¯
CREATE TABLE Sku
(
id bigint NOT NULL,
name character varying(100),
price numeric(10,5),
CONSTRAINT id_pkey PRIMARY KEY (id)
)
á¡áá¯ááºáá¯ááºáá«áááº
CLASS Sku;
name = DATA STRING[100] (Sku);
price = DATA NUMERIC[10,5] (Sku);
ááŸá áºáá¯ááŒá±áá¬áááºá áá¯ááºáááºážáá±á¬ááºáá¬ááá·áºáááºáá»ááºáá áºáá¯á¡áá±ááŒáá·áº Sku ááᯠááá·áºááœááºážááŒá®áž áá°áá¡áá»áá¯ážá¡á á¬ážááᯠááŒááºáá±ážáááºá
áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ DBMS áá áºáá¯ááœáẠá¡áá¬ááá¹áá¯áá áºáá¯á á®ááœáẠá¡ááá¯á¡áá»á±á¬ááºáá¯ááºáá±ážááá·áº á¡ááœááºážáá¯ááºá¡áá»áá¯á· áá«ááŸááááºááŒá áºááŒá®áž ááá¯á¡ááºáá«á áááºáá±á¬ááºá¡áá¯á¶ážááŒá¯ááá¯ááºáááºáᯠáá°ááá«áááºá
áá¯ááºáá á¹á ááºáž/á ááá¯ážááá¯ááº/áá±ážááœááºážáá°áá»á¬ážá¡ááœáẠá á»á±ážááŸá¯ááºážáááºááŸááºááŒáá«á áá¯á·á á¡áá»áááºááŸáá·áºá¡áá»áŸ ááŒá±á¬ááºážáá²ááá¯ááºáá±á¬ááŒá±á¬áá·áº á¡áá»áááºá¡ááœááºáá áºáá¯ááᯠááá¬ážááœáẠááá·áºááá¯ááºááŒáá«á áá¯á·á áá¯ááºááᯠá¡ááá¯áá»á¯á¶á·ááẠáááºá ááºáá±áá¬áá±á·á áºááŸá áááºážááœáŸááºáá»á¬ážá¡ááœáẠááá¬ážáá»á¬ážááᯠááŒá±áá¬ááŒááºážááᯠáá»á±á¬áºááœá¬ážáá«áááº-
áááºá ááºááŸá¯
CREATE TABLE prices
(
skuId bigint NOT NULL,
storeId bigint NOT NULL,
supplierId bigint NOT NULL,
dateTime timestamp without time zone,
price numeric(10,5),
CONSTRAINT prices_pkey PRIMARY KEY (skuId, storeId, supplierId)
)
á¡áá¯ááºáá¯ááºáá«áááº
CLASS Sku;
CLASS Store;
CLASS Supplier;
dateTime = DATA DATETIME (Sku, Store, Supplier);
price = DATA NUMERIC[10,5] (Sku, Store, Supplier);
á¡ááœáŸááºážááááºáž
áá±á¬ááºáá¯á¶ážá¥ááá¬á¡ááœááºá áááºááŸááºáá¬ážáá±á¬á¡áá»áááºáá áºáá¯á¡ááœáẠá á»á±ážááŸá¯ááºážááᯠá¡ááŒááºááŸá¬ááœá±ááá¯ááºááẠáá±á¬á·áá»á¬ážá¡á¬ážáá¯á¶ážááŸáá·áº áááºá áœá²áá±á«áºááœáẠá¡ááœáŸááºážáá áºáá¯áááºáá±á¬ááºáá«áááºá
áááºá ááºááŸá¯
CREATE INDEX prices_date
ON prices
(skuId, storeId, supplierId, dateTime)
á¡áá¯ááºáá¯ááºáá«áááº
INDEX Sku sk, Store st, Supplier sp, dateTime(sk, st, sp);
áá¬áááºáá»á¬ážááá¯
áááºááá¯ááºáá±á¬ááá
á¹á
áá»á¬ážá០ááá¯ážááŸááºážáá±á¬ ááŒá¿áá¬áá»á¬ážááŒáá·áº á
áááºááŒáá«á
áá¯á·
áŠážá áœá¬á ááá¯ááááºážáá±á¬á·áá»á áºááᯠááŒá±áá¬ááŒáá«á áá¯á· (áááºá ááºáá±áá¬áá±á·á áºá¡ááœáẠáááºážááᯠá¡áááºáá«áá±á¬ááºážáá«ážááœáẠááá¯ááºááá¯ááºáá¯ááºáá±á¬ááºáááº)á
CLASS Department;
name = DATA STRING[100] (Department);
CLASS Employee;
department = DATA Department (Employee);
chief = DATA Employee (Employee);
name = DATA STRING[100] (Employee);
salary = DATA NUMERIC[14,2] (Employee);
á¡áá¯áẠá
áááºážááá¯á·ááá»ááºáá»ááºážááŒá®ážááŒááºáá±ážááŸá°ážááẠáá á¬ááá¯ááŒá®ážáá±á¬ áááºáááºážáá»á¬ážá á¬áááºážááᯠááŒááá«á
áááºá ááºááŸá¯
select a.*
from employee a, employee b
where b.id = a.chief_id
and a.salary > b.salary
á¡áá¯ááºáá¯ááºáá«áááº
SELECT name(Employee a) WHERE salary(a) > salary(chief(a));
á¡áá¯áẠá
ááááááá¯á·áá¬áááœáẠáá á¬á¡áá»á¬ážáá¯á¶ážáááŸááá±á¬ áááºáááºážáá»á¬ážááᯠá á¬áááºážááŒá¯á á¯áá«á
áááºá ááºááŸá¯
select a.*
from employee a
where a.salary = ( select max(salary) from employee b
where b.department_id = a.department_id )
á¡áá¯ááºáá¯ááºáá«áááº
maxSalary 'ÐакÑОЌалÑÐœÐ°Ñ Ð·Ð°ÑплаÑа' (Department s) =
GROUP MAX salary(Employee e) IF department(e) = s;
SELECT name(Employee a) WHERE salary(a) = maxSalary(department(a));
// ОлО еÑлО "заОМлайМОÑÑ"
SELECT name(Employee a) WHERE
salary(a) = maxSalary(GROUP MAX salary(Employee e) IF department(e) = department(a));
á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ááŸá áºáá¯áá¯á¶ážááẠáá®áá»áŸáááºá áááááá á¹á á¡ááœááºá áááºá ááºáá±áá¬áá±á·á áºáá áºáá¯ááœáẠáááºááẠCREATE VIEW ááá¯áá¯á¶ážááá¯ááºáááºá ááá¯áááºážá¡á¬ážááŒáá·áº áááºážááŸá áá®ážááŒá¬ážáá¬ááá áºáá¯á¡ááœáẠá¡ááŒáá·áºáá¯á¶ážáá á¬ááᯠáŠážá áœá¬ááœááºáá»ááºáááºááŒá áºáááºá á¡á±á¬ááºááœááºáá±á¬áºááŒáá¬ážáá±á¬á¡áá»ááºááŸá¬ ááŸááºážááŸááºážáááºážáááºážáááááºá á¡ááŒá±ááᯠááá¯ááá¯áá±á¬ááºážááœááºá áœá¬áááºáááºá á±áá±á¬ááŒá±á¬áá·áº áááá¡ááŸá¯ááᯠáá»áœááºá¯ááºá¡áá¯á¶ážááŒá¯áá«áááºá
á¡áá¯áẠá
áá¬á ID á á¬áááºážá áá° 3 áŠážáááºáááá¯áá±á¬ áááºáááºážá¡áá±á¡ááœááºááᯠááŒááá«á
áááºá ááºááŸá¯
select department_id
from employee
group by department_id
having count(*) <= 3
á¡áá¯ááºáá¯ááºáá«áááº
countEmployees 'ÐПлОÑеÑÑвП ÑПÑÑÑЎМОкПв' (Department d) =
GROUP SUM 1 IF department(Employee e) = d;
SELECT Department d WHERE countEmployees(d) <= 3;
á¡áá¯áẠá
áá°áá®áá±á¬áá¬ááá áºáá¯ááœáẠá¡áá¯ááºáá¯ááºáá±á¬ áááºááŸááºáá¬ážáá±á¬ áááºáá±áá»á¬áááŸááá±á¬ áááºáááºážáá»á¬ážá á¬áááºážááᯠááŒááá«á
áááºá ááºááŸá¯
select a.*
from employee a
left join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where b.id is null
á¡áá¯ááºáá¯ááºáá«áááº
SELECT name(Employee a) WHERE NOT (department(chief(a)) = department(a));
á¡áá¯áẠá
á á¯á á¯áá±á«ááºáž áááºáááºážáá ᬠá¡áá»á¬ážáá¯á¶ážááŸááá±á¬ áá¬á ID á á¬áááºážááᯠááŸá¬áá«á
áááºá ááºááŸá¯
with sum_salary as
( select department_id, sum(salary) salary
from employee
group by department_id )
select department_id
from sum_salary a
where a.salary = ( select max(salary) from sum_salary )
á¡áá¯ááºáá¯ááºáá«áááº
salarySum 'ÐакÑОЌалÑÐœÐ°Ñ Ð·Ð°ÑплаÑа' (Department d) =
GROUP SUM salary(Employee e) IF department(e) = d;
maxSalarySum 'ÐакÑОЌалÑÐœÐ°Ñ Ð·Ð°ÑплаÑа ПÑЎелПв' () =
GROUP MAX salarySum(Department d);
SELECT Department d WHERE salarySum(d) = maxSalarySum();
á¡ááŒá¬ážáá
áºáá¯á០ááá¯ááá¯ááŸá¯ááºááœá±ážáá±á¬ á¡áá¯ááºáá»á¬ážááá¯á· áááºááœá¬ážááŒáá«á
áá¯á·
á¡áá¯áẠá
1997 ááœáẠáá¶áá«áẠ30 áá¯ááºáá¯áẠá¡áá¯á¶ážáá± 1 áá»á±á¬áºááᯠáááºááá·áºáá±á¬ááºážáá»áá°áá»á¬áž áá±á¬ááºážáá»áá²á·ááááºážá
Domain logic (RDBMS ááœáẠááááºáá²á·ááá¯á· áá»áœááºá¯ááºááá¯á· ááŒá±ááŒá¬áá»ááºááᯠáá»á±á¬áºááœá¬ážáááº)
CLASS Employee 'ÐÑПЎавеÑ';
lastName 'ЀаЌОлОÑ' = DATA STRING[100] (Employee);
CLASS Product 'ÐÑПЎÑкÑ';
id = DATA INTEGER (Product);
name = DATA STRING[100] (Product);
CLASS Order 'Ðаказ';
date = DATA DATE (Order);
employee = DATA Employee (Order);
CLASS Detail 'СÑÑПка заказа';
order = DATA Order (Detail);
product = DATA Product (Detail);
quantity = DATA NUMERIC[10,5] (Detail);
áááºá ááºááŸá¯
select LastName
from Employees as e
where (
select sum(od.Quantity)
from [Order Details] as od
where od.ProductID = 1 and od.OrderID in (
select o.OrderID
from Orders as o
where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30
á¡áá¯ááºáá¯ááºáá«áááº
sold (Employee e, INTEGER productId, INTEGER year) =
GROUP SUM quantity(OrderDetail d) IF
employee(order(d)) = e AND
id(product(d)) = productId AND
extractYear(date(order(d))) = year;
SELECT lastName(Employee e) WHERE sold(e, 1, 1997) > 30;
á¡áá¯áẠá
áááºáá°áá°ááá¯ááºáž (á¡áááºá áá»áá¯ážááá¯ážá¡áááº) á¡ááœáẠ1997 áá¯ááŸá áºááœáẠáááºáá°ááẠááœá±á¡áá»á¬ážáá¯á¶ážáá¯á¶ážá áœá²ááá·áº áá¯ááºáá á¹á ááºáž (á¡áááº) ááŸá áºáá¯ááᯠááŸá¬ááœá±áá«á
áá»áœááºá¯ááºááá¯á·ááẠááááºá¥ááá¬á០ááá¯ááááºážáá±á¬á·áá»á áºááᯠááá¯ážáá»á²á·áááº-
CLASS Customer 'ÐлОеМÑ';
contactName 'ЀÐÐ' = DATA STRING[100] (Customer);
customer = DATA Customer (Order);
unitPrice = DATA NUMERIC[14,2] (Detail);
discount = DATA NUMERIC[6,2] (Detail);
áááºá ááºááŸá¯
SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
PARTITION BY c.ContactName
ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3
á¡áá¯ááºáá¯ááºáá«áááº
sum (Detail d) = quantity(d) * unitPrice(d) * (1 - discount(d));
bought 'ÐÑпОл' (Customer c, Product p, INTEGER y) =
GROUP SUM sum(Detail d) IF
customer(order(d)) = c AND
product(d) = p AND
extractYear(date(order(d))) = y;
rating 'РейÑОМг' (Customer c, Product p, INTEGER y) =
PARTITION SUM 1 ORDER DESC bought(c, p, y), p BY c, y;
SELECT contactName(Customer c), name(Product p) WHERE rating(c, p, 1997) < 3;
PARTITION á¡á±á¬áºááá±áá¬ááẠá¡á±á¬ááºáá«áá°á¡á áá¯ááºáá±á¬ááºáááº- áááºážááẠSUM (á€áá±áᬠ1) ááŒá®ážáá±á¬áẠáááºááŸááºáá¬ážáá±á¬ á áá¬ážáááºááᯠáá±á«ááºážá ááºážááá¯ááºááẠ(á€áá±áá¬ááœáẠáááºáá°áá°ááŸáá·áº áá áºááŸá áºá ááá¯á·áá±á¬áº áááºááá·áºá áá¬ážáááºáááᯠááŒá áºááá¯ááºáááº)á ORDER ááœáẠáááºááŸááºáá¬ážááá·áº á áá¬ážáááºáá»á¬ážááŒáá·áº á¡á¯ááºá á¯áá»á¬ážá¡ááœááºáž á á®ááŒááºáž ( á€ááœááºáááºáááºá áá°áá®áá«áá á¡ááœááºážáá¯ááºáá¯ááºáá¯ááºá¡á)á
á¡áá¯áẠá
áááºááŸá ááŸá¬áá°ááŸá¯áá»á¬ážááᯠááŒáá·áºáááºážááẠáá¯ááºáá á¹á ááºáž áááºáá»áŸááᯠáá±ážááœááºážááẠááá¯á¡ááºáá«áááºá
ááá¯ááááºážáá±á¬á·áá»á áºááᯠáááºáá»á²á·ááŒáá·áºáá¡á±á¬ááºá
CLASS Supplier 'ÐПÑÑавÑОк';
companyName = DATA STRING[100] (Supplier);
supplier = DATA Supplier (Product);
unitsInStock 'ÐÑÑаÑПк Ма ÑклаЎе' = DATA NUMERIC[10,3] (Product);
reorderLevel 'ÐПÑЌа пÑПЎажО' = DATA NUMERIC[10,3] (Product);
áááºá ááºááŸá¯
select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel â p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel
á¡áá¯ááºáá¯ááºáá«áááº
orderedNotShipped 'ÐаказаМП, МП Ме ПÑгÑÑжеМП' (Product p) =
GROUP SUM quantity(OrderDetail d) IF product(d) = p;
toOrder 'РзаказÑ' (Product p) = orderedNotShipped(p) + reorderLevel(p) - unitsInStock(p);
SELECT companyName(supplier(Product p)), name(p), toOrder(p) WHERE toOrder(p) > 0;
ááá±ááœáá·áºááŸáá·áº ááŒá¿áá¬
áá±á¬ááºáá¯á¶áž á¥ááá¬á áá»áœááºáá±á¬áºááá¯ááºááá¯ááºááá«á áá°ááŸá¯ááœááºáááºáá áºáá¯áá²á· áá¯áá¹ááááŸááááºá áá°ááœá±áᬠáá áºáá±á¬ááºáá²á·áá áºáá±á¬áẠáá°áááºáá»ááºážáá±á¬ááºážááœá± ááŒá áºááá¯ááºáááᯠáá áºáá±á¬ááºáá²á·áá áºáá±á¬ááºáááºáž ááá±á¬áá»ááá¯ááºáá«áááºá áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá±áá¬áá±á·á ẠááŸá¯áá±á¬áá·áºá០áááºážááẠá€áá²á·ááá¯á· ááŒá áºáááº-
CLASS Person;
likes = DATA BOOLEAN (Person, Person);
friends = DATA BOOLEAN (Person, Person);
áááºáááºáááºážááŸá®ážááŸá¯á¡ááœáẠááŒá
áºááá¯ááºááŒá±ááŸááá±á¬ ááá¯ááºá
á¬ážááŸááºáá±á¬ááºážáá»á¬ážááᯠááŸá¬ááœá±ááẠááá¯á¡ááºáá«áááºá ááá¯á ááá¬ážáááºáááºááŸá¬ A ááẠB ááŸáá·áº áá°áááºáá»ááºážááŒá
áºáááºá B ááẠC ááŸáá·áº A ááŸá
áºáááºáá±á¬ C ááŸáá·áº A ááẠA ááẠC ááŸáá·áº áá°áááºáá»ááºážááá¯ááºáá±á¬ áá°á¡á¬ážáá¯á¶ážááᯠááá¬ážáááºááŸá¬ááœá±ááẠááá¯á¡ááºáááºá
áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá±áá¬áá±á·á
ẠááŸá¯áá±á¬áá·áºá០áá±ážááŒááºážáá»ááºááẠá€áá²á·ááá¯á· ááŒá
áºáááº-
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
friends(a, b) AND friends(b, c);
á€ááŒá¿áá¬ááᯠSQL ááœáẠáá°á·áá¬áá¬áá° ááŒá±ááŸááºážááẠá á¬áááºáá°ááᯠááá¯ááºááœááºážáá«áááºá áááºááŸá áºáááºáá±á¬ áá°áááºáá»ááºážáá»á¬ážááẠáá»á¬ážá áœá¬áááºážáááºáᯠáá°ááá«áááºá ááá¯á·ááŒá±á¬áá·áº áááºážááá¯á·ááẠáá®ážááŒá¬ážá á¬ážááœá²áá»á¬ážáá±á«áºááœáẠááŸááá±áááºá á¡á±á¬ááºááŒááºáá»áŸáẠááŒááºááŸá áºáá¯á¶ážáá«áá±á¬ á¡áá¯ááºáá áºáá¯áááºáž ááŸááá«áááºá á¡á²áá®áá²ááŸá¬ áááºáááºáááºážááŸá®ážááŸá¯á á¡áá»áá¯ážááá»áá«áá°ážá áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá±áá¬áá±á·á áºáá áºáá¯ááœáẠáááºážááẠá€áá²á·ááá¯á·ááŒá áºáá±áááá·áºáááº-
SELECT Person a, Person b, Person c WHERE
likes(a, c) AND NOT friends(a, c) AND
(friends(a, b) OR friends(b, a)) AND
(friends(b, c) OR friends(c, b));
UPD: áááááŸáá·áº áá¯ááá ááŒááºááœáá·áºáá»á¬ážá០ááŒá¿áá¬ááᯠááŒá±ááŸááºážáá»ááº
SELECT
pl.PersonAID
,pf.PersonAID
,pff.PersonAID
FROM Persons AS p
--ÐайкО
JOIN PersonRelationShip AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--ÐÑÑзÑÑ
JOIN PersonRelationShip AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--ÐÑÑзÑÑ ÐÑÑзей
JOIN PersonRelationShip AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--ÐÑÑ ÐœÐµ ÐŽÑÑжаÑ
LEFT JOIN PersonRelationShip AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.PersonAID IS NULL
;WITH PersonRelationShipCollapsed AS (
SELECT pl.PersonAID
,pl.PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
UNION
SELECT pl.PersonBID AS PersonAID
,pl.PersonAID AS PersonBID
,pl.Relation
FROM #PersonRelationShip AS pl
)
SELECT
pl.PersonAID
,pf.PersonBID
,pff.PersonBID
FROM #Persons AS p
--ÐайкО
JOIN PersonRelationShipCollapsed AS pl ON pl.PersonAID = p.PersonID
AND pl.Relation = 'Like'
--ÐÑÑзÑÑ
JOIN PersonRelationShipCollapsed AS pf ON pf.PersonAID = p.PersonID
AND pf.Relation = 'Friend'
--ÐÑÑзÑÑ ÐÑÑзей
JOIN PersonRelationShipCollapsed AS pff ON pff.PersonAID = pf.PersonBID
AND pff.PersonBID = pl.PersonBID
AND pff.Relation = 'Friend'
--ÐÑÑ ÐœÐµ ÐŽÑÑжаÑ
LEFT JOIN PersonRelationShipCollapsed AS pnf ON pnf.PersonAID = p.PersonID
AND pnf.PersonBID = pff.PersonBID
AND pnf.Relation = 'Friend'
WHERE pnf.[PersonAID] IS NULL
áá±á¬ááºáá»ááº
áá±ážáá¬ážáá±á¬ áá¬áá¬á áá¬áž syntax ááẠáá±ážáá¬ážáá±á¬ concept ááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºáááºá¡ááœáẠááœá±ážáá»ááºá áá¬áá»á¬ážáá²á០áá áºáá¯áá¬ááŒá áºááŒá±á¬ááºáž áááááŒá¯ááá·áºáááºá SQL ááᯠá¡ááŒá±áá¶á¡ááŒá Ạáá°áá¬ážááŒá®áž áááºááŸááºážáá»ááºá áááºážááŸáá·áº áááºááá¯ááºááá»áŸ áá°áá®á á±áááºááŒá áºáááºá áá¯ááºáá«áááºá á¡áá»áá¯á·áá±á¬áá±á¬á·áá»ááºá áá¬ážáá¯á¶ážáá»á¬ážáá¡áááºáá»á¬ážá á áá¬ážáá¯á¶ážá á¬áááºážááœááºážááŒááºážá áááºááá¯á·ááá¯áááŒáá¯ááºáá«á áá®áá±áá¬ááŸá¬ á¡áááá¡áá»ááºá ááá±á¬ááá¬ážááá¯ááºááá¯ááºáá«áá²á ááá¹áááŸááá«áá áááºááẠC++ ááŸáá·áº Python ááŸá áºáá¯áá¯á¶ážááᯠáááºáá°áá±á¬ syntax ááŒá¯áá¯ááºááá¯ááºáááºá
áá±á¬áºááŒáá¬ážáá±á¬ áá±áá¬áá±á·á áºá¡áá°á¡áááẠáá»áœááºá¯ááºáá¡ááŒááºá¡áá á¡á±á¬ááºáá« á¡á¬ážáá¬áá»ááºáá»á¬áž ááŸááá«áááºá
- áááºáá¬ááŒááºáž. á€á¡áá¬ááẠááá¯ážááŸááºážáá±á¬ááá á¹á áá»á¬ážááœáẠááŸááºážááŸááºážáááºážáááºážááááá¬áá±á¬ á¡áá±á¬áºáá±ážáá¯áá¹áááááá¹ááááœáŸááºááŒáá»ááºáá áºáá¯ááŒá áºáááºá áá«áá±ááá·áº ááá¯ááŸá¯ááºááœá±ážáá²á· ááá á¹á ááœá±ááᯠááŒáá·áºáááºááá¯ááẠ(á¥ááá¬á ááŒááºááœáá·áºááœá±áá²á· ááŒá¿áá¬áá»á¬áž) ááá¯ááŒáá·áºáááºááá¯áááºá áá«á·á¡ááŒááºá¡ááá±á¬á· áá®ááá¯áá±ážááœááºážááœá±ááᯠáá±ážááᬠááá¯ááœááºáá«áááºá
- ОМкапÑÑлÑÑОÑ. á¡áá»áá¯á·áá±á¬á¥ááá¬áá»á¬ážááœáẠáá»áœááºá¯ááºááẠá¡áááºá¡áááºáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠááŒá±áá¬áá²á·ááẠ(á¥ááá¬á áá±á¬ááºážáá»áá²á·, ááẠá áááºááŒáá·áº) áá±á¬ááºáááºááœá²áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠáááºáá±á¬ááºáá²á·áááºá áááºážááẠááá¯á¡ááºáá«á á¡áá»áá¯á·áá±á¬áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážá áá¯áá¹ááááᯠááŒá±á¬ááºážáá²ááá¯ááºá á±áᬠáááºážááá¯á·á¡áá±á«áº áá°áááºáá±á¬ áá¯áá¹áááá±áááᯠááŒá±á¬ááºážáá²ááŒááºážáááŒá¯áá²á á¥ááá¬á¡á¬ážááŒáá·áºá áááºáá±á¬ááºážá¡á¬ážááá¯áááºáá®ážááá¯ááºáááºá áá±á¬ááºážáá»áá²á· áá¯á¶ážáááŒá¬ážáá¬ážáá±á¬ á¡áá¬ááá¹áá¯áá»á¬ážá០ááœááºáá»ááºáá¬ážáá±á¬áºáááºáž áá»áẠlogic áá»á¬ážááẠááŒá±á¬ááºážáá²áááºááá¯ááºáá«á áá¯ááºáá²á·á áá«ááᯠCREATE VIEW áá¯á¶ážááŒá®áž RDBMS ááŸá¬ á¡áá±á¬ááºá¡áááºáá±á¬áºááá¯ááºáá«áááºá áá«áá±ááá·áº áá¯áá¹áááá±á á¡á¬ážáá¯á¶ážááᯠáá®áááºážáá²á· áá±ážáá¬ážááẠáááºááá¯á· áááá«áá°ážá
- áá±á«áá¬á ááœá¬áááŸá¯ áááŸááá«á. ááá¯ááá¯á·áá±á¬ áá±áá¬áá±á·á
áºááẠáá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááŸáá·áº á¡áááºážáá»á¬áž (ááá¬ážáá»á¬ážááŸáá·áº á¡ááœááºáá»á¬ážá¡á
á¬áž) áá±á«áºááœáẠáá¯ááºáá±á¬ááºáááºá classical programming áá²á·ááá¯á·ááẠ( method áá
áºáá¯ááẠáááºážááá¯ááºááá¯ááºááá·áº class áá¯á¶á
á¶ááœáẠááá parameter áá«ááá·áº function áá
áºáá¯ááŒá
áºáááºáᯠáá»áœááºá¯ááºááá¯á·áá°ááá»áŸááº)á ááá¯á·ááŒá±á¬áá·áºá á
ááŒáá á¬áááá¯ááááºážáááºážáá¬áá¬á
áá¬ážáá»á¬ážááŒáá·áº âááááºááœá±ááœá²á·â ááẠááá¯ááá¯ááœááºáá°ááá·áºáááºá ááá¯á·á¡ááŒááºá á€ááá±á¬ááá¬ážááẠááá¯ááá¯ááŸá¯ááºááœá±ážáá±á¬ áá¯ááºáá±á¬ááºáá»ááºáá»á¬ážááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááẠááœáá·áºááŒá¯áá±ážáá«áááºá á¥ááá¬á¡á¬ážááŒáá·áºá áááºááẠá¡á±á¬ááºáá«áá²á·ááá¯á·áá±á¬ á¡á±á¬áºááá±áá¬áá»á¬ážááᯠááŒáŸá¯ááºááŸá¶ááá¯ááºáááº-
CONSTRAINT sold(Employee e, 1, 2019) > 100 IF name(e) = 'ÐеÑÑ' MESSAGE 'ЧÑП-ÑП ÐеÑÑ Ð¿ÑÐŸÐŽÐ°ÐµÑ ÑлОÑкПЌ ЌМПгП ПЎМПгП ÑПваÑа в 2019 гПЎÑ';
- á¡ááœá±áááºáá¶ááŒááºáž ááŸáá·áº polymorphism. áá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá±áá¬áá±á·á áºáá áºáá¯ááœááºá áááºááẠCLASS ClassP- Class1á Class2 áááºáá±á¬ááºááŸá¯áá»á¬ážááŸáá áºááá·áº á¡ááœá±áááºáá¶ááŸá¯áá»á¬ážá áœá¬ááᯠááááºáááºááá¯ááºááŒá®áž áá»á¬ážááŒá¬ážááŸáá±á¬ polymorphism ááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááá¯ááºáááºá áá±á¬ááºáá±á¬ááºážáá«ážááœá±ááŸá¬ á¡ááá¡áá» áá±ážááŒá áºáááºáááºáááºá
áááºážááẠá¡áá°á¡ááá
áºáá¯áá»áŸáá¬ááŒá
áºáá±á¬áºáááºážá áá»áœááºá¯ááºááá¯á·ááœáẠáá¯ááºáá±á¬ááºááá¯ááºáá±á¬ áá¯áá¹áááá±áá¡á¬ážáá¯á¶ážááᯠáááºá
ááºáá¯áá¹áááá±áá¡ááŒá
Ạáá¬áá¬ááŒááºáá±ážááá·áº Java ááœáẠá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡áá»áá¯á· ááŸáááŸáá·áºááŒá®ážááŒá
áºáááºá ááá¯á·á¡ááŒááºá ááá¯ááºá
á¬ážááŒá¯ááŒááºážááá¯áá¹ááááŸáá·áº á¡ááŒá¬ážá¡áá¬áá»á¬ážá
áœá¬ááᯠáá»áœááºá¯ááºááá¯á·á¡á¬ážáá¯á¶ážáááá¯ááºáá±á¬ááŒá±á¬áá·áº áááºážááŸáá·áºááŸáá
áœá¬ááœá²áááºáá¬ážáááºá
source: www.habr.com