SQL查询构建疑问 [英] SQL query building doubt

问题描述

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）列出所有客户名称及其mem_no

2）列出current_date与cust_name和cass_name的所有问题;

3）列出借用卡片名称The Legend的客户的详细信息

4 ）gice计算每个客户借了多少个磁带







我试过的是这是正确的，如果不正确我也请提出改进​​我的SQL查询或任何快捷方式的建议。我也找不到第3个清单的详细信息，那个借用卡片名称传说的cutomer ..请帮助我。



1.选择c.cus_name，来自客户c的m.cus_no，成员m，其中m.cus_no = c.cus_no;



2.声明@curdate = sysdate;

选择i.issno，ca.cass_name，c.cus_name from issue i，cassette ca，customer c，membership m，其中ca.mem_no = m.mem_no和m.cus_no = c.cus_no和i.iss_date = curdate;







4.选择（*）计数，来自问题i的c.cus_name，会员资格m，客户c其中i.mem_no = m.mem_no和c.cus_no = m.cus_no c.cus_name的订单;

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;

推荐答案

这是你的作业，所以我不会给你所有的答案！只是修复你试图去做的地方：

1）我可能会使用JOIN - 你必须从两个或多个表中返回值 - （以及表的名称和字段错误）

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)

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

2）再次，你需要加入或更多。



3）再次加入，但这次使用WHERE子句查看cass_name列



4）语法错误！再次加入，但COUNT语法是COUNT（*）而不是（*）COUNT



为什么不试试呢？你可能安装了SQL服务器？创建表，填充数据并在Sql Server Management Studio中尝试查询！

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!

这篇关于SQL查询构建疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！