- Sql Interview Questions For Testers
- Sql Queries For Practice With Answers Free Download
- Pdf Sql Query Interview Questions And Answers For Management Positions
If you're looking for SQL Interview Questions and Answer for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many reputed companies in the world.
SQL database market continues even stronger by 2020 will be 70%.According to research SQL by Microsoft grew at 10.32%, while Oracle grew 3.5%. In the upcoming era, the competition will be more heated than is has been for years. So, You still have the opportunity to move ahead in your career in SQL certification guide. Mindmajix offers Advanced SQL Interview Questions and Answers 2019 that helps you in cracking your interview & acquire dream career as SQL Developer. SQL Interview Questions Q1) Difference between SQL Vs NoSQL?Ans. SQLOracleIt is more scalable and secure than OracleOracle too is secure and scalable but not up to the extent SQLIt widely supports procedural extensionsThe support to the same is limitedQ 5 ) How the Inner Join in SQL is different from that of Outer Join?Ans: An Inner join is the one that is useful for the purpose of returning the rows provided at least two tables are met critically. On the other hand, the outer join is the one that is useful for returning the value of rows and tables that generally include the records that must be same in all the tables.Q 6 ) Can you tell something about the Primary key in SQL and what is its significance?Ans: It is basically an array or a group of fields that generally specify a row.
It is considered as one of the unique keys that always have some defined or specific value. Generally, the users need not worry about anything when it is enabled as it cannot have a null value.It is capable to identify all the records in a database simply and the users are free to get the best possible outcome with minimum efforts. This is exactly what that makes sure of uniqueness.Q 7 ) What do you know about the database testing and how it can help to get useful results for the database users?Ans: It is basically nothing but the back end testing or data testing.
It generally involves keeping an eye on the integrity of the data an organization use. It generally validates some of the very useful tasks such as database, indexes, columns, tables as well as triggers.
IT also make sure that no duplicate data exist in the database which causes a very large number of problems and the best part is the junk records can also be trashed in a very reliable manner. The updating of record is also a task that can be made easy with the help of this approach.Q 8 ) Tell something you know about the SQL constraints?Ans: These are some important rules in the SQL which are responsible for the restrictions when it comes to deleting, updating or changing the primary data present in the database.Q9) In SQL, what do you know about the composite primary key?Ans: The key which is created on multiple columns in a table is generally considered as the Composite primary key. However, it is not always necessary that all of them have the same meaning.Q 10 ) Difference Between Stored Procedure & Functions?Ans. SQL Vs MySQLSQLMYSQLSQL stands for Structured Query LanguageMySQL is an RDMS (Relational Database Management System)Allow for accessing and manipulating DB'sMySQL is a database management system, like SQL Server, Oracle, Postgres, Informix, etcBasically works as the prompter to a DBMSIt Facilitates multi-user access to a huge number of DBsSQL codes & commands are used in various DBMS and RDBMS systems such as MySQL.MySQL has SQL at its core and requires future upgrades mostlyQ34 ) What is SQL Server?Ans: SQL Server is Microsoft's relational database management system (RDBMS). End-user cannot interact directly with the database server. If we want to interact with SQL database server then we have to interact with SQL.Q35) What are the different types of SQL’s statements?Ans: 1. DQL - Data Query Language ( or) Data Retrieval Language.
SELECT Statement2. DML – Data Manipulation LanguageDML is used for manipulation of the data itself. INSERT Statement. UPDATE Statement.
DELETE Statement3. DDL – Data Definition LanguageDDL is used to define the structure that holds the data. CREATE Statement. ALTER Statement.
DROP Statement. RENAME Statement. TRUNCATE Statement4. DCL – Data Control LanguageDCL is used to control the visibility of data. GRANT Statement. REVOKE Statement5. TCL - Transaction Control Language.
COMMIT Statement. ROLLBACK Statement. SAVEPOINT StatementQ36) What are various DDL commands in SQL? Give a brief description of their purposes. Ans: DDL Commands are used to define the structure of the table1. CREATEIt is used to create database objects like tables, views, synonyms, indexes.Creating Table:Syntax- Create table tablename(columname1 datatype(size), columname2 datatype(size).);2. SQL Delete Vs SQL TruncateDELETETRUNCATEIt is DML CommandIt is DDL CommandIt is used to delete all the records row by rowIt is used to delete all the records at a timeBy using delete command we can delete a specific recordBy using truncate we cannot delete specific recordWhere condition we can use with the delete commandWhere condition will not work with truncateDelete will work slow compare with truncateTruncate will work fast compare with deleteDelete will not reset auto-generate id.
SQL Scalar Valued Functions Vs SQL Table-Valued FunctionsScalar Valued FunctionsTable-Valued FunctionsIt will process on a single row at a time & return only one value of any databaseIt will process on multiple rows at a time & return multiple rows (or) single row from the tableThe return type of scalar-valued function is a datatypeThe return type of table-valued function is a tableThe scalar-valued function will have as begin block endThe table-valued function will not have as begin endSyntax to call Scalar Valued Functions is:: SELECT dbo. Funname(values);Syntax to call Table-Valued Functions is:: SELECT. FROM dbo.funname(values);Q 44 ) How can you say that Database testing is different from that of GUI testing?Ans:.
GUI testing is always performed at the front end whereas the Database testing is performed at the back end. When it comes to dealing with the testable items, generally the users prefer GUI testing. These items are present clearly./ On the other hand, the Database testing deals with the testable items that are hidden and are not directly visible to the users.
Structured Query Language largely matters in Database approach where the same doesn’t have any application with the GUI. Invalidating the test boxes are a part of GUI database whereas the Database testing is totally different in this mannerQ45) Write a Query to view the indexes that are applied on the table?Ans: stored procedurehelpindex tablenameQ46) Difference Between Long & Lob Datatypes?Ans. Examples2:: SELECT Coalesce(‘a’, sysdate) FROM dual;Error: inconsistent datatypes: expected CHAR got DATEQ48) What is Tuple?Ans: Tuples are the members of a relation. An entity type having attributes can be represented by a set of these attributes called tuple.Q49) What is Query & Query Language?Ans:. A query is a statement requesting the retrieval of information. The portion of dimly that involves information retrieval is called a query language.Q50) Difference Between Views & Materialized Views?Ans.
SQL REVOKE,ONFROM;REVOKE Command is Used When We Want One Database To Stop Sharing The Information With Other Users.Revoke Privileges is Assigned Not Only On TABLE Object, But Also on VIEWS, SYNONYMS, INDEXES Etc. SQL REVOKE INSERT, DELETEON EMPFROM Operators;Q63) Connecting to Oracle OR SQL. Plus?Ans: Double Click the SQL.Plus ShortCut on the Desktop.
Start - Run - Type SQLPlus OR SQLPlusW in Open Box and Click OK. Create or replace procedure procedurename(formal parameters)Authid currentuseris/as-Begin-exeception-Endprocedurename;Q78) What is Row-Level-Attribute?Ans: In this method, a single variable can represent all different datatype into a single unit.
This variable is also called a record type variable.Ro Level Attribute is represented by using%rowtype.Syntax: variablename tablename%rowtype;Q79) What are the types of Blocks in PL/SQL?Ans: PL/SQL has 2 types of blocks. Anonymous Block.
Named Block. Sqldeclarecursor c1 is select.from empn number(10):=0;beginfor i in c1loopn:=n+i.sal;end loop;dbmsoutput.putline (‘total salary is: ‘ ’ ‘ n);end;/?Output: total salary is: 42075Q81) What is Normalization?Ans: Normalization is a scientific process which is called to decomposing a table into a number of tables.
RDBMS is one of the most commonly used databases till date, and therefore are indispensable in most of the job roles. In this SQL Interview Questions blog, I will introduce you to the most frequently asked questions on SQL (Structured Query Language). This blog is the perfect guide for you to learn all the concepts related to SQL, Oracle, MS SQL Server and MySQL database.Our SQL Interview Questions blog is the one-stop resource from where you can boost your interview preparation. It has a set of top 65 questions which an interviewer plans to ask during an interview process. It starts with the basic SQL interview questions and later continues to advanced questions based on your discussions and answers. These SQL Interview questions will help you with different expertise levels to reap the maximum benefit from this blog. Edureka 2019 Tech Career Guide is out!
Hottest job roles, precise learning paths, industry outlook & more in the guide. now.Let’s get started! SQL Interview Questions Q1. What is the difference between DELETE and TRUNCATE statements? DELETE vs TRUNCATE DELETETRUNCATEDelete command is used to delete a row in a table.Truncate is used to delete all the rows from a table.You can rollback data after using delete statement.You cannot rollback data.It is a DML command.It is a DDL command.It is slower than truncate statement.It is faster.Q2.
What are the different subsets of SQL?. DDL (Data Definition Language) – It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects. DML ( Data Manipulation Language) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database. DCL ( Data Control Language) – It allows you to control access to the database. Example – Grant, Revoke access permissions.Q3.
Sql Interview Questions For Testers
What do you mean by DBMS? What are its different types?A database is a structured collection of data.A Database Management System ( DBMS) is a software application that interacts with the user, applications and the database itself to capture and analyze data.A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images etc.There are two types of DBMS:. Relational Database Management System: The data is stored in relations (tables).
Example – MySQL. Non-Relational Database Management System: There is no concept of relations, tuples and attributes.
Sql Queries For Practice With Answers Free Download
Example – MongoQ4. What do you mean by table and field in SQL?A table refers to a collection of data in an organised manner in form of rows and columns. A field refers to the number of columns in a table.
For example:Table: StudentInformationField: Stu Id, Stu Name, Stu Marks Q5. What are joins in SQL?A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:. Inner Join. Right Join. Left Join.
Full JoinQ6. What is the difference between CHAR and VARCHAR2 datatype in SQL?Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10 characters and will not be able to store a string of any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.
What is a Primary key?. A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table. Null values not allowedExample- In the Student table, StuID is the primary key. What are Constraints?Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:. NOT NULL. CHECK.
DEFAULT. UNIQUE. PRIMARY KEY.
FOREIGN KEYQ9. What is the difference between SQL and MySQL?SQL is a standard language which stands for Structured Query Language based on the English language whereas MySQL is a database management system. SQL is the core of relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix etc. What is a Unique key?. Uniquely identifies a single row in the table.
Multiple values allowed per table. Null values allowed.Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more. What is a Foreign key?. Foreign key maintains referential integrity by enforcing a link between the data in two tables. The foreign key in the child table references the primary key in the parent table. The foreign key constraint prevents actions that would destroy links between the child and parent tables.Q12.
What do you mean by data integrity?Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database. SELECT.
FROM Tablename WHERE EmpName like 'A%'Q32. Write a SQL query to get the third highest salary of an employee from employeetable?SELECT TOP 1 salaryFROM(SELECT TOP 3 salaryFROM employeetableORDER BY salary DESC) AS empORDER BY salary ASC;Q33. What is the need for group functions in SQL?Group functions work on the set of rows and returns one result per group. Some of the commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.
What is a Relationship and what are they?Relation or links are between entities that have something to do with each other. Relationships are defined as the connection between the tables in a database. There are various relationships, namely:. One to One Relationship. One to Many Relationship. Many to One Relationship.
Self-Referencing Relationship.Q35. How can you insert NULL values in a column while inserting the data?NULL values can be inserted in the following ways:. Implicitly by omitting column from column list. Explicitly by specifying NULL keyword in the VALUES clauseQ36. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition operator is used to check for values contained in a specific set of values.
Example of BETWEEN: SELECT. FROM Students where ROLLNO BETWEEN 10 AND 50. Why are SQL functions used?SQL functions are used for the following purposes:. To perform some calculations on the data. To modify individual data items. To manipulate the output. To format dates and numbers.
To convert the data typesQ38. What is the need of MERGE statement?This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
What do you mean by recursive stored procedure?Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.
What is CLAUSE in SQL?SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.For example – WHERE, HAVING clause.Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.
What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query. List the ways in which Dynamic SQL can be executed?Following are the ways in which dynamic SQL can be executed:. Write a query with parameters. Using EXEC. Using spexecutesql.Q43.
What are the various levels of constraints?Constraints are the representation of a column to enforce data entity and consistency. There are two levels of a constraint, namely:. column level constraint. table level constraintQ44. How can you fetch common records from two tables?You can fetch common records from two tables using INTERSECT.
For example: 1Select studentID from student. INTERSECT Select StudentID from ExamQ45. List some case manipulation functions in SQL?There are three case manipulation functions in SQL, namely:. LOWER: This function returns the string in lowercase. It takes a string as an argument and returns it by converting it into lower case. Syntax:LOWER(‘string’). UPPER: This function returns the string in uppercase.
It takes a string as an argument and returns it by converting it into uppercase. Syntax:UPPER(‘string’).
INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase. Syntax:INITCAP(‘string’)Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more. What are the different set operators available in SQL?Some of the available set operators are – Union, Intersect or Minus operators. What is an ALIAS command?ALIAS name can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.For example- Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empIDIn the above example, emp refers to alias name for employee table and dept refers to alias name for department table. What are aggregate and scalar functions?Aggregate functions are used to evaluate mathematical calculation and returns a single value.
These calculations are done from the columns in a table. For example- max,count are calculated with respect to numeric.Scalar functions return a single value based on the input value. For example – UCASE, NOW are calculated with respect to string. How can you fetch alternate records from a table?You can fetch alternate records i.e both odd and even row numbers. For example- To display even numbers, use the following command:Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0Now, to display odd numbers: Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1Q50. Name the operator which is used in the query for pattern matching?LIKE operator is used for pattern matching, and it can be used as.% – It matches zero or more characters.For example- select. from students where studentname like ‘a%’ (Underscore) – it matches exactly one character.For example- select.
from student where studentname like ‘abc’Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.Q51. How can you select unique records from a table?You can select unique records from a table by using the DISTINCT keyword.
Select DISTINCT studentID from StudentUsing this command, it will print unique student id from the table Student. How can you fetch first 5 characters of the string?There are a lot of ways to fetch characters from a string.
For example:Select SUBSTRING(StudentName,1,5) as studentname from student Q53. What is the main difference between SQL and PL/SQL?SQL is a query language that allows you to issue a single query or execute a single insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes. What is a View?A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship. What are Views used for?A view refers to a logical snapshot based on a table or another view.
It is used for the following reasons:. Restricting access to data. Making complex queries simple. Ensuring data independence. Providing different views of same data.Q56. What is a Stored Procedure?A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.
List some advantages and disadvantages of Stored Procedure? Advantages:A Stored Procedure can be used as a modular programming which means create once, store and call for several times whenever it is required. This supports faster execution. It also reduces network traffic and provides better security to the data. Disadvantage:The only disadvantage of Stored Procedure is that it can be executed only in the database and utilizes more memory in the database server. List all the types of user-defined functions?There are three types of user-defined functions, namely:.
Scalar Functions. Inline Table-valued functions. Multi-statement valued functionsScalar returns the unit, variant defined the return clause. Other two types of defined functions return table.
What do you mean by Collation?Collation is defined as a set of rules that determine how data can be sorted as well as compared. Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc. What are the different types of Collation Sensitivity?Following are the different types of collation sensitivity:. Case Sensitivity: A and a and B and b. Kana Sensitivity: Japanese Kana characters. Width Sensitivity: Single byte character and double-byte character.
Accent Sensitivity.Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.Q61. What are Local and Global variables? Local variables:These variables can be used or exist only inside the function.
Pdf Sql Query Interview Questions And Answers For Management Positions
These variables are not used or referred by any other function. Global variables:These variables are the variables which can be accessed throughout the program. Global variables cannot be created whenever that function is called. What is Auto Increment in SQL?Auto increment keyword allows the user to create a unique number to get generated whenever a new record is inserted into the table.This keyword is usually required whenever PRIMARY KEY is used.AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER. What is a Datawarehouse?Datawarehouse refers to a central repository of data where the data is assembled from multiple sources of information.
Those data are consolidated, transformed and made available for the mining as well as online processing. Warehouse data also have a subset of data called Data Marts.
What are the different authentication modes in SQL Server? How can it be changed?Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps to change authentication mode in SQL Server:. Click Start Programs Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Then select the server from the Tools menu.
Select SQL Server Configuration Properties, and choose the Security page.Q65. What are STUFF and REPLACE function? So this brings us to the end of the SQL interview questions blog. I hope this set of SQL Interview Questions will help you ace your job interview. All the best for your interview!Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.Check out this by Edureka, a trusted online learning company with a network o f more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database.
It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions etc. End of the training you will be able to create and administer your own MySQL Database and manage data.Got a question for us? Please mention it in the comments section of this “SQL Interview Questions ” blog and we will get back to you as soon as possible.