Главная страница
Навигация по странице:

  • Лабораторные задания типа А

  • Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007


    Скачать 0.8 Mb.
    НазваниеМетодические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
    Дата15.03.2022
    Размер0.8 Mb.
    Формат файлаdoc
    Имя файлаsql (1).doc
    ТипМетодические указания
    #397929
    страница2 из 11
    1   2   3   4   5   6   7   8   9   10   11

    Описание задания
    База данных книготорговой компании
    Рассмотрим простую предметную область жизнедеятельности,

    связанную с книгоизданием и маркетингом. В рамках данной предметной области существуют издатели, которые публикуют книги, авторы, которые книги пишут, и издания (сами книги). Разработана база данных pubs, определяющая описанную выше предметную область. Инфологическая модель предметной области с использованием диаграмм “сущность-связь” (ER-диаграмм) [1]), разработанных Ченом, представлена на рис. 1.

    На данном рисунке прямоугольниками обозначены типы сущностей (объектов), а ромбами - типы связей между сущностями. Атрибуты сущностей указаны мелким шрифтом в том же прямоугольнике, который отображает типы сущностей. Имя типа сущности отмечено в верхней части прямоугольника жирным шрифтом. Атрибуты связей в данном случае обозначены овалами. Как видно из рис. 1 у связи “Написана” имеется два атрибута: первый атрибут определяет порядок автора в названии книги, второй атрибут - гонорар автора книги.

    Б аза данных книготорговой компании (база данных pubs) включает три таблицы, определяющие сущности: таблица authors определяет авторов, таблица publishers - издателей, а таблица titles - сами книги. Четвертая таблица titleauthor задает отношение между таблицами titles и authors. Она показывает, какие авторы написали какие книги. Связь между таблицами titiles и publishers определяется столбцом pub_id в данных таблицах.
    Ниже представлены структуры используемых таблиц.
    Структура таблицы authors


    Имя столбца

    Тип данных

    Размерность

    Возможность значений null

    Содержательное описание

    au_id

    varchar

    11

    Нет

    Идентификатор автора

    au_lname

    varchar

    40

    Нет

    Фамилия автора

    au_fname

    varchar

    20

    Нет

    Имя автора

    phone

    char

    12

    Нет

    Номер телефона

    address

    varchar

    40

    Да

    Адрес (улица, дом, квартира)

    city

    varchar

    20

    Да

    Город проживания

    state

    char

    2

    Да

    Штат проживания

    zip

    char

    5

    Да

    Энергичность

    contract

    bit

    1

    Нет

    Наличие контракта


    Структура таблицы publishers


    Имя столбца

    Тип данных

    Размерность

    Возможность значений null

    Содержательное описание

    pub_id

    char

    4

    Нет

    Идентификатор издательства (издате-

    pub_name

    varchar

    40

    Да

    ля)

    city

    varchar

    20

    Да

    Название издательства (имя издателя)

    state

    char

    2

    Да

    Город

    country

    varchar

    30

    Да

    Штат


    Структура таблицы titles



    Имя столбца

    Тип данных

    Размерность

    Возможность значений null

    Содержательное описание

    title id

    varchar

    6

    Нет

    Идентификатор книги

    title

    varchar

    80

    Нет

    Название книги

    type

    char

    12

    Нет

    Тип книги

    pub_id

    char

    4

    Да

    Идентификатор издательства

    price

    money

    8

    Да

    Цена

    advance

    money

    8

    Да

    Аванс (стоимость предварительной продажи)

    royalty

    int

    4

    Да

    Гонорар

    ytd_sales

    int

    4

    Да

    Число книг, проданных в текущем году


    notes

    varchar

    200

    Да

    Замечания

    pubdate


    datetime

    8

    Нет

    Дата опубликования


    Структура таблицы titleauthor


    Имя столбца

    Тип данных

    Размерность

    Возможность значений null

    Содержательное описание

    au_id

    varchar

    11

    Нет

    Идентификатор автора книги

    title id

    varchar

    6

    Нет

    Идентификатор книги

    au_ord

    tinyint

    1

    Да

    Порядок автора в названии книги

    royaltyper

    int

    4

    Да

    Авторский гонорар


    В столбце type таблицы titles используются следующие типы книг:

    business - книги по бизнесу, mod_cook - книги по современной кулинарии, popular_comp - книги по компьютерной тематике, psychology – книги по психологии, trad_cook - книги по традиционной кулинарии, UNDECIDED - неопределенный тип книги.

    В столбцах state таблиц authors и publishers используются следующие обозначения административных единиц США: CA - штат Калифорния, DC - округ Колумбия, IL - штат Иллинойс, IN - штат Индиана, KS -штат Канзас, MD - штат Мэриленд, MA - штат Массачусетс, MI – штат Мичиган, NY - штат Нью-Йорк, OR - штат Орегон, TN - штат Теннесси, TX - штатТехас, UT - штат Юта.

    В столбце country таблицы publishers используются следующие

    обозначения стран: France - Франция, Germany - Германия, USA - США.

    Домен городов, используемый в таблицах authors и publishers,

    включает города Ann Arbor, Berkeley, Boston, Chicago, Corvallis, Colevo,

    Dallas, Gary, Lawrence, Menlo Park, Munchen, Nashville, New York, Oakland, Palo Alto, Paris, Rockville, Salt Lake City, San Francisco, San Jose, Vacaville, Walnul Creek, Washington.

    В приложении 1 приведен полный пример базы данных pubs.
    Лабораторные задания типа А
    Дать содержательную интерпретацию SQL-запросам, выполнить их на SQL-сервере с использованием клиентских утилит Query Analyzerили SQL- EM, дать содержательную интерпретацию результатам выполнения SQL-запросов.
    1) SELECT au_lname, au_fname

    FROM authors
    2) SELECT au_lname, au_fname

    FROM authors

    ORDER BY au_lname
    3) SELECT au_lname, au_fname

    FROM authors

    ORDER BY au_lname, au_fname
    4) SELECT title_id, price, ytd_sales,

    price*ytd_sales “ytd dollar sales”

    FROM titles

    ORDER BY price*ytd_sales
    5) SELECT title_id, price, ytd_sales,

    price*ytd_sales “ytd dollar sales”

    FROM titles

    ORDER BY price*ytd_sales DESC
    6) SELECT title_id, type, ytd_sales

    FROM titles

    ORDER BY type ASC, ytd_sales DESC
    7) SELECT AVG(price)

    FROM titles
    8) SELECT DISTINCT type

    FROM titles

    ORDER BY type ACS
    9) SELECT DISTINCT city

    FROM authors

    ORDER BY city DESC
    10) SELECT DISTINCT state

    FROM authors

    ORDER BY state
    11) SELECT DISTINCT country

    FROM publishers

    ORDER BY country DESC
    12) SELECT AVG(price), AVG(DISTINCT price)

    FROM titles
    13) SELECT *

    FROM titles
    14) SELECT au_lname, au_fname

    FROM authors

    WHERE state= “CA”
    15) SELECT type, title_id, price

    FROM titles

    WHERE price*ytd_sales < advance
    16) SELECT au_id, city, state

    FROM authors

    WHERE state= “CA” OR city= “Palo Alto”
    17) SELECT title_id, price

    FROM titles

    WHERE price between $5 AND $15
    18) SELECT title_id, price

    FROM titles

    WHERE type IN (“mod_cook”, “trad_cook”, “business”)
    19) SELECT au_lname, au_fname, city, state

    FROM authors

    WHERE city like “San%”
    20) SELECT type, title_id, price

    FROM titles

    WHERE title_id like “B_2075”
    21) SELECT type, title_id, price

    FROM titles

    WHERE title_id like “B[AUN]7832”
    22) SELECT AVG(price) “AVG”

    FROM titles

    WHERE type= “business”
    23) SELECT AVG(price) “avg” SUM(price) “sum”

    FROM titles

    WHERE type IN (“business”, “mod_cook”)
    24) SELECT COUNT(*)

    FROM authors

    WHERE state= “CA”
    25) SELECT COUNT(*)

    FROM titles

    WHERE LIKE “Co%s”
    26) SELECT title

    FROM titles

    WHERE ytd_sales IS NULL
    27) SELECT au_lname “Фамилия”, au_fname “Имя”

    FROM authors

    WHERE contract=1 AND phone LIKE “408____-__2_”
    28) SELECT phone

    FROM authors

    WHERE address LIKE “%Broadway Av.%”
    29) SELECT title, pubdate

    FROM titles

    WHERE pubdate>= “Jun 9 1991 12:00AM”

    AND pubdate< “6/16/91”
    30) SELECT type, AVG(price) “avg”, SUM(price) “sum”

    FROM titles

    WHERE type IN (“business”, “psychology”)

    GROUP BY type
    31) SELECT type, pub_id, AVG(price) “avg”, SUM(price) “sum”

    FROM titles

    WHERE type IN (“business”, “mod_cook”)

    GROUP BY type, pub_id
    32) SELECT type, AVG(price)

    FROM titles

    WHERE price>$11

    GROUP BY type

    HAVING AVG(price)>$19.7
    33) SELECT au_id, COUNT(*)

    FROM authors

    GROUP BY au_id

    HAVING COUNT(*)>1
    34) SELECT type, MIN(price), MAX(price)

    FROM titles

    GROP BY type

    ORDER BY type
    35) SELECT type, MIN(price), MAX(price)

    FROM titles

    GROUP BY type

    HAVING MAX(price)-MIN(price)>=3
    36) SELECT state, COUNT(DISTINCT pub_id)

    FROM publishers

    GROUP BY state
    37) SELECT pub_name, AVG(price) “дvg”,

    COUNT(DISTINCT title_id) “count”

    FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

    GROUP BY pub_name
    38) SELECT type, (MIN(price)+MIN(price))/2, AVG(price)

    FROM titles

    GROUP BY type

    HAVING type<> “UNDECIDED”

    ORDER BY 2 DESC
    39) SELECT type, MIN(pubdate), MAX(pubdate)

    FROM titles

    GROUP BY type
    40) SELECT title, pub_name

    FROM titles CROSS JOIN publishers
    41) SELECT *

    FROM titles, publishers
    42) SELECT title, pub_name

    FROM titles, publishers

    WHERE titles.pub_id=publishers.pub_id
    43) SELECT title, pub_name

    FROM titles JOIN publishers
    ON titles.pub_id=publishers.pub_id
    44) SELECT *

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id
    45) SELECT t.*, pub_name

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id
    46) SELECT a.city, a.state

    FROM authors a, publishers p

    WHERE a.city=p.city AND a.state=p.state
    47) SELECT au_lname, au_fname

    FROM authors a JOIN titleauthor ON a.au_id=ta.au_id

    JOIN titles t ON ta.title_id=t.title_id

    WHERE au_lname LIKE “R%”

    AND state IN (“CA”, “TX”, “NY”, “OR”, “UT”)

    AND (title LIKE “_h_ %” OR title LIKE “% _h_ %”

    OR title LIKE “% _h_”)
    48) SELECT title, type

    FROM authors a, titles t, titleauthor ta, publishers p

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND t.pub_id=p.pub_id AND p.city=a.city
    49) SELECT au_lname, au_fname, title

    FROM authors a, titles t, titleauthor ta, publishers p

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND t.pub_id=p.pub_id

    AND ((p.country= ‘USA’ AND t.type=’popular_comp’)

    OR (p.country=’France’ AND t.type=’psychology’))
    50) SELECT au_lname, au_fname, city

    FROM authors a, titles t, titleauthor ta

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND (city LIKE “[CPR]%” OR city LIKE “%San%”)

    AND (title LIKE “% the %” OR title LIKE “The %”

    OR title LIKE “% a %” OR title LIKE “A %”)
    51) SELECT DISTINCT au_lname, au_fname

    FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id

    JOIN titles t ON ta.title_id=t.title_id

    JOIN publishers p ON p.pub_id=t.pub_id

    WHERE p.state= “CA”

    ORDER BY au_lname, au_fname
    52) SELECT pub_name

    FROM publishers p JOIN titles t ON p.pub_id=t.pub_id

    WHERE $15>price AND type= “psychology”

    ORDER BY pub_name
    53) SELECT pub_name, AVG(price)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    GROUP BY pub_name
    54) SELECT pub_name, AVG(price)

    FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

    GROUP BY pub_name
    55) SELECT au_lname, au_fname, title

    FROM authors a, titles t, titleauthor ta

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND type= “popular_comp”
    56) SELECT au_lname, au_fname, title

    FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id

    JOIN titles t ON ta.title_id=t.title_id

    WHERE type= “psychology”
    57) SELECT au_lname, au_fname, pub_name, COUNT(*)

    FROM authors a, titles t, titleauthor ta, publishers p

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND t.pub_id=p.pub_id

    GROUP BY au_lname, au_fname, pub_name
    58) SELECT MIN(price)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    GROUP BY country

    HAVING country=’USA’
    59) SELECT pub_name, COUNT(*)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    AND (type= ‘mod_cook’ OR type=’trad_cook’)

    GROUP BY pub_name
    60) SELECT pub_name, COUNT(*)

    FROM publishers p, titles t

    WHERE p.pub_id=t.pub_id AND price>$15

    GROUP BY pub_name

    ORDER BY pub_name DESC
    61) SELECT title, COUNT(DISTINCT a.au_id)

    FROM titles t JOIN titleauthor ta ON t.title_id=ta.title_id

    JOIN authors a ON ta.au_id=a.au_id

    JOIN publishers p ON p.pub_id=t.pub_id

    GROUP BY title
    62) SELECT state, COUNT(DISTINCT p.pub_id)

    FROM publishers p JOIN titles t ON p.pub_id=t.pub_id

    GROUP BY state
    63) SELECT title

    FROM titles

    WHERE pub_id=

    (SELECT pub_id

    FROM publishers

    WHERE pub_name= “Binnet & Hardley”)
    64) SELECT pub_name

    FROM publishers

    WHERE pub_id IN

    (SELECT pub_id

    FROM titles

    WHERE type= “business”)
    65) SELECT pub_name

    FROM publishers p

    WHERE EXISTS

    (SELECT *

    FROM titles t

    WHERE p.pub_id=t.pub_id

    AND type=“popular_comp”)
    66) SELECT pub_name

    FROM publishers p

    WHERE NOT EXISTS

    (SELECT *

    FROM titles t

    WHERE p.pub_id=t.pub_id

    AND type=“mod_cook”)
    67) SELECT pub_name

    FROM publishers

    WHERE pub_id NOT IN

    (SELECT pub_id

    FROM titles

    WHERE type=“psychology”)
    68) SELECT type, price

    FROM titles

    WHERE price < (SELECT AVG(price) FROM titles)
    69) SELECT type, AVG(price)

    FROM titles

    GROUP BY type

    HAVING AVG(price) < (SELECT AVG(price) FROM titles)
    70) SELECT DISTINCT a.city, a.state

    FROM authors a

    WHERE NOT EXISTS

    (SELECT *

    FROM publishers p

    WHERE a.city=p.city AND a.state=p.state)
    71) SELECT DISTINCT p.city, p.state

    FROM publishers p

    WHERE NOT EXISTS

    (SELECT *

    FROM authors a

    WHERE p.city=a.city AND p.state=a.state)
    72) SELECT MIN(price)

    FROM titles t

    WHERE t.pub_id IN

    (SELECT pub_id

    FROM publishers

    WHERE country=’USA’)
    73) SELECT title, type, price

    FROM titles

    WHERE price>ALL

    (SELECT price

    FROM titles

    WHERE type= “psychology”)
    74) SELECT COUNT(DISTINCT city)

    FROM publishers

    WHERE pub_id IN

    (SELECT pub_id

    FROM titles

    WHERE type= “psychology”)
    75) SELECT pub_name

    FROM publishers p

    WHERE 15>SOME

    (SELECT price

    FROM titles t

    WHERE p.pub_id=t.pub_id

    AND type= “trad_cook”)
    76) SELECT pub_name, state

    FROM publishers

    WHERE pub_id NOT IN

    (SELECT pub_id

    FROM titles)
    77) SELECT title

    FROM titles

    WHERE pub_id NOT IN

    (SELECT pub_id

    FROM publishers)
    78) SELECT title

    FROM titles t

    WHERE price>=

    (SELECT AVG(price)

    FROM titles tt, publishers pp

    GROUP BY pub_id

    HAVING t.pub_id=pp.pub_id)
    79) SELECT au_lname, au_fname, price

    FROM authors a, titles t, titleauthor ta, publishers p

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    AND t.pub_id=p.pub_id AND country=’USA’

    AND price=

    (SELECT MIN(price)

    FROM titles tt, publishers pp

    WHERE tt.pub_id=pp.pub_id

    GROUP BY country

    HAVING country=’USA’)
    80) SELECT DISTINCT au_lname, au_fname

    FROM authors a, titles t, titleauthor ta

    WHERE a.au_id=ta.au_id AND ta.title_id IN

    (SELECT title_id

    FROM titles

    WHERE ytd_sales=

    (SELECT MAX(ytd_sales)

    FROM titles))
    81) SELECT DISTINCT a.city, a.state

    FROM authors a

    WHERE NOT EXISTS

    (SELECT *

    FROM publishers p

    WHERE a.city=p.city AND a.state=p.state)

    UNION SELECT DISTINCT p.city, p.state

    FROM publishers p

    WHERE NOT EXISTS

    (SELECT *

    FROM authors a

    WHERE p.city=a.city AND p.state=a.state)
    82) SELECT title, price

    FROM titles t JOIN publishers p ON t.pub_id=p.pub_id

    WHERE p.country= “USA” AND t.price=

    (SELECT MAX(price)

    FROM titles tt JOIN publishers pp

    ON tt.pub_id=pp.pub_id

    WHERE country= “USA”)
    83) SELECT pub_name, COUNT(*)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    GROUP BY pub_name

    HAVING COUNT(*)>=ALL

    (SELECT COUNT(*)

    FROM titles tt, publishers pp

    WHERE tt.pub.id=pp.pub_id

    GROUP BY pub_name)
    84) SELECT pub_name, city, state, country

    FROM publishers p

    WHERE EXISTS

    (SELECT *

    FROM titles t

    WHERE t.pub_id=p.pub_id)

    AND 20>ALL

    (SELECT price

    FROM titles t

    WHERE t.pub_id=p.pub_id

    AND price IS NOT NULL)
    85) SELECT state, SUM(price)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    GROUP BY state

    HAVING state NOT IN (“TN”, “MA”, “TX”)

    AND SUM(price)>

    (SELECT SUM(price)

    FROM titles tt, publishers pp

    WHERE tt.pub.id=pp.pub_id

    AND pp.city= “Boston”)
    86) SELECT pub_name, MIN(price)

    FROM titles t, publishers p

    WHERE t.pub_id=p.pub_id

    GROUP BY pub_name

    HAVING MIN(price)>=ALL

    (SELECT MIN(price)

    FROM titles tt JOIN publishers pp

    ON tt.pub_id=pp.pub_id

    GROUP BY pub_name)
    87) SELECT *

    FROM publishers

    WHERE pub_id IN

    (SELECT pub_id

    FROM titles

    WHERE type= “psychology” AND pub_id IN

    (SELECT pub_id

    FROM publishers

    WHERE country= “USA”

    AND state<> “CA”)
    88) SELECT au_lname, au_fname

    FROM authors a

    WHERE a.au_id IN

    (SELECT au_id

    FROM titleauthor ta

    WHERE ta.title_id IN

    (SELECT title_id

    FROM titles t

    WHERE “CA”=SOME

    (SELECT state

    FROM publishers p

    WHERE p.pub_id=t.pub_id)))

    ORDER BY au_lname, au_fname
    89) SELECT state, COUNT(*)

    FROM publishers p

    WHERE EXISTS

    (SELECT *

    FROM titles t

    WHERE p.pub_id=t.pub_id)

    AND $22>ALL

    (SELECT price

    FROM titles t

    WHERE p.pub_id=t.pub_id

    AND price IS NOT NULL)

    GROUP BY state

    ORDER BY state ASC
    90) SELECT state

    FROM publishers p1

    GROUP BY state

    HAVING COUNT(DISTINCT pub_name)=

    (SELECT COUNT(*)

    FROM publishers p2

    WHERE EXISTS

    (SELECT *

    FROM titles t

    WHERE p2.pub_id=t.pub_id)

    AND $22.5>ALL

    (SELECT price

    FROM titles t

    WHERE p2.pub_id=t.pub_id

    AND price IS NOT NULL)

    GROUP BY state

    HAVING p1.state=p2.state)
    91) SELECT p1.pub_id

    FROM titles t 1 , publishers p1

    WHERE t1.pub_id=p1.pub_id

    GROUP BY p1.pub_id

    HAVING COUNT(DISTINCT title)=

    (SELECT COUNT(*)

    FROM titles t2

    WHERE t2.pub_id=p1.pub_id

    AND EXISTS

    (SELECT *

    FROM titleauthor ta3, authors a3

    WHERE ta3.au_id=a3.au_id

    AND ta3.title_id=t2.title_id

    AND a3.state IN

    (SELECT state

    FROM publishers p4

    WHERE “business”=SOME

    (SELECT type

    FROM titles t5

    WHERE p4.pub_id= t5.pub_id))))
    92) SELECT city, state

    FROM authors

    UNION SELECT city, state

    FROM publishers

    ORDER BY state, sity
    93) SELECT city

    FROM authors

    UNION SELECT city

    FROM publishers
    94) SELECT state

    FROM authors

    UNION SELECT state

    FROM publishers
    95) SELECT city, state

    FROM authors

    WHERE state IS NOT NULL

    UNION SELECT city, state

    FROM publishers

    WHERE state IS NOT NULL

    ORDER BY city DESC, state ASC
    96) SELECT state, MIN(price), MAX(price), AVG(price)

    FROM authors a, titles t, titleauthor ta

    WHERE ta.title_id=t.title_id AND a.au_id=ta.au_id

    GROUP BY state

    HAVING state<> “CA”

    1   2   3   4   5   6   7   8   9   10   11


    написать администратору сайта