An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

-3
votes
0answers
41 views

Querying for commentator repo and poster repo for different values of the comment ID (stored in a table) in SEDE [migrated]

How can I modify the following query SELECT CASE WHEN U.Id = P.OwnerUserId THEN 'Poster' ELSE 'Commenter' END AS [User], U.Id AS [User Link], U.Reputation AS [User Rep] FROM Comments AS C INNER ...
0
votes
1answer
32 views

Optimize this query: SQL

I have this query in MySQL: SELECT p.idSentence, p.idPath, p.token, p.posTag, p.ner, p.isTV, r.relationType, t.tag FROM path p LEFT OUTER JOIN relation ...
-1
votes
2answers
54 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
1
vote
2answers
59 views

Implementing voting system like reddit/StackExchange?

In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured. So far, I have ...
1
vote
1answer
31 views

SQL Server 2008 R2 - Limit viewing on database list

In SQL Server 2008 R2 I have user1, he owns 3 db (db1 , db2, db3) and I want he can see only these 3 db when he login by using SSMS. I did REVOKE VIEW ANY DATABASE FROM PUBLIC, but then he cannot see ...
2
votes
0answers
62 views

Querying for reputation of the commenter and its addressee using SEDE [closed]

How can I modify this Data Explorer query for the case when user of the comment is same as the poster, then the query should look for the tagged user in the comment and print the tagged user ...
0
votes
0answers
29 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
0
votes
3answers
74 views

SQL Server: CONVERT From ISO8601 To DateTime Within Query

I have a Microsoft SQL Server 2008 R2 database where the DateTime column is not a datetime data type, but varchar(50). Our website logs that data and I don't really have control over it (I'm sure a ...
0
votes
1answer
57 views

Question on relation schema questions

Assume the following relation schema: S(A,B) T(B,C) Rewrite the following expressions without using join or outer join operators explicitly: S LEFT OUTER JOIN T S FULL OUTER JOIN T How do I do ...
3
votes
1answer
79 views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
0
votes
2answers
33 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
4
votes
2answers
76 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
0
votes
0answers
57 views

How control 27 locations and 27 databases? [closed]

We have 27 locations and 27 databases (all are SQL Server 2012 R2) All of them are the same (same tables,views,...), with different data for each location and separated from each other (in each town) ...
-1
votes
0answers
56 views

CTE Hanging with Larger Data Set

I have a CTE that creates a hierarchy and looks for circular references. The table it searches lookes like this: Parent_IndicatorID | Child_IndicatorID | SortOrder My CTE is the following: ;with ...
16
votes
2answers
582 views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...
3
votes
3answers
184 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
1
vote
1answer
50 views

SQL INDEX - how it works? [duplicate]

See this question here: Database Administrators meta My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was ...
1
vote
1answer
50 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
0
votes
1answer
39 views

Low-level relational operators from SQL

I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement; SELECT d.name, k.city FROM DOG d, KENNEL k WHERE ...
1
vote
0answers
31 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
1
vote
1answer
196 views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
0
votes
1answer
43 views

limiting rows in joins

So I have this problem with one of my queries. The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows id | ...
5
votes
3answers
197 views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
2
votes
2answers
71 views

Problem translating MySQL to PostgreSQL

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work. In the below, all I did this far is replace IFNULL() with COALESCE() and ...
0
votes
1answer
40 views

Why does SQlite return incorrect sum?

CREATE TABLE "Test" ("ItemQty" DOUBLE NOT NULL ); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES (1.4); Insert Into Test VALUES ...
1
vote
1answer
31 views

RETURN NEXT in Postgres Function

I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data. I was originally going to return an array, but I found a resource that explains the ...
1
vote
1answer
43 views

select and count based on a condition

I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and I want to select from key_reports for each proj_id: `- total number of `key_id` which can be done ...
0
votes
1answer
42 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
0
votes
1answer
87 views

Monitoring sql server

This question is related to monitoring of sql server. I want to send an email alert with all open transactions on SQL Server in HTML report whenever there is sustained 50-60% CPU usage for more than ...
0
votes
3answers
34 views

How to create an Access query to find # symbol into a field?

It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector. I'm looking for the fields where there are # symbol but I'm getting zero ...
1
vote
0answers
63 views

Phantom insert query

I have a log table which I have to clean up a bit because it is growing very fast. While deleting some historical data I reached count zero for that condition on which I have deleted the entries. 5 ...
0
votes
1answer
32 views

Login failed for user '' reason:The account is disabled sql error

i got the error after change my SQL windows authentication security login set enabled to Disabled . But after that i dont able to reset windows authentication security login please help me ...
0
votes
1answer
51 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
0
votes
0answers
41 views

Accidentally deleted records [duplicate]

I would like to know if it is possible to restore a deleted record directly from the transaction log without doing a DB restore to a test database to retrieve the deleted records? I could be wrong ...
0
votes
1answer
31 views

Restore InnoDB from .frm files

My database crashed and my PC and I now only have the .frm and .ibd files left. Is there any way to restore my database?
1
vote
2answers
41 views

Oracle result cache behaviour

Oracle by default caching the query results and function results(Buffer cache). I have noticed this with AutoTrace utility, where the physical reads are huge on first execution, but from next ...
2
votes
1answer
53 views

Saving passport information [closed]

For starters: Our company is based in Germany and thus is based on German law. Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored. Now we're ...
0
votes
1answer
23 views

How to overwrite csv file of MySQL result set?

My SQL query is given below, select company.Company_name as COMPANY_NAME, IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE, IFNULL((select Full_name from user where user_id = followup.user_id), ...
0
votes
1answer
52 views

How to shift a value one record 'down'

Suppose we have a field ("Flag"). How we can shift records of this flag in another field ("ShiftField") by using SQL code? See the example: ID Flag ShiftFlag 1 0 2 1 0 3 1 ...
0
votes
3answers
50 views

Get only future records

I have the following query: SELECT master.master_id, master.showname, MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date, MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS ...
1
vote
1answer
24 views

merge rows based on column

I have attendance_log table : +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
59 views

How to solve the error Msg 547, Level 16, State 0, Line 1 [closed]

Please suggest me how to solve the error How to solve the error Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The ...
0
votes
1answer
17 views

mysql - Waiting for table level lock on a table

I have a problem with my website.. The last few days, at random times the website has become very slow. I started to investigate the best I could. I saw that the MySQL process was using 85 - 95% of ...
0
votes
1answer
36 views

How to query transfers for single source to single destination?

This should probably be easier than I'm making it, but I'm scratching my head coming up with an efficient way to query the results I want. I'm working with a table that has a source field and a ...
1
vote
1answer
69 views

Inserting to table: Insert rows with Left Outer Join or Insert rows then Update?

I have a table of Orders and another table of Deliveries. The Orders table will contain a reference to the Deliveries table if there is a corresponding row. Not every row in Orders will have a row in ...
0
votes
1answer
33 views

Database design for eCommerce website with multiple products having huge specifications index

I am designing a web site for online shopping and I have a question related to that. As you know that shopping website may have different product with different statistic/specification. What is the ...
0
votes
0answers
29 views

how can i achieve this

I have a access table for which i want to design a form, where user will enter the following info. User will enter this info on weekly basis Period No Week No Canadian Dollar rate US Dollar Rate ...
-1
votes
1answer
32 views

Oracle Database XE 11.2 Question on how to get started [closed]

I have installed Oracle Database XE 11.2 on my laptop PC. As I am new to Oracle and to SQL in general, I am finding it difficult to get started. I have searched but couldn't find any "step-by-step" ...
0
votes
0answers
34 views

How to increase application performance with Centralised SQL Server [on hold]

I’m trying to get my head around to make better way to increase the performance of my application System based on single SQL Server with 50 clients tablets pc(its connected to local wireless). my ...
0
votes
0answers
36 views

Transaction Time out error C#

Database Type : SQL server 2008 R2 Issue description: There are two machines. One is server machine(OS- Windows server Enterprise) another one is client machine(OS- Windows 7 professional). In Both ...