I work within the SQL database, but am self taught, so there are a few holes in my knowledge. I have this query that although I would think is simple, I just cant get it right. I have two columns, one with a ‘Name’, and the other ‘Privacy’. I am trying to to get a result where if the P…
Create a new record for every user in a database based on a column
I have a MySQL database with a user table, after a new requirement I had to create a new table called social_media and from now on every new user will be created with a social_media_id column that holds a reference to their social media. I want to update my database so that every user that didn’t had a …
Filtering/ORDER BY – Practice
I ran into a small issue with a order I am trying to do and not sure if there is a solution to what I am trying to accomplish. I have the following table created… CustomerID is PK and I can’t change the numbers around. The goal is to get the table to look like this: The first part of
i got error ORA-00979: not a GROUP BY expression
I got Error ORA-00979 with the following query: SELECT d.department_name “department name”, e.job_id “job title”, SUM(e.salary) “monthly cost”, …
Count Distinct Window Function with Groupby
I have a table that contains a user’s name, market, and purchase_id. I’m trying to use a window function in SnowSql without a subquery to count the distinct number of purchases a user bought as well as the total number of unique purchases for the market. Initial Table User Market Purchase_ID John …
How can I reference a column alias in another place in the same select statement?
I want to do this (hypothetical example): But apparently I cannot refer to the name temp_f in the temp_description expression. I know I can do something like this: But that’s much clunkier than I want, especially in the context of my real-life use case that, for permissions reasons, prevents me from usi…
Creating and using an SQL variable after some set text
I’m needing to set some text into a field within a row, and use a SQL variable as part of that text. I’m then using this sql statement in a bash script. I have I believe I created the variable correctly I then need to use this variable as follows However its not working and is giving me errors abo…
MySQL procedure not working with both select and delete
I created a procedure to get a vehicle id from the vehicle table using vehicle number input, then delete the booking record for that vehicle id, and at last to delete the entry in the vehicle table… But when creating the procedure I get an error at line 7 saying Error Code: 1064. You have an error in yo…
Null values affecting case logic
I am having a problem in the following query. In the AR column there are some null values due to the left join of the wbr_global.raw_station_extended_attribute. To fix this I added a case to default to 0 if it is null. The issue this is causing is in my next case statement for assinging a station size. All th…
Is there a way to include a DELETE FROM statement in a nested WITH statement?
I am trying to delete duplicates by creating another table but I am unable to include the delete from statement. I have the code here in SQL Server but I am trying to convert it to Oracle. I tried replacing the select with the delete from but I’m getting the error: missing SELECT keyword. Here is the co…