SQL query building doubt

Question

0.00/5 (No votes)

See more:

1.customer(cus_no,cus_name)
2.membership(mem_no, cus_no)
3.cassette(cass_no,cass_name,language)
4.Issue(iss_no,iss_date,mem_no,cass_no)

1) list all customer names with their mem_no
2) list all issues for current_date with cust_name and cass_name;
3) list details of customer who borrowed the cassette name"The Legend"
4) gice a count of how many cassettes have been borrowed by each customer

I have tried is this correct, if not correct me and please also give a suggestion to improve my sql query or any shortcuts. I also cant find answer for 3rd one list details of cutomer who borrowed the cassette name the legend .. Please help me with that also

1. select c.cus_name, m.cus_no from customer c, member m where m.cus_no=c.cus_no;

2. declare @curdate=sysdate;
select i.issno,ca.cass_name,c.cus_name from issue i, cassette ca, customer c, membership m where ca.mem_no=m.mem_no and m.cus_no=c.cus_no and i.iss_date=curdate;

4. select (*) count,c.cus_name from issue i , membership m, customer c where i.mem_no=m.mem_no and c.cus_no=m.cus_no order by c.cus_name;

Posted 6-Apr-13 21:14pm

Member 8780842

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-04-06T21:28:00

Solution 1

This is you homework, so I won't give you all the answers! Just the one to fix where you have tried to do it:
1) I would probably use a JOIN - you have to to return values from two or more tables - (and the name of a table and a field is wrong)

SQL

SELECT c.cus_name, m.mem_no FROM customer c
JOIN membership m ON m.cus_no = c.Cus_no

2) Again, you need a JOIN or more.

3) JOIN again, but this time with a WHERE clause looking at the cass_name column

4) Wrong syntax! JOIN again, but the COUNT syntax is "COUNT(*)" not "(*)COUNT"

Why not try it? You presumably have SQL server installed? Create your tables, populate your data and try your queries in Sql Server Management Studio!

Posted 6-Apr-13 21:28pm

OriginalGriff

Comments

Member 8780842 7-Apr-13 4:40am

(and the name of a table and a field is wrong)

cant understand this :(

OriginalGriff 7-Apr-13 4:57am

Look at your query:
select c.cus_name, m.cus_no from customer c, member m where m.cus_no=c.cus_no
And look at your tables:
customer(cus_no,cus_name)
membership(mem_no, cus_no)
To answer the question you were set
"list all customer names with their mem_no"
You need to return the "m.mem_no" field not "m.cus_no"
And if you look, your query uses a table called "member", where your definitions call it "membership" - you must get the names to match! :laugh:

Member 8780842 7-Apr-13 5:00am

thank you :)

OriginalGriff 7-Apr-13 5:19am

You're welcome!

Member 8780842 7-Apr-13 5:28am

in this query
SELECT c.cus_name, m.mem_no FROM customer c
JOIN membership m ON m.cus_no = c.Cus_no

can we change the order like this ?

SELECT c.cus_name, m.mem_no FROM membership m
JOIN customer c ON c.cus_no = m.Cus_no

OriginalGriff 7-Apr-13 5:34am

Yes.

Seriously - try it. Set up your tables, add some data, and try the queries in SSMS - it's generally a lot clearer what is going on if you have data to run against rather than trying to work it out in your head (plus SSMS will complain when you try to use a table or field that doesn't exist)

Member 8780842 7-Apr-13 5:44am

count(*) count ok what does * means ?

OriginalGriff 7-Apr-13 6:03am

It's a "wild card" - it means "all fields".
For example you could say
SELECT * FROM myTable
and it will return all columns and all rows (but it's a bad practice to do that, it wastes bandwidth and can mess up displays if the table is changed afterwards)
In the case of the COUNT function you are just saying "Count(all the rows)" - you could use
SELECT COUNT(*),c.cus_name FROM customer c
or
SELECT COUNT(c.cus_name),c.cus_name FROM customer c
and get the same result.

Member 8780842 8-Apr-13 8:19am

Thank you so much i did my exam well :) :) :) thank youuuuuuuuuu so much :)))))))))))))

OriginalGriff 8-Apr-13 9:26am

You're welcome!
Congratulations!