October 2, 2015 at 2:27 pm
STEP 2 (THIS IS FROM KHANACADEMY)
Maybe your friends only like singing either recent songs or truly epic songs. Add another SELECT that uses OR to show the titles of the songs that have an 'epic' mood or a release date after 1990.
CREATE TABLE songs (
id INTEGER PRIMARY KEY,
title TEXT,
artist TEXT,
mood TEXT,
duration INTEGER,
released INTEGER);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("Bohemian Rhapsody", "Queen", "epic", 60, 1975);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("Let it go", "Idina Menzel", "epic", 227, 2013);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("I will survive", "Gloria Gaynor", "epic", 198, 1978);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("Twist and Shout", "The Beatles", "happy", 152, 1963);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("La Bamba", "Ritchie Valens", "happy", 166, 1958);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("I will always love you", "Whitney Houston", "epic", 273, 1992);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("Sweet Caroline", "Neil Diamond", "happy", 201, 1969);
INSERT INTO songs (title, artist, mood, duration, released)
VALUES ("Call me maybe", "Carly Rae Jepsen", "happy", 193, 2011);
SELECT title FROM songs;
***** this is my attempt *******
SELECT title, released FROM songs
WHERE title = "La Bamba" OR released >1990;
October 2, 2015 at 2:32 pm
*** this is my second attempt ****
SELECT mood, released, title FROM songs
WHERE mood = "epic" OR released >1990;
October 2, 2015 at 2:43 pm
*** OK THIS IS MY THIRD ATTEMPT***
but I can not move on to the next step, why?
SELECT title, mood FROM songs
WHERE mood = "epic" OR released >1990;
October 2, 2015 at 2:45 pm
*** FINAL ATTEMPT*** GOT IT, THANKS
SELECT title FROM songs
WHERE mood = "epic" OR released >1990;
October 2, 2015 at 2:52 pm
gcombina 73810 (10/2/2015)
*** this is my second attempt ****SELECT mood, released, title FROM songs
WHERE mood = "epic" OR released >1990;
Because you have epic in double quotes. In sql server you use single quotes around strings. Double quotes are interpreted as object names.
SELECT mood, released, title FROM songs
WHERE mood = 'epic' OR released > 1990;
I feel bad that you are learning this way because they are demonstrating very poor database design by not normalizing the data. Especially for people just learning it is imperative that decent design be followed. The tables here are using the deprecated text datatype. It has been deprecated for a decade now in favor of varchar(max). Of course varchar(max) is way overkill for this data. I realize that the class or whatever is free but sadly it is teaching very poor ways of doing things in sql server.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2015 at 3:53 pm
I agree with Sean here that the use of TEXT datatype is not good here. Since they are using Text, Sean's query still will not work. You'll have to explicitly cast the TEXT datatypes to varchar to make the equality compare.
SELECTtitle
FROMsongs
WHERECAST(mood as varchar) = 'epic'
OR released > 1990;
October 2, 2015 at 4:42 pm
Thank you
I will take that into account. Its Khanacademy.com
Do you recommend a better site to learn? How do I learn?
October 3, 2015 at 5:22 pm
gcombina 73810 (10/2/2015)
Thank youI will take that into account. Its Khanacademy.com
Do you recommend a better site to learn? How do I learn?
If that's what they're using as educational material... I'd drop the course and demand a refund for tuition. The TEXT datatype has been on the "do not use" list for as long as I can remember.
Directly from Microsoft's Books on Line (BOL):
Important
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
If they don't know that and they don't know the proper use of single & double quotes... What else do they have wrong?
As to alternatives... That's tough to answer. It all depends on your own learning style. I got started by leading books and participating on different forums. Once I was good enough get a job, I was fortunate enough to be hired by a seasoned DBA who took the time to fill in a lot of the blanks. I've never taken a class on the topic...
One book I'll always recommend is Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference)
It's written by a true master of SQL Server. Unlike some of his other books, it written for beginners and will provide a rock solid foundation that you can continue to build on.
If you prefer online classes, I'm sure there are good ones out there but I wouldn't have a clue which one(s) to recommend. Hopefully someone else will be able to chime in.
October 3, 2015 at 8:08 pm
Another great research is found on this site. The Stairway Series cover a variety of topics and most go from beginning through some pretty good levels of detail. There are also a number of freely-downloadable books published here. I'd say either one of these are a good place to start.
If you're looking for online videos instead, http://www.pluralsight.com/ has a lot of them. It isn't free, but you'll certainly get much better than what I've seen above. I worry if the stuff above is what they're teaching on the site you're using.
October 5, 2015 at 7:24 am
John Rowan (10/2/2015)
I agree with Sean here that the use of TEXT datatype is not good here. Since they are using Text, Sean's query still will not work. You'll have to explicitly cast the TEXT datatypes to varchar to make the equality compare.
SELECTtitle
FROMsongs
WHERECAST(mood as varchar) = 'epic'
OR released > 1990;
Ugh!! I haven't used TEXT in so long I forgot that you always have to cast to a usable datatype before you can query it. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2015 at 7:27 am
Ed Wagner (10/3/2015)
I worry if the stuff above is what they're teaching on the site you're using.
I took a peek at the khanacademy site...holy cow the sql stuff is hideous. Anybody wanting to learn sql would be better off walking away from that place immediately if not sooner. It is free and horribly overpriced. There is no way I would recommend using that place and would strongly urge people to run away screaming.
As with many things you get what you pay for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2015 at 8:02 am
Just as an FYI for the others, Khanacademy doesn't use SQL Server. Entering to the site, I found this:
Our implementation of SQL is based off of SQLite
That's why the double quotes and text data type is used, as well as no identity definition. SQLite uses a single data type for strings which is TEXT and it has no length restrictions other than the max capacity.
I'm uncertain about the quality of the site to learn SQL. Remember that SQL is a standard with many dialects that won't follow it completely and by that reason, the dialects from SQL Server and SQLite will have differences.
On the normalization side, I guess that to teach basic queries before teaching joins, a denormalized table would be better to help the student understand what he's doing.
December 5, 2016 at 9:32 am
. Add another SELECT that uses AND to show the titles of songs that are 'epic', and released after 1990, and less than 4 minutes long.
Note that the duration column is measured in seconds.
do you write AND the same as OR when during a SELECT
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply