Great demo-this is great for someone like me who is new to a job and doesn't understand all of the relationships of the tables yet. Also the similarity to Access is helpful. Thank you!
Thank you for this video, started 'playing' with SQL Developer this last month to save myself from downloading X number of tables and using vlookups in Excel to 'get the data I need'. Learning to write SQL selects with joins on multiple tables, for me at least, requires a chunk of focus time without interruption to get my head around what is coming from where... one video, one click and now it is a whole load clearer!
Chris Lamprecht I'm a bit of an Excel fan cos I challenged myself to manipulate data in my reports and data tables building. I'm a semi advanced user when I moved from just vlookup to ifindexmatch functions that's how good I was with data tables but I realised Excel is not really considered a professional skill in most cases so I've decided to take up DBMS hardcore that's why I've just installed SQL Dev. My goal is to later evvole to designing ERP systems and even become a professional database administrator. I admit that Excel was a good foundation to learn how to design tables and retrieve and manipulate data. Also it gave me insight into writing syntax or code to execute and debug.
Thank you for the clear presentation. I have to brush up on SQL to prepare for an interview. This is far better than sitting with books. I can watch & listen while on elliptical at the gym.
Dear Jeff, I am very thankful to you . First, I had watched out your video which is query builder. Second, I tried this in oracle developer which is very useful for me to join and so on. Moreover , I request you to put more videos and share your knowledge So, it will helpful for me.
🎯 Key Takeaways for quick navigation: 00:01 🧐 *Query Builder Overview* - Introduction to the Query Builder feature in SQL Developer. - Query Builder interface reminiscent of Microsoft Access for building queries. - Comparison with manual SQL statement writing. 02:11 🔄 *Switching to Query Builder* - Demonstrates the process of converting a manually written SQL statement to the Query Builder interface. - Explains the importance of referential integrity for the Query Builder to work effectively. - Highlights the ability to visually define columns from tables in the Query Builder. 03:29 🔄 *Interactivity Between Worksheet and Query Builder* - Shows the seamless interaction between the Query Builder and the worksheet. - Modifying the query in the Query Builder reflects changes in the worksheet. - Running queries from the worksheet based on modifications made in the Query Builder. 04:19 🎯 *Advanced Query Customization* - Discusses additional features in the Query Builder, such as adding aliases, applying aggregates, and sorting. - Demonstrates the process of adding criteria to filter data in the Query Builder. - Explains the default table aliases and how to customize them. 05:46 ⚙️ *Time-Saving with Query Builder* - Emphasizes the time-saving aspect of the Query Builder, especially for complex queries with multiple joins. - Illustrates building a query with multiple tables using the Query Builder. - Highlights the efficiency of the Query Builder for users dealing with numerous table joins. 07:42 🚀 *Query Builder Use Cases* - Discusses use cases for the Query Builder, especially for users new to Oracle and databases. - Advocates for Query Builder as a tool for those who struggle with manual SQL but understand tables, columns, and relationships. - Acknowledges personal preference for manual query customization beyond the initial Query Builder skeleton. Made with HARPA AI
Hi Jeff, Thanks for the wonderful video .If you can guide me in the below two questions I would be very grateful to you. 1. How do I select multiple tables and then drag them together to query builder ( I tried holding ctrl, alt,shift ,even ctrl Plus shift ..it immediately opens up table structure so i can't drag) 2. Is there any feasibility to add case statements in Query builder . Thank you
I"m getting the context menus where I can set the table alias and check/uncheck all the columns. I'm on Win7, Java 9, SQLDev 18.1 using Oracle Look and Feel.
I had the same issue. Seems to be a bug(ish). To re-create the bug, from a new session in which you have not done anything yet: Drag a table(s) and drop into the worksheet tab, then click on query builder, no context menu shows up. Clear everything, now drag and drop into the query builder tab, now the context menus are there.
Is there a way to tell QB how to name the table alias? My database uses xxx_auto_key for all the indexes and foreign key names. The standard is to alias the table with the 3 letters from its primary key.
Thank you for the demo! Easy and understanding. I have a problem using the "drag and drop effects" though. When i mark on of the tables it automaticaly opens a new tab and I cant mark two or more tables. What am I doing wrong?
you're not doing anything wrong! the default behavior in the tool is to automatically open an object when you click on it - as you can imagine, this gets in the way when you want to simply drag and drop objects onto a worksheet, model, or query builder to get around this, go into preferences, and disable 'open object on single click,' it's on the Database - Object Viewer page.
Hello I need clarity, Can we use sql developer tool to build sql without writing sql statements like Hyperion interactive reporting (Brio)? if that is possible, what's the latest or the best version available to connect to DB2 to pull records? appreciate your answer..
Yes, you basically add the same table, twice - then write the join. select ( e.first_name || ' ' || e.last_name ) employee , ( m.first_name || ' ' || m.last_name ) manager , e.JOB_ID from hr.EMPLOYEES e , hr.EMPLOYEES m where m.employee_id (+) = e.manager_id order by manager;
HELLO! I ask about this message from Services \ (Windows could not start the Oracle ORCL VSS Writer Service service on Local Computer). Error 2 : The system cannot find the file specified.
+Ndiaye Tahar sorry, but SQL Developer doesn't have anything to do with the Shadow Volume Copy Service? docs.oracle.com/cd/B28359_01/win.111/b32010/vss.htm
Jeff, if you have new tables and you have no idea about primary keys and foreign keys, how do you use this trick? Query builder does not show any relationship because there are no foreign keys set up. Can you please advise? Thanks
you have to connect the tables yourself. to do that, you'll need to understand the data model - even if that's not demonstrated in the database via foreign keys
OH! I got so excited. I thought OMG i can use this trick and create all relationships but not like that. Hmm! It will take months to understand which are foreign keys. I know Primary keys but not Foreign Keys. There are hundred's of columns and millions of rows. I am sure once i understand Foreign keys then this trick will be very useful. Thanks.
Doesn't matter how many rows there are...having hundreds of columns won't make your job any easier though. If your developers had half an ounce of pity, they would use the same column name between two tables...like 'customer_id' in both a CUSTOMERS and SALES table.
So if they are the same names that would be Foreign Key. I am new to this SQL Developer and i am working with almost 10 tables for my own practice at home and every table there is a column called ID so i think that would be primary key.Right? I was so excited to try this trick but now i have to figure out which ones are Foreign Keys. Your video is really good and will be really helpful once i get the hang of Foreign Keys. Thanks
In Query Builder, it is not even giving any alias for the tables names. Has there been any changes in SQL Developer recently. I never faced this problem earlier. Thanks
Thanks, Jeff! It is so weird. In my office I was not able to see the Context menu with options like Check All, Uncheck All, Remove etc (Maybe it does not work with Datawarehouse tables) Right now at home, I can see everything. OK, thanks
How do you set up auto aliasing and for the join command to automatically recognize columns with the same name. Also when I right click to unselect all, I dont get the menu to unselect.
The joins are identified by the presence of Foreign Key constraints on the involved tables - we don't guess relationships by column names. If there's no FK, you can manually create the Join in the query builder.
Ahh ok, that would make sense. Our database doesn't have those FKs defined. Also, is there a setting I need to set to show the right click menu when i right click on a table in the Querry Builder? I have a table that has 80 columns and want to uncheck them all but I dont get that right click menu.
Sorry to hear that. There is unfortunately an ages old myth that FK's 'slow down the database and its applications.' I call shenanigans on this as often as I can. The right-click thing is a pesky BUG. Good news is that it's fixed for version 4.2. Sorry about that inconvenience.
Aww man. That sucks. We have a huge PDM database where I assume they believed that myth and didnt assign any FKs to anything :/. Thanks for the bug update!
Thanks, Jeff! My company wants to use sqlldr only and we got XML file to upload to a table. I have been spending so much time researching but so far I had no success. Now I thought what if, I covert that XML file to CSV file and then import it the way you had shown in one of the videos. Not sure if that will work or no? Thanks
Great video. Thanks Jeff. Can you please make more of such simple videos for other sql commands in Oracle developer for people who know nothing about writing queries?
Jeff Smith please I've just installed Oracle SQL Developer 64 bit on PC. I'm a complete novice and I've been having trouble setting up a new connection and also I don't know how to build a database for SQL. I'd followed the connection setup example from Oracle' website but still no luck. Please help. Thank you very much
Ok. I'd invite you to read this medium.com/oracledevs/i-installed-oracle-sql-developer-now-what-71230b9c9303 ... then scroll down to where I talk about the VirtualBox solution for getting started with Oracle Database
Great demo-this is great for someone like me who is new to a job and doesn't understand all of the relationships of the tables yet. Also the similarity to Access is helpful. Thank you!
Thank you for this video, started 'playing' with SQL Developer this last month to save myself from downloading X number of tables and using vlookups in Excel to 'get the data I need'. Learning to write SQL selects with joins on multiple tables, for me at least, requires a chunk of focus time without interruption to get my head around what is coming from where... one video, one click and now it is a whole load clearer!
You're very welcome Chris!
Chris Lamprecht I'm a bit of an Excel fan cos I challenged myself to manipulate data in my reports and data tables building. I'm a semi advanced user when I moved from just vlookup to ifindexmatch functions that's how good I was with data tables but I realised Excel is not really considered a professional skill in most cases so I've decided to take up DBMS hardcore that's why I've just installed SQL Dev. My goal is to later evvole to designing ERP systems and even become a professional database administrator. I admit that Excel was a good foundation to learn how to design tables and retrieve and manipulate data. Also it gave me insight into writing syntax or code to execute and debug.
Thank you for the clear presentation. I have to brush up on SQL to prepare for an interview. This is far better than sitting with books. I can watch & listen while on elliptical at the gym.
Dear Jeff, I am very thankful to you . First, I had watched out your video which is query builder. Second, I tried this in oracle developer which is very useful for me to join and so on. Moreover , I request you to put more videos and share your knowledge So, it will helpful for me.
This is all I needed a simple and to the point explanation, Thank You!
you're very welcome!
Jeff Smith is such a baller! Right on, Brother!
🎯 Key Takeaways for quick navigation:
00:01 🧐 *Query Builder Overview*
- Introduction to the Query Builder feature in SQL Developer.
- Query Builder interface reminiscent of Microsoft Access for building queries.
- Comparison with manual SQL statement writing.
02:11 🔄 *Switching to Query Builder*
- Demonstrates the process of converting a manually written SQL statement to the Query Builder interface.
- Explains the importance of referential integrity for the Query Builder to work effectively.
- Highlights the ability to visually define columns from tables in the Query Builder.
03:29 🔄 *Interactivity Between Worksheet and Query Builder*
- Shows the seamless interaction between the Query Builder and the worksheet.
- Modifying the query in the Query Builder reflects changes in the worksheet.
- Running queries from the worksheet based on modifications made in the Query Builder.
04:19 🎯 *Advanced Query Customization*
- Discusses additional features in the Query Builder, such as adding aliases, applying aggregates, and sorting.
- Demonstrates the process of adding criteria to filter data in the Query Builder.
- Explains the default table aliases and how to customize them.
05:46 ⚙️ *Time-Saving with Query Builder*
- Emphasizes the time-saving aspect of the Query Builder, especially for complex queries with multiple joins.
- Illustrates building a query with multiple tables using the Query Builder.
- Highlights the efficiency of the Query Builder for users dealing with numerous table joins.
07:42 🚀 *Query Builder Use Cases*
- Discusses use cases for the Query Builder, especially for users new to Oracle and databases.
- Advocates for Query Builder as a tool for those who struggle with manual SQL but understand tables, columns, and relationships.
- Acknowledges personal preference for manual query customization beyond the initial Query Builder skeleton.
Made with HARPA AI
It takes longer to read the AI generated comments to my video than it would take to actually watch my video.
super always wondered what that Query builder tool is ! Now i know thanks to you jeff
Hi Jeff, Thanks for the wonderful video .If you can guide me in the below two questions I would be very grateful to you.
1. How do I select multiple tables and then drag them together to query builder ( I tried holding ctrl, alt,shift ,even ctrl Plus shift ..it immediately opens up table structure so i can't drag)
2. Is there any feasibility to add case statements in Query builder .
Thank you
1. Go into options, search for open on single click, DISABLE
Thank you
@@JeffSmiththat How to get to options. I did the right click but nothing.....
@@didierdodzinumatekpo3843 tools - preferences
@@JeffSmiththat Tools- Preferences-Database-Objective views-single click . Thanks jeff
In Query Builder, I am not able to right click on the tables to get the options to uncheck etc. Context menu does not show up.
I"m getting the context menus where I can set the table alias and check/uncheck all the columns. I'm on Win7, Java 9, SQLDev 18.1 using Oracle Look and Feel.
I had the same issue. Seems to be a bug(ish). To re-create the bug, from a new session in which you have not done anything yet: Drag a table(s) and drop into the worksheet tab, then click on query builder, no context menu shows up. Clear everything, now drag and drop into the query builder tab, now the context menus are there.
Is there a way to tell QB how to name the table alias? My database uses xxx_auto_key for all the indexes and foreign key names. The standard is to alias the table with the 3 letters from its primary key.
Yes, right click on the table, in the properties, set the ALIAS.
très cool vidéos, de très bons guides, suffisamment enrichis pour bien servir des lecteurs avertis.
Thank you for the demo! Easy and understanding.
I have a problem using the "drag and drop effects" though. When i mark on of the tables it automaticaly opens a new tab and I cant mark two or more tables. What am I doing wrong?
you're not doing anything wrong!
the default behavior in the tool is to automatically open an object when you click on it - as you can imagine, this gets in the way when you want to simply drag and drop objects onto a worksheet, model, or query builder
to get around this, go into preferences, and disable 'open object on single click,' it's on the Database - Object Viewer page.
@@JeffSmiththat Worked like a dream. Thank you so much!
Hello I need clarity, Can we use sql developer tool to build sql without writing sql statements like Hyperion interactive reporting (Brio)? if that is possible, what's the latest or the best version available to connect to DB2 to pull records? appreciate your answer..
This video you're commenting on shows how to browse data w/o writing actual SQL - by drag and drop. It's only for Oracle, not for IBM DB2.
thank you so much for the great work. Is there any way to do self join from query builder?
Yes, you basically add the same table, twice - then write the join.
select ( e.first_name
|| ' '
|| e.last_name ) employee
, ( m.first_name
|| ' '
|| m.last_name ) manager
, e.JOB_ID
from hr.EMPLOYEES e
, hr.EMPLOYEES m
where m.employee_id (+) = e.manager_id
order by manager;
HELLO! I ask about this message from Services \ (Windows could not start the Oracle ORCL VSS Writer Service service on Local Computer).
Error 2 : The system cannot find the file specified.
+Ndiaye Tahar sorry, but SQL Developer doesn't have anything to do with the Shadow Volume Copy Service? docs.oracle.com/cd/B28359_01/win.111/b32010/vss.htm
Jeff, if you have new tables and you have no idea about primary keys and foreign keys, how do you use this trick? Query builder does not show any relationship because there are no foreign keys set up. Can you please advise? Thanks
you have to connect the tables yourself. to do that, you'll need to understand the data model - even if that's not demonstrated in the database via foreign keys
OH! I got so excited. I thought OMG i can use this trick and create all relationships but not like that. Hmm! It will take months to understand which are foreign keys. I know Primary keys but not Foreign Keys. There are hundred's of columns and millions of rows. I am sure once i understand Foreign keys then this trick will be very useful. Thanks.
Doesn't matter how many rows there are...having hundreds of columns won't make your job any easier though. If your developers had half an ounce of pity, they would use the same column name between two tables...like 'customer_id' in both a CUSTOMERS and SALES table.
So if they are the same names that would be Foreign Key. I am new to this SQL Developer and i am working with almost 10 tables for my own practice at home and every table there is a column called ID so i think that would be primary key.Right?
I was so excited to try this trick but now i have to figure out which ones are Foreign Keys. Your video is really good and will be really helpful once i get the hang of Foreign Keys. Thanks
a very very good chance, yes
Thanks a lot for this demo
You're very welcome!
Hey we use this Query Builder to work on GMAO Data base
It's only for Oracle Database, sorry I do not know what 'GMAO' is.
In Query Builder, it is not even giving any alias for the tables names. Has there been any changes in SQL Developer recently. I never faced this problem earlier. Thanks
Peace will take a look in an hour or so. I will say we made a huge performance improvement for the QB for v18.2, so you can look fwd to that
Thanks, Jeff! It is so weird. In my office I was not able to see the Context menu with options like Check All, Uncheck All, Remove etc (Maybe it does not work with Datawarehouse tables) Right now at home, I can see everything. OK, thanks
yeah, something is 'different'
Thanks for the information. This sure saves time
Thanks Ann!
beautiful presentation. Thanks.
How do you set up auto aliasing and for the join command to automatically recognize columns with the same name. Also when I right click to unselect all, I dont get the menu to unselect.
The joins are identified by the presence of Foreign Key constraints on the involved tables - we don't guess relationships by column names. If there's no FK, you can manually create the Join in the query builder.
Ahh ok, that would make sense. Our database doesn't have those FKs defined. Also, is there a setting I need to set to show the right click menu when i right click on a table in the Querry Builder? I have a table that has 80 columns and want to uncheck them all but I dont get that right click menu.
Sorry to hear that. There is unfortunately an ages old myth that FK's 'slow down the database and its applications.' I call shenanigans on this as often as I can.
The right-click thing is a pesky BUG. Good news is that it's fixed for version 4.2. Sorry about that inconvenience.
Aww man. That sucks. We have a huge PDM database where I assume they believed that myth and didnt assign any FKs to anything :/. Thanks for the bug update!
Jeff, can we use sqlldr to import xml file? If yes, then did you create any video showing how to import xml data to our table. Thanks
Peace no, the data needs to be delimited or fixed width. There are xml solutions in the database to load data to tables though
Thanks, Jeff! My company wants to use sqlldr only and we got XML file to upload to a table. I have been spending so much time researching but so far I had no success.
Now I thought what if, I covert that XML file to CSV file and then import it the way you had shown in one of the videos. Not sure if that will work or no? Thanks
Peace you'll have to flatten the xml, redo the way the data is structured
Do you have any video on that please? Thanks
i was wrong, you CAN do that, docs.oracle.com/database/121/ADXDB/xdb25loa.htm#ADXDB5731
Great video. Thanks Jeff. Can you please make more of such simple videos for other sql commands in Oracle developer for people who know nothing about writing queries?
thai huns I can try!!
Jeff You are the best. Thank You.
This was very helpful! Thanks
Jeff Smith please I've just installed Oracle SQL Developer 64 bit on PC. I'm a complete novice and I've been having trouble setting up a new connection and also I don't know how to build a database for SQL. I'd followed the connection setup example from Oracle' website but still no luck. Please help. Thank you very much
Joseph Amiegbe do you have a database?
Jeff Smith no I do not. I'm new to DBMS and I don't know how to build a database for SQL.
Ok. I'd invite you to read this medium.com/oracledevs/i-installed-oracle-sql-developer-now-what-71230b9c9303 ... then scroll down to where I talk about the VirtualBox solution for getting started with Oracle Database
hi. now ive instaled the virtual box and the extension pack. but i cannot see the appliance or image file to import when i try to do so
there are two things to download. the image file from OTN and the virtualbox software itself
where to write query in worksheet or in the query builder?
Writing the query..leave the query builder, go back to the worksheet
And how to create new sql file in SQL Developer under same connection?
@@ankitsethia6534 right click on your connection, select SQL worksheet. Type your code, hit save.
@@JeffSmiththat thanks
Please reply to this? If worksheet is used for writing sql query then what's the use of Query Builder?
Very informative.. Thanks alot
You're very welcome!
thank u for this wonderful video.
you are very welcome!
Thanks - very helpful.
thank you so much.
Need documentation
We have a documentation book online and built-in for SQL Developer, and there is a section on Query Builder. Let me know if you can't find it.
Thanks!
Спасибо!
пожалуйста!
Need documentation