About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Monday, January 3, 2022

SQL Query: Immediate Food Delivery solved using CTE


QUESTION: Table: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id is the primary key of this table.
The table holds information about food delivery to customers that make orders at some date and specify a preferred delivery date (on the same order date or after it).


If the preferred delivery date of the customer is the same as the order date then the order is called immediate otherwise it's called scheduled.

The first order of a customer is the order with the earliest order date that customer made. It is guaranteed that a customer has exactly one first order.

Write an SQL query to find the percentage of immediate orders in the first orders of all customers, rounded to 2 decimal places.

The query result format is in the following example:

Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+

Result table:
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
The customer id 1 has a first order with delivery id 1 and it is scheduled.
The customer id 2 has a first order with delivery id 2 and it is immediate.
The customer id 3 has a first order with delivery id 5 and it is scheduled.
The customer id 4 has a first order with delivery id 7 and it is immediate.
Hence, half the customers have immediate first orders.
SOLUTION:
  • Step1) Try to find first order of each customer using dense rank function
  • Step2) Get the immediate order from the first order set
  • Step3) Get the percentage of immediate order to the total first order
;with FirstOrderCTE as
(select 
 delivery_id, customer_id, order_date, 
 customer_pref_delivery_date, 
 dense_rank() over (partition by customer_id order by order_date asc) as rnk 
from Delivery
)
,immediate_order as
(select count(*) as immediate_order_count 
 from FirstOrderCTE 
 where rnk = 1 and order_date = customer_pref_delivery_date
)
 ,percent_of_immediate_order as
 (select 
  cast(((select immediate_order_count from immediate_order limit 1)/
  cast(count(*) as numeric(8,2))) * 100 as numeric(8,2)) as immediate_percentage,
  
  (select immediate_order_count from immediate_order limit 1),
  cast(count(*) as numeric(8,2))
  from FirstOrderCTE where rnk = 1
 )
 --select immediate_order_count from immediate_order limit 1
 select immediate_percentage from percent_of_immediate_order
Post Reference: Vikram Aristocratic Elfin Share

Saturday, January 1, 2022

SQL Query: Department Top Three Salaries using Window Function

QUESTION: Table: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| Id           | int     |
| Name         | varchar |
| Salary       | int     |
| DepartmentId | int     |
+--------------+---------+
Id is the primary key for this table.
Each row contains the ID, name, salary, and department of one employee.


Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| Id          | int     |
| Name        | varchar |
+-------------+---------+
Id is the primary key for this table.
Each row contains the ID and the name of one department.


A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners in each of the departments.

Return the result table in any order.

The query result format is in the following example:



Employee table:
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

Department table:
+----+-------+
| Id | Name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+

Result table:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees
SOLUTION:
  • Step1) Use dense rank to rank the salary on the basis of department and order by salary descending
  • Step2) Filter the above result where dense rank is less than equal 3

;with EmpCTE as ( select d.Name as Department,e.Name as Employee,e.Salary as Salary, dense_rank() over (partition by e.DepartmentId order by e.Salary desc) as drno from Employee e inner join Department d on e.DepartmentId = d.Id ) select Department, Employee, Salary from EmpCTE where drno <=3

SQL Query: Maximum Transaction Each Day

QUESTION: Table: Transactions

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| transaction_id | int      |
| day            | datetime |
| amount         | int      |
+----------------+----------+
transaction_id is the primary key for this table.
Each row contains information about one transaction.


Write an SQL query to report the IDs of the transactions with the maximum amount on their respective day. If in one day there are multiple such transactions, return all of them.

Return the result table in ascending order by transaction_id.

The query result format is in the following example:



Transactions table:
+----------------+--------------------+--------+
| transaction_id | day                | amount |
+----------------+--------------------+--------+
| 8              | 2021-4-3 15:57:28  | 57     |
| 9              | 2021-4-28 08:47:25 | 21     |
| 1              | 2021-4-29 13:28:30 | 58     |
| 5              | 2021-4-28 16:39:59 | 40     |
| 6              | 2021-4-29 23:39:28 | 58     |
+----------------+--------------------+--------+

Result table:
+----------------+
| transaction_id |
+----------------+
| 1              |
| 5              |
| 6              |
| 8              |
+----------------+
"2021-4-3"  --> We have one transaction with ID 8, so we add 8 to the result table.
"2021-4-28" --> We have two transactions with IDs 5 and 9. The transaction with ID 5 has an amount of 40, while the transaction with ID 9 has an amount of 21. We only include the transaction with ID 5 as it has the maximum amount this day.
"2021-4-29" --> We have two transactions with IDs 1 and 6. Both transactions have the same amount of 58, so we include both in the result table.
We order the result table by transaction_id after collecting these IDs.
SOLUTION:
  • step1) Get each day transaction of max amount
  • step2) Make a join of transaction table with above cte on date and amount

;with highestAmt as (select date(day) as day, max(amount) as amount from Transactions group by date(day)) ,resultCte as (select t.transaction_id from Transactions t inner join highestAmt h on date(t.day) = h.day and t.amount = h.amount) select transaction_id from resultCte order by transaction_id asc

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Banned Account Problem

QUESTION: Table: LogInfo

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| account_id  | int      |
| ip_address  | int      |
| login       | datetime |
| logout      | datetime |
+-------------+----------+
There is no primary key for this table, and it may contain duplicates.
The table contains information about the login and logout dates of Leetflex accounts. It also contains the IP address from which the account logged in and out.
It is guaranteed that the logout time is after the login time.


Write an SQL query to find the account_id of the accounts that should be banned from Leetflex. An account should be banned if it was logged in at some moment from two different IP addresses.

Return the result table in any order.

The query result format is in the following example:



LogInfo table:
+------------+------------+---------------------+---------------------+
| account_id | ip_address | login               | logout              |
+------------+------------+---------------------+---------------------+
| 1          | 1          | 2021-02-01 09:00:00 | 2021-02-01 09:30:00 |
| 1          | 2          | 2021-02-01 08:00:00 | 2021-02-01 11:30:00 |
| 2          | 6          | 2021-02-01 20:30:00 | 2021-02-01 22:00:00 |
| 2          | 7          | 2021-02-02 20:30:00 | 2021-02-02 22:00:00 |
| 3          | 9          | 2021-02-01 16:00:00 | 2021-02-01 16:59:59 |
| 3          | 13         | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
| 4          | 10         | 2021-02-01 16:00:00 | 2021-02-01 17:00:00 |
| 4          | 11         | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
+------------+------------+---------------------+---------------------+

Result table:
+------------+
| account_id |
+------------+
| 1          |
| 4          |
+------------+
Account ID 1 --> The account was active from "2021-02-01 09:00:00" to "2021-02-01 09:30:00" with two different IP addresses (1 and 2). It should be banned.
Account ID 2 --> The account was active from two different addresses (6, 7) but in two different times.
Account ID 3 --> The account was active from two different addresses (9, 13) on the same day but they do not intersect at any moment.
Account ID 4 --> The account was active from "2021-02-01 17:00:00" to "2021-02-01 17:00:00" with two different IP addresses (10 and 11). It should be banned.
SOLUTION:
  • step1) create a self join with condition
    • l.account_id = l2.account_id and l1.ip_address <> l2.ip_address
  • Step2) Filter the data on the basis of
    • l2.login <= l1.logout and l2.login >=l1.login

select l1.account_id from LogInfo l1 inner join LogInfo l2 on l1.account_id = l2.account_id and l1.ip_address <> l2.ip_address where l2.login <= l1.logout and l2.login >=l1.login
Post Reference: Vikram Aristocratic Elfin Share

Friday, December 31, 2021

SQL Query: Biggest Window Between Visits

QUESTION: Table: UserVisits

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
This table does not have a primary key.
This table contains logs of the dates that users vistied a certain retailer.


Assume today's date is '2021-1-1'.

Write an SQL query that will, for each user_id, find out the largest window of days between each visit and the one right after it (or today if you are considering the last visit).

Return the result table ordered by user_id.

The query result format is in the following example:



UserVisits table:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-11-28 |
| 1       | 2020-10-20 |
| 1       | 2020-12-3  |
| 2       | 2020-10-5  |
| 2       | 2020-12-9  |
| 3       | 2020-11-11 |
+---------+------------+
Result table:
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1       | 39            |
| 2       | 65            |
| 3       | 51            |
+---------+---------------+
For the first user, the windows in question are between dates:
    - 2020-10-20 and 2020-11-28 with a total of 39 days. 
    - 2020-11-28 and 2020-12-3 with a total of 5 days. 
    - 2020-12-3 and 2021-1-1 with a total of 29 days.
Making the biggest window the one with 39 days.
For the second user, the windows in question are between dates:
    - 2020-10-5 and 2020-12-9 with a total of 65 days.
    - 2020-12-9 and 2021-1-1 with a total of 23 days.
Making the biggest window the one with 65 days.
For the third user, the only window in question is between dates 2020-11-11 and 2021-1-1 with a total of 51 days.
Solution: 
  • Step1) First Generate the row number over partition by user_id
  • Step2) Then implement lag lead to get the previous date visit
  • Step3) fin difference of latest date with prev date
  • step 4) get the max days diff on the basis of user_id

with UserVisitCTE as (select row_number() over(partition by user_id order by user_id, visit_date ) as rno, * from UserVisits order by user_id, visit_date ), UserVisitLeadCTE as ( select f.user_id, COALESCE(s.visit_date,'2021-01-01'), f.visit_date ,(COALESCE(s.visit_date,'2021-01-01') - f.visit_date) as days_window from UserVisitCTE f left join UserVisitCTE s on f.rno + 1 = s.rno and f.user_id = s.user_id ) --select * from UserVisitCTE select user_id, max(days_window) as biggest_window from UserVisitLeadCTE group by user_id order by user_id
Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Second Degree Follower

QUESTION: In facebook, there is a follow table with two columns: followee, follower.

Please write a sql query to get the amount of each follower’s follower if he/she has one.

For example:

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+
Should output:
+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+
Explanation:
Both B and D exist in the follower list, when as a followee, B's follower is C and D, and D's follower is E. A does not exist in follower list.
Note:
Followee would not follow himself/herself in all cases.
Please display the result in follower's alphabet order.
SOLUTION: 
  • Step1) implement self join on follow table
  • Step2) join follower of left table to the followee of right table using inner join
  • Step3) group by left table follower id and take the count
select f.follower , count(fr.followee) as num from follow f
inner join follow fr 
on f.follower = fr.followee
group by f.follower

Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Exchange Seats Problem

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result for Mary?



+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
For the sample input, the output is:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
Note:

If the number of students is odd, there is no need to change the last one's seat.
Solution:

  • Step1) We get the even id set of student
  • Step2) we get the odd id set of student
  • Step3) get the even id with odd student name
  • step4) get the odd id with even student name
  • step5) union and sort step3 and step4

;with even as 
(select * from seat s1 where s1.id%2 = 0)
,odd as 
(select * from seat s2 where s2.id%2 = 1)
,comb as 
(
select e.id, COALESCE(o.student,e.student) as student 
 from even e left join odd o
	on e.id = o.id +1
union all
select o.id, COALESCE(e.student,o.student) as student 
    from odd o left join even e
		on o.id + 1 = e.id
)

select * from comb order by id asc

Post Reference: Vikram Aristocratic Elfin Share

Tuesday, December 28, 2021

SQL Query: Count Student Number in Departments

A university uses 2 data tables, student and department, to store data about its students and the departments associated with each major.

Write a query to print the respective department name and number of students majoring in each department for all departments in the department table (even ones with no current students).

Sort your results by descending number of students; if two or more departments have the same number of students, then sort those departments alphabetically by department name.

The student is described as follow:

| Column Name  | Type      |
|--------------|-----------|
| student_id   | Integer   |
| student_name | String    |
| gender       | Character |
| dept_id      | Integer   |
 where student_id is the student's ID number, student_name is the student's name, gender is their gender, and dept_id is the department ID associated with their declared major.

And the department table is described as below:

| Column Name | Type    |
|-------------|---------|
| dept_id     | Integer |
| dept_name   | String  |
 where dept_id is the department's ID number and dept_name is the department name.

Here is an example input:
student table:

| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |
department table:

| dept_id | dept_name   |
|---------|-------------|
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |
The Output should be:

| dept_name   | student_number |
|-------------|----------------|
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
Solution:
select d.dept_name, count(s.dept_id) as dept_name from department d 
left join student s
on d.dept_id = s.dept_id
group by d.dept_name
order by count(s.dept_id) desc
Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Winning Candidate

Table: Candidate
 +-----+---------+
 | id  | Name    |
 +-----+---------+
 | 1   | A       |
 | 2   | B       |
 | 3   | C       |
 | 4   | D       |
 | 5   | E       |
 +-----+---------+  
Table: Vote
 +-----+--------------+
 | id  | CandidateId  |
 +-----+--------------+
 | 1   |     2        |
 | 2   |     4        |
 | 3   |     3        |
 | 4   |     2        |
 | 5   |     5        |
 +-----+--------------+
id is the auto-increment primary key,
CandidateId is the id appeared in Candidate table.
Write a sql to find the name of the winning candidate, the above example will return the winner B.

 +------+
 |  Name |
 +------+
 | B    |
 +------+
Notes:

You may assume there is no tie, in other words there will be only one winning candidate.
Solution:


PL/SQL:

select c.Name from Candidate c 
inner join vote v
on c.id = v.CandidateId
group by c.Name
order by count(*) desc
limit 1

TSQL:

select top 1 c.Name  from Candidate c 
inner join vote v
on c.id = v.CandidateId
group by c.Name
order by count(*) desc


Post Reference: Vikram Aristocratic Elfin Share

SQL Query: Rising Temperature Days

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature in a certain day.


Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
Solution:
select distinct w2.id from Weather w1 
inner join Weather w2
on w1.id + 1 = w2.id 
where w1.Temperature < w2.Temperature
Post Reference: Vikram Aristocratic Elfin Share

Sunday, December 26, 2021

LeetcodeSQL: 180. Consecutive Numbers [implementing Lag and Lead]

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.

 

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.
Solution:
select distinct l0.num as ConsecutiveNums
from Logs l0 
inner join Logs l1 on l1.id - 1 = l0.id 
inner join Logs l2 on l1.id + 1 = l2.id
where l0.num = l1.num and l1.num = l2.num
Post Reference: Vikram Aristocratic Elfin Share

Misc SQL: Create monthly Allowance report and Fill Null against those month where emp has not received any allowances

Question: Generate a month wise allowance received report for each employee, keep Null in allowance for those month where employee has not received any allowance.

emp_id emp_name month_number allowance
E001 Aayansh         1         1000
E001 Aayansh         2         3000
E002 Rishika         3         2000
E002 Rishika         5         4000

Output should be :

month_number emp_id emp_name allowance
1 E001 Aayansh 1000
2 E001 Aayansh 3000
3 E001 Aayansh null
4 E001 Aayansh null
5 E001 Aayansh null
6 E001 Aayansh null
7 E001 Aayansh null
8 E001 Aayansh null
9 E001 Aayansh null
10 E001 Aayansh null
11 E001 Aayansh null
12 E001 Aayansh null

Solution: 

Steps:
1) generate a month number derived CTE
2) Cross join MonthCTE with EmpAllowance Table and take the distinct record
3) Left join the CorssJoin result from 2nd step to EmpAllowance table and take allowance from EmpAllowance

Query:

;with MonthCTE AS
(select 1 as month_number 
 union all 
 select month_number + 1 as month_number from MonthCTE 
 where month_number < 12
 ),
ReportTemplateCTE as 
(select DISTINCT m.month_number, e.emp_id, e.emp_name from MonthCTE m cross join EmpAllowance e)
 
select r.*, e.allowance from ReportTemplateCTE r left join EmpAllowance e
on r.month_number = e.month_number and r.emp_id = e.emp_id
order by r.emp_id
 

Post Reference: Vikram Aristocratic Elfin Share

Misc: Generate duplicate rows based on quantity - CTE

Ques: You have below records:

item_name     item_qty
Keyboard          3
Mouse             5

You need to duplicate each records depending upon the value of item_qty values.

Sol:

This can be done using CTE


;With DupCreationCTE(item_name, item_qty, item_counter) as
(
  select item_name, item_qty, 1 as item_counter from DupCreation
  union All
  select item_name, item_qty, item_counter + 1 as item_counter
  from DupCreationCTE where item_counter < item_qty
)
SELECT * from DupCreationCTE order by item_name, item_counter asc


Output:

item_name item_qty item_counter
"Keyboard" 3                 1
"Keyboard" 3                 2
"Keyboard" 3                 3
"Mouse"         5                 1
"Mouse"         5                 2
"Mouse"         5                 3
"Mouse"         5                 4
"Mouse"         5                 5


Post Reference: Vikram Aristocratic Elfin Share

Saturday, September 7, 2019

What is Repeatable Read problem and how to solve it?

Let’s replicate the problem, for which I am creating a table StudentMarks and insert three records
create table studentMarks (
       id int
       ,name varchar(50)
       ,total_marks int
       )
    insert into studentMarks(id,name,total_marks) values( 1,'Prayansh',430);
    insert into studentMarks(id,name,total_marks) values( 2,'Rishika',345);
    insert into studentMarks(id,name,total_marks) values( 3,'Aayansh',390);         

Now lets open a transaction in a new session and try to read this table twice, and in between two read keep a delay of 18 Sec

Open Session1, and execute below transaction
    begin tran
    select * from studentMarks where ID in(1,2)
    waitfor delay '00:00:18'
    select * from studentMarks where ID in (1,2)
    rollback

Parallely open another session and execute below session
update studentMarks
set    total_marks=499 where id=1

Now the output from the first session you will find
 id          name         total_marks
----------- ----------------------
1           Prayansh      430
2           Rishika       345

(2 row(s) affected)

id          name          total_marks
----------- -------------------------
1           Prayansh      499
2           Rishika       345

(2 row(s) affected)

Here if you look at session1 output, a same select statement gives two different outputs, that is because , while session one is waiting for 18 second, session two updated the marks of id=1 to 499.

This is a problem with repeatable read if any outside transaction updates the data in between two reads.

How to avoid it
It can be avoided by setting transaction isolation level to repeatable read, let’s see how

set transaction isolation level repeatable read
    begin tran
    select * from studentMarks where ID in(1,2)
    waitfor delay '00:00:15'
    select * from studentMarks where ID in (1,2)
    rollback

Now here what will happen, if there is any outside transaction tries to modify the data which affect the Transaction one query then, the outside transaction will go on wait for getting update lock until transaction1 complete its operation.



Post Reference: Vikram Aristocratic Elfin Share

Sunday, August 25, 2019

Creating first database in Azure SQL Database

Login to your Azure Account, here below you can see there is no resource… Click on SQL Database service on left vertical panel



Here you can see there is no SQL Database listed, click on Add on right top area



Few item which need to be understood before creating any database

Subscription: All resources in azure subscription are billed together. You need to select your subscription here.

Resource group: A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization.

Resource: A manageable item that is available through Azure. Virtual machines, storage accounts, web apps, databases, and virtual networks are examples of resources.

Elastic Pool: SQL Database Elastic Pool is a shared resource model that enables higher resource utilisation efficiency, with all the databases within an elastic pool sharing predefined resources within the same pool.

Any database in Azure need to be created under a logical server, to create a logical server, click create new under server link and enter the server name and login detail.



Now configure storage and DTU (database throughput unit), here I am keeping it default Basic



The final version of configuration



Now click on Resource Group and select the recently created resource to see all resource which we created just above .. i.e. server and database



Now we are ready with our first ever database in Azure, lets connect it with SSMS client, but before that, we need to set the firewall to allow our client SSMS IP address registered in Azure logical SQL Server

For setting the firewall in newly created logical SQL Server, go to resource group à go to sql database created à select set server firewall



Click Add Clien IP à Give the IP address à Press Save



Once that is done, go to the resource group à. Select the newly created resource à select the newly created server à go to the server properties à copy the server name



Now lets open SSMS at client machine, enter the server detail which was copied from above, put the login name and password which was set in above steps



Now we are able to connect to our first Azure database




Enjy coding…SQL Azure J
Post Reference: Vikram Aristocratic Elfin Share