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

learn more… | top users | synonyms (1)

0
votes
0answers
42 views

Our app lags “randomly” due to Disk I/O spikes?

Our database (InnobDb) is spiking at 99% I/O at random times. After the spike it goes down to about 2% until it spikes agian. There aren't any spesefic, huge queries running in these intervals that ...
0
votes
1answer
16 views

Table that is a relation of one field to every other similar field

So, I have two tables, tblOwners and tblNonOwners. These tables are filled with users. I need a third table that keeps track of if any one user has 'seen' any other user from the other. What I ...
-1
votes
1answer
38 views

Using BIT datatype to filter a query in the WHERE clause [closed]

I'm building a stored procedure that uses a bit parameter. this bit parameter is used to filter the email column. if @param is 1 - show all clients that has an email. if @param is 0 - show all ...
1
vote
3answers
153 views

How to decrease RAM usage of SQL Server 2012 without restarting Service

Is there a way of decreasing RAM usage of SQL Server 2012 without restarting it? Setting up MAX Memory usage is not an option as it's quickly building up to limit I have set and then app based on ...
-1
votes
1answer
57 views

Rebuild an index in SQL Server 2012

When I execute this query, my .MDF file grows by 120 GB: ALTER INDEX ALL ON TABLE_NAME REBUILD WITH (FILLFACTOR = 90) or ALTER INDEX ALL ON TABLE_NAME REBUILD Can you tell me how to avoid ...
0
votes
1answer
37 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
0
votes
0answers
25 views

How can I check logs/audit files for changes made to a view in SQL Server 2008 R2?

On a SQL Server 2008 R2 database, I have a view which may have been altered by another user. The only change that was authorized to be made was for the addition of a new column to one of the tables ...
-1
votes
1answer
87 views

MySQL how can i select 1 city per state that must have at least 1M people in it?

I have a table like this City State Population area zip New city LA 10000 +33 12345 City2 NX 1000000 +48 33333 My city LA ...
0
votes
0answers
49 views

How to document SQL statements (DDL & DML)? [duplicate]

Working on legacy code that was written in part by the software vendor and in part by the client (some for SQL Server 2012 & some for Oracle 10g). The application consumes scripts (file.txt) in ...
5
votes
3answers
633 views

Where column not like multiple values

I am trying to select records in a postgresql db where username is not like a list of strings. SELECT * FROM rails_db WHERE username NOT LIKE 'j%' AND username NOT LIKE '%eepy%'; The issue is there ...
1
vote
3answers
137 views

Need to get identify all tables and columns in a SQL Query

I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
0
votes
1answer
44 views

counting the number of a specific word in one SQL column

I'm trying to make a survey like a referundum to know if my neighboors in my small building are for or against a colectif compost to reduce the organics wastes. For this tool vote I'm using PHP and ...
0
votes
3answers
71 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND ...
15
votes
2answers
2k views

Why does SQL Injection not happen on this query inside a stored procedure?

I made the following stored procedure: ALTER PROCEDURE usp_actorBirthdays (@nameString nvarchar(100), @actorgender nvarchar(100)) AS SELECT ActorDOB, ActorName FROM tblActor WHERE ActorName LIKE '%' ...
0
votes
1answer
69 views

Finding the right pattern for creating indexes,redesign query structure

I'm planning to redesign the structure of a query which uses over 20 full/left joins for fetching different data from the tables. Because the logic of the initial query was to bring data based on ...
0
votes
1answer
39 views

Database access: what should I do here?

I'll explain my situation here, I hope I'm posting this on the correct site, otherwise I'm sorry and I'll move this to somewhere else. I've built a Java program in order to control sales, inventory, ...
2
votes
1answer
71 views

Disk I/O Utilization at 95%

My disk I/O utilization is at 95% at my MySQL server. What are steps I could take to fix this? It is a Ubuntu VP with 16 GB Memory and 8 cores. Here is a screen-shot of the load:
3
votes
3answers
301 views

Programmatically finding minimum set of fields necessary to create a unique composite key

I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for ...
0
votes
1answer
69 views

Improve performance of query with many joins

From the following explain plan: explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente, cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id, ...
0
votes
0answers
32 views

Efficiently counting Clicks grouped by Week as well as all sub-totals

I have a large and slow database 'LargeSlowTable' which records clicks by users. I only have read permission, not write, so I cannot use Global Temporary Tables. I want to count the number of clicks, ...
2
votes
3answers
74 views

String Function to Get Left 1000, But Only To Last Period or Carriage Return

I have blocks of text, anywhere between 500 and 2000 characters long. What I need to be able to do is take a maximum of 1000 characters from the description but only to the last full stop or ...
1
vote
1answer
22 views

What is the logic of a query when using like with and/or

I have a table that contains subtitles ranging from subtitles_a to subtitles_z. I'm not sure why this query isn't working? select * from subtitles where field_name like '%subtitle_u%' or field_name ...
0
votes
1answer
75 views

About employee who come out at night

I have a table like this: EmployeeId Checktime 1 2015-10-14 07:51:25.000 1 2015-10-14 18:56:47.000 1 2015-10-15 00:04:04.000 I want to get the minimum ...
0
votes
1answer
18 views

Simple Query Help Required

I have this table uid | rid ----------- 1 | 4 1 | 13 2 | 4 3 | 13 I want my query to return the uid where it has rid = 13 AND does not have rid = 4 so the result would be 13 ...
0
votes
0answers
33 views

Generate N x M random sample table from normal distribution in MySQL

Is there an easy way to produce a table of, say 10000 (rows) by 500 (columns) of numbers from normal distribution with parameters mu = 1, std.dev = 5 ?
0
votes
1answer
51 views

SQL Query help: co-workers and employees from one query

I have two tables defining users and user employments. The user table looks like this: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8_unicode_ci ...
0
votes
1answer
24 views

Remove all instances of a character from a column within tag

I don't know much about writing SQL and a client of mine has an erroneous semicolon appearing in his database that he'd like removed. It's in 1 specific column, but it should only be removed if it is ...
5
votes
3answers
100 views

Aggregate discarding values in one column that haven't a match in another column

Say, I have a table representing colored and labeled items inside numbered boxes. Each box can not contain more than one item with a particular label, but items with the same label (and the same or a ...
1
vote
1answer
33 views

Multiple increments in table

Let's say I have a table which stores different bills. There are multiple departments using this database and every department needs to use their own incremental numbering for the bills. I do not ...
0
votes
1answer
67 views

User databases inaccessible after physical restart

We have an unusual error happening on the SQL Server. After restarting the physical server where the SQL Server instance is installed, the user databases are not accessible. System databases are ...
0
votes
1answer
55 views

Whether my table is stored as heap or b-tree?

I have a SQL Server 2005 database. It’s .MDF & .LDF can be downloaded at https://www.dropbox.com/s/uj2mw410jm2m078/MyDB.rar?dl=0 Now I want to know whether two table are stored as heap or ...
8
votes
3answers
681 views

Best practice between using LEFT JOIN or NOT EXISTS

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ...
0
votes
0answers
35 views

When should I create a primary key on multiple columns? [duplicate]

I'm interested to know in which case I should create a primary key on multiple columns and why ? To be more specific about my question, I want to know what are the advantages and disadvantages of ...
0
votes
1answer
92 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
0
votes
2answers
33 views

How to isolate single values from multiple records

Hello ladies and gentlemen. If we have the following records (as example) from a query: is it possible to present the following sql result with pl/sql oracle query ? We need to have the single ...
0
votes
1answer
31 views

SQL query help: Calculating days between observations

I'm extremely new to SQL, in fact, I just really started playing around with it. So I'm not exactly sure how to search for appropriate terms related to the question I have. I'll do my best. ...
-1
votes
1answer
51 views

What is the best source for deep learning about table partitioning? [closed]

I know some of the basics of table partitioning, but I haven't used it for an operational database yet, as I went studying more about the topic, I found some information that is written for SQL server ...
2
votes
1answer
72 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
1answer
90 views

MySQL: Creating a numeric user ID from distinct combinations of IP and user agent from a server log

I'm trying to create a numeric user ID from the distinct combination of IP addresses (alphanumerically anonymized) and the user agents. The table is a pre-processed server log file and contains among ...
0
votes
0answers
21 views

SQL architecture question. User entity in 2 tables

I use Symfony 2 framework and standardized bundles to operate entities. So I have somewhat stable User Entity, which is based on the bundle structure. It contains mainly service information like ...
4
votes
2answers
184 views

How to update only part of a field

A user entered a batch of records with the wrong suffix in the record name - FY15-Q2 instead of FY16-Q1. So, the records are something like: -LAG-FY15-Q2 -AN-FY15-Q2 -OY-FY15-Q2 -RV-FY15-Q2 etc ...
0
votes
0answers
23 views

Creating Common Fields across Databases for better SQL JOINs

In my company, our quality control organization has a two different SQL Server 2012 databases that capture manufacturing defects and component failures. Each database has a table and a column for ...
1
vote
1answer
40 views

Aggregate SQL Rows into single column

I would like to aggregate results of a column into a single row using MS SQL. I have create a sql fiddle which showcases the issue: http://sqlfiddle.com/#!3/384d5/2 Basically I would like all 'Tag' ...
1
vote
1answer
49 views

SQL Schema Change; Modification of Existing Table vs Creation of New

We are planning the data schema for our project. Currently we have a table with classifications, but there is the possibility of a new classification in the future which would require a new field to ...
0
votes
2answers
43 views

Limiting rows from a joined table

I have 2 tables Part and PartDtl. PartDtl contains one row for each order that has been placed for that part. PartNum | Description | ... 1234 | 1234 desc | ... 2345 | 2345 desc | ... ...
4
votes
1answer
86 views

How to convert a Postgresql ltree structure to a nested set by using VIEW?

I would like to ask a question about a way for transforming a PostgreSQL ltree structure to a nested set structure with only one query by using only VIEWs. For example, I have a table which has data ...
1
vote
1answer
39 views

How to optimize query with max on Postgres?

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It takes over an hour ...
0
votes
0answers
26 views

Special Operators in MySQL

I am going through the MySQL doc here and reading about the operators. I couldn't find anything related to special operators over there. Hence I am wondering, is there anything that exists and falls ...
0
votes
0answers
40 views

Aggregate function within CASE or Joins

Database - MySQL I have 3 tables Table1 - Order Columns -OrderId,OrderDate Contraints- PrimaryKey-OrderId Table2 - Product Columns-ProductId, InsertDate, ...
0
votes
2answers
71 views

How to calculate streaks of consecutive dates for a user

Assume a results table like so: date|user_id A user can have one or more result per day.. I need to calculate a) users who have entered a result in 30 or more consecutive days b) preferably the ...