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

learn more… | top users | synonyms (2)

1
vote
2answers
113 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
0
votes
1answer
10 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
26
votes
7answers
15k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
16
votes
7answers
4k views

Is polling the only way for updating app's data from a database?

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
0
votes
1answer
16 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
0
votes
1answer
14 views

Combine addresses missing Unit/Suite #

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it? Address 1 Burton Hills Blvd, Suite 250 1 Burton Hills ...
0
votes
0answers
848 views

Converting char to varchar columns increases database size

I have a database (SQL Server 2005) with thousands of char columns. I want to convert these columns to varchar, then I follow this steps: Shrink Database Check the current stats, general and ...
0
votes
2answers
30 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT ...
0
votes
1answer
21 views

not a single-group group function when trying to add two sum() function [closed]

I have two tables: create table a ( index_ numeric(1), value_ numeric(2) ); create table b ( index_ numeric(1), value_ numeric(2) ); Now I want to get the number of these two tables sum,I ...
15
votes
4answers
11k views

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for ...
10
votes
5answers
196 views

Convert a date range to an interval description

A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, ...
3
votes
2answers
108 views

Unable to create stored procedure to access excel files in SQL Server?

I need to create a stored procedure to access Excel files in SQL Server. I have created the below stored procedure. Since I am a sysadmin, the stored procedure is working fine for me. But for the ...
0
votes
0answers
6 views

Datastax solr query

I have 1 dse search node in my little cluster. I can look for data using solr quey only from this node. Is it possible to execute solr query from not dse search node (with cassandra only) too? Thanks
0
votes
1answer
30 views

Writing a query to COUNT all the services involving a particular

Learning PHP and SQL on an online MOOC, this is a homework that I've got to do. I need to write a query in SQL to count all the services which involve a particular person in my database. The ...
4
votes
2answers
206 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
5
votes
2answers
26k views

Granting Execute Permissions on Stored Procedures, Functions and Views

I am using a user which has read permission only. It basically has permission to Connect Execute However, it does not have execute permissions on Functions and Stored Procedures. In part of my ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
0
votes
0answers
29 views

Error while accessing TempDB Properties from SSMS

While trying to access TempDB's properties from SSMS (2014 SP1 SSMS) the following error comes up. Any ideas how to fix this? TempDB is owned by 'sa' and I am logged in with the 'sa' account. ...
4
votes
2answers
57 views

Get SSRS Datasources from ReportServer

Housekeeping on an old SSRS, this particular server is 2008R2. I have the query below which gets me the name, location, creation and modification overview for all the reports. I would also like to ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
2
votes
1answer
183 views

OFFSET … FETCH overlapping results on second page

When using OFFSET ... FETCH, I'm having I'm getting...interesting results. Here are two sqlfiddles to illustrate my problem. http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8 ...
0
votes
1answer
44 views

Multiple databases in production

We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables). In our current DB architecture, we have one single database & all level 1 ...
8
votes
4answers
217 views

Separate letters from digits in alphanumeric string

I have an alphanumeric string as input and I want to get two results out of it: a string where all the numerals are removed and an integer that is the sum of all the digits in the input string. ...
0
votes
1answer
232 views

SSRS Domain Migration

My company is currently going through a data center migration. We are now down to the SQL Servers and this includes SSRS. The bad thing that is going to hit us is the current SSRS server is being ...
0
votes
1answer
291 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
14
votes
4answers
2k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
0
votes
1answer
39 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
0
votes
0answers
23 views

Calculate total number of faults that occurred today?

I have created a database application in Access that can be used to view fault reports that have been logged against a computing department's inventory. So far I have created queries which enabled the ...
-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 ...
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 ...
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) , ...
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
22 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
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 ...
2
votes
1answer
373 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
1
vote
4answers
243 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
1
vote
1answer
41 views

Display last date grouped by warehouse and product

I have a table called daily_stock with the following fields: +-----------+----------+------------+----------+ | warehouse | product | date | stock | ...
6
votes
2answers
38k views

Primary filegroup is full SQL Server 2008

I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error: Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' ...
1
vote
1answer
588 views

SSIS Package Execution From Visual Studio Unit Test

Hello I am attempting to run a test which contains the following code: string pkgLocation; Package pkg; Application app; DTSExecResult pkgResults; ...
5
votes
4answers
220 views

Total duration “server” was in “In” state per day

I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started ...
3
votes
2answers
699 views

SQL Server 2008 R2 Mirroring issues

I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 ...
0
votes
0answers
23 views

Specify what columns to display in a SHOW COLUMNS statement

Does MySQL offer any way to specify which columns to display in a SHOW COLUMNS FROM my_table statement? From this : Field Type Null Key Default Extra ------ ...
1
vote
1answer
30 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
1
vote
1answer
27 views

Show Dollar Amount By Name On A Month To Month Basis

I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount. Below is what I get from my query (subset of data) Name Value ...
3
votes
1answer
195 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
0
votes
1answer
43 views

How to express functional dependency in relational calculus and SQL?

I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus. Vote: ...
4
votes
3answers
23k views

SQL Server 2008 Database Restore Error - Cannot open backup device 15105

I am trying to restore from a backup and getting the following error Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to ...
0
votes
2answers
26 views

Group By Month With Overlapping years

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output: 01/13/2015 01/01/2016 02/07/2015 02/01/2016 This is sample DDL - what ...
1
vote
1answer
51 views

Slot Time Challenge - Doctor Appointment database schema

I am trying to make a doctor appointment system where patient online will have two options: First time visit (this visit should be 20 mins) Follow up visit (this visit should be 10 mins) ...
0
votes
2answers
50 views

Check if the trigger was fired by INSERT or DELETE

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet. How to check if the trigger was fired by an INSERT or DELETE? Example CREATE ...