Are you preparing for your SQL (Structured Query Language) developer interview? Then you are at the right place!
Relational Databases continue to be the most extensively used database management systems. Thus, SQL skills are indispensable in most of the job roles. There is a high demand among the employers for individuals who possess SQL skills.
In this episode of TechBeans, Sailee Mohite- Technical Associate at Clover Infotech, speaks about 5 basic SQL interview questions that you may be asked in a job interview. These questions and answers will help you to brush up your SQL skills, gain confidence and be job ready!
Here’s the video…
Here are the questions and answers:
- What are the different types of SQL commands?
SQL Commands can be grouped into 4 key categories based on their functionality:
Data Definition Language: DDL is that part of SQL which defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are:
- Create table
- Alter table
- Drop table
Data Manipulation Language: DML is used to manipulate the already existing data in the database. That is, it helps users retrieve and manipulate the data. It is used to perform operations like inserting data into the database through the insert command, updating the data with the update command, and deleting the data from the database through the delete command.
Data Control Language: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and also to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.
Transaction Control Language: It is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction into logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.
- State the key differences between HAVING and WHERE clause
|SR.NO.||WHERE Clause||HAVING Clause|
|1||WHERE Clause is used to filter the records from the table based on the specified condition.||HAVING Clause is used to filter record from the groups based on the specified condition.|
|2||WHERE Clause can be used without GROUP BY Clause||HAVING Clause cannot be used without GROUP BY Clause|
|3||WHERE Clause implements in row operations||HAVING Clause implements in column operation|
|4||WHERE Clause cannot contain aggregate function||HAVING Clause can contain aggregate function|
|5||WHERE Clause can be used with SELECT, UPDATE, DELETE statement.||HAVING Clause can only be used with SELECT statement.|
- What is a Primary Key?
Primary Key is a field or a combination of fields that identify a record uniquely. It is a column or set of columns that are unique. In other words, every value is unique for Primary Key.
Key Rules for Primary Key
- Each table can have only one SQL Primary Key.
- All the values are unique and Primary key SQL value can uniquely identify each row.
- Primary Key cannot be NULL.
- What is a Foreign Key? How to create a Foreign Key in SQL?
A foreign key SQL could be a column or a set of columns that provides the link between data among two tables in the RDBMS. This would be a cross-reference platform between two tables because it references the primary key of the second table and maintains the link between tables. The foreign key is an important part of the database design similar to the primary keys and both had the capability to control data updates in more interesting ways. It strongly supports data integrity and avoids modification that could violate the data integrity rules.
To create the Foreign Key in the SQL, we can use two popular techniques.
Inline Method In the case of the inline method, this command can be used to create a Foreign key SQL
Here, in the syntax, you can see the keyword CREATE, then you have to give the name of the table and an open bracket too. This is the general form of syntax and necessary to understand when working on Foreign Keys.
In the case of out-of-the-line method, this syntax can be used to create a Foreign key SQL
It will start by declaring the name of the table and all the columns you need. However, columns are defined within brackets, add a keyword in between i.e. CONSTRAINT. It will tell you that a keyword has been defined. Now give a name to the constraint that suits the condition with maximum characters of length up to 30.
- What is an Index?
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.
The basic syntax of a CREATE INDEX is:
Indexes help speed up searching in the database. If there is no index on any column in the WHERE clause, then the SQL server has to skim through the entire table and check each and every row to find matches, which might result in slow operation on large data.
Hope you enjoyed watching the video and we hope that you are now better equipped to face a job interview! Please note that these are some of the key questions and you should refer to more material available online that can enable you to easily crack the interview.