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

learn more… | top users | synonyms (2)

1
vote
1answer
50 views

Displaying Distinct Max Value autonumber rows from tables in MySql [duplicate]

i am trying to execute the distinct records from mysql table having max value of it sno column. I have tried the following query but having no luck. SELECT DISTINCT p.sno AS sno, ...
1
vote
5answers
145 views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
0
votes
0answers
26 views

Sqlite memory management [migrated]

I am using sqlite browser to run some queries. In case of queries which return huge data, sqlite consumes large amount of RAM and does not free it until the browser is closed / killed. Is there a way ...
0
votes
0answers
10 views

ERD question, problem with connecting

So I have a question involving an assignment i got. The question is I can see out of a query the total amount of days a room was occupied. However I think my Reservations and Rooms should be many to ...
0
votes
2answers
66 views

Removing last two digits

If I have 12345678, 1234567, 123456 in a table as a column and using sql I want to create a new column that will have new numbers that removed the last two digits of each number. The new column will ...
-5
votes
2answers
108 views

Query that finds the names of the directors that played in movies that they directed

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names of the directors that played in movies that they directed The tables: ...
-2
votes
0answers
38 views

Query that finds the names of the directors that played in movies that they directed [duplicate]

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds the names of the directors that played in movies that they directed My attempt: ...
-2
votes
0answers
40 views

Query that finds most common genre [duplicate]

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query that finds most common genre (with the biggest number of movies) The tables: My attempt:: ...
5
votes
2answers
342 views

CREATE as SELECT with CONSTRAINT?

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null. I cannot find valid SQL for this ...
1
vote
1answer
58 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
-2
votes
1answer
49 views

How to solve the below problem [closed]

SELECT TOP 1 @MUTUAL_FUND_FAMILY_MASTER_ID = T.MUTUAL_FUND_FAMILY_MASTER_ID FROM (SELECT DISTINCT DENSE_RANK() OVER (ORDER BY MFFM.MUTUAL_FUND_FAMILY_MASTER_ID) AS RN1, ...
1
vote
0answers
58 views

Steps or suggestion to break up a 2,000 line sql query into many smaller pieces [closed]

Query was managed by the business team, had been running and appending to it for at least 5 years, nobody quite know exactly what it does, and it takes 15 seconds to finish. Working on project that ...
1
vote
1answer
432 views

Is introducing a where condition into the join clause bad practice

In the query below, the condition and r.master_seq=206 is used on the on clause as a join condition. In the programme code, the value for r.master_seq is set by a parameter. My concern is ...
0
votes
2answers
92 views

Trigger to compare inserted value with value from another table

I have two tables: Stockitem (serialno, qty) serialno is the primary key and Order(serialno,orderno,qty) orderno and serialno are the primary keys. I want to create a trigger in order to ensure ...
-3
votes
3answers
129 views

Finding the most common genre for “eMovies”

Given database of the website "eMovies" that manage data about cinema movies. Write in SQL query the most common genre (that have the biggest number of movies) The tables: Actors ActorID ...
0
votes
2answers
49 views

should I denormalize for user profiles?

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing ...
1
vote
2answers
62 views

Query on bridge table

I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the ...
0
votes
1answer
36 views

Return the most recent event in a series of same title events [closed]

I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance: Each time a product is moved from position A to Position B, it is ...
2
votes
2answers
57 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
2answers
56 views

Update multiple rows with different columns

I am trying to perform a bulk MySQL update where I know only certain columns need to be updated for each row, e.g. row A only "name" changed to "Sue", row B "name" and "address" changed to "Joe" and ...
2
votes
1answer
48 views

Selecting only columns where sum(column) > 0

I have a table like this: Date Bob Joe Will 1/1/15 1 0 1 1/2/15 0 0 1 1/3/15 0 0 0 1/4/15 1 1 1 Preferred solution With ...
1
vote
1answer
36 views

'Logical file name “MyDB” is already in use' Even though the database doesn't exist [closed]

I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the ...
0
votes
1answer
66 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
0
votes
0answers
19 views

How to calculate percentage of multiple values of columns in PostgreSQL?

I've the table named reviews which shows the hotels' services ratings given by guests who stayed at respective hotel as shown below dtime prop clean staff location amenety travel_type ...
0
votes
2answers
25 views

MySQL Workbench - store a query to apply to various schemas

We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query. My ...
0
votes
0answers
24 views

How can i make month columns in MySQL? [duplicate]

I want to return table like that(column for each month) : Jan-Feb -Mar -Apr-May-Jun-Jul-Aug-Sep-Oct-Nov-Dec My query : SELECT CASE WHEN month(date) = 1 then 'January' WHEN ...
4
votes
3answers
193 views

SQL OrderBy When using an In() query

Example query SELECT * FROM Table WHERE ID in (5,3,4) How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third). Edit - To ...
0
votes
2answers
46 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
1
vote
1answer
70 views

Error at line 1, column 25: ORA-00923: FROM keyword not found where expected

I have some code that works in Access but now I want to forward the code to our IT department to make a view in our SQL database. Could anyone please help me convert it so it is correct for normal ...
1
vote
1answer
37 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...
-6
votes
1answer
37 views

Select Max in stored procedure [closed]

WHY does the following stored procedure not execute correctly? declare @shID smallint, @osID smallint; set @shID = 15; set @osID = 4; IF (SELECT Max(oofficeid) AS Expr1 FROM ooffice ...
3
votes
2answers
43 views

Enabling SQL Instant File Initialization when Restoring to a Network Path

We have a large Read Only SQL DB (SQL 2012) that we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging. Part of my guidance along the ...
1
vote
1answer
38 views

If I use varaibles in the order by clause, is there any guarantee the result will be sorted?

I have a table like this: // Table1 +----+--------------------+------+------------------+---------+-----------+ | Id | QuestionOrAnswer | Type | AcceptedAnswerId | related | timestamp | ...
0
votes
0answers
17 views

Get records with specific group name from table A and see if any exist in table B, if it does then add matching records in Table B to group

Let me start off by saying I am not a DB admin or a DB pro, I work with SQL as a php developer but that's about it. So I hope I can explain this properly and I hope one of you DB guys/gals can help. ...
4
votes
3answers
146 views

Full database backup

I'm using third party tool for making schedule database backups. I need to make one additional full backup. Should it be affected on the scheduled backups? If yes, is there any solution?
0
votes
0answers
51 views

How to optimize this query on oracle?

I have built this query in oracle to get some data i need from the two tables below. At SELECT REGEXP_REPLACE i get the IN_01 as input parameters from the user and it can be much bigger list than ...
0
votes
3answers
59 views

How to copy data from one database to another database efficiently?

I have a conceptual question. I have two databases which theoretically have the same structure but one of them is a SQLBase database and the other one is a SQL Server database. One database (the ...
1
vote
4answers
87 views

Find the winner of each stage

I am learning SQL, and I am trying to get a job. I have the following table in MS SQL Server 2014: The table is called Game with the following fields: Name, Stage #, Score. My goal is to write the ...
4
votes
1answer
49 views

Need to list SQL Windows auth users on each SQL server and rename the domain name

We are doing an AD Domain NetBIOS rename. We have about 20 SQL servers, and on each we have a series of logins like OLD.DOM\sqluser1 etc. I want to do two things, and a third would be a bonus. I'm not ...
3
votes
1answer
50 views

Creating a query to pull very specific information using four tables

I have four tables, with a structure like this: testData This table holds all test data for every user. Each row associates one response for a question, divided up by test. A user can have many ...
2
votes
1answer
33 views

How to optimize this query on Postgres

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It sometimes takes up to ...
1
vote
1answer
51 views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (http://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
0
votes
0answers
17 views

How to get row position in sqlite after sorting?

I have table, UUID | INT | LONG AAA | 1 | 123 BBB | 3 | 241 CCC | 2 | 145 And i sort it by INT value. SELECT * FROM table ORDER BY int_val DESC then i want to get a row from table and ...
1
vote
0answers
29 views

How do I disable constraint checking during schema migration?

How do I disable FOREIGN KEY checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL: SET CONSTRAINTS ALL DEFERRED; MSSQL: EXEC ...
0
votes
0answers
17 views

List Network Servers on SSMS Browse Servers

My question is how will I show available network server on Browse for Server > Network Servers of Sql Server Management Studio? FYI, I am able to connect to existing and known servers even if it is ...
11
votes
2answers
80 views

PostgreSQL Recursive Descendant Depth

I need to calculate the depth of a descendant from it's ancestor. When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH ...
2
votes
1answer
26 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) ...
1
vote
2answers
108 views

Sql getting debit, credit and balance issue

I am using the query below to get Debit/Credit Balance from table statement SELECT t.[InvoiceID], t.S_Type as Type, t.Date, t.Debit, t.Credit, b.Balance FROM Statement as t CROSS apply ...
0
votes
1answer
52 views

Why do string functions on SQL based database servers start at position 1 instead of 0? [closed]

This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the ...
1
vote
0answers
41 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...