I have two tables, products and category. A product can be in multiple categories. product (<product_id>, name, desc, price) category (<category_id>, slug, display_name) product_category(<category_id>, <product_id>) Product have a N:M relationship with category. I would like a query which show the categories of the product in a single...
Is there any article/link available where I can find examples of SQL tuning(Oracle). It would great if it is explain with example. I need something like existing query, statstics, plan and then suggested query/recommendation and new plan. I found couple of really good links on google: http://www.dba-oracle.com/art_sql_tune.htm http://...
Hello! I got a database table that looks something like this: | id | clock | info ---------------------------------------------- | 1 | 1262556754 | some info | 2 | 1262556230 | some other info | 3 | 1262556988 | and another | 4 | 1262555678 | and some more It contains lo...
Hi, I would like to ask you how in PostgreSQL can you check if one of boolean values in table's column is true using select or aggregate function? Thanks. ...
I have SP that selects data from a table with two columns like this @param1 text, @param2 text Select * from tablle1 where field1 like @param1 and field2 like @param2 However , because sometimes Filed2 is null i had to do this , otherwise the query returned nothing @param1 text, @param2 text Select * from tablle1 where field1 like @p...
Hello all, I am a junior oracle DBA.Now I am graduating my university and there I should present senior project about my desired topic..I need help.What topic should I choose? What will be useful for me to deeper my knowledge about Oracle database? Thanks a lot in previous. ...
I'm looking to recategorise some relatively simple information in the most efficient way possible: Using a limited selection of sample data: CREATE TABLE #data (id varchar(30) ,payent_type varchar(30) ,payment_date DATETIME) INSERT INTO #data values ('001','single gift',DATEADD(MM,-12,GETDATE())) INSERT INTO #data values ('001','regula...
I need to get the current 2 digit year and increment by one. So the current number I'm looking for should be 11. Probably really simple but I'm a sql noob :) Thanks ...
How can I, in Oracle 11, insert large data (~100000 hex-digits) into Blob field, using sql command only (without any external data with load cluase or such). update tablename set fieldname='AA'; Works - 1 byte; update tablename set fieldname='AA...(4000 hex-digits)...AA'; Doesn't. Niether Concat helps; strings can't be larger than ...
I would like to select all rows where field A is 'x' and field B is one of 'w', 'y' or 'z'. A and B are both strings, and I would like case to be ignored. ...
Hi all, it is possible in one SQL statement to insert a record then take the autoincrement id, and update for the same record one specific column with this autoincrement value. Thanks in Advance. ...
Is there a big performance difference between those two sql count statements, when performing large counts (large here means 100k + records) first: SELECT count(*) FROM table1 WHERE <some very complex conditions> second: SELECT count(*) FROM (SELECT * FROM table1 WHERE <some very complex conditions>) subquery_alias I know that fir...
I have some information that's stored like this: DROP TABLE IF EXISTS `demographics`; CREATE TABLE IF NOT EXISTS `demographics` ( `row_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city_id` int(10) unsigned NOT NULL, `until_year` int(10) unsigned DEFAULT NULL, `population` int(10) unsigned NOT NULL, PRIMARY KEY (`row_id`) ) E...
I'm hoping to use the following update\insert method to speed up my application insert\updates: UPDATE [Application_Type_Table] SET ApplicationType='Test Value' WHERE ID='1' IF @@ROWCOUNT=0 INSERT INTO [Application_Type_Table] VALUES ('Test Value') How would I do this with sql parameters? the rowcount function will be picked up as a ...
I teach SQL Programming in an university. I was thinking to create a submit page that they could upload the .sql file and it automatically checks if the requirements were met, such as creating a database, using a custom CLR dll and so on. Does anybody have experience in creating that? I got a server running Windows for that. Thanks in ...
Hi All, I New in DB development. Please help me create trigger for moving data from one table to another. I have two tables one is containing "Transaction Status" from where I want to move records on transaction status change into another table having completed transactions. so the value in one table will get deleted and will get insert...
My Table Table cat is having id,name Table user is having id,uname,catid cat Table 1 | Cate one 2 | cate two User Table 1 | sam | 1 2 | dam | 0 my query is select cat.id, cat.name from cat left join user on cat.id=user.catid where user.id=2 since there are no category with id 0 i get zero rows. What i want is even if there ...
I have table witch contains fields: id, parent_id, name (etc.) i want to order this table in "tree travel order" ie. id parent_id 1, 0 3, 1 5, 1 2, 0 8, 2 4, 0 9, 4 (...) in short describe: take root node, append all children, take next root node append children etc. ...
I need to write a jdbc compliant driver wrapper whose purpose is to log all SQL statements that modify data. What is the easiest way to differentiate those statements that modify data from those that only read data? I guess that only way is to parse SQL code on the fly, any libraries that can do that? ...
I have a stored procedure which looks like CREATE PROCEDURE Update-all ( @RowID INT, @Parameter1 NVARCHAR(50), @Parameter2 INT ) As .... i will be passing @Parameter1 / @Parameter2 based on different conditions. if i don't pass @Parameter1 / @Parameter2 ,i am getting a error the stored procedure expects @parameter1/@pa...