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

learn more… | top users | synonyms (2)

-3
votes
1answer
33 views

Problem with INSERT INTO [closed]

I can't make INSERT INTO Booking work. My code: INSERT INTO Gaest (pasNr ,fuldNavn , adresse, tlf , email ) VALUES ('1234', 'Simon jensen', 'Heimdalsgade 6, 9000','6691612' , ...
0
votes
0answers
6 views

Using start and end event logs to create a table/view containing spans between the times of each log [duplicate]

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
0
votes
3answers
54 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
3
votes
2answers
98 views

What to do when your AlwaysOn cluster loses quorum?

I was reviewing our company DR procedures and when I looked online for solutions to an AlwaysOn Cluster losing quorum, to compare to. I was three pages into google results before finding the first SE ...
2
votes
2answers
47 views

select/union output from two columns into three

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment. I have a table hostname_facts with three columns: hostname name value hostname is a list of ...
3
votes
1answer
58 views

Nested SQL Query (Query a subset of data)

Assume I have the following structure... Structures - ID - Name Fields - ID - StructureID - Key These table hold simplistic 'structures' (Think very simplistic tables). There is then an ...
3
votes
1answer
88 views

Is it possible to extract the multiple variable parts of data into different columns without a procedure?

I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further ...
0
votes
0answers
22 views

Slow Query - Optimize - PostgreSQL 9.2

I've got a slow query.. I'd like to make it faster.. Make add an index? Query: SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, ...
1
vote
1answer
30 views

Trying to understand how an auto increment primary key is better than no primary key and some other primary key questions

I am trying to understand primary keys better, how to use them effectively in table design and in queries. First, are the primary keys themselves used in WHERE clauses? For example, if I have a table ...
6
votes
2answers
162 views

SQL Server: Accurate Row Estimates without Histogram?

I'm exploring the optimizer in SQL Server, particularly histograms and I'm seeing some strange, results. If I execute the query below, where I know there is no histogram on the field, SQL Server ...
1
vote
3answers
111 views

Restart of rownumber based on count in other table in oracle sql

I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) ...
2
votes
1answer
57 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
-1
votes
1answer
73 views

SQL query without temporary table

I want to modify this sql query from this post: List databases and tablespace in SQL Server 2014. IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL DROP TABLE #space CREATE TABLE #space ( ...
0
votes
0answers
8 views

Collect data with central management servers and keep them in local table [duplicate]

I have 50 servers. I manage them from a central management server. I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect ...
4
votes
3answers
69 views

Collect data with central management servers and keep them in local table

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository. I could open a query and get result for each one at the same time. But ...
1
vote
1answer
44 views

SQLite query rows nearest to hour

My question is similar to the one asked here. I'm new to SQL and I'm creating a weather app that stores part of the data locally in a db, so I'm writing the query using the sqlitedatabase java class ...
-2
votes
1answer
32 views

Merging two rows when there is a duplicate value in one of the columns [closed]

Consider a table T1, having columns C1, C2, C3... C1 C2 C3 CAD PF1 100 CAD PF2 200 USD PF3 300 Result should be C1 C2 ...
7
votes
1answer
87 views

Why does adding a smallint column to a postgres query increase execution time 60X?

Here I have what seems to me to be a routine table and a table creation script that I have put in /tmp/try.sql. I am running postgres under OSX 10.10.2. Here is the table: cow_dev=# \d carc ...
2
votes
1answer
55 views

I need help with a SQL-query that results in an error

I get the following error when I try to delete a record in the user table that has an entry in the feedback table: e.g. when a record in the feedback table has a reference to the user table ...
1
vote
2answers
35 views

Update audit database name in triggers and views of main database?

I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No ...
1
vote
3answers
66 views

How to retrieve the latest guid value after you have inserted a new row in realtime

Goal: Retrieve the latest guid value in real time after you have inserted the the value in the table Problem: Don't know how to do it Info: *You can only add a new vale that is address and zip code ...
0
votes
0answers
17 views

How to dynamically query values into their own columns [duplicate]

First of all, I'm aware that the wording may not be perfect, I'll glad to fix it with better suggestions. I have a table that relates some costumers to some products, like so: costumer_id ...
1
vote
1answer
72 views

SQL current timestamp

I have made a featureclass for a utility company where they can register measures set up to mitigate problems with rats, called "mitigation measure". From this feature class it is possible to state if ...
2
votes
0answers
39 views

Database Corruption - object in sys.parameters does not have a matching row in sys.objects

I'm using SQL Server 2008 R2 and I have a strange db corruption. I ran dbcc check db and the output is: Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters ...
4
votes
1answer
392 views

Why did adding a secondary index not increase select performance?

I have a table in MySQL called messages, which looks like this : id (primary key) | description | created_at(timestamp) This table is supposed to hold chat messages between users of my ...
0
votes
2answers
51 views

SQL select default parameter [closed]

I have a table with columns like name, age, email etc. For some columns I also have parameters to control the behaviour of queries. I want to show the column's value if the corresponding parameter has ...
0
votes
1answer
41 views

Three queries returning 1 row each

So.. I've got: 3 DB Queries returning 1 row each .... SELECT value FROM ja_settings WHERE varname='clickatel_api_id' SELECT value FROM ja_settings WHERE varname='clickatel_user' SELECT value FROM ...
7
votes
3answers
165 views

How to convert default instance to named instance in SQL Server 2012?

How to convert default instance to named instance in SQL Server 2012? By mistake I have chosen default instance option and installed. Now I have to change to named instance. How to change?
3
votes
2answers
47 views

How do you join tables at different points based on data in the first table?

Complicated question: I have 3 tables that I am joining to pull data. The first is a 'shift' table; the second a 'schedule' table, and the last is a 'shift_info' table. shift_info ...
0
votes
2answers
54 views

SQL temporary variable query

Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery AND typeId = (SELECT @id = typeId FROM EmpType, near @id =. I'm looking at getting the typeid in a ...
2
votes
1answer
53 views

What Host Name is my application connecting to SQL Server on

We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster. So ...
3
votes
1answer
38 views

How to convert following mysql query to oracle?

I have a mysql query like following, Column time contains date in long format. SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time, SUM(CreatedInstanceCount) AS ...
1
vote
1answer
45 views

Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

I've heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don't know if the type of table involved were supposed to be MyISAM or InnoDB), ...
3
votes
2answers
60 views

SQL Server “fix” query for Hash Match instead of Nested Loops

At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened. To debug what was going on I used an old dataset and compared both queries. On the ...
1
vote
1answer
76 views

Simple trigger to update two columns in one table

I want to create a trigger MySQL that updates two fields at the same time. Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one ...
3
votes
3answers
146 views

Choosing a PK: VARCHAR(2) or SMALLINT(2)?

Today we were discussing the following: Brazil has 27 states and each one has its own abbreviation (just like USA). So we've got RJ for Rio de Janeiro, SP for São Paulo, MG for Minas Gerais and so ...
8
votes
6answers
524 views

Switching values in a column with one update statement

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one ...
3
votes
2answers
100 views

Solving MySQL Query

I am trying to solve the following query with these data: Some assumptions will need to be made. Please list any such assumptions Users: +---------+---------+--------+-----+--------+ | USER_ID | ...
-5
votes
1answer
47 views

#1064 - You have an error in your SQL syntax; [closed]

I am trying to create index on a specific column, My syntax CREATE INDEX indexname ON test1; But i got a error #1064 - You have an error in your SQL syntax; check the manual that corresponds to ...
3
votes
3answers
204 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
0
votes
1answer
35 views

MySQL error 1066 - Error Code: 1066. Not unique table/alias: 'TAXES'

I have two MySQL statemenents: SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL, SUM(TICKETLINES.UNITS) AS UNITS, SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) ...
0
votes
1answer
24 views

How do I get a list of “large data users” from my table?

This situation is a little specific so bear with me. I have a (very) large table with many users (from a sync server). The users are stored by a hash identifier "username". Each user has many rows in ...
1
vote
2answers
79 views

Finding the names and the year of the movies that have at least 5 actors

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names and the year of the movies that have at least 5 actors. My attempt: The ...
1
vote
1answer
41 views

Understanding the flaw in the query

Suppose we have a relation Employee={empId,name,department, salary} and we are interested in finding the employees who get more salary than anyone in department 5. Would the following query work? ...
3
votes
2answers
199 views

Find the days difference between two dates per month

I have a table 'data' in which there are two columns i.e. Startdate and Enddate. Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to ...
7
votes
1answer
145 views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
0
votes
0answers
17 views

relationships in access

I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI. I have ...
0
votes
1answer
52 views

mysql query taking differently long

I need some enlightenment on a MySQL query issue: I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me). To be ...
0
votes
1answer
30 views

Understanding the given correlated subquery

I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks} Select Sname from Student s1 where (select count (*) from Student s2 where ...
0
votes
2answers
52 views

Insert Data in seconds between 30 minutes time interval [duplicate]

I want to divide my time interval from the start time to end time. The elapsed time should be divided across 30 minute time intervals so that for this input 08:05:13 12:09:49 I should get ...