Question

ID Title 1 Author Richie Karen с JAVA Script Java Year 1955 1958 1959 Price 10 20 10 2 4 5 6 C++ prolog Peri Schwartz Ross Maria 1959 1972 20 10 7 Wall 1987 20 The above relation has formation about different books. The table information is already saved as txt format in Moodle under the name Quiz2Data.txt. Using MySql on your computer do: Create a database • Create a table with name book, then load Quiz2Data.txt (check file DataLoad in Moodle for the steps to load the file) Then answer the following questions: 1. Write the definition you used to create the table 2. Provide a screen shot of all the data in the table (Load the screen shot into the Motinn DO

Database Management

Answer 1

abc

create database and use command

• create database abc;

• use abc

1. To create a table definition

create table book(

ID INT NOT NULL,
Title VARCHAR(100) NOT NULL,
Author VARCHAR(100) NOT NULL,
Year INT,
Price INT);

2. insert elements and showing the table

INSERT INTO book(ID,Title,Author,Year,Price)VALUES(1,'C','Richie',1955,10),

(2,'JAVA Script','Karen',1958,10),

(4,'Java','Schwartz',1959,10),

(5,'C++','Ross',1959,20),

(6,'prolog','Maria',1972,10),

(7,'Perl','Wall',1987,20);

To show the table command:

• select *from book;

3. list book titles and years only

• select Title,Year from book;

​​​​​​​​​​​​​​

4) how many books are in the table

• select count(*) from book;

​​​​​​​

5) list the book with distinct year .

let the distinct year be 1987 ,(you can provide any year in the table)

command:select *from book where Year=1987;

6) order the book information by year in a descending order

• select *from book order by Year desc;

​​​​​​​

7) list all the books published after year 1970

• select *from book where year>1970;

​​​​​​​