Среда, 08.05.2024, 10:54
Информационные технологии и телекоммуникации.
Приветствую Вас Гость | RSS
Главная SQL tricks - Chapter 3 Регистрация Вход
Меню сайта

Статистика
Онлайн всего: 1
Гостей: 1
Пользователей: 0

Форма входа
SELECT MAX(au_lname) AS "Max last name"
  FROM authors;

  SELECT
    MIN(price) AS "Min price",
    MAX(price) AS "Max price",
    MAX(price) - MIN(price) AS "Range"
  FROM titles;

  SELECT MAX(price * sales)
         AS "Max history revenue"
  FROM titles
  WHERE type = 'history';
  
  --SELECT  08.01.2014 Vitalie Bancu  
  SELECT SUM(advance) AS "Total advances"
  FROM royalties;

SELECT SUM(sales)
         AS "Total sales (2000 books)"
  FROM titles
  WHERE pubdate
    BETWEEN DATE '2000-01-01'
        AND DATE '2000-12-31';

SELECT
    SUM(price) AS "Total price",
    SUM(sales) AS "Total sales",
    SUM(price * sales) AS "Total revenue"
  FROM titles;
  --SELECT  08.01.2014 Vitalie Bancu  
  
  
  
  SELECT AVG(price * 2) AS "AVG(price * 2)"
  FROM titles;

SELECT AVG(sales) AS "AVG(sales)",
       SUM(sales) AS "SUM(sales)"
  FROM titles
  WHERE type = 'business';

SELECT title_id, sales
  FROM titles
  WHERE sales >
        (SELECT AVG(sales) FROM titles)
  ORDER BY sales DESC;
  
  
  --SELECT  08.01.2014 Vitalie Bancu  
  
  SELECT
    COUNT(title_id) AS "COUNT(title_id)",
    COUNT(price) AS "COUNT(price)",
    COUNT(*) AS "COUNT(*)"
  FROM titles;

SELECT
    COUNT(title_id) AS "COUNT(title_id)",
    COUNT(price) AS "COUNT(price)",
    COUNT(*) AS "COUNT(*)"
  FROM titles
  WHERE price IS NOT NULL;

SELECT
    COUNT(title_id) AS "COUNT(title_id)",
    COUNT(price) AS "COUNT(price)",
    COUNT(*) AS "COUNT(*)"
  FROM titles
  WHERE price IS NULL;
 
 
 --SELECT  08.01.2014 Vitalie Bancu 
 SELECT
    COUNT(*)     AS "COUNT(*)"
  FROM titles;

SELECT
    COUNT(price) AS "COUNT(price)",
    SUM(price)   AS "SUM(price)",
    AVG(price)   AS "AVG(price)"
  FROM titles;

SELECT
    COUNT(DISTINCT price)
      AS "COUNT(DISTINCT)",
    SUM(DISTINCT price)
      AS "SUM(DISTINCT)",
    AVG(DISTINCT price)
      AS "AVG(DISTINCT)"
  FROM titles;
 
 
 
 
 --SELECT  08.01.2014 Vitalie Bancu 
 
 SELECT COUNT(au_id)
         AS "COUNT(au_id)"
  FROM title_authors;

SELECT DISTINCT COUNT(au_id)
         AS "DISTINCT COUNT(au_id)"
  FROM title_authors;

SELECT COUNT(DISTINCT au_id)
         AS "COUNT(DISTINCT au_id)"
  FROM title_authors;
  
  
 --SELECT  08.01.2014 Vitalie Bancu 
 
 SELECT
    COUNT(price)
      AS "COUNT(price)",
    SUM(price)
      AS "SUM(price)"
  FROM titles;

SELECT
    COUNT(price)
      AS "COUNT(price)",
    SUM(DISTINCT price)
      AS "SUM(DISTINCT price)"
  FROM titles;

SELECT
    COUNT(DISTINCT price)
      AS "COUNT(DISTINCT price)",
    SUM(price)
      AS "SUM(price)"
  FROM titles;

SELECT
    COUNT(DISTINCT price)
      AS "COUNT(DISTINCT price)",
    SUM(DISTINCT price)
      AS "SUM(DISTINCT price)"
  FROM titles; 
  --0608
  
  
  --SELECT  08.01.2014 Vitalie Bancu 
  
  --Today the Thursday. Today the ninth of January.
  
 -- Let's go.
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT
    au_id,
    COUNT(*) AS "num_books"
  FROM title_authors
  GROUP BY au_id;
  
  DESCRIBE title_authors;
  
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    state,
    COUNT(state) AS "COUNT(state)",
    COUNT(*)     AS "COUNT(*)"
  FROM publishers
  GROUP BY state;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    type,
    SUM(sales)   AS "SUM(sales)",
    COUNT(sales) AS "COUNT(sales)",
    COUNT(*)     AS "COUNT(*)",
    SUM(sales)/COUNT(sales)
      AS "SUM/COUNT(sales)",
    SUM(sales)/COUNT(*)
      AS "SUM/COUNT(*)",
    AVG(sales)   AS "AVG(sales)"
  FROM titles
  GROUP BY type;
  
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    
        SELECT
    type,
    SUM(sales)   AS "SUM(sales)",
    AVG(sales)   AS "AVG(sales)",
    COUNT(sales) AS "COUNT(sales)"
  FROM titles
  GROUP BY type;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    type,
    SUM(sales)   AS "SUM(sales)",
    AVG(sales)   AS "AVG(sales)",
    COUNT(sales) AS "COUNT(sales)"
  FROM titles
  WHERE price >= 13
  GROUP BY type
  ORDER BY "SUM(sales)" DESC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU

  SELECT
    pub_id,
    type,
    COUNT(*) AS "COUNT(*)"
  FROM titles
  GROUP BY pub_id, type
  ORDER BY pub_id ASC, "COUNT(*)" DESC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    CASE
      WHEN sales IS NULL
        THEN 'Unknown'
      WHEN sales <= 1000
        THEN 'Not more than 1,000'
      WHEN sales <= 10000 
        THEN 'Between 1,001 and 10,000'
      WHEN sales <= 100000
        THEN 'Between 10,001 and 100,000'
      WHEN sales <= 1000000
        THEN 'Between 100,001 and 1,000,000'
      ELSE 'Over 1,000,000'
    END
      AS "Sales category",
    COUNT(*) AS "Num titles"
  FROM titles
  GROUP BY
    CASE
      WHEN sales IS NULL
        THEN 'Unknown'
      WHEN sales <= 1000
        THEN 'Not more than 1,000'
      WHEN sales <= 10000 
        THEN 'Between 1,001 and 10,000'
      WHEN sales <= 100000
        THEN 'Between 10,001 and 100,000'
      WHEN sales <= 1000000
        THEN 'Between 100,001 and 1,000,000'
      ELSE 'Over 1,000,000'
    END
  ORDER BY MIN(sales) ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT type
  FROM titles
  GROUP BY type;

SELECT DISTINCT type
  FROM titles;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT price, AVG(sales) AS "AVG(sales)"
  FROM titles
  WHERE price IS NOT NULL
  GROUP BY price
  ORDER BY price ASC;
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT 
    au_id,
    COUNT(*) AS "num_books"
  FROM title_authors
  GROUP BY au_id
  HAVING COUNT(*) >= 3;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT
    type,
    COUNT(price) AS "COUNT(price)",
    AVG(price * sales) AS "AVG revenue"
  FROM titles
  GROUP BY type
  HAVING AVG(price * sales) > 1000000;
 
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT
    type,
    COUNT(price) AS "COUNT(price)"
  FROM titles
  GROUP BY type
  HAVING AVG(price * sales) > 1000000;
 
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT 
    pub_id,
    type,
    COUNT(*) AS "COUNT(*)"
  FROM titles
  GROUP BY pub_id, type
  HAVING COUNT(*) > 1
  ORDER BY pub_id ASC, "COUNT(*)" DESC;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    type,
    SUM(sales) AS "SUM(sales)",
    AVG(price) AS "AVG(price)"
  FROM titles
  WHERE pub_id IN ('P03', 'P04')
  GROUP BY type
  HAVING SUM(sales) > 10000
     AND AVG(price) < 20;
     
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 
 SELECT au_id, authors.city
  FROM authors
  INNER JOIN publishers
    ON authors.city = publishers.city;
    
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    
    SELECT au_fname, au_lname, a.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;
  
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 
 SELECT au_fname, au_lname, a.city
  FROM authors a, publishers p
  c
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    au_id,
    pub_id,
    a.state AS "au_state",
    p.state AS "pub_state"
  FROM authors a
  CROSS JOIN publishers p;
 
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    a.city,
    au_id,
    pub_id,
    a.state AS "au_state",
    p.state AS "pub_state"
  FROM authors a
  CROSS JOIN publishers p
  WHERE a.city = p.city;
  /*CROSS JOIN returns the Cartesian product of rows from tables in the join. 
  In other words, it will produce rows which combine each row from the first table with 
  each row from the second table*/
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    SELECT *
    FROM authors, publishers;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT
    title_id,
    pub_id,
    pub_name
  FROM publishers
  NATURAL JOIN titles;  --natural join соединяет таблицы по столбцам с одинаковыми именами
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    title_id,
    pub_id,
    pub_name,
    advance
  FROM publishers
  NATURAL JOIN titles
  NATURAL JOIN royalties
  WHERE advance < 20000;
  
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
   SELECT
    a.au_id,
    a.au_fname,
    a.au_lname,
    ta.title_id
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  ORDER BY a.au_id ASC, ta.title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
  SELECT
    t.title_id,
    t.title_name,
    t.pub_id,
    p.pub_name
  FROM titles t
  INNER JOIN publishers p
    ON p.pub_id = t.pub_id
  ORDER BY t.title_name ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    a.au_id,
    a.au_fname,
    a.au_lname,
    a.city,
    a.state
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city
    AND a.state = p.state
  ORDER BY a.au_id ASC;
  
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    t.title_id,
    t.title_name,
    p.state,
    p.country
  FROM titles t
  INNER JOIN publishers p
    ON t.pub_id = p.pub_id
  WHERE p.state = 'CA'
    OR p.country NOT IN
      ('USA', 'Canada', 'Mexico')
  ORDER BY t.title_id ASC;
  
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT
    a.au_id,
    COUNT(ta.title_id) AS "Num books"
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  GROUP BY a.au_id
  ORDER BY a.au_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
    SELECT
    t.title_id,
    t.title_name,
    r.advance
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE t.type = 'biography'
    AND r.advance IS NOT NULL
  ORDER BY r.advance DESC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    t.type,
    COUNT(r.advance)
      AS "COUNT(r.advance)",
    SUM(r.advance)
      AS "SUM(r.advance)"
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE r.advance IS NOT NULL
  GROUP BY t.type
  ORDER BY t.type ASC;
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
   SELECT
    t.type,
    t.pub_id,
    COUNT(r.advance) AS "COUNT(r.advance)",
    SUM(r.advance) AS "SUM(r.advance)"
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE r.advance IS NOT NULL
  GROUP BY t.type, t.pub_id
  ORDER BY t.type ASC, t.pub_id ASC;
  
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
   SELECT
    ta.title_id,
    COUNT(ta.au_id) AS "Num authors"
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  GROUP BY ta.title_id
  HAVING COUNT(ta.au_id) > 1
  ORDER BY ta.title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  
  
 SELECT
    t.title_id,
    t.title_name,
    r.advance,
    t.price * t.sales AS "Revenue"
  FROM titles t
  INNER JOIN royalties r
    ON t.price * t.sales > r.advance * 10
    AND t.title_id = r.title_id
  ORDER BY t.price * t.sales DESC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 
 SELECT
    a.au_fname,
    a.au_lname,
    t.title_name
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  ORDER BY a.au_lname ASC, a.au_fname ASC,
    t.title_name ASC;
    
    
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 
 SELECT
    a.au_fname,
    a.au_lname,
    t.title_name,
    p.pub_name
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN publishers p
    ON p.pub_id = t.pub_id
  ORDER BY a.au_lname ASC, a.au_fname ASC,
    t.title_name ASC;
    
    
 
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  
 SELECT
    SUM(t.sales * t.price * r.royalty_rate) AS "Total royalties",
    SUM(r.advance) AS "Total advances",
    SUM((t.sales * t.price * r.royalty_rate) - r.advance) AS "Total due to authors"
  FROM titles t
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 
  SELECT
    ta.au_id,
    t.title_id,
    t.pub_id,
    t.sales * t.price * r.royalty_rate * ta.royalty_share AS "Royalty share",
    r.advance * ta.royalty_share AS "Advance share",
    (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) AS "Due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  ORDER BY ta.au_id ASC, t.title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT
    a.au_id,
    a.au_fname,
    a.au_lname,
    t.title_name,
    (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) AS "Due to author"
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
    AND (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) > 0
  ORDER BY a.au_id ASC, t.title_id ASC;
  
  
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
  SELECT
    t.pub_id,
    COUNT(t.sales) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate) AS "Total royalties",
    SUM(r.advance) AS "Total advances",
    SUM((t.sales * t.price * r.royalty_rate) - r.advance) AS "Total due to authors"
  FROM titles t
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  GROUP BY t.pub_id
  ORDER BY t.pub_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    ta.au_id,
    COUNT(sales) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share) AS "Total royalties share",
    SUM(r.advance * ta.royalty_share) AS "Total advances share",
    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share)) AS "Total due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  GROUP BY ta.au_id
  ORDER BY ta.au_id ASC;
  
  
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   
   SELECT
    t.pub_id,
    ta.au_id,
    COUNT(*) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share) AS "Total royalties share",
    SUM(r.advance * ta.royalty_share) AS "Total advances share",
    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share)) AS "Total due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  INNER JOIN publishers p
    ON p.pub_id = t.pub_id
  WHERE t.sales IS NOT NULL
    AND p.country IN ('USA')
  GROUP BY t.pub_id, ta.au_id
  HAVING SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) - 
    (r.advance * ta.royalty_share)) > 0
  ORDER BY t.pub_id ASC, ta.au_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT a.au_fname, a.au_lname, a.city
  FROM authors a;

  SELECT p.pub_name, p.city
  FROM publishers p;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT a.au_fname, a.au_lname, p.pub_name, a.city, p.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;
    
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    
  SELECT a.au_fname, a.au_lname, p.pub_name, a.city, p.city
  FROM authors a
  LEFT OUTER JOIN publishers p
    ON a.city = p.city
  ORDER BY p.pub_name ASC,
    a.au_lname ASC, a.au_fname ASC;
   
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT a.au_fname, a.au_lname, p.pub_name, a.city, p.city
  FROM authors a
  RIGHT OUTER JOIN publishers p
    ON a.city = p.city
  ORDER BY p.pub_name ASC,
    a.au_lname ASC, a.au_fname ASC;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT a.au_fname, a.au_lname, p.pub_name, a.city, p.city
  FROM authors a
  FULL OUTER JOIN publishers p
    ON a.city = p.city
  ORDER BY p.pub_name ASC,
    a.au_lname ASC, a.au_fname ASC;
    
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
         
    SELECT
    a.au_id,
    COUNT(ta.title_id) AS "Num books"
  FROM authors a
  LEFT OUTER JOIN title_authors ta
    ON a.au_id = ta.au_id
  GROUP BY a.au_id
  ORDER BY a.au_id ASC;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT a.au_id, a.au_fname, a.au_lname
  FROM authors a
  LEFT OUTER JOIN title_authors ta
    ON a.au_id = ta.au_id
  WHERE ta.au_id IS NULL;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT a.au_id, a.au_fname, a.au_lname,
    tta.title_id, tta.title_name, tta.sales
  FROM authors a
  LEFT OUTER JOIN
  (SELECT ta.au_id, t.title_id,
      t.title_name, t.sales
    FROM title_authors ta
    INNER JOIN titles t
      ON t.title_id = ta.title_id
    WHERE sales > 100000) tta
    ON a.au_id = tta.au_id
  ORDER BY a.au_id ASC, tta.title_id ASC;
  
  
 --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT
    e1.emp_name AS "Employee name",
    e2.emp_name AS "Boss name", e1.boss_id, e2.emp_id
FROM employees e1
INNER JOIN employees e2
  ON e1.boss_id = e2.emp_id;
  
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT a1.au_id, a1.au_fname,
    a1.au_lname, a1.state
  FROM authors a1
  INNER JOIN authors a2
    ON a1.state = a2.state
  WHERE a2.au_id = 'A04';
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT t1.title_id, t1.sales,
    t2.title_id AS "Better seller",
    t2.sales AS "Higher sales"
  FROM titles t1
  INNER JOIN titles t2
    ON t1.sales < t2.sales
  WHERE t1.type = 'biography'
    AND t2.type = 'biography'
  ORDER BY t1.title_id ASC, t2.sales ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT
    a1.au_fname, a1.au_lname,
    a2.au_fname, a2.au_lname
  FROM authors a1
  INNER JOIN authors a2
    ON a1.state = a2.state
  WHERE a1.state = 'NY'
  ORDER BY a1.au_id ASC, a2.au_id ASC;
  
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT
    a1.au_fname, a1.au_lname,
    a2.au_fname, a2.au_lname
  FROM authors a1
  INNER JOIN authors a2
    ON a1.state = a2.state
    AND a1.au_id <> a2.au_id
  WHERE a1.state = 'NY'
  ORDER BY a1.au_id ASC, a2.au_id ASC;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT
    a1.au_fname, a1.au_lname,
    a2.au_fname, a2.au_lname
  FROM authors a1
  INNER JOIN authors a2
    ON a1.state = a2.state
    AND a1.au_id < a2.au_id
  WHERE a1.state = 'NY'
  ORDER BY a1.au_id ASC, a2.au_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT pub_id
  FROM titles
  WHERE type = 'biography';
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT pub_name
  FROM publishers
  WHERE pub_id IN ('P01', 'P03');
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT DISTINCT pub_name
  FROM publishers p
  INNER JOIN titles t
    ON p.pub_id = t.pub_id
  WHERE t.type = 'biography';
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  
  SELECT pub_name
  FROM publishers
  WHERE pub_id IN
    (SELECT pub_id
      FROM titlescnas
      WHERE type = 'biography');
      --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
      
  SELECT au_id, city
  FROM authors
  WHERE city IN
    (SELECT city FROM publishers);
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT DISTINCT a.au_id, a.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    
    SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE au_id NOT IN
     (SELECT au_id FROM title_authors);
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname
  FROM authors a
  WHERE NOT EXISTS
    (SELECT *
      FROM title_authors ta
      WHERE a.au_id = ta.au_id);
      --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
      SELECT a.au_id, a.au_fname, a.au_lname
  FROM authors a
  LEFT OUTER JOIN title_authors ta
    ON a.au_id = ta.au_id
  WHERE ta.au_id IS NULL;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
      
  SELECT au_id, au_fname, au_lname, state
  FROM authors
  WHERE state IN
    (SELECT state
      FROM authors
      WHERE au_id = 'A04');
      
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU   
  
  SELECT a1.au_id, a1.au_fname,
    a1.au_lname, a1.state
  FROM authors a1
  INNER JOIN authors a2
    ON a1.state = a2.state
  WHERE a2.au_id = 'A04';
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT title_id, price
  FROM titles
  WHERE price = 
    (SELECT MAX(price)
      FROM titles);
   
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU   
   
    SELECT a.au_id, a.city, p.pub_id
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;  
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, city
  FROM authors
  WHERE city IN
    (SELECT city
      FROM publishers);
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    SELECT city
    FROM publishers;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, city
  FROM authors
  WHERE city IN
    ('New York', 'San Francisco',
     'Hamburg', 'Berkeley');
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
   SELECT
    candidate.title_id,
    candidate.type,
    candidate.sales
  FROM titles candidate
  WHERE sales >=
    (SELECT AVG(sales)
      FROM titles average
      WHERE average.type = candidate.type);  
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE au_id IN
    (SELECT au_id
      FROM title_authors
      WHERE royalty_share = 1.0);
      
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE 1.0 IN
    (SELECT royalty_share
      FROM title_authors
      WHERE title_authors.au_id =
            authors.au_id);
            
            
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT pub_name
  FROM publishers
  WHERE pub_id IN
    (SELECT pub_id
      FROM titles
      WHERE type = 'biography');

  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
    SELECT pub_name
  FROM publishers
  WHERE publishers.pub_id IN
    (SELECT titles.pub_id
      FROM titles
      WHERE type = 'biography');
      
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT title_id,
    price,
    (SELECT AVG(price) FROM titles)
      AS "AVG(price)",
    price - (SELECT AVG(price) FROM titles)
      AS "Difference"
  FROM titles
  WHERE type='biography';
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT title_id,
    (SELECT au_id
      FROM title_authors ta
      WHERE au_order = 1
        AND title_id = t.title_id)
        AS "Author 1",
    (SELECT au_id
      FROM title_authors ta
      WHERE au_order = 2
        AND title_id = t.title_id)
        AS "Author 2",
    (SELECT au_id
      FROM title_authors ta
      WHERE au_order = 3
        AND title_id = t.title_id)
        AS "Author 3"
  FROM titles t;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id,
    (SELECT COUNT(*)
      FROM title_authors ta
      WHERE ta.au_id = a.au_id)
        AS "Num books"
  FROM authors a
  ORDER BY au_id ASC;
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  
  SELECT au_id, 
    (SELECT MAX(pubdate)
      FROM titles t
      INNER JOIN title_authors ta
        ON ta.title_id = t.title_id
      WHERE ta.au_id = a.au_id)
        AS "Latest pub date"
  FROM authors a;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT t1.title_id, t1.sales,
    (SELECT SUM(t2.sales)
      FROM titles t2
      WHERE t2.title_id <= t1.title_id)
        AS "Running total"
  FROM titles t1;
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT MAX(ta.count_titles) AS "Max titles"
  FROM (SELECT COUNT(*) AS count_titles
         FROM title_authors
         GROUP BY au_id) ta;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname, state
  FROM authors
  WHERE state =
    (SELECT state
      FROM publishers
      WHERE pub_name = 'Tenterhooks Press');
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname, state
  FROM authors
  WHERE state =
    (SELECT state
      FROM publishers
      WHERE pub_name = 'XXX');
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
  SELECT title_id, sales
  FROM titles
  WHERE sales >
    (SELECT AVG(sales)
      FROM titles);
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT ta.au_id, ta.title_id
  FROM titles t
  INNER JOIN title_authors ta
    ON ta.title_id = t.title_id
  WHERE sales >
    (SELECT AVG(sales)
      FROM titles)
  ORDER BY ta.au_id ASC, ta.title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, title_id
  FROM title_authors ta
  WHERE
    (SELECT AVG(sales)
      FROM titles t
      WHERE ta.title_id = t.title_id)
    >
    (SELECT AVG(sales)
      FROM titles)
  ORDER BY au_id ASC, title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
 SELECT title_id, price
  FROM titles
  WHERE price >
    (SELECT MAX(price)
      FROM titles
      GROUP BY type
      HAVING type = 'biography');
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
  SELECT pub_id, AVG(sales) AS "AVG(sales)"
  FROM titles
  GROUP BY pub_id
  HAVING AVG(sales) >
    (SELECT AVG(sales)
      FROM titles);
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  SELECT ta1.au_id, ta1.title_id,
    ta1.royalty_share
  FROM title_authors ta1
  WHERE ta1.royalty_share <
    (SELECT MAX(ta2.royalty_share)
      FROM title_authors ta2
      WHERE ta1.title_id = ta2.title_id);
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  
  SELECT type, title_id, price
  FROM titles t1
  WHERE price >
    (SELECT AVG(t2.price)
      FROM titles t2
      WHERE t1.type = t2.type)
  ORDER BY type ASC, title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT type, title_id, sales
  FROM titles t1
  WHERE sales <
    (SELECT MAX(sales)
      FROM titles t2
      WHERE t1.type = t2.type
        AND sales IS NOT NULL)
  ORDER BY type ASC, title_id ASC;
  
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  
  SELECT pub_name
  FROM publishers
  WHERE pub_id IN
    (SELECT pub_id
      FROM titles
      WHERE type = 'biography');
      
  --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU 
  
  SELECT pub_name
  FROM publishers
  WHERE pub_id NOT IN
    (SELECT pub_id
      FROM titles
      WHERE type = 'biography');
      
    --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
    
    SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE au_id NOT IN
    (SELECT au_id
      FROM title_authors);
      
   --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
  
  SELECT DISTINCT a.au_id, au_fname, au_lname
  FROM title_authors ta
  INNER JOIN authors a
    ON ta.au_id = a.au_id
  WHERE title_id IN
    (SELECT title_id
      FROM titles
      WHERE pub_id = 'P03');
      
     --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
     SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE au_id IN
    (SELECT au_id
      FROM title_authors
      WHERE title_id IN
        (SELECT title_id
          FROM titles
          WHERE type = 'biography'));
      
      --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU    
          
  SELECT au_id, au_fname, au_lname
  FROM authors
  WHERE state = 'CA'
    AND au_id IN
      (SELECT au_id
        FROM title_authors
        WHERE royalty_share < 0.5
          AND au_order > 1);
              
     --SELECT  09.01.2014 Vitalie Bancu @VITALLYBANKOU
  SELECT au_id, au_fname, au_lname
  FROM authors a
  WHERE au_id IN
    (SELECT au_id
      FROM title_authors
      WHERE royalty_share < 1.0);     
     
 -- SQL tricks. (SELECT.) -- 09.01.2014  
Поиск

Календарь
«  Май 2024  »
ПнВтСрЧтПтСбВс
  12345
6789101112
13141516171819
20212223242526
2728293031

Друзья сайта
  • Официальный блог
  • Сообщество uCoz
  • FAQ по системе
  • Инструкции для uCoz

    		•
    Copyright Vitalie Bancu - vitallybankou © 2024 Бесплатный конструктор сайтов - uCoz
     
     
    Besucherzahler senior people meet.com
    счетчик посещений