This is in

SQL Server Management Studio 2018

CS246 Lab 3: Joins and Sub-queries

Objectives

The objectives of Lab 3 are to:

• Create and execute queries involving CROSS, NATURAL, INNER, OUTER, and SELF joins
• Create and execute queries involving UNION, INTERSECT, and EXCEPT set operators
• Create and execute queries using sub-queries with the IN, ALL, ANY, and EXISTS operators

Requirements

1. For each book display its title and the advances and royalty rates paid on the book. Order the results by title in ascending order. Click the messages tab. How many records were retrieved?
2. Modify query one to create a left (titles) outer join query. Click the message tab. How many records were retrieved? Why are more records returned by query two than by query one?
3. Modify query two to display only those titles whose publishers are located in the United States.
4. Display the last and first name of all authors along with the title and price of all the books they have published. Order the results by the author s last name in ascending order, authors first name in ascending order, and book title in ascending order.
5. Display the publication dates and titles of all books published after the book titled How About Never?. Order the results by publication date in descending order and then by title in ascending order.
6. Display the type and total sales for each type from the titles tables. Name the columns Category and Total Sales. Display only those types whose total sales are greater than the average sales of any type. Order the results by type in ascending order. Use a sub-query.
7. Display the title and price of all books whose price is greater that the average price of all books. Order the results by title in ascending order. Use a sub-query.
8. Display the publisher name of all publishers who have published books in 1999. Order the results by publisher name in ascending order. Use a sub-query.
9. Display the title and price of all books whose price is greater than the price of any history book. Order the results by price in descending order. Use a sub-query in the WHERE clause of the outer query.
10. Display the publisher name of all publishers who have published books that contain the word The in the title and whose country is equal to the minimum value of country in the publisher table. Order the results by publisher name. Use a sub-query.
11. Display the last name and first name of all authors who are not located in the same state as any of the publishers. Order the results by last name in ascending order and then by first name in ascending order.
12. Display the authors name (concatenate the first name to the last name separated by a space) and the names (concatenate the first name to the last name separated by a space) of all authors who live in the same state. Write the query so that no information is duplicated in the report. Name the columns Author and Authors in the Same State. Order the results by column one in ascending order and then by column two in ascending order.
13. Display the names (first concatenated to last separated by a space) of all authors who have not published a book. Use the EXISTS operator. Name the column Author Name Order the result by Author Name in ascending order.
14. Display the names (first concatenated to last separated by a space) of all authors who appear in both the authors and title_authors table. Use the IN operator. Name the column Author Name. Order the results by Author Name.
15. Display the category, title, and sales of books that have sales greater than or equal to the average sales of books of its type. Use a correlated sub-query. Order the results by category and title.
16. Display the titles of all books that where published before any book published by Core Dump Books. Order the result by title in ascending order.
17. Display the names (first concatenated to last separated by a space) of all authors who live in the same state as Klee Hull. Name the column Author. Exclude Klee Hull. Use a sub-query. Order the results by Author.
18. Display the names of all publishers who have published books for authors who live in CA. Order the result by publisher name in ascending order. Eliminate duplicate rows in the results.
19. Display the names, last and then first, of all authors who have published a book. Use the EXISTS operator. Order the results by last name in ascending order and then by first name in ascending order.
20. Display all columns in the authors and publishers table. Cross join the two tables. Order the results by author last name in ascending order and them by publisher name in ascending order.
21. Display the title of each book whose revenue (price * sales) is more than 10 times greater than its advance. Order the result by title in ascending order.
22. Display the author last name and publisher name of all authors and publishers who live in the same city. Display all authors even if no publisher lives in the same city. Order the results by author last name in ascending order and then by publisher name in ascending order.
23. Display the author last and first name and the latest date he published a book. Order the results by author last name in ascending order.
24. Display the TitleID title, sales, and running sum of all former book sales. Order the results by TitleID in ascending order.
25. Display the greatest number of titles written (or co-written) by any author.
26. Display the last name of all authors who have participated in writing at least one biography. Order the results by last name in ascending order.
27. Display the last name of all authors who are the sole author of a book. Order the results by last name in ascending order.
28. Display the last name of all authors who wrote (or co-wrote) three or more books. Order the results by last name in ascending order.
29. Display the last name of all authors who wrote (or co-wrote) a childrens book and also wrote (or co-wrote) a psychology book. Order the results by last name in ascending order.

Turn-in Requirements

1. Word document containing:

1. Cover page with your name, course number, section, lab number, and date
2. Screen print image of each query and it results from each step above.

This is the structure of the database

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);

INSERT INTO titles VALUES('T02','200 Years of GermanHumor','history','P03',14,19.95,9566,'1998-04-01',1);INSERT INTO titles VALUES('T03','Ask Your SystemAdministrator','computer','P02',1226,39.95,25667,'2000-09-01',1);INSERT INTO titles VALUES('T04','But I Did ItUnconsciously','psychology','P04',510,12.99,13001,'1999-05-31',1);INSERT INTO titles VALUES('T05','Exchange ofPlatitudes','psychology','P04',201,6.95,201440,'2001-01-01',1);INSERT INTO titles VALUES('T06','How AboutNever?','biography','P01',473,19.95,11320,'2000-07-31',1);INSERT INTO titles VALUES('T07','I Blame MyMother','biography','P03',333,23.95,1500200,'1999-10-01',1);INSERT INTO titles VALUES('T08','Just Wait Until AfterSchool','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 FabergeEgg','biography','P01',NULL,NULL,NULL,NULL,0);INSERT INTO titles VALUES('T11','Perhaps It''s a GlandularProblem','psychology','P04',826,7.99,94123,'2000-11-30',1);INSERT INTO titles VALUES('T12','Spontaneous, NotAnnoying','biography','P01',507,12.99,100001,'2000-08-31',1);INSERT INTO titles VALUES('T13','What Are The CivilianApplications?','history','P03',802,29.99,10467,'1999-05-31',1);DROP TABLE title_authors;CREATE TABLE title_authors(title_idCHAR(3)NOT NULL,au_idCHAR(3)NOT NULL,au_orderSMALLINTNOT NULL,royalty_share DECIMAL(5,2) NOT NULL,CONSTRAINT pk_title_authors PRIMARY KEY (title_id, au_id));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);DROP TABLE royalties;CREATE TABLE royalties(title_idCHAR(3)NOT NULL,advanceDECIMAL(9,2),royalty_rate DECIMAL(5,2),CONSTRAINT pk_royalties PRIMARY KEY (title_id));INSERT INTO royalties VALUES('T01',10000,0.05);INSERT INTO royalties VALUES('T02',1000,0.06)