Write the following queries in SQL:
1. Find the pname of parts for which there is some supplier.
2. Find the snames of suppliers who supply every part.
3. Find the snames of suppliers who supply every red part.
4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
5. Find the sids of suppliers who charge more for some part than the average cost of that part.
6. For each part, find the sname of the supplier who charges the most for that part.
7. Find the sids of suppliers who supply only red parts.
8. Find the sids of suppliers who supply a red part and a green part.
9. Find the sids of suppliers who supply a red part or a green part.
10. For every supplier that only supplies green parts print the name of the supplier and the total number of parts that she supplies.
11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
hi,i am working on asp.net development server. i have created a web page on which i have put some option to enter the any user id. as soon as user id is entered on button click i want to search the first name, last name, age in my database where i have inserted the table with entries. then i wan to display it on the page.Please tell me how can i write the C# code on the clicking event so that i get results. i have connected the database to my virtual server.
Hi. I want create a queries that is 1. Item onhand > 0 2. For next 14 day this item does not have any Sales Order. Because our product has shelf life issue, So I want to know which kind product that I have onhand and doesn't have any sales order for the next 2 weeks. Item Table is T1 and Sales Order Table is T2.
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT * INTO dupContactKeys FROM Contacts WHERE ContactKey IN ( SELECT ContactKey FROM Contacts GROUP BY ContactKey HAVING COUNT(*) > 1)
SELECT * FROM dupContactKeys WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
Beside its an opensource and supported by community, what's the fundamentaldifferences between PostgreSQL and those high-price commercial database (andsome are bloated such as Oracle) from software giant such as Microsoft SQLServer, Oracle, and Sybase?Is PostgreSQL reliable enough to be used for high-end commercialapplication? Thanks
Obviously Excel is the tool of choice for most people but with it's limited ability to leverage RAM (32 bit) and it's limitation with rows at just over 1 million what other choices do we have for viewing data?
My bosses boss created several OLAP universes and they seems to fly a lot fast than regular relational database. This still doesn't work with the fact the data can't be worked with unless you have a strong front end that can handle processing all those rows.
We're looking at using SSRS for all our new reporting requirements. However, we use SQLServer(2000 and 2005), Ingres and Postgres DBMSs. Ideally, we'd like to use SSRS against databases within all 3 DBMS. Ingres can be accessed via ODBC, JDBC, and has a .NET data provider, but no oledb data provider. Postgres can be accessed via ODBC. Reading some of the threads on this forum, there appears to be some limitations on the features of SSRS that can be used when accessing a database via ODBC compared to OLEDB. I'd like to understand these limitations if they exist so that we can work round them. Could anyone point me in the direction of a document that covers this issue, or outline what the issues are.
Hello, I've proposed to the head of IT at my organisation to head an database development export group to: - Provide assistance in improving the performance of existing relational databases - Provide assistance for the development of new database, e.g. correct construction of indexes; data contention, etc. - Creation of database development standards - Enforce the above standards for new databases so that the database is not deployed to production servers until it passes the standards.
I'd really like this to be a success as it should improve our rather crappy databases, basically because the people currently creating the databases don't know much about relational design.
Can anyone please post their experiences of setting something like this up, or working on such an expert group where they work? I'd really like to hear the good and bad experiences and what and (what not) to avoid.
I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?
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 :)
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and if T1.CheckBoxCol=1 then set T2.CheckBox1=1 else if T1.CheckBoxCol=2 then set T2.CheckBox2=1 else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
Hello, I have MyTable with ID, IsYesNo fields ID is duplicated so I need perform select on MyTable with the following conditions: 1. Select all the ID distinct where IsYesNo=’Yes’ first 2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection Combine these two and return the result
I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE? Thanks
Hi,I have a hotel reservation system.I need to implement Check availability (Room checking) function for the project.But I dont understand how do I start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me
How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid
A file with source data will be made available in comma delimitted format to supply a new weekending value, size and demand.
What I will need is first the dts to bring that in. then I am assuming a stored procedure to be run (this is why I am here) to add the data from the comma del. file into the table. If the data EXISTS I would want it to UPDATE the values that are in the dest table but run a calculation first which would be the planutil minus demand then the result to be updated intot the record. if the record from the tab del. file does not exisst in the dest table then insert it.
another words the logic i have in mind read the data from the temp tample (where the file gets imported into) see if the record exists in the live table is it does update it with the calculation of planutil minus demand if not create the new record in the live table. I need it to compare..
Someone help me with some code i thank you kindly in advance
p.s. And good books dedicated to stored procedures??
Many thanks in advance for anyone that can help me write this qry:
To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:
We also have a services table which hold all our various services we own:
dbo.services
svcID svcName 1052 svc1 1053 svc2 1054 svc3
We then have a tbl that shows the links between components and services
dbo.compUses
svcID compID 1052 310 1052 400 1053 122 1256 134
I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.
This is in SQL2005.
Have I explained this well enough? Any help would be much appreciated!
Hey peeps, fishkake's back, and he's more clueless than ever!
OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.
How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...
Hi, I have to implement a search functionality. In the various filters for the search, Store Number is one such filter. The user should be able to enter range values for store numbers. Like 1500-1600. So this should filter for all the stores between 1500 and 1600. Similarly, all these also should be valid. 1550,1600 1550 1550 - 1580,6000,8000 etc. I have function which identifies the commas or dashes and seperates out the store number and returns a string like Stores.Storenumber in(1555,1600) Store.StoreNumber between 1555 and 1600 etc... i generate a sql at run time and append this piece and then execute the sql. I have one of the query below.
declare @strQuery varchar(max) declare @strConcat varchar(10) declare @strAppend varchar(max) set @strAppend='' set @StrConcat ='And '
if @IsAdmin is null-- Not a Admin set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)
set @strQuery=' Select (Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber, CartonNumber, ActualReceiptDate as [Scan Date], isnull(Sum(QtyShipped),0) as [Total Units], b.BatchNumber from Carton c left outer join CartonDetail Cd on Cd.Carton_Id = c.Carton_ID inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton'' inner join Document d on d.Document_ID = c.document_Id inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5 inner join Stores on Stores.Store_ID = d.DestinationID inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code'' where not c.cartonNumber is null '
if not (@StoreId) is null begin set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')' End
if not (@DateFrom) is null and not (@DateTo) is null begin set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')' End
if not (@CartonNumber) is null Begin set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')' End
if not (@Status) is null Begin set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')' End
set @strAppend = @strAppend + ' group by d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber order by ActualReceiptDate'
set @strQuery = @strQuery +@strAppend execute(@strQuery) This query takes time, if there a little over 1000 records. I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.
I hope i was able to explain my query fairly. Please let me know otherwise.
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
Now, I need to check T1 and if T1.CheckBoxCol=1 then set T2.CheckBox1=1 else if T1.CheckBoxCol=2 then set T2.CheckBox2=1 else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
Hi, I have a table Projects. This table has ProjectID and Version as PK. The Version starts at 1 and everytime a project is changed, I save the project with the same ProjectID and increase the Version by 1.How can I create a query that get all Projects with the latest Version? Thx
