Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (1)

0
votes
2answers
45 views

What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity?

I am new to Database. I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile. What is the most efficient way of doing so? This is ...
1
vote
1answer
59 views

How do I index two table having a one-to-many relation but only the latter is being frequently updated?

Take the following two tables: table A has ~100k rows and 50 columns, and it is being updated only twice per month, how long this update takes is not an issue (as long as it doesn't take more than ...
0
votes
2answers
39 views

IF condition in connect command in SQL*Plus

I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script. if "connect ...
1
vote
0answers
89 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
2answers
51 views

SQL Data warehouse and normalization forms

I don't know if it's a simple question but does a data warehouse have different rules when it comes to normalization forms? At the moment I am creating some tables in a data warehouse, and they ...
0
votes
1answer
23 views

Composite foreign key not verify all columns

Table1 (Num [PK NOT NULL], Dat [PK NOT NULL], ...) Table2 (ID [PK], Num, Dat, ...) I added this FK: ALTER TABLE [dbo].[Table2] WITH CHECK ADD FOREIGN KEY([Num], [Dat]) REFERENCES [dbo].[Table1] ...
4
votes
1answer
81 views

Order result by count of common array elements

Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection. Something like: ...
0
votes
2answers
73 views

SQL Server Estimated I/O Cost in real units

As I understand from other StackExchange posts, SQL Servers estimates are unitless meaning an Estimated I/O cost of 2.82387 doesn't refer to the time taken or disk I/O's, but a relative measure of how ...
3
votes
3answers
375 views

How to purge an already dropped table?

I dropped a table but didn't purge it. How can I purge it, so I can release the tablespace that the table uses?
0
votes
0answers
13 views

SQL Query to concatenate values - incremental values based on the first column

need help in getting a sql query which will concatenate values incrementally. Here is the example. I have rows in the table as mytest23093787@PMCPERF.com#Test_Org19f7e530-d10c-4943-aabf-bc73c0537bfa ...
0
votes
1answer
40 views

ora-04091 table is mutating trigger/function may not see it

I have two tables say tab1, tab2 here tab1 is the parent table of tab2, Both have triggers enabled say tab1_trg, tab2_trg. Also in tab2_trg there is a SELECT on tab1. Now I try to delete some records ...
1
vote
2answers
54 views

Why not have Persons for every person in schema?

I noticed that a lot of database schemas often show like, "students" or "teachers" or "employees" or "passengers" or something to that affect. Why not have a schema where every individual is a ...
0
votes
0answers
12 views

MySQL Trigger at Insertion with nested CAST

So I'm using MySQL 5.5 and trying to insert a VARCHAR as ID (since my teacher requested so), I am trying that each time there is an insertion of a new USER, a trigger automatically assigns an ID to ...
0
votes
0answers
16 views

Mysql Procedure insert one to many with cursor

I would like to take a course, or just show me the way. There is a table called Questionario from it I get the records in a procedure and organize the data into it. Here is my code:      CREATE ...
4
votes
2answers
132 views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | ...
0
votes
2answers
36 views

Combining two similar queries into one (PostgreSQL)

I have 2 queries like this: SELECT c.id FROM component c, base_vee bv WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) AND c.id = ...
0
votes
1answer
43 views

How can I optimize my SQL Query (Multiple Rounds)

I'm try to round the sum of every month for the current und and last year. But with thousands of entries this query is slow. How can I optimize it? I want to use the results in a C# Application. The ...
1
vote
0answers
41 views

OLTP In-memory (Hekaton) in SQL Server Standard edition

When Microsoft says SQL Server Express and Standard edtion have "Basic OLTP" support, what do they mean? What is included and what isn't available?
0
votes
2answers
33 views

how to select rows which are mapped to a single record in a one to many relationship?

I have two tables, consumer and user. Consumer table has a foreign key for user table,say user_id mapped to user id in user table. There are some rows in consumer table which have one to one mapping ...
2
votes
2answers
48 views

Index-refresh in SQL Server

Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity? In SQL Server 2008R2 or in advanced versions.
0
votes
0answers
34 views

When create trigger then after error shown in inserting the values

When I create trigger then after error shown in inserting values in phpmyadmin. Code for create trigger as shown below- CREATE DEFINER=`root`@`localhost` TRIGGER `mobile_no` BEFORE INSERT ON ...
0
votes
1answer
27 views

select all with other parameters in db2

What is the equivalent query for SELECT *,SUM(total_request_count) AS total_calls FROM API_REQUEST_SUMMARY in DB2. It says * is invalid in db2 data explorer. After running this I got below error ...
0
votes
0answers
28 views

Composite Partition for Oracle SQL Table

This is my first time that I'm working with composite partitions. I'm trying to arrange by list and range. My code is as follows: CREATE TABLE CHANGE_LOG_DRIVER_COMP ( LICENSENUMBER VARCHAR2(100 ...
3
votes
2answers
86 views

Data extraction with multiple delimiters?

I have a legacy data source column that is delimitted by semicolons and commas. The first semicolon indicates the last name, the second indicates the first and middle name (or initials), and the last ...
1
vote
1answer
30 views

how to do a count on a grouped by value in Postgres 9.4

I have a list of orders and would like to output a list by day of when they were created at of (1) total orders and (2)orders that have a fulfillment_status of 'paid' id | fulfillment_status | ...
13
votes
1answer
548 views

SQL Server UniqueIdentifier / GUID internal representation

A colleague of mine sent me an interesting question, that I can't entirely explain. He ran some code (included below) and got somewhat unexpected results from it. Essentially, when converting a ...
2
votes
4answers
149 views

Effective dates, complex requirements

I have a table with, say, preferred colors. For every person, we store the favorite color and the effective date. If the person changes preference, we store the new one with a newer effective date. ...
1
vote
0answers
39 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for ...
0
votes
0answers
14 views

Updating column as the value selected by user in multivalued parameter

I am creating a report in which I have data from 11 different databases... I have created multi-valued parameter... user selects the state... I want to add a column to my result which should display ...
0
votes
4answers
69 views

Expected Performance of MSSQL [closed]

We have a client who has been having performance issues. One of the developers is saying that a select * from a table is taking ~50 seconds to perform the query (no joins just straight select with no ...
0
votes
0answers
30 views

How to Find Consecutive count of a column based on a their status

I have a table containing various sites and billing information of those sites. Ex: site_key | billing_date | Reason_type | rank ----------+---------------------+-------------+------ ...
1
vote
1answer
38 views

Understanding rows count computation

PostgreSQL 9.4 I have a table called customers which has a column income integer. After runnig ANALYZE against it I got the following statistic for the column: most_common_vals ...
2
votes
1answer
58 views

Is this table in my ERD redundant? (Image)

I am developing a research article database for a project. My professor approved this design but I feel like the Author's_University table is redundant and that University_ID could just be added to ...
0
votes
1answer
23 views

On checking the validity of a large set of identifiers

In the research group where I work, we must solve following problem hundreds, maybe thousands of times every day: given a set of putative gene names (typically a few hundred of them), flag those that ...
3
votes
1answer
76 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items ...
1
vote
1answer
62 views

MySQL on insert in table update field in another table?

I'm creating one PHP script and I need to update table Topics when someone insert something in table TopicAnswers. Is there any SQL query to do something like this, for example something as FOREIGN ...
0
votes
1answer
28 views

Set Default Maximum Rows for Export

I am more of a Vertica database user than admin, so I'm not even sure if what I'm asking is absurd, but here goes nothing... Could I, as an admin, put default restrictions on the number of rows that ...
0
votes
3answers
74 views

Select data from single table using Self join

I have requirement to select a data from single table. The requirement is as follows Select data from table which has one value but not another. for e.g I want to select distinct ID which has value 1 ...
1
vote
1answer
46 views

Join external data in SELECT query

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do ...
0
votes
1answer
36 views

Query to combine each duplicate found entry

Hey all I am wanting to create a query so that I can combine each of the found duplicates into one entry. An example of this is: Name | ID | Tag | Address |carNum ...
0
votes
1answer
72 views

CALCULATE TOTAL NUMBER OF ESTIMATED HOURS [closed]

I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a ...
0
votes
0answers
11 views

How do I maintain a separate table for precomputed results?

Suppose that I have the following (fictitious) schema: CREATE TABLE projects (..., client TEXT NOT NULL, ...); CREATE TABLE client_names (name TEXT NOT NULL PRIMARY KEY, soundex TEXT NOT NULL); ...
1
vote
1answer
30 views

Efficiently perform multiple similar updates

I'm trying to extend some existing SQL (SQL Server) that builds a table of all items to act on using a number of queries. Some of these are very similar and costly, and I was hoping to get some advice ...
3
votes
1answer
51 views

MySQL: Is there a better way to do this query?

I'm not sure if this is the most appropriate place for this question, but I thought I'd give it a shot. The scenario is wanting to retrieve records from a table where a column is null, but if an ...
0
votes
5answers
102 views

Select rows based of a value that only have null for all associated [closed]

The best way for me to explain this is by an example: Input: Col1 | Col2 | COL3 -------------------------------------- a | null | 1 a | null | 2 b ...
0
votes
2answers
69 views

SELECT fields from table having some conditions

I have two tables 1. Named as t1 2. Named as t2 example of the tables are : t1 : id name 1 one 2 two 3 three t2 : id name 2 five I need to select all the data ...
2
votes
1answer
202 views

SELECT values with a condition and JOIN

I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title I have a table (table-a) with a list of items, I Want to ...
0
votes
0answers
11 views

PostgreSQL dump import only imprts tables and not data

I'm trying to import a postgreSQL dump into my DB. The dump is saved in a .sql file. I use the following command : psql -U postgres -d dhris < dbDump.sql and the table was created before. The ...
0
votes
0answers
54 views

Hard dba case [schema to store delivery prices for shops]

I want to make a database scheme which would allow me to fetch the delivery price for the different Shops basing on the delivery destination (Address/Region) and order value (price). Here is the ...
0
votes
1answer
25 views

How to create a Table in Oracle which has a variable of type number?

Suppose I want to store the social security numbers with a prefix letter 'S' (eg. S123456789) or a driving license number D (eg. D12345). Is there any function or method to prefix it? Or patters? I ...