- There is an error in the script: COPY is not supported in interactive interfac

Question

- There is an error in the script: COPY is not supported in interactive interface. Write INSERT commands equivalent to the COPY commands that you find in the file.

- Write SQL code for the following queries:
a) For each bar, find its best-seller beer. A beer is the best-seller of a bar when it is involved in the most #rows in the Sells table. Note: The output will consist of (bar, beer) pairs.
b) Find the most popular beer. A beer is popular when it is sold by the most bars.
c) Find the bars that Fred frequents and sell more than one beer that Fred likes.
d) Find the names of the bars that sell 'Bud' for the cheapest price (compared with other bars). (Your query must be using EXISTS).

- Show the results of the above queries (copy+paste from your screen).

below is the .sql file

DROP DATABASE beersdb;

CREATE DATABASE beersdb

WITH

OWNER = postgres

ENCODING = 'UTF8'

LC_COLLATE = 'C'

LC_CTYPE = 'C'

TABLESPACE = pg_default

CONNECTION LIMIT = -1;

CREATE TABLE bars (

name character varying(30),

addr character varying(50),

license character varying(50)

);

CREATE TABLE beers (

name character varying(30),

manf character varying(50)

);

CREATE TABLE drinkers (

name character varying(30),

addr character varying(50),

phone character(16)

);

CREATE TABLE frequents (

drinker character varying(30),

bar character varying(30)

);

CREATE TABLE likes (

drinker character varying(30),

beer character varying(30)

);

CREATE TABLE sells (

bar character varying(20),

beer character varying(30),

price real

);

INSERT INTO beers (name, manf)

VALUES

('Coors', 'Adolph Coors'),

('Coors Lite', 'Adolph Coors'),

('Miller', 'Miller Brewing'),

('Miller Lite', 'Miller Brewing'),

('MGD', 'Miller Brewing'),

('Bud', 'Anheuser-Busch'),

('Bud Lite', 'Anheuser-Busch'),

('Michelob', 'Anheuser-Busch'),

('Anchor Steam', 'Anchor Brewing')

;

INSERT INTO bars (name, addr, license)

VALUES

('Joe''s', '123 Any Street', 'B7462A'),

('Sue''s', '456 My Way', 'C5473S')

;

COPY drinkers (name, addr, phone) FROM stdin;

Bill Jones 180 Saint St. 831-459-1812   

Kelly Arthur 180 Alto Pl. 650-856-2002   

Fred 1234 Fifth St. 831-426-1956   

.

COPY frequents (drinker, bar) FROM stdin;

Bill Jones Joe''s

Bill Jones Sue''s

Kelly Arthur Joe''s

.

COPY likes (drinker, beer) FROM stdin;

Bill Jones Miller

Bill Jones Michelob

Kelly Arthur Anchor Steam

Fred MGD

.

COPY sells (bar, beer, price) FROM stdin;

Joe''s Coors 2.5

Joe''s Bud 2.5

Joe''s Bud Lite 2.5

Joe''s Michelob 2.5

Joe''s Anchor Steam 3.5

Sue''s Coors 2

Sue''s Miller 2

.

Explanation / Answer

Qa) For each bar, find its best-seller beer. A beer is the best-seller of a bar when it is involved in the most #rows in the Sells table.

SELECT bar, COUNT(beer) AS MOST_SELLING_BEER FROM SELLS GROUP BY (bar) ORDER BY MOST_SELLING_BEER DESC LIMIT 1;

Qb) Find the most popular beer. A beer is popular when it is sold by the most bars.

SELECT COUNT(BARS) AS MOST_POPULAR_BEER, beer FROM SELLS GROUP BY(beer) ORDER BY MOST_POPULAR_BEER DESC LIMIT 1;

Qc) Find the bars that Fred frequents and sell more than one beer that Fred likes.

SELECT frequents.BAR, frequents.drinker, COUNT(SELLS.BEER) FROM frequents JOIN likes ON likes.drinker = frequents.drinker WHERE frequents.drinker = 'Fred' GROUP BY (frequents.BAR, frequents.drinker) HAVING COUNT(SELLS.BEER) >=1;

Qd) SELECT bars.name from bars WHERE EXISTS (SELECT bars.NAME, MIN(PRICE) FROM SELLS JOIN BARS ON BARS.NAME = SELLS.bar where sells.beer = 'Bud' GROUP BY (bars.NAME));

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.