Home
Search results “Oracle insert return count”
How to Fetch Latest record/row from the Table in Oracle Database
 
03:32
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 datawarehouse
SQL Aggregation queries using Group By, Sum, Count and Having
 
10:01
From SQL Queries Joes 2 Pros (Vol2) ch4.1. Learn up to write aggregated queries.
Views: 177604 Joes2Pros SQL Trainings
Row Number function in SQL Server
 
07:24
sql server row_number example sql server row number by partition sql server row_number over partition by order by In this video we will discuss Row_Number function in SQL Server. This is continuation to Part 108. Please watch Part 108 from SQL Server tutorial before proceeding. Row_Number function Introduced in SQL Server 2005 Returns the sequential number of a row starting at 1 ORDER BY clause is required PARTITION BY clause is optional When the data is partitioned, row number is reset to 1 when the partition changes Syntax : ROW_NUMBER() OVER (ORDER BY Col1, Col2) Row_Number function without PARTITION BY : In this example, data is not partitioned, so ROW_NUMBER will provide a consecutive numbering for all the rows in the table based on the order of rows imposed by the ORDER BY clause. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (ORDER BY Gender) AS RowNumber FROM Employees Please note : If ORDER BY clause is not specified you will get the following error The function 'ROW_NUMBER' must have an OVER clause with ORDER BY Row_Number function with PARTITION BY : In this example, data is partitioned by Gender, so ROW_NUMBER will provide a consecutive numbering only for the rows with in a parttion. When the partition changes the row number is reset to 1. SELECT Name, Gender, Salary, ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Gender) AS RowNumber FROM Employees Use case for Row_Number function : Deleting all duplicate rows except one from a sql server table. Discussed in detail in Part 4 of SQL Server Interview Questions and Answers video series. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/rownumber-function-in-sql-server_30.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/playlists
Views: 85483 kudvenkat
Dynamic sql output parameter
 
05:25
Text version of the video http://csharp-video-tutorials.blogspot.com/2017/05/dynamic-sql-output-parameter.html Slides http://csharp-video-tutorials.blogspot.com/2017/05/dynamic-sql-output-parameter_8.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 SQL Server Tutorial Videos https://www.youtube.com/playlist?list=PL08903FB7ACA1C2FB 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 use output parameters with dynamic sql. Let us understand this with an example. SQL 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 ('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 We want to write a dynamic sql statement that returns total number of male of female employees. If the gender value is specified as "Male", then the query should return total male employees. Along the same lines, if the the value for gender is "Female", then we should get total number of female employees. The following dynamic sql, will give us what we want. In this case, the query returns total number of "Male" employees. If you want the total number of female employees, simply set @gender='Female'. Declare @sql nvarchar(max) Declare @gender nvarchar(10) Set @gender = 'Male' Set @sql = 'Select Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10)', @gender At the moment we are not using output parameters. If you want the count of employees to be returned using an OUTPUT parameter, then we have to do a slight modification to the query as shown below. The key here is to use the OUTPUT keyword in your dynamic sql. This is very similar to using OUTPUT parameters with a stored procedure. Declare @sql nvarchar(max) Declare @gender nvarchar(10) Declare @count int Set @gender = 'Male' Set @sql = 'Select @count = Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10), @count int OUTPUT', @gender, @count OUTPUT Select @count The OUTPUT parameter returns NULL, if you forget to use OUTPUT keyword.. The following query returns NULL, as we removed the OUTPUT keyword from @count parameter Declare @sql nvarchar(max) Declare @gender nvarchar(10) Declare @count int Set @gender = 'Male' Set @sql = 'Select @count = Count(*) from Employees where [email protected]' Execute sp_executesql @sql, N'@gender nvarchar(10), @count int OUTPUT', @gender, @count Select @count
Views: 16651 kudvenkat
SSIS Tutorial Part 123-Get Delete/Update Record Count From Execute SQL Task & Write to File
 
19:43
SSIS Video Scenario: We want to create an SSIS Package in which we will be using a Stored Procedure, which will delete and update some records and return us the delete and update record count. Once the Stored Procedure is completed, we want to log the record count to a file file for our reference. We will append the record to flat file. In this video we will learn how to returned Number of records deleted or updated by a Stored Procedure that we called in Execute SQL Task. You will be able to learn following items from this video demo How to Create SSIS Package from Scratch How to create variables to hold the values from Execute SQL Task How to Create Stored Procedure that will return Delete and Update Record Count How to Map Store Procedure output to variables in SSIS Package How to Write Variable values to Flat File Blog post link for the video with scripts http://sqlage.blogspot.com/2015/04/how-to-return-deleted-and-update-record.html Check out step by Step SQL Server Integration Services(SSIS) Tutorial by TechBrothers http://www.techbrothersit.com/2014/12/ssis-videos.html
Views: 5080 TechBrothersIT
PL/SQL tutorial 65: PL/SQL Collection Method EXTEND in Oracle Database
 
08:49
RebellionRider presents the PL/SQL Collection Method Extend in Oracle Database with Example by Manish Sharma. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/col-method-extend ► PL/SQL Collection Method EXTEND https://youtu.be/QLP9xq5l_oA ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the 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 SUBSCRIBERS
Views: 5598 Manish Sharma
SQL Null or Empty   How to Check for Null or Empty Column in SQL Server   SQL Training Online
 
03:41
http://www.sqltrainingonline.com SQL Null or Empty - How to Check for Null or Empty Column in SQL Server - SQL Training Online You can download the sample table script at http://www.sqltrainingonline.com/how-to-filter-for-sql-null-or-empty-string/. In this video, I show you how to filter for a SQL null or empty string. It can be difficult to filter a null because you can't simply compare with an equal sign. You have to use a special IS NULL clause to make the filter work. I also show you how to combine both the SQL Null and Empty String together in a special trick with the isnull SQL Server function. I use SQL Server 2012 to demonstrate the technique. Let me know what you think by commenting or sharing on twitter, facebook, google+, etc. If you enjoy the video, please give it a like, comment, or subscribe to my channel. You can visit me at any of the following: SQL Training Online: http://www.sqltrainingonline.com Twitter: http://www.twitter.com/sql_by_joey Google+: https://plus.google.com/#100925239624117719658/posts LinkedIn: http://www.linkedin.com/in/joeyblue Facebook: http://www.facebook.com/sqltrainingonline
Views: 18483 Joey Blue
PL/SQL tutorial 73: Bulk Collect with SELECT-INTO statement in Oracle Database
 
07:26
RebellionRider.com presents a PL/SQL tutorial on how to use the BULK COLLECT clause with SELECT INTO statement in Oracle Database by Manish Sharma. In this tutorial, you will also learn how to overcome the overhead caused by context switch and improve the query performance. ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/bulk-collect-2 Previous Tutorial ►Introduction to Bulk Collect: https://youtu.be/CrJSlf_m3yA ►PL/SQL Tutorial 51-Nested Table: https://youtu.be/EUYyiFCzU0I ►Collection Method Count:https://youtu.be/NNq80CcC5K8 ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the 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 SUBSCRIBERS
Views: 11969 Manish Sharma
Stored procedure output parameters or return values   Part 20
 
16:45
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/playlists
Views: 285416 kudvenkat
Oracle ADD_MONTHS Function
 
03:15
https://www.databasestar.com/oracle-add_months/ The Oracle ADD_MONTHS allows you to add a specified number of months to a date value, and return the new date value. It’s great for performing calculations on dates, or date arithmetic, which can be hard in many different programming languages. The syntax of the ADD_MONTHS function is: ADD_MONTHS ( input_date, number_months ) The function returns a DATE value. The parameters of this function are: - input_date (mandatory): This is the starting date, or the date that you will add a number of months to. - number_months (mandatory): This is an integer value that represents the number of months to add to input_date. The input_date can be any datetime value, or even a character value that can be converted to a date. Also, the number_months can be any number value, or a character value that can be converted to a number. What if the number of days in the two months are different? For example, adding a month to Jan 31 could result in Feb 31, which does not exist. Well, in this case, Oracle would work out that the date does not exist, and use the last date of the month (e.g. Feb 28). You can also provide a negative value for ADD_MONTHS to subtract months from the specified date. For more information about the Oracle ADD_MONTHS function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-add_months/
Views: 473 Database Star
PL/SQL tutorial 59: PL/SQL Collection Method COUNT in Oracle Database by Manish Sharma
 
04:53
RebellionRider presents PL/SQL tutorial explaining the concepts of Collection Method COUNT in Oracle Database. This tutorial will help you in learning the basics of COUNT function. This video will also help you in learning – What is collection method Count in Oracle Database • How to use COUNT function • How to use COUNT function with IF Condition • How to use COUNT function with For-Loop Hope you will enjoy. Do make sure to Like, Share & Subscribe. Thanks Tweet Link: https://twitter.com/OracleSQLDev/status/872441483223281664 Follow me on Twitter: https://twitter.com/RebellionRider ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/count-function Previous Tutorial ► Introduction to collection methods https://youtu.be/8V40IaOkGcU ► Tut 51. How to create nested table https://youtu.be/EUYyiFCzU0I ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the 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 SUBSCRIBERS
Views: 8295 Manish Sharma
PL/SQL tutorial 81: PL/SQL Execute Immediate with INTO clause in Oracle Database
 
08:51
Learn what is an Execute Immediate statement and how to use dynamic SQL using Execute Immediate. In this video, you will also see an example of Execute Immediate with INTO clause by Manish Sharma ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/NDS-2 learn What Is A Data Warehouse: Definition and Benefits http://bit.ly/the-data-warehouse Previous Tutorial ► Introduction to Dynamic SQL https://youtu.be/Yzk1zENbhg4 ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the 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 SUBSCRIBERS
Views: 6103 Manish Sharma
SQL: Group By,  Having, Min, Max Sum
 
14:43
Learn to summarize yoru data using the SQL Aggregates such as count and sum and the GROUP BY and HAVING clauses of the SELECT statement
Views: 153658 Michael Fudge
Stored Procedure - Copy value from one table to another
 
02:28
Stored Procedure - Copy value from one table to another using program variables as temporary place holders. DB2 UDB, Oracle, SQL Server, MySQL
Views: 3293 Professor Saad
oracle mysql select max and return multiple values
 
06:25
Code and details: http://blog.softhints.com/oracle-mysql-select-max-and-return-multiple-values/ another example on max N per group http://blog.softhints.com/mysql-select-n-max-values-per-group/ Mysql group by column and return N max/min rows per group: * get max per group * get minimum per group * use additional constraints * return N per group --------------------------------------------------------------------------------------------------------------------------------------------------------------- If you really find this channel useful and enjoy the content, you're welcome to support me and this channel with a small donation via PayPal and Bitcoin. PayPal donation https://www.paypal.me/fantasyan Bitcoin: 1DBZu6N9JTpRDdc9QChLZnX3v2iVRaQ4ym Programming is a fun! :) Site: www.softhints.com Facebook: www.facebook.com/Softhints/ Twitter: www.twitter.com/SoftwareHints
Views: 35 Softhints
Part 6   Transform rows into columns in sql server
 
08:53
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) ) Piv
Views: 199330 kudvenkat
Part 8   SQL Query to find department with highest number of employees
 
04:03
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-8-sql-query-to-find-department.html Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC Scenario asked in the SQL Server Interview Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series. SQL query that retrieves the department name with maximum number of employees SELECT TOP 1 DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID GROUP BY DepartmentName ORDER BY COUNT(*) DESC
Views: 125832 kudvenkat
ORACLE SQL INTERVIEW QUESTION : HOW TO SELECT ODD OR EVEN ROWS FROM A TABLE IN ORACLE
 
03:52
This video tutorial answers the frequently asked interview question, how to select only odd or even records in oracle sql, after watching this video you will understand more on the topic and will be able to answer this interview question with a lot of confidence.
Views: 4522 Kishan Mashru
return last insert id using stored procedure scope_identity c#4.6
 
18:09
#storedprocedure #returnidentityinsert #asp.nettutorials get last inserted row id after insert new record into database using stored procedure scope_identity() c# asp.net
C# SQL Database Tutorial 3: Getting the number of rows in Local database in real time
 
09:17
c# - How to count total number of rows of an access database table How to count the number of rows from sql table in c# c# - DataTable Row count always returning Getting the number of rows in an SQL database in a C# app C# Data Table Rows.Count always returns at least one row Searches related to count rows in database c# php count database rows android database count rows c# sqldatareader count rows c# mysql count rows c# datatable count rows count rows in database sql c# count rows in file codeigniter database count rows
Views: 65881 ProgrammingKnowledge
Oracle Retrieve data from XML Document using XMLTable
 
17:49
Oracle retrieve data from xml document using xmltable
Views: 6568 Sridhar Raghavan
Oracle Decode and case statement inside insert and insert all statement
 
09:20
Oracle SQL PLSQL and Unix Shell Scripting
Views: 731 Sridhar Raghavan
Part 1   How to find nth highest salary in sql
 
11:45
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 = 3
Views: 944142 kudvenkat
Oracle : Stored Procedure with Input and Output Parameters
 
04:04
Java Source Code here: http://ramj2ee.blogspot.com/2015/07/oracle-stored-procedure-with-input-and.html Oracle : Stored Procedure with Input and Output Parameters JavaEE Tutorials and Sample code - Click here : http://ramj2ee.blogspot.in/
Views: 36475 Ram N
Formatting your Oracle Query Results Directly to CSV
 
03:02
How to get Oracle SQL Developer to automatically format your query results to CSV, HTML, JSON, Insert statements, and more.
Views: 45926 Jeff Smith
SSIS Tutorial Part 39-How to Use Row Count Transformation to Log Record Count in SSIS Package
 
11:46
In this video you will learn how to get the insert row count and log into audit table for SSIS Package. This is real time scenario that we use to log different type of row counts such as insert, update, delete to log table for each SSIS Package execution. The video also covers How to create an SSIS Package How to use Data Flow Task in SSIS Package How to create Audit Table for SSIS Package Execution How to use Flat file Source to read text file in SSIS Package How to load data to a table in SSIS Package How to create variables in SSIS Package How to store Insert row count in a variable in SSIS Package How to use Execute SQL Task in SSIS Package How to Map parameters for Insert statement in Execute SQL Task in SSIS Package How to use Data Flow Task with Source and Destination in SSIS Package Check out our Full Step by Step SQL Server Integration Services(SSIS) Tutorial http://www.techbrothersit.com/2014/12/ssis-videos.html
Views: 22075 TechBrothersIT
Part 2   SQL query to get organization hierarchy
 
10:14
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.EmployeeId
Views: 293119 kudvenkat
Calling a stored procedure with output parameters   Part 7
 
20:13
Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/calling-stored-procedure-with-output.html Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-7-calling-stored-procedure-with.html All ADO .NET Text Articles http://csharp-video-tutorials.blogspot.com/p/free-adonet-video-tutorial.html All ADO .NET Slides http://csharp-video-tutorials.blogspot.com/p/adonet-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 learn about calling a stored procedure with output parameters from an asp.net web application.
Views: 192544 kudvenkat
SQL Server - Retrieve Multiple Rows as a Single Record
 
02:53
A short T-SQL tutorial to show how to join two tables for selecting mutiple rows on a single record. The contents of this video applies to Microsoft® SQL Server®
Views: 16927 Emiliano Musso
GLOBAL TEMPORARY TABLE (GTT) IN ORACLE SQL WITH EXAMPLES
 
13:33
This video demonstrates the usage and working of GTT in oracle SQL. The video shows how internally oracle works with GTT, and also explains the working of ON COMMIT DELETE ROWS and ON COMMIT PRESERVER ROWS in oracle with easy examples.
Views: 9817 Kishan Mashru
LAST VALUE function in SQL Server
 
05:35
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/playlists
Views: 34672 kudvenkat
Java & MySQL - Check if a value already exists
 
12:03
Easy tutorial on how to check if a value already exists in the database.
Views: 28899 Mozoran
What is High Water Mark(HVM) in oracle and how it effects the performance of queries?
 
05:41
This video tutorial is to understand the concept of a High Water Mark or HVM of a oracle table, also it states the various changes in the HVM when we perform a insert, delete and Truncate operation on the table, and the video also informs why is the truncate operation more advisable and optimized then the delete operation.
Views: 3760 Kishan Mashru
Part 5   SQL query to find employees hired in last n months
 
04:53
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-5-sql-query-to-find-employees.html This question is asked is many sql server interviews. If you have used DATEDIFF() sql server function then you already know the answer. -- Replace N with number of months Select * FROM Employees Where DATEDIFF(MONTH, HireDate, GETDATE()) Between 1 and N
Views: 179084 kudvenkat
Php : How To Search And Filter Data In Html Table Using Php And MySQL Database [ with source code ]
 
08:32
How To Find Data In MySQL Database And Display It In Html Table Using Php Source Code: http://1bestcsharp.blogspot.com/2015/10/php-html-table-search-filter-data-mysql-database.html PHP Programming Tutorials ➜ http://bit.ly/2Lh7zPM PHP And MySQL Tutorials ➜ http://bit.ly/2kdXWot PHP Course For Beginners ➜ http://bit.ly/2GxRodb Web Development Course ➜ http://bit.ly/2ITRQrj visit our blog https://1bestcsharp.blogspot.com/ facebook: https://www.facebook.com/1BestCsharp twitter: https://www.twitter.com/1BestCsharp_ subscribe: http://goo.gl/nRjPKk Share This Video: https://youtu.be/2XuxFi85GTw In this Php Tutorial You will see How You Can Make Search Input Text And Show Data With Input Text Value In Html Table Using MySQL Database And Php . I Use In This Tutorial: - NetBeans IDE . - XAMPP . - PhpMyAdmin . Others Tutorials : Get Select Option Value From MySQL Database Using Php https://www.youtube.com/watch?v=V8sIWh_sdvs How To Populate Html Table From MySQL Database Using Php https://www.youtube.com/watch?v=6goqMwURH9Q How To Use Mysqli Num Rows In Php https://www.youtube.com/watch?v=12L6Sckk8cc How To Use Pdo Row Count In Php https://www.youtube.com/watch?v=F3tqnbDTP3k Insert Update Delete Search Data In MySQL Database Using Php Part1 https://www.youtube.com/watch?v=f85jvD_Y8Ck Check Out This: Top Programming Courses On Udemy http://1bestcsharp.blogspot.com/2016/11/top-udemy-programming-courses.html
Views: 283117 1BestCsharp blog
How to retrieve data from database and Display in JSP Page?
 
08:16
In this video tutorial you will learn How to retrieve data from database and Display in JSP Page? Get the source code from http://www.roseindia.net/jsp/how-to-retrieve-data-from-database-in-jsp.shtml
Views: 69498 roseindiatutorials
SQL Server - Query Table Record Data via TSQL - SELECT COUNT
 
03:06
How to select count(*) of table records. This tutorial covers how to select count(*) of entire table or how to select count(*) of a subset of data via using a where clause filter.
Views: 1067 Dave Merton
jQuery datatables stored procedure for paging sorting and searching
 
14:36
Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/jquery-datatables-stored-procedure-for.html In this video we will discuss implementing a stored procedure that can perform paging sorting and searching. In our upcoming videos we will discuss writing a generic handler that calls this stored procedure. The generic handler will convert the data to JSON format. The JSON formatted data can then be consumed by the jQuery datatables plugin. All the processing, that is, paging, sorting and searching is done on the server side. If the dataset is small you can retrieve all the data at once and all the processing (i.e paging, searching, sorting) can be done on the client-side. We discussed this in Part 106 for jQuery tutorial. However, if the dataset is large (i.e if you have millions of records), loading all data at once is not the best thing to do from a performance standpoint. With large dataset, we only want to load the correct set of rows (sorted, paged and filtered data ) that want to display on the page. So all the processing ((i.e paging, searching and sorting) should be done by the server. So in this and our upcoming videos we will discuss performing server side processing. SQL Script to create the table and populate it with test data Create table tblEmployees ( ID int primary key identity, FirstName nvarchar(50), LastName nvarchar(50), Gender nvarchar(20), JobTitle nvarchar(20) ) Go Insert into tblEmployees values('Mark', 'Hastings','Male','Developer') Insert into tblEmployees values('Maria', 'Nicholas','Female','Developer') Insert into tblEmployees values('Robert', 'Stephenson','Male','Sr. Developer') Insert into tblEmployees values('Mary', 'Quant','Female','Sr. Developer') Insert into tblEmployees values('John', 'Stenson','Male','Sr. Developer') Insert into tblEmployees values('Gilbert', 'Sullivan','Male','Developer') Insert into tblEmployees values('Rob', 'Gerald','Male','Sr. Developer') Insert into tblEmployees values('Ron', 'Simpson','Male','Developer') Insert into tblEmployees values('Sara', 'Solomon','Female','Sr. Developer') Insert into tblEmployees values('Rad', 'Wicht','Male','Sr. Developer') Insert into tblEmployees values('Julian', 'John','Male','Developer') Insert into tblEmployees values('James', 'Bynes','Male','Sr. Developer') Insert into tblEmployees values('Mary', 'Ward','Female','Developer') Insert into tblEmployees values('Michael', 'Niron','Male','Sr. Developer') SQL Server stored procedure paging sorting and searching create proc spGetEmployees @DisplayLength int, @DisplayStart int, @SortCol int, @SortDir nvarchar(10), @Search nvarchar(255) = NULL as begin Declare @FirstRec int, @LastRec int Set @FirstRec = @DisplayStart; Set @LastRec = @DisplayStart + @DisplayLength; With CTE_Employees as ( Select ROW_NUMBER() over (order by case when (@SortCol = 0 and @SortDir='asc') then Id end asc, case when (@SortCol = 0 and @SortDir='desc') then Id end desc, case when (@SortCol = 1 and @SortDir='asc') then FirstName end asc, case when (@SortCol = 1 and @SortDir='desc') then FirstName end desc, case when (@SortCol = 2 and @SortDir='asc') then LastName end asc, case when (@SortCol = 2 and @SortDir='desc') then LastName end desc, case when (@SortCol = 3 and @SortDir='asc') then Gender end asc, case when (@SortCol = 3 and @SortDir='desc') then Gender end desc, case when (@SortCol = 4 and @SortDir='asc') then JobTitle end asc, case when (@SortCol = 4 and @SortDir='desc') then JobTitle end desc ) as RowNum, COUNT(*) over() as TotalCount, Id, FirstName, LastName, Gender, JobTitle from tblEmployees where (@Search IS NULL Or Id like '%' + @Search + '%' Or FirstName like '%' + @Search + '%' Or LastName like '%' + @Search + '%' Or Gender like '%' + @Search + '%' Or JobTitle like '%' + @Search + '%') ) Select * from CTE_Employees where RowNum ] @FirstRec and RowNum [= @LastRec end Finally test the stored procedure
Views: 41418 kudvenkat
PL/SQL tutorial 53: How to create nested tables using user define datatype
 
07:13
RebellionRIder.com Presents PL/SQL tutorial 53 on How to create nested tables using user define datatype by Manish Sharma ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog : http://bit.ly/nested-table-3 Previous Tutorial ► Nested Table as Database Object : https://youtu.be/P_8iRaM_T5Q ------------------------------------------------------------------------- ►Make sure you SUBSCRIBE and be the 1st one to see my videos! ------------------------------------------------------------------------- ►►►Find me on Social Media◄◄◄ Follow What I am up to as it happens on https://twitter.com/rebellionrider http://instagram.com/rebellionrider https://plus.google.com/+Rebellionrider http://in.linkedin.com/in/mannbhardwaj/ ___Facebook Official Page of Manish Sharma___ https://www.facebook.com/TheRebellionRider/ ___Facebook Official Page of RebellionRider.com___ https://www.facebook.com/RebellionRider.official/ You can also Email me at for E-mail address please check the 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 SUBSCRIBERS
Views: 14915 Manish Sharma
Get Table Names with Column Names and Data Types in SQL Server - SQL Server Tutorial
 
05:47
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.html
Views: 49536 TechBrothersIT
Tutorial#53  Count and Sum  Aggregate Function in Oracle SQL Database| Group by Function in SQL
 
09:01
Explaining How to get Count and Sum Value in Oracle Database in others words what is the aggregate function in Oracle or what are the types of aggregate function in SQL An Aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurements such as a set, a bag or a list or Aggregate Function in Oracle SQL Database or Aggregate Function in SQL or How to use Count and sum Aggregate Function in Oracle or Types of Aggregate function in SQL Assignment: Assignment link will be available soon: SQL basics, create table oracle, SQL functions, SQL queries, SQL server, SQL developer installation, Oracle database installation, SQL Statement, OCA, Data Types, Types of data types, SQL Logical Operator, SQL Function,Join- Inner Join, Outer join, right outer join, left outer join, full outer join, self-join, cross join, View, SubQuery, Set Operator. Follow me on: Facebook Page: https://www.facebook.com/LrnWthr-319371861902642/?ref=bookmarks Contacts Email: [email protected] Instagram: https://www.instagram.com/equalconnect/ Twitter: https://twitter.com/LrnWthR
Views: 27 EqualConnect Coach
Basic aggregate functions in Oracle plsql.
 
19:22
Understanding basic aggregate functions in pl sql. A simple pl sql tutorial which demonstrate how to use basic aggregate functions like sum, avg, count, max and min.
Views: 355 Subhroneel Ganguly
Send datatable as parameter to stored procedure
 
09:27
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/playlists
Views: 57767 kudvenkat
SQL String Functions PART  -- 2
 
21:01
sql String functions are used in computer programming languages to manipulate a string or query information about a string (some do both). ... The most basic example of a string function is the length(string) function. This function returns the length of a string literal.Scalar-valued UDFs are an easy way to define constant values to use in your database environment. You can pass field values as parameters into UDFs. You can nest scalar function calls. This means that you can pass a call to a scalar-valued function to another function or stored procedure.Numeric functions perform manipulation of numbers that normally are contained in a table column. ... Returns a numeric value, rounded to the specified length or precision. SIGN() SQL server, MySQL, Oracle, PostgreSQL. Returns the positive (+1), zero (0), or negative (-1) sign of a numeric expression.A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . ... Functions can be called from Procedure whereas Procedures cannot be called from Function,The CTE was introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable. ... Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.The isDate(string, string) function accepts two string arguments. It takes them, compares the first argument with the second as a pattern and, if the first string can be converted to a date which is valid within system value of locale , according to the specified pattern , the function returns true.printf does the same, treating %s as a pointer-to-string. In C, variables that are arrays become a pointer to the first element of the array when used as function arguments -- so your scanf() sees a pointer to memory (assuming "str1" is an array). SQL - Useful Functions SQL COUNT Function - The SQL COUNT aggregate function is used to count the number of rows in a database table. SQL MAX Function - The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column. More items... SQL has many built-in functions for performing processing on string or numeric data. Following is the list of all useful SQL built-in functions − SQL COUNT Function - The SQL COUNT aggregate function is used to count the number of rows in a database table. SQL MAX Function - The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column. SQL MIN Function - The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column. SQL - The SQL AVG aggregate function selects the average value for certain table column. SQL SUM Function - The SQL SUM aggregate function allows selecting the total for a numeric column. SQL SQRT Functions - This is used to generate a square root of a given number. SQL RAND Function - This is used to generate a random number using SQL command. SQL CONCAT Function - This is used to concatenate any string inside any SQL command. SQL Numeric Functions - Complete list of SQL functions required to manipulate numbers in SQL. SQL String Functions - Complete list of SQL functions required to manipulate strings in SQL. Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. REPLACE. Replaces a sequence of characters in a string with another set of characters. REPLICATE. Repeats a string value a specified number of times.SQL. Character Functions with Examples. Character functions accept character inputs and can return either characters or number values as output. SQL provides a number of different character datatypes which includes – CHAR, VARCHAR, VARCHAR2, LONG, RAW, and LONG RAW.Aggregate Functions: Aggregates the values and return a single value, below is the list of some aggregate values in sql server. These functions are created by user in the system database, and we have 3 types of user define functions: Scalar Function. Inline Table-Valued Function.
Views: 52 Technology mart
Sql query to select all names that start with a given letter without like operator
 
03:02
Text Article http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that.html Slides http://csharp-video-tutorials.blogspot.com/2017/01/sql-query-to-select-all-names-that_31.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 writing a SQL query to retrieve all student names that start with letter 'M' without using the LIKE operator. If the interviewer has not mentioned not to use LIKE operator, we would have written the query using the LIKE operator as shown below. SELECT * FROM Students WHERE Name LIKE 'M%' We can use any one of the following 3 SQL Server functions, to achieve exactly the same thing. CHARINDEX LEFT SUBSTRING The following 3 queries retrieve all student rows whose Name starts with letter 'M'. Notice none of the queries are using the LIKE operator. SELECT * FROM Students WHERE CHARINDEX('M',Name) = 1 SELECT * FROM Students WHERE LEFT(Name, 1) = 'M' SELECT * FROM Students WHERE SUBSTRING(Name, 1, 1) = 'M'
Views: 63383 kudvenkat
Best Way to Write Basic SQL Queries
 
13:33
SQL Server Query writing strategies is something I have yet to find in any book. When enthusiastic SQL students do this, they experience a revelation. The number of errors drops significantly and the speed at writing complex queries increases immediately. Knowing how to narrow down what we are looking for amongst a vast list of choices helps immensely. Grabbing the right tables first and then the fields second is akin to grabbing the right menu before ordering an item from it. In fact, one student named Tim took this back to his team of SQL developers and they immediately implemented this process. We are all used to following steps. Most of the time, actions are sequential from top to bottom or left to right. Other times we complete things in phases. The two phases we are going to use in this exercise apply to joining tables. This is easy to implement as we only need remember to organize first and clean up second. When visiting a new restaurant, we will ask to see the menu, because we want to see all they have to offer. The odds are that we might be interested in half of the items, but only need a few dishes for our group at the table. Looking at the menu is like starting a query with a ‘SELECT *’ statement. Once we have looked at all the fields, we narrow our choice(s) to only the items we want at the time. Sometimes restaurants have multiple menus. My favorite restaurant has a kids’ menu, an adult menu, a gluten-free menu and a drink menu. These menus were each gathered at our table. Ultimately, in my head, a selection was narrowed to what was needed. Phase I: Organize. When building a new query from many tables, we often find ourselves wondering, “Where do I start?” First, lay the steps out by identifying which tables contain the essential data. Second, get all the table joins working with a basic ‘SELECT *’ statement. Third, add any basic filtering criteria. Phase II: Itemize. Once all joins and criteria, such as SELECT, FROM and WHERE are working, we are ready for Phase II. This entails going back and changing our ‘SELECT *’ to an itemized SELECT field list as the final step. Let’s explore how this two-phase process of Organize and then Itemize can be a big time-saver. We are going to use one of the challenges from the last lab. In Lab 3.2 (Outer Joins), Skill Check 2, we needed to get four fields from two different tables. If we were to list all four desired fields and test one table at time, we will get an error as seen on the right side of the figure below. In the figure below we write a SELECT statement and part of the FROM clause. When completed, the FROM clause will have two tables, but for now we just want to get the Location table working. By using the ‘SELECT *’ strategy, we remove any possible errors from line 1. From there, we can focus on the more complicated logic used for joining tables together. We can add tables one at a time until everything is working. This is the Organize phase. NOTE: SELECT * never results in an Error message stating “invalid column name”, however; a SELECT list with itemized field(s) can have this error. After our query is organized and working, we can go back and itemize the SELECT field list to display only the fields that are necessary. This is done during Phase II (Itemize). The steps for this system are broken down as follows: Since SELECT is always the first statement in a query, it’s natural to want to write the field names before writing the FROM clause. However; we can save time and trouble by using the ‘*’ until the entire query is working properly. When this is complete, it is very easy to itemize the field list, with the confidence of knowing it will not cause any problems. Exercise: Simple membership for a club: Table1 (id_Member,name_Member, email_member, title, age, company) Table2 (id_Club,club_name, club_Location, address) Table3 (id_club, id_member,membership_date) Q1: Show all member names, email, age, and company from Table1 Q2: Show all club name, location, and address data Q3: Show member name, club name, club location (hint: Use joins and all 3 tables) You can find all other classes related to this video here: http://www.joes2pros.com/joes2pros/Course/Introduction_To_Microsoft_SQL_Server Enroll Today & Get the First Month for only $1! Use code: YOUTUBE1
Views: 242996 Joes2Pros SQL Trainings
JAVA - How To Search Values From MySQL Database And Set It Into JTextfield In Java NetBeans | + Code
 
07:14
JAVA - How To Search And Set Values From MySQL Database Into Textfield In Java NetBeans Source Code:http://1bestcsharp.blogspot.com/2015/02/java-how-to-search-and-set-values-from.html ------------------ Check out my Java Projects! -------------------- ▶ Inventory Management System - http://bit.ly/Java_Inventory ▶ Students Information System - http://bit.ly/JavaStudent_System ▶ Contacts Management System - http://bit.ly/Java_Contacts ▶ All My Programming Projects Here - http://bit.ly/2HrU8hK ▶ My Source Code Store Here - http://bit.ly/2OsC0TU -------------------------- JAVA COURSE ----------------------------- ▶ master Java core development step-by-step - http://bit.ly/2HXSuAn -------------------------------------------------------------------- java - How to display data from database into JTextfield visit our blog https://1bestcsharp.blogspot.com/ facebook: https://www.facebook.com/1BestCsharp twitter: https://www.twitter.com/1BestCsharp_ subscribe: http://goo.gl/nRjPKk *************************************************************************** *************************************************************************** C# And Java Programming Projects Source Code: http://1bestcsharp.blogspot.com/p/programming-projects.html *************************************************************************** *************************************************************************** in this java tutorial we will see how to get data into jtextfield in netbeans from mysql database we are gonna use: java programming language MySQL Database NetBeans Ide now to retrieve data from mysql database into jtextfield in netbeans we have firs to connect java to MySQL and to do this watch this video: https://www.youtube.com/watch?v=zM7oe2_S-jY and follow this java video thanks for watching this java tutorial where we Fetch data from database into Jtextfield in Netbeans for more java tutorials subscribe Java - How To Create Login Form With MySQL Database [with source code] Part 1 : https://www.youtube.com/watch?v=7sNYhlBz9xo Part 2 : https://www.youtube.com/watch?v=meGhTnlS9k4
Views: 91680 1BestCsharp blog
Oracle APEX -  Calculation value in APEX form
 
02:12
RETURN(:P1_VALUE1+:P1_VALUE2*:P1_VALUE3);
Views: 2805 Oracle Apex
SQL script to insert into many to many table
 
14:52
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#'
Views: 89641 kudvenkat
How to get input from user in oracle database
 
02:36
How to get input from user on run time in oracle database of Sql Plus.
Views: 359 latest ITChannel