Question

ID Title Year Price 1 Author Richie Karen 1955 1958 10 20 2 4 C JAVA Script Java C++ prolog 1959 10 5 Schwartz Ross Maria 1959 1972 20 10 6 7 Perl Wall 1987 20 The above relation has information 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)

help create a table

data.txt

1,C,Richie,1955
2,JAVA Script,Karen,1958
4,Java,Schwartz,1959
5,C++,Ross,1959
6,prolog,Maria,1972
7,Perl,Wall,1987

Answer 1

Answer

I am using mysql and linux operating system for this question. You gave me a data.txt file. and also i add price in that data.txt file(please note it).

Here is your answer

1) For creating table we can use the following query. CREATE TABLE command is used to create table in data base

create table book(id int(20) primary key,title varchar(20),author varchar(20),year YEAR,price int(20));

This is the definition for the creating table

2) To load data.txt file in this table we can use LOAD DATA INFILE command. We can use the following query for insert the data into table

 LOAD DATA INFILE '/root/data.txt'  INTO TABLE book  FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

Here /root/data.txt is my file path .The field of the data file is terminated by a comma so it indicated by FIELD TERMINATED BY ',' and enclosed by double quotation marks specified by ENCLOSED BY '" ‘. Each line of the data.txt file is terminated by a newline character indicated by LINES TERMINATED BY '\n' .

*****Here i added heading in data.txt file.

Note: That is why IGNORE 1 ROWS is used in query, first row will treated as heading.

Screenshot attached below.

create table

data.txt

Inserting data

View the data

3) listing the book title and years we can use the following query

select title,year from book;

4) Here we can use COUNT() for counting the number of books. Query given below

select count(id) from book;

5) WE can use DISTINCT in select statment. The follwoing query can be used. Actually it is used to avoid duplicancy

select distinct(year),title from book;

6) We can use the follwoing query, To order the data in decending/asending order we can use ORDER BY cluase with DESC/AESC

select * from book order by year desc;

Any doubts please comment. You can solve remaing part. Please upvote too...

Thanks in advance