How to Answer REAL Deloitte SQL INTERVIEW question.

Key Takeaways

[Music] one of my student who was interviewing for data analyst position told me that this was the first SQL interview question they were asked you have been given data in a CSV file you need to analyze this data using SQL but there is one restriction you cannot upload or import this data into the database at this point you might think what's so special about this we can analyze the data using Excel but the question specifically states that you must analyze the data using SQL let's see how to answer such an SQL interview question but first let's take a moment to thank the sponsor of this video none other than myself I have created an ebook with over 300 pages of pure knowledge covering 35 Plus SQL window functions that are incredibly useful for data analyst and SQL enthusiasts this book also include interview questions real world use cases and multiple choice questions to test your knowledge for my subscribers this book is available for just 250 rupees get your copy now the link is in the description and in the pinned comment to solve this I will use Oracle database I have a CSV file named stores unor sales underscore data which contains some data however the Restriction is that I cannot upload this data into my database so my Approach will be to use the concept of an external table an external table in Oracle allows you to read data from an external file such as a CSV text or other structured file format without loading it into the database to create this external file I first need to place my CSV file into a directory for that I have created a folder in my D drive and named it Oracle dataor storage now I will move my CSV file into this folder this is the same CSV file whose data we will use next let's move to vs code here I'll connect to the database using CIS user if you are unsure how to connect to Oracle database I have made a video covering installation and creating a connection in vs code you can find the link in the description now that I'm connected to the database the next step is to create a directory object that points to the folder contain our CSV file first write create or replace directory followed by the directory object me this can be anything in my case I'm naming it sales data Dr then use ads followed by the location of the folder that contains your Source data file once the directory object is created the next step is to Grant the necessary permission to our Oracle user in my case this Oracle user will be testore DB this will be the same schema into which I want to create my external table simply write Grant read comma write on directory followed by the name of the directory object we just created then the keyword two and the name of your Oracle database user in my case testore DB now I will connect to my database using testore DB schema now that the setup is complete the next step is to create our external table this is a simple create table statement I will include all the column names from the CSV file so that we can retrieve all data from the source file make sure these column names match exactly with those in the CSV file next after the closing bracket write the GLA organization external and start a pair of parenthesis the first thing in inside these parentheses will be the Clause type Oracle _ loader which makes our table an external table next specify the directory object we created to do that write the Clause default directory followed by the directory object name which in our case is csor dataor drir then write access parameters and start another pair of parenthesis the first parameter will will be record delimited by new line the second will be Fields terminated by comma I have enclosed the comma in a single code but you should use whatever delimiter is used in your source file next is optionally enclosed by double code this is useful when some strings in the CSV file are enclosed in double codes ensure that the double code sign is enclosed in single codes now we need to specify how how to handle missing field values right missing field values are null meaning missing Fields will be treated as null now outside these parenthesis write the keyword location followed by another pair of parentheses inside specify the source data file name which in my case is store uncore sales unod data.csv make sure to enclose the file name in single codes finally before the semicolon write reject limit unlimited this allows error handling without failing the query your external table has been created successfully now you can query this external table just like any other table in your database here is an example the only restriction is that you cannot insert or update data in this table other than that you can perform any operation you want and that's how you tackle this tricky SQL interview question subscribe to the channel for more videos on SQL interview questions thanks for watching this is Manish from rebellionrider.com