We’ve been having a few instances per day where we get a slew of SQL Timeout errors from multiple applications (System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.) We have over 100 different applications o…
Tag: sql
How to get the current effective date in Oracle?
I have a table like the following: TID TName EffectiveDate 1 A 2011-7-1 2 A 2011-8-1 3 A 2011-9-1 4 A 2011-10-1 5 B 2011-8-1 6 B 2011-9-1 7 B 2011-10-1 8 C 2011-9-1 If today is 2011-9-10, I wish the query result will be like this: TID TName EffectiveDate Status 1 A 2011-7-1 Invalid 2 A 2011-8-1 Invalid 3
Can all SQL queries be represented in Relational Algebra, Domain and Tuple relational calculus
My query includes a having and count or all in. How are these represented in RA/DRC/TRC? Would I have to simplify my SQL query even more? Here is a simplified example: empl(employee (primary key), …
SQL query to find distinct values in two tables?
I would like to find the distinct Code values and get a return like this: I can’t figure out how to write a SQL query that would return me the above results. Anyone have any experience with a query like this or similar? Answer In proper RDBMS: In MySQL… the UNION removes duplicates
left join returning more than expected
Using the following query table1 returns 16 rows and table2 returns 35 rows. I was expecting the above query to return 16 rows because of the left join, but it is returning 35 rows. right join also returns 35 rows Why is this happening and how do I get it to return 16 rows? Answer LEFT JOIN can return multipl…
How can I return pivot table output in MySQL?
If I have a MySQL table looking something like this: company_name action pagecount ——————————- Company A PRINT 3 Company A PRINT 2 Company A PRINT 3 Company B EMAIL Company B PRINT 2 Company B PRINT 2 Company B PRINT 1 Company A PRINT 3 Is it possibl…
How do I make the rows of a lookup table into the columns of a query?
I have three tables: students, interests, and interest_lookup. Students has the cols student_id and name. Interests has the cols interest_id and interest_name. Interest_lookup has the cols student_id and interest_id. To find out what interests a student has I do What I want to do is get a result set like wher…
How do you Select TOP x but still get a COUNT of the whole query?
I’m writing a webpage to interactively filter results based on filter criteria as it is specified by the user. I only want to return from SQL the TOP 20 rows but I want to know how many rows met the criteria (Count). I want to be able to tell the user: “here are the top 20 rows matching your crite…
Foreign Key Used in Composite Primary Key
Is it possible to use a composite foreign key as a piece of a table’s composite primary key? For instance, let’s say I have two tables: … and then in a second table, I would like to reference the foreign key in the second table’s primary key: Is there any way that I can do that? Yes, i…
Get number of values that only appear once in a column
Firstly, if it is relevant, I’m using MySQL, though I assume a solution would work across DB products. My problem is thus: I have a simple table with a single column. There are no constraints on the column. Within this column there is some simple data, e.g. I need to get the number/count of values that …