DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

3
votes
1answer
171 views

PostgreSQL seems to ignore RAISE EXCEPTION in a CTE

Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows: CREATE FUNCTION public.assert ( in_assertion boolean, ...
0
votes
0answers
20 views

Installing database

I have created database in my system using mysql.I tried to use in one of my friend's system.How can install the database in other system?
1
vote
2answers
36 views

SQL view, how to append results from tableB with the results from tableA

I am using SQL server 2012. I would like to generate a sql view where I got all records from tableA. And below these results I would like to have the results from tableB. There are no relations ...
0
votes
0answers
1k views

eCommerce product table design - MySQL

I am designing database for an web application, I have designed the product and specification details database as follows, create table pr_size ( size_id int not null auto_increment, ...
0
votes
1answer
130 views

SQL Server 2012 Express SP1 install still not 11.1

I run the SQL Server 2012 Express SP1 install and it succeeds. After I have rebooted my computer, I do a select @@version But still I only have version 11.0.21600.64. I have tried this on two ...
0
votes
1answer
10 views

Get devices with not driver assigned

I have this related tables: And I need to get those devices with no driver assigned, in other words the device returned on this query shouldn't be on driver_has_device table since it'll not have ...
0
votes
4answers
353 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
0
votes
1answer
39 views

Can PostgreSQL results include the query?

I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment. psql -U <username> -h <servername> -d <databasename> -f ...
11
votes
1answer
3k views

Postgres Update, limit 1

I have a postgres database which contains details on clusters of servers, such as server status (active, standby, etc). Active servers at any time may need to fail over to a standby, and I don't care ...
1
vote
4answers
2k views

Is it ok to change from full recovery to simple recovery in Sql Server

I have an old database - a users membership/role that was setup automatically by an ASP.Net 2 application years ago: The Sql Server version currently running is: Sql Server 10.5.1617 The users ...
0
votes
1answer
35 views

Finding Item's distribution over customer routes

Please help me to find a solution. I have two tables [CustomerMaster and SalesData] as shown below. CustomerMaster | CODE | NAME | ROUTE | DIVISION | |------|------------|-------|----------| ...
-1
votes
2answers
266 views

Retrieve recently inserted 5 rows [closed]

I want to fetch recently inserted 5 rows from my table. I want to know the sql query for the same. My table is: table xyz ( id integer generated always as identity (start with 1 increment by 1), ...
0
votes
1answer
78 views

Join reorders results [duplicate]

Is there any particular reason why adding an additional join to an ordered table would reorder the result set? Using a left join on the existing result set table-valued function is similar to below ...
0
votes
2answers
57 views

Oracle conditional statements

I am making a report, and I am trying to do following: If the value of the field is NULL, output empty string. Another words, I don't need the word "null" to be output to the user, just empty string. ...
1
vote
2answers
206 views

Finding the highest quarter of the year

I'm sitting here and do some practise but I just can't find the Solution. Maybe someone could give me some help? I have a table and should find the highest sale amount of a quarter per year. Data ...
2
votes
2answers
3k views

Checking if an Username is already in use

I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if ...
0
votes
1answer
212 views

Best relationship database design to manage types for different entities

Suppose we have to manage phone types for: Clients, Employees and Suppliers. Each of these can have multiple phone numbers, so for better management I would like to add phone type. Each entity can ...
0
votes
1answer
32 views

What is the “other miscellaneous information” in the Shared Global Area (oracle database)?

I'm studying Oracle database ,and it says here that the SGA contains these data structures : Database buffer cache Redo log buffer Shared pool Java pool Large pool (optional) Data dictionary cache ...
0
votes
1answer
72 views

How do I count and order by unique entries?

I have this: SELECT tblClient.[Printed Name], tblClient.[Address 1], tblClient.[Address 2], tblClient.City, tblClient.State, tblClient.Zip, tblClient.Phone, tblClient.[Email Address] FROM tblClient ...
2
votes
3answers
363 views

Listener pointing to incorrect AlwaysOn Availability Group

All, Thanks in advance I have two instances of SQL Server 2012 SP1 on a three node WSFC with a HAdr group on each instance with a corresponding listener. Instance 1's listener points to Instance 1, ...
1
vote
1answer
39 views

SQL Server: affect other transactions?

I'd be surprised if this existed, but will ask just in case. Is there some mechanism which would allow one transaction to affect another (outside) transaction? For example: BEGIN TRY BEGIN TRAN ...
0
votes
1answer
1k views

How to delete from two tables same field name in single query?

Help me, I want to delete same filed name from two tables in single query, I Attached query but it's not working... DELETE PersonalTrainerMasterData ,PersonalTrainerCheckboxData FROM ...
13
votes
1answer
1k views

“Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

I am trying to alter a column in a table. The existing table is like this: CREATE TABLE [dbo].[table]( [id1] [int] NOT NULL, [id2] [int] NOT NULL, [id3] [int] NOT NULL, [name] ...
0
votes
1answer
1k views

The best way to query a partitioned table in Oracle

I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better: making ...
0
votes
0answers
34 views

Avoid using SOQL/DML query statements in trigger

SELECT field1 FROM sObject WHERE field = 'Myconst' LIMIT 50000; I need to replace this field = 'const' by storing this const in a variable which suppose is: String constVar = 'Myconst'; SELECT ...
2
votes
1answer
3k views

Using scope_identity() to get ID “during” insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so: alter table dbo.Employee add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for ...
3
votes
2answers
2k views

PostgreSQL using count() to determine percentages (cast issues)

I'm trying to run the following query to provide the % of records in my patients table that have a value entered in the refinst field. I keep getting a result of 0. select (count (refinst) / (select ...
1
vote
1answer
794 views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
5
votes
3answers
883 views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
6
votes
5answers
951 views

Performance tuning a Great Circle distance calculation

I have a query that I am using to find locations that are within 1km of a known point. To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes. Currently, the ...
0
votes
1answer
140 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
0
votes
1answer
70 views

RAID drives or replicate data?

I have an old Microsoft SQL server that, for financial reasons, I cannot replace until later in the year. The OS, DB and temp files are on one 10k RAID 5 array (I didn't design it) The server is ...
0
votes
0answers
77 views

msdb 'backupset' log history

One of our production server‘s backup job is getting failed for one DB because of Low disk space when I looked into ‘physical_device_name’ column in ‘msdb.dbo.backupset’ to get the path of backups I ...
1
vote
1answer
126 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
1
vote
3answers
601 views

Block user access to certain tables

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and ...
2
votes
0answers
131 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
-2
votes
1answer
50 views

how to select column3 from rec1 value which is matched to column1 val in rec2

i am having following scenario : col1 col2 col3 10 2 13 13 4 14 14 5 15 15 6 16 form the above given table col3 value(13) in row1 is seen in col1 value(13) in ...
0
votes
2answers
62 views

How to print ORDER wise using LEFT JOIN in SQL?

Below SQL query is not displaying ORDER wise. How can I fix it? SELECT TabTitle,A.ButtonName,A.CheckBoxName,B.SubCheckboxName FROM (Select * from PersonalTrainerCheckboxData where ...
1
vote
1answer
728 views

Changing Databases Dynamically using Variables [closed]

I want change into a database programatically using in code: DECLARE @SQL nvarchar(2000) DECLARE @DBName sysname SET @DBName= 'foo' SET @SQL = 'USE [' + @DBName + ']' select @SQL execute @SQL ...
0
votes
1answer
510 views

ORA-00907: missing right parenthesis on query with subqueries

I am getting ORA-00907: missing right parenthesis for the following query: SELECT PEOPLE.NAME FROM CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L WHERE NOT EXISTS ( (SELECT DISTINCT ARTIST.ID_U ...
0
votes
0answers
40 views

How to aggregate similar columns inside a very long table? [duplicate]

I have quite a difficult problem that I don't have any idea how to approach it. I'll be glad to receive some advice. I have a very long table which contains details about a specific house residents. ...
0
votes
2answers
94 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
0
votes
2answers
662 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
0answers
14 views

Specific id to appear first in result of query [duplicate]

I have a sample query where: Select gID, entID, cID, gString From tblGF Where ((entID=$entID And cID=0) Or (entID=$entID And cID=$cID)) And (gFP='BB' Or gFP='CP' Or gFP='PS' Or gID=$gID) Order ...
1
vote
2answers
67 views

Time elapsed Calculation

I need to calculated time elapsed for each user to perform his part in a transaction. The sequence is: 0 -1 - B2 or 0-B3 The table looks like the following: ...
0
votes
0answers
75 views

Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

In this question I have asked about modulus calculation in sharded tables, this design is used: CREATE TABLE Orders (order_id INT PRIMARY KEY, ...); CREATE TABLE LineItems (order_id INT, lineitem_id ...
1
vote
0answers
54 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
0
votes
0answers
27 views

Indexed column INSERT vs. UPDATE

I have an indexed column. I could give it a value on INSERT. But it would be easier if I would add this value via UPDATE later (I have to update this row anyway). But I was thinking if it would be ...
0
votes
1answer
259 views

COUNT and GROUP BY performance

I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country. I use this query: SELECT country, COUNT(*) as hits FROM hits_table ...
0
votes
1answer
451 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...