An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data ...

learn more… | top users | synonyms (1)

1
vote
1answer
3k views

Clarify - Need to add Multiple foreign key constraints

I am not sure if I am thinking about this process the wrong way. Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a ...
3
votes
3answers
1k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
9
votes
0answers
3k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
6
votes
4answers
5k views

Select statement takes long remotely due to long “client processing time”, but short locally

While connected to our production server (SQL2008. very powerful machine), this select statement takes 2 seconds, spitting back all fields (4 MB of data in total). select top 30000 * from person ...
2
votes
1answer
97 views

Improving Oracle SQL Query

I'm trying to improve the below code. The unique identifier in this code is result_label_seq, but I still need to make sure that it's uniquely identified with facility_id and service_code. The ...
5
votes
2answers
14k views

Returning a result set with multiple rows based on max date

I have a child table that is something like this: [Cust Date Table] | Customer ID | Some Date | Balance | +-------------+------------+---------+ | 1 | 2012-04-30 | 20.00 | | 1 ...
2
votes
1answer
516 views

Alias Names and Two Table Query Problems

I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this. I'm working on trying to use alias names to ...
2
votes
1answer
537 views

Performing aggregations and calculations: SQL (CTE) vs MDX

I’ve hit a wall and would like some expert advice. I’m building a data warehouse and don’t quite understand whether the calculations (also time aggregations) should be done in SQL (T-SQL and CTE) or ...
0
votes
2answers
4k views

Diff in Transactional and Non-transactional tables

What is the difference between Transactional and Non-transactional tables? And How will the sql query go if I want to create them? Also when to use transactional or non-transactional tables?.
1
vote
1answer
199 views

MySql - How can I improve this query?

I have the following table: http://pastebin.com/XH6FvhZ8 and the following query: SELECT * FROM (SELECT * FROM `job_logs` ORDER BY id DESC) AS job_logs_tmp WHERE (job_logs_tmp.user_id = ...
3
votes
1answer
415 views

Changing Query Results?

I have a query that I am running and I just noticed that every time I run it, it is returning a different amount of records (usually less by 1 or 2 rows). My question is how can this be possible? I am ...
0
votes
1answer
148 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
5
votes
2answers
17k 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 ...
4
votes
1answer
245 views

MySQL: A better way to write this query?

We are using the following query in our system: SELECT f.type AS type, COUNT(i.id) AS submissions, SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail) WHEN 0 THEN 0 WHEN ...
4
votes
2answers
830 views

Add column taking too long

I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website: alter table MyTable add MyNewColumn varchar(max) not ...
2
votes
1answer
2k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
6
votes
2answers
317 views

Help with this query

I have a person table. I also have a car table and books table. The car and book table both have a foreign key back person. I am trying to count the number of books and cars each person has: I try: ...
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
1
vote
1answer
1k views

Simple Car Rental database

I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has ...
7
votes
2answers
2k views

A good SQL sniffer

Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time? If it's free, that would be great.
1
vote
1answer
80 views

Rename cell based on duplicate compound key in MS Access

I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns. Here's an example: Col_1 Col_2 Col_3 Note A1 B1 C1 A1 ...
2
votes
2answers
302 views

pull records that are inactive

I need help with this query. I have records in customer table Main table - M +---------+---------+----------+ | company | Account | Active | +---------+---------+----------+ | A | A123 | ...
1
vote
2answers
314 views

MySQL Cluster Node Connections

I am a brand new user to the MySQL cluster world (literally hours). I was following a tutorial just to get a simple cluster set up (one management node on one machine, a second machine with a SQL node ...
3
votes
2answers
565 views

SQL Server consuming all my CPU

I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost ...
1
vote
2answers
139 views

Get Total in a single line in SQL Server

I have a very simple SQL Query which is getting results in the following way COLUMN A COL-B WINDCHILL 99.998889 WINDCHILL 96.000000 Now how i can modify my query so that I get the ...
3
votes
3answers
481 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like ...
2
votes
3answers
510 views

Identifying rows which don't match a master row

I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by locationID, have the non-matching rows because they might ...
1
vote
1answer
3k views

TSQL Get minute part from time data type

With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
1
vote
2answers
2k views

LEFT JOIN conversion to INNER JOIN, can't change FROM clause

I have 2 tables: BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation. These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date. ...
2
votes
2answers
74 views

Data Backup and Restoring the same if requried in SQL SERVER 2008

In Multi-tenancy , Shared Database Shared Schema. How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction. Thanks!!! NB: ...
4
votes
2answers
1k views

Help with this SELECT in the same table

I have this table structure: id1 id2 1 2 1 3 1 4 2 1 2 5 I need to build a query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4. I tried ...
1
vote
2answers
362 views

Managing features for a real estate agency [duplicate]

I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table. The data ...
1
vote
1answer
361 views

Migrating data in SQL Server from source to target with schema change at target

We have a strange scenario. We have a source database running on SQL Server 2005 where we push data to a target database running on SQL Server 2008 R2. The source database table has 5 columns. The ...
3
votes
2answers
705 views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
6
votes
1answer
239 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
1
vote
0answers
206 views

Cannot Check if the Access Database Engine is Installed and Configured

All, I want to check if the Access Database Engine is installed and linked to SQL Server. On my development machine (x64-bit) the following queries both show the Access Engine to be avalible as a ...
0
votes
1answer
3k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
1
vote
1answer
137 views

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which ...
8
votes
2answers
4k views

Subqueries' aliases same as main queries' aliases

I have an SQL query whose aliases are the same as some of its subquery's aliases. For example: select * from ROOM r where ... ( select * from ROAD r ...
2
votes
1answer
777 views

Oracle SQL Loader - load file where record is composed of “n” delimiters

How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas? I have read the documentation, however I cannot find how to handle this scenario, ...
5
votes
1answer
205 views

Oracle: DB *partial* constraint?

I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation. I currently have a table at hand that records ...
2
votes
3answers
175 views

Setting up database structure for Students, Managers, and Administrators

I'm trying to figure out how to set up the structure for users. Users can have multiple Managers and Managers can manage multiple Business Units. Administrators have full control of the system. I'm ...
2
votes
1answer
1k views

Is there any way to get the position of a value in a table returned by a SELECT statement?

I have the following statement: SELECT id FROM myTable WHERE aCondition == 1 ORDER BY id which returns a table like this one for example: id 1 3 4 5 But what I need is to know the position of a ...
1
vote
2answers
246 views

Join on clustered tables shows greater cost than non-clustered tables?

I have created a cluster and have two tables emp and sep on the cluster. When I do the simple query... explain plan select * from emp_cluster join dep_cluster using (dno) the cost is 26. I ...
3
votes
1answer
154 views

SQL Calculation based on 'similar' rows within table

I'm using SQL Server 2008 R2, building a fact table for SSAS. I can therefore accept solutions to this via either SQL query to SSIS task. Let's say I have a fact table that looks a little like this, ...
4
votes
3answers
2k views

PostgreSQL - Help Optimizing SQL performance - full text search

I have a big table ~15M records called entities. I want to find top 5 entities matching "hockey" in their name. I have a Full text index built for that, which is used : ...
4
votes
3answers
4k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
2
votes
0answers
243 views

Oracle faster overlap check [closed]

We've a table with two columns beginrange and endrange, which contain hex values. To this table no overlapping ranges should be inputted. We had implemented the check by having a composite functional ...
3
votes
2answers
116 views

SQL Statement Help… Ignore already existing rows

I have a table with a foreign key constraint and the command below gives me an error because it's trying to set a value that already in the provider table. How do I update this command to ignore ...
204
votes
4answers
50k views

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?