Please Assist with questions with attached databases. thank you. 

1)Firstly, tell me what time it is right now on the system

2)Format it to be in mm/dd/yyyy format

3)Format it to be in month nn yyyy format (today is February 3, 2012)

4)What does "coalesce" do?

5)Use it to get a list of distinct cities which have publishers.

6)From the Cars database, concatenate the year, make, and model of the vehicles with their cost. Lable the columns "usefully" and format the cost to look like money. So I would expect the results to look like "2005 VW Jetta$18,000"

7)From the Cars database, find me all cars that cost below the average amount of the cars on the list

8)Find out how much we paid in royalties for the various titles in our Books database. I want the title and how much we paid for it for royalties.

9)Looking in the authors table, concatenate the author's name and a string telling me which state they live in. I would expect something that looks like "Timmy Johnson lives in Washington." (think case statements to convert from the two-digit state to a physical name)

10)For a couple easy ones: Show me the results of the multiplication of 12 and 3.14

11)Show me the results of adding 2 days to today's date

12)How many authors do we have?

13)How many books have they each written?

14)What's the most we've paid to an author for a book?

15)Define the "standard look" of a query. Explain the parts. (I would expect "select" to be first for example, followed by what it does...)

16)Display the title, the price, and a sale price if we reduced all the books in our inventory by 15% Define the new column as "Sales Price"

17)Better, do the same thing but only for the children's books

18)What's the cheapest vehicle in the Cars database?

19)What's the cost per cc for each vehicle? Rack them in order

20)Which vehicle is cheapest for the Miles Per Gallon? (meaning which vehicle gets the best mileage) Rank them in reverse order (so that the best is on the top)

 

DROP TABLE authors;CREATE TABLE authors(au_idCHAR(3)NOT NULL,au_fname VARCHAR(15) NOT NULL,au_lname VARCHAR(15) NOT NULL,phoneVARCHAR(12),addressVARCHAR(20),cityVARCHAR(15),stateCHAR(2),zipCHAR(5),CONSTRAINT pk_authors PRIMARY KEY (au_id));INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205St','Bronx','NY','10468');INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922Baseline 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 HoratioSt','New York','NY','10014');INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 SerraMall','Palo Alto','CA','94305');INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442Main St','Sarasota','FL','34236');INSERT INTO authors VALUES('A08','Chris','Smith','719-925-0752','1442 MainSt','Cos','Co','80907');DROP TABLE publishers;CREATE TABLE publishers(pub_idCHAR(3)NOT NULL,pub_name VARCHAR(20) NOT NULL,cityVARCHAR(15) NOT NULL,stateCHAR(2),countryVARCHAR(15) NOT NULL,CONSTRAINT pk_publishers PRIMARY KEY (pub_id));INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');INSERT INTO publishers VALUES('P02','Core Dump Books','SanFrancisco','CA','USA');INSERT INTO publishers VALUES('P03','SchadenfreudePress','Hamburg',NULL,'Germany');INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');DROP TABLE titles;CREATE TABLE titles(title_idCHAR(3)NOT NULL,title_name VARCHAR(40)NOT NULL,typeVARCHAR(10),pub_idCHAR(3)NOT NULL,pagesINTEGER,priceDECIMAL(5,2),salesINTEGER,pubdateDATE,contractSMALLINTNOT NULL,CONSTRAINT pk_titles PRIMARY KEY (title_id));INSERT INTO titles VALUES('T01','1977!','history','P01',107,21.99,566,'2000-08-01',1);

DROP TABLE cars;CREATE TABLE cars(car_idCHAR(3)NOT NULL,yearint NOT NULL,makeVARCHAR(15) NOT NULL,modelVARCHAR(12),mpgcityint,mgphwyint,savingsint,costint,enginecc int,seatsint,greenscore int,rankint,PRIMARY KEY (car_id));INSERT INTO cars VALUES('A01',2011,'Smart','ForTwoCoupe',33,41,1487,18000,NULL,2,53,1);INSERT INTO carsVALUES('A02',2011,'Toyota','Prius',51,48,1121,21000,1800,5,52,2);INSERT INTO cars VALUES('A03',2011,'Honda','CivicHybrid',40,43,1320,19000,1900,5,51,3);INSERT INTO carsVALUES('A04',2011,'Honda','Insight',40,43,1330,19500,1500,5,50,4);INSERT INTO cars VALUES('A05',2011,'Ford','FiestaSFE',29,43,1601,15500,1986,5,50,5);INSERT INTO cars VALUES('A06',2011,'Chevrolet','CruzeEco',28,42,1599,16700,1780,5,49,6);INSERT INTO carsVALUES('A07',2011,'Hyundai','Elantra',29,40,1605,14900,2503,5,49,7);INSERT INTO carsVALUES('A08',2011,'Mini','Cooper',29,37,1657,16900,1800,5,49,8);INSERT INTO carsVALUES('A09',2011,'Toyota','Yaris',29,36,1702,19900,2237,5,49,9);INSERT INTO cars VALUES('A10',2011,'Mazda','2',29,35,1709,18900,2535,5,48,10);INSERT INTO carsVALUES('A11',2011,'Chevrolet','Impala',19,31,NULL,22900,3757,5,35,11);INSERT INTO carsVALUES('A12',2011,'Ford','Mustang',19,31,NULL,21900,3727,5,35,12);