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

learn more… | top users | synonyms (2)

0
votes
0answers
35 views

Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?

I have a table with lots of default values, and want to store very common values as NULL to save space, without forcing my SQL statements or application logic to become overburdened with conditionals. ...
1
vote
1answer
1k views

HTML entity decoding with oracle DBMS_XMLGEN.convert

Here, I am trying to convert the value, where I don't understand I am going wrong. Your help is highly appreciated! Here I am#44; THis is me there#39;s nowhere else on earth I rather be Here I am#44; ...
1
vote
0answers
24 views

Simplifying UNION query that looks for duplicate names

create table sections( id serial, section_name char(255) not null, version_id int not null ); create table section_versions( id int primary key not null, version_name char(255) not null ); ...
0
votes
1answer
28 views

wild card for entry id mysql [closed]

So I have a blog and some of my entries are set to "members", but I want to set them to "everyone". I originally thought it was only a few entries and so I thought to change them manually using ...
1
vote
2answers
273 views

SQL Server - Slow Database for no apparent reason [closed]

I have a DB Server running SQL Server 2008 R2 SP2 on 24 physical Cores and 32GB of RAM. The database files are on a SSD Raid1 and the TempDB on a SAS 15k (150GB DB) I assume that the server has more ...
0
votes
0answers
150 views

MySQL Dynamic columns duplicate value workaround

As the title suggests i have the following code which transposes certain row values into field names, however the problem im having is that the values "closed" & "reopened" may appear in several ...
0
votes
1answer
107 views

Exported CSV from MySQL doesn't appear in specified directory

I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying: SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv' ...
0
votes
1answer
160 views

NOT IN statement or what to use in this case?

I need to delete each row in fabricante_producto_solicitud (fps from now on) table if fps.id has not relation (NOT IN) fabricante_modelo_marca_producto (fmmp from now on). Since my input will be an ...
0
votes
0answers
151 views

Database design for varying prices from multiple vendors

I am designing a database where the products are sold in bulk with multiple pricing options, from 4 different vendors. What is the best table configuration to store this information? Id like to be ...
4
votes
2answers
1k views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
2
votes
2answers
1k views

OLAP or OLATP - How to determine

I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP. There is no analysis server installed, the DB is ...
1
vote
2answers
420 views

Efficient query to get last row group by multiple columns

I have a table like the following: spread_id serial NOT NULL, game_id integer NOT NULL, sportsbook_id integer NOT NULL, spread_type integer NOT NULL, spread_duration integer NOT NULL, home_line ...
1
vote
2answers
153 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
2
votes
1answer
4k views

How to update a table with a stored procedure with XML parameter in SQL 2008?

I have the following Stored Procedure query. This query is incomplete. I need help completing this query. Problem: In this SP, I am receiving a well-formed XML string into the SP. My goal is to ...
1
vote
0answers
40 views

Error when creating Trigger (PostgreSQL 9.4)

I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only ...
0
votes
0answers
71 views

Auditing SQL Server 2012 [closed]

I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the ...
5
votes
1answer
384 views

Which parameters was a stored procedure or command executed with?

We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a ...
1
vote
1answer
51 views

How to shard a database like that of SO?

I know a bit about sharding but I cannot wrap my head around that fact that say eventually SO cannot scale vertically then how would they shard their SQL DB? Even if we assume SO will not reach that ...
17
votes
4answers
2k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
1
vote
2answers
3k views

SQL ssis delete imported rows from source table

I have setup a simple SSIS package that copies data from a source table to a destination table which works. I would like to get the package to also delete the data in the source table that has been ...
5
votes
1answer
1k views

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ...
0
votes
1answer
30 views

how to query multiple counts on related tables

For each Forum, there are many Posts, and for each Post, there are many Comments I'm trying to query the total number of Posts and Comments for each Forum So I'm looking to have an output as such: ...
0
votes
1answer
70 views

how to get child categorys based on categoryid

this is my table structure i need to find child categoryids. If CategoryID 1 then 1,2,3,4,10,12,13,14,15,16 when CategoryID 2 then 2,10,12,13,14,15,16
1
vote
1answer
77 views

Join conditions and Where conditions in Left Join

STUDENT table contains a list of students in my school Database LIBRARY_BOOK table contains the information of each Book that is with currently with any student. each student is allowed only 1 book ...
1
vote
1answer
217 views

Monitoring the rows in a table

I'm trying to achieve the following tasks in SQL Server. I have a table that I need to monitor its growth. The table shouldn't be containing more than X amount of rows, if there are that many amount ...
2
votes
1answer
148 views

Retrieving latest record using DISTINCT ON is slow [duplicate]

Using Postgres 9.4. I have a table cartests with 5.5M rows. Each row is a car test: \d log.cartests; Table "log.cartests" Column | Type | Modifiers ------------...
10
votes
2answers
284 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
0
votes
0answers
435 views

Error in creating trigger in phpMyAdmin

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) notification: sender_id ...
7
votes
1answer
178 views

Looking for a set-based solution for a rolling 30-day period

I have a stored procedure (MS SQL Server 2008 R2) that needs to see if 10 points/sales fall within a 30-day rolling window. So far, I have a solution that works, but it's not a "proper" solution as ...
0
votes
0answers
79 views

Rebuilding of Index in LogShipping DB in SQL Server 2008 R2 Standard version

Recently I joined the new organization and I am new to Log Shipping. After joining new company I found the fragmentation size of clustered indexes approx 256 indexes more than a 80% and I got the task ...
0
votes
0answers
263 views

Export a varbinary column to file system using existing authentication

I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use ...
3
votes
2answers
279 views

Finding violations of the symmetry constraint

Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
-1
votes
1answer
684 views

delete record if table exists sql

I want to delete record from a table if that table exists Below is the delete statement DELETE FROM UM_CLAIM_BEHAVIOR WHERE UM_TENANT_ID = ? Can I extend this query to check the existence of the ...
0
votes
1answer
83 views

What does normalization say about storing data from objects that use a decorator pattern?

I am just curious as to what the best practices are for storing the data from objects built using decorator design. I have a base class and can decorate it with A, B, and/or C, with A B and C having ...
1
vote
1answer
168 views

Will SQL charset wildcard query work on Oracle database? [closed]

I am trying to run the following query SELECT * FROM GEEKYBRAINS.admin_emp where ename like '[sm]%'; It results in an empty set. The admin_emp table has the following data though. I am running ...
-1
votes
1answer
182 views

SQL Server CE tools to modify database in network share

I am using SQL Server CE 4.0 SP1. I am using Web Developer 2010 Express to connect to database and perform operations like creating table, modifying column etc. But we cannot connect a database in a ...
0
votes
2answers
85 views

joining same table without a common field

Oracle 11g/SQLPLUS Given single table TABLE with three fields ID, PARM, VALUE, I want to select and group by based on different values of VALUE E.g. ID PARM VALUE id1 accnt France-1 id2 ...
1
vote
1answer
85 views

Initialize replication from multiple split backup files

We are creating a new Node within our existing P2P replication setup. To be able to create the backup and copy the files I am Backing up to 9 files - each will be about 72Gb: e.g. BACKUP DATABASE [...
3
votes
1answer
979 views

How do you drop an unnamed check constraint in a PostgresQL table?

I created a PostgresQL table but I added an unnamed check constraint on one of the columns: CREATE TABLE FOO ( id serial primary key, price_range smallint CHECK (price_range > 0), url varchar(255) ...
1
vote
0answers
279 views

Backup and FTP SQL Server databases

We plan to backup our SQL Server 2012 databases every night, FTP the backup files to our test servers and restore them. Then deploy the latest schemas to those databases. The reason for this is so we ...
3
votes
1answer
63 views

Wich is the most efficient/easy way for end-user to get data from Analysis database?

I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can ...
1
vote
4answers
93 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the table)...
2
votes
1answer
70 views

Auditing/Logging Help

I'm working on a request to start logging some additional data for PCI compliance. I need to log who, when, and what changed on a set of tables. I set up SQL auditing which would work fine except ...
0
votes
1answer
69 views

same table on different commands (inner join)

Testing some commands of MySQL, I've come across these two comands resulting on the same table. Command 1: select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where ...
2
votes
1answer
135 views

I can't create relationship in my database sql server

I have designed a database for my website. It has 3 tables: user, topic and comment. A user has many topics (or articles) A topic (or article) has many comments A user has many comments I ...
2
votes
1answer
2k views

Alter table Statement Failed -Msg 207, Level 16, State 1

I wrote a script to add a column in a table and on successful execution update its values. I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between ...
1
vote
1answer
932 views

Why is Excel File Opened Exclusively By Another?

I'm creating an SSIS package to loop through a series of Excel files (.xls). I create my while loop container and then create my data flow, which includes an Excel source. When I edit my Excel ...
1
vote
3answers
162 views

Remove duplicates from different columns

I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table: ColA, ColB, ColC, ColD ---------- 1, 1, 'ABC', 'DEF' ---------- 1, 1, 'DEF', ...
1
vote
0answers
87 views

Does partitioning help if you don't query on the partition value?

Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created). The table has over 10 million rows and the partition is quarterly (...
2
votes
2answers
199 views

Should I set document version field to be part of a primary key

Should I set document version as a primary key I am working on a document management system, and I have these two tables representing part of the whole tables. On the DocumentVersion table I have ...