Hey, I am trying to create a system to match wildcard domain names. Although the query above will work for me is it possible to index it at all? I don't have access to the DB to do any clever stuff as I am using Heroku to host my app but I can add indexes easy enough? Thanks! Edit: I would like to match wildcard domain names in my da...
I am a newbie to SQL, I am using this query to look for the minimum value in the field weight of my table. SELECT product_id, MIN(weight) FROM table WHERE 1; It does show one field with the min value, but only one? But I have many products with the same minimum weight. Is there a way I could specify that I need to show a...
Suppose that I am using a sql server to keep track of all my personal expenses and that I am tagging everything with a date and a category. This allows me to do things like aggregate monthly expenses per category and look at the last several months of expenses with each category as a row and the most recent months as columns. What ...
Is there anything built into Ibatis that helps prevent SQL Injection attacks? I'm not looking for a list of ways outside of Ibatis to do this, and am just wondering if Ibatis has anything to prevent SQL Injection. ...
Almost every table in every database I've seen in my 7 years of development experience has an auto-incrementing primary key. Why is this? If I have a table of U.S. states where each state where each state must have a unique name, what's the use of an auto-incrementing primary key? Why not just use the state name as the primary key? Seems...
I seem to be having some trouble grasping the concept of using AS to create an alias, and then using that alias later. Here is an example of a very simple query that I get an error when I run: SELECT IP, (SELECT server_ip as IP FROM table1 WHERE start_time BETWEEN @startdate AND @enddate Group By...
How can I get the next available primary key from my table? The primary key is a numeric. I'm using C#. ...
I have the following SQL statement: SELECT CONNECT_BY_ROOT ANIMAL_ID "ORIGINAL_ANIMAL" , ANIMAL_ID, LINE_ID, SIRE_ANIMAL_ID, DAM_ANIMAL_ID, LEVEL -1 "LEVEL" FROM ANIMALS START WITH ANIMAL_ID IN( '2360000002558' ) CONNECT BY ((PRIOR SIRE_ANIMAL_ID = ANIMAL_ID and LEVEL < 5) OR (PRIOR DAM_ANIMAL_ID = ANIMAL_ID AND LEVEL < ...
I have a table of items which I'm getting from a few different online stores, like Ebay/Amazon, etc. Before today, I wanted to simply group each item based on the year it was made and the manufacturer who made it. So, the results would look something like this total year manufacturer 100 1999 man_a 32 2002 man_b Now, I wan...
I currently have two tables, one with documents, and another with ratings doc_id | doc_groupid | doc_name | doc_time and then rating_id | rating_docid | rating_score where rating_score is either -1 or 1. What I need to do is have a single query that retrieves every column in the document table WHERE groupid = #, but also has colum...
what is the best way to write a select statement with two table that have a one to many relation ship? I have a table called broker with these fields id companyname then another table called brokerContact that has a foreign key to the broker table with these fields id brokerid contact name phone How can I write a select statement...
Hello All, Was wondering if someone could assist with some Postgres. I have a table which has a column called mydate which is a postgres date type. I want to do something like: SELECT * FROM MyTable WHERE mydate > [Today-1year] I've never used Postgres before and I'm sure I just need to know the name of some functions- I'll gladly l...
I have the following snippet: CONVERT(varchar, FLOOR(ROUND((DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(minute, lunchStart.timeEntered, lunchEnd.timeEntered)) * 1.0 / 60, 2, 1))) + ':' + CONVERT(varchar, ROUND(ROUND(DATEDIFF(minute, shiftStart.timeEntered, shif...
I know SQL pretty well for joins and queries, but when it comes to this I have no idea. Can someone take a crack at this, please? How would you create the data in table1 given the data in table2? Write an SQL query. Table1 (csv): order_id1, prod_id1 order_id1, prod_id2 order_id1, prod_id3 order_id1, prod_id4 order_id1, prod_id4 Tabl...
So, it seems to me like a query on a table with 10k records and a query on a table with 10mil records are almost equally fast if they are both fetching roughly the same number of records and making good use of simple indexes(auto increment, record id type indexed field). My question is, will this extend to a table with close to 4 billio...
Hi, I am writing a query on Mysql . I have to find the youngest customer and in my database , I have 2 persons with same dob. I am doing it in a way that first it checks all the conditions and then order by dob desc, limit 1. It gives me only 1 result. If I set the limit 2, I get the second person but setting limit according to database...
I have to gather data from a sql database to populate a ning map i am creating. I only need data from one table in the database. The table has company data in it, name country state, email, website, etc. What is the best way to do this? I am doing my bing map using .net so i was thinking of just gathering all my data in code behind i...
Disclaimer: I'm an SQL newb and this is for a class, but I could really use a poke in the right direction. I've got these three tables: student(_sid_, sname, sex, age, year, gpa) section(_dname_, _cno_, _sectno_, pname) enroll(_sid_, grade, _dname_, _cno_, _sectno_) (primary keys denoted by underscores) I'm trying to write an Oracle...
I'm about to implement this function to calculate some numbers. CREATE FUNCTION [dbo].[funLookupFTE] (@PFID int) RETURNS VARCHAR(20) AS BEGIN DECLARE @NumberOfFTE AS VARCHAR(20) SET @NumberOfFTE = (SELECT SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1 END) AS FTECount FROM tblPractitioners ...
Let assume we have these tables: product product_id product_name category category_id category_name product_in_category product_in_category_id product_id category_id how would you get all products that are not in specific category in the product_in_category table (without duplicates). In other words, all products t...