SQL++ Tutorial for SQL Users by Don Chamberlin

"(O SQL++) pode lidar com outros tipos de dados, com mais poder de expressão."

Don Chamberlin

Co-inventor do SQL

O SQL tem uma função a desempenhar em um mundo pós-relacional?

SQL++O SQL++, uma linguagem de consulta compatível com SQL desenvolvida na Universidade da Califórnia, em San Diego, amplia o SQL com alguns recursos simples que permitem a consulta de dados no popular formato JSON. O objetivo do SQL++ é capacitar os desenvolvedores a criar uma nova geração de aplicativos, aproveitando seus conhecimentos e ferramentas baseados em SQL.

Este tutorial, escrito para desenvolvedores que têm um conhecimento prático de SQL, apresenta o SQL++ por meio de uma série de exemplos que foram testados no Couchbase Analytics Service. Esta página fornece todos os recursos de que você precisa para carregar dados de exemplo no Couchbase e executar as consultas de exemplo.

Para executar as consultas de exemplo, você precisará instalar o Couchbase Server, Enterprise Edition, que pode ser baixado gratuitamente em couchbase.com/downloads. O Couchbase Server fornece vários serviços, dois dos quais são o Query Service e o Analytics Service. Certifique-se de habilitar esses dois serviços em sua instalação do Couchbase. Depois de instalar o Couchbase Server, prossiga com as etapas a seguir:

1 - Inicie o Console da Web e observe a exibição do Dashboard. No lado esquerdo da tela, você verá uma lista de serviços que podem ser chamados a partir do Dashboard, incluindo Buckets, Query e Analytics.

2 - Clique em Buckets e use o recurso Add Buckets para criar dois novos buckets chamados customers e orders. Para cada bucket, defina o Bucket Type como Couchbase e defina a cota de memória como o mínimo (100 MB).

3 - Clique em Query (Consulta) para chamar o Query Editor (Editor de consulta). Copie e cole uma instrução SQL INSERT de aqui no editor de consultas e clique em Executar para carregar os dados dos clientes no Couchbase.

4 - Em seguida, copie e cole uma instrução SQL INSERT de aqui no Query Editor e clique em Execute (Executar) para carregar os dados dos pedidos no Couchbase.

5 - Os dados de exemplo agora são carregados no Query Service, que é usado para dados operacionais. Precisamos que os dados sejam replicados no Analytics Service, onde podemos consultá-los usando o N1QL for Analytics, a implementação do Couchbase do SQL++. Para fazer essa replicação, clique em Analytics no lado esquerdo do Dashboard e digite os seguintes comandos no Analytics Query Editor (clique em Execute depois de cada um):

criar um conjunto de dados sobre os clientes;
criar conjunto de dados sobre pedidos;
link de conexão Local;

6 - Agora você está pronto para executar algumas consultas de exemplo. Basta copiar uma consulta de aquicole-o no Analytics Query Editor e clique em Executar. O resultado pode não ser exatamente o mesmo que o mostrado no Tutorial, pois a ordem dos campos dentro dos objetos pode não corresponder à ordem dos campos na cláusula SELECT. Faça alguns experimentos modificando as consultas de exemplo e criando suas próprias consultas!

7 - Esta é uma etapa opcional. Os dados de exemplo são armazenados no Query Service e no Analytics Service. Você está executando consultas no Analytics Service, usando o N1QL for Analytics, uma implementação do SQL++. Para consultar os mesmos dados no Query Service, clique em Query no lado esquerdo do Dashboard para retornar ao Query Editor e execute os seguintes comandos para criar dois índices:

criar índice primário em clientes;
criar índice primário em pedidos;

Agora você pode digitar consultas com base nos dados de exemplo no Query Editor e executá-las. O Query Service e o Analytics Service suportam duas linguagens de consulta ligeiramente diferentes, chamadas N1QL for Query e N1QL for Analytics. Devido às diferenças entre essas linguagens, algumas das consultas de exemplo não serão executadas no Query Service. Para executar consultas no Query Service, use o N1QL for Query, documentado em outra parte do site do Couchbase.

8 - Se desejar excluir os dados de exemplo de seu sistema, basta refazer os passos. Execute os seguintes comandos no Analytics Service:

desconectar link Local;
soltar os clientes do conjunto de dados;
soltar pedidos de conjuntos de dados;

Em seguida, clique em Buckets (Compartimentos) no lado esquerdo do Dashboard, clique em cada um dos compartimentos de exemplo (clientes e pedidos) e clique em Delete (Excluir) para cada compartimento.

Exemplo de consulta de dados

 (Q1)
SELECT custid, name, address.zipcode, rating
FROM customers
ORDER BY custid;

(Q2)
SELECT c.name, c.rating
FROM customers AS c
WHERE c.custid = "C41";

(Q3)
SELECT name
FROM customers
WHERE rating > 650;

(Q4)
SELECT VALUE name
FROM customers
WHERE rating > 650;

(Q5)
SELECT VALUE [name, rating]
FROM customers
WHERE rating > 650;

(Q6)
SELECT VALUE
{"high-rated customers, ordered by rating":
(SELECT c.rating, c.custid, c.name
FROM customers AS c
WHERE c.rating > 650
ORDER BY c.rating DESC),
"high-rated customers, ordered by zipcode":
(SELECT c.address.zipcode, c.custid, c.name
FROM customers AS c
WHERE c.rating > 650
ORDER BY c.address.zipcode)
};

(Q7)
SELECT name
FROM customers
WHERE rating =
(SELECT MAX(rating) FROM customers);

(Q8)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating =
(SELECT MAX(c2.rating)
FROM customers AS c2);

(Q9)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating IN
(SELECT VALUE MAX(c2.rating) FROM customers AS c2);

(Q10)
SELECT c1.name
FROM customers AS c1
WHERE c1.rating >
(SELECT VALUE AVG(c2.rating) FROM customers AS c2)[0];

(Q11)
SELECT VALUE c1.name
FROM customers AS c1
WHERE c1.rating =
(SELECT VALUE MAX(c2.rating) FROM customers AS c2)[0];

(Q12)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
(SELECT VALUE c2.rating FROM customers AS c2)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q13)
SELECT VALUE c1.name
FROM customers AS c1
WHERE EVERY r IN
(SELECT VALUE c2.rating
FROM customers AS c2
WHERE c2.rating IS KNOWN)
SATISFIES c1.rating >= r;

(On N1QL, add END after SATISFIES clause)

(Q14)
FROM customers
WHERE address.zipcode = "63101"
SELECT custid AS customer_id, name
ORDER BY customer_id;

(Q15)
FROM customers AS c
WHERE c.address.zipcode = "63101"
SELECT c.custid AS customer_id, c.name
ORDER BY customer_id;

 (Q16)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND o.orderno = 1001
SELECT o.orderno,
c.name AS customer_name,
c.address,
o.items AS items_ordered;

(Q17)
FROM customers AS c JOIN orders AS o
ON c.custid = o.custid
WHERE o.orderno = 1001
SELECT o.orderno,
c.name AS customer_name,
c.address,
o.items AS items_ordered;

(Q18)
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.custid = o.custid
SELECT c.custid, c.name, o.orderno, o.order_date
ORDER BY c.custid, o.order_date;

(Q19)
FROM orders AS o, o.items AS i
WHERE i.qty > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
i.qty AS quantity
ORDER BY o.orderno, item_number;

(Q20)
FROM orders AS o UNNEST o.items AS i
WHERE i.qty > 100
SELECT o.orderno, o.order_date, i.itemno AS item_number,
i.qty AS quantity
ORDER BY o.orderno, item_number;

(Q21a)
FROM orders AS o, o.items AS i, customers AS c
WHERE o.custid = c.custid
AND i.itemno = 680
SELECT c.custid, c.name, o.order_date AS date
ORDER BY c.custid, date;

(Q21b)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
AND EXISTS
(SELECT i.itemno
FROM o.items AS i
WHERE i.itemno = 680)
SELECT c.custid, c.name, o.order_date AS date
ORDER BY c.custid, date;

(Q22)
FROM orders AS o
LET days = DATE_DIFF_STR(o.ship_date, o.order_date, "day")
WHERE days > 2
SELECT o.orderno, days - 2 AS days_late
ORDER BY days_late DESC;

(Q23)
FROM orders AS o
GROUP BY o.custid
SELECT o.custid, COUNT(o.orderno) AS `order count`
ORDER BY o.custid;

(Q24)
FROM customers AS c
LEFT OUTER JOIN orders AS o ON c.custid = o.custid
GROUP BY c.custid, c.name
SELECT c.custid, c.name, COUNT(o.orderno) AS `order count`
ORDER BY c.custid;

(Q25)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
GROUP BY DATE_PART_STR(o.order_date, "month") AS month
SELECT month, COUNT(*) AS order_count
ORDER BY month;

(Q26)
FROM orders AS o, o.items as i
GROUP BY o.orderno
SELECT o.orderno, SUM(i.qty * i.price) AS revenue
ORDER BY o.orderno;

(Q27)
FROM orders AS o, o.items as i
GROUP BY o.orderno
LET revenue = SUM(i.qty * i.price)
HAVING revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q28)
FROM orders AS o
LET revenue =
(FROM o.items AS i
SELECT VALUE SUM(i.qty * i.price))[0]
WHERE revenue > 1000
SELECT o.orderno, revenue
ORDER BY revenue DESC;

(Q29)
FROM orders AS o, o.items AS i, customers AS c
WHERE o.custid = c.custid
GROUP BY o.orderno AS `order no.`,
o.order_date AS date, c.name, c.address
SELECT `order no.`, date, c.name, c.address,
SUM(i.qty * i.price) AS `amount due`
ORDER BY `order no.`;

(Q30)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
HAVING SUM(i.qty * i.price) > 10000
SELECT o.orderno AS order_number, o.order_date,
SUM(i.qty * i.price) AS revenue
ORDER BY revenue DESC;

  (Q31)
FROM orders AS o, o.items AS i
GROUP BY o.orderno, o.order_date
LET revenue =  SUM(i.qty * i.price) 
HAVING revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q32)
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, AVG(c.rating) AS `avg credit rating`
ORDER BY zip;

(Q33)
FROM customers AS c
SELECT AVG(c.rating) AS `avg credit rating`;

(Q34)
SELECT ARRAY_AVG(
   (SELECT VALUE c.rating
    FROM customers AS c) ) AS `avg credit rating`;
    
(Q35)
FROM orders AS o
LET revenue = ARRAY_SUM(
       (FROM o.items AS i
        SELECT VALUE i.qty * i.price) )
WHERE revenue > 10000
SELECT o.orderno AS order_number, o.order_date, revenue
ORDER BY revenue DESC;

(Q36)
FROM orders AS o
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
SELECT o.orderno, ARRAY_COUNT(o.items) AS line_items
ORDER BY o.orderno; 

(Q37) 
FROM orders AS o, o.items AS i
WHERE DATE_PART_STR(o.order_date, "year") = 2017
AND DATE_PART_STR(o.order_date, "month") = 9
GROUP BY o.orderno
SELECT o.orderno, COUNT(i) AS line_items
ORDER BY o.orderno; 

(Q38a) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g AS gi
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) AS `local customers`
ORDER BY zip;

(Q38b)
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
SELECT zip, AVG(c.rating) AS `avg credit rating`,
   (FROM g 
    SELECT c.custid, c.name
    ORDER BY c.custid) AS `local customers`
ORDER BY zip;

(Q39) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating)
SELECT zip, `best rating`,
   (FROM g AS gi
    WHERE gi.c.rating = `best rating`
    SELECT gi.c.custid, gi.c.name
    ORDER BY gi.c.custid) AS `best customers`
ORDER BY zip;

(Q40) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
GROUP AS g
LET `best rating` = MAX(c.rating),
    `best customers` =
       (FROM g AS gi
        WHERE gi.c.rating = `best rating`
        SELECT gi.c.custid, gi.c.name
        ORDER BY gi.c.custid)
SELECT zip, `best rating`, `best customers`
ORDER BY zip;

(Q41) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip 
GROUP AS g
SELECT zip, 
   ARRAY_AVG((FROM g AS gi SELECT VALUE gi.c.rating)) 
       AS `avg credit rating`
ORDER BY zip;

(Q42)
FROM orders AS o, o.items AS i
GROUP BY o.order_date
GROUP AS g
LET revenue = SUM(i.price * i.qty)
HAVING revenue > 1000.00
SELECT o.order_date AS `good day`, revenue,
   (FROM g AS gi 
    WHERE gi.i.price > 100.00
    SELECT gi.i.itemno, gi.i.price 
    ORDER BY gi.i.itemno) AS `expensive items`
ORDER BY o.order_date;

(Q43)
FROM customers AS c, orders AS o, o.items AS i
WHERE c.custid = o.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
	(FROM g AS gi
 	 SELECT gi.o.order_date, gi.i.itemno, gi.i.qty
 	 ORDER BY gi.o.order_date, gi.i.itemno) AS recent_items
ORDER BY c.custid;

(Q44)
FROM customers AS c LEFT OUTER JOIN
   /* This subquery unnests the items in each order, 
  	 returning an array of results named sq */
   (FROM orders AS o, o.items AS i
    SELECT o.custid, o.order_date, i.itemno, i.qty) AS sq
ON c.custid = sq.custid
GROUP BY c.custid, c.name
GROUP AS g
SELECT c.custid, c.name,
      (FROM g AS gi
       SELECT gi.sq.order_date, gi.sq.itemno, gi.sq.qty
       ORDER BY gi.sq.order_date, gi.sq.itemno) 
       AS recent_items
ORDER BY c.custid;

(Q45)
FROM orders AS o, o.items AS i
GROUP BY i.itemno
GROUP AS g
LET total_on_order = SUM(i.qty)
SELECT i.itemno, total_on_order,
    (FROM g AS gi
     SELECT gi.o.order_date, gi.o.custid
     ORDER BY gi.o.order_date) AS purchasers
ORDER BY i.itemno;

  (Q46) 
SELECT DISTINCT VALUE i.itemno
FROM orders AS o, o.items AS i
WHERE o.custid IN 
    (SELECT VALUE c.custid 
     FROM customers AS c 
     WHERE c.name = "T. Cruise");
   
(Q47) 
FROM customers AS c
GROUP BY c.address.zipcode AS zip
SELECT zip, ROUND(AVG(c.rating)) AS avg_rating
ORDER BY zip;
   
(Q48)
SELECT VALUE { "Average credit rating by zipcode" :
   (FROM customers AS c 
    WHERE c.address.zipcode IS KNOWN
    GROUP BY c.address.zipcode AS zip
    SELECT VALUE { zip : ROUND(AVG(c.rating)) }
    ORDER BY zip 
   )
};

(Q49)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT *;

(Q50)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*;

(Q51)
FROM customers AS c, orders AS o
WHERE c.custid = o.custid
AND c.address.zipcode = "02115"
SELECT *;

(Q52)
FROM customers AS c
WHERE c.address.zipcode = "02115"
SELECT c.*, "Northeast" AS region;

(Q53)
FROM customers AS c
LET credit =
    CASE 
      WHEN c.rating > 650 THEN "Good"
      WHEN c.rating BETWEEN 500 AND 649 THEN "Fair"
      ELSE "Poor" 
    END
WHERE c.address.zipcode = "02115"
SELECT c.*, credit; 
      
(Q54) 
WITH order_revenue AS
  (FROM orders AS o, o.items AS i
   WHERE DATE_PART_STR(o.order_date, "year") = 2017 
   GROUP BY o.orderno
   SELECT SUM(i.qty * i.price) AS revenue
  )
FROM order_revenue
SELECT ROUND(MIN(revenue)) AS minimum,
       ROUND(MAX(revenue)) AS maximum,
       ROUND(AVG(revenue)) AS average;
   
(Q55) 
SELECT c.custid, "Unknown zipcode" AS reason
FROM customers AS c
WHERE c.address.zipcode IS NOT KNOWN

UNION ALL

SELECT o.custid, "Big order" AS reason
FROM orders AS o
WHERE ARRAY_COUNT(o.items) > 3
ORDER BY reason;

  insert into customers (key, value)
values ("C13",

  { "custid": "C13",
    "name": "T. Cruise",
    "address": 
       { "street": "201 Main St.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       },
    "rating": 750
  }
  
), ("C25", 

  { "custid": "C25",
    "name": "M. Streep",
    "address": 
       { "street": "690 River St.",
         "city": "Hanover, MA",
         "zipcode": "02340"
       }, 
    "rating": 690
  }
  
), ("C31",

  { "custid": "C31",
    "name": "B. Pitt",
    "address": 
       { "street": "360 Mountain Ave.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       }
  }
  
), ("C35",

  { "custid": "C35",
    "name": "J. Roberts",
    "address": 
       { "street": "420 Green St.",
         "city": "Boston, MA",
         "zipcode": "02115"
       },
    "rating": 565 
  }
  
), ("C37", 

  { "custid": "C37",
    "name": "T. Hanks",
    "address": 
       { "street": "120 Harbor Blvd.",
         "city": "Boston, MA",
         "zipcode": "02115"
       }, 
    "rating": 750
  }
  
), ("C41", 

  { "custid": "C41",
    "name": "R. Duvall",
    "address": 
       { "street": "150 Market St.",
         "city": "St. Louis, MO",
         "zipcode": "63101"
       },
    "rating": 640
  }

), ("C47", 

  { "custid": "C47",
    "name": "S. Lauren",
    "address": 
       { "street": "17 Rue d'Antibes",
         "city": "Cannes, France"
       },
    "rating": 625
  }
       
);

insert into orders (key, value)
values ( "1001",

  { "orderno": 1001,
    "custid": "C41",
    "order_date": "2017-04-29",
    "ship_date": "2017-05-03",
    "items": [ { "itemno": 347,
                 "qty": 5,
                 "price": 19.99
               },
               { "itemno": 193,
                 "qty": 2,
                 "price": 28.89
               }
             ]
  }
  
), ("1002", 
   
  { "orderno": 1002,
    "custid": "C13",
    "order_date": "2017-05-01",
    "ship_date": "2017-05-03",
    "items": [ { "itemno": 460,
                 "qty": 95,
                 "price": 100.99
               },
               { "itemno": 680,
                 "qty": 150,
                 "price": 8.75
               }
             ]
  }
  
), ("1003",

  { "orderno": 1003,
    "custid": "C31",
    "order_date": "2017-06-15",
    "ship_date": "2017-06-16",
    "items": [ { "itemno": 120,
                 "qty": 2,
                 "price": 88.99
               },
               { "itemno": 460,
                 "qty": 3,
                 "price": 99.99
               }
             ]
  }
  
), ("1004", 

  { "orderno": 1004,
    "custid": "C35",
    "order_date": "2017-07-10",
    "ship_date": "2017-07-15",
    "items": [ { "itemno": 680,
                 "qty": 6,
                 "price": 9.99
               },
               { "itemno": 195,
                 "qty": 4,
                 "price": 35.00
               }
             ]
  }
  
), ("1005", 

  { "orderno": 1005,
    "custid": "C37",
    "order_date": "2017-08-30",
    "items": [ { "itemno": 460,
                 "qty": 2,
                 "price": 99.98
               },
               { "itemno": 347,
                 "qty": 120,
                 "price": 22.00
               },
               { "itemno": 780,
                 "qty": 1,
                 "price": 1500.00
               },
               { "itemno": 375,
                 "qty": 2,
                 "price": 149.98
               }
             ]
  }
  
), ("1006", 

  { "orderno": 1006,
    "custid": "C41",
    "order_date": "2017-09-02",
    "ship_date": "2017-09-04",
    "items": [ { "itemno": 680,
                 "qty": 51,
                 "price": 25.98
               },
               { "itemno": 120,
                 "qty": 65,
                 "price": 85.00
               },
               { "itemno": 460,
                 "qty": 120,
                 "price": 99.98
               }
             ]
  }

), ("1007",

  { "orderno": 1007,
    "custid": "C13",
    "order_date": "2017-09-13",
    "ship_date": "2017-09-20",
    "items": [ { "itemno": 185,
                 "qty": 5,
                 "price": 21.99
               },
               { "itemno": 680,
                 "qty": 1,
                 "price": 20.50
               }
             ]
  }
  
), ("1008",

  { "orderno": 1008,
    "custid": "C13",
    "order_date": "2017-10-13",
    "items": [ { "itemno": 460,
                 "qty": 20,
                 "price": 99.99
               }
             ]
  }


);

Plataforma

Autogerenciado

Serviços

Recursos

Por que o Couchbase?

Migrar para o Capella

Por caso de uso

Por setor

Por necessidade de aplicativo

Documentos populares

Por função de desenvolvedor

COMUNIDADE

Participe da comunidade de desenvolvedores

Centro de recursos

Educação

Comparar

Sobre

Parcerias

Nossos serviços

Parceiros: Registrar um negócio

Pronto para registrar um negócio com o Couchbase?

Marriott

Tutorial do SQL++ para usuários de SQL

Um tutorial de Don Chamberlin

Don Chamberlin

O SQL tem uma função a desempenhar em um mundo pós-relacional?

Exemplo de consulta de dados

Dados de exemplo

Obter o PDF do livro eletrônico