An acronym for "Structured Query Language", 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 access privileges.
0
votes
0answers
1 views
Zero conversion in sql
Number convertion in Oracle have a pattern: to_number(number,'pattern') and provide option "s" to take sign to result, for ex: select to_number(10,'S999') from dual; will return +10, but when i check ...
0
votes
2answers
17 views
One table used in two databases
I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different ...
-2
votes
0answers
20 views
SUM 1 column with same ID and SELECT multiple columns from multiple tables [on hold]
I would like to SUM the values of the column TimeTook when the MPRO column is the same. I also require to grab data from multiple tables and select multiple columns. I have 2 queries with the ...
1
vote
4answers
56 views
Update column value in entire database
In my database, I have around 30 tables, each with an emp_number (INT) column.
How can I update the value of emp_number in all 30 tables?
0
votes
0answers
9 views
Filtering results by date [migrated]
I have the following query:
SELECT STOCK.ITEM_DATE, STOCK.AMOUNT, STOCK.OPERATIONTYPE
,ITEM.ITEM_NAME
FROM ITEM INNER JOIN STOCK ON STOCK.ITEM_ID = ITEM.ITEM_ID
WHERE STOCK.ITEM_DATE ...
0
votes
1answer
17 views
How do I create a trigger that cascades new rows into another table?
I am having a table name link with two columns link1 and link2 and another table name hide with four columns hide1, link1,link2 and hide2. If i insert data into link table the data should insert into ...
0
votes
0answers
21 views
Extract MySQL information from a compressed tar.gz file
I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it.
I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
0
votes
0answers
26 views
Creating view recursive query in Oracle DB results in ORA-00600 (ORA-600)
Hello Ladies and Gents,
I've been trying to deploy a recursive query as a view in Oracle XE and Standard Edition, neither to much success.
The query is located in this question here: ...
0
votes
1answer
23 views
Linking third table with other two table to have a dynamic populated data inside using postgresql [on hold]
I am in a phase where I need to have a data populated dynamically in a table which is associated with two other tables using foreign key constraint. Below is the schema/table that I have created in ...
2
votes
2answers
68 views
Changing ORDER BY direction causes performance issue
I use MySQL 5.6, and I have two tables each with 16M rows:
CREATE TABLE IF NOT EXISTS `newsstudios` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
...
0
votes
1answer
51 views
Does this error message indicate a SQL injection vulnerability?
I just took over management for a webservice and today I got a user report about this error. Now I'm no sql injection expert but does this error make this attack possible?
Microsoft OLE DB Provider ...
1
vote
2answers
65 views
Is there any way to tell the number of matches found in a database value matched with LIKE?
Is there any way I can query a SQL-Server-CE database that will return the number of matches found in a cell value using LIKE (or MATCH AGAINST or any other method I'm not aware of, really)?
EXAMPLE ...
4
votes
2answers
84 views
Multiple JOIN of 6 tables via 5 database instances
I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
7
votes
2answers
159 views
How to find parent rows that have indentical sets of child rows?
Suppose I have structure like this:
Recipes Table:
RecipeID
Name
Description
RecipeIngredients Table:
RecipeID
IngredientID
Quantity
UOM
What are some good ways for ...
1
vote
1answer
39 views
MySQL Insert into table, where name = id, if not exist, then insert name and use that ID
I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ...
-5
votes
1answer
48 views
Updating a record based on parent id [on hold]
I have a table like this:
intProductID vchProductName intParentCategory intCategoryId
1 Post Cards NULL 3
2 Packaging Boxe NULL ...
6
votes
1answer
97 views
Convert units of measurement
Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.
Unit Conversion Table
The unit ...
0
votes
1answer
27 views
Wrong return results
I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
-1
votes
0answers
53 views
Query returning correct data, and additional data [on hold]
Thank you all in advance for any responses.
I am querying various Snort tables in order to produce a report. When I run my current query, I receive the expected results (as verified by our IDS ...
0
votes
2answers
33 views
Is it possible to build an UNDO framework for postgres?
I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the ...
-4
votes
1answer
58 views
What will the sql statement “DELETE FROM sales” do?
DELETE FROM sales;
And if there are no other uncommitted transactions on the sales table. Which statement is true about the DELETE statement?
It removes all the rows in the table and deleted rows ...
0
votes
0answers
14 views
Installing QMYSQL driver [migrated]
Basically when I try to connect to the mysql database i get this error:
QSqlDatabase: QMYSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QODBC QODBC3
I tried to install the drivers, ...
-1
votes
1answer
100 views
No predicate warning [on hold]
I am getting a No predicate warning on this query. Can someone help?
SELECT DISTINCT TOP 100 T0.nsid,
T0.id,
T0.version,
...
0
votes
1answer
58 views
How to profile a particularly slow or inefficient query
I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries.
...
0
votes
0answers
22 views
Any recommendation on tuning following query
My query has lot of select statements and unions. I think it is querying database multiple times and combining data. Is there a way we can eliminate multiple passes to the database?
I would like to ...
-1
votes
2answers
150 views
Query to search for a substring in xml
I use below query to search for a substring in whole xml(including node name and node value)
SELECT *
FROM tablename
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 )
I want an ...
1
vote
1answer
64 views
Relative cost of two similar queries involving XML columns
I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.
I checked the execution ...
0
votes
1answer
56 views
xml queries with exist and contains giving unexpected results
I have a query as shown below.
DECLARE @tbl TABLE
(
id INT,
col XML
)
INSERT INTO @tbl
VALUES
(1,'<Root>
<Row>
<User>xyz</User>
...
-1
votes
0answers
37 views
How to calculate sigma > 1 in MySQL [on hold]
I have a big set of data and calculating AVG and STD already. But as STD is only 1 sigma and 31% of all data points are outside of avg +/- std, I want to know, if there is any good way to STD with ...
0
votes
1answer
22 views
Chaining System Query in MySQL
I have a table which maintains a user chain
Here is the table structure
+------+--------+
| id | user |
+------+--------+
| 2 | 4 |
| 10 | 4 |
| 11 | 4 |
| 1 | 2 |
...
1
vote
2answers
32 views
Dynamic Oracle Pivot_In_Clause
I'm kinda stuck. I want to do a user-role-relationship pivot table and my query so far looks like this:
WITH PIVOT_DATA AS (
SELECT *
FROM
(
SELECT USERNAME, GRANTED_ROLE
...
0
votes
0answers
33 views
Search for string in xml column
I am using sql server 2008 r2 express. I have a table with column name 'xmlmycolumn' which is of datatype xml. I want to search for string in that whole xml. So to do this i am casting column as ...
1
vote
2answers
26 views
Query for master in Postgres replication
I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication.
From a slave, I can:
SELECT pg_is_in_recovery()
And ...
0
votes
0answers
10 views
What is a solution in keyword trend analysis with a FreeText field in SQL Server 2008R2?
I am currently using a SQL Server 2008 db, where in a table, I have a freetext field defined.
I would like to perform some trend analysis on one field, where I would provide the base for the trend, ...
3
votes
4answers
49 views
Error 3154 while restoring a backup using WITH REPLACE
I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak.
I have a database with the name test2 which is the same database, but the data changed.
I want to restore ...
0
votes
3answers
78 views
Space used by databases file through linked server
I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers).
I created view on client size, for all databases:
...
0
votes
2answers
34 views
SQL Server Maintenance Plan TSQL query to log the backup databases
Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up?
Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3.
...
1
vote
1answer
38 views
Count number of common column names among all tables in a database
Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over ...
4
votes
1answer
42 views
ORDER BY optimization for a large table
I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest.
Consider a table posts
posts
{
id ...
2
votes
1answer
22 views
Optimizing mysql queries running on 50 million rows
Is there any technique/advice in order to optimize the performance of the queries below?
According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, ...
2
votes
1answer
50 views
Is it ok to let users add new columns and tables to your database?
I'm basically making a database of databases. An administrative User can go in and hit "Create Database" (which is basically a table) and set up the column types and how many columns. Other users can ...
0
votes
0answers
24 views
Calculating the row size of a table [duplicate]
Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
-1
votes
0answers
35 views
How much time is considered 'Ideal' for a SELECT query? [closed]
This is my first experience in developing for the masses, and I wanna know how much time is considered ideal for running a select query and returning the data to the client?
I've seen simple queries ...
-1
votes
2answers
33 views
How to use GROUP BY after the Having clause Mysql
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
All the accounts which is not ...
-2
votes
3answers
75 views
How to write a query to find all tables in a db that have a specific column name
I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like:
select ...
1
vote
3answers
61 views
Nonclustered index is faster than clustered index?
Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created
clustered index on persontb(BusinessEntityID)
and
...
0
votes
1answer
38 views
Hazards of Upgrading SQL Server Express 2008 to R2
A client of mine reached the 4GB database limit on a SQL Server 2008 Express.
As a test I installed the R2 version on another machine and restored a backup of the database.
I'd like to upgrade the ...
0
votes
1answer
60 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
0
votes
2answers
43 views
how important are mysql's innodb logs?
I'm basically concerned about the following two settings, these are my default settings :
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
I haven't changed them for 2 reasons : one, ...
5
votes
2answers
80 views
Given two known statements, how can I replicate deadlock?
I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
