|
It is not possible to do a backup of full text catalog, so if you use it, make sure to have the tables and the model finished, or almost finished. Because, if you do changes (to add a column, create a new foreign key...), you will need to create the full text catalog and the indexes over and over again.
Despite to everything, I recommend the use of full text catalog in some cases, because his use increase the speed of the querys with filters, it is faster than the use of LIKE in most cases. But the maintenance is bigger.
|
|
|
|
|
I'm being asked to create a couple of SQL statements to retrieve information about a Luxury Hotel Database.
List all the rooms in each hotel that have never been reserved in order by hotel number.
I have the SQL statement retrieved the rooms what have been reserved but my problem is retrieving those that have not been reserved.
SELECT RoomNo FROM RESERVATION WHERE RoomNo ORDER BY HotelNo
List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.
Here are the tables we are working on.
HOTEL (HotelNo, HotelName, City)
ROOM_TYPE (RoomType, Descr, RoomRate)
ROOM (HotelNo, RoomNo, RoomType, PhoneExt)
FK1: Foreign key HotelNo references HOTEL
FK2: Foreign key RoomType references ROOM_TYPE
GUEST (GuestNo, FirstName, LastName, Address, City, State, ZipCode)
GUEST_PHONE (PhoneNumber, GuestNo, PhoneType)
FK: Foreign key GuestNo references GUEST
RESERVATION (ResNum, HotelNo, RoomNo, GuestNo, ArrivalDate, DepartureDate, NumPersons)
FK1: Foreign Key (HotelNo, RoomNo) references ROOM
FK2: Foreign Key GuestNo references GUEST
|
|
|
|
|
Let's see what your query looks like for the rooms that have been reserved. I suspect that you simply need to negate the criterion for choosing those that have been reserved.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
SELECT RoomNo
FROM RESERVATION
ORDER BY HotelNo
|
|
|
|
|
You could try a subquery:
SELECT HotelNo, RoomNo FROM Room WHERE RoomNo NOT IN (SELECT RoomNo FROM Reservation) ORDER BY HotelNo, RoomNo
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
No data is retrieved back. It's blank.
|
|
|
|
|
My SQL may be a bit rusty. But I don't know what your data looks like either.
Let's see if anyone else has an idea.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
It worked. I was trying to incorporate something else into the statement.
|
|
|
|
|
List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.
My two related tables are Guest and Reservation.I can't have the FK GuestNo be one of the attributes.
This is what I have so far.
Select FirstName, LastName, GuestNo FROM GUEST INNER JOIN RESERVATION ON GUEST.firstname = RESERVATION.guestno
I'm not sure about GuestNo.
|
|
|
|
|
SELECT GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST
INNER JOIN RESERVATION ON GUEST.GuestNo = RESERVATION.GuestNo
ORDER BY GUEST.FirstName This is my SQL statement that retrieves all the names of the people who made reservations with the GuestNo appearing as well. I don't know where to incorporate the Count function.
|
|
|
|
|
You need to COUNT(reservation.guestNo) while GROUPing BY Guest.GuestNo
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
SELECT HotelNo, RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo
|
|
|
|
|
Access is giving me that HotelNo could refer to more than one table in this SQL statement.
|
|
|
|
|
try now I forgot to add table name
SELECT ROOM.HotelNo, ROOM.RoomNo from ROOM
LEFT JOIN RESERVATION ON (ROOM.HotelNo = RESERVATION.HotelNo) AND (ROOM.RoomNo = RESERVATION.RoomNo)
WHERE (((RESERVATION.HotelNo) Is Null) AND ((RESERVATION.RoomNo) Is Null))
ORDER BY ROOM.HotelNo
|
|
|
|
|
This is second SQL statement that retrieves everyone's name and the GuestNo who have made a reservation at the hotel. I don't know where to add the count function without getting a Syntax Error.
SELECT GUEST.FirstName, COUNT (RESERVATION.GuestNo)
FROM GUEST INNER JOIN RESERVATION ON GUEST.GuestNo =RESERVATION.GuestNo
ORDER BY GUEST.FirstName
GROUP BY GUEST.GuestNo
This is giving me a Syntax error in my query expression GUEST.FirstName, GROUP BY GUEST.GuestNo.
modified 9-Mar-15 23:28pm.
|
|
|
|
|
SELECT COUNT(RESERVATION.GuestNo), GUEST.FirstName, RESERVATION.GuestNo
FROM GUEST INNER JOIN RESERVATION on GUEST.GuestNo = RESERVATION.GuestNo
GROUP BY GUEST.FirstName
I've tried this combination as well but Access tells me, You tried to execute a query that does not include the specified expression 'GuestNo' as part of an aggregate functio
|
|
|
|
|
i am trying to save datagridview records to mysql table...But am getting the error:
"value cannot be null
parameter:datasource"
any help
private sub Save()
Try
con = New MySqlConnection(dbPath)
sql = "SELECT *FROM shubject1"
da = New MySqlDataAdapter(sql, con)
con.Open()
ds = New DataSet()
cmdb = New MySqlCommandBuilder(da)
da.Fill(ds, "subject1")
bsource.DataSource = ds.Tables("subject1")
dgMarksheet.DataSource = bsource
Catch ex As Exception
MsgBox(ex.Message)
End Try
end sub
Private Sub SaveRecords()
Try
ds = New DataSet()
dt = New DataTable()
dt = ds.Tables("subject1")
Me.dgMarksheet.BindingContext(dt).EndCurrentEdit()
Me.da.Update(dt)
MsgBox("Saved", MsgBoxStyle.Information, "Record Changes")
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try
End Sub
|
|
|
|
|
At which point do you get that error?
|
|
|
|
|
the error is found at dt = ds.Tables("subject1")
|
|
|
|
|
In your save method you declare a new dataset and then you try and reference a datatable "subject1" in that new, empty dataset. Next time point out where you are getting the error!
You need to get the datacontext of the DGV into a dataset before trying to do anything with it.
And dataadaptors must have changed since I last used them, the idea that you can throw an entire datatable at it and get it to update the database was not there in my day, a decade ago 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Seriously, this isn't a subject that can be answered in a forum post.
Read about indexing[^] first and come back with specific questions.
|
|
|
|
|
.
What an interesting question!
Oh you mean your title is the entirety of your question.
Ok, here is an answer, buy a book on Oracle database development and/or read lots of articles and blogs on how to optimise Oracle queries. DBAs spend years learning this subject, and you want them in a forum post!
I'll put my sarcasm stick away now!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi, i want to get data from table, which has data like that:
ColA ColB
1 12.324.145
2 425.152.643
3 12
After Select the results must be like:
1 12
1 324
1 145
2 425
...
3 12
How can i do this?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
