YouTubehttp://betonarka.info
RSS FeedWed, 19 Dec 2018 12:06:26 MSKruHow to Fetch Latest record/row from the Table in Oracle Databasehttp://betonarka.info/watch/TOCC0BZHFHY/How+to+Fetch+Latest+record+row+from+the+Table+in+Oracle+Database.html
You will learn how to How to Fetch Latest record/row from the Table in Oracle Database.
Realtime Scenario:
This logic can be implemented to find whether the latest record should be inserted or updated to datawarehouseWed, 31 May 2017 09:39:31 MSKStored procedure output parameters or return values Part 20http://betonarka.info/watch/st8RnNg_LLA/Stored+procedure+output+parameters+or+return+values+Part+20.html
In this video we will
1. Understand what are stored procedure return values
2. Difference between stored procedure return values and output parameters
3. When to use output parameters over return values
Text version of the video
http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedure-output-parameters-or.html
Slides
http://csharp-video-tutorials.blogspot.com/2013/08/part-20-stored-procedure-output.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlistsFri, 24 Aug 2012 01:27:15 MSK08 of 13 - Oracle APEX 5 - List of values (LOV) for a field in a form; display a hidden columnhttp://betonarka.info/watch/6sGGrxVvcBQ/08+of+13+Oracle+APEX+5+List+of+values+LOV+for+a+field+in+a+form+display+a+hidden+column.html
A new series using APEX 18.1 is now available.
https://youtu.be/65LExgaQMHI
This is one in a series of videos about creating an application using Oracle APEX 5. In this video we create two lists of values (LOVs) in the Shared Components section of our application. We use each LOV in place of a field in a form. We see how APEX will help us write the SQL for a LOV. The difference between a "display value"and a "return value"is discussed. Finally, we display the primary key field in a form, which APEX hides by default. See the primary key field in a form can be helpful during application development.
SQL scripts for this series are available to download at: http://web.nmsu.edu/~jkreie/oracle/DatabaseTopics.html
or by request at
http://db.kreie.netFri, 10 Jul 2015 01:10:01 MSKHow to get last Inserted Id in vb nethttp://betonarka.info/watch/T7F9BAnqYME/How+to+get+last+Inserted+Id+in+vb+net.html
Fri, 29 Sep 2017 03:32:56 MSKOracle tutorial : sys_refcursor in oracle pl sql (sys_refcursor)http://betonarka.info/watch/riWRJYMaMFg/Oracle+tutorial+sys+refcursor+in+oracle+pl+sql+sys+refcursor.html
Oracle tutorial : sys_refcursor in oracle pl sql
oracle tutorial for beginners
cursor in pl sql
we can use sys_refcursor as OUT parameter.
A cursor is a pointer to a result set for a query.
By returning a sys_refcursor you allow the client to fetch
many rows from the query as it requires.
basically we use sys ref cursor to return set of rows to client.
so client can use this result set.
u can fetch multiple rows.
lets see example..
CREATE OR REPLACE PROCEDURE get_my_list (list OUT SYS_REFCURSOR) AS
BEGIN
OPEN list FOR
SELECT ALTID,
FNAME,
LNAME
FROM EMPLOYEE;
END get_my_list;
/
DECLARE
l_cursor SYS_REFCURSOR;
ID VARCHAR2(20);
FNAME VARCHAR2(20);
LNAME VARCHAR2(20);
BEGIN
get_my_list (list = l_cursor);
LOOP
FETCH l_cursor
INTO ID, FNAME, LNAME;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ID || ' | ' || FNAME || ' | ' || LNAME);
END LOOP;
CLOSE l_cursor;
END;
/
Subscribe on youtube:
https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA
For more tutorial please visit #techquerypond
https://techquerypond.wordpress.com
https://twitter.com/techquerypond
database cursor example database cursor sql server cursorThu, 08 Dec 2016 17:21:12 MSKGet Table Names with Column Names and Data Types in SQL Server - SQL Server Tutorialhttp://betonarka.info/watch/-t0EGkw386Q/Get+Table+Names+with+Column+Names+and+Data+Types+in+SQL+Server+SQL+Server+Tutorial.html
In this video ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process.
Blog link with scripts used in video
http://sqlage.blogspot.com/2015/02/get-all-tables-with-column-names-and.htmlSun, 01 Mar 2015 19:14:48 MSKPart 12 Creating a view to insert data using mvchttp://betonarka.info/watch/qpAe45xTEfQ/Part+12+Creating+a+view+to+insert+data+using+mvc.html
Text version of the video
http://csharp-video-tutorials.blogspot.com/2013/05/part-12-creating-view-to-insert-data.html
Slides
http://csharp-video-tutorials.blogspot.com/2013/09/part-12-creating-view-to-insert-data.html
All ASP .NET MVC Text Articles
http://csharp-video-tutorials.blogspot.com/p/aspnet-mvc-tutorial-for-beginners.html
All ASP .NET MVC Slides
http://csharp-video-tutorials.blogspot.com/p/aspnet-mvc-slides.html
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
In this video we will discuss, creating a view to insert a new employee into the database table tblEmployee. Please watch Part 11, before proceeding with this video.
We want to present the end user with a form to enter data.
Copy and paste the following "Create" action method, in EmployeeController class.
[HttpGet]
public ActionResult Create()
{
return View();
}
Please note that, the method is decorated with "HttpGet" attribute. This makes this action method to respond only to the "GET" request.
Now let's add a "Create" view. To do this, right click on the "Create" action method and select "Add View" from the context menu. Set
1. View name = "Create"
2. View engine = "Razor"
3. Select "Create Strongly-typed view checkbox
4. Select "Employee" class, from "Model class" dropdownlist
5. Scaffold Template = Create
6. Click "Add" button
At this point "Create.cshtml" view will be added in "Employee" folder. If you have the following "Scripts" section at the bottom of the view, please delete it. We will discuss about sections and scripts in a later video session.
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Run the application and navigate to the following URL
"http://localhost/MVCDemo/Employee/Index"
Click on "Create New" link. You will be naviaged to the following URL
"http://localhost/MVCDemo/Employee/Create"
A form with textboxes to add a new employee is rendered. For employee "Gender" it is ideal to have a dropdownlist instead of a text box.
Run the application and notice that, a dropdownlist is now displayed for "Gender".
If you click on "Create" button, you will get an error message stating - The resource cannot be found. This is because we donot have the "Create" controller action method that can handle HTTPPost request. We will discuss fixing this in our next video.Sat, 11 May 2013 01:48:22 MSKTSQL: Get Last Row of Data In A Tablehttp://betonarka.info/watch/LyVilVKwo5k/TSQL+Get+Last+Row+of+Data+In+A+Table.html
Return the final row of data in a tableTue, 24 Sep 2013 15:57:12 MSKLAST VALUE function in SQL Serverhttp://betonarka.info/watch/zulLKbTK_j0/LAST+VALUE+function+in+SQL+Server.html
last_value function in sql server 2008
sql server last_value function returns incorrect data
sql server last_value function example
sql server last_value function with partition example
LAST_VALUE function in SQL Server
In this video we will discuss LAST_VALUE function in SQL Server.
LAST_VALUE function
Introduced in SQL Server 2012
Retrieves the last value from the specified column
ORDER BY clause is required
PARTITION BY clause is optional
ROWS or RANGE clause is optional, but for it to work correctly you may have to explicitly specify a value
Syntax : LAST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...)
LAST_VALUE function not working as expected : In the following example, LAST_VALUE function does not return the name of the highest paid employee. This is because we have not specified an explicit value for ROWS or RANGE clause. As a result it is using it's default value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (ORDER BY Salary) AS LastValue
FROM Employees
LAST_VALUE function working as expected : In the following example, LAST_VALUE function returns the name of the highest paid employee as expected. Notice we have set an explicit value for ROWS or RANGE clause to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
This tells the LAST_VALUE function that it's window starts at the first row and ends at the last row in the result set.
SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue
FROM Employees
LAST_VALUE function example with partitions : In the following example, LAST_VALUE function returns the name of the highest paid employee from the respective partition.
SELECT Name, Gender, Salary,
LAST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue
FROM Employees
Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server.html
Slides
http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server_9.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
Full SQL Server Course
https://www.youtube.com/playlist?list=PL4cyC4G0M1RQ_Rm52cQ4CcOJ_T_HXeMB4
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlistsFri, 09 Oct 2015 23:06:41 MSKORACLE DML ERROR LOGS/ DBMS_ERRLOG / شرح عربيhttp://betonarka.info/watch/8FDKw9ZGrNU/ORACLE+DML+ERROR+LOGS+DBMS+ERRLOG+%D8%B4%D8%B1%D8%AD+%D8%B9%D8%B1%D8%A8%D9%8A.html
ORACLE DML ERROR LOGS/ DBMS_ERRLOG / شرح عربي
-------
select employee_id , first_name||' '||last_name name
from employees
create table emp_c
( employee_id number,
ename varchar2(15)
);
insert into emp_c
select employee_id , first_name||' '||last_name name
from employees
---the solution
exec dbms_errlog.create_error_log('emp_c');
desc err$_emp_c
insert into emp_c
select employee_id , first_name||' '||last_name name
from hr.employees
log errors reject limit unlimited;
select * from err$_emp_cMon, 29 Jan 2018 19:50:42 MSKPart 2 SQL query to get organization hierarchyhttp://betonarka.info/watch/Kd3HTph0Mds/Part+2+SQL+query+to+get+organization+hierarchy.html
Link for all dot net and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists
Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2014/05/part-2-sql-query-to-get-organization.html
To get the best out of this video, the following concepts need to be understood first. These are already discussed in SQL Server Tutorial.
1. Self-Join - http://www.youtube.com/watch?v=qnYSN_7qwgg
2. CTE - http://www.youtube.com/watch?v=ZXB5b-7HJHk
3. Recursive CTE - http://www.youtube.com/watch?v=GGoV0wTMCg0
Here is the problem definition:
1. Employees table contains the following columns
a) EmployeeId,
b) EmployeeName
c) ManagerId
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.
For example,
Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy starting from David.
Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy starting from Lara.
We will be Employees table for this demo. SQL to create and populate Employees table with test data
Create table Employees
(
EmployeeID int primary key identity,
EmployeeName nvarchar(50),
ManagerID int foreign key references Employees(EmployeeID)
)
GO
Insert into Employees values ('John', NULL)
Insert into Employees values ('Mark', NULL)
Insert into Employees values ('Steve', NULL)
Insert into Employees values ('Tom', NULL)
Insert into Employees values ('Lara', NULL)
Insert into Employees values ('Simon', NULL)
Insert into Employees values ('David', NULL)
Insert into Employees values ('Ben', NULL)
Insert into Employees values ('Stacy', NULL)
Insert into Employees values ('Sam', NULL)
GO
Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara')
Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon')
Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom')
Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam')
Update Employees Set ManagerID = 4 Where EmployeeName IN ('David')
GO
Here is the SQL that does the job
Declare @ID int ;
Set @ID = 7;
WITH EmployeeCTE AS
(
Select EmployeeId, EmployeeName, ManagerID
From Employees
Where EmployeeId = @ID
UNION ALL
Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId = EmployeeCTE.ManagerID
)
Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeIdTue, 20 May 2014 01:07:42 MSKSend datatable as parameter to stored procedurehttp://betonarka.info/watch/P3uBWp0qvlo/Send+datatable+as+parameter+to+stored+procedure.html
how to pass datatable to stored procedure in c#.net
pass datatable to stored procedure in sql server 2008
In this video we will discuss how to send datatable as parameter to stored procedure. This is continuation to Part 99. Please watch Part 99 from SQL Server tutorial before proceeding.
In Part 99, we discussed creating a stored procedure that accepts a table as a parameter. In this video we will discuss how to pass a datatable from a web application to the SQL Server stored procedure.
Here is what we want to do.
1. Design a webform to insert 5 employees at a time into the database table.
2. When "Insert Employees" button is clicked, retrieve the from data into a datatabe and then pass the datatable as a parameter to the stored procedure.
3. The stored procedure will then insert all the rows into the Employees table in the database.
WebForm1.aspx.cs code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}
private DataTable GetEmployeeData()
{
DataTable dt = new DataTable();
dt.Columns.Add("Id");
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Rows.Add(txtId1.Text, txtName1.Text, txtGender1.Text);
dt.Rows.Add(txtId2.Text, txtName2.Text, txtGender2.Text);
dt.Rows.Add(txtId3.Text, txtName3.Text, txtGender3.Text);
dt.Rows.Add(txtId4.Text, txtName4.Text, txtGender4.Text);
dt.Rows.Add(txtId5.Text, txtName5.Text, txtGender5.Text);
return dt;
}
protected void btnInsert_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spInsertEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramTVP = new SqlParameter()
{
ParameterName = "@EmpTableType",
Value = GetEmployeeData()
};
cmd.Parameters.Add(paramTVP);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
protected void btnFillDummyData_Click(object sender, EventArgs e)
{
txtId1.Text = "1";
txtId2.Text = "2";
txtId3.Text = "3";
txtId4.Text = "4";
txtId5.Text = "5";
txtName1.Text = "John";
txtName2.Text = "Mike";
txtName3.Text = "Sara";
txtName4.Text = "Pam";
txtName5.Text = "Todd";
txtGender1.Text = "Male";
txtGender2.Text = "Male";
txtGender3.Text = "Female";
txtGender4.Text = "Female";
txtGender5.Text = "Male";
}
}
Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/send-datatable-as-parameter-to-stored.html
Slides
http://csharp-video-tutorials.blogspot.com/2015/09/send-datatable-as-parameter-to-stored_18.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlistsFri, 18 Sep 2015 23:19:27 MSKOracle username and password and Account unlockinghttp://betonarka.info/watch/0TOg7w0aFrA/Oracle+username+and+password+and+Account+unlocking.html
all education purpose videosWed, 24 Apr 2013 21:20:06 MSKSQL script to insert into many to many tablehttp://betonarka.info/watch/cEfjYynwooE/SQL+script+to+insert+into+many+to+many+table.html
Text Article
http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many.html
Slides
http://csharp-video-tutorials.blogspot.com/2017/02/sql-script-to-insert-into-many-to-many_6.html
SQL Server Interview Questions and Answers text articles & slides
http://csharp-video-tutorials.blogspot.com/2014/05/sql-server-interview-questions-and.html
SQL Server Interview Questions and Answers playlist
https://www.youtube.com/playlist?list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
In this video we will discuss how to insert data into a table that has many-to-many relationship
Create table Students
(
Id int primary key identity,
StudentName nvarchar(50)
)
Go
Create table Courses
(
Id int primary key identity,
CourseName nvarchar(50)
)
Go
Create table StudentCourses
(
StudentId int not null foreign key references Students(Id),
CourseId int not null foreign key references Courses(Id)
)
Go
Students - Id column is identity column
Courses - Id column is identity column
StudentCourses - StudentId and CourseId columns are foreign keys referencing Id column in Students and Courses tables
As you can see, StudentCourses is a bridge table that has many to many relationship with Students and Courses tables. This means a given student can be enrolled into many courses and a given course can have many students enrolled.
Below is the question asked in an interview for SQL Server Developer role.
Write a SQL script to insert data into StudentCourses table. Here are the rules that your script should follow.
1. There will be 2 inputs for the script
Student Name - The name of the student who wants to enroll into a course
Course Name - The name of the course the student wants to enroll into
2. If the student is already in the Students table, then use that existing Student Id. If the student is not already in the Students table, then a row for that student must be inserted into the Students table, and use that new student id.
3. Along the same lines, if the course is already in the Courses table, then use that existing Course Id. If the course is not already in the Courses table, then a row for that course must be inserted into the Courses table, and use that new course id.
4. There should be no duplicate student course enrollments, i.e a given student must not be enrolled in the same course twice. For example, Tom must not be enrolled in C# course twice.
Answer : To avoid duplicate student course enrollments create a composite primary key on StudentId and CourseId columns in StudentCourses table. With this composite primary key in place, if someone tries to enroll the same student in the same course again we get violation of primary key constraint error.
Alter table StudentCourses
Add Constraint PK_StudentCourses
Primary Key Clustered (CourseId, StudentId)
Here is the SQL script that inserts data into the 3 tables as expected
Declare @StudentName nvarchar(50) = 'Sam'
Declare @CourseName nvarchar(50) = 'SQL Server'
Declare @StudentId int
Declare @CourseId int
-- If the student already exists, use the existing student ID
Select @StudentId = Id from Students where StudentName = @StudentName
-- If the course already exists, use the existing course ID
Select @CourseId = Id from Courses where CourseName = @CourseName
-- If the student does not exist in the Students table
If (@StudentId is null)
Begin
-- Insert the student
Insert into Students values(@StudentName)
-- Get the Id of the student
Select @StudentId = SCOPE_IDENTITY()
End
-- If the course does not exist in the Courses table
If (@CourseId is null)
Begin
-- Insert the course
Insert into Courses values(@CourseName)
-- Get the Id of the course
Select @CourseId = SCOPE_IDENTITY()
End
-- Insert StudentId & CourseId in StudentCourses table
Insert into StudentCourses values(@StudentId, @CourseId)
If required, we can very easily convert this into a stored procedure as shown below.
Create procedure spInsertIntoStudentCourses
@StudentName nvarchar(50),
@CourseName nvarchar(50)
as
Begin
Declare @StudentId int
Declare @CourseId int
Select @StudentId = Id from Students where StudentName = @StudentName
Select @CourseId = Id from Courses where CourseName = @CourseName
If (@StudentId is null)
Begin
Insert into Students values(@StudentName)
Select @StudentId = SCOPE_IDENTITY()
End
If (@CourseId is null)
Begin
Insert into Courses values(@CourseName)
Select @CourseId = SCOPE_IDENTITY()
End
Insert into StudentCourses values(@StudentId, @CourseId)
End
Use the following statement to execute the stored procedure
Execute spInsertIntoStudentCourses 'Tom','C#'Tue, 07 Feb 2017 01:17:07 MSKSQL tutorial 61: SEQUENCE in Oracle Database By Manish Sharma RebellionRiderhttp://betonarka.info/watch/RrajmYKzlVQ/SQL+tutorial+61+SEQUENCE+in+Oracle+Database+By+Manish+Sharma+RebellionRider.html
This tutorial is all about Sequence In Oracle Database. In this SQL Tutorial you will learn How to create a sequence How to populate a Primary key column (auto increment in Oracle) using Sequence How to drop a sequence
------------------------------------------------------------------------
►►►LINKS◄◄◄
Blog : http://bit.ly/sql-sequence-in-oracle-database-rebellionrider-manish-sharma
Previous Tutorial
► How To Insert Data into Table using SQL Developer
http://youtu.be/YYQCSV6MzTk
► INSERT INTO Command
http://youtu.be/uQXgqFtxI_k
► How to copy /Insert data into a table from another table
http://youtu.be/m3Ep8tAMqok
-------------------------------------------------------------------------
►►►Help Me In Getting A Job◄◄◄
►Help Me In Getting A Good Job By Connecting With Me on My LinkedIn and Endorsing My Skills. All My Contact Info is Down Below. You Can Also Refer Me To Your Company
Thanks
-------------------------------------------------------------------------
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=kb4rc1
--------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the 1st one to see my videos!
--------------------------------------------------------------------------
Amazon Wishlist: http://bit.ly/wishlist-amazon
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on
https://twitter.com/rebellionrider
https://www.facebook.com/imthebhardwaj
http://instagram.com/rebellionrider
https://plus.google.com/+Rebellionrider
http://in.linkedin.com/in/mannbhardwaj/
http://rebellionrider.tumblr.com/
http://www.pinterest.com/rebellionrider/
You can also Email me at
for E-mail address please check About section
Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERSThu, 05 Mar 2015 18:16:40 MSKOracle SELECT Statement and WHERE Clause | Oracle Tutorials for Beginnershttp://betonarka.info/watch/MvI1fYZHO4I/Oracle+SELECT+Statement+and+WHERE+Clause+Oracle+Tutorials+for+Beginners.html
Oracle Tutorial for Beginners
Oracle Tutorial for Beginners
Oracle Database Introduction
Oracle SQL Introduction
How to retrieve data using SELECT statement
SELECT and WHERE Clause in Oracle
SELECT and WHERE Clause in Oracle SQL
SELECT , INSERT ,UPDATE,DELETE IN Oracle
SELECT IN ORACLE
SELECT in oracle
WHERE Clause in Oracle
INSERT in oracle
sql where clause multiple
sql where and
sql where like
having clause in sql
sql where clause multiple conditions
sql where not equal
sql update
sql order byMon, 02 Apr 2018 14:09:20 MSKPart 1 How to find nth highest salary in sqlhttp://betonarka.info/watch/fvPddKyHxpQ/Part+1+How+to+find+nth+highest+salary+in+sql.html
Link for all dot net and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists
Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2014/05/part-1-how-to-find-nth-highest-salary_17.html
This is a very common SQL Server Interview Question. There are several ways of finding the nth highest salary.
By the end of this video, we will be able to answer all the following questions as well.
How to find nth highest salary in SQL Server using a Sub-Query
How to find nth highest salary in SQL Server using a CTE
How to find the 2nd, 3rd or 15th highest salary
Let's use the following Employees table for this demo
Use the following script to create Employees table
Create table Employees
(
ID int primary key identity,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
Salary int
)
GO
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Mark', 'Hastings', 'Male', 60000)
Insert into Employees values ('Steve', 'Pound', 'Male', 45000)
Insert into Employees values ('Ben', 'Hoskins', 'Male', 70000)
Insert into Employees values ('Philip', 'Hastings', 'Male', 45000)
Insert into Employees values ('Mary', 'Lambeth', 'Female', 30000)
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000)
Insert into Employees values ('John', 'Stanmore', 'Male', 80000)
GO
To find the highest salary it is straight forward. We can simply use the Max() function as shown below.
Select Max(Salary) from Employees
To get the second highest salary use a sub query along with Max() function as shown below.
Select Max(Salary) from Employees where Salary [ (Select Max(Salary) from Employees)
To find nth highest salary using Sub-Query
SELECT TOP 1 SALARY
FROM (
SELECT DISTINCT TOP N SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC
) RESULT
ORDER BY SALARY
To find nth highest salary using CTE
WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM EMPLOYEES
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = N
To find 2nd highest salary we can use any of the above queries. Simple replace N with 2.
Similarly, to find 3rd highest salary, simple replace N with 3.
Please Note: On many of the websites, you may have seen that, the following query can be used to get the nth highest salary. The below query will only work if there are no duplicates.
WITH RESULT AS
(
SELECT SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER
FROM EMPLOYEES
)
SELECT SALARY
FROM RESULT
WHERE ROWNUMBER = 3Sun, 18 May 2014 01:19:55 MSKOracle tutorial : Autonomous Transactions in Oracle 11g PL SQL-PRAGMAhttp://betonarka.info/watch/c-gOtJcHI5Q/Oracle+tutorial+Autonomous+Transactions+in+Oracle+11g+PL+SQL+PRAGMA.html
Oracle tutorial : Autonomous Transactions in Oracle 11g PL SQL-PRAGMA
What is pragma autonomous_transaction
An autonomous transaction is an independent transaction to the main or parent transaction. If an Autonomous transaction is started by another transaction it is not nested, but independent of parent transaction.
PRAGMA autonomous_transaction
1)In Oracle session, all of the changes made to data are part of a single
transaction.
2)An autonomous transaction is an independent transaction started within another
transaction (the main transaction).
3)Autonomous transactions allow you to temporarily suspend the main
transaction, perform additional SQL operations, commit or rollback those
operations separately, then resume the main transaction.
4)To define an autonomous transaction, we can use a PRAGMA statement
PRAGMA AUTONOMOUS_TRANSACTION;
CREATE TABLE log_data
( empno NUMBER(6),
userid VARCHAR2(30),
create_date DATE );
CREATE OR REPLACE TRIGGER add_log
BEFORE INSERT OR UPDATE ON employee FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION; — This statement made this autonomous this trigger
BEGIN
INSERT INTO log_data
VALUES (:new.id, USER, SYSDATE);
COMMIT;
END;
/
Insert into EMPLOYEE
(ID, NAME, CITY, SALARY, DEPT_NO)
Values
(20, ‘MY Tech Query’, ‘Mumbai’, 20000, 1)
ROLLBACK;
SELECT * FROM EMPLOYEE
SELECT * FROM LOG_DATA
https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA
For more tutorial please visit #techquerypond
https://techquerypond.com
https://techquerypond.wordpress.com
https://twitter.com/techquerypond
oracle transaction
pragma autonomous_transactionSun, 06 Mar 2016 17:16:22 MSKHow To Get Last Inserted ID Sql Serverhttp://betonarka.info/watch/EM-RKolYCzU/How+To+Get+Last+Inserted+ID+Sql+Server.html
This this tutorial you will learn how get the last inserted in sql server 2008 or laterFri, 28 Apr 2017 10:45:35 MSKDML Processing in an Oracle Database - DBArch Video 8http://betonarka.info/watch/shg1MjT0PuE/DML+Processing+in+an+Oracle+Database+DBArch+Video+8.html
This video explains the steps involved in processing a DML statement in an Oracle Database Server.
Our Upcoming Online Course Schedule is available in the url below
https://docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY/edit#gid=0
Reach us at learn@tisyasolutions.comThu, 08 Jan 2015 12:17:03 MSKPart-3 (Oracle Procedures) Oracle PL SQL Training - Fast Track Serieshttp://betonarka.info/watch/vbFlk-i1gCE/Part+3+Oracle+Procedures+Oracle+PL+SQL+Training+Fast+Track+Series.html
Oracle Procedures
Is a group of PL SQL statement that can call by name.
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS | AS
[declaration_section]
BEGIN
executable_section
[EXCEPTION exception_section]
END [procedure_name];
Example:1 of procedure having Only parameter procedure.
create or replace procedure insert_employee
( p_emp_name varchar2,
p_deptno IN number
)
is
begin
Insert into emp (id,name,deptno) values
(emp_id_seq.nextval, p_emp_name, p_deptno);
commit;
end insert_employee;
/
Prerequisite for the Example:1
1. Need create emp table
create table emp(
id number,
name varchar2(200),
deptno number );
2. Create sequence object.
create sequence emp_id_seq
start with 1
Increment by 1
nomaxvalue
nocycle;
How to call procedure created in Example:1
exec insert_employee('sanket',10);
Or
begin insert_employee('sanket',10); end;
set pagesize 100
set linesize 100
column id format 999
column name format a6
column deptno format 999
select * from emp;
ID NAME DEPTNO
---- ------ ------
1 sanket 10
Example:2 of procedure having In/Out parameter procedure
create or replace procedure insert_employee
( p_emp_name varchar2,
p_deptno IN number,
p_message OUT varchar2
)
is
begin
Insert into emp (id,name,deptno) values
(emp_id_seq.nextval, p_emp_name, p_deptno);
commit;
p_message:= 'one row inserted...';
end insert_employee;
/
How to call procedure created in Example:2
set serveroutput on;
declare
v_message varchar2(100);
begin
insert_employee(‘',20,v_message);
dbms_output.put_line(v_message);
end;
select * from emp;
ID NAME DEPTNO
---- ------ ------
1 sanket 10Sat, 18 Feb 2017 16:50:19 MSKHow to create Virtual Columns in Oracle Databasehttp://betonarka.info/watch/BESXA-k5EJU/How+to+create+Virtual+Columns+in+Oracle+Database.html
How to create Virtual Columns in Oracle Database 12c
When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.
The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;
Querying the table shows the inserted data plus the derived commission-based salaries.
SELECT * FROM employees;
ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOHN DOE 100 5 10 105 110
2 JAYNE DOE 200 10 20 220 240
2 rows selected.
SQL
The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.
COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2)
8 rows selected.
SQL
Notes and restrictions on virtual columns include:
1)Indexes defined against virtual columns are equivalent to function-based indexes.
2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
3)Tables containing virtual columns can still be eligible for result caching.
4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
a)Constraint on the virtual column must be disabled and re-enabled.
b)Indexes on the virtual column must be rebuilt.
c)Materialized views that access the virtual column must be fully refreshed.
d)The result cache must be flushed if cached queries have accessed the virtual column.
e)Table statistics must be regathered.
5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
6)The expression used in the virtual column definition has the following restrictions:
a.It cannot refer to another virtual column by name.
b.It can only refer to columns defined in the same table.
c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.Thu, 20 Jul 2017 22:18:19 MSKRandom number & Random String in Oracle/ عربيhttp://betonarka.info/watch/RSkne46dXww/Random+number+Random+String+in+Oracle+%D8%B9%D8%B1%D8%A8%D9%8A.html
oracle sql
Random number & Random String in Oracle
select dbms_random.random from dual;
select dbms_random.value from dual;
select dbms_random.value(1,10) from dual;
select round(dbms_random.value(1,10) ) from dual;
/*
'u', 'U' - returning string in uppercase alpha characters
'l', 'L' - returning string in lowercase alpha characters
'a', 'A' - returning string in mixed case alpha characters
'x', 'X' - returning string in uppercase alpha-numeric characters
'p', 'P' - returning string in any printable characters.
*/
select dbms_random.STRING('L',15) from dual;
select dbms_random.STRING('U',10) from dual;
select dbms_random.STRING('A',10) from dual;
select dbms_random.STRING('X',20) from dual;
select dbms_random.STRING('P',20) from dual;Mon, 29 May 2017 16:57:55 MSKPart 6 Transform rows into columns in sql serverhttp://betonarka.info/watch/C0mQqDnF7wQ/Part+6+Transform+rows+into+columns+in+sql+server.html
Link for all dot net and sql server video tutorial playlists
http://www.youtube.com/user/kudvenkat/playlists
Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2014/06/part-6-transform-rows-into-columns-in.html
This is another common sql server interview question. We will be using Countries table in this example.
SQL to create the table
Create Table Countries
(
Country nvarchar(50),
City nvarchar(50)
)
GO
Insert into Countries values ('USA','New York')
Insert into Countries values ('USA','Houston')
Insert into Countries values ('USA','Dallas')
Insert into Countries values ('India','Hyderabad')
Insert into Countries values ('India','Bangalore')
Insert into Countries values ('India','New Delhi')
Insert into Countries values ('UK','London')
Insert into Countries values ('UK','Birmingham')
Insert into Countries values ('UK','Manchester')
Here is the interview question.
Write a sql query to transpose rows to columns.
Using PIVOT operator we can very easily transform rows to columns.
Select Country, City1, City2, City3
From
(
Select Country, City,
'City'+
cast(row_number() over(partition by Country order by Country)
as varchar(10)) ColumnSequence
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3)
) PivMon, 09 Jun 2014 00:35:57 MSK