Relational Alegbra and SQL Queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Relational Alegbra and SQL Queries

Author

Topic

dahawk
Starting Member

5 Posts

Posted - 2014-06-03 : 17:24:19

Hey there, I fairly new to SQL stuff and I'm just trying to learn the basics and do some simple questions. I hope you guys can help me out do some practice questions.

For each of the following, (1) provide a relational algebra expression that will correctly produce the
result set required by the query. You may provide the expression as one assignment with several
operators, or as a sequence of assignments as shown below. Then, (2) create an SQL statement
that will generate the equivalent result

A) List all the teams that are located in the city "New York".
B) List the average ticket price for all the tickets in "Atlanta". You may use the SQL function AVG(price)to assist with this calculation.
C) List the names of all the players on teams named "New Jersey Devils" with salaries greater than $1,000,000. For the SQL query, sort the list in descending alphabetical order.

Here are the tables:
http://tinypic.com/r/23r0fu1/8

I'm not too sure even how to approach this, can someone help me out?
Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-03 : 22:34:47

can you show us what you have tried ?

KH
[spoiler]Time is always against us[/spoiler]

dahawk
Starting Member

5 Posts

Posted - 2014-06-04 : 00:16:52

For A) I tried
SELECT city_name
FROM team

B)
SELECT AVG(price)
FROM game ticket

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-04 : 00:32:05

quote:
A) List all the teams that are located in the city "New York".

you need to add a WHERE clause to your query


WHERE ctiy_name = 'New York'

quote:
B) List the average ticket price for all the tickets in "Atlanta". You may use the SQL function AVG(price)to assist with this calculation.

similarly you need the WHERE clause to for "Atlanta"

KH
[spoiler]Time is always against us[/spoiler]

dahawk
Starting Member

5 Posts

Posted - 2014-06-04 : 12:14:42

Thanks a lot khtan!
I'm starting to understand the where clause more.
For B), would the WHERE clause be

WHERE city_name = Atlanta

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-04 : 20:46:55

you will need to enclose the string in single quote


WHERE city_name = 'Atlanta'

KH
[spoiler]Time is always against us[/spoiler]

dahawk
Starting Member

5 Posts

Posted - 2014-06-04 : 21:02:21

Oh thanks!

If there were two more questions would it be like this?

List the names of all the players on teams named "Blackhawks" with salaries greater than
$1,000,000. For the SQL query, sort the list in descending alphabetical order.

SELECT first name AND last name
FROM Player
WHERE salary > 1000000 AND team = "Blackhawks"
ORDER BY last name DESC;

List the names of sports teams playing a game between Feb 15, 2015 and Feb 22, 2015 in
"Ohio", with tickets less than $100.

SELECT team_name
FROM Game Ticket
WHERE date BETWEEN "February 15, 2015" AND "Feb 22, 2015" AND city_name = "Ohio" AND price < 100;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-04 : 22:02:10

yes. Correct.

KH
[spoiler]Time is always against us[/spoiler]

dahawk
Starting Member

5 Posts

Posted - 2014-06-04 : 23:46:30

Thank you.
Could you explain to me how to produce relational expressions result sets that are required by query for the 4 questions?
I'm completely oblivious to the question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-05 : 12:53:22

No, it is not correct. Don't use double quotes (") to enclose strings and dates.
Using double quotes makes SQL Server think you are denoting column names, not values.

Use single quote (') as pointed out before.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources