Less asked SQL questions
We have already covered queries on joins, aggregate functions, sub-queries, running sum, etc.
This article will concentrate on the theoretical part of SQL which are less asked but are important to know about
1.What are the different types of statements supported in SQL?
There are three types of statements in SQL:-
a. DDL – Data Definition Language
b. DML – Data Manipulation Language
c. DCL – Data Control Language
2. What is DDL?
It is used to define the database structure such as tables. It includes 3 commands:-
a. Create – Create is for creating tables
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);
b. Alter – Alter table is used to modifying the existing table object in the database.
ALTER TABLE table_name
ADD column_name datatype
c. Drop – If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once the table is dropped, you can’t get it back
3. What is DML?
Data Manipulation Language is used to manipulate the data in records. Commonly used DML commands are Update, Insert and Delete. Sometimes SELECT command is also referred as a Data Manipulation Language.
4. What is DCL?
Data Control Language is used to control the privileges by granting and revoking database access permission
5. What is Normalization? How many Normalization forms are there?
Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.
There are 5 forms of Normalization
First Normal Form (1NF): It removes all duplicate columns from the table. Creates a table for related data and identifies unique column values
Second Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using a primary key
Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF
6. Full Outer Join is a combination of which of the following:-
a. Left Outer and Left Inner Join
b. Left Outer and Right Inner Join
c. Left Outer and Right Outer Join
d. Left Outer and Right Outer Join
A. Full Outer Join is a combination of Left Outer and Right Outer Join in SQL
7. What is unnest in SQL?
In General, unnesting comes into picture when you are dealing with Big Data because there you will have data in different formats. You might have to deal with an array in a particular column.
Array is nothing but a sequence of data points under one heading.
Let’s take an example of creating an array in BigQuery
Select ['Jan','Feb','Mar'] as Month
Select [0,0,1] as Bool
SELECT * FROM UNNEST(['a', 'b', 'c', 'd', 'e', 'f'])AS alpha WITH OFFSET AS offset ORDER BY offset;
The above code will create the following table
alpha offset
a 0
b 1
c 2
d 3
e 4
f 5
8. What is RDBMS?
RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.
9. What is NoSQL ?
NoSQL is a non-relational DMS, that does not require a fixed schema, avoids joins, and is easy to scale. NoSQL database is used for distributed data stores with humongous data storage needs.
10. What is a unique key?
A Unique key constraint uniquely identified each record in t
A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.
11. What is a foreign key?
A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.
11. Table 1 has only one column ‘Col1’ having values 1,2,3,4
Table2 has only one column ‘Col2’ having values 1,1,4,6
Inner Join on the two table will get you how many rows?
Inner Join will get you 3 rows
1 1
1 1
4 4
The duplicate row will repeat
12. Left Outer Join in the same table?
It will get you the following rows given that the query looks like the one below
Select t1.Col1,t2.Col2 From Table1 t1 Left Outer Join Table2 t2 on (t1.Col1 = t2.Col2)
Left Outer Join Output
t1.Col1 t2.Col2
1 1
1 1
2 NULL
3 NULL
4 4
13. Add a NULL in the first table i.e. Table1
Table1[Col1] = 1,2,3,4,NULL
Table2[Col1] = 1,1,4,5
Table1 Left Outer Join Table2
Approach
The NULL will impact the number of rows being created
Output
t1.Col1 t2.Col1
1 1
1 1
2 NULL
3 NULL
4 4
NULL NULL
14. What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.
15. What is Denormalization.
DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.
16. What is a stored procedure?
Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.
17. What is a trigger?
A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.
18. What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A non-clustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
19.What is Union, minus and Interact commands?
UNION operator is used to combine the results of two tables, and it eliminates duplicate rows from the tables.
MINUS operator is used to return rows from the first query but not from the second query. Matching records of first and second query and other rows from the first query will be displayed as a result set.
INTERSECT operator is used to return rows returned by both the queries.
20. How can you create an empty table from an existing table?
Select * into Employee from Department where 0=1
This will ONLY copy the structure of the table
We will keep on adding tricky questions. But the last 4 days SQL queries should give you a good base to start with.
Keep Learning 🙂
The Data Monk
The Data Monk services
We are well known for our interview books and have 70+ e-book across Amazon and The Data Monk e-shop page . Following are best-seller combo packs and services that we are providing as of now
- YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
Link – The Data Monk Youtube Channel - Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
Link – The Data Monk website - E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions. Do check it out
Link – The Data E-shop Page - Instagram Page – It covers only Most asked Questions and concepts (100+ posts). We have 100+ most asked interview topics explained in simple terms
Link – The Data Monk Instagram page - Mock Interviews/Career Guidance/Mentorship/Resume Making
Book a slot on Top Mate
The Data Monk e-books
We know that each domain requires a different type of preparation, so we have divided our books in the same way:
1. 2200 Interview Questions to become Full Stack Analytics Professional – 2200 Most Asked Interview Questions
2.Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions
3. 30 Days Analytics Course – Most Asked Interview Questions from 30 crucial topics
You can check out all the other e-books on our e-shop page – Do not miss it
For any information related to courses or e-books, please send an email to [email protected]
