NBAmine/xlangai-spider-with-context huggingface dataset:
CREATE TABLE flight (
    flno NUMBER PRIMARY KEY,
    origin TEXT,
    destination TEXT,
    distance NUMBER,
    departure_date TIME,
    arrival_date TIME,
    price NUMBER,
    aid NUMBER,
    FOREIGN KEY ("aid") REFERENCES aircraft("aid")
);
CREATE TABLE aircraft (
    aid NUMBER PRIMARY KEY,
    name TEXT,
    distance NUMBER
);
CREATE TABLE employee (
    eid NUMBER PRIMARY KEY,
    name TEXT,
    salary NUMBER
);
CREATE TABLE certificate (
    eid NUMBER PRIMARY KEY,
    aid NUMBER,
    FOREIGN KEY ("aid") REFERENCES aircraft("aid"),
    FOREIGN KEY ("eid") REFERENCES employee("eid")
);

Question: What is the name and distance of every aircraft that can cover a distance of more than 5000 and which at least 5 people can fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5

Question: what is the salary and name of the employee who has the most number of aircraft certificates?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salaray and name of the employee that is certified to fly the most planes?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salary and name of the employee who has the most number of certificates on aircrafts with distance more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salaray and name of the employee with the most certificates to fly planes more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1
CREATE TABLE Allergy_Type (
    Allergy TEXT PRIMARY KEY,
    AllergyType TEXT
);
CREATE TABLE Has_Allergy (
    StuID NUMBER,
    Allergy TEXT,
    FOREIGN KEY ("Allergy") REFERENCES Allergy_Type("Allergy"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);

Question: Find the first name and major of the students who are not allegry to soy.
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")

Question: What are the first name and major of the students who are able to consume soy?
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")
CREATE TABLE Accounts (
    account_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    account_name TEXT,
    other_account_details TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_first_name TEXT,
    customer_last_name TEXT,
    customer_address TEXT,
    customer_phone TEXT,
    customer_email TEXT,
    other_customer_details TEXT
);
CREATE TABLE Customers_Cards (
    card_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    card_type_code TEXT,
    card_number TEXT,
    date_valid_from TIME,
    date_valid_to TIME,
    other_card_details TEXT
);
CREATE TABLE Financial_Transactions (
    transaction_id NUMBER,
    previous_transaction_id NUMBER,
    account_id NUMBER,
    card_id NUMBER,
    transaction_type TEXT,
    transaction_date TIME,
    transaction_amount NUMBER,
    transaction_comment TEXT,
    other_transaction_details TEXT,
    FOREIGN KEY ("account_id") REFERENCES Accounts("account_id"),
    FOREIGN KEY ("card_id") REFERENCES Customers_Cards("card_id")
);

Question: Show the card type codes and the number of transactions.
SQL: SELECT T2.card_type_code ,  count(*) FROM Financial_transactions AS T1 JOIN Customers_cards AS T2 ON T1.card_id  =  T2.card_id GROUP BY T2.card_type_code

Question: What are the different card types, and how many transactions have been made with each?
SQL: SELECT T2.card_type_code ,  count(*) FROM Financial_transactions AS T1 JOIN Customers_cards AS T2 ON T1.card_id  =  T2.card_id GROUP BY T2.card_type_code

Question: Show the transaction type and the number of transactions.
SQL: SELECT transaction_type ,  count(*) FROM Financial_transactions GROUP BY transaction_type

Question: What are the different transaction types, and how many transactions of each have taken place?
SQL: SELECT transaction_type ,  count(*) FROM Financial_transactions GROUP BY transaction_type

Question: What is the transaction type that has processed the greatest total amount in transactions?
SQL: SELECT transaction_type FROM Financial_transactions GROUP BY transaction_type ORDER BY sum(transaction_amount) DESC LIMIT 1

Question: Return the type of transaction with the highest total amount.
SQL: SELECT transaction_type FROM Financial_transactions GROUP BY transaction_type ORDER BY sum(transaction_amount) DESC LIMIT 1

Question: Show the account id and the number of transactions for each account
SQL: SELECT account_id ,  count(*) FROM Financial_transactions GROUP BY account_id

Question: What are the different account ids that have made financial transactions, as well as how many transactions correspond to each?
SQL: SELECT account_id ,  count(*) FROM Financial_transactions GROUP BY account_id
CREATE TABLE medicine (
    id NUMBER PRIMARY KEY,
    name TEXT,
    Trade_Name TEXT,
    FDA_approved TEXT
);
CREATE TABLE enzyme (
    id NUMBER PRIMARY KEY,
    name TEXT,
    Location TEXT,
    Product TEXT,
    Chromosome TEXT,
    OMIM NUMBER,
    Porphyria TEXT
);
CREATE TABLE medicine_enzyme_interaction (
    enzyme_id NUMBER PRIMARY KEY,
    medicine_id NUMBER,
    interaction_type TEXT,
    FOREIGN KEY ("medicine_id") REFERENCES medicine("id"),
    FOREIGN KEY ("enzyme_id") REFERENCES enzyme("id")
);

Question: List the medicine name and trade name which can both interact as 'inhibitor' and 'activitor' with enzymes.
SQL: SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id WHERE interaction_type  =  'inhibitor' INTERSECT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id WHERE interaction_type  =  'activitor'

Question: What are the medicine and trade names that can interact as an inhibitor and activitor with enzymes?
SQL: SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id WHERE interaction_type  =  'inhibitor' INTERSECT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id WHERE interaction_type  =  'activitor'

Question: Show the medicine names and trade names that cannot interact with the enzyme with product 'Heme'.
SQL: SELECT name ,  trade_name FROM medicine EXCEPT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id JOIN enzyme AS T3 ON T3.id  =  T2.enzyme_id WHERE T3.product  =  'Protoporphyrinogen IX'

Question: What are the medicine and trade names that cannot interact with the enzyme with the product 'Heme'?
SQL: SELECT name ,  trade_name FROM medicine EXCEPT SELECT T1.name ,  T1.trade_name FROM medicine AS T1 JOIN medicine_enzyme_interaction AS T2 ON T2.medicine_id  =  T1.id JOIN enzyme AS T3 ON T3.id  =  T2.enzyme_id WHERE T3.product  =  'Protoporphyrinogen IX'

Question: How many distinct FDA approval statuses are there for the medicines?
SQL: SELECT count(DISTINCT FDA_approved) FROM medicine

Question: How many different FDA approval statuses exist for medicines?
SQL: SELECT count(DISTINCT FDA_approved) FROM medicine

Question: Which enzyme names have the substring "ALA"?
SQL: SELECT name FROM enzyme WHERE name LIKE "%ALA%"

Question: What are the names of enzymes that include the string 'ALA'?
SQL: SELECT name FROM enzyme WHERE name LIKE "%ALA%"

Question: find the number of medicines offered by each trade.
SQL: SELECT trade_name ,  count(*) FROM medicine GROUP BY trade_name

Question: How many medicines are offered by each trade name?
SQL: SELECT trade_name ,  count(*) FROM medicine GROUP BY trade_name
CREATE TABLE basketball_match (
    Team_ID NUMBER PRIMARY KEY,
    School_ID NUMBER,
    Team_Name TEXT,
    ACC_Regular_Season TEXT,
    ACC_Percent TEXT,
    ACC_Home TEXT,
    ACC_Road TEXT,
    All_Games TEXT,
    All_Games_Percent NUMBER,
    All_Home TEXT,
    All_Road TEXT,
    All_Neutral TEXT,
    FOREIGN KEY ("School_ID") REFERENCES university("School_ID")
);
CREATE TABLE university (
    School_ID NUMBER PRIMARY KEY,
    School TEXT,
    Location TEXT,
    Founded NUMBER,
    Affiliation TEXT,
    Enrollment NUMBER,
    Nickname TEXT,
    Primary_conference TEXT
);

Question: Return the maximum enrollment across all schools.
SQL: SELECT max(Enrollment) FROM university

Question: List all information regarding the basketball match.
SQL: SELECT * FROM basketball_match

Question: What is all the information about the basketball match?
SQL: SELECT * FROM basketball_match

Question: List names of all teams in the basketball competition, ordered by all home scores in descending order.
SQL: SELECT team_name FROM basketball_match ORDER BY All_Home DESC

Question: What are the names of all the teams in the basketball competition, sorted by all home scores in descending order?
SQL: SELECT team_name FROM basketball_match ORDER BY All_Home DESC
CREATE TABLE people (
    People_ID NUMBER PRIMARY KEY,
    District TEXT,
    Name TEXT,
    Party TEXT,
    Age NUMBER
);
CREATE TABLE debate (
    Debate_ID NUMBER PRIMARY KEY,
    Date TEXT,
    Venue TEXT,
    Num_of_Audience NUMBER
);
CREATE TABLE debate_people (
    Debate_ID NUMBER PRIMARY KEY,
    Affirmative NUMBER,
    Negative NUMBER,
    If_Affirmative_Win OTHERS,
    FOREIGN KEY ("Negative") REFERENCES people("People_ID"),
    FOREIGN KEY ("Affirmative") REFERENCES people("People_ID"),
    FOREIGN KEY ("Debate_ID") REFERENCES debate("Debate_ID")
);

Question: How many debates are there?
SQL: SELECT count(*) FROM debate

Question: List the venues of debates in ascending order of the number of audience.
SQL: SELECT Venue FROM debate ORDER BY Num_of_Audience ASC

Question: What are the date and venue of each debate?
SQL: SELECT Date ,  Venue FROM debate

Question: List the dates of debates with number of audience bigger than 150
SQL: SELECT Date FROM debate WHERE Num_of_Audience  >  150

Question: Show the names of people aged either 35 or 36.
SQL: SELECT Name FROM  people WHERE Age  =  35 OR Age  =  36

Question: What is the party of the youngest people?
SQL: SELECT Party FROM people ORDER BY Age ASC LIMIT 1

Question: Show different parties of people along with the number of people in each party.
SQL: SELECT Party ,  COUNT(*) FROM people GROUP BY Party

Question: Show the party that has the most people.
SQL: SELECT Party FROM people GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the distinct venues of debates
SQL: SELECT DISTINCT Venue FROM debate

Question: Show the names of people, and dates and venues of debates they are on the affirmative side.
SQL: SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID

Question: Show the names of people, and dates and venues of debates they are on the negative side, ordered in ascending alphabetical order of name.
SQL: SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Negative  =  T3.People_ID ORDER BY T3.Name ASC

Question: Show the names of people that are on affirmative side of debates with number of audience bigger than 200.
SQL: SELECT T3.Name FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID WHERE T2.Num_of_Audience  >  200

Question: Show the names of people and the number of times they have been on the affirmative side of debates.
SQL: SELECT T2.Name ,  COUNT(*) FROM debate_people AS T1 JOIN people AS T2 ON T1.Affirmative  =  T2.People_ID GROUP BY T2.Name

Question: Show the names of people who have been on the negative side of debates at least twice.
SQL: SELECT T2.Name FROM debate_people AS T1 JOIN people AS T2 ON T1.Negative  =  T2.People_ID GROUP BY T2.Name HAVING COUNT(*)  >=  2

Question: List the names of people that have not been on the affirmative side of debates.
SQL: SELECT Name FROM people WHERE People_id NOT IN (SELECT Affirmative FROM debate_people)
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_first_name TEXT,
    customer_middle_initial TEXT,
    customer_last_name TEXT,
    gender TEXT,
    email_address TEXT,
    login_name TEXT,
    login_password TEXT,
    phone_number TEXT,
    town_city TEXT,
    state_county_province TEXT,
    country TEXT
);
CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    date_order_placed TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Invoices (
    invoice_number NUMBER PRIMARY KEY,
    order_id NUMBER,
    invoice_date TIME,
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Accounts (
    account_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    date_account_opened TIME,
    account_name TEXT,
    other_account_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Product_Categories (
    production_type_code TEXT PRIMARY KEY,
    product_type_description TEXT,
    vat_rating NUMBER
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    parent_product_id NUMBER,
    production_type_code TEXT,
    unit_price NUMBER,
    product_name TEXT,
    product_color TEXT,
    product_size TEXT,
    FOREIGN KEY ("production_type_code") REFERENCES Product_Categories("production_type_code")
);
CREATE TABLE Financial_Transactions (
    transaction_id NUMBER,
    account_id NUMBER,
    invoice_number NUMBER,
    transaction_type TEXT,
    transaction_date TIME,
    transaction_amount NUMBER,
    transaction_comment TEXT,
    other_transaction_details TEXT,
    FOREIGN KEY ("account_id") REFERENCES Accounts("account_id"),
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number")
);
CREATE TABLE Order_Items (
    order_item_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    product_id NUMBER,
    product_quantity TEXT,
    other_order_item_details TEXT,
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_id")
);
CREATE TABLE Invoice_Line_Items (
    order_item_id NUMBER,
    invoice_number NUMBER,
    product_id NUMBER,
    product_title TEXT,
    product_quantity TEXT,
    product_price NUMBER,
    derived_product_cost NUMBER,
    derived_vat_payable NUMBER,
    derived_total_cost NUMBER,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number"),
    FOREIGN KEY ("order_item_id") REFERENCES Order_Items("order_item_id")
);

Question: How many products were not included in any order?
SQL: SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )

Question: Count the number of products that were never ordered.
SQL: SELECT count(*) FROM products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )
CREATE TABLE artist (
    Artist_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Country TEXT,
    Year_Join NUMBER,
    Age NUMBER
);
CREATE TABLE exhibition (
    Exhibition_ID NUMBER PRIMARY KEY,
    Year NUMBER,
    Theme TEXT,
    Artist_ID NUMBER,
    Ticket_Price NUMBER,
    FOREIGN KEY ("Artist_ID") REFERENCES artist("Artist_ID")
);
CREATE TABLE exhibition_record (
    Exhibition_ID NUMBER PRIMARY KEY,
    Date TEXT,
    Attendance NUMBER,
    FOREIGN KEY ("Exhibition_ID") REFERENCES exhibition("Exhibition_ID")
);

Question: Show all artist names with an average exhibition attendance over 200.
SQL: SELECT T3.name FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id JOIN artist AS T3 ON T3.artist_id  =  T2.artist_id GROUP BY T3.artist_id HAVING avg(T1.attendance)  >  200

Question: What are the names of artist whose exhibitions draw over 200 attendees on average?
SQL: SELECT T3.name FROM exhibition_record AS T1 JOIN exhibition AS T2 ON T1.exhibition_id  =  T2.exhibition_id JOIN artist AS T3 ON T3.artist_id  =  T2.artist_id GROUP BY T3.artist_id HAVING avg(T1.attendance)  >  200
CREATE TABLE company (
    Company_ID NUMBER PRIMARY KEY,
    Rank NUMBER,
    Company TEXT,
    Headquarters TEXT,
    Main_Industry TEXT,
    Sales_billion NUMBER,
    Profits_billion NUMBER,
    Assets_billion NUMBER,
    Market_Value NUMBER
);
CREATE TABLE gas_station (
    Station_ID NUMBER PRIMARY KEY,
    Open_Year NUMBER,
    Location TEXT,
    Manager_Name TEXT,
    Vice_Manager_Name TEXT,
    Representative_Name TEXT
);
CREATE TABLE station_company (
    Station_ID NUMBER PRIMARY KEY,
    Company_ID NUMBER,
    Rank_of_the_Year NUMBER,
    FOREIGN KEY ("Company_ID") REFERENCES company("Company_ID"),
    FOREIGN KEY ("Station_ID") REFERENCES gas_station("Station_ID")
);

Question: Show the manager name with most number of gas stations opened after 2000.
SQL: SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the manager with the most gas stations that opened after 2000?
SQL: SELECT manager_name FROM gas_station WHERE open_year  >  2000 GROUP BY manager_name ORDER BY count(*) DESC LIMIT 1

Question: order all gas station locations by the opening year.
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year

Question: What are the locations of all the gas stations ordered by opening year?
SQL: SELECT LOCATION FROM gas_station ORDER BY open_year

Question: find the rank, company names, market values of the companies in the banking industry order by their sales and profits in billion.
SQL: SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion

Question: What is the rank, company, and market value of every comapny in the banking industry ordered by sales and profits?
SQL: SELECT rank ,  company ,  market_value FROM company WHERE main_industry  =  'Banking' ORDER BY sales_billion ,  profits_billion

Question: find the location and Representative name of the gas stations owned by the companies with top 3 Asset amounts.
SQL: SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3

Question: What are the locations and representatives' names of the gas stations owned by the companies with the 3 largest amounts of assets?
SQL: SELECT T3.location ,  T3.Representative_Name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id  =  T2.company_id JOIN gas_station AS T3 ON T1.station_id  =  T3.station_id ORDER BY T2.Assets_billion DESC LIMIT 3
CREATE TABLE region (
    Region_ID NUMBER PRIMARY KEY,
    Region_name TEXT,
    Date TEXT,
    Label TEXT,
    Format TEXT,
    Catalogue TEXT
);
CREATE TABLE party (
    Party_ID NUMBER PRIMARY KEY,
    Minister TEXT,
    Took_office TEXT,
    Left_office TEXT,
    Region_ID NUMBER,
    Party_name TEXT,
    FOREIGN KEY ("Region_ID") REFERENCES region("Region_ID")
);
CREATE TABLE member (
    Member_ID NUMBER PRIMARY KEY,
    Member_Name TEXT,
    Party_ID TEXT,
    In_office TEXT,
    FOREIGN KEY ("Party_ID") REFERENCES party("Party_ID")
);
CREATE TABLE party_events (
    Event_ID NUMBER PRIMARY KEY,
    Event_Name TEXT,
    Party_ID NUMBER,
    Member_in_charge_ID NUMBER,
    FOREIGN KEY ("Member_in_charge_ID") REFERENCES member("Member_ID"),
    FOREIGN KEY ("Party_ID") REFERENCES party("Party_ID")
);

Question: How many Annual Meeting events happened in the United Kingdom region?
SQL: SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"

Question: Count the number of Annual Meeting events that took place in the region of the United Kingdom.
SQL: SELECT count(*) FROM region AS t1 JOIN party AS t2 ON t1.region_id  =  t2.region_id JOIN party_events AS t3 ON t2.party_id  =  t3.party_id WHERE t1.region_name  =  "United Kingdom" AND t3.Event_Name  =  "Annaual Meeting"
CREATE TABLE works_on (
    Essn NUMBER PRIMARY KEY,
    Pno NUMBER,
    Hours NUMBER
);
CREATE TABLE employee (
    Fname TEXT,
    Minit TEXT,
    Lname TEXT,
    Ssn NUMBER PRIMARY KEY,
    Bdate TEXT,
    Address TEXT,
    Sex TEXT,
    Salary NUMBER,
    Super_ssn NUMBER,
    Dno NUMBER
);
CREATE TABLE department (
    Dname TEXT,
    Dnumber NUMBER PRIMARY KEY,
    Mgr_ssn NUMBER,
    Mgr_start_date TEXT
);
CREATE TABLE project (
    Pname TEXT,
    Pnumber NUMBER PRIMARY KEY,
    Plocation TEXT,
    Dnum NUMBER
);
CREATE TABLE dependent (
    Essn NUMBER PRIMARY KEY,
    Dependent_name TEXT,
    Sex TEXT,
    Bdate TEXT,
    Relationship TEXT
);
CREATE TABLE dept_locations (
    Dnumber NUMBER PRIMARY KEY,
    Dlocation TEXT
);

Question: List all department names ordered by their starting date.
SQL: SELECT dname FROM department ORDER BY mgr_start_date

Question: find all dependent names who have a spouse relation with some employee.
SQL: SELECT Dependent_name FROM dependent WHERE relationship  =  'Spouse'

Question: how many female dependents are there?
SQL: SELECT count(*) FROM dependent WHERE sex  =  'F'

Question: Find the names of departments that are located in Houston.
SQL: SELECT t1.dname FROM department AS t1 JOIN dept_locations AS t2 ON t1.dnumber  =  t2.dnumber WHERE t2.dlocation  =  'Houston'

Question: Return the first names and last names of employees who earn more than 30000 in salary.
SQL: SELECT fname ,  lname FROM employee WHERE salary  >  30000

Question: Find the number of employees of each gender whose salary is lower than 50000.
SQL: SELECT count(*) ,  sex FROM employee WHERE salary  <  50000 GROUP BY sex

Question: list the first and last names, and the addresses of all employees in the ascending order of their birth date.
SQL: SELECT fname ,  lname ,  address FROM employee ORDER BY Bdate
CREATE TABLE Campuses (
    Id NUMBER PRIMARY KEY,
    Campus TEXT,
    Location TEXT,
    County TEXT,
    Year NUMBER
);
CREATE TABLE csu_fees (
    Campus NUMBER PRIMARY KEY,
    Year NUMBER,
    CampusFee NUMBER,
    FOREIGN KEY ("Campus") REFERENCES Campuses("Id")
);
CREATE TABLE degrees (
    Year NUMBER PRIMARY KEY,
    Campus NUMBER,
    Degrees NUMBER,
    FOREIGN KEY ("Campus") REFERENCES Campuses("Id")
);
CREATE TABLE discipline_enrollments (
    Campus NUMBER PRIMARY KEY,
    Discipline NUMBER,
    Year NUMBER,
    Undergraduate NUMBER,
    Graduate NUMBER,
    FOREIGN KEY ("Campus") REFERENCES Campuses("Id")
);
CREATE TABLE enrollments (
    Campus NUMBER PRIMARY KEY,
    Year NUMBER,
    TotalEnrollment_AY NUMBER,
    FTE_AY NUMBER,
    FOREIGN KEY ("Campus") REFERENCES Campuses("Id")
);
CREATE TABLE faculty (
    Campus NUMBER,
    Year NUMBER,
    Faculty NUMBER,
    FOREIGN KEY ("Campus") REFERENCES Campuses("Id")
);

Question: How many CSU campuses are there?
SQL: SELECT count(*) FROM campuses

Question: What is the total number of campuses?
SQL: SELECT count(*) FROM campuses
CREATE TABLE storm (
    Storm_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Dates_active TEXT,
    Max_speed NUMBER,
    Damage_millions_USD NUMBER,
    Number_Deaths NUMBER
);
CREATE TABLE region (
    Region_id NUMBER PRIMARY KEY,
    Region_code TEXT,
    Region_name TEXT
);
CREATE TABLE affected_region (
    Region_id NUMBER PRIMARY KEY,
    Storm_ID NUMBER,
    Number_city_affected NUMBER,
    FOREIGN KEY ("Storm_ID") REFERENCES storm("Storm_ID"),
    FOREIGN KEY ("Region_id") REFERENCES region("Region_id")
);

Question: Show the region name with at least two storms.
SQL: SELECT T1.region_name FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id HAVING count(*)  >=  2

Question: What are the names of regions with two or more storms?
SQL: SELECT T1.region_name FROM region AS T1 JOIN affected_region AS T2 ON T1.region_id = T2.region_id GROUP BY T1.region_id HAVING count(*)  >=  2

Question: Find the names of the regions which were affected by the storm that killed the greatest number of people.
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id ORDER BY T3.Number_Deaths DESC LIMIT 1

Question: What are the names of regions that were affected by the storm in which the most people died?
SQL: SELECT T2.region_name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id ORDER BY T3.Number_Deaths DESC LIMIT 1

Question: Find the name of the storm that affected both Afghanistan and Albania regions.
SQL: SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Afghanistan' INTERSECT SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Albania'

Question: What are the names of the storms that affected both the regions of Afghanistan and Albania?
SQL: SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Afghanistan' INTERSECT SELECT T3.Name FROM affected_region AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id JOIN storm AS T3 ON T1.storm_id  =  T3.storm_id WHERE T2.Region_name  =  'Albania'
CREATE TABLE Inst (
    instID NUMBER PRIMARY KEY,
    name TEXT,
    country TEXT
);
CREATE TABLE Authors (
    authID NUMBER PRIMARY KEY,
    lname TEXT,
    fname TEXT
);
CREATE TABLE Papers (
    paperID NUMBER PRIMARY KEY,
    title TEXT
);
CREATE TABLE Authorship (
    authID NUMBER PRIMARY KEY,
    instID NUMBER,
    paperID NUMBER,
    authOrder NUMBER,
    FOREIGN KEY ("paperID") REFERENCES Papers("paperID"),
    FOREIGN KEY ("instID") REFERENCES Inst("instID"),
    FOREIGN KEY ("authID") REFERENCES Authors("authID")
);

Question: Retrieve all the last names of authors in alphabetical order.
SQL: SELECT lname FROM authors ORDER BY lname

Question: Give me a list of all the last names of authors sorted in alphabetical order
SQL: SELECT lname FROM authors ORDER BY lname

Question: Retrieve all the first and last names of authors in the alphabetical order of last names.
SQL: SELECT fname ,  lname FROM authors ORDER BY lname

Question: Sort the list of all the first and last names of authors in alphabetical order of the last names.
SQL: SELECT fname ,  lname FROM authors ORDER BY lname
CREATE TABLE bank (
    branch_ID NUMBER PRIMARY KEY,
    bname TEXT,
    no_of_customers NUMBER,
    city TEXT,
    state TEXT
);
CREATE TABLE customer (
    cust_ID TEXT PRIMARY KEY,
    cust_name TEXT,
    acc_type TEXT,
    acc_bal NUMBER,
    no_of_loans NUMBER,
    credit_score NUMBER,
    branch_ID NUMBER,
    state TEXT,
    FOREIGN KEY ("branch_ID") REFERENCES bank("branch_ID")
);
CREATE TABLE loan (
    loan_ID TEXT PRIMARY KEY,
    loan_type TEXT,
    cust_ID TEXT,
    branch_ID TEXT,
    amount NUMBER,
    FOREIGN KEY ("branch_ID") REFERENCES bank("branch_ID")
);

Question: Find the average credit score of the customers who do not have any loan.
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)

Question: What is the average credit score for customers who have never taken a loan?
SQL: SELECT avg(credit_score) FROM customer WHERE cust_id NOT IN (SELECT cust_id FROM loan)
CREATE TABLE CLASS (
    CLASS_CODE TEXT PRIMARY KEY,
    CRS_CODE TEXT,
    CLASS_SECTION TEXT,
    CLASS_TIME TEXT,
    CLASS_ROOM TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("PROF_NUM") REFERENCES EMPLOYEE("EMP_NUM"),
    FOREIGN KEY ("CRS_CODE") REFERENCES COURSE("CRS_CODE")
);
CREATE TABLE COURSE (
    CRS_CODE TEXT PRIMARY KEY,
    DEPT_CODE TEXT,
    CRS_DESCRIPTION TEXT,
    CRS_CREDIT NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);
CREATE TABLE DEPARTMENT (
    DEPT_CODE TEXT PRIMARY KEY,
    DEPT_NAME TEXT,
    SCHOOL_CODE TEXT,
    EMP_NUM NUMBER,
    DEPT_ADDRESS TEXT,
    DEPT_EXTENSION TEXT,
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE EMPLOYEE (
    EMP_NUM NUMBER PRIMARY KEY,
    EMP_LNAME TEXT,
    EMP_FNAME TEXT,
    EMP_INITIAL TEXT,
    EMP_JOBCODE TEXT,
    EMP_HIREDATE TIME,
    EMP_DOB TIME
);
CREATE TABLE ENROLL (
    CLASS_CODE TEXT,
    STU_NUM NUMBER,
    ENROLL_GRADE TEXT,
    FOREIGN KEY ("STU_NUM") REFERENCES STUDENT("STU_NUM"),
    FOREIGN KEY ("CLASS_CODE") REFERENCES CLASS("CLASS_CODE")
);
CREATE TABLE PROFESSOR (
    EMP_NUM NUMBER,
    DEPT_CODE TEXT,
    PROF_OFFICE TEXT,
    PROF_EXTENSION TEXT,
    PROF_HIGH_DEGREE TEXT,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE"),
    FOREIGN KEY ("EMP_NUM") REFERENCES EMPLOYEE("EMP_NUM")
);
CREATE TABLE STUDENT (
    STU_NUM NUMBER PRIMARY KEY,
    STU_LNAME TEXT,
    STU_FNAME TEXT,
    STU_INIT TEXT,
    STU_DOB TIME,
    STU_HRS NUMBER,
    STU_CLASS TEXT,
    STU_GPA NUMBER,
    STU_TRANSFER NUMBER,
    DEPT_CODE TEXT,
    STU_PHONE TEXT,
    PROF_NUM NUMBER,
    FOREIGN KEY ("DEPT_CODE") REFERENCES DEPARTMENT("DEPT_CODE")
);

Question: What is the name of the department that offers a course that has a description including the word "Statistics"?
SQL: SELECT T2.dept_name FROM course AS T1 JOIN department AS T2 ON T1.dept_code  =  T2.dept_code WHERE T1.crs_description LIKE '%Statistics%'

Question: What is the first name of the student whose last name starting with the letter S and is taking ACCT-211 class?
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'

Question: What is the first name of the student whose last name starts with the letter S and is taking ACCT-211?
SQL: SELECT T1.stu_fname FROM student AS T1 JOIN enroll AS T2 ON T1.stu_num  =  T2.stu_num JOIN CLASS AS T3 ON T2.class_code  =  T3.class_code WHERE T3.crs_code  =  'ACCT-211' AND T1.stu_lname LIKE 'S%'
CREATE TABLE club (
    Club_ID NUMBER PRIMARY KEY,
    name TEXT,
    Region TEXT,
    Start_year TEXT
);
CREATE TABLE club_rank (
    Rank NUMBER PRIMARY KEY,
    Club_ID NUMBER,
    Gold NUMBER,
    Silver NUMBER,
    Bronze NUMBER,
    Total NUMBER,
    FOREIGN KEY ("Club_ID") REFERENCES club("Club_ID")
);
CREATE TABLE player (
    Player_ID NUMBER PRIMARY KEY,
    name TEXT,
    Position TEXT,
    Club_ID NUMBER,
    Apps NUMBER,
    Tries NUMBER,
    Goals TEXT,
    Points NUMBER,
    FOREIGN KEY ("Club_ID") REFERENCES club("Club_ID")
);
CREATE TABLE competition (
    Competition_ID NUMBER PRIMARY KEY,
    Year NUMBER,
    Competition_type TEXT,
    Country TEXT
);
CREATE TABLE competition_result (
    Competition_ID NUMBER PRIMARY KEY,
    Club_ID_1 NUMBER,
    Club_ID_2 NUMBER,
    Score TEXT,
    FOREIGN KEY ("Competition_ID") REFERENCES competition("Competition_ID"),
    FOREIGN KEY ("Club_ID_2") REFERENCES club("Club_ID"),
    FOREIGN KEY ("Club_ID_1") REFERENCES club("Club_ID")
);

Question: which country did participated in the most number of Tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: what is the name of the country that participated in the most tournament competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Tournament' GROUP BY country ORDER BY count(*) DESC LIMIT 1

Question: which countries did participated in both Friendly and Tournament type competitions.
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'

Question: What are the countries that participated in both friendly and tournament type competitions?
SQL: SELECT country FROM competition WHERE competition_type  =  'Friendly' INTERSECT SELECT country FROM competition WHERE competition_type  =  'Tournament'

Question: Find the countries that have never participated in any competition with Friendly type.
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'

Question: What are the countries that have never participated in any friendly-type competitions?
SQL: SELECT country FROM competition EXCEPT SELECT country FROM competition WHERE competition_type  =  'Friendly'
CREATE TABLE film (
    Film_ID NUMBER PRIMARY KEY,
    Title TEXT,
    Studio TEXT,
    Director TEXT,
    Gross_in_dollar NUMBER
);
CREATE TABLE market (
    Market_ID NUMBER PRIMARY KEY,
    Country TEXT,
    Number_cities NUMBER
);
CREATE TABLE film_market_estimation (
    Estimation_ID NUMBER PRIMARY KEY,
    Low_Estimate NUMBER,
    High_Estimate NUMBER,
    Film_ID NUMBER,
    Type TEXT,
    Market_ID NUMBER,
    Year NUMBER,
    FOREIGN KEY ("Market_ID") REFERENCES market("Market_ID"),
    FOREIGN KEY ("Film_ID") REFERENCES film("Film_ID")
);

Question: Return the title of the film with the highest high estimate?
SQL: SELECT t1.title FROM film AS T1 JOIN film_market_estimation AS T2  ON T1.Film_ID  =  T2.Film_ID ORDER BY high_estimate DESC LIMIT 1

Question: What are the titles and directors of the films were never presented in China?
SQL: SELECT title ,  director FROM film WHERE film_id NOT IN (SELECT film_id FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.market_id  =  T2.Market_ID WHERE country  =  'China')

Question: Return the titles and directors of films that were never in the market of China.
SQL: SELECT title ,  director FROM film WHERE film_id NOT IN (SELECT film_id FROM film_market_estimation AS T1 JOIN market AS T2 ON T1.market_id  =  T2.Market_ID WHERE country  =  'China')
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Club (
    ClubID NUMBER PRIMARY KEY,
    ClubName TEXT,
    ClubDesc TEXT,
    ClubLocation TEXT
);
CREATE TABLE Member_of_club (
    StuID NUMBER,
    ClubID NUMBER,
    Position TEXT,
    FOREIGN KEY ("ClubID") REFERENCES Club("ClubID"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);

Question: Find the average age of members of the club "Hopkins Student Enterprises".
SQL: SELECT avg(t3.age) FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid  =  t2.clubid JOIN student AS t3 ON t2.stuid  =  t3.stuid WHERE t1.clubname  =  "Hopkins Student Enterprises"

Question: On average, how old are the members in the club "Hopkins Student Enterprises"?
SQL: SELECT avg(t3.age) FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid  =  t2.clubid JOIN student AS t3 ON t2.stuid  =  t3.stuid WHERE t1.clubname  =  "Hopkins Student Enterprises"

Question: Retrieve the average age of members of the club "Tennis Club".
SQL: SELECT avg(t3.age) FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid  =  t2.clubid JOIN student AS t3 ON t2.stuid  =  t3.stuid WHERE t1.clubname  =  "Tennis Club"

Question: Compute the average age of the members in the club "Tennis Club".
SQL: SELECT avg(t3.age) FROM club AS t1 JOIN member_of_club AS t2 ON t1.clubid  =  t2.clubid JOIN student AS t3 ON t2.stuid  =  t3.stuid WHERE t1.clubname  =  "Tennis Club"
CREATE TABLE Person (
    name TEXT PRIMARY KEY,
    age NUMBER,
    city TEXT,
    gender TEXT,
    job TEXT
);
CREATE TABLE PersonFriend (
    name TEXT,
    friend TEXT,
    year NUMBER,
    FOREIGN KEY ("friend") REFERENCES Person("name"),
    FOREIGN KEY ("name") REFERENCES Person("name")
);

Question: What is the total number of people who has no friend living in the city of Austin.
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')

Question: What is the total number of people who have no friends living in Austin?
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')

Question: Find Alice's friends of friends.
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'

Question: What are the names of all of Alice's friends of friends?
SQL: SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name  =  T2.name JOIN PersonFriend AS T3 ON T1.friend  =  T3.name JOIN PersonFriend AS T4 ON T3.friend  =  T4.name WHERE T2.name  =  'Alice' AND T4.name != 'Alice'
CREATE TABLE college (
    College_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Leader_Name TEXT,
    College_Location TEXT
);
CREATE TABLE member (
    Member_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Country TEXT,
    College_ID NUMBER,
    FOREIGN KEY ("College_ID") REFERENCES college("College_ID")
);
CREATE TABLE round (
    Round_ID NUMBER,
    Member_ID NUMBER PRIMARY KEY,
    Decoration_Theme TEXT,
    Rank_in_Round NUMBER,
    FOREIGN KEY ("Member_ID") REFERENCES member("Member_ID")
);

Question: How many members are there?
SQL: SELECT count(*) FROM member

Question: List the names of members in ascending alphabetical order.
SQL: SELECT Name FROM member ORDER BY Name ASC

Question: What are the names and countries of members?
SQL: SELECT Name ,  Country FROM member

Question: Show the names of members whose country is "United States" or "Canada".
SQL: SELECT Name FROM member WHERE Country  =  "United States" OR Country  =  "Canada"

Question: Show the different countries and the number of members from each.
SQL: SELECT Country ,  COUNT(*) FROM member GROUP BY Country

Question: Show the most common country across members.
SQL: SELECT Country FROM member GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1

Question: Which countries have more than two members?
SQL: SELECT Country FROM member GROUP BY Country HAVING COUNT(*)  >  2

Question: Show the leader names and locations of colleges.
SQL: SELECT Leader_Name ,  College_Location FROM college

Question: Show the names of members and names of colleges they go to.
SQL: SELECT T2.Name ,  T1.Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID

Question: Show the names of members and the locations of colleges they go to in ascending alphabetical order of member names.
SQL: SELECT T2.Name ,  T1.College_Location FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID ORDER BY T2.Name ASC

Question: Show the distinct leader names of colleges associated with members from country "Canada".
SQL: SELECT DISTINCT T1.Leader_Name FROM college AS T1 JOIN member AS T2 ON T1.College_ID  =  T2.College_ID WHERE T2.Country  =  "Canada"

Question: Show the names of members and the decoration themes they have.
SQL: SELECT T1.Name ,  T2.Decoration_Theme FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID

Question: Show the names of members that have a rank in round higher than 3.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID WHERE T2.Rank_in_Round  >  3

Question: Show the names of members in ascending order of their rank in rounds.
SQL: SELECT T1.Name FROM member AS T1 JOIN round AS T2 ON T1.Member_ID  =  T2.Member_ID ORDER BY Rank_in_Round ASC

Question: List the names of members who did not participate in any round.
SQL: SELECT Name FROM member WHERE Member_ID NOT IN (SELECT Member_ID FROM round)
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Faculty (
    FacID NUMBER PRIMARY KEY,
    Lname TEXT,
    Fname TEXT,
    Rank TEXT,
    Sex TEXT,
    Phone NUMBER,
    Room TEXT,
    Building TEXT
);
CREATE TABLE Department (
    DNO NUMBER PRIMARY KEY,
    Division TEXT,
    DName TEXT,
    Room TEXT,
    Building TEXT,
    DPhone NUMBER
);
CREATE TABLE Member_of (
    FacID NUMBER,
    DNO NUMBER,
    Appt_Type TEXT,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("FacID") REFERENCES Faculty("FacID")
);
CREATE TABLE Course (
    CID TEXT PRIMARY KEY,
    CName TEXT,
    Credits NUMBER,
    Instructor NUMBER,
    Days TEXT,
    Hours TEXT,
    DNO NUMBER,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("Instructor") REFERENCES Faculty("FacID")
);
CREATE TABLE Minor_in (
    StuID NUMBER,
    DNO NUMBER,
    FOREIGN KEY ("DNO") REFERENCES Department("DNO"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Enrolled_in (
    StuID NUMBER,
    CID TEXT,
    Grade TEXT,
    FOREIGN KEY ("Grade") REFERENCES Gradeconversion("lettergrade"),
    FOREIGN KEY ("CID") REFERENCES Course("CID"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Gradeconversion (
    lettergrade TEXT PRIMARY KEY,
    gradepoint NUMBER
);

Question: What are the names of departments either in division AS, or in division EN and in building NEB?
SQL: SELECT DName FROM DEPARTMENT WHERE Division  =  "AS" UNION SELECT DName FROM DEPARTMENT WHERE Division  =  "EN" AND Building  =  "NEB"

Question: Find the first name of students not enrolled in any course.
SQL: SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)

Question: What are the first names of all students that are not enrolled in courses?
SQL: SELECT Fname FROM STUDENT WHERE StuID NOT IN (SELECT StuID FROM ENROLLED_IN)
CREATE TABLE Songs (
    SongId NUMBER PRIMARY KEY,
    Title TEXT
);
CREATE TABLE Albums (
    AId NUMBER PRIMARY KEY,
    Title TEXT,
    Year NUMBER,
    Label TEXT,
    Type TEXT
);
CREATE TABLE Band (
    Id NUMBER PRIMARY KEY,
    Firstname TEXT,
    Lastname TEXT
);
CREATE TABLE Instruments (
    SongId NUMBER PRIMARY KEY,
    BandmateId NUMBER,
    Instrument TEXT,
    FOREIGN KEY ("BandmateId") REFERENCES Band("Id"),
    FOREIGN KEY ("SongId") REFERENCES Songs("SongId")
);
CREATE TABLE Performance (
    SongId NUMBER PRIMARY KEY,
    Bandmate NUMBER,
    StagePosition TEXT,
    FOREIGN KEY ("Bandmate") REFERENCES Band("Id"),
    FOREIGN KEY ("SongId") REFERENCES Songs("SongId")
);
CREATE TABLE Tracklists (
    AlbumId NUMBER PRIMARY KEY,
    Position NUMBER,
    SongId NUMBER,
    FOREIGN KEY ("AlbumId") REFERENCES Albums("AId"),
    FOREIGN KEY ("SongId") REFERENCES Songs("SongId")
);
CREATE TABLE Vocals (
    SongId NUMBER PRIMARY KEY,
    Bandmate NUMBER,
    Type TEXT,
    FOREIGN KEY ("Bandmate") REFERENCES Band("Id"),
    FOREIGN KEY ("SongId") REFERENCES Songs("SongId")
);

Question: Find the number of songs in all the studio albums.
SQL: SELECT count(DISTINCT T3.title) FROM albums AS T1 JOIN tracklists AS T2 ON T1.aid  =  T2.albumid JOIN songs AS T3 ON T2.songid  =  T3.songid WHERE t1.type  =  "Studio"

Question: How many songs appear in studio albums?
SQL: SELECT count(DISTINCT T3.title) FROM albums AS T1 JOIN tracklists AS T2 ON T1.aid  =  T2.albumid JOIN songs AS T3 ON T2.songid  =  T3.songid WHERE t1.type  =  "Studio"
CREATE TABLE Problem_Category_Codes (
    problem_category_code TEXT PRIMARY KEY,
    problem_category_description TEXT
);
CREATE TABLE Problem_Log (
    problem_log_id NUMBER PRIMARY KEY,
    assigned_to_staff_id NUMBER,
    problem_id NUMBER,
    problem_category_code TEXT,
    problem_status_code TEXT,
    log_entry_date TIME,
    log_entry_description TEXT,
    log_entry_fix TEXT,
    other_log_details TEXT,
    FOREIGN KEY ("problem_status_code") REFERENCES Problem_Status_Codes("problem_status_code"),
    FOREIGN KEY ("problem_id") REFERENCES Problems("problem_id"),
    FOREIGN KEY ("assigned_to_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("problem_category_code") REFERENCES Problem_Category_Codes("problem_category_code")
);
CREATE TABLE Problem_Status_Codes (
    problem_status_code TEXT PRIMARY KEY,
    problem_status_description TEXT
);
CREATE TABLE Product (
    product_id NUMBER PRIMARY KEY,
    product_name TEXT,
    product_details TEXT
);
CREATE TABLE Staff (
    staff_id NUMBER PRIMARY KEY,
    staff_first_name TEXT,
    staff_last_name TEXT,
    other_staff_details TEXT
);
CREATE TABLE Problems (
    problem_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    closure_authorised_by_staff_id NUMBER,
    reported_by_staff_id NUMBER,
    date_problem_reported TIME,
    date_problem_closed TIME,
    problem_description TEXT,
    other_problem_details TEXT,
    FOREIGN KEY ("reported_by_staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("product_id") REFERENCES Product("product_id"),
    FOREIGN KEY ("closure_authorised_by_staff_id") REFERENCES Staff("staff_id")
);

Question: Find the products which have problems reported by both Lacey Bosco and Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"

Question: Which products have problems reported by both the staff named Lacey Bosco and the staff named Kenton Champlin?
SQL: SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Lacey" AND T3.staff_last_name = "Bosco" INTERSECT SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 JOIN staff AS T3 ON T1.product_id = T2.product_id AND T1.reported_by_staff_id = T3.staff_id WHERE T3.staff_first_name = "Kenton" AND T3.staff_last_name = "Champlin"