Questions tagged [sql]

Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.

Filter by
Sorted by
Tagged with
0
votes
0answers
3 views

Dynamic Join on Columns in a Table Based on if Data is Present

I have two tables: Table 1 Year | Make | Model | Color -------+-------+----------+-- 2011 | Ford | F150 | White 2012 | Chevy| Cobalt| Grey 2012 | Chevy| Cobalt| Black 2011 | Ford | Taurus| ...
0
votes
0answers
8 views

Writing a sql query to find the doctor whose patients have the most different type of diseases

I am trying to figure out how to write this query and I am having a really hard time. Here are the schemas relevant to the question: DOCTORS (D_ID, D_NAME, D_GENDER, D_AGE, D_SPECIALIZATION, ...
0
votes
1answer
10 views

how to show all users name in sql

How to show all user name even count is zero. $users = DB::select('select count(c.status) as countEvent, sum(c.point) as totalPoint,u.name from users u left join campaign_users c on u.id=c.user_id ...
0
votes
1answer
8 views

Fraction formatting as halves or one third or quarters in PostgreSQL

I have some double-precision values in a column in PostgreSQL as following: **amount** *11.33 11.75 12.2 13.9 16.3 .....* I want to round these values to their nearest quarter value at the decimal ...
0
votes
1answer
19 views

How to get rows having sum equal to given in SQL

**Table A :** **Table B:** ITEMCODE Total QTY ITEMCODE Qty A 100 A 25 B 50 A 75 C 75 B 10 D ...
0
votes
1answer
9 views

optimizing subquery in sql

Given the following SQL query SELECT * FROM geotypes WHERE geotype_id IN (SELECT geotype_id FROM geotype_mixed WHERE test_name = $1) ORDER BY latest_date DESC LIMIT 100 Recently this query has ...
0
votes
0answers
7 views

php zend-db version: 2.10, Subselect

I used zend-db version: 2.10 in my project. now, I want to write the sql, but I don't know how to write. SELECT category_1_index.*, (SELECT real_name FROM ...
0
votes
0answers
12 views

Database to SQL Query

I wrote the SQL queries for the following database specification (note there are no weak entities) but I was told some to these were wrong and I can't figure out which ones and why. Database ...
0
votes
1answer
11 views

SQL - Adding a new column or concatenate to a table based on conditional values

I am very (very!!!) new to SQL and I am struggling with this scenario: I have a master table named tbl_fin_View (with many columns and rows) and one of the columns is named profit_center_code (there ...
0
votes
0answers
12 views

Stuck on storing individual rows from the sqlDataReader for later use

I am using visual studio, winforms and the integrated SQL server, The win form is simple it contains 1 x textbox & 3 x buttons i have two sql tables Questions & Answers i would like ...
0
votes
1answer
19 views

PostgreSQL checking for empty array changes behavior in Golang

I'm trying to implement the behavior of selecting data based on either an array of input, or get all data if array is null or empty. SELECT * FROM table_name WHERE ('{}' = $1 OR col = ...
0
votes
0answers
14 views

Fix sql injection on Rails model scope

There is a model scope I have been trying to refactor, Brakeman is complaining about it so I thought it was a good idea to fix it since we were scanned by bots who are looking for our site ...
0
votes
0answers
9 views

SuperType Table of subtype objects not showing added attributes

I am trying to make a supertype table of objects that holds subtype objects, but the bottom line insert once ran shows the first two attributes SSN and name as inserted. Is Oracle Express just broken ...
0
votes
2answers
21 views

Joining two different tables with a common third table on a common column

Here are the tables Table: Status ID, StatusDesc 1, New 2, Active 3, Cancelled 4, Complete Table: Order (foreign key relationship with Status table above) ID, OrderNumber, StatusID 1, 1001 , ...
0
votes
1answer
23 views

PL/SQL: Function returned without value

I wrote below FUNCTION to check given data is exist. CREATE OR REPLACE FUNCTION PSPIWEB_USER.AB_GetAuditTypeID (in_DeptID NUMBER) RETURN NUMBER IS intAuditTypeID NUMBER; BEGIN SELECT ...
0
votes
1answer
15 views

Copying CSV into Postgres table - invalid input syntax for type timestamp with time zone: “1”

I'm creating a new table in Postgres that I want to copy data into, from a CSV file. The CSV data is like this: nodeid,usertoken,application,starttime,endtime,count1,count2,count3,count4,count5,...
1
vote
1answer
22 views

Compare two data frames and find number of nulls

I have a problem. I heve data frame 1 named "df": enter image description here And I have the data frame 2 named "dfP1": enter image description here I want to compare the unique rows that exist ...
1
vote
1answer
17 views

Get executed sql statement in pymysql

I am executing a query that is invalid, but I'd like to be able to see exactly what the query is being executed by pymysql to debug it. Is there a way to do something like: try: self.cursor....
0
votes
2answers
29 views

String formatting — skip one format string

I have the following sql statement: SELECT_CLAUSE += ", IF(%s IS NULL, -1, %s=%s) AS %s_score" % ( field, field, '%s', field ) Basically I want to string-format everything except for the second-...
0
votes
1answer
32 views

SQL Server Designer table re-creation

Can I change the script the SSMS does when you use designer to alter your table which causes table re-creation? I know the 'Prevent saving changes that require table re-creation' option but I want ...
0
votes
0answers
13 views

How do I get SQL Server Management Studio to save CSV in the correct format?

If I don't add anything and just "Save Results As..." csv then everything is quoted and separated correctly except for the first and last column. I have a later version of SQL Server Management ...
0
votes
2answers
22 views

How do i make a sql query using more than one tables with onde condition for each?

i want to get some data from a table but separated by different conditions, for example how many lines have a column are equal to 1, how many have a column equal and the same until the number 5. i did ...
1
vote
2answers
32 views

Representing a binary tree in an SQL table using foreign keys

This is an example of a representation of a binary tree in an SQL table: A node | parent / \ A | NULL / \ ...
0
votes
1answer
34 views

SQL - find average value by using two different tables

I have to find the average ram size of a pc (from pc_table) for all those manufacturers (from product_table) that making printers. pc_table have these columns: model, speed, ram product_table have ...
0
votes
0answers
37 views

How do you convert datetime into just date using SQL?

I'm running Excel 2010, and I tried converting my code in sql SELECT Repository$`.`Date Archived` to just read the date without the time stamp, because right now it is seen as a mm/dd/yyyy/ hh:mm:...
0
votes
0answers
41 views

Regex or conversion for 'YYYY-MMM-DD'

I'm working with data in t-SQL and in order to automate my package in SSIS that will process each file with each given date, I need to figure out the regex or conversion for a file such as 'leads_2019-...
-1
votes
1answer
19 views

How to JOIN ALL different ROWs with the same ID but a different DATE and return it as a unique row with NEW COLUMNs like DATE_0, DATE_1, DATE_2, …?

Consider this table: +----+------------+ | ID | DATE | +----+------------+ | 0 | 01/12/2007 | | 0 | 15/02/2018 | | 1 | 21/05/2006 | | 1 | 03/12/2017 | | 1 | 01/03/2007 | | 1 | 23/11/2018 |...
-1
votes
1answer
21 views

How to remove duplicate value and then calculate mean of each group using SQL?

The data contains three column, name of person, group, and score. Each person at least belongs to one group, may belongs to more groups. If a pearson belongs to more than one groups, the name will ...
0
votes
1answer
25 views

How to do a trailing sum on an extremely large table

I have a table with 22 billion rows and I was asked to do a 30-day trailing sum on certain metrics, partitioned by day, then product. The answers I have found online recommend a self-join to determine ...
0
votes
2answers
35 views

Update Column values based on join with CTE

I'm currently learning how to use CTE in SQL Server 2016, as I have some large table where I need to update some columns based on a match from a helper column, and I'm not completely sure how it's ...
-3
votes
2answers
34 views

How to aggregate by certain columns and not others in SQL

|other|last|first|program| |cat|Acker|Bob A| |dog|Acker|Bob|B| |reptile|Acker|Bob|A| |cat|Birch|Patty|B| |bunny|Calhoun|Peter|C| BECOMES |other|last|first|program| |cat|Acker|Bob A| |dog|...
0
votes
1answer
14 views

Eliminating blank space when concatenating multiple columns

I want to create an address column that looks like this: 123 S Main St. I have 4 columns I am combining: STNo, PreDir, STName, Suffix Some addresses don't have a PreDir though, when that happens ...
-2
votes
0answers
15 views

implement in a script an example of how disparate data sources can be joined by collecting data from known public data providers

Me and my teammate are new student of technology in undergrad program and we came across following challenge in one of the website in which I am solving all the problems to learn implementation of ...
0
votes
2answers
30 views

How to display the related records in a single row in Oracle SQL?

I wrote a query joining two tables and I got a below resultset: SELECT emp.employee_id, dept.department_name, dept.department_id FROM employee emp, ...
0
votes
2answers
17 views

Current date and past 30 days total value

The code I currently have shows me the current value for everyday and gives the running total. select t.lgl_entity_nm, d.date_key, count(distinct s.site_key) AS Dis, SUM(Dis) over (partition by t....
-1
votes
2answers
30 views

Inserting random data into a table in MySql

Could someone please help me? I don't know what the problem here DROP PROCEDURE IF EXISTS insertRandom; CREATE PROCEDURE insertRandom() BEGIN DECLARE mytime timestamp; SET mytime := '2009-01-01 ...
-3
votes
0answers
25 views

I have a SQL database where I store an image file path as a string and pass it to a URI. How would I make the file path relative in my database?

I basically need to know how to store a relative file path in my SQL database, and give the file path to URI in my C#/wpf application. If the file path in my DB is full it works and displays the ...
-1
votes
2answers
21 views

Inserting a point into MySQL 8.0 from workbench

I've got a column called 'coordinates' of type 'point'. This query: update `my-db`.`community` set `coordinates`= POINT( 31.9931217, 35.2823115 ) where 'id' = 1; Returns the following error: ...
-3
votes
0answers
25 views

Uni assignment for databases - could you have a look? i cant run it in oracle atm, as im away from uni for my 21st

so ive uploaded all my code, could someone just double check it because i think im using oracle sql developer, which isnt the right one im assuming? its for uni but im currently 231 miles away from ...
1
vote
4answers
45 views

How does this dot notation within SQL work?

I'm working with some SQL and trying to understand what is going on. Within the select there are what seem like variables s.id s.status with last_transactions as ( select s....
0
votes
0answers
26 views

Storing logs in a PostgreSQL database

I'm forced to store my NodeJS application's logs in a PostgreSQL database. The application's quite small. Naturally, logs are less structured than my usual data, and data without rigid structure ...
-1
votes
1answer
46 views

How to calculate offset for years, months, days, etc. in SQL

We use a Retail Calendar (445) which is different from the regular one. The year begins in October and the first day of every month is not the real first day of this month. For example for this fiscal ...
1
vote
2answers
45 views

Removing leading 0's in SQL Server query

How do I trim the leading 0's from assoc_userid? SELECT assoc_name FROM table WHERE assoc_userid= #arguments.op_id# This column used to be assoc_id and would have values like 1,2,3,4,5,..,091,101 ...
-1
votes
1answer
23 views

How to get the user ID from MySQL/Maria DB?

In PostgreSQL, for instance, the query SELECT * FROM pg_user return the usesysid column in which you can see the users IDs. So I can get the oldest/newest user from this table through this ID. How ...
2
votes
2answers
27 views

Conditionally joining from multiple tables

Does SQL allow some form of conditional "table choosing" within the Join statement? ie. selecting a different table to join based on a predefined variable/condition. define var = 1 select * from ...
0
votes
0answers
36 views

The 'DateOfBirth' property on 'Patient' could not be set to a 'System.Byte[]' value. You must set this property to a non-null type 'System.DateTime

We have a AngularJS FE web app based on Entity Framework using SQL database with Always Enabled and experiencing issues two encrypted columns DateOfBirth and PatientID that are encrypted. This is the ...
0
votes
1answer
26 views

Sort High To Low SQL PHP [duplicate]

I have a big problem with the selection of the table. I want to show me from the highest to the smallest, just that it appears to me like this NAME `POINTS` John Doe 0 John ...
1
vote
1answer
16 views

DB2 SQL Having Clause with a Nested Table Expression

Return the department number and the total payroll for the department that has the highest payroll. Payroll is the sum of all salaries and commissions of the department. Use a having clause with a ...
0
votes
0answers
16 views

Pivot and generate dynamic column names

I have a many-to-many relationship between two tables and the only way I can see to do it, in my head, is to perform a pivot on the left table first to essentially "aggregate" the rows up to join-key ...
0
votes
1answer
30 views

Having two different Customer Types in a Database

My system needs two different Customer Types. I need an Individual and Corporate Customer. How would I go about creating the tables in Apex? Do I need a Customer, Individual and Corporate table or ...