SQL Interview Questions and Answers
Best feasible SQL questions that could increase your confidence for your subsequent Database Developer task interview. Well, there are more, however this distilled set of questions covers all SQL functionalities, so that it will enable you to get better prepared to stand an interview. You could also enhance your SQL abilities with the high-quality SQL Tutorials encouraged for you.
Best SQL Interview Questions and Answers
Here we've indexed pinnacle SQL interview questions and solutions which might be frequently requested by way of the interviewer.
Basic Level Interview Questions
Question: What is the difference among DBMS and RDBMS?
Answer: DBMSs are software program applications that help you construct and maintain databases. RDBMS is a subset of DBMS, and it's far a database control system based totally at the relational model of the DBMS.
Question: Can we embed Pl/SQL in SQL? Justify your answers
Answer: PL/SQL is a procedural language, and it has one or more SQL statements in it. So SQL can be embedded in a PL/SQL block; however, PL/SQL can't be embedded in SQL as SQL executes a unmarried query at a time.
DECLARE /* this is a PL/SQL block */
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory /* this is the SQL statement embedded in the PL/SQL block */
WHERE product = 'TENNIS RACKET';
END;
Question: What do you mean via facts manipulation language - DML?
Answer: DML includes the maximum commonplace SQL statements to save, modify, delete, and retrieve information. They are SELECT, UPDATE, INSERT, and DELETE.
INSERT INTO table_name /* Insert is a DML statement */
VALUES (value, value, value …)
INSERT INTO customers /* data being inserted in the table customers */
VALUES (‘George’ , 'Washington' , 'Connecticut')
Answer: A join is used to question facts from a couple of tables based on the connection among the fields.
There are 4 kinds of joins:
Inner Join
Rows are lower back whilst there may be at least one fit of rows among the tables.
select first_name, last_name, order_date, order_amount
from customers c
inner join orders o
on c.customer_id = o.customer_id
/* customers and orders are two tables. Data will be displayed from the two tables where the customer_id from customer table matches
The customer_id from the orders table. */
Right Join
Right, be a part of returns all rows from the proper table and people which can be shared among the tables. If there are no matching rows within the left table, it'll nevertheless go back all of the rows from the proper table.
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
/* customers and orders are two tables. All rows from the Orders table is returned with matching rows from the Customers table if any */
Left Join
Left be part of returns all rows from the Left desk and people that are shared among the tables. If there are no matching rows in the right desk, it's going to nonetheless return all the rows from the left desk.
select first_name, last_name, order_date, order_amount
from customers c
left join orders o
on c.customer_id = o.customer_id
/* customers and orders are two tables. All rows from the customers table is returned with matching rows from the orders table if any */
Full Join
Full be part of go back rows when there are matching rows in any person of the tables. This approach it returns all the rows from the left-hand side table and all the rows from the proper-hand facet desk.
select first_name, last_name, order_date, order_amount
from customers c
full join orders o
on c.customer_id = o.customer_id
/* customers and orders are two tables. All rows from the Orders table and customer table are returned */
Question: What is the difference among CHAR and VARCHAR2 datatype in SQL?
Answer: CHAR is used to save fixed-period individual strings, and VARCHAR2 is used to save variable-duration man or woman strings.
For example, suppose you store the string ‘Database’ in a CHAR(20) field and a VARCHAR2(20) field.
The CHAR field will use 22 bytes (2 bytes for leading length).
The VARCHAR2 field will use 10 bytes only (8 for the string, 2 bytes for leading length).
Question: Explain SQL constraints?
Answer: Constraints are used to specify the regulations of statistics type in a desk.
They can be particular even as creating and changing the desk.
The following are the constraints in SQL:
NOT NULL - Restricts NULL price from being inserted right into a column.
CHECK - Verifies that every one values in a subject satisfy a circumstance.
DEFAULT - Automatically assigns a default cost if no value has been targeted for the sector.
UNIQUE - Ensures specific values to be inserted into the sphere.
INDEX - Indexes a subject supplying faster retrieval of records.
PRIMARY KEY - Uniquely identifies every document in a table.
FOREIGN KEY - Ensures referential integrity for a report in another table.
Question: What is a primary key, a overseas key, and a completely unique key
Answer:
The primary key is a discipline in the table which uniquely identifies a row. It can't be NULL
A overseas secret's a subject in one table, that's a primary key in some other table. A courting is created between the two tables by using referencing the foreign key of one table with the number one key of another desk.
In the instance under, the employee_id_ref inside the earnings desk is the overseas key.
Unique Key uniquely identifies a document in a desk. There may be many specific key constraints described on a desk.
EMP_ID EMP_NAME Government_ID
222 Harry 111-203-987
333 Stephan 789-456-123
444 Lan 745-562-321
In the table above Emp_id is the number one key but Government_id is the unique key. You might also want the Government_id to be particular for every employee. Since the information belongs to the government, you may now not want it to be the primary key.
Question: What is a cause?
Answer: Triggers are saved applications that get routinely executed when an occasion which include INSERT, DELETE, UPDATE(DML) declaration takes place. Triggers can also be evoked in reaction to Data definition statements(DDL) and database operations, as an instance, SERVER ERROR, LOGON.
create trigger dbtrigger
on database
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter table in this database'
rollback;
create trigger emptrigger
on emp
for
insert,update,delete
as
print'you can not insert,update and delete this table i'
rollback;
Intermediate Level Interview Questions
Question: What are ACID properties in a transaction
Answer: In order to hold consistency in a database ‘earlier than and after’ transactions, certain properties are followed. They are
Atomicity: This method the transaction ought to occur absolutely and cannot be left midway.
Consistency: To maintain integrity constraints as a result valid statistics enters the database
Isolation: Controls Concurrency
Durability: Once a transaction is dedicated it stays committed
Question: What is Dateadd SQL?
Answer: Dateadd is a feature this is used to feature a number to distinct part of the date and returns the changed date. Syntax- DATEADD (date_part, fee, input_date); Date component may be as below-
| date_part | abbreviations |
| Year | yy, yyyy |
| Quarter | qq, q |
| Month | mm, m |
| dayofyear | dy, y |
| Day | dd, d |
| Week | wk, ww |
| Hour | hh |
| Minute | mi, n |
| Second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Question: What is SAVEPOINT in transaction manipulate?
Answer: A SAVEPOINT is a factor in a transaction whilst you may roll the transaction returned to a certain factor with out rolling lower back the whole transaction.
SQL> SAVEPOINT A
SQL> INSERT INTO TEST VALUES (1,'Savepoint A');
1 row inserted.
SQL> SAVEPOINT B
SQL> INSERT INTO TEST VALUES (2,'Savepoint B');
1 row inserted.
SQL> ROLLBACK TO B;
Rollback complete.
SQL> SELECT * FROM TEST;
ID MSG
-------- -----------
1 Savepoint A
Question: What is a Natural Join?
Answer: A Natural be a part of via default is an Inner Join that creates an implicit be a part of primarily based at the commonplace columns inside the tables being joined:
A NATURAL JOIN can be an INNER join, a LEFT OUTER be part of, or a RIGHT OUTER be a part of. The default is INNER be part of.
If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:
SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)
Question: What is a Cross Join?
Answer: In a SQL move be a part of, a aggregate of every row from the two tables is protected within the result set. This is also referred to as Cross Product Join. For instance, if desk A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 2 hundred rows provided there may be NOWHERE clause within the SQL statement.
Question: What are the subsets of SQL?
Answer:
The following are the subsets of SQL
DDL(Data Definition Language): Includes SQL commands like CREATE, ALTER, and DELETE.
DML(Data Manipulation Language): Accesses and manipulates statistics Uses INSERT, UPDATE instructions
DCL(Data Control Language): Controls get right of entry to to the database. Uses commands like GRANT and REVOKE.
Question: What are the scalar features in SQL? Give an example?
Answer: Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows:
UCASE(): Converts the required discipline in top case
SELECT UCASE("SQL Tutorial is FUN!") AS UppercaseText;
UppercaseText
SQL TUTORIAL IS FUN!
LCASE(): Converts the specified field in lower case
Question: What is a cursor, and whilst do you operate it?
Answer: A cursor is a database object that is used to manipulate facts by using traversing row by means of row in a end result set. A cursor is used when you want to retrieve records, one row at a time from a result set and whilst you want to replace facts one row at a time.
DECLARE @CustomerId INT
,@Name VARCHAR(100)
,@Country VARCHAR(100)
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1
--DECLARE THE CURSOR FOR A QUERY.
DECLARE PrintCustomers CURSOR READ_ONLY
FOR
SELECT CustomerId, Name, Country
FROM Customers
--OPEN CURSOR.
OPEN PrintCustomers
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO
@CustomerId, @Name, @Country
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Counter = 1
BEGIN
PRINT 'CustomerID' + CHAR(9) + 'Name' + CHAR(9) + CHAR(9) + CHAR(9) + 'Country'
PRINT '------------------------------------'
END
--PRINT CURRENT RECORD. PRINT CAST(@CustomerId AS VARCHAR(10)) + CHAR(9) + CHAR(9) + CHAR(9) + @Name + CHAR(9) + @Country
--INCREMENT COUNTER.
SET @Counter = @Counter + 1
--FETCH THE NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO
@CustomerId, @Name, @Country
END
--CLOSE THE CURSOR.
CLOSE PrintCustomers
DEALLOCATE PrintCustomers
Question: What is a set-primarily based answer?
Answer: Cursors operate on character rows, and within the case of a fixed, it really works on a resultant set of statistics, which could be a desk/view or a be part of of each. The resultant set is an output of a SQL query.
Question: What is a forward cursor?
Answer: Forward cursors aid fetching of rows from start to stop from a result set. You can not go to the preceding row in the end result set.
Question: State one situation in which the set-based solution is tremendous over the cursor-primarily based solution
Answer: Set-primarily based solutions provide higher performance as they work on a result set and now not on one row at a time. They are concise and greater readable.
Question: What is normalization and what are the everyday forms
Answer: Normalization is a method in database design to decrease records redundancy and dependency. The database is divided into or more tables, and relationships are defined between them.
First Normal Form: Every file is unique in a desk and is identified by using a number one or a composite key
StudiD Name Phonenum
------------------
1 John 9176612345
1 John 9176645698
2 Susie 9176645789
3 Jim 9176696325
In the above table the sector ‘phonenum’ is a multi-valued attribute, so it isn't in 1NF.
Below Table is in 1NF as there is no multi-valued characteristic
StudiD Name Phonenum
------------------
1 John 9176612345
1 John 9176645698
2 Susie 9176645789
3 Jim 9176696325
Second Normal Form: The table have to be in First Normal Form, and it have to have a unmarried column as its number one key.
2NF tries to reduce the redundant data getting stored in memory. To deliver the above table in 2NF we split the desk into two tables
StudiD Name /* student table */
1. John
2 Susie
3. Jim
StudiD Phonenum /* studentphonenumber table */
------------------
1 9176612345
1 9176645698
2 9176645789
3 9176696325
Third Normal Form: The desk need to be in Second Normal Form and have to haven't any transitive useful dependencies. I.E., a non-key column need to no longer be dependent on another non-key column within the equal table.'
Consider the EMPLOYEE_DETAIL desk: This table is not inside the 1/3 regular shape due to the fact the fields emp_state
and emp_city rely upon emp_zip and not at the number one key emp_id.
| EMP_ID | EMP_NAME | EMP_ZIP | EMP_STATE | EMP_CITY |
| 222 | Harry | 201010 | CT | Monroe |
| 333 | Stephan | 02228 | TX | Dallas |
| 444 | Lan | 060007 | IL | Chicago |
The above desk is cut up into 2 tables and now the tables are inside the third everyday shape.
EMPLOYEE table:
| EMP_ID | EMP_NAME | EMP_ZIP |
| 222 | Harry | 201010 |
| 333 | Stephan | 02228 |
| 444 | Lan | 060007 |
EMPLOYEE_ZIP table:
| EMP_ZIP | EMP_STATE | EMP_CITY |
| 201010 | CT | Monroe |
| 02228 | TX | Dallas |
| 060007 | IL | Chicago |
Question: What is de-normalization, and when do you pass for it?
Answer: De-normalization is a technique once in a while used to enhance performance so the table design permits redundant statistics to avoid complex joins. If the utility involves heavy read operations, then de-normalization is used at the rate of the write operations performance.
Question: What are clustered indexes and non-clustered indexes?
Answer: A desk can have best one clustered index. In this form of index, it reorders the table based totally on the key values and physically stores them in that order.
The non-clustered index does no longer have the bodily ordering of the statistics in the desk it has a logical order.
CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score
ON student(gender ASC, total_score DESC)
The above script creates a clustered index named “IX_tblStudent_Gender_Score” at the student desk. This index is created on the “gender” and “total_score” columns. An index that is created on a couple of column is referred to as the “composite index”.
A non-clustered index doesn’t type the bodily statistics in the table. A non-clustered index is saved in one place, and table records is stored in any other vicinity. This lets in for more than one non-clustered index in keeping with table.
CREATE NONCLUSTERED INDEX IX_tblStudent_Name
ON student(name ASC)
The above script creates a non-clustered index at the “name” column of the student desk — the index kinds by using name in ascending order. The table information and index may be stored in distinctive places.
Question: What is T-SQL?
Answer: It is an extension of SQL(Structured Query Language) developed by using Sybase and utilized by Microsoft.
Question: What are system functions and supply one instance
Answer: System features are operations achieved at the database server, and values are again consequently. Example @@ERROR - Returns 0 if the previous Transact-SQL statement encountered no errors. Otherwise returns an error wide variety.
@@ERROR - Returns 0 if the previous Transact-SQL statement encountered no errors.
Otherwise returns an blunders wide variety.
Question: What is a transaction log?
Answer: A log is an audit trail record wherein the history of actions executed by way of the DBMS is stored.
Question: How do you keep database integrity in which deletions from one table will routinely cause deletions in any other desk?
Answer: ON DELETE CASCADE is a command that is used while deletions show up within the discern table, and all baby records are robotically deleted, and the kid table is referenced through the foreign key in the parent desk.
CREATE TABLE products
( product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25)
);
CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT,
min_level INT,
max_level INT,
CONSTRAINT fk_inv_product_id
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE CASCADE
);
The Products desk is the figure table and the inventory table is the child desk. If a productid is deleted from the determine desk all the stock statistics for that productid can be deleted from the kid table
Question: What is the distinction among SQL and MySQL?
Answer: SQL is a based question language used to get admission to the DBMS whereas MYSQL is an Open Source Relational DBMS.
Question: Can we use TRUNCATE with a WHERE clause?
Answer: No, we can not use TRUNCATE with the WHERE clause.
Question: Define COMMIT
Answer: When a COMMIT is utilized in a transaction all changes made within the transaction are written into the database completely.
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT TRANSACTION;
The above example deletes a job candidate in a SQL server.
Question: What does CHECK CONSTRAINT do?
Answer: Check Constraint limits the values that could input a column in a database table. It is used as an integrity constraint test.
The following SQL creates a CHECK constraint at the "Age" column while the "Persons" table is created. The CHECK constraint ensures that you can't have any individual beneath 18 years:
The syntax under is in MySQL.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
Question: What is a schema?
Answer: A schema is a group of database objects in a database for a particular user/owner. Objects may be tables, perspectives, indices and so on.
Question: How can you create an empty table from an existing table?
Answer:
CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2 ……column]
FROM EXISTING_TABLE_NAME [WHERE ]
Question: What is a composite key?
Answer: When more than one column is used to outline the number one key, it's far called a composite key.
Here is a SQL syntax to create a composite key in MySQL
CREATE TABLE SAMPLE_TABLE
(COL1 integer,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));
Question: How do you type records in a table?
Answer: The ORDER BY Clause is used to kind facts in a desk.
SELECT * FROM Emp ORDER BY salary;
By default, the facts are returned in ascending order.
Question: What is a shared lock?
Answer: When transactions are granted read get right of entry to to the equal records, they're given a shared lock. This enables studying the equal records, and facts is not updated until the shared lock is launched.
Question: What is a deadlock?
Answer: It is an undesirable scenario wherein or greater transactions are waiting indefinitely for each other to release the locks.
Below is an instance of a deadlock situation
Question: What is lock escalation?
Answer: Lock escalation is a technique of converting row or page locks into desk locks. It is an optimization approach utilized by RDBMS like SQL Server dynamically.
Question: What is SQL injection?
Answer: SQL injection is a code injection technique used to hack facts-driven applications.
Question: What are views, and why are they used?
Answer: SQL views are virtual tables made from one or extra tables. Views are a subset of facts; as a result, it can limit the diploma of publicity of statistics from the tables.
The following SQL creates a view that shows all clients from Brazil:
CREATE VIEW Brazil_Customers_view AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
You can question the view above as follows:
SELECT * FROM Brazil_Customers_view;
Question: How will we keep away from getting replica entries in a question?
Answer: The Select DISTINCT is used to get wonderful records from tables using a query
The following SQL assertion selects handiest the DISTINCT values from the "Country" column inside the "Customers" desk:
SELECT DISTINCT Country FROM Customers;
Question: Give an example of a evaluation operator in SQL
Answer: EQUAL TO written as = is used to evaluate information values
Question: What is a Subquery?
Answer: A subQuery is a SQL query nested into a bigger Query.
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDER BY first_name , last_name;
The question placed inside the parentheses is called a subquery. It is also referred to as an inner query or internal select. The query that includes the subquery is called an outer query or an outer select.
Question: What is a Non-correlated subquery
Answer: A Non-Correlated subquery is an independent query, and the output of subquery is substituted within the essential question.
Advance Interview Questions
Question: What is a SYSTEM Privilege?
Answer: Rights are given to a user, usually via the DBA, to perform a selected movement on the database schema objects like creating tablespaces.
The following are examples of system privileges that may be granted to users:
CREATE TABLE allows a grantee to create tables inside the grantee's schema.
CREATE USER permits a grantee to create customers inside the database.
CREATE SESSION allows a grantee to connect with an Oracle database to create a person session.
Question: What are Object Privileges?
Answer: An item-level privilege is a permission granted to a database consumer account or function to carry out some action on a database object. These object privileges consist of SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables, and so on.
The following examples are item privileges that may be granted to customers:
SELECT ON hr.Employees TO myuser
INSERT ON hr.Employees TO myuser
Question: What does the BCP command do?
Answer: The BCP (Bulk Copy) is a application or a tool that exports/imports facts from a desk right into a document and vice versa
Question: What is a NULL Value subject?
Answer: A NULL fee is a discipline with No Value.
Question: What does the VARIANCE characteristic do?
Answer: This characteristic returns the VARIANCE of a set of numbers:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT VARIANCE(sal)
2 FROM emp;
VARIANCE(SAL)
-------------
759056.818
Question: What is the position of GRANT and REVOKE instructions?
Answer: The GRANT command allows privileges on the database objects and the REVOKE command eliminates them. They are DCL instructions
GRANT CREATE ANY TABLE TO username
GRANT sysdba TO username
GRANT DROP ANY TABLE TO username
REVOKE CREATE TABLE FROM username
Question: What is a UNION operator?Answer: The UNION operator combines the effects of two or greater Select statements via doing away with reproduction
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
Question: Where are saved techniques stored within the database?
Answer: Stored Procedures are stored within the Data Dictionary of the Database.
Question: Can we name Stored Procedure internal Stored Procedure?
Answer: Yes, we can call a stored procedure from some other stored technique. For instance, Procedure2 is the system that is referred to as Procedure1. Both Procedure1 and Procedure2 can have commercial enterprise good judgment applied in it.
Create PROCEDURE Procedure1
AS BEGIN
Exec Procedure2
END
Question: Does the records stored inside the stored procedure growth access time or execution time? Explain
Answer: Data stored in saved techniques can be retrieved a whole lot faster than the information saved in the SQL database. Data can be precompiled and stored in saved techniques. This reduces the time gap between question and compiling because the records has been pre-compiled and stored inside the technique.
Question: Can a Stored Procedure incorporate a go back value?
Answer: Procedures may or won't go back values.
Question: Can a View be active if the Base table is dropped?
Ans: No, the view cannot be active within the discern table is dropped.
Question: What is a One-Many Relationship in SQL?
Answer: In a One-Many relationship, a file in One Table may be associated or associated with Many statistics in any other table.
Question: Distinguish between a table and a discipline in SQL.
Answer: The series of information organized within the form of columns and rows refers back to the desk. The variety of columns in a table refers to the sphere.
Table: Employee_Details
Fields: Emp_id, Emp_name, Emp_dept, Emp_salary
Question: What is information integrity?
Answer: Data integrity defines the accuracy, consistency, and reliability of information this is stored inside the database.
There are 4 styles of facts integrity:
Row integrity
Column integrity
Referential integrity
User-described integrity
Question: What are entities and dating
Answer:
Entity: A individual, place, or any real-global element that may be represented as a desk is called an entity.
Example: Employee desk represents the details of an worker in an organisation.
Relationship: Relationship defines the dependency that entities percentage among each different.
Example: Employee call, identity, earnings may belong to the same or extraordinary tables.
Question: Write the SQL query to get the present day date.
Answer:
SELECT CURDATE();
Question: What is the TRUNCATE command? How is it different from the DELETE command?
Answer:
| DELETE | TRUNCATE |
| DML command | DDL command |
| We can use WHERE clause | We cannot use WHERE clause |
| Deletes a row from the table. | Deletes all rows from the table. |
| We can rollback. | We cannot rollback. |
Question: What is the distinction between null, 0 and blank area?
Answer: NULL refers to a fee that is unknown, now not to be had, unapplicable or unassigned. While zero is more than a few and clean area is handled as a man or woman.
Question: Which feature is used to return the remainder in a department operator in SQL?
Answer: The MOD characteristic returns the remainder inside the department operation.
Question: What are case manipulation features?
Answer: Case manipulation features converts the prevailing statistics inside the desk to lower, top or blended case characters.
Question: What are the different case manipulation functions in SQL?
Answer:
LOWER: converts all of the characters to lowercase.
UPPER: converts all the characters to uppercase.
INITCAP: converts preliminary character of each word to uppercase
Question: What are the individual manipulation functions?
Answer: Character manipulation features adjust, extract and alternate the man or woman string.
Question: What are the extraordinary person manipulation features?
Answer:
CONCAT: joins two or more string values.
SUBSTR: extracts string of a selected period.
LENGTH: returns the period of the string
INSTR: returns the location of the precise individual.
LPAD: padding of the left-side individual cost for right-justified cost.
RPAD: padding of proper-side character price for left-justified price.
TRIM: removes the defined man or woman from beginning and quit or both.
REPLACE: replaces a specific sequence of characters with some other sequence of characters.
Question: Define inconsistent dependency.
Answer: The issue of gaining access to records as the direction can be damaged or missing defines inconsistent dependency. Inconsistent dependency enables customers to look for information within the incorrect unique table which afterward consequences in an errors as an output.
Question: What are the exclusive operators in SQL?
Answer:
Arithmetic
Comparison
Logical
Question: What are GROUP features? Why can we want them?
Answer: Group functions work on a hard and fast of rows and return a single result per institution. The popularly used group features are AVG, MAX, MIN, SUM, VARIANCE, COUNT
Question: Distinguish between BETWEEN and IN conditional operators.
Answer: BETWEEN- Displays the rows based totally on range of values
IN- Checks for values contained in a specific set of values.
Example:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
SELECT * FROM students where ROLL_NO IN (8,15,25);
Question: What is the MERGE announcement?
Answer: The assertion permits conditional updates or inserts into the desk. It updates the row if it exists or inserts the row if it does now not exist.
Question: Explain recursive stored technique.
Answer: Stored technique calling itself until it reaches some boundary condition is a recursive saved procedure. It permits the programmers to apply a fixed of code n number of instances.
Question: How can dynamic SQL be accomplished?
Answer: It can be accomplished with the aid of the subsequent approaches:
By executing the question with parameters.
By the use of EXEC
By the use of sp_executesql
Question: Define Cursor
Answer: Cursor is a control that permits traversal over the rows and records of the desk. It acts as a pointer to a row in a hard and fast of rows. It is used for traversing like retrieval, addition, and removal of database statistics.
Question: What is the saved manner?
Answer: It is a characteristic which includes many SQL statements to get entry to the database machine. Several SQL statements are consolidated into a stored method and are accomplished wherever and every time required.
Question: What do you recognize by way of Auto Increment?
Answer: This keyword lets in a brand new unique wide variety to be generated each time a brand new file is inserted into the desk. It may be used in which we need the PRIMARY KEY.
Question: What is a Data warehouse?
Answer: Data from more than one assets of statistics is saved in a important repository referred to as Data warehouse. Data warehouses have subsets of statistics known as facts marts. The records saved is transformed and used for on line mining and processing.
Question: What are consumer-described functions?
Answer: Functions written to apply the specific logic each time required are user-described features. It avoids redundancy via fending off writing the equal good judgment again.
Question: Mention the types person-defined functions?
Answer: There are 3 forms of person-described features:
Scalar functions
Inline Table-valued functions
Multi declaration valued features
Question: What is ALIAS command?
Answer: This command provides every other name to a desk or a column. It may be utilized in WHERE clause of a SQL question the use of the as key-word.
Example:
SELECT S.StudentID, E.Result from student S, Exam as E where S.StudentID = E.StudentID
S and E are alias names for pupil desk and examination table respectively.
Question: What is Collation?
Answer: Collation is described because the set of guidelines that determines how to shop and compare statistics.
Question: Mention the one-of-a-kind sorts of collation sensitivity.
Answer: The following are the kinds of collation sensitivity:
Case
Kana
Width
Accent
Question: What are STUFF and REPLACE functions?
Answer:
STUFF: overwriteS current man or woman or inserts a string into any other string.
Syntax:
STUFF(string_expression,start, length, replacement_characters)
REPLACE: replaces the existing characters of all the occurrences.
Syntax:
REPLACE (string_expression, search_string, replacement_string)
Conclusion
Structured Query Language or SQL is used to manipulate and control data in a database. With the assist of the above-stated Best SQL Interview Questions, you can actually strive an interview for Database Administrator, SQL developer, and even crack SQL Server Certification.
Still hungry for greater SQL interview questions? This udemy route will put together you extra for the coming SQL interview: two hundred+ SQL interview Questions.
Here is a outstanding e book for upgrading your SQL Skills for interviews: SQL in 10 Minutes, Sams Teach Yourself.
Have any more inquiries to share? Or every other interview guidelines? Comment beneath!!
