How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Raj Your Own Question

Raj
Raj, Computer Engg.
Category: Programming
Satisfied Customers: 1267
Experience:  BE CS, 4+ Experience in Programming and Database (ERP)
16752232
Type Your Programming Question Here...
Raj is online now
A new question is answered every 9 seconds

1. Generate a query that lists all company names of customers

Resolved Question:

1.     Generate a query that lists all company names of customers who placed one or more orders during November 1996 (Hint: JOIN on Orders and Customers table).
(Insert screenshot)
2.     Generate a list of customers (company name, contact name, and phone) that have had an order taken by Andrew Fuller. (Inner join between Customers, Orders, and Employees).
(Insert screenshot)
5.     Generate a list of employees (last name and first name) and their supervisors (last name and first name of supervisor aliased to ReportsToLastName and ReportsToFirstName) (self-join on Employees table). (Hints: When joining a table to itself, you must use table aliases. The ReportsTo column of Employees is the supervisor's EmployeeID.)
(Insert Screenshot)
6.     Generate a list of products (ProductName) that have never been ordered (outer join between [Order Details] and Products).
Submitted: 5 years ago.
Category: Programming
Expert:  Raj replied 5 years ago.
Ok so you need SQL queries am i right?
Customer: replied 5 years ago.
Yes, SQL 2008
Expert:  Raj replied 5 years ago.
I am done with three
check whether this works or not:

1.
SELECT c.companyname
FROM orders o JOIN customers c
WHERE o.dat BETWEEN '01/11/1996' AND '30/11/1996'

2.
SELECT e.companyname, c.contactname, c.phone
FROM customers c JOIN orders o JOIN employees e
ON o.ordersby = 'Andrew Fuller'

3.
SELECT lastname,firstname,reportsto.lastname,reportsto.firstname
FROM employees, employees reportsto
WHERE employeeID = reportsto.employeeID

4.
SELECT p.code,productname
FROM products p LEFT OUTER JOIN orders o
WHERE p.code NOT IN (o.code)
Raj, Computer Engg.
Category: Programming
Satisfied Customers: 1267
Experience: BE CS, 4+ Experience in Programming and Database (ERP)
Raj and other Programming Specialists are ready to help you
Expert:  Raj replied 5 years ago.
sorry wherever WHERE is not working type ON
Customer: replied 5 years ago.
I need to add this as the reply

Each one errors out    1. Msg 156, Level 15, State 1, Line 3 Incorrect
syntax near the keyword 'WHERE'.    2. Msg 102, Level 15,
State 1, Line 3 Incorrect syntax near 'Andrew Fuller'.    3.
Msg 209, Level 16, State 1, Line 3 Ambiguous column name
'employeeID'. Msg 209, Level 16, State 1, Line 1 Ambiguous
column name 'lastname'. Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'firstname'.    4. Msg 156, Level 15,
State 1, Line 3 Incorrect syntax near the keyword 'WHERE'.
Expert:  Raj replied 5 years ago.
Ok can you post the table attribute names and table names in details......
That will help a bit.
In 1st replace WHERE by ON
Customer: replied 5 years ago.
Can I email you a screen shot of a copy of the DB?
Expert:  Raj replied 5 years ago.
Ok wait

Go on website

www.rapidshare.com

Upload that screenshot on that site...then it will give you a link.
post that link here
Customer: replied 5 years ago.
Each one errors out 1. Msg 156, Level 15, State 1, Line 3 Incorrect
syntax near the keyword 'WHERE'. 2. Msg 102, Level 15,
State 1, Line 3 Incorrect syntax near 'Andrew Fuller'. 3.
Msg 209, Level 16, State 1, Line 3 Ambiguous column name
'employeeID'. Msg 209, Level 16, State 1, Line 1 Ambiguous
column name 'lastname'. Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'firstname'. 4. Msg 156, Level 15,
State 1, Line 3 Incorrect syntax near the keyword 'WHERE'.
Expert:  Raj replied 5 years ago.
ok type this and send the screen shot of results if possible saperately(its for table structure):

sp_help orders

sp_help customers

sp_help employees

sp_help products

sp_help order details

i need total 5 screen shots if possible
Expert:  Raj replied 5 years ago.
1st querry can't be processed because there is nothing known as companyname inside customers table as well as orders table
then how can i get company name in query?
Expert:  Raj replied 5 years ago.
I think 3rd query should run now
even in employee table you should also have supervisorid,supervisorlastname and supervisorfirstname columns and employeeid,employeefirstname and employeelastname to get a better/best result.
with this:

SELECT e.employeeid,e.lastname,e.firstname,reportsto.lastname,reportsto.firstname
FROM employees e, employees reportsto
WHERE e.employeeID = reportsto.employeeID
Expert:  Raj replied 5 years ago.
SELECT p.productid,productname
FROM products p LEFT OUTER JOIN orders o
WHERE p.productid NOT IN (o.productid)

this should work
Expert:  Raj replied 5 years ago.
and regarding 2nd query......
there is no name column present to compare the name of Andrew Fuller.as well as no common column in between employees & customers
as well as employees & orders then how can we perform join on these tables
Customer: replied 5 years ago.
1.     Create a view that includes the city, last name, and first name of the employees. Name the view emp_vu1.
(Insert screenshot)
2.     Create a view that shows the Product name and category name of every product. Name the view prod_cat_vu (Hint – this view will include an inner join).
(Insert screenshot)
3.     Create a view that only shows employees who have a last name that begins with the letter ‘M’. Name the view m_emp_vu.
(Insert screenshot)
4.     Create a view (with the WITH CHECK OPTION clause) that comprises the first and last names of all customers whose country is Mexico. Name this view mexico_cust_vu.
(Insert screenshot)
5.     Attempt to change Ana Trujillo’s country to USA – what happens? Why?
(Insert Screenshot)
6.     Delete all of the views (be sure to show your work)
(Insert Screenshot)
Expert:  Raj replied 5 years ago.
are we suppose to use the same table or you have created new tables in database??
If new tables then post its structure using sp_help <table name>
if old tables then:
1. city is missing in employee(or whether address contains city name?)

2. post the screen shot of categories table using:

sp_help categories
Expert:  Raj replied 5 years ago.
If you are going to use the old structure that you sent to me then first add

two columns to your table which are not present using the below query:

1. To add 'country' column to 'employees' table

ALTER TABLE employees
ADD country varchar(20) not null

2. To add 'city' column to 'employees' table

ALTER TABLE employees
ADD city varchar(20) not null

After this try to add fresh entries in table employee and then execute the

below queries to get the result needed......

SOLUTION:

1.
CREATE VIEW emp_vu1 AS
SELECT lastname, firstname, city
FROM employees

2.
CREATE VIEW prod_cat_vu AS
SELECT p.productname, c.categoryname FROM products p
JOIN categories c ON
p.categoryID = c.categoryID

3.
CREATE VIEW m_emp_vu AS
SELECT *
FROM employees
WHERE left(lastname,1) = 'M'

4.
CREATE VIEW mexico_cust_vu AS
SELECT firstname, lastname
FROM employees
WHERE country = "Mexico"
WITH CHECK

5.
UPDATE mexico_cust_vu
SET country = 'USA'
WHERE firstname = 'Ana' AND lastname = 'Trujillo'

ERROR (^): In above case you will receave an error because the view

mexico_cust_vu only contains 'firstname' and 'lastname' of employees then

how can it update the value of country when that column is not present in

the view....

6. To delete view with all the associated database objects use DROP VIEW

<view name>

DROP VIEW emp_vu1
DROP VIEW prod_cat_vu
DROP VIEW m_emp_vu
DROP VIEW mexico_cust_vu
Raj, Computer Engg.
Category: Programming
Satisfied Customers: 1267
Experience: BE CS, 4+ Experience in Programming and Database (ERP)
Raj and other Programming Specialists are ready to help you
Customer: replied 5 years ago.
1.     Write a subquery which finds all of the employees who live in the same city as Steven Buchanan.
(Insert screenshot)
2.     Write a subquery which displays all of the employee lastname who do not have the same title as Nancy Davolio.
(Insert screenshot)
3.     List all of the data for the employees who do have the same title of courtesy as XXXXX XXXXX.
(Insert screenshot)
4.     Write a subquery that shows all of the products that were shipped via the same method as OrderID 10248.
(Insert screenshot)
5.     Display all of the supplier information from the suppliers which are from the same country as Norske Meierier.
(Insert Screenshot)
Customer: replied 5 years ago.
Taj,

How can I pay you for this one and the last one without closing out the question?? I have two more after this one to do.
Customer: replied 5 years ago.
I need this one tonight as well Raj.

1.For each city, how many customers does the database have?
(Insert screenshot)
2.How many orders has each employee handled? Show the employeeID and the number of orders.
(Insert screenshot)
3.How many orders has each employee that lives in Seattle handled?
(Insert screenshot)
4.How many employees have handled at least 2 orders? Show the number of employees.
(Insert screenshot)
5.Who is the employee who handled the most number of orders? Show employee id and # XXXXX orders.
(Insert Screenshot)
Expert:  Raj replied 5 years ago.
you didn't accepted my previous answer........
Customer: replied 5 years ago.
I just accepted and left you a bonus. Please respond to these two new ones as soon as you can, thanks!
Customer: replied 5 years ago.
Raj, will you be able to do these two new ones, or should I ask another expert? I really need it soon, and since you have all the info, I would rather that you do it. Thanks!
Expert:  Raj replied 5 years ago.
Ok
I will help you out.......

Did you did what i posted in above queries(i.e. did you add the city column in the employee table)?

Because we will need it.......

I will post queries in few min.

If your tables have changed then please post again the pics of what you get by:

sp_help orders

sp_help customers

sp_help employees

sp_help products

sp_help order details

-- specially i need this one because i don't have table structure for suppliers
sp_ help suppliers
Customer: replied 5 years ago.
Yes, I added those two, and nothing else has changed.
Expert:  Raj replied 5 years ago.
OK
I will need two more tables to answer query number 4 and 5

sp_help shippers

sp_help suppliers

and query no. 4 can't work because no method attribute is present in products

so to add a method in table use the following query:

ALTER TABLE products
ADD method varchar(20) not null

I am working and am done with 3 queries
just post what i requested then i will be finished with you queries and will post it........
Customer: replied 5 years ago.
When I add that command, I get this.

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'method' cannot be added to non-empty table 'products' because it does not satisfy these conditions.

Here are the screen shots

http://depositfiles.com/files/rysazuca8
Expert:  Raj replied 5 years ago.
Ok try this:

ALTER TABLE products
ADD method varchar(20)
Expert:  Raj replied 5 years ago.
and also wanted to ask in suppliers table......
Have you stored the country name in region attribute?
Expert:  Raj replied 5 years ago.
I am really sorry actually it is to be added in order_details table as:

ALTER TABLE order_details
ADD method varchar(20)

and then you will have to enter data for methods then only 4th query can run

Expert:  Raj replied 5 years ago.
Here is the Answer:


1. Write a subquery which finds all of the employees who live in the same city as Steven Buchanan.


SELECT *
FROM employees
WHERE city =
(SELECT city
FROM employees
WHERE firstname='Steven' AND lastname='Buchanan')


2. Write a subquery which displays all of the employee lastname who do not have the same title as Nancy Davolio.


SELECT lastname
FROM employees
WHERE title !=
(SELECT title
FROM employees
WHERE firstname='Nancy' AND lastname='Davolio')


3. List all of the data for the employees who do have the same title of courtesy as XXXXX XXXXX.

SELECT *
FROM employees
WHERE titleofcourtesy =
(SELECT titleofcourtesy
FROM employees
WHERE firstname='Robert' AND lastname='King')


4.Write a subquery that shows all of the products that were shipped via the same method as OrderID 10248.


SELECT p.productid,p.productname
FROM products p JOIN order_details o
WHERE o.method =
(SELECT method
FROM products
WHERE orderid=1024)


5. Display all of the supplier information from the suppliers which are from the same country as Norske Meierier.

SELECT *
FROM suppliers
WHERE region =
(SELECT region
FROM employees
WHERE contactname='Norske Meierier')
Raj, Computer Engg.
Category: Programming
Satisfied Customers: 1267
Experience: BE CS, 4+ Experience in Programming and Database (ERP)
Raj and other Programming Specialists are ready to help you
Customer: replied 5 years ago.
Should I accept now, or wait until you do the second one?
Customer: replied 5 years ago.
Raj,

I paid for the first set of queries, when you think you could do the second set??
Expert:  Raj replied 5 years ago.
i will post it in about an hr or 2
Expert:  Raj replied 5 years ago.
I am facing problems in second set.........so will need some information before continuing with it

for Query no. 2:
Is there any table in which EmployeeID and order taken by him is present.?

For Query no 3:
For this at any perticular place we should have Employee, town and orders taken by him.

For Query 4 & 5:
same above information is needed

Expert:  Raj replied 5 years ago.
if possible then can you send me the o/p of this:

sp_help orders1

I think to run this second set you will need following columns in table:
employeeID
orders
EmployeeName
city/town
Customer: replied 5 years ago.
Here is a copy of the DB so you can see everything

http://depositfiles.com/files/k733g9myb
Expert:  Raj replied 5 years ago.
No i can't
My friend have taken my laptop for tonight so can't have a look at it

and my home PC don't have SQL software on it

I will give you queries later......
will it do?
Customer: replied 5 years ago.
Yes, that is fine. Thank you!
Customer: replied 5 years ago.
Raj, any word on this?
Expert:  Raj replied 5 years ago.
I am done with queries but to execute them you will have to add some columns to your tables and then you will have to add fresh entries in those tables..........Will that be fine with you??

I still haven't got my laptop so was not able to view your database.........I am really sorry for that

Customer: replied 5 years ago.
I will try to add whatever you want....give me both the additions and the queries, thanks.
Expert:  Raj replied 5 years ago.
I will suggest to create a new table with name "ORDERTAKEN" using:
CREATE TABLE ordertaken
(
employeeid varchar(4) not null,
firstname varchar(15),
lastname varchar(15),
productid int,
city varchar(15)
)

-> add foreign key to productid using following query:

ALTER TABLE ordertaken
ADD CONSTRAINT fkproductid FOREIGN KEY(productid)
REFERENCES products(productid)

-> Now enter data's in this table(i.e. ordertaken)

INSERT ordertaken
VALUES('E001','John','Smith','TEST 1','ANY')

NOTE:
Use above format to enter data
1. In place of 'TEST 1' Enter any value from table 'product' table, And in

place of 'ANY' type any city name but for some records use 'Seattle' which

will be needed to execute query no. 3

If you face any problem then do send me the screenshot of following:

SELECT productid
FROM products

then i can send you what values to insert exactly


1.For each city, how many customers does the database have?
SELECT town,COUNT(town) AS 'Number Of Customers'
FROM customers
GROUP BY town

2.How many orders has each employee handled? Show the employeeID and the

number of orders.
SELECT employeeID,COUNT(productid) AS 'Number Of Orders'
FROM orders
GROUP BY employeeID

3.How many orders has each employee that lives in Seattle handled?
SELECT firstname,lastname,COUNT(productid) AS 'Number Of Orders'
FROM orders
WHERE city = 'Seattle'
GROUP BY employeeid

4.How many employees have handled at least 2 orders? Show the number of

employees.
SELECT COUNT(employeeid) AS 'Number of Employees'
FROM orders
WHERE COUNT(productid)>=2

5.Who is the employee who handled the most number of orders? Show employee

id and # XXXXX orders.
SELECT employeeid,max(COUNT(productid)) AS 'NUMBER OF ORDERS'
FROM orders
Raj, Computer Engg.
Category: Programming
Satisfied Customers: 1267
Experience: BE CS, 4+ Experience in Programming and Database (ERP)
Raj and other Programming Specialists are ready to help you
Expert:  Raj replied 5 years ago.
Sorry i forgot to change the table name in the queries so the Queries will be as follows:


1.For each city, how many customers does the database have?
SELECT town,COUNT(town) AS 'Number Of Customers'
FROM customers
GROUP BY town

2.How many orders has each employee handled? Show the employeeID and the

number of orders.
SELECT employeeID,COUNT(productid) AS 'Number Of Orders'
FROM ordertaken
GROUP BY employeeID

3.How many orders has each employee that lives in Seattle handled?
SELECT firstname,lastname,COUNT(productid) AS 'Number Of Orders'
FROM ordertaken
WHERE city = 'Seattle'
GROUP BY employeeid

4.How many employees have handled at least 2 orders? Show the number of

employees.
SELECT COUNT(employeeid) AS 'Number of Employees'
FROM ordertaken
WHERE COUNT(productid)>=2

5.Who is the employee who handled the most number of orders? Show employee

id and # XXXXX orders.
SELECT employeeid,max(COUNT(productid)) AS 'NUMBER OF ORDERS'
FROM ordertaken

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7463
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5603
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/unvadim/2010-11-15_210218_avatar.jpg unvadim's Avatar

    unvadim

    Computer Software Engineer

    Satisfied Customers:

    1158
    Good knowledge of OOP principles. 3+ years of programming experience with Java and C++. Sun Certified Java Programmer 5.0.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    950
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    864
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/RA/rajivsharma086/2012-6-6_17128_displaypic.64x64.jpg Raj's Avatar

    Raj

    Computer Engg.

    Satisfied Customers:

    860
    BE CS, 4+ Experience in Programming and Database (ERP)
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    590
    11 yrs of programming (PHP, WordPress, XSL, SQL, JavaScript)
 
 
 

Related Programming Questions

Chat Now With A Programmer
Raj
Raj
SAP ABAP Consultant
904 Satisfied Customers
BE CS, 4+ Experience in Programming and Database (ERP)