Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (1)

11
votes
3answers
2k 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
152 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
626 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
59 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) ...
19
votes
2answers
929 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
266 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
84 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
59 views

Consolidating large table

I need some help with a complex query. Table def: Table "public.agent_log" Column | Type | Modifiers ...
1
vote
1answer
447 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
168 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
3
votes
1answer
4k 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, ...
1
vote
1answer
55 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
9k 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 ...
3
votes
2answers
523 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 ...
1
vote
1answer
3k 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
163 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 ...
2
votes
1answer
202 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
0answers
98 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
3answers
561 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
120 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
0answers
1k views

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

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
102 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
44 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
95 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
186 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
166 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
2k 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
428 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
2answers
171 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 ...
2
votes
1answer
331 views

merge rows based on column

I have attendance_log table : +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
1answer
14k 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
507 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
87 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 ...
2
votes
2answers
625 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 ...
1
vote
1answer
8k views

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

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 ...
-1
votes
1answer
2k 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
368 views

How to increase application performance with Centralised SQL Server [closed]

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
657 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 ...
1
vote
1answer
907 views

Using EncryptByPassPhrase in where condition

I am using EncryptByPassPhrase of sql server. But i am not able to use where condition properly. DECLARE @login_details TABLE(uid integer,username varchar(10),password varbinary(100)) insert into ...
0
votes
5answers
1k views

Is it impossible to store 2^266 records on a database of some kind? [closed]

Each records weighs 52 bytes, is it possible in today's technology if someone had the storage needed? What kind of db would hold the data and will make it possible to retrieve it. An example record: ...
0
votes
0answers
61 views

Reduce read write delays in specific database setup

I have a database with a large table with 15 columns (type double). It contains a large amount of data (~ 100 rows of data for every second. Holds 3 days of data). I also have a real-time program that ...
0
votes
1answer
1k views

Delete Query takes a long time

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. ...
5
votes
2answers
14k views

How to Log All mysql queries into log file?

I am using Mysql 5.6.12 under Wamp server environment. Now I want to log All queries into ".log" file, the queries which are running by PHP or from PHPMyAdmin, I want to log them...
1
vote
1answer
399 views

Difference between COUNT(ColName) vs COUNT(ALL(ColName))

Both query return the same output: select COUNT(id) from Employee select COUNT(All(id)) from Employee So what is the difference in SQL-Server 2008R2?
0
votes
1answer
511 views

UPDATE CONCAT without duplicates

I would like to concatenate multiple values ​​and remove duplicates (case-insensitive) in the same field with SQL only. Without using a unique key or stored procedure. Ex. ...
0
votes
1answer
5k views

How to insert default values to a table with not null columns?

I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required). I would like to write a query that inserts a row with ...
2
votes
6answers
4k views

Efficiently storing irregular/repeating intervals (think calendar/events)

I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing. Right now ...
3
votes
1answer
830 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
0
votes
1answer
1k views

Postgresql Database on external HD

I'm completely inexperienced with PostgreSQL, but have to do some work on it, and I have been googling for a solution for almost two days. I hope someone here can help me out. I have a PostgreSQL ...
0
votes
2answers
76 views

MySQL query optimization for legacy application

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. ...