具有多个选择的SQL查询[关闭]

Question

Here's what I have so far:

//Insert a CD supplied by a particular supplier and produced by a particular producer 
Mysqli_query($con, "INSERT INTO CD WHERE suppliername = "Sony" AND producername = "Gold Records" (CD name, CD type, CD year) 
VALUES ('CDname', 'CDtype', 'CDyear')");

//List producers information who produce CD of a particular artist released in a particular year 
mysqli_query($con, "SELECT name from producers WHERE CD artist="Yoko Kano" AND year= "1999");

OR Should I include WHERE artist from song="Yoko Kano" AND year from song= "1999" ?

Is this the correct syntax? Just want to double check.

Answer 1

To insert a CD, you should be able to use something like:

INSERT INTO cd (suppliername, producername, title, type, year) 
VALUES ('Sony', 'Gold Records', 'My CD Title', 'My CD Type', 2008)

I'm not going to give you a full answer to your second question since I'm assuming this is homework. I will give you some guidance though.

First, if you want a list of producers, you will need to do

SELECT * FROM producer

But you don't want every producer, you only want the ones that produced CDs released in 1999. How can you get a list fo those CDs? With another query:

SELECT * FROM cd WHERE year = 1999

So how do you combine these? With something like:

SELECT * FROM producer, cd where cd.producername = producer.name and cd.year = 1999

The query that you need is more complex, you need to include the fact that the artist is Yoko Ono but the artist is not in the Cd table, it's in the Song table. That means that you need another join with the Song table. To give you another clue, consider the question, how do I see all Cds released by a particular artist? If you want to see all songs by a particular artist, that's easy:

SELECT * FROM Song WHERE artist = 'Yoko Ono'

What about the Cds? You could do:

SELECT * FROM Cd, Song WHERE Song.CdTitle = Cd.Title

Your final query will need to select from all three tables and you'll need all of the constraints to be in place.