SQL Flashcards
Difference between ON clause and Where clause in joins
Difference between ON clause and Where clause in joins
PIVOT
- -https://www.youtube.com/watch?v=h3BtudZehuo
- -PIVOT sql server operator that is used to turn unique values from one column into multiple columns in the output, there by effectively rotating a table
- -for PIVOT to work, we need to have only the columns that we need to group by and aggregate this is achieved using temp, CTE or Derived tables
select * from ( select FORMAT(MonthStartDate, 'yyyy-MM') as DateInfo,--Non-Pivoted column from the source table - it is going to be included in the grouping for the pivot TotalExpense,ExpenseType --,ExpenseCategory -- this is additional grouping along with the date from MonthlyExpenseTracker MET left join ExpenseType ET on MET.ExpenseTypeID=ET.ExpenseTypeID left join ExpenseCategory EC on EC.ExpenseCategoryID=ET.ExpenseCategoryID ) as SourceTable -- this should have only the columns that we need in PIVOT ; we are using derived table here PIVOT ( sum(TotalExpense) for ExpenseType in ( Restaurant ,Trips ,Travel ,Rent ,Internet ,Electricity ,[Education Loan] ,Groceries ,Others ,AtWork ,Shopping ,[Splitwise settlement] ,[Entrance Tickets] ,Entertainment ,Fuel ,Parking ,Medicine ,Electronics ,Furniture ,Insurance ,[Mobile bill] ,[Car Loan Payment] ,Gym ,Savings ,Retirement ,[God Prayer] ,[Future Generation] ,[Future Trips Savings] ,[Pocket Money] ,[Salary] ,[Hair Cut] ,Donations) ) AS PIVOTTABLE -- alias name for pivot order by DateInfo
–select * from ExpenseType
–select * from MonthlyExpenseTracker
- -using MAX of a value will give the same values
- -if we do not want to have aggregate function as sum,count; we could use MAX or MIN; just to pivot the values in the table
select [Maternal-Fetal Medicine],[Oral Surgery & Maxillofacial Surgery] from (
select DD.DimDiv_UPIR_DepartmentName as Division,M.LongMeasureName as Measure
,M.MeasureName –including additional column to the grouping to reduce the show the actual values for the measure
from odsDivMeas DM
left join DimDivision DD on DD.DimDivID_SK=DM.DivID
left join DimMeasure M on DM.MeasID=M.DimMeasureID_SK
where DM.DivID in (4,7) and DM.ActiveFlag=1)
as SourceTable
Pivot
(
MAX(Measure)
for Division in ([Maternal-Fetal Medicine],[Oral Surgery & Maxillofacial Surgery])
)
as PivotTable
order by [Maternal-Fetal Medicine],[Oral Surgery & Maxillofacial Surgery]
Replace function in SQL
Replace “T” with “M”:
SELECT REPLACE(‘SQL Tutorial’, ‘T’, ‘M’);
The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.
Note: The search is case-insensitive.
Tip: Also look at the STUFF() function.
Syntax
REPLACE(string, old_string, new_string)
Parameter Values
Parameter Description
string Required. The original string
old_string Required. The string to be replaced
new_string Required. The new replacement string
CAST(expression AS datatype(length))
The CAST() function converts a value (of any type) into a specified datatype.
Tip: Also look at the CONVERT() function.
Syntax
CAST(expression AS datatype(length))
Parameter Values
Value Description
expression Required. The value to convert
datatype Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length) Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary)
CAST vs CONVERT
In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another.
Since the CAST and CONVERT can be used interchangeably in most situations, you may wonder if one function is better than the other.
Let’s read further. We will see whether there really is a difference between these two functions in this post.
The examples you’ll find in this lesson are based on the Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Are Cast and Convert Different?
The CAST and CONVERT functions are both used to convert data from one data type to another, and it is no coincidence they share the same entry in MSDN.
Here is an example using both functions in the same statement:
SELECT CAST ('10' as int) * 20,
CONVERT (int, '10') * 20
In both cases we’re converting from the text value ’10’ to its integer representation.
Hopefully this example jogs your memory regarding the CAST and CONVERT functions. If not, then read my blog Data Type Conversion Functions to know all the details.
Similarities with CAST and CONVERT
In many ways CAST and CONVERT are similar. Both are used to convert data from one type to another. Thought their syntax is different, both functions are able to convert values from one formation to another.
Anything you can do with CAST you can do with CONVERT. If you’re wondering whether there is a difference in performance in performance, according to Nakul Vachhrajani’s post, there isn’t. In fact, Nakul show that CAST is really implemented internally via CONVERT!
Differences between CAST and CONVERT
CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation specific.
CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.
For example, when converting a DateTime datatype to Varchar, you can specify the resulting date’s format, such as YYYY/MM/DD or MM/DD/YYYY.
SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY,
CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD
The numbers shown in red are the style formatter. There are many style formats you can use. The complete list is here.
Should I use CAST or Convert?
Unless you have some specific formatting requirements you’re trying to address during the conversion, I would stick with using the CAST function. There are several reason I can think of:
CAST is ANSI-SQL compliant; therefore, more apt to be used in other database implementation.
There is no performance penalty using CAST.
I think CAST is easier to read, and since it is part of the ANSI specification, your non-SQLServer DBA think so too!
Getting columns for a select from objects
select the columns and drag and drop into the query window, it will pull up all the columns
using try catch for dropping temp table instead of objectID() verification
BEGIN TRY Drop table #Conditions end try begin catch end catch
You can include multiple tables in a DROP TABLE statement.
This will drop all that exist and raise an error for any that don’t.
If you don’t want those errors reported back you would need to use try..catch.
BEGIN TRY
DROP TABLE #Scores1,
#Scores2,
#Scores3,
#Scores4,
#Scores5,
#Scores6,
#Scores7,
#Scores8
END TRY
BEGIN CATCH
END CATCH
i think try-catch will drop the temp tables but ObjectID will run a query against the tempdb, this might be performance hit but not sure
Returning Muliple tables from stored procedure
create procedure ProcedureName
as
select * from table1
select * from table2
Fetch rows in sql
in this tutorial, you will learn how to use the SQL Server OFFSET FETCH clauses to limit the number of rows returned by a query.
https://www.sqlservertutorial.net/sql-server-basics/sql-server-offset-fetch/
The OFFSET and FETCH clauses are the options of the ORDER BY clause. They allow you to limit the number of rows to be returned by a query.
The following illustrates the syntax of the OFFSET and FETCH clauses:
1
2
3
ORDER BY column_list [ASC |DESC]
OFFSET offset_row_count {ROW | ROWS}
FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY
In this syntax:
The OFFSET clause specifies the number of rows to skip before starting to return rows from the query. The offset_row_count can be a constant, variable, or parameter that is greater or equal to zero.
The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed. The offset_row_count can a constant, variable or scalar that is greater or equal to one.
The OFFSET clause is mandatory while the FETCH clause is optional. Also, the FIRST and NEXT are synonyms respectively so you can use them interchangeably. Similarly, you can use the FIRST and NEXT interchangeably.
The following illustrates the OFFSET and FETCH clauses:
SQL Server OFFSET FETCH
Note that you must use the OFFSET and FETCH clauses with the ORDER BY clause. Otherwise, you will get an error.
The OFFSET and FETCH clauses are preferable for implementing the query paging solution than the TOP clause.
The OFFSET and FETCH clauses have been available since SQL Server 2012 (11.x) and later and Azure SQL Database.
To skip the first 10 products and return the rest, you use the OFFSET clause as shown in the following statement:
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price,
product_name
OFFSET 10 ROWS;To skip the first 10 products and select the next 10 products, you use both OFFSET and FETCH clauses as follows:
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price,
product_name
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;To get the top 10 most expensive products you use both OFFSET and FETCH clauses:
SELECT
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC,
product_name
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY;
In this example, the ORDER BY clause sorts the products by their list prices in descending order. Then, the OFFSET clause skips zero row and the FETCH clause fetches the first 10 products from the list.
In this tutorial, you have learned how to use the SQL ServerOFFSET FETCH clauses the limit the number of rows returned by a query.
isnumeric(Order_value)
select * from stgHbA1C where
isnumeric(Order_value)=0
to check if the data in this field is numeric value =0 means non-numeric and =1 means numeric
Passing parameters to the query and running open query
DECLARE @HistStartDate as varchar(12);
SET @HistStartDate = ‘01/01/2019’;
SET @Qry = ‘ select ser.prov_id,ser.prov_name, p.pat_name, p.pat_mrn_id, p.pat_id, e.pat_enc_csn_id, e.contact_date,
TO_CHAR(rslt.ord_num_value) as ord_num_value,TO_CHAR(rslt.ord_value) as ord_value
from pat_enc e join patient p on p.pat_id = e.pat_id join order_results rslt on rslt.pat_enc_csn_id = e.pat_enc_csn_id and rslt.component_id in (801002,802097,801046,805282,700194,700185,700046) left join clarity_ser ser on ser.prov_id = e.visit_prov_id where e.appt_prc_id in (‘’'’20790’’’’,’’'’823’’’’,’’'’826’’’’,’’'’800’’’’,’’'’804’’’’,’’'’801’’’’,’’'’20120’’’’,’’'’20121’’’’,’’'’1743’’’’) and e.department_id in (20001010,20010010) and e.appt_status_c = 2 and e.contact_date >= to_date(‘’’’’ + @HistStartDate + ‘’’’’,’’'’DD/MM/YYYY’’’’) order by e.contact_date desc ‘;
SET @OPENQUERY=’select * from OPENQUERY(PERIOPRPT,’’’
+ @Qry + ‘’’)’
executing open query from SQL to oracle
DECLARE @OPENQUERY VARCHAR(max);
DECLARE @Qry VARCHAR(max);
SET @Qry = ‘ select ser.prov_id,ser.prov_name, p.pat_name, p.pat_mrn_id, p.pat_id, e.pat_enc_csn_id,
e.contact_date,TO_CHAR(rslt.ord_num_value) as ord_num_value,TO_CHAR(rslt.ord_value) as ord_value
from pat_enc e join patient p on p.pat_id = e.pat_id join order_results rslt on rslt.pat_enc_csn_id = e.pat_enc_csn_id and rslt.component_id in (801002,802097,801046,805282,700194,700185,700046) left join clarity_ser ser on ser.prov_id = e.visit_prov_id where e.appt_prc_id in (‘’'’20790’’’’,’’'’823’’’’,’’'’826’’’’,’’'’800’’’’,’’'’804’’’’,’’'’801’’’’,’’'’20120’’’’,’’'’20121’’’’,’’'’1743’’’’) and e.department_id in (20001010,20010010) and e.appt_status_c = 2 and e.contact_date >= to_date(‘’'’01-07-2019’’’’,’’'’DD-MM-YYYY’’’’) and e.contact_date <= to_date(‘’'’31-12-2019’’’’,’’'’DD-MM-YYYY’’’’) order by e.contact_date desc ‘;
SET @OPENQUERY=’select * from OPENQUERY(PERIOPRPT,’’’
+ @Qry + ‘’’)’
EXEC (@OPENQUERY);
