Home
Search results “Table row count in oracle”
Oracle Rowid, RowNum and row_number
 
06:44
Oracle Rowid, RowNum and row_number... Explained in detailed...
Views: 5413 CONNECT TO LEARN
Oracle sql Interview Question : How to change rows to column
 
09:18
This video tutorial talks about the frequently asked oracle sql interview question of changing rows to the column. Based on the given scenario, we are asked to change the presentation of data in a table with 3 rows and 2 column to 3 columns and 2 rows. Decode Function in oracle https://youtu.be/YUAjPMjqMws Pivot and unpivot in oracle https://youtu.be/6s0DI1YcWIM
Views: 12361 Kishan Mashru
Table Shrinking in Oracle Database
 
14:15
1.Shrink the Table: Shrinking is started from 10g. In this method I’m using user u1 and table name sm1. Now I’m deleting some rows in sm1 COUNT ---------- 1048576 Table sm1 has 1048576 rows. [email protected]: delete from sm1 where deptno=10; 262144 rows deleted. I deleted above number of rows. Rows COUNT ---------- 786432 And I’m giving commit [email protected]: commit; Commit complete. So now we have 786432 rows in sm1 table. Now see the following command [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 After I deleted some rows in sm1 table still above result showing same values, so now our duty is shrink this table. This is done by following 2 ways, i By using COMPACT key word: In this method shrinking is done in two phases. In the first phase all fragmented space are just defragmented, but still the High Water Mark is persist with last used block only. That mean used free blocks are not de allocated and HWM is not updated here. Issue the following command before use shrink command. [email protected] alter table sm1 enable row movement; Table altered. There is particular use with above command, when we shrink the table all rows are moves to contiguous blocks, so here row movement should be done. By default the row movement is disabled for any table, so above command enabled the row movement. Then execute shrink command now. [email protected]: alter table sm1 shrink space compact; Table altered. Now see the space of table by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ------ ---------- ---------- U1 U1TS SM1 TABLE 29 mb 3712 44 So here seems nothing happened with above shrink command, but internally the fragmented space is defragmented. But the high water mark is not updated, used free blocks are also not de allocated. For de allocating the used blocks we have to execute below command. This is the second phase. [email protected]: alter table sm1 shrink space; Table altered. Now see the space by using below command. [email protected]: select OWNER,TABLESPACE_NAME,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024||' mb'"space",BLOCKS,EXTENTS from dba_segments where tablespace_name like 'U%TS'; OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE space BLOCKS EXTENTS ----- --------------- ------------- ------------- ---------- ---------- ---------- U1 U1TS SM1 TABLE 20.8125 mb 2664 36 So now the space of sm1 table is reduced. Note: Actually the alter table sm1 shrink space command will complete these two phases of the shrinking of table at a time. But here we done shrink process in two phases because when we use alter table sm1 shrink space command the table locked temporarily some time period, during this period users unable to access the table. So if we use alter table sm1 shrink space compact command the table is not locked but space is defragmented. When we not in business hours issue the second phase shrink command then users are won’t get any problem. ii Because of above method the table dependent objects are goes to invalid state, to overcome this problem we have to use below command. [email protected]: alter table sm1 shrink space cascade; Table altered. The above command also shrinks the space of all dependent objects. We also do this in two phases like above two phases. See the below command. [email protected]: alter table sm1 shrink space compact cascade; Table altered. And then [email protected]: alter table sm1 shrink space cascade; Table altered. Transporting tablespace to different platform by Using RMAN : https://www.youtube.com/watch?v=CN401PUKK4A Oracle EBS apps Upgrade from 12 2 to 12 2 5 (start CD 51) : https://www.youtube.com/watch?v=zeO4goqR70Y Transport tablespace by using RMAN.: https://www.youtube.com/watch?v=YG6kWX7Par8
Views: 6481 BhagyaRaj Katta
SQL Table Row Count
 
01:32
This tutorial video will walk you through how to count how many rows there are in every table in a database. You can also sort by schema. It is all done by looking at the sys tables. Github Script: https://github.com/ProfessorPitch/ProfessorPitch/blob/master/SQL/Table%20Rowcount.sql
Views: 21 ProfessorPitch
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
How to return multiple values from a function in oracle pl/sql ? (without using out parameter)
 
14:19
This video demonstrates how a function can return multiple values to the calling environment, but without using multiple out parameters, the video shows an example on how we can create a object, a nested table based on the object and then returning the nested table type from the function.
Views: 16969 Kishan Mashru
SQL with Oracle 10g XE - Using DELETE to Remove a Row from a Table
 
03:17
In this video I use the DELETE command to remove an entire row from a table. When using the DELETE command be sure to use the keywords FROM and WHERE to choose the table and record you wish to delete. The code I used is: DELETE FROM BOOKS WHERE BOOK_ID = '11'; This video is part of a series of videos with the purpose of learning the SQL language. For more information visit Lecture Snippets at http://lecturesnippets.com.
Views: 8947 Lecture Snippets
Oracle SQL Tutorial 14 - Column-Level and Table-Level Constraints
 
04:15
In the previous video we talked about adding constraints at the column-level. We made it nice and simple by only requiring a few keywords, but the problem we were having is that we could not assign a name to the constraint, which many people like to do so we can reference easily if we need to at a later time. To do this, it requires a little bit more typing, but it will give us extra flexibility and many consider it to be the higher quality approach to adding constraints. Let's go though a simple example. Let's say we have a users table with a user_id column that we want to make a primary key. We will create the table like this: CREATE TABLE( user_ id NUMBER PRIMARY KEY ) Instead of adding the PRIMARY KEY keywords after the data type, we add: CONSTRAINT user_pk PRIMARY KEY Now, we have assigned the name user_pk to this constraint. You can do the same with other constraints, such as UNIQUE. The syntax would be CONSTRAINT username_un UNIQUE. The other way to create constraints requires to put all of our constraints at the bottom of our table creation rather than inline with the column. This type of constraint is known as a table-level constraints. To make a column a primary key using table-level constraints, we add it to the CREATE TABLE command as if it is another row and use the CONSTRAINT keyword to tell Oracle that what is coming is a constraint, not a column in our table. CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR), CONSTRAINT username_un UNIQUE (username), CONSTRAINT users_pk PRIMARY KEY (user_id) ) The primary differences here is that you have to put the column you are talking about in parenthesis after the PRIMARY KEY keyword. That's because it's at the end of the table and you need a way to tell it what column you are talking about. The option of putting it at the end of the table has the added benefit in this situation because if we needed to have a primary key that is the combination of multiple columns, we can do that by just adding the other column in the PRIMARY KEY parenthesis right after a comma. In summary, there are three ways to make constraints. The first is at the column level, unnamed. The second is at the column level, named. The third is at the table level, also named. In the next video we are going to create a named constraint in Oracle SQL Developer, so stay tuned and be sure to subscribe! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 13097 Caleb Curry
Analytical Functions in oracle explained with real examples
 
25:24
This video explains analytical functions and how they are implemented in real projects. Analytical functions are somewhat similar to aggregate functions,but they offer much more. Why use analytical function ? They allow you to write fast and concise queries which otherwise will involve self join and long processing times They allow you to perform aggregate functions independently on sets of partitions. You can access values from previous rows in current row and you can restrict the window on which you want to apply this analytical function. I have given additional practice exercises along with the dataset so that you can comfortably work with analytical functions. You can find the sample problems along with dataset in the below link. http://www.internshipsfromhome.com/oracle-analytical-functions-ddl-and-dml/
Views: 13865 Tech Coach
Oracle Partition By clause, Oracle Analytic Functions tutorial
 
09:09
More about analytic functions, visit http://www.learn-with-video-tutorials.com Oracle Analytic Functions and Over By Partition clause enable calculating cumulative and moving aggregates returning multiple rows for each group.
Oracle SQL Developer Tutorial For Beginners   85   Find the Total Number of Rows
 
00:52
Oracle SQL Developer Tutorial For Beginners Series. This course introduces Oracle SQL Development for its subscribers. Currently this is based on Oracle 12c. The test environment is in Windows 10.
Views: 562 Sam Dhanasekaran
Oracle DENSE_RANK Function
 
04:09
https://www.databasestar.com/oracle-dense_rank/ The Oracle DENSE_RANK function allows you to calculate a rank of a row in a group of rows. It returns this rank as a NUMBER value. This function can be used as either an analytic or an aggregate function, and the syntax depends on how you use it. As an aggregate function it looks like this: DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] ) These parameters are: - expr: this is the value to search for in a particular table. You can specify multiple search values, and just separate them by a comma. - order_expr: this is used to order the expr values by, because you get a different results depending on what order you rank the values in. - NULLS FIRST or LAST: this is where you can specify if you want to have NULL values appear at the top of your ranking order or at the bottom. As an analytic function, it looks like this: DENSE_RANK() OVER ( [query_partition_clause] order_by_clause) The parameters are: - query_partition_clause: this is the expression that the ranking is “grouped” by, as analytic functions allow you to group data within rows. - order_by_clause: this is the expression to order your results by to determine a rank. For this function, if two records have the same sort or rank position, they will have the same RANK value. It won’t cause a gap in the rankings, unlike the RANK function which does cause a gap. For more information about the Oracle DENSE_RANK function, including all of the SQL shown in this video and the examples, read the related article here: https://www.databasestar.com/oracle-dense_rank/
Views: 204 Database Star
HOW TO IDENTIFY AND DELETE DUPLICATE ROWS USING ROWID AND GROUPBY IN ORACLE SQL
 
07:53
This video demonstrates examples on how to find and delete duplicate records from a table. The video gives simple and easy to understand examples on finding duplicate records from a table using group by and having clause and row_number function. It also shows the ways in which duplicates can be deleted very efficiently using the rowid of that record. You can get the code from our website http://oracleplsqlblog.com/FullBlog/FullBlog/21
Views: 8201 Kishan Mashru
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: 179575 kudvenkat
Oracle SQL Tutorial 18 - How to Create Foreign Keys
 
07:09
In this video we are going to be creating foreign keys. I highly recommend watching the previous video before you watch this one. Essentially, we are creating a very simple database for a system where we can create projects and add people to those projects. We started with the users table: --Delete the table if needed: --DROP TABLE users; CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR) UNIQUE, CONSTRAINT users_pk PRIMARY KEY (user_id) ); Now we are going to create a table for projects with a column that is a foreign key to the username. We're going to want to make this match data types: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) ) Next, we need to add the column attributes we decided on last video: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL ) NOT NULL because we want every project to have a creator, but we are not labeling UNIQUE because that means we could only have a specific username once in the table. We want to allow a user to create multiple projects. We also need to add a primary key: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (username) ) Now, the question that remains is, how can I tell Oracle that I want the username to reference the username column of the other table? We have to make a foreign key constraint. As you've learned from the previous videos, there are about three ways to create constraints. You can do it inline at the column level, unnamed. You can do it at the column level, named, and you can do it at the Table level, named. Usually the table-level is preferred, but I will quickly remind you how to do all three again. CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL REFERENCES users (username), CONSTRAINT projects_pk PRIMARY KEY (project_id) ) This works, but if we want to name it, we should do this: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL CONSTRAINT projects_users_fk REFERENCES users (username), CONSTRAINT projects_pk PRIMARY KEY (project_id) ) This works, but the preferred method is to do it at the table level: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ) Great! So you've learned how to create a foreign key, now we can see it inside of Oracle SQL Developer. One important thing when it comes to foreign keys is what happens when have data in your database and you try to delete the parent row that a row in the child table references? In the next video we are going to configure that using ON DELETE. See you all then and if you enjoy this series, please do me a huge favor by liking the video and subscribing to my YouTube channel. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 18752 Caleb Curry
SQL query to find first record and last record in single query
 
08:55
SQL query to find first record and last record in single query
Views: 7530 SQL Guru
PIVOT Tutorial - generating dynamic column with PIVOT Interview Question SQL
 
14:14
In this lecture you will learn about PIVOT operator and using it to generate dynamic columns.PIVOT is very use full operator to convert values into columns. PIVOT: •Convert your output from (values)vertical order to horizontal(making columns) one Topic to cover: •PIVOT example •PIVOT syntax •Interview Question •PIVOT on northwind •Dynamic PIVOT query http://www.techsapphire.in/index/pivot_sql_lesson_with_generating_dynamic_columns_with_pivot/0-158
Views: 27232 techsapphire
How to Delete the Duplicate Records in oracle
 
03:44
http://oracletemple.blogspot.in/ How to Delete the duplicate records How delete the duplicate rows ? Tips: - Always take extra caution while deleting records. 1. First identify the duplicates using select. 2. Double verify those are actual ‘duplicates’ or not 3. Take backup if necessary 4. Apply commit only if you are sure. -~-~~-~~~-~~-~- Please watch: "difference between delete and truncate" https://www.youtube.com/watch?v=7b6wQ3Qumgg -~-~~-~~~-~~-~- ------------------------------------------------------------------ Below is link for Useful Pl/SQL Books http://goo.gl/XMy0tt ---------------------------------------------------------- Watch More... What is Parameterized Cursor in Oracle https://www.youtube.com/watch?v=JMKwHlVi6-A What is autonomous transaction in Oracle. https://www.youtube.com/watch?v=gyvFajpfoWE What are Constraints available in Oracle. https://www.youtube.com/watch?v=WivhdLXQklQ What is Mutation Error and How to avoid mutation. https://www.youtube.com/watch?v=CbWNCyW18Bs 5 Effective way to delete Duplicate rows https://www.youtube.com/watch?v=1wtTtnc87Oc What is Difference between Delete and Truncate https://www.youtube.com/watch?v=7b6wQ3Qumgg
Views: 21458 Ram Gupta
Create  a Table,inserting record & find out count of records  in Oracle Database 10g using SQL Plus
 
05:27
How to Create a Table,inserting record & find out count of records in Oracle Database 10g using SQL Plus
Views: 34 Technical Aashique
SQL, Oracle, Dual table, 3e62
 
07:37
Understanding Dual table. Most SQL videos are available on www.my-uni.net also. Feel free to visit
Views: 2706 hammadshams
SQL Partition By Explained
 
06:42
Using Oracle's SQL, I'll explain how to use Partition By. This will be similar in other SQL engines that have the Partition By keyword.
Views: 25161 Pretty Printed
SQL: pseudo columns (Rowid/Rownum)
 
05:54
In this tutorial, u'll learn the difference between rowid & rownum..
Views: 29798 radhikaravikumar
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: 162393 Joes2Pros SQL Trainings
Oracle Database | Bangla Tutorials 17 :: Sql  Insert  Statement (Row insert in table)
 
06:35
www.facebook.com/oracle.shohag Email : [email protected] Website : www.oraclebangla.com Topics : Sql Insert Statement
Views: 1258 Oracle Bangla
Oracle SQL Tutorial 19 - ON DELETE (SET NULL and CASCADE)
 
05:59
Welcome everyone! Something you need to consider when you are creating foreign keys is what happens if you delete the parent? As a reminder, the parent is the row that has the value you are referencing in the row that has a foreign key. Why is this something important to consider? It's important because foreign keys need to protect us from two primary things, unacceptable INSERT statements, and unacceptable DELETE statements. Let's see what happens when we try to insert incorrect data into the table with the foreign key: INSERT INTO projects VALUES (1, 'Update website homepage', 'CalebCurry') The response tells us plainly that there is no such user in the users table. So this works correctly. Deleting data on the other hand works a bit differently because the database does not know what you want to do with the child row when you delete the parent from the parent table. By default, we will get an error message that prevents the parent from being deleted, but there are some other options. How do we configure this? This is where the ON DELETE statement comes in. We add the keywords ON DELETE right after the foreign key and then we can give it the option of CASCADE or SET NULL. CASCADE means that if we delete the parent, we are also going to delete the child. In our situation what that means is that if somebody creates a project in our project table and then that persons account gets deleted, all of the projects he owns will also be deleted. CASCADE: CREATE TABLE projects( project_id NUMBER, Project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ON DELETE CASCADE ) SET NULL will take the value in the child table and get rid of it. What you are left with is NULL. This means that we have an orphaned child. The first thought you might have is that it is a bad thing to have an orphaned child, but in databases that is not always so. In our application if we had it set to SET NULL, when a user account gets deleted the projects would remain in existence they would just lack a creator. This might be a good thing if you are concerned about the long term survival of a project, this might be the route you want to go. It ultimately depends on the application purpose. If you don't like CASCADE or SET NULL, you can leave the entire ON DELETE statement and just have Oracle throw an error when a parent is deleted. As for us, we are going to use ON DELETE CASCADE. We need to use this with extreme caution. If you are not careful, someday you will run a delete a row and that will cascade through you database deleting a bunch of stuff you didn't want to delete. Stuff happens, so make sure you back up your database every once in eternity. Now, in the last video we started with a database design that had three tables. We've only created two in this video. In the next video we are going to create the next one, which is a little special. Then we'll finish things up by adding some indexes. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me on Patreon! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 13156 Caleb Curry
SQL with Oracle 10g XE - Using SELECT to View the Entire Table
 
02:36
In this video I use the SELECT command to view the data of a table. When viewing the entire table be sure to choose the appropriate number of records to display from the drop-down menu and then enter the command. To view the Books table I used the following: SELECT * FROM BOOKS; This video is part of a series of videos with the purpose of learning the SQL language. For more information visit Lecture Snippets at http://lecturesnippets.com.
Views: 16027 Lecture Snippets
Difference between rank dense rank and row number in SQL
 
04:43
row_number vs rank vs dense_rank in sql server dense_rank vs rank vs row_number sql server rank dense_rank row_number in sql server In this video we will discuss the similarities and difference between RANK, DENSE_RANK and ROW_NUMBER functions in SQL Server. Similarities between RANK, DENSE_RANK and ROW_NUMBER functions Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties) ORDER BY clause is required PARTITION BY clause is optional When the data is partitioned, the integer value is reset to 1 when the partition changes SQL Script to create the Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 6000) Insert Into Employees Values (2, 'John', 'Male', 8000) Insert Into Employees Values (3, 'Pam', 'Female', 4000) Insert Into Employees Values (4, 'Sara', 'Female', 5000) Insert Into Employees Values (5, 'Todd', 'Male', 3000) Notice that no two employees in the table have the same salary. So all the 3 functions RANK, DENSE_RANK and ROW_NUMBER produce the same increasing integer value when ordered by Salary column. SELECT Name, Salary, Gender, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber, RANK() OVER (ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees You will only see the difference when there ties. Now let's include duplicate values for Salary column. To do this First delete existing data from the Employees table DELETE FROM Employees Insert new rows with duplicate valuse for Salary column Insert Into Employees Values (1, 'Mark', 'Male', 8000) Insert Into Employees Values (2, 'John', 'Male', 8000) Insert Into Employees Values (3, 'Pam', 'Female', 8000) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 3500) Notice 3 employees have the same salary 8000. When you execute the following query you can clearly see the difference between RANK, DENSE_RANK and ROW_NUMBER functions. SELECT Name, Salary, Gender, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber, RANK() OVER (ORDER BY Salary DESC) AS [Rank], DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees Difference between RANK, DENSE_RANK and ROW_NUMBER functions ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates. RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates. DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rank-denserank-and.html Slides http://csharp-video-tutorials.blogspot.com/2015/10/difference-between-rank-denserank-and_2.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: 38446 kudvenkat
SQL Tutorial - 13: Inserting Data Into a Table From Another Table
 
07:00
In this tutorial we'll learn to use the INSERT Query to copy data from one table into another.
Views: 234648 The Bad Tutorials
SQL Error ORA-00942 Table or View Does Not Exist in Oracle Database
 
05:03
How to use Tab View (Data Dictionary) of Oracle Database to solve the SQL Error ORA-00942 Table or View Does Not Exist error in Oracle Database ------------------------------------------------------------------------ ►►►LINKS◄◄◄ Blog: http://bit.ly/ora-00942 Previous Tutorial ► ------------------------------------------------------------------------- ►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: 5403 Manish Sharma
SQL Server interview question :- Explain RowNumber,Partition,Rank and DenseRank ?
 
10:50
For more such videos visit http://www.questpond.com See our other Step by Step video series below :- Learn SQL Server Step by Step in 16 hours:- http://tinyurl.com/ja4zmwu Learn Angular tutorial for beginners https://tinyurl.com/ycd9j895 Learn MVC Core step by step :- http://tinyurl.com/y9jt3wkv Learn MVC 5 Step by Step in 16 hours:- https://goo.gl/dmdakg Learn MSBI Step by Step in 32 hours:- https://goo.gl/TTpFZN Learn Xamarin Mobile Programming Step by Step :- https://goo.gl/WDVFuy Learn Design Pattern Step by Step in 8 hours:- https://goo.gl/eJdn0m Learn C# Step by Step in 100 hours :- https://goo.gl/FNlqn3 Learn Data structures & algorithm in 8 hours :-https://tinyurl.com/ybx29c5s Learn Javascript in 2 hours :- http://tinyurl.com/zkljbdl Learn SharePoint Step by Step in 8 hours:- https://goo.gl/XQKHeP Learn TypeScript in 45 Minutes :- https://goo.gl/oRkawI Learn webpack in 50 minutes:- https://goo.gl/ab7VJi Learn Visual Studio code in 10 steps for beginners:- https://tinyurl.com/lwgv8r8 Learn Tableau step by step :- https://tinyurl.com/kh6ojyo SQL Server interview question :- Explain RowNumber,Partition,Rank and DenseRank ? We are also distributing a 100 page Ebook "Sql Server Interview Questions". If you want this ebook please share this video in your facebook/twitter/linkedin account and email us on [email protected] with the shared link and we will email you the PDF.
How to use the Oracle SQL PIVOT Clause
 
11:46
PIVOT allows you to "flip a table on its side", i.e. Columns to Rows / Rows to Columns. Previously we did this with DECODE or CASE. As of Oracle 11g, Oracle Database includes the PIVOT clause (and UNPIVOT). Oracle expert Geoff Wiland from SkillBuilders will demonstrate PIVOT, UNPIVOT, including the use of aggregate functions.
Views: 9711 SkillBuilders
ORACLE SQL- 3.insert row into table
 
08:35
we will insert a row into the table and how to modify it and all will be shown here
Views: 145 Ban3 GaiN
Single Row Conversion Functions Querying a table in Oracle SQL
 
13:26
This video shows how to Create Single Row Conversion Functions in Oracle SQL. Try to share these videos for poor students and subscribe this channel for more upcoming other technical videos.
Views: 21 Technology mart
SQL Tutorial - 12: Inserting Data Into Tables
 
10:10
In this tutorial we'll learn to insert data into tables using the INSERT INTO statement.
Views: 153461 The Bad Tutorials
Oracle tutorial : How to create Id with AUTO INCREMENT in Oracle PL SQL
 
08:43
Oracle tutorial: How to create Id with AUTO INCREMENT in Oracle oracle tutorial for beginners sequence in oracle identity key in sql In this Oracle tutorial , we can create an auto increment field using ‘sequence’ object that can be assigned as primary keys. Using Oracle ‘sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. Examples CREATE SEQUENCE SYSTEM.MYSEQ START WITH 1 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER; CREATE OR REPLACE TRIGGER TR_CITY BEFORE INSERT ON CITY FOR EACH ROW BEGIN SELECT LPAD(LTRIM(RTRIM(TO_CHAR(myseq.NEXTVAL))),10,'0') INTO :NEW.id FROM DUAL; END; / Subscribe on youtube: https://www.youtube.com/channel/UCpiyAesWNYOXSz5GPq8lbkA For more tutorial please visit #techquerypond https://techquerypond.wordpress.com https://twitter.com/techquerypond
Views: 8494 Tech Query Pond
SQL Server - Row Number Function (tr)
 
03:46
SQL Server - Row Number Function
Over clause in SQL Server
 
09:13
over partition by in sql server 2008 sql server over clause partition partition by clause in sql server 2008 over partition by clause in sql In this video we will discuss the power and use of Over clause in SQL Server. The OVER clause combined with PARTITION BY is used to break up data into partitions. Syntax : function (...) OVER (PARTITION BY col1, Col2, ...) The specified function operates for each partition. For example : COUNT(Gender) OVER (PARTITION BY Gender) will partition the data by GENDER i.e there will 2 partitions (Male and Female) and then the COUNT() function is applied over each partition. Any of the following functions can be used. Please note this is not the complete list. COUNT(), AVG(), SUM(), MIN(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK() etc. Example : SQl Script to create Employees table Create Table Employees ( Id int primary key, Name nvarchar(50), Gender nvarchar(10), Salary int ) Go Insert Into Employees Values (1, 'Mark', 'Male', 5000) Insert Into Employees Values (2, 'John', 'Male', 4500) Insert Into Employees Values (3, 'Pam', 'Female', 5500) Insert Into Employees Values (4, 'Sara', 'Female', 4000) Insert Into Employees Values (5, 'Todd', 'Male', 3500) Insert Into Employees Values (6, 'Mary', 'Female', 5000) Insert Into Employees Values (7, 'Ben', 'Male', 6500) Insert Into Employees Values (8, 'Jodi', 'Female', 7000) Insert Into Employees Values (9, 'Tom', 'Male', 5500) Insert Into Employees Values (10, 'Ron', 'Male', 5000) Go Write a query to retrieve total count of employees by Gender. Also in the result we want Average, Minimum and Maximum salary by Gender. This can be very easily achieved using a simple GROUP BY query as show below. SELECT Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender What if we want non-aggregated values (like employee Name and Salary) in result set along with aggregated values You cannot include non-aggregated columns in the GROUP BY query. SELECT Name, Salary, Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender The above query will result in the following error : Column 'Employees.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause One way to achieve this is by including the aggregations in a subquery and then JOINING it with the main query as shown in the example below. Look at the amount of T-SQL code we have to write. SELECT Name, Salary, Employees.Gender, Genders.GenderTotals, Genders.AvgSal, Genders.MinSal, Genders.MaxSal FROM Employees INNER JOIN (SELECT Gender, COUNT(*) AS GenderTotals, AVG(Salary) AS AvgSal, MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal FROM Employees GROUP BY Gender) AS Genders ON Genders.Gender = Employees.Gender Better way of doing this is by using the OVER clause combined with PARTITION BY SELECT Name, Salary, Gender, COUNT(Gender) OVER(PARTITION BY Gender) AS GenderTotals, AVG(Salary) OVER(PARTITION BY Gender) AS AvgSal, MIN(Salary) OVER(PARTITION BY Gender) AS MinSal, MAX(Salary) OVER(PARTITION BY Gender) AS MaxSal FROM Employees Text version of the video http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2015/09/over-clause-in-sql-server_29.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: 79916 kudvenkat
55. ALTER TABLE to Modify existing column in PL/SQL Oracle
 
08:00
In this video you will learn about ALTER TABLE to Modify existing column in PL/SQL Oracle. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 3474 ITORIAN
SQL Inner Join Multiple Tables with SUM Tutorial - SQL 2008/2012/2016/2017
 
18:03
Use inner joins with multiple tables to create reports. Uses aliases, and the SUM aggregate function to demonstrate the flexibility of the join statement. Get the SQL script to create the database and run the exercies in the video here: https://www.qewbi.com/sql/scripts/qewbijoins.zip
Views: 130072 Edward Kench
how to perform group function in  table(Oracle Apex 12c -Run SQL Commands)
 
02:19
the group functions are sum,avg,max,min,count and distinct syntax sum: select sum(columnname) from tablename Avg : select avg(columnname) from tablename max :select max(columnname) from tablename min :select min(columnname) from tablename count:select count(columnname) from tablename distinct:select distinct(columnname) from tablename
Views: 45 SB TUTORIALS
HOW TO RETURN MULTIPLE VALUES FROM A PROCEDURE IN ORACLE PL/SQL? (USING OUT PARAMETERS)
 
04:46
This video tutorial explains how an oracle procedure can return multiple values with a proper example. The video tutorial shows how this problem can be tacked using simple and easy to understand out parameters!!!
Views: 11289 Kishan Mashru
SQL with Oracle 10g XE - Using ALTER TABLE to Modify Table Columns
 
05:23
In this video I use the ALTER TABLE command to modify an existing field column. The command will allow you to change the data types, whether the field can be null, or even the primary key. When using the ALTER TABLE command you would use the keyword MODIFY to make changes to an existing column. Be careful when changing a data type of the null field as existing data may cause an error if not in compliance with the new change. The code I used to alter the Books table is : ALTER TABLE BOOKS MODIFY ISBN_10 VARCHAR(13); This video is part of a series of videos with the purpose of learning the SQL language. For more information visit Lecture Snippets at http://lecturesnippets.com.
Views: 15366 Lecture Snippets
Pivot Table and its advanced properties in OBIEE 11g and 12C
 
08:22
This video shows you advanced properties of pivot table in obiee 11g and obiee 12c
17. RENAME Table and DROP Table in Oracle
 
03:06
In this video I will show you how to change the name of existing table and also how to drop the table. Dropping table can delete your all records exist in that database table. For Support =========== Email: [email protected] Contact Form: http://www.learninhindi.com/home/contact Our Social Media ================ Facebook: https://www.facebook.com/LearnInHindi Twitter: https://twitter.com/LearnInHindi For Training & Videos ===================== For more videos and articles visit: http://www.learninhindi.com Free Java Programming In Hindi Course ===================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAwwhMyoLISrxkXTADGp7PH Free Oracle PL/SQL Programming In Hindi Course ============================================== https://www.youtube.com/playlist?list=PLB5DA82419C2D99B6 Free C Programming In Hindi Course ================================== https://www.youtube.com/playlist?list=PLOZ3jentlCDAxKpBLMWogxSdy6BZcsAJq Trips & Tricks Channel ====================== https://www.youtube.com/channel/UCGmLfkuCo-3lHHJXRJ9HUMw Programming in Hindi Channel ============================ https://www.youtube.com/channel/UCudElIDgwNrybeAvXIy1HZQ
Views: 30731 ITORIAN
Oracle SQL Tutorial 13 - How to Add Column Constraints (Attributes)
 
05:55
So far, we have this table declaration: CREATE TABLE users( user_id NUMBER, username VARCHAR(50 CHAR) first_name VARCHAR2(50 CHAR), last_name VARCHAR2(50 CHAR) ) We can run this command see that it works. As we are learning though, we are going to want to be able to recreate our table with different settings and such, but if you try to run this command, it will complain that the table already exists. So we first need to delete this table before we start editing settings. When we get more experienced, we will learn about ways to edit the structure of a table that already exists. To fix this, we can get rid of the table using the DROP TABLE command: DROP TABLE users You can run this every time if you need to practice by adding a semicolon after it. This is how you can tell Oracle that you are putting in another command after it. This is known as a delimiter. When you run the script, it is going to run both commands. Now we can go through and reconsider our table structure. This is fine for starting out because we don't have any important data in our database, but once your database is in production you are not going to want to just drop tables. In the last video we discussed different constraints that you can apply to columns in a database table, but how do you actually apply these when you are creating a table? The first way you define constraints is to put them right after the column in your CREATE TABLE statement. CREATE TABLE users( username VARCHAR2(50 CHAR) NOT NULL ) When we define constraints this way, we usually say we are adding column attributes. If you have two constraints you want to add, you just put one after the other with spaces in between. There is no particular order that is required. CREATE TABLE users( username VARCHAR2(50 CHAR) NOT NULL UNIQUE ) In this situation we have already given the column the NOT NULL and UNIQUE attributes, so we should consider making this a primary key: CREATE TABLE users( username VARCHAR2(50 CHAR) PRIMARY KEY ) As you can see, adding column attributes is super easy. We can add a default like this: CREATE TABLE users( username VARCHAR2(50 CHAR) PRIMARY KEY, account_balance NUMBER DEFAULT 0 ) Note that now we need the comma after the first row. There are a few constraints we did not talk go through an example, specifically foreign keys and check constraints. We will be adding these constraints to our database in future videos. In the mean time, I have a thought for you… Many people prefer to name their column constraints. That way, we can refer to certain constraints by name. For example, we might have a primary key constraint that could be named users_pk. The way we are creating these constraints does not allow for this feature, so in the next video we are going to be discussing different ways to create constraints. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support me on Patreon! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Views: 17685 Caleb Curry
Partitioning in Oracle Explained with Real project Examples : Introduction
 
22:43
This Video series will explain partitioning and its use cases referencing real project examples from different domain. It will explain what when and why of partitioning in a simple but elaborate manner. This is the 1st video which explains why partitioning is required and what are the advantages we gain from it. The following videos will explain when should we do it and how.
Views: 17897 Tech Coach

Vedanta newsletter formats
Computer service technician cover letter
Vocational teacher cover letter
Jobs applications for 15 year olds in memphis tn
The best writing service review