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 ...
0
votes
2answers
44 views
Finding duplicates
The scenario is as follows, a while ago a colleague accidentally ran a query similar to
DELETE FROM app_i18n WHERE disabled = 0 and translation is null or translation = '';
This is the table ...
0
votes
1answer
16 views
MySQL 5.6.14 will not use proper key/index in query
OS: ubuntu 12.04 amd64
DB: MySQL 5.6.14-community amd64
I cannot figure out why this query is using the wrong index and doing a full table scan on the ar_batch (ab) table. Perhaps someone can see ...
2
votes
2answers
29 views
How to define alias in an ARRAY_AGG expression?
I'm trying to return pure JSON from a Postgres 9.2 table.
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
album,
max(release_year) AS release_year,
...
0
votes
1answer
13 views
MySQL: Error while reverse engineering database from a sql file
I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error:
ERROR: Line 1: syntax ...
0
votes
2answers
16 views
Databases: are there universal datatypes?
While designing database tables (using some tool like Enterprise Architect), how do I use only 'universal' datatypes that will work with all databases when I need to switch to a different database in ...
0
votes
1answer
1k views
SQL Server Management Studio: CREATE DATABASE permission denied in database 'master'
I am trying to add the AdventureWorks database in MS SQL Server Management Studio (SSMS) 2008 R2. As a new person to this area, or noob, this seemingly simple task has caused me much hardship and ...
0
votes
3answers
148 views
Oracle query is slower the second time
I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
1
vote
3answers
46 views
Best practice to query administrative views for instance level elements
I want to query a monitor element at instance level (for example the memory used for the instance) via a query like this:
SELECT MEMBER, MAX_MEMBER_MEM, CURRENT_MEMBER_MEM, PEAK_MEMBER_MEM
FROM ...
-1
votes
1answer
22 views
how to update multiple records for single user in database? [on hold]
I want to update multiple roles for user when admin update user roles
I have table as follow
user_roles
username || role_name
user1 | role1
user2 | role2
users
username || ...
0
votes
0answers
26 views
How is pl/sql different from sql queries [on hold]
What is the need of pl/sql besides the procedural paradigm it offers? What is it pl/sql that cannot be solved using sql query?
Some example will be very much appreciated. Thanks in advance
I wanted ...
8
votes
4answers
24k views
How to select the first row of each group?
I have a table like this:
ID | Val | Kind
----------------------
1 | 1337 | 2
2 | 1337 | 1
3 | 3 | 4
4 | 3 | 4
I want to make a SELECT that will return just the ...
1
vote
1answer
58 views
Oracle upgrade, v$version what is it actually doing?
There are 42 threads tagged as oracle8i, none of them answer my question...
This is Oracle 8i (8.1.5.0.0)
SQL> select * from v$version;
BANNER
...
3
votes
3answers
19k views
SELECT DISTINCT on one column, while returning the other columns?
I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.
My SQL ...
0
votes
0answers
8 views
Access: search for more than one parameter?
I need to perform several queries, looking for more than one parameter.
for example: in my field "lenght" I want to extract values matching both 4.6 and 4.7.
then, next time I need 5.8 and 5.9, and ...
0
votes
5answers
118 views
How to store schema-less data efficiently in a relational database?
I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.
Option 1:
Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.
...
0
votes
0answers
17 views
Website API problem on casting the XML output [migrated]
Hi guys as the title says, I'm using an API to get the weather information by country and city. Everything works great, the output is being returned. But when I try to cast the varchar to xml i get ...
1
vote
1answer
170 views
Delete word, its meanings, its meaning's example sentences from DB
I have three tables as below (simplified for demonstration):
words
=====
integer id
text word
meanings
========
integer id
integer word_id
text meaning
examples
========
integer id
integer ...
0
votes
1answer
118 views
How can I optimize this query and support multiple SKUs?
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
-1
votes
0answers
22 views
SQLite - SELECT with declarator and related group
I'm having a problem that I'm struggling with even defining, I'm almost convinced there is no way of doing that but I thought it won't hurt to ask.
In my case, I have two tables; I'll refer to them ...
9
votes
4answers
203 views
Efficiently Filter Large Set With Disjunctions
Let's say I have a single table
CREATE TABLE Ticket (
TicketId int NOT NULL,
InsertDateTime datetime NOT NULL,
SiteId int NOT NULL,
StatusId tinyint NOT NULL,
AssignedId int NULL,
...
-1
votes
1answer
22 views
How to deleted stored procedure with a specific word? [on hold]
I want to delete a stored procedure with a specific word "like sam_ ". How do I do it with code?
0
votes
1answer
33 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
1answer
144 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
0
votes
1answer
59 views
Display row data in different columns ORACLE [on hold]
I'm new to SQL and currently this is what I'm trying to do:
Display multiple rows of data into different columns within the same row
I have a table like this:
CREATE TABLE TRIPLEG(
T# ...
2
votes
2answers
181 views
How to design a table that each rows have 5K boolean attributes?
I have about 2M rows and each row looks like the following.
244 true false ... true
-> One integer column(V) and about 5K boolean columns(B1, B2, ..., B5K) associated to the integer.
Due to the ...
0
votes
1answer
18 views
Sorting by a field in child table
There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract).
contractupdate also has DATE field startdate and TINYINT field day.
I ...
1
vote
0answers
31 views
SQL Error 3183 On SQL Server 2012
I have a backup of database version sql server 2008 R2 . while restoring it to SQL Server 2012 I am getting error
msg 3183 RESTORE detected an error on page (6100:1886858449) in database ... .
...
0
votes
1answer
39 views
SQL - Referential integrity
seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as ...
1
vote
2answers
59 views
T-SQL - How to filter data from table
I'm new in DB world, so if you can help me it would be great.
My problem:
I have filtered some data and got a table with data, but need to filter them again. Picture might help you. For me, it is ...
0
votes
2answers
100 views
Which is better? A join or a SELECT IN?
I have a set of ids that I extract using a join of 2 tables.
Now I need some information from 2 other tables. What would normally be the best approach?
So a SELECT IN using the set of ids I already ...
0
votes
0answers
22 views
How to store UserName who is Updating the value of a table in For Update Trigger?
In my web application, am creating a log process. I have created an update trigger, its working perfectly but I need to know who updated this data i.e the user who performed this action.
How to store ...
1
vote
1answer
37 views
Update old database with SQL dump
I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump.
Is ...
1
vote
1answer
88 views
Select on several {name,value} pairs
I have a this schema:
CREATE TABLE Pairs(
id VARCHAR(8) NOT NULL
,name VARCHAR(40) NOT NULL
,value VARCHAR(1000) NOT NULL
,PRIMARY KEY (id,name));
I want to write a query to ...
0
votes
1answer
36 views
unexpected null returned from calculated columns
I'm working on a program that will calculate journey times based on bluetooth MAC addresses and i'm trying to nail down the mySQL commands I need before I get cracking on the software part of it.
i ...
0
votes
1answer
74 views
Inserting dates before year 1950 in Oracle
How can I insert date which is before 1950?
update up_sif.tb_person set dateB =to_date('18.10.1949','DD.MM.YYYY') where ID = '123345';
It keeps update with date 18.19.2049.
Any ideas?
1
vote
2answers
98 views
Delete rows from a large table with joins and aggregating (resulting in 1 % less rows in the big table)
I got a bit stuck on performance, so I thought about asking a bit of help. I have a working query, but unfortunatelly it feels rather slow and I know it's not as performant as it could be made ...
1
vote
1answer
211 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 ...
-3
votes
0answers
32 views
query execution taking very long with N'sql [closed]
I have a stored proc that uses n'sql - the sp is a loop that happens again and again for each item in my loop. It takes about 2.5 minutes to run. When I take it out of the sp and just run the loop ...
1
vote
1answer
187 views
How to split/explode comma delimited string field into SQL query
I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'
and I want to use it to search IN this field:
SELECT *
FROM table1
WHERE id IN (id_list)
id is integer
id_list is varchar/text
...
0
votes
1answer
53 views
Optimize MySQL Self JOIN Query
I have a query which is running in 15+ seconds
SELECT
t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
FROM
`stocks` AS t1,
`stocks` AS t2
WHERE
...
0
votes
1answer
36 views
MySQL select query result set changes based on column order
I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results ...
1
vote
1answer
60 views
Oracle password has expired, however I have no way of changing it
When I connect to my local development database, I get an error saying the password has expired. I've been trying to change it with sqlplus to no avail:
C:\>sqlplus ...
1
vote
2answers
428 views
SQL Server 2012, restore database
I am using MS SQL Management Studio 2012 to restore the database from a .bak file. (That file is from the backup of the database on server)
In my new database, the triggers are missing. Why did I ...
0
votes
0answers
6 views
sql server 2000 linked server works locally but not from remote machine
sql server 2000 linked server works locally but not from remote machine ,i am not able to use linked server remotely (from my desktop).
i enabled TCP/IP for the sql Server and i enabled win&sql ...
1
vote
1answer
42 views
Query_cache doesn't work with join
I have a simple join query, but for some reason query_cache won't cache it!
SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
1
vote
1answer
23 views
.NET error when trying to create snapshot for transactional Replication
I encountered a bizarre prob today while creating a transactional publication on a SQL 2012 server running on Windows Server 2008R2.
I had just had some success creating and initializing some Merge ...
20
votes
2answers
9k views
Find highest level of a hierarchical field: with vs without CTEs
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.
I ...
1
vote
1answer
111 views
Help me convert SQL statement to MySQL
I have an application called Moodle version 1.9 and was migrated from Windows platform and Microsoft SQL Server to Linux and MySQL.
Now, when I click save changes the SQL query statement from Custom ...
2
votes
2answers
75 views
What can be done to further enhance performance of Multiple Join and Aggregate Queries?
I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
0
votes
0answers
33 views
Is there a syntax to access the table name in the select query?
In MYSQL. I want to select the records which has the same contents with the table name.
for example, consider the following table.
<TABLE : tbl_1>
+-------+------+
| id | no |
...
