w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » SQL » Page 10
Transpose vertical input data to horizontal output in Oracle
Since Oracle 11g (tab is your table name): select * from tab UNPIVOT (num for fruit in (apple as 'apple', orange as 'orange', mango as 'mango', banana as 'banana')); Oracle 10g: with col_names as ( select 'apple' fruit from dual union all select 'orange' from dual union all select 'mango' from dual union all select 'banana' from dual ) select c.fruit, case c.fruit whe

Categories : SQL

SQL Count: How to count value of a column from the same value of other column
This is a pivot, which has several solutions. One that is general across databases is to use conditional aggregation: select name, sum(case when Color = 'Black' then 1 else 0 end) as Black, sum(case when Color = 'Red' then 1 else 0 end) as Red, sum(case when Color = 'White' then 1 else 0 end) as White from tblTry group by name; The problem with your query is that the count

Categories : SQL

SQLAlchemy conditional relationship
It's done with SQLAlchemy's bindparam() function. A relationship is created for Movie model: show_times = db.relationship(lambda: ShowTime, primaryjoin=lambda: db.and_( Movie.id == ShowTime.movie_id, ShowTime.theatre_id == db.bindparam('theatre_id'))) Then a query would be something like this: Movie.

Categories : SQL

SQL JOIN WITH GROUPBY AND WHERE CLAUSE
Use NOW() function of MYSQL as below instead of sysdate: SELECT c.REGION,COUNT(*)COUNT FROM OAS_CONTRACT a INNER JOIN OAS_CONTRACT_OFFICES b ON a.CONTRACT_ID=b.CONTRACT_ID INNER JOIN OAS_CONTRACT_OFFICES_DETAIL c ON b.T2_CODE = c.T2_CODE WHERE (a.EXPIRY_DATE >= NOW()) GROUP BY c.REGION ORDER BY c.REGION Try it in SQL fiddle here

Categories : SQL

Oracle SQL Query to Distribute a value into different rows based on priority
You can calculate the occupied space using a cumulative sum: select f.*, sum(max_capacity-available_seats) as occupied, sum(max_capacity-available_seats) over (order by priority) as cumeocc from m_flight f; With this information, you can allocate v_Num new seats: select f.*, (case when v_Num >= cumeocc - occupied then greatest(v_num - (cumeocc - occupied), occupi

Categories : SQL

How to reference a composite primary key into a single field?
What you're asking for is tantamount to saying "I want to treat three pieces of information as one piece of information without explicitly making it one piece of information". Which is to say that it's not possible. That said, there are ways to make happen what you want to happen Create a surrogate key (i.e. identity column) and use that as the FK reference Create a computed column that is the

Categories : SQL

SPARQL : Find Difference and Common Elements in 2 Sets
SELECT ?subject WHERE { ?subject a python:Keywords . ?subject a cpp:Keywords . } should give you the common keywords. SELECT ?subject WHERE { ?subject a python:Keywords. FILTER NOT EXISTS {?subject a cpp:Keywords} } should return python keywords, which are not cpp keywords.

Categories : SQL

Issues with Select Query in Access
You don't have a valid Data Model. Your Data table should be linked with other tables with IDs, not your phone. And I suppose because of that your query is so strangely generated (A join within a join). And why do you have duplicates in the linking table? And naming tables with spaces is a bad practice. This is your edited sql query: SELECT DATA.[Charges], DATA.[Rate], Date.[ Date], Phone_Maste

Categories : SQL

Need help designing a query that tells how many remaining days before a payment is due
If you are using Oracle (since you removed the SQL Server tag and kept the SQL*Plus tag), you wouldn't use DATEDIFF (which is a valid function in SQL Server but not Oracle). You'd simply add 45 to the date. SELECT tID, dateBooked + 45 as PaymentDueDate FROM transactions If you want to use intervals, you could also SELECT tID, dateBooked + interval '45' day as PaymentDueDate FROM transacti

Categories : SQL

Compare Date from TableB to several date ranges in TableA: Building WHERE statement
If you just want to find the rows in TableB that have a Start Date in any of the date ranges in TableA you can use a correlatedexists: select * from TableB b where exists ( select 1 from TableA a where b.StartDate between a.StartDate and a.EndDate ) Sample SQL Fiddle

Categories : SQL

MS SQL Join function anomaly
This query ran with no repeat rows SELECT sv.FirstName+' '+sv.LastName 'SalesPersonName',st.[Group] 'TerritoryGroup',st.Name 'TerritoryName', ps.Name 'ShipState', YEAR(OrderDate) 'OrderYear',MONTH(OrderDate) 'OrderMonth',soh.TotalDue FROM Person.Address pa INNER JOIN Person.StateProvince ps ON pa.StateProvinceID = ps.StateProvinceID INNER JOIN Sales.SalesOrderHeader

Categories : SQL

I am unable to get max function in SQL
Use group by and max(): SELECT A.F01 AS F01, P.F31 AS F31, P.F30 AS F30, MAX(C.F38) AS F38 FROM A_TAB A LEFT OUTER JOIN P_TAB P ON P.F01=A.F01 LEFT OUTER JOIN C_TAB C ON C.F01=A.F01 GROPU BY A.F01, P.F31, P.F30 ORDER By 1; I removed F19 because you don't say what to do with it.

Categories : SQL

Table Join issue
The only way to "join" tables that have no relation is by unioning them together: select attribute1, attribute2, ... , attributeN from table1 where <predicate> union // or union all select attribute1, attribute2, ... , attributeN from table2 where <predicate> the where clauses are obviously optional EDIT optionally you could join the tables together by stating ON true which

Categories : SQL

Oracle - Create a procedure to insert into a dimension table from multi tables
SELECT query you use for INSERT. It has to be a JOIN and your syntax is wrong. It has to to be table_name/alias.column_name while referring the columns. create or replace procedure populate_product_dimension AS BEGIN INSERT into product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date) SELECT seq_prod.nextval, p.prod_id, p.brand, ph.price,

Categories : SQL

Finding an approach to selecting last recorded value alongside current value for a field in SQLite
I would do this using a correlated subquery: SELECT p.date, p.instrument, p.price AS curpx, (SELECT p2.price FROM price p2 WHERE p2.instrument = p.instrument AND p2.date < p.date ORDER BY p2.date DESC LIMIT 1 ) AS lastpx FROM prices p;

Categories : SQL

SQL Server generating random spatial geography around point?
I found an answer from this post http://gis.stackexchange.com/a/25883/37373 I adapted the response into SQL Server code. DECLARE @geo as GEOGRAPHY,@newgeo as GEOGRAPHY SET @geo = (SELECT ZipGeo FROM Location.ZipCodes WHERE ZipCode='90210') DECLARE @r float,@t float, @w float, @x float, @y float, @u float, @v float; SET @u=RAND(); SET @v=RAND(); --8046m = ~ 5 miles SET @r= 8046/(111300*1.0); S

Categories : SQL

SQL Server 2012 - No information stores if I use INSTEAD OF INSERT trigger
INSTEAD OF triggers completely replaces the operation they intercept. When creating such a trigger the operation that triggered it to execute doesn't execute, instead its code is executed, so the INSERT itself will never actually be run. To correct your code, the trigger must run the INSERT again after it checks the condition: CREATE TRIGGER CheckValidRegionCode ON RegionCodes INSTEAD O

Categories : SQL

Cannot insert duplicate key in object 'dbo.Countries'. The duplicate key value is (1)
Try this... INSERT INTO [dbo].[Countries] (countryID, countryName) SELECT countryId, countryName FROM [dbo].Sheet1 WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Countries] WHERE Sheet1.countryId = [Countries].countryID) And since you are inserting into an Identity field I would also execute the following lines of code once I am done inserting data DBCC CHECKIDEN

Categories : SQL

How can I get all the objects create by a user in Oracle?
As I know Oracle doesn't keep a creator in the dictionary. But you can use the information from dba_objects: SELECT s.owner, s.name, s.TYPE, s.line, s.text, o.created, o.last_ddl_time from sys.dba_source s, sys.dba_users u, sys.dba_objects o where s.owner = u.username and u.default_tablespace not in ('SYSTEM', 'SYSAUX') and s.owner = o.

Categories : SQL

Select random non-repeating rows per user
I'd go with approach #1. You can get a first estimate of C by counting the user's rows in user_match_product (supposed unique). If he already possesses half the possible products, selecting twice the number of random products seems a good heuristic. You can also have a last-ditch correction that verifies that the number of extracted products is actually X. If it was, say, X/3, you'd need to run

Categories : SQL

Select all records where function is false
I tried the same logic but am getting proper output as expected. Am getting rows returned only when function outputs 0 (ie) Invalid records. I don't see NULL values for valid records. create table dbo.Contracts1(id int,name varchar(50),pric int) INSERT INTO dbo.Contracts1 VALUES ( 1,'invalid',40000), (2,'valid',50000), (3,'valid',35000), (4,'invalid',40000)

Categories : SQL

Soft delete in SQL Server using stored procedure
Try this update statement inside the SP: Update t set t.IsDeleted = 1 from tblTeam t where t.TeamId = @pteamid and not exists (select 1 from tblUser where TeamId = @pteamid) and not exists (select 1 from tblAcount where TeamId = @pteamid)

Categories : SQL

How to get the id of MAX() function
In Oracle, you can use a keep clause: select id_employee, max(sum_order), max(id_order) keep (dense_rank first order by sum_order desc) as IdAtMaxSum from table group by id_employee;

Categories : SQL

Audit operations with a trigger
I'm still not sure that I understand the question. My guess is that you just want something like CREATE OR REPLACE TRIGGER trigger_name AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW BEGIN INSERT INTO audit_e( dml_type, old_employee_id, new_employee_id, old_first_name, new_first_name, ...

Categories : SQL

why do i need to create a database if i only have one table?
Tables don't exists out of nowhere, they always are contained within a database. The basic structure of a DB server is that it contains some DBs, then they contain in turn tables, views, stored procedures and some other objects. Even if your entire database is as simplistic as to have a single table, it's still a database. The DB itself also adds other properties like the actual storage on disk,

Categories : SQL

How can I make null values be considered as MAX in SQL?
select product, case when sum(case when price is null then 1 else 0 end) > 0 then null else max(price) end as price from your_table group by product

Categories : SQL

How to update a column with values depending upon conditions - Oracle
Edit: Corrected my answer to update 0 as well. Assuming Y as 1 and N as 0 (since you said COL2 is INT) UPDATE TAB1 T1 SET T1.COL2 = CASE WHEN EXISTS(SELECT 1 FROM TAB2 T2 WHERE T2.COL1 = T1.COL1) THEN 1 ELSE 0 END; Here is the SQLFiddle.

Categories : SQL

T-SQL - Performing a Join to Retrieve Counts of 0
Assuming EventType is in [StormEvents].[dbo].[DT_FatalityEvent] the following would break a right join where EventType like'%tornado%' SELECT State, YEAR(BeginDate) as Year, COUNT([StormEvents].[dbo].[DT_FatalityEvent].[EventID]) as CountOfEvents FROM [StormEvents].[dbo].[DT_Event] right JOIN [StormEvents].[dbo].[DT_FatalityEvent] ON [StormEvents].[dbo].[DT_Event].[EventID]

Categories : SQL

SQL select statement to get calculated column from second row
Try this. Using Left Join and Row_Number() you can get the result. There is a mistake in your expected output in last column the last row value should be 23 not 33 because you have said C4=C1-c3 so 50-27 should be 23 not 33 CREATE TABLE #t (c1 INT,c2 INT) INSERT #t VALUES( 25,30), (42,28), (50,15); WITH cte AS (SELECT Row_number()OVER (ORDER BY c1) rn,* FROM #t)

Categories : SQL

select last day of each month
Try this. Used CTE for better code readability ;WITH cte AS (SELECT custom, Max(fecha) fecha FROM @sales GROUP BY custom, Datepart(yyyy, fecha), Datepart(mm, fecha)) SELECT b.custom, b.fecha, Sum(b.sales) [Sum] FROM cte a JOIN @sales b ON a.custom = b.custom AND a.fecha = b.fecha GROUP BY b.custom, b.fecha

Categories : SQL

SQL query in Oracle and SQL server to get 3rd highest salary
Use Dense_Rank() ranking function in SQL SERVER to find the 3rd highest salary. Row_number() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER SELECT empname, salary FROM (SELECT Dense_Rank() OVER(ORDER BY salary

Categories : SQL

Query to show sum of all earnings at the all lower levels of hierarchy of people for each person
SELECT p.root, sum(e.EARNINGVALUE) s FROM Earnings e JOIN (SELECT p.IdPerson, connect_by_root p.IdPerson root, connect_by_isleaf lf FROM People p CONNECT BY PRIOR p.IdPerson= p.IdLeader ) p on p.IdPerson = e.IdPerson where root != p.IdPerson -- uncomment this if you want to calculate earnings of people who don't have subordinates -- or lf = 1 GROUP BY root;

Categories : SQL

how to remove duplicate records but need to keep their child table rows and tag them to survived row
You need to replace all the ids in the second table with the minimum matching id in the first, if I understand correctly. This query should return the result set you want: select mt.minid, name_city from (select t.*, min(id) over (partition by name) as minid from master_table t ) mt join table2 t2 on t2.id = t.id; It is unclear from the question whether you just want to ge

Categories : SQL

ISNull, NULLIF, COALESCE, not working
That means that Amountt is not NULL. Try using a case instead: select (case when Amountt is null or Amountt = 'NULL' or Amountt = '' then 0 else Amountt end) If the situation is that your code is returning no rows, then you can use aggregation: SELECT COALESCE(SUM(Amountt), '0') AS count FROM 1Table WHERE (Operations_Day BETWEEN @startdate AND @enddate) AND (location = @Storenumber) AND

Categories : SQL

Query making sum in the next child level of hierarchy
From what you describe, you don't need a hierarchical query. Just do: select p.idleader, sum(earningvalue) as earningvalue from person p join earnings e on e.idperson = p.idperson group by p.idleader;

Categories : SQL

Locating a trigger
In SSMS, expand your database_name -> Expand Programmability -> Expand Database trigger -> All trigger created on this database are listed here Quoting specific part from MSDN Documentation Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL trigger

Categories : SQL

Need query for SQL Server
You have a very poor data structure. SQL has a great method for storing lists of things -- it is called a table, not a string. You should have a junction table, with one row per user and per article. So, in UserPages, there would be four rows for the first user. Sometimes, we are stuck with data that we have no ability to change. If that is the case, you can do what you want, but it is ineffi

Categories : SQL

Unable to create account for installing Oracle Database
This is the description of the error, I saw it, but was trying to find an idea how to fix it. Anyway, I solved it by renaming the volume group and updating accordingly the fstab and the grub.conf.

Categories : SQL

what's wrong with my transaction...?
Maybe you want something like this to happen? begin try BEGIN TRAN T1 DELETE FROM EMP1 WHERE ID=5 DELETE FROM EMP WHERE ID=11 COMMIT TRAN T1 PRINT 'SUCCES FULLY DELETED' end try begin catch if @@trancount > 0 ROLLBACK TRAN T1 end catch

Categories : SQL




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.