Consider the following schema Customers CustomerNumber Compa

Consider the following schema:

Customers: (CustomerNumber, CompanyName, ContactFirstName, ContactLastName, Phone, AddressLine1, AddressLine2, City, State, Country, PostalCode, SalesEmployeeNumber, CreditLimit)

Employees: (EmployeeNumber, LastName, FirstName, Extension, Email, OfficeCode, ReportTo, JobTitle)

Office: (EmployeeNumber, LastName, FirstName, Extension, Email, OfficeCode, ReportTo, JobTitle)

Answer the questions listed below based on sample database linked above. These are SQL problems. Queries should execute properly on server setup described above. The queries should generate the correct information for any instance of the sample database.p.s do not use Limit

1. What are the officeCodes, cities and countries of all offices that don\'t have a state value?

2. For each Sales Rep, give me a table with their name (First and Last) and the number of clients they service.

3. What are the full names of customer contacts with the initials J.B. or M.B. (provide the names by First then Last in alphabetical order by last name)?

Solution

1)select distinct o.officecode,c.city,c.country from Office o,Customer c where o.officecode=c.customerNumber and c.state=NULL 2)select e.employeenumber,e.firstname,e.lastname,count(c.SalesEmployeeNumber) as NoOfClients from Employee e,Customer c where e.employeeNumber=c.SalesEmployeeNumber groupby(c.salesemployeenumber) 3)select c.ContactFirstname,c.ContactLastname from Customer c where c.CompanyName like \'J.B%\' OR c.CompanyName like \'M.B%\' sort by c.ContactLastName asc;