Home
Search results “Oracle json field”
JSON in Oracle 12C Part 1 - Creating JSON Columns in Tables
 
09:20
This is the first video in a series of videos on how to handle JSON data in Oracle 12C. In this video, I'll show you how to create columns in a table that will be used to hold JSON data.
Views: 6010 Pretty Printed
Converting JSON Data to Relational Data in Oracle 12C
 
08:26
JSON data is nice, but it is difficult to work with directly in Oracle. By converting it to relational data (rows and columns) we can easily work with it. This video demonstates how to convert JSON data to relational data using json_table.
Views: 7430 Pretty Printed
How to load text file content into CLOB column using Oracle sql loader
 
05:01
How to load text content into oracle table CLOB column? http://easy-learning-tech.blogspot.in/2018/04/loading-clob-data-into-oracle-table.html
Views: 2436 Siva Academy
How to Parse XML in Oracle
 
13:54
This Video Shows 1. How to Read and parse XML in Oracle using EXTRACTVALUE and XMLTABLE. 2. When to use extractvalue and XMLTable. 3. Brief Introduction to XMLPATH
Views: 17144 yrrhelp
AskTOM Office Hours: Using JSON to Implement Flexfields
 
24:41
Held on May 5 2018 A flexfield is a flexible data field that your organization can customize to your business needs without programming. In this session, Beda Hammerschmidt shows you how to leverage JSON columns to implement flexfields more effectively than was previously possible. 01:00 Demonstration begins 14:35 Q&A AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists. https://asktom.oracle.com/ Oracle Developers portal: https://developer.oracle.com/ Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit music: bensound.com
Views: 283 Oracle Developers
Indexing JSON Data in Oracle Database 12c
 
03:55
This video gives an overview of indexing JSON data in Oracle database 12c. For more information see: https://oracle-base.com/articles/12c/indexing-json-data-in-oracle-database-12cr1 Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Bertrand Drouvot : Blog: https://bdrouvot.wordpress.com/ Twitter : https://twitter.com/BertrandDrouvot Cameo appearances are for fun, not an endorsement of the content of this video. All trademarks, product names and logos are the property of their respective owners.
Views: 2010 ORACLE-BASE.com
How to Query a FlowFile in Apache NiFi Using QueryRecord
 
10:25
How to Query a FlowFile in Apache NiFi Using QueryRecord http://www.aodba.com Here are the Steps as described in the Demo GenerateFlowfile EId|PId|Date|Desc 1|ABC-123|2017-05-01|"Groove party" 2|ABC-111|2017-06-01|"Snack No. One" 3|ABC-123|2017-06-01|"The night out" UpdateAttribute schema.name schema_name QueryRecord AvroSchemaRegistry { "name": "myschema", "namespace": "nifi", "type": "record", "fields": [ {"name": "EId" , "type" : "string"}, {"name": "PId" , "type" : "string"}, {"name": "Date" , "type" : "string"}, {"name": "Desc" , "type" : "string"} ] } Setup CSVReader Setup JsonRecordSetWriter sql = select * from FLOWFILE where EId='2' apache nifi | nifi hadoop | nifi processors | nifi | nifi examples | nifi etl | apache nifi example
Views: 3771 AO DBA
One SQL Cheat Code For Amazingly Fast JSON Queries - SQL JSON Index performance
 
10:41
How non-persisted computed column indexes make your JSON queries high performance. Did you know that non-persisted columns actually get saved to the index pages? This makes JSON querying incredibly fast in SQL Server. Please like and subscribe if you enjoyed this video! Blog post: https://bertwagner.com/2017/05/09/one-sql-cheat-code-for-amazingly-fast-json-queries/ Want to receive the latest weekly blog posts and videos in your inbox? Sign up for the newsletter here: https://upscri.be/c77fc8/ Elsewhere on the internet: https://bertwagner.com https://twitter.com/bertwagner
Views: 3857 Bert Wagner
JSON in the Oracle Database: Customize Auto Generated Views with JSON Dataguide
 
06:37
In Part 3 of the JSON Dataguide tutorial series, Beda Hammerschmidt, Consulting Member of Technical Staff at Oracle, explains how to customize auto-generated views with the JSON Dataguide Learn more: https://developer.oracle.com/ Get started on Oracle Cloud: https://cloud.oracle.com/en_US/tryit
Views: 298 Oracle Developers
Oracle Tutorial - How to add virtual column in oracle 11g
 
07:41
Hello friends in this video i'm going to demonstrate you how to add virtual column in ORACLE 11g step by step. Oracle 11g supports virtual columns which are derived from the other columns in the table. You can use the virtual columns in your queries, you can index them, you can collect the statistics, etc. There are few restrictions on the virtual columns. You cannot write/insert the data into virtual columns. There is no support for index_organized, external, object, cluster, temporary tables.
Views: 1469 OCP Technology
Database Mobile Server Demonstration
 
14:56
This video is a Database Mobile Server demo which shows the steps required to enable off-line data access for an application on a mobile device. You can find the Transportation sample bundled with the installed product in this directory: %MOBILE_HOME%\Sdk\samples\android\transport. Copyright © 2014 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
JSON Data Modeling for RDBMS Users
 
18:23
JSON data modeling is a vital part of using a document database like Couchbase. There are two key approaches to modeling relationships.
Views: 437 Matthew Groves
How to generated nested JSON objects and arrays in Mockaroo
 
02:50
Have a deeply nested structure you need to recreate? In this tutorial I'll show you how to use "." notation to create the data you need.
Views: 7347 Mockaroo
Vim - SQL/JSON field conversion macros
 
07:04
This is a scenario I run into all the time when constructing the typical methods that are needed when working with model classes. The file on the right gives a completed example from an older file to imitate for our new Writer. Still missing Screenkeys and tight editing of the different takes. Also it sounds like I recorded it on a busy train station platform.
Views: 277 Bill Frasure
Getting Started with SSIS Productivity Pack - JSON Processing Components
 
29:27
The JSON Processing Components components are SSIS add-on components from the SSIS Productivity Pack which facilitate extracting JSON structure into column data or merging column data into JSON structure. We currently offer the following 4 JSON components: JSON Source: An SSIS source component used to retrieve JSON documents from an HTTP URL or a local file. The components will then break up the structure and produce column data which can be consumed by downstream SSIS pipeline components. JSON Extract: A transformation component that receives a JSON document from an upstream component and extracts data to produce column data for the SSIS pipeline. JSON Merge: An SSIS transformation component used to take incoming data from upstream SSIS source components and merge them into one SSIS column data based on the JSON data structure defined in the component. JSON Destination: An SSIS destination component used to merge input data based on the JSON data structure defined in the component. You can then specify the component to write the merged JSON document to a local file or you can send it to a HTTP URL to perform a RESTful service call. You can learn more about these components from the help manual: http://www.kingswaysoft.com/products/ssis-productivity-pack/help-manual/json-and-xml-processing The components along with all others found within the SSIS Productivity Pack are available for download here: http://www.kingswaysoft.com/products/ssis-productivity-pack/download Visit the following link to learn more about the SSIS Productivity Pack, a collection of premium, powerful and unique SSIS components ever developers needs in their toolbox: https://www.kingswaysoft.com/products/ssis-productivity-pack This video was made with v4.1 of the SSIS Productivity Pack.
Views: 1313 KingswaySoft
MySQL 5.7: MySQL JSON data type example
 
07:08
This video log shows how to use JSON data type provided by MySQL 5.7.
Views: 16541 CodeVlog
10.12. - JSON, REST und die Oracle-Datenbank - mit Carsten Czarski
 
03:50
Moderne Technologien wie JSON und REST mit der Oracle-Datenbank.
Database connection in Eclipse (Oracle) : javavids
 
03:33
How to connect Oracle database in Eclipse? Part of Java online video tutorials: http://www.javavids.com
Views: 99587 Jiri Pinkas
Field Service | Setting up Authorization for the REST API in Oracle Field Service
 
02:03
Shows you how to add appropriate privileges to a user type so that users of that type can make calls to the Oracle Field Service REST API.
How to create Virtual Columns in Oracle Database
 
09:02
How to create Virtual Columns in Oracle Database 12c When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMBER(3), salary1 AS (ROUND(salary*(1+comm1/100),2)), salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL, CONSTRAINT employees_pk PRIMARY KEY (id) ); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (1, 'JOHN', 'DOE', 100, 5, 10); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (2, 'JAYNE', 'DOE', 200, 10, 20); COMMIT; Querying the table shows the inserted data plus the derived commission-based salaries. SELECT * FROM employees; ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 JOHN DOE 100 5 10 105 110 2 JAYNE DOE 200 10 20 220 240 2 rows selected. SQL The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views. COLUMN data_default FORMAT A50 SELECT column_name, data_default FROM user_tab_columns WHERE table_name = 'EMPLOYEES'; COLUMN_NAME DATA_DEFAULT ------------------------------ -------------------------------------------------- ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2) SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2) 8 rows selected. SQL Notes and restrictions on virtual columns include: 1)Indexes defined against virtual columns are equivalent to function-based indexes. 2)Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML. 3)Tables containing virtual columns can still be eligible for result caching. 4)Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled: a)Constraint on the virtual column must be disabled and re-enabled. b)Indexes on the virtual column must be rebuilt. c)Materialized views that access the virtual column must be fully refreshed. d)The result cache must be flushed if cached queries have accessed the virtual column. e)Table statistics must be regathered. 5)Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables. 6)The expression used in the virtual column definition has the following restrictions: a.It cannot refer to another virtual column by name. b.It can only refer to columns defined in the same table. c.If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column. e.The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Views: 607 OracleDBA
Oracle Responsys - Getting Started with Program
 
03:16
This video will introduce you Program and the Program Designer and show you how to use the Program Designer to send an email campaign.
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: 52451 Jeff Smith
Field Service | Using the Service Cloud REST API in PHP
 
03:54
This video shows you an example of using the Field Service REST API in PHP code.
jQuery datatables get data from database table
 
21:04
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-get-data-from.html In this video we will discuss how to use database data with jQuery datatables plugin. There are many ways to get data into DataTables. If the data is small you can retrieve all the data at once and the processing (i.e paging, searching, ordering) is done on the client-side. On the other hand if the database data is large, server-side processing can be enabled. With server-side processing enabled, all paging, searching, ordering actions that DataTables performs are handed off to the server. In this video we will discuss retrieving all the data at once. The datatables plugin performs all the processing (i.e paging, searching, ordering etc) on the client-side. We want to retrieve data from the following database table tblEmployee. The database data should then be displayed using jQuery datatables plugin
Views: 169483 kudvenkat
Using Flyway to manage Oracle DB Versions with Oracle Developer Cloud Service
 
09:01
How to setup flyway and tie it into a DevOps cycle for an Oracle Database Cloud Service leveraging Oracle Developer Cloud Service. Related blog - https://blogs.oracle.com/shay/using-flyway-to-manage-oracle-db-versions-in-the-cloud
Views: 1314 ShayJDev
How to read JSON data in Spring Boot and write to a database
 
23:36
Enroll in my Spring Boot Course http://bit.ly/2pQuyFq In this tutorial, we are going to look at how to read JSON data in a Spring Boot application and write it to a database. Source Code: https://github.com/cfaddict/spring-boot-jsontodb Please Subscribe to this channel and leave a comment below. Blog: http://therealdanvega.com/blog Facebook: http://www.facebook.com/therealdanvega Twitter: http://www.twitter.com/therealdanvega
Views: 21633 Dan Vega
How to Query JSON in SQL 2016 Part 2 -1
 
12:30
In this video we will discuss how to transforom relational data to JSON data using JSON Path and JSON Auto and we will also get introduced to concepts of JSON Objects, Arrays, Embedded and Nested JSON object. To continue part 2 please click this video https://www.youtube.com/watch?v=j2Uxo9sMw0A
Views: 7358 SQL JUNKIESHARE
Extracting Data from a JSON Response in Python (Python for Beginners) | Part 34
 
07:13
Enjoyed my video? Leave a like! GitHub Link: https://github.com/maxg203/Python-for-Beginners Personal Website: http://maxgoodridge.com
Views: 57061 Max Goodridge
Apache Drill  Enabling High-Performance SQL with a JSON Data Model
 
07:14
http://drill.apache.org Tomer Shiran, PMC member and Apache Drill committer, walks you through the magic behinde Drill. The execution engine's JSON data model enables high-performance SQL with: - Data-driven query compilation and recompilation - Columnar execution on complex and schema-free data Apache Drill The Rise of the Non-Relational Datastore http://youtu.be/65c42i7Xg7Q Apache Drill How to Deploy Apache Drill and Connect to BI Tools http://youtu.be/MYY51kiFPTk Apache Drill How Drill Connects to Data Sources http://youtu.be/bhmNbH2yzhM Go to http://drill.apache.org and download the Apache Drill!
Views: 6888 MapR Technologies
Oracle Ask Tom Office Hours: Understanding Async Processing in Node.js
 
01:18:11
Held on March 6, 2018 This session explores the intersection of JavaScript and Oracle Database. We begin with a brief tutorial on how to understand async processing and patterns in Node.js. Then Oracle Developer experts Dan McGhan, Chris Jones, Anthony Tuininga and Blaine Carter discuss the capabilities and field questions. AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists. https://asktom.oracle.com/ Oracle Developers portal: https://developer.oracle.com/ Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit music: bensound.com
Views: 255 Oracle Developers
Beautiful REST + JSON APIs with JAX-RS and Jersey
 
02:00:00
Designing and building a really clean and intuitive ReST API is no small feat. You have to worry about resources, collections of resources, pagination, query parameters, references to other resources, which HTTP methods to use, HTTP caching, security, and more. And you have to make sure it lasts and doesn't break clients as you add features over time. Furthermore, although there are many references on creating REST APIs with XML, there are far fewer references on REST + JSON. It is enough to drive you crazy. This session demonstrates how to design and implement an elegant REST API. Copyright © 2013 Oracle and/or its affiliates. Oracle® is a registered trademark of Oracle and/or its affiliates. All rights reserved. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the "Materials"). The Materials are provided "as is" without any warranty of any kind, either express or implied, including without limitation warranties of merchantability, fitness for a particular purpose, and non-infringement.
Oracle REST Enabled SQL Lesson 1 What is REST
 
01:57
Learn what REST technology is and how and where (ORDS) it relates to Oracle Database. All lessons are free at https://www.skillbuilders.com/free-oracle-apex-tutorials/what-is-rest-enabled-sql-oracle/.
Views: 413 SkillBuilders
Oracle Cloud Infrastructure High Performance Computing with Karan Batta
 
09:09
Karan Batta, Product Manager, shows how Oracle addresses HP and performance-oriented customers with on-demand compute, storage, and software technologies. Recorded at Cloud Field Day in Silicon Valley on April 4, 2018. For more information, visit http://TechFieldDay.com/event/cfd3/
Views: 358 Tech Field Day
Tutorial : Concepts of Oracle APEX DML Form Page
 
40:46
In this video we discussed about Automatic Row Fetch, Automatic Row Processing, Page redirection with button and report linking. Hope the videos will be helpful for the new developers.
Views: 3373 Oracle Testlab
Modern (NoSQL) Database Application Development Everywhere
 
44:12
Beda Hammerschmidt, Consulting Member of Technical Staff, Oracle Vikas Arora, Senior Director, Oracle Maxim Orgiyan, PMTS, Oracle NoSQL databases such as MongoDB have captured a major share of developers’ attention. SQL databases such as PostgreSQL, MySQL, and Oracle Database are not going away and have added JSON support, enabling data to be stored in the same way as in NoSQL databases, as JSON documents. However, APIs for relational databases, such as JDBC, still offer only the low-level model of rows stored in tables. An open source API called SODA, implementable on any relational database with JSON support, bridges this gap, providing document collections with CRUD operations. This session describes the speakers’ implementation of this API across different languages and lessons learned from customers using their relational databases as NoSQL document stores through this API.
Views: 117 Oracle Developers
BASH scripting lesson 10 working with CSV files
 
12:03
More videos like this online at http://www.theurbanpenguin.com We now have some more great fun and see how much we can use the shell for; creating reports easily from the command line against CSV files. The script should be quite easy to read now as we use a while loop to read in the CSV file. We change the file delimiter to be the comma and then we have the line that we read in broken up into the schema elements we need. A report then is easy with colours and search ability. This is very usable
Views: 56545 theurbanpenguin
Field Service | Using node.js with the Field Service REST API
 
03:30
Shows you a code example of how to access the REST API of Oracle Field Service using Node.js.
High Availability Oracle 12c Flex Clusters Part 1 of 6
 
02:51
Learn how to configure High Availability for Applications (e.g. Apache, Tomcat, etc) with Oracle 12c Flex Clusters. (Watch all 6 lessons, free, at http://skillbuilders.com/oracle-12c-flex-clusters-tutorial.) Of course, we can configure HA for databases too, but if your applications will failover also, well that is fantastic. Yes, it can be done with FREE software from Oracle, Oracle 12c Flex Clusters.
Views: 1889 SkillBuilders
Load Json File To Table - Kettle
 
04:56
Show you how to load json files to table in postgres use kettle
Views: 17201 Bux Projects
Using Theme Roller with Universal Theme - Oracle Application Express (APEX) 5
 
28:44
See how to quickly change the look and feel of your Oracle Application Express applications. Quickly change or just preview colors for any element of your application, save to a new theme, add custom CSS or HTML, and much more.
Application Express 18.1 New Features Roundup
 
55:58
Held on July 12 2018 Marc Sewtz, Oracle Application Express Development Manager, proudly shares all the cool new features his team packed into the 18.1 release of APEX. 47:40 Q&A Begins AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists. https://asktom.oracle.com/ Oracle Developers portal: https://developer.oracle.com/ Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit music: bensound.com
Views: 4886 Oracle Developers
Field Service | Using the Notification Panel in Oracle Field Service Cloud
 
01:49
This tutorial shows technicians how to use the notification panel feature of Oracle Field Service Cloud.
Service | Using the Oracle Service Cloud REST API in PHP
 
04:30
This tutorial applies to Rel 15.8, 15.11 and 16.2. It shows you how to write PHP code that uses the OSvC REST API.
Beautiful Apps on data from everywhere: With Oracle APEX
 
46:42
Abstract: The times, when there were only a few enterprise databases, holding all the relevant data, are over. Today, enterprises have to deal with data from many sources: databases, non-relational stores or REST APIs provide the data for developers to use in their apps. Applications on top of that data are often required very quickly. In this session, we will look at some public REST APIs and Oracle APEX as the Low Code tool. The audience will learn how not only to quickly build applications on REST or database data but also how to integrate public data from a REST API with local data from an Oracle database. Tips and Tricks for dealing with REST APIs from an enterprise application point of view will wrap the session up.
Views: 346 Oracle Developers
Python Tutorial: CSV Module - How to Read, Parse, and Write CSV Files
 
16:12
In this Python Programming Tutorial, we will be learning how to work with csv files using the csv module. We will learn how to read, parse, and write to csv files. CSV stands for "Comma-Separated Values". It is a common format for storing information. Knowing how to read, parse, and write this information to files will open the door to working with a lot of data throughout the world. Let's get started. The code from this video can be found at: https://github.com/CoreyMSchafer/code_snippets/tree/master/Python-CSV ✅ Support My Channel Through Patreon: https://www.patreon.com/coreyms ✅ Become a Channel Member: https://www.youtube.com/channel/UCCezIgC97PvUuR4_gbFUs5g/join ✅ One-Time Contribution Through PayPal: https://goo.gl/649HFY ✅ Cryptocurrency Donations: Bitcoin Wallet - 3MPH8oY2EAgbLVy7RBMinwcBntggi7qeG3 Ethereum Wallet - 0x151649418616068fB46C3598083817101d3bCD33 Litecoin Wallet - MPvEBY5fxGkmPQgocfJbxP6EmTo5UUXMot ✅ Corey's Public Amazon Wishlist http://a.co/inIyro1 ✅ Equipment I Use and Books I Recommend: https://www.amazon.com/shop/coreyschafer ▶️ You Can Find Me On: My Website - http://coreyms.com/ My Second Channel - https://www.youtube.com/c/coreymschafer Facebook - https://www.facebook.com/CoreyMSchafer Twitter - https://twitter.com/CoreyMSchafer Instagram - https://www.instagram.com/coreymschafer/ #Python
Views: 286387 Corey Schafer
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: 103548 kudvenkat
Oracle APEX PWA Preview #2 - Using an APEX App Offline
 
00:52
Documentation is available on my blog: https://vmorneau.me/apex-pwa-part5 This video shows: Observations on a regular APEX application: * User turns off the Wi-Fi * User navigates to another page * Application crashes Observations on a regular APEX PWA application: * User turns off the Wi-Fi * Application indicates that there is no connectivity * User navigates to another page * Application is still usable Source Code: https://github.com/vincentmorneau/apex-pwa
Views: 331 Vincent Morneau
Service | Querying via the Oracle Service Cloud REST API
 
04:29
This tutorial applies to Rel 15.8, 15.11 and 16.2. It shows you how to write ROQL-style queries with the OSvC REST API.