SQL Interview Questions and Answers in 2022 (2022)

Table of Contents
SQL Interview Questions for Freshers 1. What is Database? 2. What is DBMS? 3. What is RDBMS? How is it different from DBMS? 4. How to create a table in SQL? 3. How to delete a table in SQL? 4. How to change a table name in SQL? 5. How to delete a row in SQL? 6. How to create a database in SQL? 7. What is Normalization in SQL? 8. What is join in SQL? 8. How to insert date in SQL? 9. What is Primary Key in SQL? 10. How do I view tables in SQL? 11. What is PL/SQL? 12. How can I see all tables in SQL? 13. What is ETL in SQL? 14. How to install SQL? 15. What is the update command in SQL? 16. How to rename column name in SQL Server? 17. What are the types of SQL Queries? 18. Write a Query to display the number of employees working in each region? 19. What are Nested Triggers? 20. Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000. 21. Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 asc; 22. What is OLTP? 23. What is Data Integrity? 24. What is OLAP? 25. Find the Constraint information from the table? 26. Can you get the list of employees with same salary? 27. What is an alternative for TOP clause in SQL? 28. Will following statement give error or 0 as output? SELECT AVG (NULL) 29. What is the Cartesian product of the table? 30. What is a schema in SQL? 31. How to delete a column in SQL? 32. What is a unique key in SQL? 33. How to implement multiple conditions using WHERE clause? SQL Interview Questions for Experienced Professionals 1. What is SQL injection? 2. What is a trigger in SQL? 3. How to insert multiple rows in SQL? 4. How to find the nth highest salary in SQL? 5. How to copy table in SQL? 6. How to add a new column in SQL? 7. How to use LIKE in SQL? 8. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures? 9. Can we disable a trigger? If yes, How? 10. What is a Live Lock? 11. How to fetch alternate records from a table? 12. Define COMMIT and give an example? 13. Can you join table by itself? 14. Explain Equi join with example. 15. How do we avoid getting duplicate entries in a query? 16. How can you create an empty table from an existing table? 17. Write a Query to display odd records from student table? 18. Explain Non Equi Join with example? 19. How can you delete duplicate records in a table with no primary key? 20. Difference between NVL and NVL2 functions? 21. What is the difference between clustered and non-clustered indexes? 22. What does this query says? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION]; 23. Where MyISAM table is stored? 24. What does myisamchk do? 25. What is ISAM? 26. What is Database White box testing? 27. What are the different types of SQL sandbox? 28. What is Database Black Box Testing? 29. Explain Right Outer Join with Example? 30. What is a Subquery? SQL Interview Questions for Developers 1. How to find duplicate records in SQL? 2. What is Case WHEN in SQL? 3. How to find 2nd highest salary in SQL? 4. How to delete duplicate rows in SQL? 5. What is cursor in SQL? 6. How to create a stored procedure using SQL Server? 7. How to create an index in SQL? 8. How to change column data-type in SQL? 9. Difference between SQL and NoSQL databases? SQL Joins Interview Questions 1. How to change column name in SQL? 2. What is a view in SQL? 3. How to drop a column in SQL? 4. How to use BETWEEN in SQL? Advanced SQL Interview Questions 1. What are the subsets of SQL? 2. Difference between CHAR and VARCHAR2 datatype in SQL? 3. How to sort a column using a column alias? 4. Difference between COALESCE() & ISNULL() ? 5. What is “Trigger” in SQL? 6. Write a Query to display employee details along with age? 7. Write a Query to display employee details along with age? 8. Write an SQL query to get the third maximum salary of an employee from a table named employee_table. 9. What are aggregate and scalar functions? 10. What is a deadlock? 11. Explain left outer join with example? 12. What is SQL injection? 13. What is an UNION operator? 14. Explain SQL Constraints? 15. What is ALIAS command? 16. What are Group Functions? Why do we need them? 17. How can dynamic SQL be executed? 18. What is the usage of NVL() function? 19. Write a Query to display employee details belongs to ECE department? 20. What are the main differences between #temp tables and @table variables and which one is preferred ? 21. What is CLAUSE? 22. What is recursive stored procedure? 23. What does the BCP command do? 24. What is a Cross Join? 25. Which operator is used in query for pattern matching? 26. Write a SQL query to get the current date? 27. State the case maniplation functions in SQL? 28. How to add a column to an existing table? 29. Define lock escalation? 30. How to store Videos inside SQL Server table ? 31. State the order of SQL SELECT? 32. What is the difference between IN and EXISTS? 33. How do you copy data from one table to another table ? 34. List the ACID properties that makes sure that the database transactions are processed 35. What will be the output of the following Query, provided the employee table has 10 records? 36. What do you mean by Stored Procedures? How do we use it? 37. What does GRANT command do? 38. What does First normal form do? 39. How to add e record to the table? 40. What are the different tables present inMySQL? 41. What is BLOB and TEXT in MySQL? 42. What is the use of mysql_close()? 43. How do you return a hundred books starting from 25th? 44. How would you select all the users, whose phone number is NULL? 45. How do you run batch mode in mysql? 46. Write an SQL query to show the second highest salary from a table. 47. Write an SQL query to fetch three max salaries from a table. 48. What is the difference between NOW() and CURRENT_DATE()? 49. How can we convert between Unix & MySQL timestamps? 50. What are the nonstandard string types? 51. What is the group by clause used for? 52. How do you get the last id without the max function? 53. Write a SQL query to fetch only even rows from the table. 54. Write a SQL query to create a new table with data and structure copied from another table. 55. What are the different types of Collation Sensitivity? 56. Write a query to find out the data between range? 57. How to calculate the number of rows in a table without using the count function? 58. What is wrong with the following query? SELECT empName FROM employee WHERE salary <> 6000 59. Will the following statements execute? if yes what will be output? SELECT NULL+1 SELECT NULL+’1′ 60. SQL vs PL/SQL 61. SQL having vs where 62. NoSQL vs SQL 63. SQL vs TSQL 64. MySQL vs SQL Server 65. MongoDB vs SQL SQL Server Interview Questions 1. What is SQL server? 2. How to install SQL Server? 3. How to create a stored procedure in SQL Server? 4. How to install SQL Server 2008? 5. How to install SQL Server 2017? 6. How to restore the database in SQL Server? 7. How to install SQL Server 2014? 8. How to get the connection string from SQL Server? 9. How to install SQL Server 2012? 10. What is cte in SQL Server? 11. How to change SQL Server password? 12. How to delete duplicate records in SQL Server? 13. How to uninstall SQL Server? 14. How to check SQL Server version? 15. How to rename column name in SQL Server? 16. What is the stored procedure in SQL Server? 17. How to create a database in SQL Server? 18. What is an index in SQL Server? 19. How to create the table in SQL Server? 20. How to connect to SQL Server? 21. How to delete duplicate rows in SQL Server? 22. How to download SQL Server? 23. How to connect SQL Server management studio to the local database? 24. How to change column name in SQL Server? 25. How to download SQL Server 2014? 26. How to uninstall SQL Server 2014? 27. How to find server name in SQL Server? 28. How to start SQL Server? 29. What is the case when in SQL Server? 30. How to install SQL Server management studio? 31. How to write a stored procedure in SQL Server? 32. How to open SQL Server? 33. How to connect SQL Server? 34. What is replication in SQL Server? 35. How to open SQL Server configuration manager? 36. What is a collation in SQL Server? 37. How to use SQL Server? 38. What is a function in SQL Server? 39. How to find nth highest salary in SQL Server without using a subquery 40. How to install SQL Server in Windows 10? 41. How to create a temp table in SQL Server? PostgreSQL Interview Questions 1. What is PostgreSQL? 2. List different datatypes of PostgreSQL? 3. What are the Indices of PostgreSQL? 4. What are tokens in PostgreSQL? 5. How to create a database in PostgreSQL? 6. How to create a table in PostgreSQL? 7. How can we change the column datatype in PostgreSQL? 8. Compare ‘PostgreSQL’ with ‘MongoDB’ 9.What is Multi-Version concurrency control in PostgreSQL? 10. What is pgadmin? 11. How do you delete the database in PostgreSQL? 12. What does a schema contain? 13. What is the square root operator inPostgreSQL? 14. What are the main differences between SQL and PostgreSQL? 15. How are the stats updated in Postgresql? 16. What Is A Candid? 17. What is Cube Root Operator (||/) in PostgreSQL? 18. Explain Write-Ahead Logging? 19. What is a non-clustered index? 20. How is security ensured in PostgreSQL? Frequently Asked Questions in SQL To Conclude FAQs Related content Videos

SQL Interview Questions and Answers in 2022 (1)

If you’re preparing for an upcoming SQL Interview, you’re at the right place! This blog on SQL Interview Questions has been divided into various sections. Whether you’re a fresher, a developer, or an experienced working professional, there are SQL Interview Questions to meet your needs and help you ace your interview!

One can be confident in cracking interviews by working on different projects. Check out SQL Projects For Beginners that will adequately help you in understanding different concepts of SQL with source code.

Let’s get started!

  1. SQL Interview Questions for Freshers
  2. SQL Interview Questions for Experienced Professionals
  3. SQL Interview Questions for Developers
  4. SQL Joins Interview Questions
  5. Advanced SQL Interview Questions
  6. SQL Server Interview Questions
  7. PostgreSQL Interview Questions
  8. Frequently Asked Questions in SQL

Relational Databases continue to be the most extensively used database management systems. Accessing and analyzing the data is the topmost need these days and SQL enables you to do just that. There is a high demand among employers for individuals who possess SQL skills. It is therefore absolutely essential for you to prepare yourself with the frequently asked SQL Interview questions if you want to land your dream job!

SQL Interview Questions for Freshers

1. What is Database?

A database is a system that helps in collecting, storing and retrieving data. Databases can be complex, and such databases are developed using design and modelling approaches.

2. What is DBMS?

DBMS stands for Database Management System which is responsible for the creating, updating, and managing of the database.

3. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System that stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables.

4. How to create a table in SQL?

The command to create a table in sql is extremely simple:

 CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype, ....);

We will start off by giving the keywords, CREATE TABLE, then we will give the name of the table. After that in braces, we will list out all the columns along with their datatypes.

For example, if we want to create a simple employee table:

CREATE TABLE employee (name varchar(25),age int,gender varchar(25), ....);

3. How to delete a table in SQL?

There are two ways to delete a table from sql: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command:

DROP TABLE table_name;

The above command will completely delete all the data present in the table along with the table itself.

But if we want to delete only the data present in the table but not the table itself, then we will use the truncate command:

DROP TABLE table_name ;

4. How to change a table name in SQL?

This is the command to change a table name in SQL:

ALTER TABLE table_nameRENAME TO new_table_name;

We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

For example, if we want to change the “employee” table to “employee_information”, this will be the command:

ALTER TABLE employeeRENAME TO employee_information;

5. How to delete a row in SQL?

We will be using the DELETE query to delete existing rows from the table:

DELETE FROM table_nameWHERE [condition];

We will start off by giving the keywords DELETE FROM, then we will give the name of the table, after that we will give the WHERE clause and give the condition on the basis of which we would want to delete a row.

For example, from the employee table, if we would like to delete all the rows, where the age of the employee is equal to 25, then this will the command:

DELETE FROM employeeWHERE [age=25];

6. How to create a database in SQL?

A database is a repository in sql, which can comprise of multiple tables.

This will be the command to create a database in sql:

CREATE DATABASE database_name.

7. What is Normalization in SQL?

Normalization is used to decompose a larger, complex table into simple and smaller ones. This helps us in removing all the redundant data.

Generally, in a table, we will have a lot of redundant information which is not required, so it is better to divide this complex table into multiple smaller tables which contains only unique information.

First normal form:

A relation schema is in 1NF, if and only if:

– All attributes in the relation are atomic(indivisible value)

-And there are no repeating elements or group of elements.

Second normal form:

A relation is said to be in 2NF, if and only if:

It is in 1st Normal Form.

No partial dependency exists between non-key attributes and key attributes.

Third Normal form:

A relation R is said to be in 3NF if and only if:

It is in 2NF.

No transitive dependency exists between non-key attributes and key attributes through another non-key attribute

8. What is join in SQL?

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

INNER JOIN − Returns rows when there is a match in both tables.

LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

FULL OUTER JOIN − Returns rows when there is a match in one of the tables.

SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN (CROSS JOIN) − Returns the Cartesian product of the sets of records from the two or more joined tables.

INNER JOIN:

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.colnFROM table1INNER JOIN table2ON table1.commonfield = table2.commonfield;

LEFT JOIN:

The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.colnFROM table1LEFT JOIN table2ON table1.commonfield = table2.commonfield;

RIGHT JOIN:

The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.colnFROM table1RIGHT JOIN table2ON table1.commonfield = table2.commonfield;

FULL OUTER JOIN:

The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SYNTAX:

SELECT table1.col1, table2.col2,…, table1.colnFROM table1Left JOIN table2ON table1.commonfield = table2.commonfield;UnionSELECT table1.col1, table2.col2,…, table1.colnFROM table1Right JOIN table2ON table1.commonfield = table2.commonfield;

SELF JOIN:

The SELF JOIN joins a table to itself; temporarily renaming at least one table in the SQL statement.

SYNTAX:

SELECT a.col1, b.col2,..., a.colnFROM table1 a, table1 bWHERE a.commonfield = b.commonfield;

8. How to insert date in SQL?

If the RDBMS is MYSQL, this is how we can insert date:

"INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2020-9-10')";

9. What is Primary Key in SQL?

Primary Key is a constraint in SQL. So, before understanding what exactly is a primary key, let’s understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level.

Let’s look at the different types of constraints which are present in SQL:

ConstraintDescription
NOT NULLEnsures that a column cannot have a NULL value.
DEFAULTProvides a default value for a column when none is specified.
UNIQUEEnsures that all the values in a column are different
PRIMARYUniquely identifies each row/record in a database table
FOREIGNUniquely identifies a row/record in any another database table
CHECKThe CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEXUsed to create and retrieve data from the database very quickly.

You can consider Primary Key constraint to be a combination of UNIQUE and NOT NULL constraint. This means that if a column is set as a primary key, then this particular column cannot have any null values present in it and also all the values present in this column must be unique.

10. How do I view tables in SQL?

To view tables in SQL, all you need to do is give this command:

Show tables;

11. What is PL/SQL?

PL SQL stands for Procedural language constructs for Structured Query Language. PL SQL was introduced by Oracle to overcome the limitations of plain sql. So, pl sql adds in procedural language approach to the plain vanilla sql.

One thing to be noted over here is that pl sql is only for oracle databases. If you don’t have an Oracle database, then you cant work with PL SQL.

While, with the help of sql, we were able to DDL and DML queries, with the help of PL SQL, we will be able to create functions, triggers and other procedural constructs.

12. How can I see all tables in SQL?

Different database management systems have different queries to see all the tables.

To see all the tables in MYSQL, we would have to use this query:

show tables;

This is how we can see all tables in ORACLE:

SELECT table_nameFROM User_tables;

This is how we can extract all tables in SQL Server:

SELECT *FROM Information_schema.tables;

13. What is ETL in SQL?

ETL stands for Extract, Transform and Load. It is a three step process, where we would have to start off by extracting the data from sources. Once we collate the data from different sources, what we have is raw data. This raw data has to be transformed into tidy format, which will come in the second phase. Finally, we would have to load this tidy data into tools which would help us to find insights.

14. How to install SQL?

SQL stands for Structured Query Language and it is not something you can install. To implement sql queries, you would need a relational database management system. There are different varieties of relational database management systems such as:

  • ORACLE
  • MYSQL
  • SQL Server

Hence, to implement sql queries, we would need to install any of these Relational Database Management Systems.

15. What is the update command in SQL?

The update command comes under the DML(Data Manipulation Langauge) part of sql and is used to update the existing data in the table.

UPDATE employeesSET last_name=‘Cohen’WHERE employee_id=101;

With this update command, I am changing the last name of the employee.

16. How to rename column name in SQL Server?

When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.

17. What are the types of SQL Queries?

We have four types of SQL Queries:

  • DDL (Data Definition Language): the creation of objects
  • DML (Data Manipulation Language): manipulation of data
  • DCL (Data Control Language): assignment and removal of permissions
  • TCL (Transaction Control Language): saving and restoring changes to a database

Let’s look at the different commands under DDL:

CommandDescription
CREATECreate objects in the database
ALTERAlters the structure of the database object
DROPDelete objects from the database
TRUNCATERemove all records from a table permanently
COMMENTAdd comments to the data dictionary
RENAMERename an object

18. Write a Query to display the number of employees working in each region?

SELECT region, COUNT(gender) FROM employee GROUP BY region;

19. What are Nested Triggers?

Triggers may implement DML by using INSERT, UPDATE, and DELETE statements. These triggers that contain DML and find other triggers for data modification are called Nested Triggers.

20. Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000.

By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000 and <=10000.

SELECT FullName FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)

21. Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 asc;

“Order by 2” is valid when there are at least 2 columns used in SELECT statement. Here this query will throw error because only one column is used in the SELECT statement.

22. What is OLTP?

OLTP stands for Online Transaction Processing. And is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency.

23. What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

24. What is OLAP?

OLAP stands for Online Analytical Processing. And a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations.

25. Find the Constraint information from the table?

There are so many times where user needs to find out the specific constraint information of the table. The following queries are useful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;

26. Can you get the list of employees with same salary?

Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid

27. What is an alternative for TOP clause in SQL?

1. ROWCOUNT function
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0

28. Will following statement give error or 0 as output? SELECT AVG (NULL)

Error. Operand data type NULL is invalid for Avg operator.

(Video) SQL interview questions and answers 2022 part 1

29. What is the Cartesian product of the table?

The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.

30. What is a schema in SQL?

Our database comprises of a lot of different entities such as tables, stored procedures, functions, database owners and so on. To make sense of how all these different entities interact, we would need the help of schema. So, you can consider schema to be the logical relationship between all the different entities which are present in the database.

Once we have a clear understanding of the schema, this helps in a lot of ways:

  • We can decide which user has access to which tables in the database.
  • We can modify or add new relationships between different entities in the database.

Overall, you can consider a schema to be a blueprint for the database, which will give you the complete picture of how different objects interact with each other and which users have access to different entities.

31. How to delete a column in SQL?

To delete a column in SQL we will be using DROP COLUMN method:

ALTER TABLE employeesDROP COLUMN age;

We will start off by giving the keywords ALTER TABLE, then we will give the name of the table, following which we will give the keywords DROP COLUMN and finally give the name of the column which we would want to remove.

32. What is a unique key in SQL?

Unique Key is a constraint in SQL. So, before understanding what exactly is a primary key, let’s understand what exactly is a constraint in SQL. Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. Constraints can either be column level or table level.

Unique Key:

Whenever we give the constraint of unique key to a column, this would mean that the column cannot have any duplicate values present in it. In other words, all the records which are present in this column have to be unique.

33. How to implement multiple conditions using WHERE clause?

We can implement multiple conditions using AND, OR operators:

SELECT * FROM employees WHERE first_name = ‘Steven’ AND salary <=10000;

In the above command, we are giving two conditions. The condition ensures that we extract only those records where the first name of the employee is ‘Steven’ and the second condition ensures that the salary of the employee is less than $10,000. In other words, we are extracting only those records, where the employee’s first name is ‘Steven’ and this person’s salary should be less than $10,000.

You can refer this SQL beginner’s tutorial if you want to know more about Structure Query Language

Learn In-Demand Skills for free on GL Academy

SQL Interview Questions for Experienced Professionals

1. What is SQL injection?

SQL injection is a hacking technique which is widely used by black-hat hackers to steal data from your tables or databases. Let’s say, if you go to a website and give in your user information and password, the hacker would add some malicious code over there such that, he can get the user information and password directly from the database. If your database contains any vital information, it is always better to keep it secure from SQL injection attacks.

2. What is a trigger in SQL?

A trigger is a stored program in a database which automatically gives responses to an event of DML operations done by insert, update, or delete. In other words, is nothing but an auditor of events happening across all database tables.

Let’s look at an example of a trigger:

CREATE TRIGGER bank_trans_hv_alertBEFORE UPDATE ON bank_account_transactionFOR EACH ROWbeginif( abs(:new.transaction_amount)>999999)THEN RAISE_APPLICATION_ERROR(-20000, 'Account transaction exceeding the daily deposit on SAVINGS account.');end if;end;

3. How to insert multiple rows in SQL?

To insert multiple rows in SQL we can follow the below syntax:

INSERT INTO table_name (column1, column2,column3...)VALUES (value1, value2, value3…..), (value1, value2, value3….), ... (value1, value2, value3);

We start off by giving the keywords INSERT INTO then we give the name of the table into which we would want to insert the values. We will follow it up with the list of the columns, for which we would have to add the values. Then we will give in the VALUES keyword and finally, we will give the list of values.

Here is an example of the same:

INSERT INTO employees ( name, age, salary)VALUES ( 'Sam', 21, 75000 ), ( ' 'Matt', 32, 85000 ), ( 'Bob', 26, 90000 );

In the above example, we are inserting multiple records into the table called employees.

4. How to find the nth highest salary in SQL?

This is how we can find the nth highest salary in SQL SERVER using TOP keyword:

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM #Employee ORDER BY salary DESC ) AS temp ORDER BY salary

This is how we can find the nth highest salary in MYSQL using LIMIT keyword:

SELECT salary FROM Employee ORDER BY salary DESC LIMIT N-1, 1

5. How to copy table in SQL?

We can use the SELECT INTO statement to copy data from one table to another. Either we can copy all the data or only some specific columns.

This is how we can copy all the columns into a new table:

SELECT *INTO newtableFROM oldtableWHERE condition;

If we want to copy only some specific columns, we can do it this way:

SELECT column1, column2, column3, ...INTO newtable FROM oldtableWHERE condition;

6. How to add a new column in SQL?

We can add a new column in SQL with the help of alter command:

ALTER TABLE employees ADD COLUMN contact INT(10);

This command helps us to add a new column named as contact in the employees table.

7. How to use LIKE in SQL?

The LIKE operator checks if an attribute value matches a given string pattern. Here is an example of LIKE operator

SELECT * FROM employees WHERE first_name like ‘Steven’;

With this command, we will be able to extract all the records where the first name is like “Steven”.

8. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?

Yes, SQL server drops all related objects, which exists inside a table like constraints, indexex, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table.

9. Can we disable a trigger? If yes, How?

Yes, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON<>. We also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”.

10. What is a Live Lock?

A live lock is one where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keep interferring. A live lock also occurs when read transactions create a table or page.

11. How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers – To display even numbers –

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0 

To display odd numbers –

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

12. Define COMMIT and give an example?

When a COMMIT is used in a transaction, all changes made in the transaction are written into the database permanently.

Example:

BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION; 

The above example deletes a job candidate in a SQL server.

13. Can you join table by itself?

A table can be joined to itself using self join, when you want to create a result set that joins records in a table with other records in the same table.

14. Explain Equi join with example.

When two or more tables has been joined using equal to operator then this category is called as equi join. Just we need to concentrate on condition is equal to (=) between the columns in the table.

Example:

Select a.Employee_name,b.Department_name from Employee a,Employee b where a.Department_ID=b.Department_ID

15. How do we avoid getting duplicate entries in a query?

The SELECT DISTINCT is used to get distinct data from tables using a query. The below SQL query selects only the DISTINCT values from the “Country” column in the “Customers” table:

SELECT DISTINCT Country FROM Customers;

16. How can you create an empty table from an existing table?

Lets take an example:

Select * into studentcopy from student where 1=2 

Here, we are copying student table to another table with the same structure with no rows copied.

17. Write a Query to display odd records from student table?

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM student) WHERE row_id %2!=0

18. Explain Non Equi Join with example?

When two or more tables are joining without equal to condition, then that join is known as Non Equi Join. Any operator can be used here, that is <>,!=,<,>,Between.

Example:

Select b.Department_ID,b.Department_name from Employee a,Department b where a.Department_id <> b.Department_ID;

19. How can you delete duplicate records in a table with no primary key?

By using the SET ROWCOUNT command. It limits the number of records affected by a command. Let’s take an example, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.

20. Difference between NVL and NVL2 functions?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

21. What is the difference between clustered and non-clustered indexes?

  1. Clustered indexes can be read rapidly rather than non-clustered indexes.
  2. Clustered indexes store data physically in the table or view whereas, non-clustered indexes do not store data in the table as it has separate structure from the data row.

22. What does this query says? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];

The given syntax indicates that the user can grant access to another user too.

23. Where MyISAM table is stored?

Each MyISAM table is stored on disk in three files.

  1. The “.frm” file stores the table definition.
  2. The data file has a ‘.MYD’ (MYData) extension.
  3. The index file has a ‘.MYI’ (MYIndex) extension.

24. What does myisamchk do?

It compresses the MyISAM tables, which reduces their disk or memory usage.

25. What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method. It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

26. What is Database White box testing?

White box testing includes: Database Consistency and ACID properties Database triggers and logical views Decision Coverage, Condition Coverage, and Statement Coverage Database Tables, Data Model, and Database Schema Referential integrity rules.

27. What are the different types of SQL sandbox?

There are 3 different types of SQL sandbox:

1. Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files.

2. External Access Sandbox: Users can access files without having the right to manipulate the memory allocation.

3. Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

28. What is Database Black Box Testing?

This testing involves:

1. Data Mapping
2. Data stored and retrieved
3. Use of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA).

29. Explain Right Outer Join with Example?

This join is usable, when user wants all the records from Right table (Second table) and only equal or matching records from First or left table. The unmatched records are considered as null records. Example: Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 where t1.col(+)=t2.col;

30. What is a Subquery?

A SubQuery is a SQL query nested into a larger query. Example: SELECT employeeID, firstName, lastName FROM employees WHERE departmentID IN (SELECT departmentID FROM departments WHERE locationID = 2000) ORDER BY firstName, lastName;

SQL Interview Questions for Developers

1. How to find duplicate records in SQL?

There are multiple ways to find duplicate records in SQL. Let’s see how can we find duplicate records using groupby:

SELECT x, y, COUNT(*) occurrencesFROM z1GROUP BY x, yHAVING COUNT(*) > 1;

We can also find duplicates in the table using rank:

SELECT * FROM ( SELECT eid, ename, eage, Row_Number() OVER(PARTITION BY ename, eage ORDER By ename) AS Rank FROM employees ) AS X WHERE Rank>1

2. What is Case WHEN in SQL?

If you have knowledge about other programming languages, then you’d have learnt about if-else statements. You can consider Case WHEN to be analogous to that.

In Case WHEN, there will be multiple conditions and we will choose something on the basis of these conditions.

Here is the syntax for CASE WHEN:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE resultEND;

We start off by giving the CASE keyword, then we follow it up by giving multiple WHEN, THEN statements.

3. How to find 2nd highest salary in SQL?

Below is the syntax to find 2nd highest salary in SQL:

SELECT name, MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

4. How to delete duplicate rows in SQL?

There are multiple ways to delete duplicate records in SQL.

Below is the code to delete duplicate records using rank:

alter table emp add sid int identity(1,1) delete e from emp e inner join (select *, RANK() OVER ( PARTITION BY eid,ename ORDER BY id DESC )rank From emp )T on e.sid=t.sid where e.Rank>1 alter table emp drop column sno

Below is the syntax to delete duplicate records using groupby and min:

alter table emp add sno int identity(1,1) delete E from emp E left join (select min(sno) sno From emp group by empid,ename ) T on E.sno=T.sno where T.sno is null alter table emp drop column sno

5. What is cursor in SQL?

Cursors in SQL are used to store database tables. There are two types of cursors:

  • Implicit Cursor
  • Explicit Cursor

Implicit Cursor:

These implicit cursors are default cursors which are automatically created. A user cannot create an implicit cursor.

Explicit Cursor:

Explicit cursors are user-defined cursors. This is the syntax to create explicit cursor:

DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

We start off by giving by keyword DECLARE, then we give the name of the cursor, after that we give the keywords CURSOR FOR SELECT * FROM, finally, we give in the name of the table.

6. How to create a stored procedure using SQL Server?

If you have worked with other languages, then you would know about the concept of Functions. You can consider stored procedures in SQL to be analogous to functions in other languages. This means that we can store a SQL statement as a stored procedure and this stored procedure can be invoked whenever we want.

This is the syntax to create a stored procedure:

CREATE PROCEDURE procedure_nameASsql_statementGO;

We start off by giving the keywords CREATE PROCEDURE, then we go ahead and give the name of this stored procedure. After that, we give the AS keyword and follow it up with the SQL query, which we want as a stored procedure. Finally, we give the GO keyword.

Once, we create the stored procedure, we can invoke it this way:

EXEC procedure_name;

We will give in the keyword EXEC and then give the name of the stored procedure.

Let’s look at an example of a stored procedure:

CREATE PROCEDURE employee_location @location nvarchar(20)ASSELECT * FROM employees WHERE location = @locationGO;

In the above command, we are creating a stored procedure which will help us to extract all the employees who belong to a particular location.

EXEC employee_location @location = 'Boston';

With this, we are extracting all the employees who belong to Boston.

7. How to create an index in SQL?

We can create an index using this command:

CREATE INDEX index_nameON table_name (column1, column2, column3 ...);

We start off by giving the keywords CREATE INDEX and then we will follow it up with the name of the index, after that we will give the ON keyword. Then, we will give the name of the table on which we would want to create this index. Finally, in parenthesis, we will list out all the columns which will have the index. Let’s look at an example:

CREATE INDEX salaryON Employees (Salary);

In the above example, we are creating an index called a salary on top of the ‘Salary’ column of the ‘Employees’ table.

Now, let’s see how can we create a unique index:

(Video) TOP 23 SQL INTERVIEW QUESTIONS & ANSWERS! (SQL Interview Tips + How to PASS an SQL interview!)

CREATE UNIQUE INDEX index_nameON table_name (column1, column2,column3 ...);

We start off with the keywords CREATE UNIQUE INDEX, then give in the name of the index, after that, we will give the ON keyword and follow it up with the name of the table. Finally, in parenthesis, we will give the list of the columns which on which we would want this unique index.

8. How to change column data-type in SQL?

We can change the data-type of the column using the alter table. This will be the command:

ALTER TABLE table_nameMODIFY COLUMN column_name datatype;

We start off by giving the keywords ALTER TABLE, then we will give in the name of the table. After that, we will give in the keywords MODIFY COLUMN. Going ahead, we will give in the name of the column for which we would want to change the datatype and finally we will give in the data type to which we would want to change.

9. Difference between SQL and NoSQL databases?

SQL stands for structured query language and is majorly used to query data from relational databases. When we talk about a SQL database, it will be a relational database.

But when it comes to NoSQL database, we will be working with non-relational databases.

SQL Joins Interview Questions

1. How to change column name in SQL?

The command to change the name of a column is different in different RDBMS.

This is the command to change the name of a column in MYSQL:

ALTER TABLE Customer CHANGE Address Addr char(50);

IN MYSQL, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the CHANGE keyword and give in the original name of the column, following which we will give the name to which we would want to rename our column.

This is the command to change the name of a column in ORACLE:

ALTER TABLE Customer RENAME COLUMN Address TO Addr;

In ORACLE, we will start off by using the ALTER TABLE keywords, then we will give in the name of the table. After that, we will use the RENAME COLUMN keywords and give in the original name of the column, following which we will give the TO keyword and finally give the name to which we would like to rename our column.

When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.

2. What is a view in SQL?

A view is a database object that is created using a Select Query with complex logic, so views are said to be a logical representation of the physical data, i.e Views behave like a physical table and users can use them as database objects in any part of SQL queries.

Let’s look at the types of Views:

  • Simple View
  • Complex View
  • Inline View
  • Materialized View

Simple View:

Simple views are created with a select query written using a single table. Below is the command to create a simple view:

Create VIEW Simple_view as Select * from BANK_CUSTOMER ;

Complex View:

Create VIEW Complex_view as SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000

Inline View:

A subquery is also called as an inline view if and only if it is called in FROM clause of a SELECT query.

SELECT * FROM ( SELECT bc.customer_id , ba.bank_account From Bank_customer bc JOIN Bank_Account ba Where bc.customer_id = ba.customer_id And ba.balance > 300000)

3. How to drop a column in SQL?

To drop a column is SQL, we will be using this command:

ALTER TABLE employeesDROP COLUMN gender;

We will start off by giving the keywords ALTER TABLE, then we will give the name of the table, following which we will give the keywords DROP COLUMN and finally give the name of the column which we would want to remove.

4. How to use BETWEEN in SQL?

The BETWEEN operator checks an attribute value within a range. Here is an example of BETWEEN operator:

SELECT * FROM employees WHERE salary between 10000 and 20000;

With this command, we will be able to extract all the records where the salary of the employee is between 10000 and 20000.

Advanced SQL Interview Questions

1. What are the subsets of SQL?

a. DDL (Data Definition Language): Used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc.

b. DML (Data Manipulation language): Used to manipulate already existing data in the database, commands like SELECT, UPDATE, INSERT

c. DCL (Data Control Language): Used to control access to data in the database, commands like GRANT, REVOKE.

2. Difference between CHAR and VARCHAR2 datatype in SQL?

CHAR is used to store fixed-length character strings, and VARCHAR2 used to store variable-length character strings.

3. How to sort a column using a column alias?

By using the column alias in the ORDER BY instead of where clause for sorting

4. Difference between COALESCE() & ISNULL() ?

COALESCE() accepts two or more parameters, one can apply 2 or as many parameters but it returns only the first non NULL parameter.

ISNULL() accepts only 2 parameters.

The first parameter is checked for a NULL value, if it is NULL then the 2nd parameter is returned, otherwise, it returns the first parameter.

5. What is “Trigger” in SQL?

A trigger allows you to execute a batch of SQL code when an insert,update or delete command is run against a specific table as Trigger is said to be the set of actions that are performed whenever commands like insert, update or delete are given.

6. Write a Query to display employee details along with age?

SELECT * DATEDIFF(yy, dob, getdate()) AS 'Age' FROM employee

7. Write a Query to display employee details along with age?

SELECT SUM(salary) FROM employee

8. Write an SQL query to get the third maximum salary of an employee from a table named employee_table.

SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC ) AS emp ORDER BY salary ASC;

9. What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on input value.

Example -. Aggregate – max(), count – Calculated with respect to numeric. Scalar – UCASE(), NOW() – Calculated with respect to strings.

10. What is a deadlock?

It is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks.

11. Explain left outer join with example?

Left outer join is useful if you want all the records from the left table(first table) and only matching records from 2nd table. The unmatched records are null records. Example: Left outer join with “+” operator Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 where t1.col=t2.col(+);

12. What is SQL injection?

SQL injection is a code injection technique used to hack data-driven applications.

13. What is an UNION operator?

The UNION operator combines the results of two or more Select statements by removing duplicate rows. The columns and the data types must be the same in the SELECT statements.

14. Explain SQL Constraints?

SQL Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY

15. What is ALIAS command?

This command provides another name to a table or a column. It can be used in WHERE clause of a SQL query using the “as” keyword.

16. What are Group Functions? Why do we need them?

Group functions work on a set of rows and return a single result per group. The popularly used group functions are AVG, MAX, MIN, SUM, VARIANCE, COUNT.

17. How can dynamic SQL be executed?

  • By executing the query with parameters
  • By using EXEC
  • By using sp_executesql

18. What is the usage of NVL() function?

This function is used to convert NULL value to the other value.

19. Write a Query to display employee details belongs to ECE department?

SELECT EmpNo, EmpName, Salary FROM employee WHERE deptNo in (select deptNo from dept where deptName = ‘ECE’)

20. What are the main differences between #temp tables and @table variables and which one is preferred ?

1. SQL server can create column statistics on #temp tables.

2. Indexes can be created on #temp tables

3. @table variables are stored in memory up to a certain threshold

21. What is CLAUSE?

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records. Example – Query that has WHERE condition.

22. What is recursive stored procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

23. What does the BCP command do?

The Bulk Copy is a utility or a tool that exports/imports data from a table into a file and vice versa.

24. What is a Cross Join?

In SQL cross join, a combination of every row from the two tables is included in the result set. This is also called cross product set. For example, if table A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 200 rows provided there is NOWHERE clause in the SQL statement.

25. Which operator is used in query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as- 1. % – Matches zero or more characters. 2. _(Underscore) – Matching exactly one character.

26. Write a SQL query to get the current date?

SELECT CURDATE();

27. State the case maniplation functions in SQL?

1. LOWER: converts all the characters to lowercase.

2. UPPER: converts all the characters to uppercase.

3. INITCAP: converts initial character of each word to uppercase

28. How to add a column to an existing table?

ALTER TABLE Department ADD (Gender, M,F)

29. Define lock escalation?

A query first takes the lowest level lock possible with the smallest row-level.When too many rows are locked, the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.

30. How to store Videos inside SQL Server table ?

By using FILESTREAM datatype, which was introduced in SQL Server 2008.

31. State the order of SQL SELECT?

Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clauses are mandatory.

32. What is the difference between IN and EXISTS?

IN: Works on List result set Doesn’t work on subqueries resulting in Virtual tables with multiple columns Compares every value in the result list.

Exists: Works on Virtual tables Is used with co-related queries Exits comparison when match is found

33. How do you copy data from one table to another table ?

INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition;

34. List the ACID properties that makes sure that the database transactions are processed

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

35. What will be the output of the following Query, provided the employee table has 10 records?

BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

36. What do you mean by Stored Procedures? How do we use it?

A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures earlier before using it and can execute them wherever required by applying some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.

37. What does GRANT command do?

This command is used to provide database access to users other than the administrator in SQL privileges.

38. What does First normal form do?

First Normal Form (1NF): It removes all duplicate columns from the table. It creates a table for related data and identifies unique column values.

39. How to add e record to the table?

INSERT syntax is used to add a record in the table. INSERT into table_name VALUES (value1, value2..);

40. What are the different tables present inMySQL?

There are 5 tables present in MYSQL.

1. MyISAM

2. Heap

3. Merge

4. INNO DB

5. ISAM

41. What is BLOB and TEXT in MySQL?

BLOB stands for large binary object. It is used to hold a variable amount of data. TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string).

42. What is the use of mysql_close()?

Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

43. How do you return a hundred books starting from 25th?

SELECT book_titile FROM books LIMIT 25,100;

44. How would you select all the users, whose phone number is NULL?

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

45. How do you run batch mode in mysql?

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

46. Write an SQL query to show the second highest salary from a table.

Select max(Salary) from Worker where Salary not in (Selct max(Salary) from Worker);

47. Write an SQL query to fetch three max salaries from a table.

SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.salary <= b.Salary) order by a.Salary desc;

48. What is the difference between NOW() and CURRENT_DATE()?

NOW () command is used to show current year,month,date with hours,minutes and seconds. CURRENT_DATE() shows current year,month and date only.

49. How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

50. What are the nonstandard string types?

Following are Non-Standard string types:

1. TINYTEXT

2. TEXT

3. MEDIUMTEXT

4. LONGTEXT

51. What is the group by clause used for?

The group by clause combines all those records that have identical values in a particular field or any group of fields.

(Video) Top 100 SQL Interview Questions And Answers | SQL Interview Questions For 2022 - MindMajix

52. How do you get the last id without the max function?

SELECT ID from table order by ID desc limit 1

53. Write a SQL query to fetch only even rows from the table.

Using the same Row_Number() and checking that the remainder when divided by 2 is 0- SELECT E.EmpId, E.Project, E.Salary FROM ( SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber FROM EmployeeSalary ) E WHERE E.RowNumber % 2 = 0

54. Write a SQL query to create a new table with data and structure copied from another table.

Using SELECT INTO command- SELECT * INTO newTable FROM EmployeeDetails;

55. What are the different types of Collation Sensitivity?

Case sensitivity: A and a are treated differently. Accent sensitivity: a and á are treated differently. Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently. Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

56. Write a query to find out the data between range?

In day to day activities the user needs to find out the data between some range. To achieve this user needs to use Between..and operator or Greater than and less than operator.

Query 1 : Using Between..and operator
Select * from Employee where salary between 25000 and 50000;
Query 2 : Using operators (Greater than and less than)
Select * from Employee where salary >= 25000 and salary <= 50000;

57. How to calculate the number of rows in a table without using the count function?

There are so many system tables which are very important .Using the system table user can count the number of rows in the table.following query is helpful in that case, Select table_name, num_rows from user_tables where table_name=’Employee’;

58. What is wrong with the following query? SELECT empName FROM employee WHERE salary <> 6000

The following query will not fetch a record with the salary of 6000 but also will skip the record with NULL.

59. Will the following statements execute? if yes what will be output? SELECT NULL+1 SELECT NULL+’1′

Yes, no error. The output will be NULL. Perform any operation on NULL will get the NULL result.

60. SQL vs PL/SQL

BASIS FOR COMPARISONSQLPL/SQL
BasicIn SQL you can execute a single query or a command at a time.In PL/SQL you can execute a block of code at a time.
Full formStructured Query LanguageProcedural Language, an extension of SQL.
PurposeIt is like a source of data that is to be displayed.It is a language that creates an application that display’s data acquired by SQL.
WritesIn SQL you can write queries and command using DDL, DML statements.In PL/SQL you can write block of code that has procedures, functions, packages or variables, etc.
UseUsing SQL, you can retrieve, modify, add, delete, or manipulate the data in the database.Using PL/SQL, you can create applications or server pages that display the information obtained from SQL in a proper format.
EmbedYou can embed SQL statement in PL/SQL.You can not embed PL/SQL in SQL

61. SQL having vs where

S. No.Where ClauseHaving Clause
1The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP by clauseThe HAVING clause cannot be used without the GROUP BY clause
2The WHERE clause selects rows before groupingThe HAVING clause selects rows after grouping
3The WHERE clause cannot contain aggregate functionsThe HAVING clause can contain aggregate functions
4WHERE clause is used to impose a condition on SELECT statement as well as single row function and is used before GROUP BY clauseHAVING clause is used to impose a condition on GROUP Function and is used after GROUP BY clause in the query
5SELECT Column,AVG(Column_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmaeSELECT Columnq, AVG(Coulmn_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae Having column_name>or<value

62. NoSQL vs SQL

SQL Databases NoSQL Databases
Data Storage Model Tables with fixed rows and columns Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges
Development History Developed in the 1970s with a focus on reducing data duplication Developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices.
Examples Oracle, MySQL, Microsoft SQL Server, and PostgreSQL Document: MongoDB and CouchDB, Key-value: Redis and DynamoDB, Wide-column: Cassandra and HBase, Graph: Neo4j and Amazon Neptune
Primary Purpose General purpose Document: general purpose, Key-value: large amounts of data with simple lookup queries, Wide-column: large amounts of data with predictable query patterns, Graph: analyzing and traversing relationships between connected data
Schemas Rigid Flexible
Scaling Vertical (scale-up with a larger server) Horizontal (scale-out across commodity servers)
Multi-Record ACID Transactions Supported Most do not support multi-record ACID transactions. However, some—like MongoDB—do.
Joins Typically required Typically not required
Data to Object Mapping Requires ORM (object-relational mapping) Many do not require ORMs. MongoDB documents map directly to data structures in most popular programming languages.

63. SQL vs TSQL

SQLTSQL
A domain-specific language used in programming and designed for managing data held in a Relational Database Management SystemMicrosoft’s proprietary version of SQL for its SQL Server RDBMS
Stands for Structured Query LanguageStands for Transact Structured Query Language
Query Language to Manage Data in an RDBMSAn Extension of SQL That is Used on MS SQL Server
Developed by IDMDeveloped by Microsoft
It is Possible to Embed SQL into TSQLIt is Not Possible to Embed TSQL into SQL
Helps to Process and Analyze the Data Using Simple QueriesHelps to Add Business Logic into an Application

64. MySQL vs SQL Server

SQL Server MySQL Server
Relational Database Management SystemOpen-source Relational Database Management System
Developed by MicrosoftDeveloped by Oracle
Available in Multiple Languages Only Available in English
Supports Windows, Linux and ContainersSupports Windows, Linux and Mac
Commercial Open-Source
Programmed in C++Programmed in C and C++
Compatible with Kubernetes, Apache Spark and Hadoop Distributed File SystemHas Difficulty Operating with Kubernetes, Apache Spark and Hadoop Distributed File System

65. MongoDB vs SQL

MongoDBMySQL
When you need high availability of data with automatic, fast, and instant data recoveryIf you’re just starting and your database is not going to scale much, MYSQL will help you in easy and low-maintenance setup
In future, if you’re going to grow big as MongoDB has in-built sharding solutionIf you want high performance on a limited budget
If you have an unstable schema and you want to reduce your schema migration cost If you have fixed schema and data structure isn’t going to change over time like WikiPedia
If you don’t have a database administrator If high transaction rate I going to be your requirement
If most of the services are cloud-based, MongoDB is best suitable for youIf data security is the topmost priority, MySQL is most suited DBMS

SQL Server Interview Questions

1. What is SQL server?

SQL server has stayed on top as one of the most popular database management products ever since its first release in 1989 by Microsoft Corporation. The product is used across industries to store and process large volumes of data. It was primarily built to store and process data that is built on a relational model of data.

SQL Server is widely used for data analysis and also scaling up of data. SQL Server can be used in conjunction with Big Data tools such as Hadoop.

SQL Server can be used to process data from various data sources such as Excel, Table, .Net Framework application, etc.

2. How to install SQL Server?

1. Click on the below SQL Server official release link to access the latest version: https://www.microsoft.com/en-in/sql-server/sql-server-downloads

2.Select the type of SQL Server edition that you want to install. SQL Server can be used on aCloud Platform or as an open-source edition(Express or Developer) in your local computer system.

3.Click on the Download Now button.

4.Save the .exe file on your system. Right-click on the .exe file and click on Open.

5.Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.

6.Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

3. How to create a stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAMEASSQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)GO;

Stored procedures can be user-defined or built-in. Various parameters can be passed onto a Stored Procedure.

4. How to install SQL Server 2008?

  1. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  2. Click on the search icon and type in – SQL Server 2008 download
  3. Click on the result link to download and save SQL Server 2008.
  4. Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
  5. Click on the Download Now button.
  6. Save the .exe file on your system. Right-click on the .exe file and click on Open.
  7. Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server installed.
  8. Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application.

5. How to install SQL Server 2017?

  1. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  2. Click on the search icon and type in – SQL Server 2017 download
  3. Click on the result link to download and save SQL Server 2017.
  4. Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
  5. Click on the Download Now button.
  6. Save the .exe file on your system. Right-click on the .exe file and click on Open.
  7. Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server installed.
  8. Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

6. How to restore the database in SQL Server?

Launch the SQL Server Management Studio application and from the Object Explorer window pane, right-click on Databases and click on Restore. This would automatically restore the database.

7. How to install SQL Server 2014?

  1. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  2. Click on the search icon and type in – SQL Server 2014 download
  3. Click on the result link to download and save SQL Server 2014.
  4. Select the type of SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
  5. Click on the Download Now button.
  6. Save the .exe file on your system. Right-click on the .exe file and click on Open.
  7. Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.
  8. Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

8. How to get the connection string from SQL Server?

Launch the SQL Server Management Studio. Go to the Database for which you require the Connection string. Right-click on the database and click on Properties. In the Properties window that is displayed, you can view the Connection String property.

Connection strings help connect databases to another staging database or any external source of data.

9. How to install SQL Server 2012?

  1. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  2. Click on the search icon and type in – SQL Server 2012 download
  3. Click on the result link to download and save SQL Server 2012.
  4. Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
  5. Click on the Download Now button.
  6. Save the .exe file on your system. Right-click on the .exe file and click on Open.
  7. Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed.
  8. Once the installation is complete, restart your system, if required, and launch the SQL Server Management Studio application from the START menu.

10. What is cte in SQL Server?

CTEs are Common Table Expressions that are used to create temporary result tables from which data can be retrieved/ used. The standard syntax for a CTE with a SELECT statement is:

WITH RESULT AS

(SELECT COL1, COL2, COL3

FROM EMPLOYEE)

SELECT COL1, COL2 FROM RESULT

CTEs can be used with Insert, Update or Delete statements as well.

Few examples of CTEs are given below:

Query to find the 10 highest salaries.

with result as

(select distinct salary, dense_rank() over (order by salary desc) as salary rank from employees)

select result. salary from result where the result.salaryrank = 10

Query to find the 2nd highest salary

with the result as

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result. salary from result where the result.salaryrank = 2

In this way, CTEs can be used to find the nth highest salary within an organisation.

11. How to change SQL Server password?

Launch your SQL Server Management Studio. Click on the Database connection for which you want to change the login password. Click on Security from the options that get displayed.

Click on Logins and open your database connection. Type in the new password for login and click on ‘OK’ to apply the changes.

12. How to delete duplicate records in SQL Server?

Select the duplicate records in a table HAVING COUNT(*)>1

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table-

(SELECT COL1, COUNT(*) AS DUPLICATE

FROM EMPLOYEE

GROUP BY COL1

HAVING COUNT(*) > 1)

13. How to uninstall SQL Server?

In Windows 10, go to the START menu and locate the SQL Server.

Right-click and select uninstall to uninstall the application.

14. How to check SQL Server version?

You can run the below query to view the current version of SQL Server that you are using.

SELECT @@version;

15. How to rename column name in SQL Server?

From the Object Explorer window pane, go to the table where the column is present and choose Design. Under the Column Name, select the name you want to rename and enter the new name. Go to the File menu and click Save.

16. What is the stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAMEASSQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)GO;

You can execute the Stored Proc by using the command Exec Procedure_Name;

17. How to create a database in SQL Server?

After installing the required version of SQL Server, it is easy to create new databases and maintain them.

  1. Launch the SQL Server Management Studio
  2. In the Object Explorer window pane, right-click on Databases and select ‘New Database’
  3. Enter the Database Name and click on ‘Ok’.
  4. Voila! Your new database is ready for use.

18. What is an index in SQL Server?

Indexes are database objects which help in retrieving records quickly and more efficiently. Column indexes can be created on both Tables and Views. By declaring a Column as an index within a table/ view, the user can access those records quickly by executing the index. Indexes with more than one column are called Clustered indexes.

Syntax:

CREATE INDEX INDEX_NAMEON TABLE_NAME(COL1, COL2);

The syntax to drop an Index is DROP INDEX INDEX_NAME;

Indexes are known to improve the efficiency of SQL Select queries.

19. How to create the table in SQL Server?

Tables are the fundamental storage objects within a database. A table is usually made up of

Rows and Columns. The below syntax can be used to create a new table with 3 columns.

CREATE TABLE TABLE_NAME(COLUMN1 DATATYPE,COLUMN2 DATATYPE,COLUMN3 DATATYPE);

Alternatively, you can right-click on Table in the Object Explorer window pane and select ‘New -> Table’.

You can also define the type of Primary/ Foreign/ Check constraint when creating a table.

20. How to connect to SQL Server?

  1. Launch the SQL Server Management Studio from the START menu.
  2. In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system.
  3. Select the appropriate Authentication type and click on the Connect button.
  4. A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.
    SQL Interview Questions and Answers in 2022 (2)

21. How to delete duplicate rows in SQL Server?

Select the duplicate records in a table HAVING COUNT(*)>1

Add a delete statement to delete the duplicate records.

Sample Query to find the duplicate records in a table –

(SELECT COL1, COUNT(*) AS DUPLICATEFROM EMPLOYEEGROUP BY COL1HAVING COUNT(*) > 1);

22. How to download SQL Server?

The Express and Developer versions (open-source versions) of the latest SQL Server release can be downloaded from the official Microsoft website. The link is given below for reference.
https://www.microsoft.com/en-in/sql-server/sql-server-downloads

23. How to connect SQL Server management studio to the local database?

  1. Launch the SQL Server Management Studio from the START menu.
  2. In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system and click on the Connect button.
  3. Select the Authentication as ‘Windows Authentication.
  4. A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.
    SQL Interview Questions and Answers in 2022 (3)

24. How to change column name in SQL Server?

From the Object Explorer window pane, go to the table in which the column is present and choose Design. Under the Column Name, select the name you want to rename and enter the new name. Go to the File menu and click Save.

25. How to download SQL Server 2014?

  1. Both the Express and Developer versions (free editions) of SQL Server can be downloaded from the official Microsoft website. The link is given below for reference.
  2. Click on the link below : https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  1. Click on the search icon and type in – SQL Server 2014 download
  2. Click on the result link to download and save SQL Server 2014.

26. How to uninstall SQL Server 2014?

From the START menu, type SQL Server. Right-click on the app and select uninstall to uninstall the application from your system. Restart the system, if required, for the changes to get affected.

27. How to find server name in SQL Server?

Run the query SELECT @@version; to find the version and name of the SQL Server you are using.

28. How to start SQL Server?

Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects.

29. What is the case when in SQL Server?

Case When statements in SQL are used to run through many conditions and to return a value when one such condition is met. If none of the conditions is met in the When statements, then the value mentioned in the Else statement is returned.

Syntax:

CASEWHEN CONDITION1 THEN RESULT1WHEN CONDITION2 THEN RESULT2ELSERESULTEND;

Sample query:

HOW MANY HEAD OFFICES/ BRANCHES ARE THERE IN CANADA

select
sum (
case
when region_id >= 5 AND region_id <= 7 then
1
else
0
end ) as Canada
from company_regions;
Nested CASE statement:
SELECT
SUM (
CASE
WHEN rental_rate = 0.99 THEN
1
ELSE
0
END
) AS "Mass",
SUM (
CASE
WHEN rental_rate = 2.99 THEN
1
ELSE
0
END
) AS "Economic",
SUM (
CASE
WHEN rental_rate = 4.99 THEN
1
ELSE
0
END
) AS " Luxury"
FROM
film;

30. How to install SQL Server management studio?

Launch Google and in the Search toolbar, type in SQL Server Management Studio’ download.

Go to the routed website and click on the link to download. Once the download is complete, open the .exe file to install the content of the file. Once the installation is complete, refresh or restart the system, as required.

Alternatively, once SQL Server is installed and launched, it will prompt the user with an option to launch SQ Server Management Studio.

31. How to write a stored procedure in SQL Server?

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc:

CREATE PROCEDURE PROCEDURE_NAMEASSQL_QUERY (GIVE YOUR OFTEN USED QUERY HERE)GO;

You can execute the Stored Proc by using the command Exec Procedure_Name;

(Video) Top 65 SQL Interview Questions and Answers | SQL Interview Preparation | SQL Training | Edureka

32. How to open SQL Server?

Launch the SQL Server Management Studio from the START menu. Login using Windows Authentication. In the Object Explorer window pane, you can view the list of databases and corresponding objects.

33. How to connect SQL Server?

The most common way of connecting to a SQL Server is using Windows Authentication.

Make sure to select the Server Name as the desktop or laptop system name.

34. What is replication in SQL Server?

Replication of a database node is the most common way to prevent the complete loss of any data. When a database is replicated/ taken a copy of, it can be used across databases for data reuse and synchronization. Apart from the primary motive of data backup, replicated data is also used for data analysis in Big Data projects.

35. How to open SQL Server configuration manager?

Click on the START menu and select All Programs. Select Microsoft SQL Server, select Configuration Tools, and then select SQL Server Configuration tools. In that, select the SQL Server Configuration Manager.

36. What is a collation in SQL Server?

Collation refers to a set of pre-defined rules on SQL Server, which define the encoding rules of character data both at a database and server level. Collation rules can be used on Metadata as well.

37. How to use SQL Server?

SQL Server is used to retrieve and process various data that is built on a relational model.

Some of the common actions that can be taken on the data are CREATE, DELETE, INSERT, UPDATE, SELECT, REVOKE, etc.

SQL Server can also be used to import and export data from different data sources. SQL Server can also be connected to various other databases/ .Net framework using Connection Strings.

SQL Server can also be used in conjunction with Big Data tools like Hadoop.

38. What is a function in SQL Server?

Functions are pre-written codes that return a value and which help the user achieve a particular task concerning viewing, manipulating, and processing data.

Examples of few functions are:

AGGREGATE FUNCTIONS:

MIN()- Returns the minimum value

MAX()- Retuns the maximum value

AVG()- Returns the average value

COUNT()

STRING FUNCTIONS:

  • COALESCE()
  • CAST()
  • CONCAT()
  • SUBSTRING()

DATE FUNCTIONS:

  • GETDATE()
  • DATEADD()
  • DATEDIFF()

There are many types of functions such as Aggregate Functions, Date Functions, String Functions, Mathematical functions, etc.

39. How to find nth highest salary in SQL Server without using a subquery

This is one of the common SQL Interview questions that have been asked in several Job interviews.

Query to find the 10 highest salary. For up-gradation of the b10 band.

with result as

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result.salary from result where result.salaryrank = 10

Query to find the 2nd highest salary

with the result as

(select distinct salary, dense_rank() over (order by salary desc) as salaryrank from employees)

select result.salary from result where result.salaryrank = 2

In this way, by replacing the salaryrank value, we can find the nth highest salary in any organisation.

40. How to install SQL Server in Windows 10?

  1. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
  2. Click on the search icon and type in – SQL Server 2012 download
  3. Click on the result link to download and save SQL Server 2012.
  4. Select the type of the SQL Server edition that you want to install. SQL Server can be used on a Cloud Platform or as an open-source edition(Express or Developer) in your local computer system.
  5. Click on the Download Now button.
  6. Save the .exe file on your system. Right-click on the .exe file and click on Open.
  7. Click on ‘Yes’ to allow the changes to be made on your system and have SQL Server Installed

41. How to create a temp table in SQL Server?

Temporary tables can be used to retain the structure and a subset of data from the original table from which they were derived.

Syntax:

SELECT COL1, COL2INTO TEMPTABLE1FROM ORIGTABLE;

Temporary tables do not occupy any physical memory and can be used to retrieve data faster.

PostgreSQL Interview Questions

1. What is PostgreSQL?

PostgreSQL is one of the most widely and popularly used languages for Object-Relational Database Management systems. It is mainly used for large web applications. It is an open-source, object-oriented, -relational database system. It is extremely powerful and enables users to extend any system without problem. It extends and uses the SQL language in combination with various features for safely scaling and storage of intricate data workloads.

2. List different datatypes of PostgreSQL?

Listed below are some of the new data types in PostgreSQL

  • UUID
  • Numeric types
  • Boolean
  • Character types
  • Temporal types
  • Geometric primitives
  • Arbitrary precision numeric
  • XML
  • Arrays etc

3. What are the Indices of PostgreSQL?

Indices in PostgreSQL allow the database server to find and retrieve specific rows in a given structure. Examples are B-tree, hash, GiST, SP-GiST, GIN and BRIN. Users can also define their indices in PostgreSQL. However, indices add overhead to the data manipulation operations and are seldom used

4. What are tokens in PostgreSQL?

Tokens in PostgreSQL act as the building blocks of a source code. They are composed of various special character symbols. Commands are composed of a series of tokens and terminated by a semicolon(“;”). These can be a constant, quoted identifier, other identifiers, keyword or a constant. Tokens are usually separated by whitespaces.

5. How to create a database in PostgreSQL?

Databases can be created using 2 methods

  • First is the CREATE DATABASE SQL Command

We can create the database by using the syntax:- CREATE DATABASE <dbname>;

  • The second is by using the createdb command

We can create the database by using the syntax:- createdb [option…] <dbname> [description]

Various options can be taken by the createDB command based on the use case.

6. How to create a table in PostgreSQL?

You can create a new table by specifying the table name, along with all column names and their types:

CREATE TABLE [IF NOT EXISTS] table_name (column1 datatype(length) column_contraint,column2 datatype(length) column_contraint,...columnn datatype(length) column_contraint,table_constraints);

7. How can we change the column datatype in PostgreSQL?

The column the data type can be changed in PostgreSQL by using theALTER TABLE command:

ALTER TABLE table_nameALTER COLUMN column_name1 [SET DATA] TYPE new_data_type,ALTER COLUMN column_name2 [SET DATA] TYPE new_data_type,...;

8. Compare ‘PostgreSQL’ with ‘MongoDB’

PostgreSQL is an SQL database where data is stored as tables, with structured rows and columns. It supports concepts like referential integrity entity-relationship and JOINS. PostgreSQL uses SQL as its querying language. PostgreSQL supports vertical scaling. This means that you need to use big servers to store data. This leads to a requirement of downtime to upgrade. It works better if you require relational databases in your application or need to run complex queries that test the limit of SQL.

MongoDB, on the other hand, is a NoSQL database. There is no requirement for a schema, therefore it can store unstructured data. Data is stored as BSON documents and the document’s structure can be changed by the user. MongoDB uses JavaScript for querying. It supports horizontal scaling, as a result of which additional servers can be added as per the requirement with minimal to no downtime. It is appropriate in a use case that requires a highly scalable distributed database that stores unstructured data.

9.What is Multi-Version concurrency control in PostgreSQL?

MVCC or better known as Multi-version concurrency control is used to implement transactions in PostgreSQL. It is used to avoid unwanted locking of a database in the system. while querying a database each transaction sees a version of the database. This avoids viewing inconsistencies in the data, and also provides transaction isolation for every database session. MVCC locks for reading data do not conflict with locks acquired for

10. What is pgadmin?

pgadmin is a GUI, Web-based tool that can be used to interact with Postgres database sessions. It can be utilized for local and remote servers. Its latest version pgadmin4 is released under the PostgreSQL License.

11. How do you delete the database in PostgreSQL?

Databases can be deleted in PostgreSQL using the syntax

DROP DATABASE [IF EXISTS] <database_name>;

Please note that only databases having no active connections can be dropped.

12. What does a schema contain?

Schemas are a part of the database that contains tables. They also contain other kinds of named objects, like data types, functions, and operators. The object names can be used in different schemas without conflict; Unlike databases, schemas are separated more flexibly. This means that a user can access objects in any of the schemas in the database they are connected to, till they have privileges to do so. Schemas are highly beneficial when there is a need to allow many users access to one database without interfering with each other. It helps in organizing database objects into logical groups for better manageability. Third-party applications can be put into separate schemas to avoid conflicts based on names.

13. What is the square root operator inPostgreSQL?

It is denoted by ‘|/” and returns the square root of a number. Its syntax is

Select |/ <number>

Egs:- Select |/16

14. What are the main differences between SQL and PostgreSQL?

PostgreSQL is an advanced version of SQL. Views in PostgreSQL are not updatable.

PostgreSQL cannot provide computed columns but has functional indexes. Replication in PostgreSQL is in the form of reports. PostgreSQL supports dynamic actions.

15. How are the stats updated in Postgresql?

To update statistics in PostgreSQL a special function called an explicit ‘vacuum’ call is made. Entries in pg_statistic are updated by the ANALYZE and VACUUM ANALYZE commands

16. What Is A Candid?

The CTIDs field exists in every PostgreSQL table. It is unique for every record of a table and exactly shows the location of a tuple in a particular table. A logical row’s CTID changes when it is updated, thus it cannot be used as a permanent row identifier. However, it is useful when identifying a row within a transaction when no update is expected on the data item.

17. What is Cube Root Operator (||/) in PostgreSQL?

It is denoted by ‘|/” and returns the square root of a number. Its syntax is

Select |/ <number>

Egs:- Select |/16

18. Explain Write-Ahead Logging?

Write-ahead logging is a method to ensure data integrity. It is a protocol that ensures writing the actions as well as changes into a transaction log. It is known to increase the reliability of databases by logging changes before they are applied or updated onto the database. This provides a backup log for the database in case of a crash.

19. What is a non-clustered index?

A non-clustered index in PostgreSQL is a simple index, used for fast retrieval of data, with no certainty of uniqueness of data. It also contains pointers to locations where other parts of data are stored

20. How is security ensured in PostgreSQL?

PostgreSQL uses 2 levels of security

  1. Network-level security which uses Unix Domain sockets, TCP/IP sockets, and firewalls.
  2. Transport-level security which uses SSL/TLS to enable secure communication with the database
  3. Database-level security with features like roles and permissions, row-level security (RLS), and auditing.

Frequently Asked Questions in SQL

1. How do I prepare for the SQL interview?

There are many sources online that can help you prepare for an SQL interview. You can go through brief tutorials and free online courses on SQL (eg.: SQL basics on Great Learning Academy) to revise your knowledge of SQL. You can also practice projects to help you with practical aspects of the language. Lastly, there are many blogs such as this that list out all the probable questions that an interviewer might ask.

2. What are the 5 basic SQL commands?

The five basic SQL commands are:

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)
  • Data Query Language (DQL)

3. What are basic SQL skills?

SQL is a vast topic and there is a lot to learn. But the most basic skills that an SQL professional should know are:

  • How to structure a database
  • Managing a database
  • Authoring SQL statements and clauses
  • Knowledge of popular database systems such as MySQL
  • Working knowledge of PHP
  • SQL data analysis
  • Creating a database with WAMP and SQL

4. How can I practice SQL?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others.

5. Where can I practice SQL questions?

There are some platforms available online that can help you practice SQL such as SQL Fiddle, SQLZOO, W3resource, Oracle LiveSQL, DB-Fiddle, Coding Groud, GitHub and others.

You can also refer to articles and blogs online that list the most important SQL interview questions for preparation.

6. What is the most common SQL command?

Some of the most common SQL commands are:

  • CREATE DATABASE
  • ALTER DATABASE
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX

7. How are SQL commands classified?

SQL Commands are classified under four categories, i.e.,

  • Data Definition Language (DDL)
  • Data Query Language (DQL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)

8. What are basic SQL commands?

Basic SQL commands are:

  • CREATE DATABASE
  • ALTER DATABASE
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX

9. Is SQL coding?

Yes, SQL is a coding language/ programming language that falls under the category of domain-specific programming language. It is used to access relational databases such as MySQL.

10. What is SQL example?

SQL helps you update, delete, and request information from databases. Some of the examples of SQL are in the form of the following statements:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE DATABASE
  • ALTER DATABASE

11. What is SQL code used for?

SQL code is used to access and communicate with a database. It helps in performing tasks such as updating and retrieving data from the databases.

To Conclude

For anyone who is well-versed with SQL knows that it is the most widely used Database language. Thus, the most essential part to learn is SQL for Data Science to power ahead in your career.

(Video) Top 25 SQL Interview Questions and Answers(The BEST SQL Interview Questions)

Wondering where to learn the highly coveted in demand skills for free? Check out the courses on Great Learning Academy. Enroll to any course, learn the in demand skill and get your free certificate. Hurry!

FAQs

How do I prepare for SQL interview? ›

Next steps: Preparing for your SQL interview
  1. Retrieve Data using Single-Table SQL Queries.
  2. Retrieve Data with Multiple-Table SQL Queries.
  3. Querying Databases Using SQL SELECT statement.
  4. Aggregate Data in SQL using MySQL Workbench.
  5. Mastering SQL Joins.
  6. SQL CASE Statements.
May 26, 2022

What is SQL question answer? ›

What is SQL? SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database. Standard SQL Commands are Select.

What is SQL very short answer? ›

Q #1) What is SQL? Answer: Structured Query Language SQL is a database tool that is used to create and access the database to support software applications.

What are the 5 basic SQL commands? ›

Some of The Most Important SQL Commands
  • SELECT - extracts data from a database.
  • UPDATE - updates data in a database.
  • DELETE - deletes data from a database.
  • INSERT INTO - inserts new data into a database.
  • CREATE DATABASE - creates a new database.
  • ALTER DATABASE - modifies a database.
  • CREATE TABLE - creates a new table.

What is the full form of SQL? ›

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

How do I find duplicates in SQL? ›

How to Find Duplicate Values in SQL
  1. Using the GROUP BY clause to group all rows by the target column(s) – i.e. the column(s) you want to check for duplicate values on.
  2. Using the COUNT function in the HAVING clause to check if any of the groups have more than 1 entry; those would be the duplicate values.
Sep 2, 2020

What is a unique key in SQL? ›

The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.

What is DDL in SQL? ›

Data Definition Language (DDL) Statements

Create, alter, and drop schema objects. Grant and revoke privileges and roles. Analyze information on a table, index, or cluster. Establish auditing options. Add comments to the data dictionary.

What is a primary key in SQL? ›

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

How do you delete duplicate rows in SQL? ›

To delete the duplicate rows from the table in SQL Server, you follow these steps:
  1. Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
  2. Use DELETE statement to remove the duplicate rows.

What is the symbol in SQL? ›

Wildcard Characters in SQL Server
SymbolDescription
%Represents zero or more characters
_Represents a single character
[]Represents any single character within the brackets
^Represents any character not in the brackets
1 more row

What are SQL commands? ›

SQL commands are the instructions used to communicate with a database to perform tasks, functions, and queries with data. SQL commands can be used to search the database and to do other functions like creating tables, adding data to tables, modifying data, and dropping tables.

What are SQL data types? ›

Data types in SQL Server are organized into the following categories:
  • Exact numerics. Unicode character strings.
  • Approximate numerics. Binary strings.
  • Date and time. Other data types.
  • Character strings.
  • bigint. numeric.
  • bit. smallint.
  • decimal. smallmoney.
  • int. tinyint.
Mar 22, 2022

How delete a column in SQL? ›

Right-click the column you want to delete and choose Delete Column from the shortcut menu. If the column participates in a relationship (FOREIGN KEY or PRIMARY KEY), a message prompts you to confirm the deletion of the selected columns and their relationships. Choose Yes.

What is DCL and DML? ›

DML – Data Manipulation Language. DCL – Data Control Language.

What language is SQL? ›

SQL stands for Structured Query Language, which is a programming language used to communicate with relational databases. The acronym is pronounced like the word sequel, but some people will just use the three letters S, Q, and L.

Is Excel a SQL database? ›

As an example, Excel cells let you store strings of information like “January 3, 2020,” whereas relational databases using SQL would divide the date into three separate cells. Excel is more visual than SQL—instead of writing commands, users get to directly work with and manipulate their data in tables.

Is SQL a coding? ›

Yes, SQL is a programming language.

How can I improve MySQL skills? ›

Here's the best way to learn SQL:
  1. Step 1: Determine why you want to learn SQL. ...
  2. Step 2: Learn the basic syntax. ...
  3. Step 3: Start working on guided projects. ...
  4. Step 4: Familiarize yourself with helpful SQL resources. ...
  5. Step 5: Build your own SQL projects. ...
  6. Step 6: Make more advanced projects.
Feb 17, 2021

How can I practice SQL at home? ›

  1. 4 steps to start practicing SQL at home. Download MySQL and do it yourself. ...
  2. Download the software. Your first task is to download database software. ...
  3. Create your first database and data table. Great — we now have the software we need to get started. ...
  4. Get your hands on some data. ...
  5. Get curious.
Nov 19, 2020

Which platform is SQL used? ›

SQL - SQL Server

Microsoft's SQL Server is steadily on the rise in the commercial world gaining popularity slowly. This platform is only available to Windows users at this time, but offers its users very user-friendly interface that is great for beginning SQL developers.

What is SQL Indexing? ›

A SQL index is used to retrieve data from a database very fast. Indexing a table or view is, without a doubt, one of the best ways to improve the performance of queries and applications. A SQL index is a quick lookup table for finding records users need to search frequently.

What is left join in SQL? ›

The LEFT JOIN command returns all rows from the left table, and the matching rows from the right table. The result is NULL from the right side, if there is no match.

How do I query NULL values in SQL? ›

How to Test for NULL Values?
  1. SELECT column_names. FROM table_name. WHERE column_name IS NULL;
  2. SELECT column_names. FROM table_name. WHERE column_name IS NOT NULL;
  3. Example. SELECT CustomerName, ContactName, Address. FROM Customers. WHERE Address IS NULL; ...
  4. Example. SELECT CustomerName, ContactName, Address. FROM Customers.

What is null value? ›

A null value in a relational database is used when the value in a column is unknown or missing. A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types).

Can primary key be null? ›

A primary key defines the set of columns that uniquely identifies rows in a table. When you create a primary key constraint, none of the columns included in the primary key can have NULL constraints; that is, they must not permit NULL values.

What is not null in SQL? ›

The NOT NULL constraint enforces a column to not accept NULL values, which means that you cannot insert or update a record without adding a value to this field.

What is DCL command? ›

Data Control Language (or DCL) consists of statements that control security and concurrent access to table data. COMMIT. Instructs the XDB Server to make permanent all data changes resulting from DML statements executed by a transaction.

What is the full form of DCL? ›

The Full Form Of DCL is a Data Control Language. DCL commands are used to control the access data stored in a database. To execute any operation related to a database such as creating the table, managing the data, we require permission.

What is TCL in SQL? ›

TCL stands for Transaction Control Languages. These commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands. A Transaction is a set of SQL statements that are executed on the data stored in DBMS.

Can we change column name in SQL? ›

You select the table with ALTER TABLE table_name and then write which column to rename and what to rename it to with RENAME COLUMN old_name TO new_name .

Can foreign key be null? ›

A foreign key containing null values cannot match the values of a parent key, since a parent key by definition can have no null values. However, a null foreign key value is always valid, regardless of the value of any of its non-null parts.

What is field in SQL? ›

Records and Fields in SQL

Tables contain rows and columns, where the rows are known as records and the columns are known as fields. A column is a set of data values of a particular type (like numbers or alphabets), one value for each row of the database, for example, Age, Student_ID, or Student_Name.

How do you search in SQL? ›

SQL Server Management Studio Object Explorer
  1. browse to the database you want to search through.
  2. write the name (full or partial) of the database object in the Search text box.
  3. press Enter to start the search process.
Feb 15, 2020

How do you count in SQL? ›

SQL COUNT() Function
  1. SQL COUNT(column_name) Syntax. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: ...
  2. SQL COUNT(*) Syntax. The COUNT(*) function returns the number of records in a table: ...
  3. SQL COUNT(DISTINCT column_name) Syntax.

How do I ensure no duplicates in SQL? ›

The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.

What is colon in SQL? ›

The colon (:) is used to select "slices" from arrays. (See Section 5.12.) In certain SQL dialects (such as Embedded SQL), the colon is used to prefix variable names. The asterisk (*) has a special meaning when used in the SELECT command or with the COUNT aggregate function.

Is * a wildcard in SQL? ›

SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table. Sr.No. Matches one or more characters. Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

What are the 3 types of SQL commands? ›

There are 3 main types of commands. DDL (Data Definition Language) commands, DML (Data Manipulation Language) commands, and DCL (Data Control Language) commands.

What is Sqlplus command? ›

SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to: Enter SQL*Plus commands to configure the SQL*Plus environment. Startup and shutdown an Oracle database. Connect to an Oracle database.

How do I view tables in SQL? ›

Right-click the Products table in SQL Server Object Explorer, and select View Data. The Data Editor launches. Notice the rows we added to the table in previous procedures. Right-click the Fruits table in SQL Server Object Explorer, and select View Data.

What are the 5 data types? ›

Most modern computer languages recognize five basic categories of data types: Integral, Floating Point, Character, Character String, and composite types, with various specific subtypes defined within each broad category.

How many commands are there in SQL? ›

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

What is VARCHAR2 in SQL? ›

The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database. When you create a table with a VARCHAR2 column, you must specify the column length as size optionally followed by a length qualifier.

Truncate: This is a command used when a user wants to delete all data in a table without altering the structure of the table. Roles and Profiles can share names, but neither Roles nor Profiles can share names with databases.. Constraints are rules and restrictions in SQL that are applied to a table or column to prevent unwanted data from being inserted into the table or column.. Constraints maintain the accuracy and integrity of the data in the table.. SQL Server has the following types of constraints’:. Consistency- This property maintains data integrity to ensure that valid data are inserted into the database.. The term “normalization” refers to the process of removing data redundancy and improving data integrity in a table.. SQL is a querying and operating language for database systems.. A database management system (DBMS) is software that allows you to create, save, manage, update, and retrieve data from databases.. On the other hand, a relational database management system (RDBMS) refers to software that enables the development and management of databases for efficient data retrieval, updating, and storage.. Only a single user is supported by the Database Management System but multiple users can access databases using a Relational Database Management System.

When a table is joined to itself, every table row has been coupled with itself and each other row in the table.. The cross joins in SQL integrate each row from the first table with every row from the second table.. SQL provides a platform for querying, storing, retrieving, and manipulating data from the relational databases management system (RDBMS).. Define what is a database!. This database is maintained inside a database management system with the ability to data access, data storage, data retrieval, and data manipulation in every aspect.. What are tables and field in SQL?. In databases, information or data is stored in the form of tables.. Define inner join in SQL.. Define right join in SQL.. What are the main differences between SQL, MySQL, and SQL server?

Using SQL, you can insert records, update and delete records, and can also create new objects such as databases and tables.. SQL SELECT - Retrieve data from DB SQL INSERT - Insert data to DB SQL UPDATE - Update data to DB SQL DELETE - Delete data from the DB. DDL statements are the subset of SQL statements that helps you to change the structure of the database schema in some way by creating, deleting, or modifying schema objects such as databases, tables, and views.. CREATE DATABASE - creates a new database ALTER DATABASE - modifies the database DROP DATABASE – deletes the database CREATE TABLE - creates a new table ALTER TABLE - modifies the table DROP TABLE – deletes the table. A) SELECT CustomerName, SalesDate FROM Sales ORDER BY SalesDate DESC, CustomreName; SELECT TOP(1) Salary FROM { SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary DESC } AS EMP ORDER BY SALARY. B) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName; SELECT TOP(1) Salary FROM { SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary } AS EMP ORDER BY SALARY DESC. C) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName, SalesDate; SELECT TOP(1) Salary FROM { SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary } AS EMP ORDER BY SALARY. D) SELECT CustomerName, SalesDate FROM Sales ORDER BY CustomreName, SalesDate DESC; SELECT TOP(1) Salary FROM { SELECT DISTINCT TOP(10) Salary FROM EMPLOYEE ORDER BY Salary DESC } AS EMP ORDER BY SALARY DESC. A) Select name from instructor where salary between 1,00,000 and 90,000B) Select name from instructor where salary < 90,000 and salary >= 1,00,000C) Select name from instructor where salary <= 90,000 and salary >= 1,00,000D) Select name from instructor where salary between 90,000 and 1,00,000. A) Select e* from emp e join dept d on e.deptno=d.deptnoB) Select e* from emp e, dept d where e.deptno=d.deptnoC) Select e* from emp e inner join dept d on e.deptno=d.deptnoD) Select *from emp INNER JOIN dept USING (deptno). A) Updates the statistics of every table in the database only once at 1:00 AM, writes a log entry to SQL Server log and NT application logB) Updates the statistics of every table in the database every night at 1:00 AM, writes a log entry to SQL Server log and NT application logC) Error MessageD) Updates the statistics of every table in the database every night at 1:00 AM. A) A view is a group of SQL statements forming a logical unit of work.B) Views are queries that can provide users with a managed (limited) presentation of data or in other words, provides an abstraction of the table.C) Views are nothing but a set of nested inner and outer queries.D) Views are created using CREATE TABLE statements.

SQL is short for Structured Query Language, with the primary purpose of interacting with databases.. SQL is the default query language that is used in communicating with RDBMS (Relational Database Management Systems) and also for carrying out various operations on different data types that are related to data manipulation.. DDL or Data Definition Language: DDL helps to define the database’s data structure during the starting stage before the database is created.. Create table Drop table Alter table. DML is used while performing operations like adding data to the database using the ‘Insert’ command, keeping data up-to-date with the ‘Update’ command and removing data from databases with the ‘Delete’ command.. Relational Database: This database is structured like a table.. The values given through the rows and columns are closely related to each other.. Object-oriented Database: Data values and operations are structured as objects in this database.. One table can have a maximum of one primary key, consisting of either single or multiple fields.. A foreign key can be defined as a set of attributes or an attribute that provides a reference to a primary key within another table.. Without an index on columns within the WHERE clause, the SQL server will have to go over entire tables and check every row to ascertain a match.. Clustered Index: A clustered index sorts through rows of data using their key values.. PL/SQL is made up of code that is used to create procedures and functions.. PL/SQL can contain SQL code as it is an extension of SQL itself.. A transaction is a defined logical data operation.

SQL means structured query language that is used to communicate with the database, retrieve information and manipulate data stored in the database.. So if you want to crack the interview and get your dream job you will need to prepare yourself with all kinds of SQL interview questions that are usually asked to test your technical skills.. Here we present to you the top 10 toughest SQL developer interview questions along with their answers that will help you prepare for your SQL interview.. ACID is a set of properties in the database that ensures that the transactions are processed reliably even in case of power failures or error events.. The SQL server database has 2 kind of files *.MDF files are the actual physical database files where the data is stored.. When multiple values in the set have same ranking, then the RANK() function will assign non-consecutive ranks to the values resulting in gaps.. Our experts share tips on preparing for job interviews relating to SQL and other database skills.

The key tasks for database management systems are – create database, update database or manipulate the databases.. 2.Different database operations : The database management system is used for multiple database operations to create the database, manipulate the database, to delete the data from database or for controlling the transactions.. It is the subset of a database that defines the data structure of the database when the database is created.. Answer :  Allows users to access data in relational database management systems. Allows users to define the data in database and manipulate that data. Allows users to create and drop databases and tables. Allows users to create view, stored procedure, functions in a database. Allows users to set permissions on tables, procedures, and views.. SELECT – Extracts data from a databaseUPDATE – Updates data in a databaseDELETE – Deletes data from a databaseINSERT INTO – Inserts new data into a databaseCREATE TABLE – Creates a new tableALTER TABLE – Modifies a tableDROP TABLE – Deletes a tableCREATE INDEX – Creates an index (search key)DROP INDEX – Deletes an index. The view in SQL is nothing but the snapshot which will give you data from multiple tables in single go.. 2.Complex view : The complex view is view or snapshot where user can collect the data from multiple tables using joining conditions or any other way.. ViewMaterialized Views(Snapshots)1.View is nothing but the logical structure of the table which will retrieve data from 1 or more table.1.Materialized views(Snapshots) are also logical structure but data is physically stored in database.2.You need to have Create view privileges to create simple or complex view2.You need to have create materialized view ‘s privileges to create Materialized views3.Data access is not as fast as materialized views3.Data retrieval is fast as compare to simple view because data is accessed from directly physical location4.There are 2 types of views:1.Simple View2.Complex view4.There are following types of Materialized views:1.Refresh on Auto2.Refresh on demand5.In Application level views are used to restrict data from database5.Materialized Views are used in Data Warehousing.Difference between View and materialized view UnionUnion ALL 1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records 2.Syntax: Select col1,col2…from table1;UnionSelect col1,col2…from table2; 2.Syntax: Select col1,col2…from table1;Union allSelect col1,col2…from table2;3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records3.Union all is preferable operator in Performance tuning.Difference Between Union and Union all19.What is difference between decode and Case statement?

RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table.. There can be many unique constraint defined per table, but only one Primary key constraint defined per table.. 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.. This is a keyword used to query data from more tables based on the relationship between the fields of the tables.. Right join return rows which are common between the tables and all rows of Right hand side table.. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.. Left join return rows which are common between the tables and all rows of Left hand side table.. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.. 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.. Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table.

SQL is the kernel of the relational database which is used for accessing and managing database MySQL is an RDMS ( Relational Database Management System ) such as SQL Server, Informix etc .. A relational database management system ( RDBMS ) is a type of database management system ( DBMS ) that stores data in a row-based table structure that links related data components.. SQL skills help data analysts in the initiation, care, and retrieval of data from relational databases, which divide data into column and rows.. Database Management Structuring a database Creating SQL clauses and statements SQL System SKills like MYSQL, PostgreSQL PHP expertness is utilitarian .. NoSQL vs SQL In summary, the following are the five major distinctions between SQL and NoSQL : relational databases are SQL, while non-relational databases are NoSQL.. Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a not clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching .. Better Database organization More Tables with smaller rows Efficient data access Greater Flexibility for Queries cursorily find the information Easier to implement Security Allows easily modification reduction of pleonastic and duplicate data More covenant Database guarantee Consistent data after modification. How to create empty tables with the same structure as another table?To create empty tables: Using the INTO operator to fetch the records of one table into a fresh board while setting a WHERE clause to false for all entries, it is possible to create empty tables with the lapp structure.. Click Start > Programs > Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server plan group .

When a single column value in one table has a minimum of one dependent column value in some other table, a one-to-many relationship exists.. The foreign key is a constraint that ensures that the values of the child table appear in the parent table.. The foreign key of one table is the primary key of the other, and a table can have several foreign keys.. Aggregating data for performance Customizing the schema and data for a set of users Controlling access to columns and rows of data. To hide the SQL Server Instances, we need to make changes in SQL Server Configuration Manager, and to launch it, the following steps are needed:. Select instance of SQL server Select properties after right-clicking Set Hide Instances to Yes and click on APPLY Post changes, restart the instance of SQL Server. Database Consistency Checker (DBCC) checks the consistency of the database; It helps in reviewing and monitoring the maintenance of database, tables, and operation validation.. When a table has more than one candidate key (i.e., candidate for primary keys), one becomes the primary key, and the rest are the alternate keys.. They also allow users to select data from one table depending on the data of the other table.. To handle this data, there is a need for a database and database management system.. Knowing MS SQL opens the door to become an SQL Specialist and an SQL Developer .. To ace the SQL server interview, one needs to be prepared to face SQL server interview questions.

SELECT * FROM Student WHERE Stu_Age = 20;. SELECT * FROM Student WHERE Subject_ID = ‘BCA103’ and Subject_ID = ‘BCA106’ ;. We can easily find the maximum and minimum values of any integer column using the MAX and MIN aggregate functions.. CREATE TABLE Student_Marks SELECT * FROM Student;. SELECT Stu_Name, Stu_Marks FROM Student WHERE Stu_Age = 20;. SELECT * FROM Student WHERE Stu_Marks > 82 and Stu_Age = 22;. SELECT * FROM Student WHERE Stu_Name LIKE ‘%m’;. This syntax uses the SQL MINUS operator, which shows the values of Table1 that does not exist in Table2.. SELECT * FROM Student_Age;. The following query adds the Stu_Address column to the existing Student table:

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements.. Single row sub query which returns only one row Multiple row sub query which returns multiple rows Multiple column sub query which returns multiple columns to the main query.. What is the command used to get the version of SQL Server?. Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure.. What are Magic Tables in SQL Server?. How to delete duplicate rows in SQL Server?. Where are SQL Server user names and passwords stored in SQL Server?. How data can be copied from one table to another table?. This command is used to insert data into a table which is already created.. XML data type is used to store XML documents in the SQL Server database.

What is SQL?. With the help of SQL , we can extract data from a database, modify this data and also update it whenever there is a requirement.. The above command will completely delete all the data present in the table along with the table itself.. This will be the command to create a database in sql:. CREATE DATABASE database_name.. Different database management systems have different queries to see all the tables.. When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.. following queries are useful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;. Let’s say, we have a table like this:. Whenever we give the constraint of unique key to a column, this would mean that the column cannot have any duplicate values present in it.. When it comes to SQL Server, it is not possible to rename the column with the help of ALTER TABLE command, we would have to use sp_rename.. The columns and the data types must be the same in the SELECT statements.. This command provides another name to a table or a column.. Launch the SQL Server Management Studio In the Object Explorer window pane, right-click on Databases and select ‘New Database’ Enter the Database Name and click on ‘Ok’.

The statements of this standard language can be utilized to finish the works that involve things like updating data on a database and retrieving data from a database.. Structured Query Language is the main part of the relational database, which can be utilized for gaining access to the Database and also plays a major role in the management of the Database.. The Database Management System lets the candidate to connect with the Database.. Constraints are those elements of the table which can be used to classify the data limit type of the table.. We can term data integrity by saying that it maintains the consistency of the data which is stored in the Database.. A clustered index can be utilized for recovering information from the Database in an easy way.. Clustered index basically changes the way in which the storage of records is maintained in a database in a changed manner.. On the other hand, the non clustered index doesn’t change the way of storing information.. Joins are basically used to recover data in a database in between the tables.

SQL functions essentially as a database language for creating and deleting databases.. By structuring fields and tables in databases, normalization helps reduce data reliance and redundancy.. Data flows into a data warehouse from relational databases, transactional systems, and other sources.. CDC records recent SQL Server tables, consisting of INSERT, DELETE, and UPDATE operations.. Syntax: CREATE TRIGGER trigger_name To create empty tables with the same structure as another table, developers use the INTO operator.. Here are five basic-level SQL interview questions: Define database What is normalization?. What are joins in SQL?. What are Joins in SQL?

Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.. CREATE: To create databases and database objects ALTER: To alter existing database objects DROP: To drop databases and databases objects TRUNCATE: To remove all records from a table but not its database structure RENAME: To rename database objects. SELECT: To select specific data from a database INSERT: To insert new records into a table UPDATE: To update existing records DELETE: To delete existing records from a table. The difference between the clustered and non-clustered index in SQL is as follows: Clustered Index: It is used for easy retrieval of data from the database and it is faster.One table can only have one clustered indexIt alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index.. A database query is a request for data or information from a database table or combination of tables.. So we cant delete required rows aloneWe can delete specific rows using DELETEWe can only delete all the rows at a time using TRUNCATEDelete is a DML commandTruncate is a DDL commandDelete maintains log and performance is slower than TruncateTruncate maintains minimal log and performance wise fasterWe need DELETE permission on Table to use DELETE commandWe need at least ALTER permission on the table to use TRUNCATE command This is one of the tricky SQL Interview Questions.. The tester who is performing GUI Testing doesn’t need to know Structured Query Language The tester who is performing Database Testing needs to know Structured Query Language GUI Testing includes invalidating the text boxes, check boxes, buttons, drop-downs, forms etc., majorly the look and feel of the overall application Database Testing involves in verifying the integrity of data in the front end with the data present in the back end.

There can be many non-clustered indexes in a table, however, there can be only one clustered index.. View AnswerView AnswerAs the name suggests, a synonym is used to give different names to the same object in the database.. View AnswerView AnswerA Scalar subquery is when a query returns just one row and one column of data.. View AnswerView AnswerWith the auto-increment command, one can generate unique numbers when new records are added to a table.. Here, data is stored, analyzed, and reported.. However, recruiters are looking for skilled and knowledgeable SQL developers and therefore, it is imperative to prepare SQL interview questions and answers well before you appear for your SQL interview.. Recruiters hiring SQL developers must ensure that they ask both the technical SQL interview questions and the soft-skills-related questions to get the best candidate for the job.

If you want to build your career in data science, data analysis, or simple database systems, you must consider SQL in your skillset.. SQL is Structured Query Language , a computer language needed to store, attempt to alter, and fetch data stored in a database system.. Relational Database Management System also called RDBMS and it is the foundation of all present database systems such as ORACLE, SQL, MS SQL Server, IBM DB2, MySQL, and so on.. Hierarchical Database Network Database Relational Database Object-oriented Database. A join clause is used in a SQL database to combine records or rows from multiple, two or more, tables relevant to the research columns between the tables.. The cross joins in SQL integrate each row from the first table with every row from the second table.. A query is a sequence of commands crafted in a query language such as SQL that allows a person to access data stored in a database.. SQL provides a platform for querying, storing, retrieving, and manipulating data from the relational databases management system (RDBMS).. Most of the systems today such as MS Access, MySQL, Postgres, Sybase, Oracle, SQL Server, etc use SQL as the main query and database language.. This database is maintained inside a database management system with the ability to data access, data storage, data retrieval, and data manipulation in every aspect.. Relational Databases (RDBMS) Hierarchical Databases (DBMS) Network Databases (IDMS) Object-oriented databases. What is a join in SQL and the most common used SQL joins?. Define inner join in SQL.. Define right join in SQL.

b. DML ( Data Manipulation speech ) : Used to manipulate already existing data in the database, commands like SELECT, UPDATE, INSERT. c. DCL ( Data Control Language ) : Used to control access to data in the database, commands like GRANT, REVOKE .. It can be used without the GROUP by clauseThe HAVING clause cannot be used without the GROUP BY clause2The WHERE clause selects rows before groupingThe HAVING clause selects rows after grouping3The WHERE clause cannot contain aggregate functionsThe HAVING clause can contain aggregate functions4WHERE clause is used to impose a condition on SELECT statement as well as single row function and is used before GROUP BY clauseHAVING clause is used to impose a condition on GROUP Function and is used after GROUP BY clause in the query5SELECT Column,AVG(Column_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmaeSELECT Columnq, AVG(Coulmn_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae Having column_name>or SQL DatabasesNoSQL Databases Data Storage Model Tables with fixed rows and columns Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges Development History Developed in the 1970s with a focus on reducing data duplication Developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices.. SQLTSQL A domain-specific language used in programming and designed for managing data held in a Relational Database Management SystemMicrosoft’s proprietary version of SQL for its SQL Server RDBMSStands for Structured Query LanguageStands for Transact Structured Query LanguageQuery Language to Manage Data in an RDBMSAn Extension of SQL That is Used on MS SQL ServerDeveloped by IDMDeveloped by MicrosoftIt is Possible to Embed SQL into TSQLIt is Not Possible to Embed TSQL into SQLHelps to Process and Analyze the Data Using Simple QueriesHelps to Add Business Logic into an Application SQL Server MySQL Server Relational Database Management SystemOpen-source Relational Database Management SystemDeveloped by MicrosoftDeveloped by OracleAvailable in Multiple Languages Only Available in EnglishSupports Windows, Linux and ContainersSupports Windows, Linux and MacCommercial Open-SourceProgrammed in C++Programmed in C and C++Compatible with Kubernetes, Apache Spark and Hadoop Distributed File SystemHas Difficulty Operating with Kubernetes, Apache Spark and Hadoop Distributed File System MongoDBMySQL When you need high availability of data with automatic, fast, and instant data recoveryIf you’re just starting and your database is not going to scale much, MYSQL will help you in easy and low-maintenance setupIn future, if you’re going to grow big as MongoDB has in-built sharding solutionIf you want high performance on a limited budget If you have an unstable schema and you want to reduce your schema migration cost If you have fixed schema and data structure isn’t going to change over time like WikiPediaIf you don’t have a database administrator If high transaction rate I going to be your requirementIf most of the services are cloud-based, MongoDB is best suitable for youIf data security is the topmost priority, MySQL is most suited DBMS SQL server has stayed on circus tent as one of the most democratic database management products ever since its first gear handout in 1989 by Microsoft Corporation.. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads Click on the search icon and type in – SQL Server 2008 download Click on the result link to download and save SQL Server 2008.. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads Click on the search icon and type in – SQL Server 2017 download Click on the result link to download and save SQL Server 2017.. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads Click on the search icon and type in – SQL Server 2014 download Click on the result link to download and save SQL Server 2014.. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads Click on the search icon and type in – SQL Server 2012 download Click on the result link to download and save SQL Server 2012.. Click on the below SQL Server official release link: https://www.microsoft.com/en-in/sql-server/sql-server-downloads Click on the search icon and type in – SQL Server 2012 download Click on the result link to download and save SQL Server 2012.. How to structure a database Managing a database Authoring SQL statements and clauses Knowledge of popular database systems such as MySQL Working knowledge of PHP SQL data analysis Creating a database with WAMP and SQL. CREATE DATABASE ALTER DATABASE CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX DROP INDEX. CREATE DATABASE ALTER DATABASE CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX DROP INDEX

What is SQL?. Structured storage: Data is stored in structured data tables which makes data retrieval easier.. Views: Virtual tables or views help in hiding sensitive data.. 1NF- First Normal Form states that every attribute(represented by column) of the database table should be atomic.. We use a database key to uniquely identify a row or record in a database table.. A table can have only one primary key to uniquely identify rows.. Foreign key: It is a column which shows a relationship with the primary key of another table.. Views can provide access permissions to users for executing various commands.. DML commands cannot be used on views because they are not stored physically in storage.. They are database objects which store the rows returned by the query and point to a single row at a time.. There are two types of cursors that you should know for SQL interview questions:. Different types of SQL Joins are:

Sometimes it is mistakenly referred as SQL, but both are different, as SQL is a language whereas SQL Server is a Microsoft product that supports SQL.. In the stored procedure, while you can’t use try-catch in functions.. SQL Server supports recursive stored procedure which calls by itself.. Stored procedures provide better security for data.. You have to make a change in the SQL Server Configuration Manager to hide the SQL Server instances.. SQL server agent store scheduled administrative tasks information using SQL server.. SQL Server agent is a component of Microsoft SQL Server.. to a reference type (object) is called boxing.. Many servers support this technique for maintaining a backup server, such as Microsoft SQL Server, 4D server, MySQL, and PostgreSQL.. On each of secondary database, log backups are applied particularly.. There are three types of replication in SQL Server.. A user can delete a TABLE from the database by using SQL Server Management Studio or by Transact-SQL in SQL Server. We can use encryption for security of data in the database in SQL Server.

SQL is a language designed specifically for communicating with databases.. DROP TABLE – deletes the table from the database.. A Unique key constraint uniquely identifies each record in the database.. The constraint can be specified while creating or altering the table statement.. A ROLLBACK statement gets issued when the transaction ends, and the following is true.. There are three types of Indexes in SQL:. Matching records of the first and second query and other rows from the first query will be displayed as a result set.. Unique index can be applied automatically when the primary key is defined.. SQL is a language designed specifically for communicating with databases.

Ans: Relationships between tables describe how a row in a table is related to a row or rows of another table in a database.. One to one: When a row in a table has only one related row in another table One to many: When a row in a table has multiple related rows in another table Many to many: When a row has multiple related rows in another table and vice-versa.. Character mode data file: Bulk copy from a table or view into another table in a different format Bulk copying from a data file into a table or view Loading data into program variables initially and then bulk copying into a table or view.. Ans: A table variable functions faster than a temporary table because table variables are stored in memory, whereas temporary tables are stored in a disk.. INNER JOIN: This command helps to return records that are common to both the tables LEFT JOIN: This command helps to return values that are common to each other along with the complete records of the left table RIGHT JOIN: This command helps to return values that are common to each other along with the complete records of the right table FULL JOIN: This command helps return all the tables' records when there is a match between the two.. Clustered IndexNon-clustered Index It describes the order in which data is stored in tables physically.It doesn’t sort tables physically inside a table but creates a logical order for stored data.Each table will have only one clustered index.There could be many non-clustered indexes for a table.Less storage is required since they only sort tables.A non-clustered index is stored in one location, and data is stored in another.. Copy-only backup: It is a special-use backup that is independent of the regular backups Data backup: It is a backup of data either entirely or partially Database backup: It is a backup of a complete database Differential backup: It is a record of only the changes made in a database after a database backup event Full backup: It is a backup of full data with the necessary transaction log for restoration Log backup: It is a backup of all transaction logs File backup: It is a backup of files in a database Partial backup: It is a backup of files from a specific filegroup such as primary, every read/write, and optionally specified read-only files.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns(fields).. The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.. A FOREIGN KEY is a field(or collection of fields) in one table that refers to the PRIMARY KEY in another table.. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.. ALTERNATE KEYS is a column or group of columns in a table that uniquely identifies every row in that table.. A table can have multiple choices for a primary key but only one can be set as the primary key.. All the keys which are not primary keys are called Alternate keys.. The difference between the compound and the composite key is that any part of the compound key cab is a foreign key, but the composite key may or may not be a part of the foreign key.

SQL query to fetch all the Employees from EmployeeDetails who joined in the Year 2020.. Write an SQL query to fetch project-wise count of employees.. Write an SQL query to fetch the count of employees working in project ‘P1’.. Write an SQL query to fetch common records between two tables.. MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the sub query-. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as subquery to find the fullName of the employee from EmployeeDetails table.. Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020.. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS.. Write an SQL query to join 3 tables.. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.. Write an SQL query to fetch only even rows from the table.. Ans, Using Top keyword (SQL Server)-

The database is also able to support structure Query Language (SQL).. Examples of transactions include select, delete, insert or update.. The system ensures consistency and durability by focusing on a committed transaction despite any type of system failure.. MySql TRIGGERS is a database store program that is automatically executed to respond to a specific command that leads to events such as updating, deletion or insertion.. The following are the six MySql table TRIGGERS.. CREATE: it creates tables, triggers, functions, index and stored procedures in the data base.. This command deletes data from the database.. 1CHAR is an SQL data type that stores a fixed string of characters of a particular length.

SQL Interview Questions and Answers on Relational Database Management System Fundamentals, SQL Language Fundamentals, and SQL Queries & Subqueries.. SQL is used to Create New Databases, New Tables in a Database, Insert records in a Database, Update records in a Database, and Delete records in a Database.. SQL is used to retrieve data from a Database, Execute queries against a Database, Create stored procedures in a Database, Create views in a Database, Set permissions on tables, procedures, and views.. • Creating new databases • Creating new tables in a database • Inserting records in a database • Updating records in a database • Deleting records from a database • Retrieving data from a database • Executing queries against a database • Creating stored procedures in a database • Creating views in a database • Setting permissions on tables, procedures, and views Etc…. SQL – Structured Query Language used to manage the relational databases like Oracle, MS SQL Server, MySQL, Sybase, etc,. SQL Server and MySQL are relational database management systems used to store, retrieve, modify and administer a database using SQL.. Relational Database Management System/SQL Database: The data is stored in relations (tables).. • Better Database organization • More Tables with smaller rows • Efficient data access • Greater Flexibility for Queries • Quickly find the information • Easier to implement Security • Allows easy modification • Reduction of redundant and duplicate data • More Compact Database • Ensure Consistent data after modification. SELECT is a SQL command that is used for selecting data from a database.. Important Data Definition Language Operations 1) Create a Database 2) Use Database 3) Rename a Database 4) Drop Database 5) Create a Table 6) Rename Table 7) Add a Column to exiting Table 8) Add multiple columns to existing Table 9) Modify an existing column 10) Rename a Column 11) Drop a Column 12) Truncate a Table 13) Drop a Table. Drop Table command is used to delete complete Table (Data and Table Structure) from the Database.

Procedure : A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller.. There are two type of exceptions: pre_defined exception and user_defined exception.. A function returns a value while a stored procedure doesn?t return a value.. There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.. A trigger is automatically executed without any action required by the user, while, a stored procedure is explicitly invoked by the user.. Stored procedures and functions Packages Triggers Cursors. The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.. Mutating table error is occurred when a trigger tries to update a row that it is currently using.. A cursor is a temporary work area created in a system memory when an SQL statement is executed.

Videos

1. SQL interview questions and answers 2022 part 2
(Learn Coding with AK)
2. Basic SQL Interview Questions (with Answers + Exam/Test Tips)
(Coding With Brett)
3. Ms SQL Server DBA Experienced Interview Questions And Answers - Interview 12
(Real Time Interviews)
4. SQL Interview Questions and Answers | Asked in my TCS and other Companies Interview | TechnonTechTV
(TechnonTechTV)
5. Top 50 SQL Server Interview Questions |Part-1| Generally asked Basics level top SQL Questions 2022
(Clearcat .net)
6. Top 65 SQL Interview Question and Answers | SQL Training | Edureka Rewind - 4
(edureka!)

You might also like

Latest Posts

Article information

Author: Terrell Hackett

Last Updated: 08/14/2022

Views: 5409

Rating: 4.1 / 5 (52 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Terrell Hackett

Birthday: 1992-03-17

Address: Suite 453 459 Gibson Squares, East Adriane, AK 71925-5692

Phone: +21811810803470

Job: Chief Representative

Hobby: Board games, Rock climbing, Ghost hunting, Origami, Kabaddi, Mushroom hunting, Gaming

Introduction: My name is Terrell Hackett, I am a gleaming, brainy, courageous, helpful, healthy, cooperative, graceful person who loves writing and wants to share my knowledge and understanding with you.