Database: browser_web

CREATE TABLE IF NOT EXISTS "Web_client_accelerator" (
"id" int,
"name" text,
"Operating_system" text,
"Client" text,
"Connection" text,
primary key("id")
);
CREATE TABLE IF NOT EXISTS "browser" (
"id" int,
"name" text,
"market_share" real,
primary key("id")
);
CREATE TABLE IF NOT EXISTS "accelerator_compatible_browser" (
"accelerator_id" int,
"browser_id" int,
"compatible_since_year" int,
primary key("accelerator_id", "browser_id"),
foreign key ("accelerator_id") references `Web_client_accelerator`("id"),
foreign key ("browser_id") references `browser`("id")
);

Question: How many main stream browsers whose market share is at least 5 exist?
SQL: SELECT count(*) FROM browser WHERE market_share  >=  5

Question: List the name of browsers in descending order by market share.
SQL: SELECT name FROM browser ORDER BY market_share DESC

Question: List the ids, names and market shares of all browsers.
SQL: SELECT id ,  name ,  market_share FROM browser

Question: What is the maximum, minimum and average market share of the listed browsers?
SQL: SELECT max(market_share) ,  min(market_share) ,  avg(market_share) FROM browser

Question: What is the id and market share of the browser Safari?
SQL: SELECT id ,  market_share FROM browser WHERE name  =  'Safari'

Question: What are the name and os of web client accelerators that do not work with only a 'Broadband' type connection?
SQL: SELECT name ,  operating_system FROM web_client_accelerator WHERE CONNECTION != 'Broadband'

Question: What is the name of the browser that became compatible with the accelerator 'CProxy' after year 1998 ?
SQL: SELECT T1.name FROM browser AS T1 JOIN accelerator_compatible_browser AS T2 ON T1.id  =  T2.browser_id JOIN web_client_accelerator AS T3 ON T2.accelerator_id  =  T3.id WHERE T3.name  =  'CProxy' AND T2.compatible_since_year  >  1998

Question: What are the ids and names of the web accelerators that are compatible with two or more browsers?
SQL: SELECT T1.id ,  T1.Name FROM web_client_accelerator AS T1 JOIN accelerator_compatible_browser AS T2 ON T2.accelerator_id  =  T1.id GROUP BY T1.id HAVING count(*)  >=  2

Question: What is the id and name of the browser that is compatible with the most web accelerators?
SQL: SELECT T1.id ,  T1.name FROM browser AS T1 JOIN accelerator_compatible_browser AS T2 ON T1.id  =  T2.browser_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1

Question: When did the web accelerator 'CACHEbox' and browser 'Internet Explorer' become compatible?
SQL: SELECT T1.compatible_since_year FROM accelerator_compatible_browser AS T1 JOIN browser AS T2 ON T1.browser_id  =  T2.id JOIN web_client_accelerator AS T3 ON T1.accelerator_id  =  T3.id WHERE T3.name  =  'CACHEbox' AND T2.name  =  'Internet Explorer'

Question: How many different kinds of clients are supported by the web clients accelerators?
SQL: SELECT count(DISTINCT client) FROM web_client_accelerator

Question: How many accelerators are not compatible with the browsers listed ?
SQL: SELECT count(*) FROM web_client_accelerator WHERE id NOT IN ( SELECT accelerator_id FROM accelerator_compatible_browser );

Question: What distinct accelerator names are compatible with the browswers that have market share higher than 15?
SQL: SELECT DISTINCT T1.name FROM web_client_accelerator AS T1 JOIN accelerator_compatible_browser AS T2 ON T2.accelerator_id  =  T1.id JOIN browser AS T3 ON T2.browser_id  =  T3.id WHERE T3.market_share  >  15;

Question: List the names of the browser that are compatible with both 'CACHEbox' and 'Fasterfox'.
SQL: SELECT T3.name FROM web_client_accelerator AS T1 JOIN accelerator_compatible_browser AS T2 ON T2.accelerator_id  =  T1.id JOIN browser AS T3 ON T2.browser_id  =  T3.id WHERE T1.name  =  'CACHEbox' INTERSECT SELECT T3.name FROM web_client_accelerator AS T1 JOIN accelerator_compatible_browser AS T2 ON T2.accelerator_id  =  T1.id JOIN browser AS T3 ON T2.browser_id  =  T3.id WHERE T1.name  =  'Fasterfox'

Question: Show the accelerator names and supporting operating systems that are not compatible with the browser named 'Opera'.
SQL: SELECT name ,  operating_system FROM web_client_accelerator EXCEPT SELECT T1.name ,  T1.operating_system FROM web_client_accelerator AS T1 JOIN accelerator_compatible_browser AS T2 ON T2.accelerator_id  =  T1.id JOIN browser AS T3 ON T2.browser_id  =  T3.id WHERE T3.name  =  'Opera'

Question: Which accelerator name contains substring "Opera"?
SQL: SELECT name FROM web_client_accelerator WHERE name LIKE "%Opera%"

Question: Find the number of web accelerators used for each Operating system.
SQL: SELECT Operating_system ,  count(*) FROM web_client_accelerator GROUP BY Operating_system

Question: give me names of all compatible browsers and accelerators in the descending order of compatible year
SQL: SELECT T2.name ,  T3.name FROM accelerator_compatible_browser AS T1 JOIN browser AS T2 ON T1.browser_id  =  T2.id JOIN web_client_accelerator AS T3 ON T1.accelerator_id  =  T3.id ORDER BY T1.compatible_since_year DESC