SQL for Analytics

-

Comments

Post a Comment

Popular Posts

CLASS 1 -How to CREATE DATABASE AND TABLE and DATA TYPES information

-
Image
 SYNTAX--- --crate database Create   DATABASE Finance ; --how to use your created database use Finance ; --create table Create TABLE EMPLOYEE (                          ID INT primary key ,                            Employee_Name VARCHAR ( 100 ),                          EMP_PHONE bigint ,                          EMP_DOB DATE not null,                     ...
Read more

Views in SQL

-
What is a View? A view is nothing more than a  saved SQL query . A view can also be considered as a  virtual table . View script  Create view VIEW_NAME AS SELECT T1. CUSTEMOR _NAME , T1.CUST_ID , T1.Address,  T2.Order_units From CUSTERMORTABLE  AS T1 Inner join   ORDERS AS T2 ON T1.CUST_ID =T2.CUST_ID Where order_units > = 60  
Read more

How to get only numbers in a column in SQL server? using ISNUMERIC function ?

-
Image
Q  How to get only numbers in a column in SQL server ?  Let me explain problem.      I have a table like this in this table the column(Pin No) have invalid data when i write a query i want only correct pin number rows how to do that. i will use  ISNUMERIC  function to achieve this result . ID User_name Pin no 1 Todd 517408 2 sara Pin no 3 Mark pin 516 4 Kera 506789 create a table in sql server:- create   table  user   ( id  int   , User_Name  varchar ( 20 ),  pinno  varchar ( 7 )) Go insert   into   user   values  ( 1  , 'Todd' ,   '517408' ) insert   into   user   values  ( 2 ,   'sara' ,   'Pin no' ) insert   into   user   values  ( 3 ,   'Mark' ,   'pin 516' ) insert   into   user   values  ( 4 ,   'Kera' ,   '506789' ) write a query to get only correct pin num...
Read more

Answers for Sql interview questions ?

-
Image
Problem 1 find the problem statement click on the bellow link           Problem Statement Answer select * from plch_by_car intersect select * from plch_by_car union select * from plch_on_foot Problem 2 find the problem statement click on the bellow link            Problem Statement in this problem we are using LAG function to sallow this problem. for Lag function followed by Over and order by  clause  is required  Answer select Logg_date , logged_price , round ( isnull ( logged_price - lag ( logged_price ) over ( order by Logg_date ), ' ' ), 2 ) as yesterday_diff , round ( isnull ( logged_price - lag ( logged_price , 2 ) over ( order by Logg_date ), ' ' ), 2 ) as Day_Before_yesterday_diff   from PLCH_GAS_PRICE order by Logg_date if we are not using  round  function then data comes like this  Problem 3 find the problem stat...
Read more

How to Clean unwanted symbols in a Number column In SQL server ?

-
Image
. How to clean a phone Number In SQL Server ?       I have a table like this. In this table i want to display phone number without (.) symbol. for this we suppose to create a function. Table info: CREATE A USER DEFINED FUNCTION :-  USE BELLOW CODE TO CREATE A FUNCTION IN SQL SERVER(SSMS) create   function  Udf_getNumber   (  @String  Nvarchar ( 200 )) Returns   nvarchar ( 200 ) As Begin Declare  @intalpha  int set  @intalpha  =   PATINDEX ( '%[^0-9]%' , @String ) Begin   while  @intalpha  > 0 Begin set   @String  = STUFF ( @String , @intalpha , 1 , '' ) set  @intalpha  = PATINDEX ( '%[^0-9]%' , @String ) end end Return   isnull ( @String , 0 ) end once create the function use this function to get desired output  note:- use function Fully Qualified name (schema name. function name eg  dbo . Udf_getNumber ...
Read more

class 5 - SQL Operators

-
SQL Arithmetic Operators Operator Description Example + Add Try it - Subtract Try it * Multiply Try it / Divide Try it % Modulo Try it SQL Bitwise Operators Operator Description & Bitwise AND | Bitwise OR ^ Bitwise exclusive OR SQL Comparison Operators Operator Description Example = Equal to Try it > Greater than Try it < Less than Try it >= Greater than or equal to Try it <= Less than or equal to Try it <> Not equal to Try it SQL Compound Operators Operator Description += Add equals -= Subtract equals *= Multiply equals /= Divide equals %= Modulo equals &= Bitwise AND equals ^-= Bitwise exclusive equals |*= Bitwise OR equals SQL Logical Operators Operator Description Example ALL TRUE if all of the subquery values meet the condition Try it AND TRUE if all the conditions separated by AND is TRUE Try it ANY TRUE if any of the subquery values meet the condition Try it BETWEEN TRUE if the operand is within the...
Read more