Write sql queries

profileuga3tb
a_10.zip
Home>Computer Science homework help>Write sql queries

A 10.docx

1. Write a SQL statement to show the writer ID of anyone who written more than 10 articles.

2. Write a SQL statement to show the total number of articles published by year during the 1980’s and sort the results in descending order by year.

3. List the title and type of all international articles done by freelance writers.

4. List the article title of all unpublished articles over 2000 words (Hint: unpublished article are articles not listed in the Published table ) (Another Hint: Null).

5. List how many exposé articles have been published by each magazine; sort the results in descending order by the number of articles published.

6. Create a single phone directory list showing the name (first and last) and phone number of writers and editors in the 210 and 610 area codes; each person’s name and phone should only appear once. (Hint: UNION)

7. List by last name and first name all writers who are also editors.

8. List writers and the number of articles published. List all writers even if they have no articles.

9. List the types of articles published and how many of each type. Include types for which no articles have been published. Sort by number of articles descending.

10. List the types of articles published and the average number of words for each type. Include types for which no articles have been published.(Hint use Round to round to 2 decimals)

11. List the Magazine Name, the issue year and how many published by the magazine in the year.

12. List the length of the shortest article, the longest article and the average length of all articles.

13. List the freelance writers in California and the number of articles written by each.

image6.PNG

image7.PNG

image8.PNG

image9.PNG

image10.PNG

image11.PNG

image12.PNG

image13.png

image1.PNG

image2.PNG

image3.PNG

image4.PNG

image5.PNG

Publications.accdb

ART_ID ART_Title ART_Type ART_Length WRT_ID
1 Unleaded Fuel and Automobile Costs BUS 1835 N425
2 Foreign Aircraft Sales INT 2204 C500
3 4 Percent Unemployment Predicted POL 947 C500
4 First Postal Strike and the Economy POL 1072 K500
5 Martina Navratilova Seeks U.S. Asylum POL 849 M635
6 Casey Stengel: Manager and Humorist ITV 1326 E235
7 The Supreme Court's Business Attitude BUS 1773 Y556
8 Stock Market at Seven-Year Low FMK 798 C200
9 International Agriculture INT 1191 S500
10 The Deer Hunter EXP 664 K500
11 Business and the First Earth Day BUS 1288 N425
12 Jobs in the Seventies BUS 1409 E235
13 Willie Nelson On the Road Again INT 1083 N200
14 Genetic Engineering TEC 1625 N200
15 New York's World Trade Center BUS 658 N200
16 The New Auto Contract in Depth LAW 1727 E235
17 Artificial Heart Implants TEC 1224 K500
18 Canada's Railroads INT 1681 L130
19 Unemployment Reaches 5 Percent POL 684 W432
20 Business Trends in the Seventies BUS 1362 N200
21 $2.9 Billion Federal Deficit Is the Peak MON 665 E235
22 Chile Nationalizes Banking Industry INT 1008 Y556
23 Rolls-Royce Declares Bankruptcy FMK 1196 Y556
24 Peking Trade Embargo Ends INT 844 N200
25 90-Day Freeze on Wages and Prices POL 1429 W432
26 Pan-American Games Highlights EXP 1823 E235
27 World Series Game Six: Best Ever? EXP 892 M635
28 "Heaven's Gate" Disaster EXP 1207 K500
29 European Common Market Today INT 955 Y556
30 The Economy Under Sub-Zero Population Growth MON 1020 E235
31 U.S. Dollar Devalued 10 Percent FMK 714 N200
32 Oil Embargo Threatens World Economies MON 1633 Y556
33 U.S. Plans Gas Rationing POL 1068 W432
34 Oil Embargo Ends; Prices Remain High FMK 992 N200
35 Condominiums Alter U.S. Housing BUS 1317 N425
36 Celtics Win 13th NBA Title EXP 1780 M635
37 The Council on Wage and Price Stability POL 1431 E235
38 Unemployment Reaches 6.5 Percent POL 852 J525
39 The Economy and the New $2 Minimum Wage MON 1174 N425
40 You and the Pension Reform Act MON 1698 N200
41 Dow Jones at Twelve-Year Low FMK 793 L130
42 Innsbruck Winter Olympics In Depth EXP 2371 S260
43 Montreal Summer Olympics In Depth EXP 2889 M635
44 Rebates Spur Auto Sales BUS 1028 S260
45 25 Percent British Inflation European High INT 812 Y556
46 New York City Fiscal Crisis MON 1477 N200
47 Unemployment Reaches 9.2 Percent POL 906 W432
48 International Trade Improves FMK 1175 Y556
49 Sunbelt States Lead U.S. Business Growth FMK 1310 C200
50 Chris Evert: 1975 Athlete of the Year EXP 1633 C200
51 Economic Outlook Under Carter MON 1707 J525
52 Raging Bull EXP 1642 L130
53 U.S. Agriculture in the World Economy FMK 1339 K500
54 Milton Friedman Interview ITV 1994 W432
55 Trans-Alaskan Oil Pipeline Opening MON 803 J525
56 Soviet Economy Lags INT 1320 Y556
57 U.S. Trade Deficit Grows MON 881 L130
58 Bruce Jenner: 1976 Athlete of the Year EXP 1428 E235
59 Farm Aid Abuses MON 1866 J525
60 Muhammad Ali: 1978 Athlete of the Year EXP 1670 C200
61 Pact Between Japan and China INT 789 Y556
62 Herbert A. Simon Interview ITV 1811 W432
63 California's Proposition 13 LAW 1293 L130
64 Chrysler Asks U.S. For $1 Billion FMK 975 K500
65 OPEC Will Again Raise Oil Prices MON 812 Y556
66 Lake Placid Olympics In Depth EXP 2684 S260
67 U.S. Boycotts Moscow Olympics POL 1137 C200
68 Alternative Energy Sources TEC 2085 S100
69 Borg Bests McEnroe EXP 1456 E235
70 Windfall Tax on Oil Profits FMK 1497 K500
71 Economic Outlook Under Reagan MON 1621 J525
72 25% Tax Cut Bill Approved LAW 1515 H400
73 Reagan Fires Air Traffic Controllers POL 737 L130
74 On Golden Pond EXP 1703 L130
75 AT&T Antitrust Settlement LAW 1600 K500
76 Oil Glut Tumbles Gas Prices MON 1399 N200
77 "The Twilight Zone" Disaster BUS 1122 N200
78 Toyota and GM Joint Venture BUS 1682 S100
79 Building Trade Outlook BUS 1437 K500
80 Michael Jackson at the Top EXP 1360 K500
81 Cola Advertising War BUS 1096 K500
82 Steve Cram's Dream Mile INT 948 E235
83 Cable TV Goes Worldwide BUS 1384 W432
84 Bingham Family Feud BUS 2103 L130
85 Depression in the Farming Belt MON 1605 L130
86 Texaco Files Bankruptcy FMK 1414 K500
87 Stock Market's Black Monday FMK 2395 L130
88 Island Gems BUS 1601 S300
89 Aspirin Tablets Protect Heart TEC 762 Y600
90 Reagan's $1.09 Trillion Budget POL 1871 C500
91 Trade Deficit Continues Decline MON 848 H400
92 Savings & Loan Crisis MON 1800 L130
93 Jobless Rate at 15-Year Low POL 697 H400
94 Economics of the Decline of Communism POL 1905 S100
95 The BCCI Scandal BUS 2416 H400
96 Hurricane Andrew and the Insurance Industry FMK 1855 Y556
97 REITs Are Back BUS 1152 L130
98 Economics of Safeguarding Our Food Supply MON 1733 N200
99 Eurpoean Common Market Today INT 955 W432
100 The Pension Reform Act and You LAW 1689 N425
101 Untapped Alternative Energy Sources EXP 2085 S260
102 The BCCI Scandal Revealed EXP 2461 H400
103 Computers in the Future TEC 2222 H400
ART_Type ART_Description
ADV Advertising
BUS Business
EXP Expose
FMK Financial market
INT International
ITV Interview
LAW Law
MON Monetary
POL Political
STA Statistical
TEC Technology
ED_ID ED_LastName ED_FirstName ED_Phone
C123 Cross Michelle 2107838181
J065 Jones Bill 9177892046
M635 Martinez Bill 6105028244
N029 Nixon Ronald
S300 Steer Nick
MAG_ID MAG_Name
1 Business Perspective
2 Media Scene
3 Science Outlook
4 Total Sports
5 Travel Vista
ART_ID MAG_ID Issue
1 1 1970-03-01
2 1 1970-03-01
7 1 1970-05-01
8 1 1970-06-01
9 1 1970-06-01
10 2 1978-12-01
20 1 1970-08-01
21 1 1970-08-01
22 1 1971-02-01
23 1 1971-03-01
24 1 1971-07-01
25 1 1971-09-01
26 4 1975-11-01
27 4 1975-11-01
28 2 1980-10-01
29 1 1972-02-01
30 1 1972-12-01
31 1 1973-03-01
32 1 1973-11-01
33 1 1973-12-01
34 1 1974-04-01
35 1 1974-07-01
36 4 1976-06-01
42 4 1976-03-01
43 4 1976-08-01
44 1 1975-02-01
45 1 1975-07-01
46 1 1975-11-01
47 1 1975-06-01
48 1 1975-12-01
49 1 1976-03-01
58 4 1977-01-01
59 1 1978-04-01
60 4 1979-01-01
61 1 1978-11-01
62 1 1978-12-01
63 1 1978-11-01
68 1 1980-06-01
69 4 1980-07-01
77 2 1982-08-01
78 1 1983-03-01
79 1 1984-04-01
80 2 1984-10-01
81 1 1985-05-01
82 4 1985-08-01
83 1 1985-11-01
84 1 1986-06-01
85 1 1987-02-01
86 1 1987-05-01
87 1 1987-11-01
88 5 1987-03-01
89 3 1988-02-01
90 1 1988-03-01
91 1 1988-07-01
96 1 1992-09-01
97 1 1993-05-01
98 1 1993-07-01
ED_ID MAG_ID
C123 1
C123 3
J065 1
J065 4
M635 3
M635 5
N029 2
N029 4
S300 2
S300 5
WRT_ID WRT_LastName WRT_FirstName WRT_State WRT_Phone WRT_LastContactDate WRT_Freelancer
C200 Cox Adam NY 2107835415 1982-11-14 true
C500 Cohen Julia Rice CA 9093381875 1995-02-28 false
E235 Epstein Diane PA 1979-11-14 true
H400 Hall Valerie TX 7106587767 1994-09-16 false
J525 Johnson Leroy W. NY 2108952046 1991-01-29 false
K500 Kim Chong CA 9097295364 1994-05-19 false
L130 Leavitt Morris OH 8102702927 1995-03-06 false
M635 Martinez Bill PA 6105028244 1995-01-18 false
N200 Ngo Thuy TX 7066360046 1995-03-06 true
N425 Nilsson Ronald CA 1977-07-09 true
S100 Seeger Wilhelm TX 7062885132 1993-12-24 true
S260 Smith Henry TX 7069570652 1993-12-24 false
S300 Steer Nick TX 1993-12-24 true
S500 Steg Patty TX 7066544482 1993-12-24 false
W432 Waldeck Kristine CA 9053618181 1986-04-01 true
Y556 Yamamura Shinjiro CA 9055511293 1972-09-26 false
Y600 Yam Zampa CA 9058757874 1972-09-26 false