Question

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

Database Management

Answer 1

USE DATABASE BookDB;

CREATE TABLE `book` (
`ID` int(11) NOT NULL,
`Title` varchar(50) NOT NULL,
`Author` varchar(50) NOT NULL,
`Year` year(4) NOT NULL,
`Price` double NOT NULL
);

INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('1', 'C', 'Richie', '1955', '10');
INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('2', 'JAVA Script', 'Karein', '1958', '20');
INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('4', 'Java', 'Schwartz', '1959', '10');
INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('5', 'C++', 'Ross', '1959', '20');
INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('6', 'prolog', 'Maria', '1972', '10');
INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('7', 'Perl', 'Wall', '1987', '20');

SELECT COUNT(*) FROM `book`;

SELECT `Title` FROM `book` WHERE `Year` IN (SELECT DISTINCT `Year` FROM `book`);

SELECT * FROM `book` ORDER BY `Year` DESC;

SELECT `Title` FROM `book` WHERE `Year` >= 1970;

SELECT `Title` FROM `book` WHERE `Year` BETWEEN 1958 1972;

SELECT `Title` FROM `book` WHERE `Title` REGEXP 'JAVA';

SELECT AVG(Price) FROM `book`;

UPDATE `book` SET `Title` = 'Visual C++' WHERE `ID` = 5;

INSERT INTO `book` (`ID`, `Title`, `Author`, `Year`, `Price`) VALUES ('8', 'MySql', 'Jackson', '1995', '30');