Задачи и упражнения по SQL запросам

Задачи и упражнения по SQL запросам

SELECT (обучающий этап) задачи по SQL запросам -> https://exercises-on-sql.blogspot.com/
DML
Используя таблицу Product, определить количество производителей, выпускающих по одной модели
Как объединить данные из двух столбцов в один без использования UNION и JOIN?
Команды DML
Как выводить в запросе все столбцы кроме одного, не перечисляя их?
Тест знаний SQL - Основы
Проектирование Интернет-приложений
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
Найти производителей, которые выпускают более одной модели, при этом все

Оператор SELECT
Предикаты (часть I)
Переименование столбцов и вычисления в результирую...
Получение итоговых значений
Использование в запросе нескольких источников записей
Традиционные операции над множествами и оператор S...
Использование ключевых слов SOME | ANY и ALL с пре...
Преобразование типов
Функции Transact-SQL для обработки даты/времени
Функции работы со строками в MS SQL SERVER 2005
Операторы модификации данных
Оператор UPDATE
Приложение 1. Описание учебных баз данных
Как объединить данные из двух столбцов в один без ...
Как добавить новый столбец в таблицу между существ...
Как вывести по N строк из каждой группы?
Как удалить дубликаты строк из таблицы?
Как удалить дубликаты строк при наличии первичного...
Как подсчитать накопительный итог?
Как выводить в запросе все столбцы кроме одного, н...

Как выводить в запросе все столбцы кроме одного, не перечисляя их?

Источник:
https://exercises-on-sql.blogspot.com/2017/02/blog-post.html

Другими словами, как исключить столбец с известным именем из результата запроса select * from Table?

Перечислять столбцы также приходится в операторе INSERT, исключая из списка автоинкрементируемые столбцы или столбцы, которым нужно присвоить значение по умолчанию.

Допустим, требуется выбрать все столбцы из таблицы Laptop кроме столбца code. Чтобы вывести все столбцы таблицы, достаточно написать

SELECT * FROM Laptop;

Но чтобы исключить из списка столбец code, мы вынуждены перечислить все остальные столбцы:

SELECT model, speed, ram, hd, price, screen
FROM Laptop;

Было бы неплохо, если мы могли написать что-то типа

SELECT *[^code] FROM Laptop;

Подобная возможность позволила бы нам избежать рутинной работы с вероятностью ошибиться при наборе и пригодилась бы при динамическом формировании запроса, когда заранее не известна ни таблица, ни число столбцов в ней.

Но, увы, такой возможности у нас нет. Зато мы можем сформировать список столбцов с помощью вспомогательного скрипта, чтобы в дальнейшем использовать его результаты в запросах. Это мы можем сделать, используя стандартное представление метаданных, которое называется информационной схемой:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Laptop' AND
 COLUMN_NAME NOT IN ('code');

Предикат NOT IN мы используем для того, чтобы можно было в перспективе исключать не один, а несколько столбцов. Теперь нам нужно получить результат в виде строки символов, представляющей собой разделенный запятыми перечень столбцов. Для этого нам уже придется использовать нестандартные средства.

SQL Server

В частности, для SQL Server мы можем это сделать при помощи конструкции FOR XML PATH, заменив попутно с помощью функции REPLACE пробел между именами столбцов на запятые:

SELECT REPLACE(
(SELECT COLUMN_NAME AS 'data()'
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Laptop' AND
 COLUMN_NAME NOT IN ('code')
ORDER BY ORDINAL_POSITION
FOR XML PATH(''))
,' ',', ');

В принципе, мы в состоянии сформировать требуемый оператор целиком с тем, чтобы его можно было динамически использовать в коде приложения:

SELECT 'SELECT ' +REPLACE(
(SELECT COLUMN_NAME AS 'data()'
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Laptop' AND
 COLUMN_NAME NOT IN ('code')
ORDER BY ORDINAL_POSITION
FOR XML PATH(''))
,' ',', ') + ' FROM Laptop';

MySQL

В случае MySQL мы можем воспользоваться специальной агрегатной функцией GROUP_CONCAT:

SELECT GROUP_CONCAT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'computers' AND
 TABLE_NAME='Laptop' AND
    COLUMN_NAME NOT IN ('code')
ORDER BY ORDINAL_POSITION;

Следует отметить, что в MySQL информационная схема относится не к отдельной базе данных, а ко всему серверу. Поэтому на тот случай, если в разных базах сервера имеются таблицы с одинаковыми именами, в условия отбора нужно добавить предикат с указанием схемы (базы данных): TABLE_SCHEMA='computers'.

Конкатенация в MySQL выполняется с помощью функции CONCAT. В итоге окончательное решение нашей задачи можно написать так:

SELECT CONCAT('SELECT ',
(SELECT GROUP_CONCAT(COLUMN_NAME)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA='computers' AND
  TABLE_NAME='Laptop' AND
    COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
), ' FROM Laptop');

PostgreSQL

В PostgreSQL задачу представления значений столбца в виде текстового списка можно решить при помощи двух встроенных функций: ARRAY и ARRAY_TO_STRING. Первая из них преобразует выборку в массив значений, а вторая преобразует массив в список. При этом вторым параметром функции ARRAY_TO_STRING задается символ, который будет являться разделителем элементов списка. Теперь решение нашей задачи можно записать в виде:

SELECT 'SELECT ' ||
ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME='laptop' AND
     COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
), ', ') || ' FROM Laptop';

Заметим, что для конкатенации строк в PostgreSQL используется стандартный оператор "||", и отметим необходимость явного приведения типа данных столбца COLUMN_NAME (information_schema.sql_identifier) к типу CHAR/VARCHAR.

Объединить данные в двух столбцах,sql пример,команда

Как объединить данные из двух столбцов в один без использования UNION и JOIN?

Полная статья по ссылке: http://exercises-on-sql.blogspot.com/2017/02/union-join.html

Такие вопросы с завидной регулярностью появляются на страницах различных форумов. К слову сказать, для меня до сих пор остается загадкой, почему при этом ставится дополнительное условие не использовать UNION и/или JOIN. Могу лишь предположить, что это вопросы, которые задают на собеседовании при приеме на работу.

Лирическое отступление. Догадываюсь, как ответил бы на этот вопрос Джо Селко: налицо ошибка проектирования, состоящая в том, что один атрибут расщеплен на два. Однако оставим в стороне вопросы проектирования и перейдем к решению этой задачи.

Создадим тестовую таблицу и добавим в нее немного данных:

CREATE TABLE T (
col1 INT
, col2 INT
)
GO
INSERT INTO T
SELECT 1, 1
UNION ALL SELECT 1, 3
UNION ALL SELECT NULL, NULL
UNION ALL SELECT NULL, 2
GO

Итак, имеется таблица T, которая содержит два столбца с данными одного типа:

SELECT col1, col2
FROM T

col1     col2
 1        1
 1        3
NULL     NULL
NULL      2

Требуется получить следующий результат:

col 
1
1
NULL
NULL
1
3
NULL
2

Мне известны три способа, реализуемых стандартными средствами интерактивного языка SQL.

1. UNION ALL

Очевидное решение, не требующее комментариев. Заметим лишь, что UNION не подходит для решения этой задачи, т.к. устраняет дубликаты.

SELECT col1 col FROM T
UNION ALL
SELECT col2 FROM T

2. FULL JOIN

Чтобы не потерять дубликаты, находящиеся в разных столбцах, выполним полное соединение (FULL JOIN) по заведомо ложному предикату, скажем, 1 = 2:

SELECT T.col1,T1.col2
FROM T FULL JOIN T AS T1 ON 1=2

Результат:

col1  col2
  1   NULL
  1   NULL
NULL  NULL
NULL  NULL
NULL   1
NULL   3
NULL  NULL
NULL   2

Далее используем функцию COALESCE, которая даст нам все, что нужно:

SELECT COALESCE(T.col1,T1.col2) col
FROM T FULL JOIN T AS T1 ON 1=2

3. UNPIVOT

Конструкции PIVOT и UNPIVOT появились в последних версиях стандарта SQL и были реализованы SQL Server, начиная с

sql задачи пример ru

sql задачи пример ru

Задачи и решения по етим запросам 120штук на сайте:
https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

найти таблицу, из которой получено не менее 10 орудий.    390    608
найдите производителя, выпускающего пк, но не пк-блокноты.    310    449
найдите производителей принтеров. вывести: производитель    287    520
Поиск продуктов (любого типа) производителя b (латинская буква).    240    531
найдите размеры жестких дисков, встречдающих у двух и более шт. вывести: HD    182    386
для каждого жесткого диска не менее 10 гбайт, найти скорость таких пк-блокнотов. вывод: производитель, скорость.    149    633
найдите модели пк-блокнотов, скорость которых меньше скорости любого из пк. вывести: тип, модель, скорость    135    206
найдите производителей пк с процессором не менее 450 мгц. вывести: производитель    129    219
найдите производителей самых дешевых цветных принтеров. вывести: производитель, цена    127    370
найдите модели принтеров. вывести: модель, цена    126    170
Найти номер модели, скорость и размер жесткого диска, 12x или 24x CD и цена менее 600 дол.    125    176
список моделей любого типа;    122    299
в предположении, что приход и расход денег на каждом приеме фиксируются не чаще одного раза в день [т.е. первичный ключ (пункт, дата), написать запрос с выходными данными (пункт, дата, приход, расход). использовать таблицу доход_о и результат_о.    111    279
используя таблицу продукта, определите количество производителей, выпускающих по одной модели.    110    702
компьютер, ноутбук или принтер, чтобы определить максимальную цену на его продукцию. для этого производителя указывается значение null, то есть для нулевого значения, иначе максимальная цена.    110    399
средний размер диска и принтеры. вывести: производитель, средний размер hd.    108    211
найдите пары моделей ПК, имеют одинаковую скорость и оперативную память. то есть (i, j), но не (j, i), порядок вывода: модель с большим номером, модель с меньшим номером, скорость и оперативная память.    102    454
найдите корабли, `сохранившиеся для будущих сражений`; т.е. Они участвовали в другом, произошедшем позже.    97    236
Найти только корабли в базе данных.    97    194
sql упражнения    96    645
упражнения по sql    95    331
найти номер модели, скорость и размер жесткого диска для всех пк стоимостью менее 500 дол. Вывести: модель, скорость и HD    94    283
по вашемуингтонскому международному договору от начала 1922 г. запрещено строить линейные корабли водоизмещением более 35 тыс.тонн. указать корабли, нарушившие этот договор. вывести названия кораблей.    91    152
для каждого корабля, участвующего в сражении при гвадалканале (гуадалканал), вы должны перевести название, водоизмещение и число орудия.    86    147
не менее 750 мгц, так и не менее 750 мгц. вывести: производитель    84    158
найдите среднюю цену пк и пк-блокнотов, выпущенных в свет (латинская буква). вывести: одна общая средняя цена.    82    361
sql задачи    75    950
задача по sql    74    737
для ноутбуков, средний размер экрана выпускаемых им пк-блокнотов. вывести: производитель, средний размер экрана.    66    93
найти производителей принтеров, которые получают максимальный объем оперативной памяти и имеют самый быстрый процессор среди всех производителей, имеют доступный объем оперативной памяти. вывести: производитель    63    396
перечисленные названия головных кораблей, имеющиеся в базе данных.    60    107
одна из характеристик корабля - половина куба. с точностью до двух десятичных знаков определите среднее значение mw для кораблей каждой страны.    59    156
найти номер модели, объем памяти и размеры экрана пк-блокнотов, цена которых превышает 1000 дол.    58    76
в таблице найти модель, которая состоит только из букв латинского алфавита (az, без учета регистра). вывод: номер модели, тип модели.    56    189
найдите все записи в таблице принтер для цветных принтеров.    55    79
упражнения sql    51    264
найдите производителей, выпускающих по меньшей мере три различных модели пк. вывести: производитель, число моделей пк.    51    243
найдите среднюю скорость пк, выпущенных станком а.    51    83
определить страны, которые потеряли в боях.    49    404
для каждой группы блокнот с одинаковым номером модели добавить запись в таблицу pc с указанием требований: код: минимальный код блокнота в группе +20; модель: номер модели блокнота +1000; скорость: максимальная скорость блокнота в группе; ram: максимальный объем ram блокнота в группе * 2; HD: максимальный объем HD блокнота в группе * 2; cd: значение по умолчанию; цена: максимальная цена блокнота в группе, уменьшенная в 1,5 раза. замечание. считать номер модели числом.    45    82
указать сражения, которые произошли в годы.    43    142
найти максимальную цену вывести: производитель, максимальная цена.    43    62
найдите названия кораблей с орудиями калибра 16 дюймов.    42    93
Все модели выпускаются в единственном экземпляре. вывести: производитель, тип    39    259
sql упражнения онлайн    39    156
найдите классных кораблей, которые были потоплен в битве.    36    81
указать корабли, потопленные в сражениях в северной атлантике (северная атлантика). вывод: корабль.    35    50
задания по sql    32    187
Все, что нужно для этой цели. вывод: производитель    32    62
удалить все блокноты, выпускаемые производителями, которые не выпускают принтеры.

SQL задачи ПК, решения,ответы тут

Задачи и решения по sql 120 штук sql ex на сайте :



ПОСМОТРЕТЬ РЕШЕНИЕ - https://exercises-on-sql.blogspot.com/2017/02/select-sql.html


Задание: 11 (Serge I: 2002-11-02)
Найдите среднюю скорость ПК.


Задание: 12 (Serge I: 2002-11-02)
Найдите среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.


Задание: 13 (Serge I: 2002-11-02)
Найдите среднюю скорость ПК, выпущенных производителем A.


Задание: 14 (Serge I: 2012-04-20)
Найти производителей, которые выпускают более одной модели, при этом все выпускаемые производителем модели являются продуктами одного типа. Вывести: maker, type



Задание: 15 (Serge I: 2003-02-03)
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD


Задание: 16 (Serge I: 2003-02-03)
Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером,
модель с меньшим номером, скорость и RAM.


Задание: 17 (Serge I: 2003-02-03)
Найдите модели ПК-блокнотов, скорость которых меньше скорости любого из ПК.
Вывести: type, model, speed


Задание: 18 (Serge I: 2003-02-03)
Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price


Задание: 19 (Serge I: 2003-02-13)
Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ПК-блокнотов.
Вывести: maker, средний размер экрана.


Задание: 20 (Serge I: 2003-02-13)
Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей ПК.

Ответы sql ex,задачи,упражнения,решения,подзапросы

Задачи и решения по sql 120 штук sql ex на сайте :



ПОСМОТРЕТЬ РЕШЕНИЕ - https://exercises-on-sql.blogspot.com/2017/02/select-sql.html

Задание: 1 (Serge I: 2002-09-30)
Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd



Задание: 2 (Serge I: 2002-09-21)
Найдите производителей принтеров. Вывести: maker



Задание: 3 (Serge I: 2002-09-30)
Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.


Задание: 4 (Serge I: 2002-09-21)
Найдите все записи таблицы Printer для цветных принтеров.


Задание: 5 (Serge I: 2002-09-30)
Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и цену менее 600 дол.


Задание: 6 (Serge I: 2002-10-28)
Для каждого производителя, выпускающего ПК-блокноты c объёмом жесткого диска не менее 10 Гбайт, найти скорости таких ПК-блокнотов. Вывод: производитель, скорость.


Задание: 7 (Serge I: 2002-11-02)
Найдите номера моделей и цены всех продуктов (любого типа), выпущенных производителем B (латинская буква).



Задание: 8 (Serge I: 2003-02-03)
Найдите производителя, выпускающего ПК, но не ПК-блокноты.


Задание: 9 (Serge I: 2002-11-02)
Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker

Задание: 10 (Serge I: 2002-09-23)
Найдите модели принтеров, имеющих самую высокую цену. Вывести: model, price

DML задачи и ответы,примеры,операторы ,операции,команды

Остальные задачи по ссылке:
http://exercises-on-sql.blogspot.com/2017/02/dml.html

1-10, TBC

Задание: 1 (Serge I: 2004-09-08)

Добавить в таблицу PC следующую модель:
code: 20
model: 2111
speed: 950
ram: 512
hd: 60
cd: 52x
price: 1100

INSERT INTO pc (code, model, speed, ram, hd,cd,price)
VALUES(20,2111,950,512,60, '52x', 1100)