Oracle SQL Developer: Query Builder Demo

แชร์
ฝัง
  • เผยแพร่เมื่อ 20 ม.ค. 2025

ความคิดเห็น • 105

  • @pstory6474
    @pstory6474 7 ปีที่แล้ว +6

    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!

  • @ChrisTian-uw9tq
    @ChrisTian-uw9tq 7 ปีที่แล้ว +3

    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!

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      You're very welcome Chris!

    • @josephamiegbe4552
      @josephamiegbe4552 7 ปีที่แล้ว

      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.

  • @wscott44
    @wscott44 9 ปีที่แล้ว +1

    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.

  • @sanjaykallakuri7010
    @sanjaykallakuri7010 7 ปีที่แล้ว +1

    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.

  • @MannyNegron
    @MannyNegron 8 ปีที่แล้ว +4

    This is all I needed a simple and to the point explanation, Thank You!

  • @Faither2187
    @Faither2187 4 ปีที่แล้ว +1

    Jeff Smith is such a baller! Right on, Brother!

  • @jasonabdi1
    @jasonabdi1 ปีที่แล้ว

    🎯 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

    • @JeffSmiththat
      @JeffSmiththat  ปีที่แล้ว

      It takes longer to read the AI generated comments to my video than it would take to actually watch my video.

  • @rubyhat7136
    @rubyhat7136 6 ปีที่แล้ว +1

    super always wondered what that Query builder tool is ! Now i know thanks to you jeff

  • @techproductowner
    @techproductowner 3 ปีที่แล้ว +2

    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

    • @JeffSmiththat
      @JeffSmiththat  3 ปีที่แล้ว +1

      1. Go into options, search for open on single click, DISABLE

    • @techproductowner
      @techproductowner 3 ปีที่แล้ว

      Thank you

    • @didierdodzinumatekpo3843
      @didierdodzinumatekpo3843 2 ปีที่แล้ว

      @@JeffSmiththat How to get to options. I did the right click but nothing.....

    • @JeffSmiththat
      @JeffSmiththat  2 ปีที่แล้ว

      @@didierdodzinumatekpo3843 tools - preferences

    • @didierdodzinumatekpo3843
      @didierdodzinumatekpo3843 2 ปีที่แล้ว +1

      @@JeffSmiththat Tools- Preferences-Database-Objective views-single click . Thanks jeff

  • @shoppersdream
    @shoppersdream 6 ปีที่แล้ว +1

    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.

    • @JeffSmiththat
      @JeffSmiththat  6 ปีที่แล้ว

      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.

    • @alicedee7994
      @alicedee7994 4 ปีที่แล้ว

      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.

  • @MikeCarey101
    @MikeCarey101 ปีที่แล้ว

    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.

    • @JeffSmiththat
      @JeffSmiththat  ปีที่แล้ว

      Yes, right click on the table, in the properties, set the ALIAS.

  • @mohamedlaminekeita7844
    @mohamedlaminekeita7844 8 ปีที่แล้ว +1

    très cool vidéos, de très bons guides, suffisamment enrichis pour bien servir des lecteurs avertis.

  • @martinfermin1841
    @martinfermin1841 5 ปีที่แล้ว

    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?

    • @JeffSmiththat
      @JeffSmiththat  5 ปีที่แล้ว +4

      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.

    • @martinfermin1841
      @martinfermin1841 5 ปีที่แล้ว +1

      @@JeffSmiththat Worked like a dream. Thank you so much!

  • @jonnathanjon1
    @jonnathanjon1 10 หลายเดือนก่อน

    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..

    • @JeffSmiththat
      @JeffSmiththat  10 หลายเดือนก่อน

      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.

  • @danayoung4460
    @danayoung4460 2 ปีที่แล้ว

    thank you so much for the great work. Is there any way to do self join from query builder?

    • @JeffSmiththat
      @JeffSmiththat  2 ปีที่แล้ว

      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;

  • @officialtaher3957
    @officialtaher3957 9 ปีที่แล้ว

    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.

    • @JeffSmiththat
      @JeffSmiththat  9 ปีที่แล้ว

      +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

  • @shoppersdream
    @shoppersdream 7 ปีที่แล้ว

    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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      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

    • @shoppersdream
      @shoppersdream 7 ปีที่แล้ว

      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.

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      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.

    • @shoppersdream
      @shoppersdream 7 ปีที่แล้ว

      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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      a very very good chance, yes

  • @aligat2311
    @aligat2311 ปีที่แล้ว +1

    Thanks a lot for this demo

  • @highvolt9679
    @highvolt9679 ปีที่แล้ว

    Hey we use this Query Builder to work on GMAO Data base

    • @JeffSmiththat
      @JeffSmiththat  ปีที่แล้ว

      It's only for Oracle Database, sorry I do not know what 'GMAO' is.

  • @shoppersdream
    @shoppersdream 6 ปีที่แล้ว

    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

    • @JeffSmiththat
      @JeffSmiththat  6 ปีที่แล้ว

      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

    • @shoppersdream
      @shoppersdream 6 ปีที่แล้ว

      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

    • @JeffSmiththat
      @JeffSmiththat  6 ปีที่แล้ว

      yeah, something is 'different'

  • @annlussikeh484
    @annlussikeh484 6 ปีที่แล้ว +1

    Thanks for the information. This sure saves time

  • @user-vo7sr5ob6y
    @user-vo7sr5ob6y 4 ปีที่แล้ว +1

    beautiful presentation. Thanks.

  • @sliphere011
    @sliphere011 7 ปีที่แล้ว

    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.

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      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.

    • @sliphere011
      @sliphere011 7 ปีที่แล้ว

      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.

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      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.

    • @sliphere011
      @sliphere011 7 ปีที่แล้ว

      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!

  • @shoppersdream
    @shoppersdream 7 ปีที่แล้ว

    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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      Peace no, the data needs to be delimited or fixed width. There are xml solutions in the database to load data to tables though

    • @shoppersdream
      @shoppersdream 7 ปีที่แล้ว

      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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      Peace you'll have to flatten the xml, redo the way the data is structured

    • @shoppersdream
      @shoppersdream 7 ปีที่แล้ว

      Do you have any video on that please? Thanks

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      i was wrong, you CAN do that, docs.oracle.com/database/121/ADXDB/xdb25loa.htm#ADXDB5731

  • @thaihuns9172
    @thaihuns9172 7 ปีที่แล้ว

    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?

  • @navk1000
    @navk1000 5 ปีที่แล้ว

    Jeff You are the best. Thank You.

  • @shoppersdream
    @shoppersdream 7 ปีที่แล้ว +1

    This was very helpful! Thanks

  • @josephamiegbe4552
    @josephamiegbe4552 7 ปีที่แล้ว

    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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      Joseph Amiegbe do you have a database?

    • @josephamiegbe4552
      @josephamiegbe4552 7 ปีที่แล้ว

      Jeff Smith no I do not. I'm new to DBMS and I don't know how to build a database for SQL.

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว +1

      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

    • @josephamiegbe4552
      @josephamiegbe4552 7 ปีที่แล้ว

      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

    • @JeffSmiththat
      @JeffSmiththat  7 ปีที่แล้ว

      there are two things to download. the image file from OTN and the virtualbox software itself

  • @ankitsethia6534
    @ankitsethia6534 5 ปีที่แล้ว

    where to write query in worksheet or in the query builder?

    • @JeffSmiththat
      @JeffSmiththat  5 ปีที่แล้ว

      Writing the query..leave the query builder, go back to the worksheet

    • @ankitsethia6534
      @ankitsethia6534 5 ปีที่แล้ว

      And how to create new sql file in SQL Developer under same connection?

    • @JeffSmiththat
      @JeffSmiththat  5 ปีที่แล้ว

      @@ankitsethia6534 right click on your connection, select SQL worksheet. Type your code, hit save.

    • @ankitsethia6534
      @ankitsethia6534 5 ปีที่แล้ว

      @@JeffSmiththat thanks

    • @ankitsethia6534
      @ankitsethia6534 5 ปีที่แล้ว

      Please reply to this? If worksheet is used for writing sql query then what's the use of Query Builder?

  • @virendrajaiswal4633
    @virendrajaiswal4633 4 ปีที่แล้ว +1

    Very informative.. Thanks alot

  • @ibrahimmaiga1564
    @ibrahimmaiga1564 3 ปีที่แล้ว

    thank u for this wonderful video.

  • @shadowsruss5748
    @shadowsruss5748 8 ปีที่แล้ว +1

    Thanks - very helpful.

  • @hanhngo2355
    @hanhngo2355 5 ปีที่แล้ว +1

    thank you so much.

  • @highvolt9679
    @highvolt9679 ปีที่แล้ว

    Need documentation

    • @JeffSmiththat
      @JeffSmiththat  ปีที่แล้ว

      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.

  • @catherinebedard2273
    @catherinebedard2273 3 ปีที่แล้ว

    Thanks!

  • @na-ck9mn
    @na-ck9mn 6 ปีที่แล้ว

    Спасибо!

  • @highvolt9679
    @highvolt9679 ปีที่แล้ว

    Need documentation