for have an experience in SQL - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

for have an experience in SQL

Author

Topic

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-29 : 04:00:24

look at [url]http://www.sql-ex.ru[/url] it may help you to familiar with SQL solving exercises

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-29 : 04:57:48

Cool. I give it a thumbs-up. Make sure and switch to English from the drop-down at the top :)

Edit: But I'd like to skip the first 100 easy questions and get right to the difficult ones :) Can I test out of SQL 101?
--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-29 : 06:13:23

to test 101 you have to solve all 100 before

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-29 : 06:19:19

but for example

Exercise: 70
For successive 7 days from the first day where number of trips from town Rostov where the maximum, find the number of trips from town Rostov. Result set: date, number of trips

Short description of database:

Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Company table has ID and name of the company, which transports passengers. Trip table has information about trips: trip's number, company's ID, plane's type, departure city, arrival city, departure time, and arrival time. The passenger table has passenger's ID and passenger's name.
Pass_in_trip table has information about the flights: trip's number, departure date, passenger's ID and his place during the flight.
We should note that,
- Duration of the flight is less than a calendar-day (24 hours)
- Time and date are considered comparatively one time zone
- There can be the passengers bearing the same surnames
- Relationships and restrictions are shown in the data schema.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-29 : 13:13:50

Since I can't tell if it's right until I solve 1-69, here is my idea:


select y.dt, count(trip.trip_no) trips
from 
(
select dateadd(dd,days.id,x.dt) dt from
	(select 1 id union select 2 
	union select 3 union select 4 union select 5
	union select 6 union select 7) days,
	(
	select top 1 count(1) ct,
	date dt from trip, pass_in_trip
	where trip.trip_no = pass_in_trip.trip_no
	and town_from = 'Rostov'
	group by date
	order by 1 desc
	) x
) y
left outer join pass_in_trip on pass_in_trip.date = y.dt 
left outer join trip on trip.trip_no = pass_in_trip.trip_no and town_from = 'Rostov'
group by y.dt 
order by y.dt

But I probably souldn't post this here - I don't want to answer it for anyone else. I guess I'll delete it later.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-29 : 13:36:54

Try to solve 1-69 first :)

Incorrect.
The result of Your query:

dt trips
2003-04-02 00:00:00.000 0
2003-04-03 00:00:00.000 0
2003-04-04 00:00:00.000 0
2003-04-05 00:00:00.000 1
2003-04-06 00:00:00.000 0
2003-04-07 00:00:00.000 0
2003-04-08 00:00:00.000 2

The result of correct query:

Dt Qty
2003-04-01 00:00:00.000 1
2003-04-02 00:00:00.000 0
2003-04-03 00:00:00.000 0
2003-04-04 00:00:00.000 0
2003-04-05 00:00:00.000 1
2003-04-06 00:00:00.000 0
2003-04-07 00:00:00.000 0

it is not good idea to post your query here. If you can't to find the mistake, send the e-mail to the site administrator (of www.sql-ex.ru) You can find address there

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-29 : 13:40:05

Heh. Okey-Dokey. I'm up to 8. They are getting a little harder so I won't be too bored.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-30 : 01:17:22

Ok I'm stumped. I have submitted at least 11 different solutions that generate the correct answer to #35 but they are all rejected as incorrect.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-30 : 01:24:30

try to send your 'right' query(s) to msi77@mail.ru

They answers fast and, perhaps, will help you to find mistakes.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-30 : 05:19:30

page cannot be found, can you please redirect me? tnx

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-30 : 05:27:19

http://www.sql-ex.ru/index.php?Lang=1

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-30 : 05:36:10

thanks..

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 05:30:51

I need your opinion about http://www.sql-ex.ru/index.php?Lang=1

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 05:40:09

it's kinda difficult to submit a query without actually looking at the data and the other "database" they were talking about. i posted an answer which returned the correct query but they said it was incorrect.

i mean there are a lot of solutions to a problem, but if you keep getting the correct query but not the "right answer" then it's like trying to troubleshoot sql errors with just "general network failure".

i guess i'm still green when it comes to tsql but answering the questions are a bit frustrating. need more practice...

in general, it's pretty cool, especially for beginners like me.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 05:43:04

quote:
Originally posted by kselvia

Ok I'm stumped. I have submitted at least 11 different solutions that generate the correct answer to #35 but they are all rejected as incorrect.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

i guess, now i know what you mean, just skipped 16 coz i just submitted "unable to count" answers and none was correct. lolz

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 05:48:33

http://msi77.narod.ru/Computer_script.sql
http://msi77.narod.ru/Inc_Out_script.sql
http://msi77.narod.ru/Ships_script.sql
http://msi77.narod.ru/aero_script.sql
http://msi77.narod.ru/painting_script.sql

are scripts of tables

They may help you to ajust your query inserting data into tables. All possible cases have to be checked.

and

REMEMBER! when you stumbs on some exercise, try to send your query to e-mail: msi77@mail.ru

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 06:35:15

thanks for the script, now i can see what i need to do, just passed the questions i skipped

and now relieved.

did you create the site?

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 06:41:44

1.The key of success is in uderstanding questions. It was hard sometime (for me).

2.No, I just member. I solved 128 ex yet.

Serge I
Starting Member

10 Posts

Posted - 2004-10-01 : 08:40:41

quote:
Originally posted by jen

i mean there are a lot of solutions to a problem, but if you keep getting the correct query but not the "right answer" then it's like trying to troubleshoot sql errors with just "general network failure".

About checking the solutions see FAQ #1 [url]http://sql-ex/faq.php?Lang=1[/url]

Serge I
Starting Member

10 Posts

Posted - 2004-10-01 : 08:51:38

Sorry, I mean http://www.sql-ex.ru/faq.php?Lang=1

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-01 : 11:06:37

How can you skip a question?

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources