1.
select distinct employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Northern'
['employees', 'employeeterritories', 'territories', 'region']
employeeterritories and territories ,region tables did not existed in db
select distinct employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern' and employees.reportsto = ( select employeeid from employees where firstname = 'Andrew' and lastname = 'Fuller' )
sql using ['employees', 'employeeterritories', 'territories', 'region'] tables,
'employeeterritories', 'territories', 'region' tables did not existed in db
select count(territoryid) from territories
territories in sql , but territories did not existed in db
select region.regiondescription from territories as territories inner join region as region on territories.regionid = region.regionid where territories.territorydescription = 'Hoffman Estates'
'territories', 'region' tables did not existed in db
select order details.quantity from products as products inner join order details as order details on order details.productid = products.productid where products.productname = 'Manjimup Dried Apples' order by order details.quantity desc limit 1
order details should be orderDetails
select distinct territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern'
'territories', 'region' did not existed in db
select products.productname from orders as orders inner join order details as order details on orders.orderid = order details.orderid inner join products as products on order details.productid = products.productid where orders.shipaddress = 'Starenweg 5' group by products.productname
order details table should be orderDetails
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.lastname = 'King' and employees.firstname = 'Robert'
'employeeterritories', 'territories' did not existed
select companyname from sales totals by amount order by saleamount desc limit 10
sales totals by amount did not existed
10
select avg(productsales) from sales by category group by categoryname
sales by category did not existed
11.
select sum(order details.quantity) from products as products inner join order details as order details on products.productid = order details.productid where products.productname LIKE 'Uncle Bob%s Organic Dried Pears'
order details should be orderDetails
12
select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employeeterritories.employeeid < 4
employeeterritories did not existed
13.
select count(distinct employees.firstname) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
'employeeterritories', 'territories', 'region' did not existed
14.
select customers.contactname, customers.phone from customers as customers inner join orders as orders on customers.customerid = orders.customerid inner join order details as order details on orders.orderid = order details.orderid group by orders.orderid, customers.contactname, customers.phone order by sum(order details.unitprice order details.quantity (1 - order details.discount)) desc limit 1
order details should be orderDetails
15.
select count(territories.regionid) from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
'territories', 'region' did not existed
16.
select sum(order details.unitprice * order details.quantity) from products as products inner join order details as order details on products.productid = order details.productid where products.discontinued = 1
order details should be orderDetails
17.
select count(products.productid) from categories as categories inner join products as products on categories.categoryid = products.categoryid inner join order details as order details on products.productid = order details.productid inner join orders as orders on order details.orderid = orders.orderid where categories.categoryname = 'Condiments' and categories.categoryid = 2 and orders.orderdate LIKE '1997%'
order details should be orderDetails
18.
select customers.companyname from customers as customers inner join orders as orders on customers.customerid = orders.customerid inner join order details as order details on orders.orderid = order details.orderid group by customers.companyname order by count(order details.productid) desc limit 1
order details should be orderDetails
19.
select count(region.regiondescription), territories.territorydescription, count(*) as num from territories as territories inner join region as region on territories.regionid = region.regionid group by territories.territorydescription order by num desc limit 1
'territories', 'region' did not existed
20.
select count(eid) from ( select employees.employeeid as eid from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.titleofcourtesy IN ('Ms.' or 'Mrs.') group by employees.employeeid having count(employeeterritories.territoryid) >= 3 ) employees
employeeterritories did not existed
21.
select territoryid from employeeterritories where employeeid = 7
employeeterritories did not existed
22.
select territories.territoryid, territories.territorydescription, region.regiondescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where employees.titleofcourtesy = 'Mrs.' and employees.lastname = 'Peacock' and employees.firstname = 'Margaret'
'employeeterritories', 'territories', 'region' did not existed
23.
select territories.territoryid, territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern'
'territories', 'region' did not existed
24.
select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where territories.territorydescription = 'Denver'
'employeeterritories', 'territories' did not existed
25.
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.firstname = 'Laura' and employees.lastname = 'Callahan'
'employeeterritories', 'territories' did not existed
26.
select count(territoryid) from territories where regionid = 1
select count(territorydescription) from territories where regionid IN (1, 2, 3, 4) group by regionid
territories did not existed
27.
select personphone.businessentityid from phonenumbertype as phonenumbertype inner join personphone as personphone on phonenumbertype.phonenumbertypeid = personphone.phonenumbertypeid where phonenumbertype.name = 'Cell' limit 3
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid group by phonenumbertype.name order by count(phonenumbertype.name) desc
select personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid where person.firstname = 'Gerald' and person.lastname = 'Patel'
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype using (phonenumbertypeid) where personphone.businessentityid = 13626
select cast(sum(case when phonenumbertype.name = 'Cell' then 1 else 0 end) as REAL) * 100 / count(phonenumbertype.name) from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid where personphone.phonenumber = '114-555-0100'
select modifieddate from personphone where phonenumber = '1 (11) 500 555-0143'
select person.firstname, person.lastname, personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid inner join employee as employee on person.businessentityid = employee.businessentityid where employee.jobtitle = 'Marketing Manager'
select personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid where person.persontype = 'SC'
select phonenumber from personphone where businessentityid = 12597
'personphone' did not existed
28.
select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where territories.territorydescription = 'Hollis'
select count(employeeterritories.territoryid) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.firstname = 'Nancy' and employees.lastname = 'Davolio'
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.firstname = 'Nancy' and employees.lastname = 'Davolio'
select count(employeeterritories.territoryid) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.city = 'London'
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.reportsto = ( select employeeid from employees where firstname = 'Andrew' and lastname = 'Fuller' )
select count(counteid) from ( select employees.employeeid as counteid from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.country = 'UK' group by employees.employeeid having count(employeeterritories.territoryid) > 4 ) employees
select count(territories.territoryid) from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
select distinct territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
select count(t.employeeid) from ( select employeeterritories.employeeid from region as region inner join territories as territories on region.regionid = territories.regionid inner join employeeterritories as employeeterritories on territories.territoryid = employeeterritories.territoryid where region.regiondescription = 'Eastern' group by employeeterritories.employeeid ) t
employeeterritories did not existed
29.
select companyname from sales totals by amount order by saleamount desc limit 10
sales totals by amount
sales totals by amount did not existed
30.
select companyname from customer and suppliers by city where city = ( select city from customer and suppliers by city group by city order by count(relationship) desc limit 1 )
1. select distinct employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Northern' ['employees', 'employeeterritories', 'territories', 'region'] employeeterritories and territories ,region tables did not existed in db
select distinct employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern' and employees.reportsto = ( select employeeid from employees where firstname = 'Andrew' and lastname = 'Fuller' ) sql using ['employees', 'employeeterritories', 'territories', 'region'] tables, 'employeeterritories', 'territories', 'region' tables did not existed in db
select count(territoryid) from territories territories in sql , but territories did not existed in db
select region.regiondescription from territories as territories inner join region as region on territories.regionid = region.regionid where territories.territorydescription = 'Hoffman Estates' 'territories', 'region' tables did not existed in db
select order details.quantity from products as products inner join
order detailsas order details on order details.productid = products.productid where products.productname = 'Manjimup Dried Apples' order by order details.quantity desc limit 1 order details should be orderDetailsselect distinct territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern' 'territories', 'region' did not existed in db
select products.productname from orders as orders inner join
order detailsas order details on orders.orderid = order details.orderid inner join products as products on order details.productid = products.productid where orders.shipaddress = 'Starenweg 5' group by products.productname order details table should be orderDetailsselect territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.lastname = 'King' and employees.firstname = 'Robert' 'employeeterritories', 'territories' did not existed
select companyname from
sales totals by amountorder by saleamount desc limit 10 sales totals by amount did not existed10 select avg(productsales) from
sales by categorygroup by categoryname sales by category did not existed11. select sum(order details.quantity) from products as products inner join
order detailsas order details on products.productid = order details.productid where products.productname LIKE 'Uncle Bob%s Organic Dried Pears' order details should be orderDetails12 select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employeeterritories.employeeid < 4
employeeterritories did not existed
13. select count(distinct employees.firstname) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
'employeeterritories', 'territories', 'region' did not existed
14. select customers.contactname, customers.phone from customers as customers inner join orders as orders on customers.customerid = orders.customerid inner join
order detailsas order details on orders.orderid = order details.orderid group by orders.orderid, customers.contactname, customers.phone order by sum(order details.unitprice order details.quantity (1 - order details.discount)) desc limit 1 order details should be orderDetails15. select count(territories.regionid) from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern' 'territories', 'region' did not existed
16. select sum(order details.unitprice * order details.quantity) from products as products inner join
order detailsas order details on products.productid = order details.productid where products.discontinued = 1order details should be orderDetails
17. select count(products.productid) from categories as categories inner join products as products on categories.categoryid = products.categoryid inner join
order detailsas order details on products.productid = order details.productid inner join orders as orders on order details.orderid = orders.orderid where categories.categoryname = 'Condiments' and categories.categoryid = 2 and orders.orderdate LIKE '1997%'order details should be orderDetails
18. select customers.companyname from customers as customers inner join orders as orders on customers.customerid = orders.customerid inner join
order detailsas order details on orders.orderid = order details.orderid group by customers.companyname order by count(order details.productid) desc limit 1order details should be orderDetails
19. select count(region.regiondescription), territories.territorydescription, count(*) as num from territories as territories inner join region as region on territories.regionid = region.regionid group by territories.territorydescription order by num desc limit 1
'territories', 'region' did not existed
20. select count(eid) from ( select employees.employeeid as eid from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.titleofcourtesy IN ('Ms.' or 'Mrs.') group by employees.employeeid having count(employeeterritories.territoryid) >= 3 ) employees
employeeterritories did not existed
21. select territoryid from employeeterritories where employeeid = 7
employeeterritories did not existed
22. select territories.territoryid, territories.territorydescription, region.regiondescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid inner join region as region on territories.regionid = region.regionid where employees.titleofcourtesy = 'Mrs.' and employees.lastname = 'Peacock' and employees.firstname = 'Margaret'
'employeeterritories', 'territories', 'region' did not existed
23. select territories.territoryid, territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Southern'
'territories', 'region' did not existed
24. select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where territories.territorydescription = 'Denver'
'employeeterritories', 'territories' did not existed
25. select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.firstname = 'Laura' and employees.lastname = 'Callahan'
'employeeterritories', 'territories' did not existed
26. select count(territoryid) from territories where regionid = 1
select count(territorydescription) from territories where regionid IN (1, 2, 3, 4) group by regionid
territories did not existed
27. select personphone.businessentityid from phonenumbertype as phonenumbertype inner join personphone as personphone on phonenumbertype.phonenumbertypeid = personphone.phonenumbertypeid where phonenumbertype.name = 'Cell' limit 3
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid group by phonenumbertype.name order by count(phonenumbertype.name) desc
select personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid where person.firstname = 'Gerald' and person.lastname = 'Patel'
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype using (phonenumbertypeid) where personphone.businessentityid = 13626
select cast(sum(case when phonenumbertype.name = 'Cell' then 1 else 0 end) as REAL) * 100 / count(phonenumbertype.name) from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid
select phonenumbertype.name from personphone as personphone inner join phonenumbertype as phonenumbertype on personphone.phonenumbertypeid = phonenumbertype.phonenumbertypeid where personphone.phonenumber = '114-555-0100'
select modifieddate from personphone where phonenumber = '1 (11) 500 555-0143'
select person.firstname, person.lastname, personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid inner join employee as employee on person.businessentityid = employee.businessentityid where employee.jobtitle = 'Marketing Manager'
select personphone.phonenumber from person as person inner join personphone as personphone on person.businessentityid = personphone.businessentityid where person.persontype = 'SC'
select phonenumber from personphone where businessentityid = 12597
'personphone' did not existed
28. select employees.firstname, employees.lastname from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where territories.territorydescription = 'Hollis'
select count(employeeterritories.territoryid) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.firstname = 'Nancy' and employees.lastname = 'Davolio'
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.firstname = 'Nancy' and employees.lastname = 'Davolio'
select count(employeeterritories.territoryid) from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.city = 'London'
select territories.territorydescription from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid inner join territories as territories on employeeterritories.territoryid = territories.territoryid where employees.reportsto = ( select employeeid from employees where firstname = 'Andrew' and lastname = 'Fuller' )
select count(counteid) from ( select employees.employeeid as counteid from employees as employees inner join employeeterritories as employeeterritories on employees.employeeid = employeeterritories.employeeid where employees.country = 'UK' group by employees.employeeid having count(employeeterritories.territoryid) > 4 ) employees
select count(territories.territoryid) from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
select distinct territories.territorydescription from territories as territories inner join region as region on territories.regionid = region.regionid where region.regiondescription = 'Eastern'
select count(t.employeeid) from ( select employeeterritories.employeeid from region as region inner join territories as territories on region.regionid = territories.regionid inner join employeeterritories as employeeterritories on territories.territoryid = employeeterritories.territoryid where region.regiondescription = 'Eastern' group by employeeterritories.employeeid ) t
employeeterritories did not existed
29. select companyname from
sales totals by amountorder by saleamount desc limit 10 sales totals by amountsales totals by amount did not existed
30. select companyname from
customer and suppliers by citywhere city = ( select city fromcustomer and suppliers by citygroup by city order by count(relationship) desc limit 1 )customer and suppliers by city did not existed