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

learn more… | top users | synonyms (2)

3
votes
3answers
219 views

How to return rows between two datetimes stored as date and time?

How to view the result between 2 dates and times? My current Code: SELECT * FROM [DB].[dbo].[TABLE] WHERE [USER_ID] = '005' AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] >= ...
0
votes
1answer
15 views

Server configuration SQL Server 2008

I am really fresh in the DB world so firstly,sorry about my noob question. I have a data warehouse project to perform in a group of 3 people. When installing SQL Server 2008 R2 Developer in my own ...
1
vote
1answer
24 views

How to Increment duplicate strings in a column

I have a table similar to what is below. I have listed only one column since that's what I'm interested in. | column --------- | foo | foo | foo | bar | bar | bar Below is the output that I ...
4
votes
1answer
94 views

Update with default values in case clause

I have a single table like CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); GO INSERT INTO MyTable (Name) VALUES ('test1'); INSERT INTO MyTable (Name) VALUES ('test2'); GO ALTER TABLE ...
-2
votes
2answers
105 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
0
votes
1answer
73 views

Error launching sqlplus

I try to run sqlplus on my server with the command sqlplus /as sysdba and I got this error sqlplus: error while loading shared libraries: ...
0
votes
1answer
20 views

Communition Link Failure

I want to make a DNS to a sql database by SQL Driver. so I do it as it should be done. I have over hundred Clients with Windows 7 OS. All of them can connect to the database easily just a few of them ...
1
vote
3answers
180 views

Finding count of null values in 500 columns of a single table

I have 500 columns in a table in SQL Server. I want to find the count of null values in each column. Is there a way to do it using a cursor and dynamic SQL?
0
votes
1answer
39 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
0
votes
1answer
39 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
0
votes
3answers
92 views

Select distinct value from row if condition exists

I am new to SQL and I am having trouble constructing a SELECT statement. What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I ...
3
votes
1answer
27 views

How can I assign values (-1, 0, 1) to the difference between consecutive rows in a MySQL table having 20 columns and 20 rows

I have a table from MySQL called References with 20 columns and 20 rows: CREATE TABLE bop_ppy ( start_time DATETIME (6) , end_time DATETIME (6) , high DECIMAL (5,2) , low DECIMAL (5,2) , close ...
1
vote
1answer
32 views

Is it possible to create a function for transactions that use more than one database?

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have ...
1
vote
1answer
37 views

Slow Query Postgres 9.2

I've got the following query: SELECT j.id, concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client, c.email AS client_email, ...
1
vote
1answer
51 views

Grouping similar rows together based on time

I have a table that records all the changes done to a model by a user. The table looks something like this: (SQLFiddle) In the table an action_hash is generated based on the model, model_id and ...
0
votes
2answers
57 views

SQL query for matrix multiplication

I am facing a problem in writing a sql query for multiplication of two matrices. Consider that i have a 3x3 matrices A and B, having the following schema: A(i,j,val) and B(i,j,val) where i int: Row ...
0
votes
1answer
52 views

Having primary key in both fields but repeated in either fields allowed repeating

Is it possible to have both fields to be primary key but allows repeating For either 1 field in Oracle SQL. Student_ID Date 1001 23/10/2016 // allowed 1002 23/10/2016 // ...
0
votes
1answer
52 views

Auto update a column every 24 hours in Oracle database

I want to reset the values of a column every 24 hours. Is it possible? The column contains numeric values. It's a ticket reservation system. I need to reset the number of tickets.
4
votes
5answers
139 views

Is there any best number for rows count in a table to start using indexing?

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between ...
0
votes
2answers
59 views

Does Oracle 11g support CREATE ASSERTION? [closed]

When I tried to write the following statement: CREATE ASSERTION ir_collaboration CHECK(NOT EXISTS( SELECT ngo, id_employee, COUNT(*) FROM collaboration GROUP BY ngo, id_employee HAVING ...
0
votes
1answer
49 views

Select statement from 3 tables with join [closed]

How can I select from 3 tables (task, status, employee)? Task TASK_ID (PK), TASK_NAME , DESCRIPTION , STATUS_CODE (FK for status table) START_DATE , END_DATE , TASK_TYPE_CODE (FK) , ...
1
vote
2answers
62 views

Single sql query to get the transpose of a matrix

I am facing a problem in writing a query for this question.Consider two random 3x3 (n=3) matrices A and B,having the following schema: A(i,j,val) and B(i,j,val) where i int:Row Index j int:Coloumn ...
0
votes
2answers
37 views

Getting multiple out when using NOT EQUAL operator

How to display name of the department managers (fname, lname) who have no dependent? Tried this: Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname from EMPLOYEE INNER JOIN DEPENDENT ON ...
1
vote
1answer
39 views

Grouping by more than 1 column using Partion By or any other method - Sql Server

I have the following tables in my SQL Server database: Product -Id -Name -Price -TypeId (Foreign Key) -SubTypeId (Foreign Key) Type -Id -Name SubType -Id -Name -TypeId (Foreign Key) I currently ...
0
votes
1answer
72 views

mysql 5.7 json filter and features products

sorry for my english! I need use this column(feature) for show features, filters for products and in some language(It depends on the country). I have products table(innodb) and a column(json) ...
1
vote
3answers
79 views

Remove duplicates in SQL Server Group By

I have 2 tables, Product and Type, the tables looks something like this: Product -Id -Name -TypeId Type -Id -Name I want to be able to write a SQL Server query which outputs the products grouped ...
0
votes
2answers
50 views

How can I create a join on three tables, each from a different database?

Here is a simplified example of what I'd like to do: SELECT a.create_time, b.create_time FROM DOB.dob_state AS a JOIN numID.numID_history AS b ON a.create_time = b.create_time; I'd like to join ...
1
vote
1answer
40 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
4
votes
4answers
280 views

Easy way to change Column ID (primary key) of a table to Auto increment

I started to use SQL Server recently and I still don't know the best way to do some things. I created all the tables with a column ID as the primary key. Now when I try to insert values I get the ...
1
vote
2answers
63 views

SQL Join problem [closed]

I have a problem trying to join two tables TableA +----------+--------------+ | PersonID | customerId | +----------+--------------+ | 123 | 456 | | 258 | 145 | | ...
0
votes
2answers
46 views

select only one element from an priority ordered group with limits?

This question searches for an answer in pure sql (no db-server specific code) -edit- Due comments/answers: "pure sql" - SQL92/99 because this works on all servers. I do not only use this code on ...
6
votes
1answer
542 views

AND conditions within unbracketed ORs work but why?

I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses or (1=1) to ...
1
vote
3answers
66 views

How should INNER JOIN query look?

I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it. What "pros" and "cons" have the queries shown below? All of them ...
2
votes
5answers
127 views

MIN(column) with subquery

How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe? SQL> DESC STUDENTS; Name Null? Type ...
0
votes
0answers
23 views

Parent–child relationship and querying strategies

I am using a MySQL database with an InnoDB engine and primarily interacting with it via SQLAlchemy in Python. My models / tables are defined in the SQLAlchemy ORM that allows for several convenience ...
2
votes
5answers
179 views

How to get one MAX() between SUMs of several columns in SQL? [closed]

I need to select MAX() between SUM(Math), SUM(English), SUM(Physics) Result must be: 19 | English
0
votes
1answer
37 views

Alter multiple Logins through SQL

I am trying to change the following properties for SQL users to disable password complexity and expiration. Using: ALTER LOGIN xx WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO Where 'xx' is an ...
0
votes
1answer
37 views

Help on joining 3 tables Oracle XE 11g

I am trying to return the first name, surname, subject and mark from the following tables: SQL> desc students; Name Null? Type ------------------------ -------- ...
0
votes
1answer
40 views

VB.Net error with CASE SELECT

The following VB.Net code runs fine on SQL Server but gives an error on Access: sql = "SELECT sr.itemcode, sr.itemname, SUM(CASE WHEN sr.transdate='4/14/2016' THEN sr.temunits] END) AS [Units Sold], ...
1
vote
1answer
42 views

Select Units Sold and Total Units Sold from a Sales record table

I am trying to Select Units Sold and Total Units Sold from a Sales record table using. When I use this Query, I can get the Units Sold for '2016-04-14' SELECT salesrecord.itemID, ...
-4
votes
2answers
45 views

Query orders in recent 5 years [closed]

I am using SQL Server, and I have an Order table with a column OrderDate (datetime). How can I write a query to retrieve all the orders in the past 5 years? My query: select * from Order where ...
2
votes
2answers
64 views

Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

I have a quite simple problem, but the solution is very tricky. I'm trying to solve this for months: There is a column "SortID" that needs to get updated with sequential integers, starting from 1. ...
1
vote
2answers
107 views

OrderBy int slow for large table join

I have roughly 1.5 million records. This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling. I ...
-4
votes
1answer
32 views

calculate a column based on existing columns [closed]

Simply I want to calculate a formula on specific columns in my table to populate an empty column in the same table using SQL Server.
2
votes
3answers
157 views

How can I create 3 tables with references On SQL Server

I started using SQL Server recently and I think this is simple to resolve. I need to create one table with a country list, and two tables that will use the country list in a column. Here is the ...
1
vote
0answers
16 views

Find entire hierarchy based in all hierarchy levels [duplicate]

I have a table that looks like this: Where EMP has a child SEG and it has a children SUP and so on... What I would like to do is find the full hierarchy EMP>SEG>SUP>DIR>REG>GOP in any of the ...
1
vote
2answers
53 views

Can't create a table with the same name I deleted before

I started to use SQL Server recently and I think that its simple to resolve I did: 1 - Create table table_nome 2 - Drop table table_nome 3 - I'm trying to create a table with the same name, and ...
1
vote
1answer
32 views

linked servers between availability group to a server

I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables ...
0
votes
1answer
11 views

Query Last Value in Fixed Length Array With Empty Values

I have a table that generally looks like: Name varchar | Location varchar[] --------------------------------------------- Lemoney | {Home,Work,Home,Stack Exchange,,,,,,} Other Dude | ...
0
votes
2answers
49 views

Need help with this backup audit SQL Server script

I am using below backup script taken from http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/ link to audit my servers for backups. This works great except when I have spilt backups. ...