ProgrammingOcean

In Microsoft SQL Server normally when you use aggregation function you must use Group By clause to get the Total/Average So on. But by using Group by you can get the aggregation only (Total/Avg…etc) based on the columns which you are giving in Group By clause. But if you want get the subtotals; you can use Cube, rollup clause with Group By clause. Cube clause would give the subtotal for all possible matching like summary for all specified columns whatever you mentioned in group by clause Main different between Cube and Rollup is, Group BY with Cube clause would give All the Subtotal as summary. In the shown example, I need to get the total and subtotal of NoofBooks based on BCode and NoofLeaves but I can only get total based on those columns if I use Group By clause only. For that I can use cube clause with Group By clause. When we use cube clause it would give total of NoofBooks for each BCode and NoofLeaves , subtotal for each BCode , Give the Subtotal s

In Microsoft SQL Server normally when you use aggregation function you must use Group By clause to get the Total/Average So on. But by using Group by you can get the aggregation only (Total/Avg…etc) based on the columns which you are giving in Group By clause. But if you want get the subtotals; you can use rollup clause with Group By clause. Rollup clause would give the subtotal specified columns whatever you mentioned in group by clause. In the shown example, I need to get the total and subtotal of NoofBooks based on BCode and NoofLeaves but I can only get total based on those columns if I use Group By clause only. For that I can use rollup clause with Group By clause. When we use rollup clause it would give total of NoofBooks for each BCode and NoofLeaves , subtotal for each BCode and Grand Total of NoOfBooks Here you can notice that when its give the subtotal for each BCode , NoofLeaves columns filled NULL values as it does not have any values to retur

You use SOUNDEX(x) to get a string containing the phonetic representation of x. This lets you compare words that sound alike in English but are spelled differently. The following query retrieves the last_name column from the customers table where last_name sounds like “whyte”: (Pronouncing in same way but spelling different ) SELECT last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('whyte'); LAST_NAME ---------- White The next query gets last names that sound like “bloo”: SELECT last_name FROM customers WHERE SOUNDEX(last_name) = SOUNDEX('bloo'); LAST_NAME ---------- Blue

If you need to search for actual underscore or percent characters in a string, you can use the ESCAPE option to identify those characters. For example, ' %\%%' ESCAPE '\' The character after the ESCAPE tells the database how to differentiate between characters to search for and wildcards, and in the example the backslash character (\) is used. The first % is treated as a wildcard and matches any number of characters; the second % is treated as an actual character to search for; the third % is treated as a wildcard and matches any number of characters. The following query uses the promotions table, which contains the details for products being discounted by the store . The query uses the LIKE operator to search the name column of the promotions table for the values whatever starts or ends with '%' character. SELECT name FROM promotions WHERE name LIKE '% \% % ' ESCAPE '\'; The results would be as shown below, NAME ------------------------------ 1

SELECT Dense_Rank() OVER ( ORDER BY receiptdate ) as Firt_Arrival,locationnumber,receiptdate FROM inventorylocsubdetail Here Dense_Rank() fn would return the rank based on the ReceiptDate. But this would be in sequence even if there are same rank more than one. ( This is the major different between Rank() and Dense_Rank() ) E.g: here rank 4 repeating for three records,though next rank is 5 (4,4,4,5)

SELECT Rank() OVER ( ORDER BY receiptdate ) as Firt_Arrival,locationnumber,receiptdate FROM inventorylocsubdetail Here Rank() fn would return the rank based on the ReceiptDate. But this would be jumbling if there are same rank more than one. E.g: here rank 4 repeating for three records,because of this the next rank is 7 (4,4,4,7)

In oracle SQL *Plus Worksheet you can simply export the query and results to external files rather copy and paste. E.g :If you want to export the all employee details to Text file, Here it is the very simple solution ; Spool c:\Employees_Data.txt; Selec t * From Employees ; Once you run this query,The Query and Results would export to Employees_Data.txt file.

SELECT JobNo,BankName , NoofLeaves,NoofBooks ,bcode,ROW_NUMBER() over (partition by bcode order by bcode) as Rank FROM ChequeDetails WHERE JobNo='161008LN1' and BankName='008'

Hi folks, This is very useful when we use query results with a table. for example here i have written query to get the sum of books from one table then i join that query resultset with another table in which i have sum up. with chequedet( BCode,NoofLeaves,TTLBooks) as ( select BCode,NoofLeaves,sum(NoofBooks) TTLBooks from ChequeDetails where JobNo='xxxxxxx' group by BCode,NoofLeaves ) select a.bcode,b.NoofLeaves, b.TTLBooks,sum(a.noofbooks) from ChequeDetails a ,chequedet b where a.BCode=b.BCode and JobNo='xxxxxxx' group by a.bcode,b.NoofLeaves,b.TTLBooks order by a.bcode,b.NoofLeaves, b.TTLBooks

SELECT row_number() OVER (ORDER BY DeliveryLocationCode) AS Row_No,DeliveryLocationCode, ContactPerson FROM DeliveryLocationMaster

SQL Server/Oracle/MySQL Union query Many time we require results from different tables or different queries. We try one after the other and list the result accordingly. There is a better way to do this by using UNION command or query. We can list records of two different tables by combining two sql queries to one. What is the advantage ? We can as well write the query two times and display one after the other. The point here is we can pick up unique records also. Say we have two tables one is storing name of the football players and other is storing players of baseball team. We want to display a list of players who are member of one of the team at least. Here we can display the name of the players by using UNION command. If John is member of football and baseball team then his name will appear once only. If we want to display all the records then we can use ALL along with the UNION command. Here are two tables with three records each. Please note that there is one common record

SELECT sysobjects.name FROM syscolumnsleft join sysobjects on sysobjects.id = syscolumns.id WHERE syscolumns.name like 'OBRNo'

SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE UPPER( COLUMN_NAME ) LIKE '%FRANCE%';