Please Help In Queries
Jan 23, 2006
Hi,
I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.
In Book table, BookID, BookTitle, CategoryID
In Category table, CategoryID, CategoryName
In Subject table, SubjectID, SubjectName, CategoryID
In UserDownload table, UserID, BookID
In User table, UserID, UserName
I used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.
When user downloads book, I update UserDownload table.
The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Aug 9, 2006
Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
View 1 Replies
View Related
Dec 7, 2007
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies
View Related
Dec 18, 2007
how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
View 3 Replies
View Related
Jun 18, 2004
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies
View Related
Oct 21, 2004
Hi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
View 2 Replies
View Related
Jul 16, 2002
Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies
View Related
Aug 3, 2000
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
View 5 Replies
View Related
Feb 7, 2003
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
View 2 Replies
View Related
May 1, 2001
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
View 1 Replies
View Related
Oct 12, 2007
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
View 1 Replies
View Related
Feb 15, 2007
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
View 5 Replies
View Related
Jul 2, 2007
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks
View 14 Replies
View Related
Jun 26, 2012
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int
dateon DATETIME
dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
View 8 Replies
View Related
Mar 18, 2004
I'm confused.
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
PLEASE help, I have a deadline in a week : (
THANK YOU
View 1 Replies
View Related
Apr 24, 2008
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information?
this code that i have so far doesn't do waht i want,, showing
part_number
2 2 1 1 Kilimanjaro
2 2 2 2 Dolomiti
2 2 11 11 Mud Zinger 1
and jumbling up information
select part_inventory.part_number, assemble.assemble_number, model.model_number, product.serial_number, model.model_name
from part_inventory inner join model_configuration on part_inventory.part_number = model_configuration.part_number
inner join model on model.model_number = model_configuration.model_number
inner join assembly_configuration on part_inventory.part_number = assembly_configuration.part_number
inner join assemble on assemble.assemble_number = assembly_configuration.assembly_number
inner join product on product.serial_number = model.serial_number
where part_inventory.part_number = 2;
View 3 Replies
View Related
Sep 27, 2005
Label(LabelID,Label) labelid=pk
-------------------------------
Shelf(ShelfID,Shelf) shelfid=pk
-------------------------------
Country(CountryID,Country) countryid=pk
---------------------------------------
Track(TrackID,Track) trackid=pk
-------------------------------
Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid)
-----------------------------------------------------------------------------------------
CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid)
------------------------------------------------------------------------------------------------------------
CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid)
----------------------------------------------------------------------------------
ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid)
---------------------------------------------------------------------------------------------------------
I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries
------------------------------------------------------------
Input Artist
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1
Group ByCDSection
------------------------------------------------------------
InputCDTitle
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2
Group ByCDSection
------------------------------------------------------------
InputTrack
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3
Group ByCDSection
------------------------------------------------------------
InputLabel
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4
Group ByCDSection
------------------------------------------------------------
View 2 Replies
View Related
Dec 22, 2005
Ashish Amodia writes "how to create tables in ms-sql?
how different opration can be perform in database table, like insert, update, delete with tables.
how to create store procedure?"
View 2 Replies
View Related
Dec 24, 2005
Iam developing a randomizing program .which selects random rows frm the database.iam using sql as backend and asp.net as front end.
i allow user to give a number say 1000, so 1000 random rows will be slected frm the database.iam using NEWID() for random selection.
But how do i put the user value into query. so as to select that many random rows from the databse.
please help me out on this
View 8 Replies
View Related
Dec 29, 2005
i hav this problem .i hav one column in my table which contains records which can be integers or string.
i allow user in my .aspx page to enter integers in three text boxes.
and depending on those integers say 1,2,3 ... i hav to select those rows from my table.
where the problem is that i hav to selct the row which is say row no.1 which can contain string or integer and iam not getting the slected result instead some integers .
the query iam using is
SELECT " & range1 & " UserID FROM TAble
where range1 is the integer that user puts into textbox .i hav only one cloumn(UserID) in my table which can contain integer or string so i hav to select the row which is what the user has entered but contains string or integer(which is not what user has entered).
View 15 Replies
View Related
Feb 7, 2006
Can we say that if there are 4 tables that are joined , the query will definately take a longer time to execute, can we still reduce the execution time
I mean how can u still further optimize the performance , or say that the performace can no further be done on the particular job
View 5 Replies
View Related
Feb 10, 2006
Hi
Do all the applications use Storede procedures & not queries. I mean when we use the ADO connection. Is it better to use stored procedsure alsways or could we do with the rs.Open "Select statment"
Please advice
View 3 Replies
View Related
Dec 15, 2006
hi all i am very new to Sql and i am on process of learning SQL Queries i need to know what will be the sql commands if i wanted the following options ( all tables are created perfect just need to know these commands)
Options
1. Calculate the summation of the column 'price' (as Sum).
Present the information in the following format; 'The sum is', 'Sum'
2. Calculate the minimum, median and maximum values of the column 'price'
Present the information in the following format;
'The minimum is' 'minimum', 'The median is' 'median', 'The maximum is' 'maximum',
3. Calculate the summation of the prices for items ItemID = 10, 11, 15 and 22. (as Sum).
Present the information in the following format; 'The sum is', 'Sum'
4. Select all items with 'Power meter' in the 'Description' field.
Present the information in the following format;
ItemID, Price, Description
5. List all data from 'mainTable' in ascending order by category.
Present the information in the following format;
ItemID, category (from category.Name).
6. List all data from 'mainTable' that has the word 'battery' in it anywhere.
Present the information in the following format;
ItemID, Title (consists of 'brand.Name Model briefDescription.Name'), Price.
7. Count the number of items in the table 'mainTable'. (as count)
Present the information in the following format; 'Count is count)
8. List all the information saved in all the provided tables.
Present the information in the following format;
ItemID, Category, price, Title (consists of 'brand.Name Model briefDescription.Name'), image, description, ship_code, Price_Discount, price_tax, Shipping_Discount, Shipping_tax
9. List all data from 'mainTable' in ascending order by category
Present the information in the following format;
ItemID, Title (consists of 'brand.Name Model briefDescription.Name'), SalePrice
SalePrice is calculated using the logical operation;
if Price > 440 AND Price < 1000 then use discount '10%';
if Price >= 1000 then use discount '12%'.
Calculation can be based on [Price * 1.1] for items between 440 and 1000, and [Price * 1.12] for items above 1000.
Please if you can provide SQL commands will be great
so i can work on them as i am new to all this all of your guys help is much appreciated
Thank you in advance to every one
nomi
View 3 Replies
View Related
Feb 26, 2007
Sandesh writes "If i hv 100 record in one system & i want only selected 15 records form old sys to new sys, is there any commands in SQL to copy that data fr 1sys to another sys."
View 2 Replies
View Related
Dec 6, 2007
hi
i'm doing a hoilday database and i was wondering does anyone know how to create a query which shows the hoilday and site details that commence in the calender month that follows the current month.
thanks
jessica
View 2 Replies
View Related
Dec 12, 2007
hi
in my hoilday database, i need to write a query in sql that shows which shows the total cost of a hoilday per day. i'm having alot of trouble with this can anyone help me?
thanks
jessica
View 5 Replies
View Related
Dec 18, 2007
Hi
If I have a table set up by a sub query
like
select
a,
b,
c,
CASE
WHEN a in (
select * from R)
FROM
(
select code here...
) R
This gives me
Invalid object name 'R'.
but R is read if i just select R.code (or another fieldname)
outside the CASE statement ?
I have tried putting the inner SQL in the CASE
but its a large query and i get a 'ran out of stack space error'...
is there some restrictions on accessing sub qrys from CASES ?
View 8 Replies
View Related
Feb 25, 2008
I have a single table "amount" which has only three columns such as memberid referenceid and AMOUNT with following data ........
memberid referenceid AMOUNT
L001 L001
L002 L001
L003 L002
L004 L003
and so on
my needed query willbe if i choose L003 from referenceid the query should select the previous column where it was a memberid and again it choose the same row refernceid of(L003) as L002 where L002 was a memberid and choose L002s referenceid which is L001(BOTTOM TO UP ZIGZAG MODEL)
Finally i should REACH the memberid(L001) and
the other one i need is i should return the count of no of rows crossed above,
p.s the order in data will be random and not from down to upwards.
and forgive my grammar
View 5 Replies
View Related
Mar 18, 2008
Hello all,
I am new to this forum is this the correct place to post general SQL queries?
TIA
View 2 Replies
View Related
Mar 18, 2008
I am stuck writing a code that will give me the following information...Shows the dollar amount of brass cap fittings sold to customers located in Illinois during March 2005. I need the following displayed in the query Customer name, city, invoice date, inventory diameter, quantity and extended price. Sort the output by extended price with largest first. Name the dynaset "Brass Cap Fittings sold to customers in Illinois March 2005". The problem I am having is getting the City, Diameter, and Price data to show up. I also do not have a clue of how to limit the data to including only brass fitings, only sold to IL customers, and sold only in March of 2005. Here is a sample of the query I have to adjust and the current SQL code associated with it...
SELECT tblCustomer.CustomerName, tblCustomer.City, tblSales.InvoiceDate, tblInventory.Diameter, tblSalesInventory.Quantity, [SInvPrice]*[SInvQuantity] AS Extension
FROM (tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerNumber = tblSales.CustomerNumber) INNER JOIN (tblInventory INNER JOIN [tblSales-Inventory] ON tblInventory.InventoryItemCode = [tblSales-Inventory].InventoryItemCode) ON tblSales.InvoiceNumber = [tblSales-Inventory].InvoiceNumber
ORDER BY tblSales.InvoiceNumber, [tblSales-Inventory].InventoryItemCode;
NameCityDateDiameterquantityExtension
Dunn Plumbing1/23/2005$651.90
Dunn Plumbing1/23/2005$398.86
Dunn Plumbing1/23/2005$747.50
Dunn Plumbing1/23/2005$58.50
Cole & Co.1/23/2005$522.50
Cole & Co.1/23/2005$65.80
Cole & Co.1/23/2005$42.90
Lin Plumbing Repair, Inc.1/23/2005$439.00
Lin Plumbing Repair, Inc.1/23/2005$262.50
View 2 Replies
View Related
Jul 23, 2005
1.In SQL Server 2000, even after using round function upto 2 decimals,the operation over a float field gives results upto 16 decimal places.How can we avaoid this? Is it possible to display such results upto 2decimal places in Query Analyzer? What are the related settings andwhere to do it?2.My table 1 has 3 fields, table 2 has 5 fields.Both tables have same records and a common field.I want table 3 which will contain fields from both the tables and thecommon field. How to achieve this?
View 2 Replies
View Related
Oct 17, 2007
I've come accross queries like this one previously but never got thetime to carefully study them form some book.SELECT 1 WHERE 1 = 11) What do queries like these mean? From common sense, I can deducethat it is some kind of a test for a boolean value, but the result isalready deterministic in the above case (true).What use is such a query for?2) What dialect of SQL (ANSI/T-SQL) does MS-SQL Server use?
View 3 Replies
View Related
