SQL функция FLOOR

Question

вопрос такой ...создав первые три таблицы (ниже) надо создать и заполнить 4-ю использовав BookID из третьей и в поле SalesTotal для SaleTotal использоватьFLOOR(RAND()*(25-10+1))+10; НЕ ПОНИМАЮ КАК написать insert into выражение это мои грустные потуги написать это выражение, надо что бы цифры от функции были не в новом ряду, уникальный и напротив bookid, пока только или в новом ряду или в каждом ряду, но одно и то же число

CREATE TABLE BookSales2 (
    ID int not null IDENTITY (1,1) PRIMARY KEY,
    BookID INT,
    SaleTotal INT
);

insert into BookSales2 (bookid, saletotal) select ID, (FLOOR(RAND()*(25-10+1))+10) from books

как то надо сделать что бы выбрались все bookID из таблицы 3 при чем есть книга у которой 2 автора и есть 2 автора которые написали по 2 книги, а есть автор который не имеет книг

'Dickens' wrote 'A Tale of Two Cities'
'Tolkien' wrote 'The Lord of the Rings' AND 'The Hobbit'
'Lewis' wrote 'The Lion, The Witch and the Wardrobe'
'Rowling' wrote 'Harry Potter and the Half Blood Prince' AND 'Harry Potter and the Goblet of Fire'
'Chaucer' wrote 'The Canterbury Tales'
BOTH 'Berenstain' wrote ('Berenstain Bears series')

ПОМОГИТЕ ПОЖАЛУЙСТА

вот это сделано для создания первых трех таблиц

    CREATE TABLE Authors (
   ID int not null IDENTITY(1,1) PRIMARY KEY,
    FirstName varchar(25),
    LastName varchar(25)
);
insert into authors (firstname, Lastname) values ('Charles', 'Dickens')
insert into authors (firstname, Lastname) values ('J.R.R', 'Tolkien')
insert into authors (firstname, Lastname) values ('C.S.','Lewis')
insert into authors (firstname, Lastname) values ('J.K.', 'Rowling')
insert into authors (firstname, Lastname) values ('R.L.', 'Stein')
insert into authors (firstname, Lastname) values ('Geoffery', 'Chaucer')
insert into authors (firstname, Lastname) values ('Stan', 'Berenstain')
insert into authors (firstname, Lastname) values ('Jan', 'Berenstain')
;
SELECT * FROM authors;

результат

ID  FirstName  LastName
1   Charles   Dickens
2  J.R.R  Tolkien
3  C.S.  Lewis
4  J.K.  Rowling
5  R.L.  Stein
6  Geoffery  Chaucer
7  Stan  Berenstain
8  Jan  Berenstain

    CREATE TABLE BookTitles (
     ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(50)
);

Insert into booktitles ( name) values ('A Tale of Two Cities')
Insert into booktitles ( name) values ('The Lord of the Rings')
Insert into booktitles ( name) values ('The Lion, The Witch and the Wardrobe')
Insert into booktitles ( name) values ('Harry Potter and the Half Blood Prince')
Insert into booktitles ( name) values ('Harry Potter and the Goblet of Fire')
Insert into booktitles ( name) values ('The Hobbit')
Insert into booktitles ( name) values ('The Canterbury Tales')
Insert into booktitles ( name) values ('Berenstain Bears series');

SELECT * FROM BookTitles;

результат:

ID     Name
1A Tale of Two Cities
2The Lord of the Rings
3The Lion, The Witch and the Wardrobe
4Harry Potter and the Half Blood Prince
5Harry Potter and the Goblet of Fire
6The Hobbit
7The Canterbury Tales
8Berenstain Bears series

3.

    CREATE TABLE Books (ID int not null IDENTITY(1,1) PRIMARY KEY, BookTitleID INT, AuthorID INT);
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='A Tale 
of Two Cities'), (SELECT ID FROM authors where lastname = 'Dickens')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='The Lord of the Rings'), (SELECT ID FROM authors where lastname = 'Tolkien')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='The Hobbit'), (SELECT ID FROM authors where lastname = 'Tolkien')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='The Lion, The Witch and the Wardrobe'), (SELECT ID FROM authors where lastname = 'Lewis')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='Harry Potter and the Half Blood Prince'), (SELECT ID FROM authors where lastname = 'Rowling')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='Harry Potter and the Goblet of Fire'), (SELECT ID FROM authors where lastname = 'Rowling')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='The Canterbury Tales'), (SELECT ID FROM authors where lastname = 'Chaucer')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='Berenstain Bears series'), (SELECT ID FROM authors where firstname = 'Stan')) 
INSERT INTO books (Booktitleid, AuthorID) values ((select ID from booktitles where name ='Berenstain Bears series'), (SELECT ID FROM authors where firstname = 'Jan'));


SELECT * FROM Books;

результат

IDBook TitleID AuthorID
1        1        1
2        2        2
3        6        2
4        3        3
5        4        4
6        5        4
7        7        6
8        8        7
9        8        8

    CREATE TABLE BookSales (
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    BookID INT,
    SaleTotal INT
);

БЛОГ НА HUSL

SQL функция FLOOR

Ответы (0 шт):