Lets say I have a Users table with a UserID column and I need to model a scenario where a user can have multiple relationships with another user, e.g. phone calls. Any user can initiate 0...n phone calls with another user. Would it be a classic junction table like: UserCalls ----------------------- | CallerID | CalleeID | ------------...
I have the following tables: platforms(id,platformname) games(id,title,platformid) gameslists(id,memberid,gameid) I would like to select all records from the games table, but exclude records where games.gameid is present in gameslists for a specific member. So in plain English: select all records from the games table except for those ...
Using SQLite, Got a table with ~10 columns. Theres ~25million rows. That table has an INDEX on 'sid, uid, area, type'. I run a select like so: SELECT sid from actions where uid=1234 and area=1 and type=2 That returns me 1571 results, and takes 4 minutes to complete. Is that sane? I'm far from an SQL expert, so hopefully someone ca...
I have an sql: SELECT Sum(Field1), Sum(Field2), Sum(Field1)+Sum(Field2) FROM Table GROUP BY DateField HAVING Sum(Field1)+Sum(Field2)<>0; Problem is sometimes Sum of field1 and field2 is value like: 9.5-10.3 and the result is -0,800000000000001. Could anybody explain why this happens and how to solve it? ...
I have a MySQL table "stats", which is a list of entries for each login into a website. Each entry has a "userId" string, a "loginTime" timestamp and other fields. There can be more than one entry for each user - one for each login that he makes. I want to write a query that will calculate the average of unique daily logins over, say, 30...
Hi, What are the combination of characters for a table name in SQLite to be valid? Are all combinations of alphanumerics (A-Z, a-z and 0-9) constitute a valid name? Ex. CREATE TABLE 123abc(...); What about a combination of alphanumerics with dashes "-" and periods ".", is that valid as well? Ex. CREATE TABLE 123abc.txt(...); Ex. CR...
there is a time slot table. it exists on two or more computers. the table maintains the reservation of the time slot for the complete year. assume that the connection between the servers breaks. user on site1, enters data like :-- timeslot(3:8) reserved for(this) where timeslot is the primary key user on site2 does the same. pro...
Hi all I use Entity Framework to access my SQL data. I have some constraints in the database schema and I wonder how to handle exceptions that are caused by these constraints. As example, I get the following exception in a case where two users try to add an (almost) identical entity to the DB concurrently. System.Data.UpdateException ...
On SQL Server 2008, how can I place my stored procedures in "Stored Procedures" folder of my DB? When I declare it this way: CREATE PROCEDURE mySchema.myProc It goes to: MYSERVER\System Databases\Master\Programmability\Stored procedures folder. How to tell server, to store it in: MYSERVER\System Databases\MYDB\Programmability\Sto...
Hi, i have something like 40K rows and i must run update query like this: UPDATE table1 SET column1='very long value 1...', column2='normal lenght value 1a' WHERE pid ='123' column3='ccc'; column1 is TEXT column2 is Varchar(150) ...and i want to use mysql query what is more server resource friendly. I want to use something lik...
Hi, I've got two tables. I'm trying to calculating the SUM quantity of tbl1 tbl1.xid is the primary, while tbl2.xid is the foreign tbl1 xid pub quantity 1 1 10 2 1 2 3 0 1 4 1 5 tbl2 id ttype fno xid qnty 1 A 0 1 0 2 A 1 1 3 3 B 1 1 4 4 A ...
Hi, I have a table which looks like that: As You see, there are some date duplicates, so how to select only one row for each date in that table? the column 'id_from_other_table' is from INNER JOIN with the table above ...
So I've been doing this all night - can't quite understand my homework, and sadly my professor is unavailable on the weekend. I've posted a few of these questions, this being the last one. I've got something to go on, but it needs working (and coming out of this I'd love to fully understand the answer so I don't need help on something ...
I have two tables, one stores the users, the other stores the users' email addresses. table users: (userId, username, etc) table userEmail: (emailId, userId, email) I would like to do a query that allows me to fetch the latest email address along with the user record. I'm basically looking for a query that says FIRST ORDER BY userEm...
I have a 22 machine cluster with a common NFS mount. On each machine, I am able to start a new MySQL instance. I finished creating a table with about 71 million entries and started an ADD INDEX operation. It's been more than 12 hours and the operation is still going on. So what I logged onto one of my other machines in the cluster, start...
We're thinking of storing SQL queries in a separate table. These queries are solely for reporting purposes. Different queries for different reports - moreover the queries would contain placeholders so we can either format or use prepared statements from the Winforms front end (it's a simple 2-tier reporting app) to e.g. format the date r...
I'm using SQL Server 2008. I've got a column NVARCHAR(MAX) in a table which I want to make sure is unique. The table has 600,000 records and grows every day by 50,000 records. Currently before adding an item to the table I check if it exists in the table and if not I insert it. IF NOT EXISTS (SELECT * FROM Softs Where Title = 'exampl...
I want to store a tree structure of arbitrary depth in an SQL database (MySQL, but want to avoid DBMS-specific features). Now I want to compute a value N for each node as follows: first, calculate the sum of a certain column in all (immediate) children of the current node which are leaves (i.e. have no children) then, calculate the max...
When I need to get the number of row(data) inside a SQLite database, I run the following pseudo code. cmd = "SELECT Count(*) FROM benchmark" res = runcommand(cmd) read res to get result. But, I'm not sure if it's the best way to go. What would be the optimum way to get the number of data in a SQLite DB? I use python for accessing SQLi...
I have a script that parses xml file to generate SQLite table automatically. And, the simplified command is as follows. Table string CREATE TABLE IF NOT EXISTS benchmark (id integer primary key autoincrement,Version float, CompilationParameters_Family text, CompilationParameters_XilinxVersion text, CompilationParameters_Device text, C...