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

learn more… | top users | synonyms (2)

0
votes
1answer
51 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
57 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
61 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
48 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
277 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
22 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
41 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
156 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. ...
1
vote
0answers
34 views

SQL Server dynamic inserts based on another table's rows

I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert ...
0
votes
1answer
28 views

WSFC File Share Witness

I've got a multi-site 2008 R2 WSFC with a file share witness. I noticed that the possible owners for the FSW is every node in the cluster. I thought the concept of a FSW was that no node of the ...
3
votes
3answers
56 views

How to Pivot in PostgreSQL

Having the following data in a table: ID Category Value 1234 Cat01 V001 1234 Cat02 V002 1234 Cat03 V003 1234 Cat03 V004 1234 Cat03 V005 I want to have the ...
1
vote
2answers
59 views

How to get the PC models with identical speed?

I am trying to get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i). Result required as : model with ...
0
votes
0answers
43 views

MySQL apostrophes suddenly handled differently

I have 2 production servers with exact same MySQL version (mysql Ver 14.14 Distrib 5.6.26-74.0, for Linux (x86_64) using 6.0). In one, a stored procedure continues to run properly off of a ...
0
votes
1answer
80 views

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time. I am trying to ...
1
vote
1answer
56 views

PostgreSQL 9.5: Permission to deny functions body

I create new role without any grants. New user can't select on tables, but can see functions body (pl/PgSQL) I want permissions for new role: allow INSERT into one table. deny see tables, ...
1
vote
2answers
59 views

Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups

If I were to backup 50 databases in SQL server, I want to make sure that backup is taking place when the server is not that busy. Is there any way or script/tool to use to check when the server is ...
0
votes
3answers
54 views

How to select mutiple values with like clause

SELECT * from table WHERE column LIKE "AAA%" OR column LIKE "BBB%" OR column LIKE "CCC%" I have more than 10 values to select, so it is not appropriate to write like the above example. Is ...
0
votes
1answer
60 views

open/execute huge sql file

I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using navicat premium,razor SQL, HeidiSQL, workbench. In all I get out of memory. ...
0
votes
2answers
66 views

How to count specific products?

I have a table product and its columns are maker, type and model. I want to get the makers who produce 6 product types and more than one model. Here is something I am trying: select maker, type from ...
0
votes
1answer
31 views

how to count specific product in sql table

I have a table product and its fields are maker, type and model I want to get the makers who produce only one product type and more than one model. Here is something I am trying select count(distinct ...
1
vote
2answers
108 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
0
votes
1answer
37 views

Creating a pseudo email database

I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these: Employer ID int ...
2
votes
1answer
24 views

How to count multiple values of a column as one group? [duplicate]

I have a table main with the following rows: id | name | rank ----+--------+------ 1 | Ali | a 2 | Sami | b 3 | Khan | c 4 | Kamran | d 5 | Imran | e 6 | Asad | a 7 | ...
0
votes
0answers
26 views

How can I create a user that can only create databases and users?

What I want is a user (#1) that can create other users and databases (#2, #3, n). To clarify, user #1 should NOT be able to manipulate any databases. Even the ones created by himself. Users (#2, #3, ...
0
votes
1answer
22 views

Aggregate function to count occurrences of specific word

I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an ...