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)

0
votes
2answers
294 views

Idea for manking tables for a pharmacy

OK I'm not into Database management, but I got this problem, also not sure if I'm posting it at the right place or not. So, my Dad works in a pharmacy and all the work there is on paper and is done ...
0
votes
1answer
221 views

How to check mysql password change and other events logs

Recently someone change my phpmyadmin password and drop database I think it was sql injecting. Now I want to find information about this like when, how and who change my password Please tell me ...
0
votes
0answers
10 views

Form builder schema for SQL database?

There are many ways one can envision the solution, from the top of my head: Two table approach: Field table with (name, field_name, type, description, meta) attributes Form table with (name, ...
0
votes
1answer
145 views

Display a one-to-many relationship in one row

I have a "one to many" relationship in two tables: Table "products" contains: product_id, product_descr Table "packing" contains: product_id, package_id, package_desc One product has often more ...
3
votes
0answers
41 views

why “select” work in mariadb but doesn't work in mysql

set @row_number = 0; SELECT * FROM (SELECT (@row_number:=@row_number + 1) AS num, id, tbl_user_id, title, description, length lengths, ...
2
votes
1answer
6k views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
1
vote
0answers
11 views

sql server on big machine falls over inexplicably

We've been doing some throughput testing with our web application. The production application resides on a couple of machines in the amazon cloud, consisting of a web server and a database server. ...
1
vote
0answers
74 views

How to connect to a |Database from SQL Script, in SQL developer

Hi i am trying to connect to a DB using a connection string mentioned in a Script file.. in SQL developer. I am using the below format Connect USERNAME/PASSWORD@HOSTNAME:PORT/Service_name.. But its ...
0
votes
1answer
213 views

Connect by prior - multiple fields and query efficiency

I have 2 tables one of which defines the hierarchy for the other equipment id desc 1 item1 2 item2 3 item3 4 item4 5 item5 6 item6 7 item7 8 item8 ...
4
votes
1answer
408 views

System.Data.Entity.Infrastructure.CommitFailedException: C# Multithreading & SQL Server 2012

We have a C# multi threaded (100 threads) program which reads the records from the DB and each thread picks up one record (one Entity Framework connection per thread) and update the a single DB table. ...
2
votes
2answers
44 views

Query to know who paid more than 10k

I have a table with payment informations, like this: ID COSTUMER_ID DATEPAID AMOUNT 1 1 2012-01-01 50 2 1 2012-02-01 100 3 1 2012-03-01 70 4 2 ...
9
votes
4answers
251 views

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My ...
0
votes
0answers
19 views

Different ways to log the DML operations being performed, along with the hostname (or some type of unique ID) performing the said operations?

What are the different ways to log the different DML operations that are being performed, along with the host performing them. I do not wish to use triggers as they are costly. I considered SQL Audit ...
1
vote
2answers
46 views

Copy values to another column - ORA-01481: invalid number format model

I want to copy values of col1 NUMBER(13,4) column to another newly added col1_temp VARCHAR2(50). To perform this action I build the following update query: update table1 set col1_temp = to_char(col1, ...
-1
votes
0answers
13 views
-2
votes
1answer
20 views

Selecting different variables depending on another variable [on hold]

I must make a new table with filling up fields depending on a variable. If type = holder, then I should take the enterprise field, if type = requester, I should take the name. How do i write this in ...
0
votes
1answer
153 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
0
votes
0answers
14 views

SQL Express SA user has disabled [duplicate]

Hi we have an issue where our SQL Express has randomly disabled the SA account can I am able to connect to SQL as the local admin but it does not have sufficient access to re enable the SA account. ...
4
votes
1answer
101 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 ...
4
votes
2answers
49 views

Charindex, Getting specific data from a string

Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error. I searched for similar problems on stackexchange as well as a ...
3
votes
2answers
2k views

SQLite writing a query where you select only rows nearest to the hour

I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the ...
0
votes
0answers
25 views

would you help me understand errors and bad exercise containing in the query?

A customer called technical support for a DBMS, saying that the DBMS had a severe bug and was crashing the operating system. On investigation, it was found that the ‘crash’ always happened when a ...
3
votes
2answers
57 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
0
votes
1answer
214 views

SQL Query - Possibly the most poorly written query in the history of mankind

Having a major headache trying to tune this query and looking for help, a programmer who had no idea what they were doing messed with this query and it is currently being used in production and it ...
1
vote
1answer
34 views

How to use an alias name that has RANK() OVER in a WHERE clause

I have a query that has a RANK() OVER function inside it but I would like to use the results displayed on this column in the WHERE clause that follows. How do I write that as all the other questions I ...
1
vote
1answer
19 views

Recording history of ranks

Once per day, this is being executed to rank everyone in the database based on points. SET @r=0; UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC; The field ...
6
votes
3answers
187 views

Find data size of table for several identical databases on a instance

I have several identical databases with the same schema/table structure within a instance. I need to find the size of one table that they all have. For example, I have 30 databases on a instance and ...
2
votes
2answers
174 views

Build Restore History table

We restore a database daily that we download from an FTP site. I would like to build a history table that tracks when the database is restored on our SQL Server. This is easy. My issue is sometime ...
-3
votes
1answer
49 views

Retrieve result for all the users

I have one query which returns particular user total hours for a month. I want to return for all the users in an org. UserRegistration table contains UserID. My code for particular user is, SELECT ...
0
votes
2answers
28 views

select where column with a %

How can I select all records with a % in? Goes code below works? SELECT * FROM table WHERE column LIKE '%%%' first and last % for zero, one or more chars second % for the percent skinn If not ...
2
votes
1answer
3k 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
42 views

How come MySQL LIKE is faster then FULLTEXT?

So, I was trying to optimize my MySQL queries, but found out that i'm actually doing this wrong. I've changed my query from using SELECT * FROM test WHERE tst_title LIKE '1%' To: SELECT * FROM ...
1
vote
1answer
102 views

MySQL: Rank by Department in an employees table

I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.
0
votes
1answer
66 views

Is there a way to run this query in all databases ? ( SQL Server 2008 R2 )

How can I use this query for each database? I can easily use sp_msforeachdb but it breaks the results for every database. So I tried to insert the results in a temp table, but it's giving me syntax ...
0
votes
1answer
29 views

How to prevent insert row duplicate value in a column and equal the any value

I have table TABLE1( id number (primary key), IDAccount number, status char(10)) Table can have 2 row duplicate (IDAccount, status) with status=disable as well as id IDAccount status 1 ...
1
vote
1answer
145 views

SELECT COUNT(*) to html table

This may be a very simple but its still not clear to me. I'm writing a SQL Agent job to generate output in html format. I need to get results of a query SELECT COUNT(*) FROM TABLE_NAME. I'm able to ...
-6
votes
0answers
29 views

i want to join the sum but different LIKE CONDITION [on hold]

Below is my table this is fill on the training_transaction table
0
votes
1answer
160 views

How to merge two databases, both with primary keys

I have two databases, both with the same tables, but the data is out of sync. I need to add data from database A to database B, but not overwrite any data in B that exists in A, instead add it with a ...
0
votes
2answers
13k views

Inserting data into a table with one value as clob and blob

My table has one clob column and one blob column. I need to insert data into it through insert statements. The length of data to be inserted is more than 4000 chars. When I do export of insert ...
1
vote
1answer
32 views

PostgreSQL, how to extract all the possible couples of a primary key in a table?

I would like to find out a PostgreSQL command that reads the content of a table, and returns all the possible couplings of the primary key of the table. Something like this: input_table (ID is the ...
0
votes
1answer
43 views

Update if exist in other table

I want to update column in table A if the value of column A1 exist in one column of B table for example B2 update A set A1 = 'F' WHERE A1 in (SELECT B2 FROM B ); I tried somethig like this but no ...
0
votes
0answers
23 views

SQL TDE Key Format

I'm trying to find the Key Format that SQL TDE uses. Is it PKCS? If so, what #? I haven't been able to identify this based on research done on MS website. And, there is no mention of key format during ...
3
votes
1answer
47 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
0
votes
2answers
3k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
1
vote
0answers
14 views

Can i use PGP for securing My database

I have considered TDE and cell level encryption mechanism for securing my database. but these two cannot fully satisfies my requirements.through surfing i found that PGP may help me, but it is mainly ...
1
vote
1answer
340 views

Can we use TDE as an anti-injection technique

TDE prevent unauthorized access to the data by restoring the files to another server, because it requires the original encryption certificate and master key. If i applied a table level encryption ...
7
votes
3answers
83 views

Totals(sums/counts) across multiple child tables query optimization

We have 12 types of expenses in our database, some have fairly differing data minus the Amount fields. We have multiple places in the application and reports that require single and multiple expense ...
-1
votes
2answers
53 views

How can we create a table from stored procedure?

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_tb_one`(In tableName varchar(255)) BEGIN CREATE table tableName( ID INT NOT NULL, SNAME VARCHAR(100) NOT NULL, ...
3
votes
1answer
96 views

Select where bit is not 0

I have made the following query to select a record that isn't 0. SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL; Why is the check Bit != 1 not enough for a nullable BIT?
0
votes
0answers
12 views

how to find and replace text in phpmyadmin

We are migrating from one domain to another domain. Usually we will follow the procedure of open the database file in notepad++ software and find and replace old-domain-name to new-domain-name. But as ...