Question

In: Computer Science

Aggregation 1. List the following about history books: number of history books, the minimum price, maximum...

Aggregation

1. List the following about history books: number of history books, the minimum price, maximum price, and average sales. The format of output is: "Number" "Min Price" "Max Price" "Average Sale"

2. List the number of books and the average number of pages published by pub_id 01.

3. For each book type, list the the number of books and the average price. Sort the results by the number of books

Functions

4. List the name(s) of the publisher(s) that published the book with the shortest title name.

5. For each author, list the author id, area code, and the phone number without the area code.

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Authors
  (
  au_id    CHAR(3)     NOT NULL,
  au_fname VARCHAR(15) NOT NULL,
  au_lname VARCHAR(15) NOT NULL,
  phone    VARCHAR(12)         ,
  address  VARCHAR(20)         ,
  city     VARCHAR(15)         ,
  state    CHAR(2)             ,
  zip      CHAR(5)             ,
  CONSTRAINT pk_Authors PRIMARY KEY (au_id)
  );

CREATE TABLE Publishers
  (
  pub_id   CHAR(3)     NOT NULL,
  pub_name VARCHAR(20) NOT NULL,
  city     VARCHAR(15) NOT NULL,
  state    CHAR(2)             ,
  country  VARCHAR(15) NOT NULL,
  CONSTRAINT pk_Publishers PRIMARY KEY (pub_id)
  );

CREATE TABLE Titles
  (
  title_id   CHAR(3)      NOT NULL,
  title_name VARCHAR(40)  NOT NULL,
  type       VARCHAR(10)          ,
  pub_id     CHAR(3)      NOT NULL,
  pages      INTEGER              ,
  price      DECIMAL(5,2)         ,
  sales      INTEGER              ,
  pubdate    DATE                 ,
  contract   SMALLINT     NOT NULL,
  CONSTRAINT pk_Titles PRIMARY KEY (title_id)
  );

CREATE TABLE Title_Authors
  (
  title_id      CHAR(3)      NOT NULL,
  au_id         CHAR(3)      NOT NULL,
  au_order      SMALLINT     NOT NULL,
  royalty_share DECIMAL(5,2) NOT NULL,
  CONSTRAINT pk_Title_Authors PRIMARY KEY (title_id, au_id)
  );

CREATE TABLE Royalties
  (
  title_id     CHAR(3)      NOT NULL,
  advance      DECIMAL(9,2)         ,
  royalty_rate DECIMAL(5,2)         ,
  CONSTRAINT pk_Royalties PRIMARY KEY (title_id)
  );

INSERT INTO Authors VALUES('A01','Sarah','Buchman','718-496-7223',
  '75 West 205 St','Bronx','NY','10468');
INSERT INTO Authors VALUES('A02','Wendy','Heydemark','303-986-7020',
  '2922 Baseline Rd','Boulder','CO','80303');
INSERT INTO Authors VALUES('A03','Hallie','Hull','415-549-4278',
  '3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO Authors VALUES('A04','Klee','Hull','415-549-4278',
  '3800 Waldo Ave, #14F','San Francisco','CA','94123');
INSERT INTO Authors VALUES('A05','Christian','Kells','212-771-4680',
  '114 Horatio St','New York','NY','10014');
INSERT INTO Authors VALUES('A06','','Kellsey','650-836-7128',
  '390 Serra Mall','Palo Alto','CA','94305');
INSERT INTO Authors VALUES('A07','Paddy','O''Furniture','941-925-0752',
  '1442 Main St','Sarasota','FL','34236');

INSERT INTO Publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
INSERT INTO Publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');
INSERT INTO Publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
INSERT INTO Publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');
INSERT INTO Publishers VALUES('P05','PTR Press','Los Angeles','CA','USA');



INSERT INTO Titles VALUES('T01','1977!','history','P01',
  107,21.99,566,'2000-08-01',1);
INSERT INTO Titles VALUES('T02','200 Years of German Humor','history','P03',
  14,19.95,9566,'1998-04-01',1);
INSERT INTO Titles VALUES('T03','Ask Your System Administrator','computer','P02',
  1226,39.95,25667,'2000-09-01',1);
INSERT INTO Titles VALUES('T04','But I Did It Unconsciously','psychology','P04',
  510,12.99,13001,'1999-05-31',1);
INSERT INTO Titles VALUES('T05','Exchange of Platitudes','psychology','P04',
  201,6.95,201440,'2001-01-01',1);
INSERT INTO Titles VALUES('T06','How About Never?','biography','P01',
  473,19.95,11320,'2000-07-31',1);
INSERT INTO Titles VALUES('T07','I Blame My Mother','biography','P03',
  333,23.95,1500200,'1999-10-01',1);
INSERT INTO Titles VALUES('T08','Just Wait Until After School','children','P04',
  86,10.00,4095,'2001-06-01',1);
INSERT INTO Titles VALUES('T09','Kiss My Boo-Boo','children','P04',
  22,13.95,5000,'2002-05-31',1);
INSERT INTO Titles VALUES('T10','Not Without My Faberge Egg','biography','P01',
  NULL,NULL,NULL,NULL,0);
INSERT INTO Titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',
  826,7.99,94123,'2000-11-30',1);
INSERT INTO Titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',
  507,12.99,100001,'2000-08-31',1);
INSERT INTO Titles VALUES('T13','What Are The Civilian Applications?','history','P03',
  802,29.99,10467,'1999-05-31',1);

INSERT INTO Title_Authors VALUES('T01','A01',1,1.0);
INSERT INTO Title_Authors VALUES('T02','A01',1,1.0);
INSERT INTO Title_Authors VALUES('T03','A05',1,1.0);
INSERT INTO Title_Authors VALUES('T04','A03',1,0.6);
INSERT INTO Title_Authors VALUES('T04','A04',2,0.4);
INSERT INTO Title_Authors VALUES('T05','A04',1,1.0);
INSERT INTO Title_Authors VALUES('T06','A02',1,1.0);
INSERT INTO Title_Authors VALUES('T07','A02',1,0.5);
INSERT INTO Title_Authors VALUES('T07','A04',2,0.5);
INSERT INTO Title_Authors VALUES('T08','A06',1,1.0);
INSERT INTO Title_Authors VALUES('T09','A06',1,1.0);
INSERT INTO Title_Authors VALUES('T10','A02',1,1.0);
INSERT INTO Title_Authors VALUES('T11','A03',2,0.3);
INSERT INTO Title_Authors VALUES('T11','A04',3,0.3);
INSERT INTO Title_Authors VALUES('T11','A06',1,0.4);
INSERT INTO Title_Authors VALUES('T12','A02',1,1.0);
INSERT INTO Title_Authors VALUES('T13','A01',1,1.0);

INSERT INTO Royalties VALUES('T01',10000,0.05);
INSERT INTO Royalties VALUES('T02',1000,0.06);
INSERT INTO Royalties VALUES('T03',15000,0.07);
INSERT INTO Royalties VALUES('T04',20000,0.08);
INSERT INTO Royalties VALUES('T05',100000,0.09);
INSERT INTO Royalties VALUES('T06',20000,0.08);
INSERT INTO Royalties VALUES('T07',1000000,0.11);
INSERT INTO Royalties VALUES('T08',0,0.04);
INSERT INTO Royalties VALUES('T09',0,0.05);
INSERT INTO Royalties VALUES('T10',NULL,NULL);
INSERT INTO Royalties VALUES('T11',100000,0.07);
INSERT INTO Royalties VALUES('T12',50000,0.09);
INSERT INTO Royalties VALUES('T13',20000,0.06);

show tables;

6. List the author names in the form <first initial>, period, space, <last name>, e.g. K. Hull. Order the results first by last name, then by first name. Only list the authors who have both a first name and a last name in the database.

Solutions

Expert Solution

1. List the following about history books: number of history books, the minimum price, maximum price, and average sales. The format of output is: "Number" "Min Price" "Max Price" "Average Sale"

select count(title_id) 'Number',min(price) as 'Min Price',max(price) as 'Max Price',
avg(sales) as 'Average Sale'
from titles where type like 'history';

***********************************************

2. List the number of books and the average number of pages published by pub_id 01.

select count(title_id) as 'Number of Books',avg(pages) as 'Average number of pages'
from titles where pub_id like 'P01';

*************************************

3. For each book type, list the the number of books and the average price. Sort the results by the number of books

select type,count(title_id) as 'Number of Books',avg(price) as 'Average Price'
from titles group by type order by count(title_id);

*********************************************

Functions

4. List the name(s) of the publisher(s) that published the book with the shortest title name.

select pub_name from publishers where pub_id=
(Select pub_id from titles where title_id=
(select title_id from titles where length(title_name)=
(select length(title_name) from titles order by length(title_name) asc limit 1)));

******************************

5. For each author, list the author id, area code, and the phone number without the area code :

select au_id,SUBSTRING(phone, 1, 3) as areaCode,
SUBSTRING(phone, 5, length(phone)) as 'phone number without the area code'
from authors;


Related Solutions

When the government sets a ▼(maximum/ minimum) price that exceeds the equilibrium​ price, the result is...
When the government sets a ▼(maximum/ minimum) price that exceeds the equilibrium​ price, the result is permanent excess ▼ (supply/ demand). Producers will produce ▼ (less/more) and consumers buy ▼ (less/more). For a perfectly competitive​ firm, marginal revenue equals price ▼(average cost/ marginal cost/ price), and to maximize​ profit, the firm produces the quantity of output at which ▼(marginal cost/ marginal revenue/ marginal cost) equals ▼ (price/ average cost)
Provide the Java code to compute the sum, average, maximum number and minimum number if I...
Provide the Java code to compute the sum, average, maximum number and minimum number if I have a string sentence of double numbers. Assume that the length of the string sentence is not known. It can be of any length. To split a string based on the comma character use the following. String sentence = "A,B,C,D,E"; String[] stringsArray = receivedSentence.split(","); Then stringsArray is an array of five elements such that: stringsArray[0] = 'A' stringsArray[1] = 'B' stringsArray[2] = 'C' stringsArray[3]...
What is the minimum and maximum number of solutions that we can expect to see in...
What is the minimum and maximum number of solutions that we can expect to see in any given system of nonlinear equations? In your own words, what is the meaning of extraneous solutions? When solving a system of nonlinear equations, is it possible to always use the Addition Method? Explain your reasoning in complete sentences. PLEASE TYPE, DO NOT WRITE IT DOWN and Check your punctuation and proofreading.
Name and explain ONE effect of price controls, either minimum price controls (price floors) OR maximum...
Name and explain ONE effect of price controls, either minimum price controls (price floors) OR maximum price controls (price ceilings).
MIPS code to calculate The 1- minimum 2- maximum 3- mid value of three number, user...
MIPS code to calculate The 1- minimum 2- maximum 3- mid value of three number, user will enter the numbers
1- Use calculus to find the absolute maximum and minimum values of the following functions on...
1- Use calculus to find the absolute maximum and minimum values of the following functions on the given intervals. Give your answers exactly and show supporting work. f(x) = (7x − 1)e^−2x on [0, 1] f(x) = x^4 − 2x^2 + 4 on [0, 2] f(x) = x^3 − 2x^2 + x + 1 on [0, 1]
for the following questions find : Vertex form, identify the vertex , concality and maximum/minimum. 1....
for the following questions find : Vertex form, identify the vertex , concality and maximum/minimum. 1. Q(x)=-3x^2+12x 2. K (x) = x^2 + 7x - 10 3. f(x)= 2x^2 + 4x +6
These questions are about Community nursing. Sentences should be minimum of 4 sentences with maximum of...
These questions are about Community nursing. Sentences should be minimum of 4 sentences with maximum of 10 sentences. 1.Distinguish between the levels of prevention. 2. Provide two (2) examples of each type of prevention. 3. Identify the circumstances and settings in which each level is indicated.  Explain your answers.
Using Selection Sort on an any array of size 7, what will be the minimum/maximum number...
Using Selection Sort on an any array of size 7, what will be the minimum/maximum number of comparisons and number of exchanges? 1 for i = 1 to A.length-1 2 for j = i+1 to A.length 3 if A[j] < A[i] 4 exchange A[i] with A[j]
You recently acquired books on three different subjects in the following quantities: 6 history books, 5...
You recently acquired books on three different subjects in the following quantities: 6 history books, 5 music books, and 4 photography books. (a) In how many ways can you arrange the books on a shelf? (b) In how many ways can you arrange the books on a shelf so they can be grouped by subject? (c) In how many ways can they choose 6 books with 2 books per subject? (d) In how many ways can they choose 6 books...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT