Can sombody Explains me about joins? Inner join selects common data based on where condition. Left outer join selects all data from left irrespective of common but takes common data from right table and vice versa for Right outer. I know the basics but question stays when it comes to join for than 5, 8, 10 ta…
Tag: sql
Convert Rows to columns using ‘Pivot’ in SQL Server
I have read the stuff on MS pivot tables and I am still having problems getting this correct. I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic,
Compare two tables, find missing rows and mismatched data
I’d like to compare two tables and get a set of results where the lookup values are mismatched as well as where the key values are missing from the other table. The first part works fine with the …
How to kill/stop a long SQL query immediately?
I am using SQL server 2008 and its management studio. I executed a query that yields many rows. I tried to cancel it via the red cancel button, but it has not stopped for the past 10 minutes. It usually stops within 3 minutes. What could the reason be and how do I stop it immediately ? Answer What could
Order by stored procedure parameter
I have a problem ordering my stored procedure by a parameter given by a user, I have tried reading but the solutions I have tried won’t work. So, is there somebody who can help me? I would like it to …
Defining size of CLOB in Oracle
I am making a table in which I am storing XML. To store XML I am using CLOB data type. The max size of my XML would be 5kb. What size of CLOB column should I define while creating the table? Answer you don’t define a size exactly when setting a clob (unlike a varchar). it is just simply clob.
Throw exception from SQL Server function to stored procedure
I have stored procedure in SQL Server 2012 say spXample and a scaler-valued function say fXample. I call a function fXample from spXample. Can I throw an exception in function and catch it in stored …
check if the column value exists in subquery
i have 3 tables Product Category and ProductCategory. Product table: Category table: ProductCategory: I need a query which returns products which fall under more than 1 categories. Based on the table data above the result would be: So i wrote a query to fetch all the ProductID’s which have more than one…
How to select a record if the query returns one row, or select no record if the query returns more rows?
I require to select a row if there is only one row exists, if there are more rows, it should select 0 rows. Answer If you’re using PL/SQL, then selecting the column using select-into will throw a too_many_rows exception if there’s more than one row returned: If you want to do this just using SQL, …
How to guarantee atomic SQL inserts with subqueries?
Given a simplified table structure like this: Can I use a subquery like this for inserting records without causing a race condition? Or are subqueries not atomic? I’m worried about simultaneous INSERTs grabbing the same value for num and then causing a unique constraint violation. Answer Yes, this can m…