Blog

If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, The average salary for  SQL Server ranges from approximately $69,682 pa. So, You still have opportunity to move ahead in your career in SQL Server. Mindmajix offers Advanced SQL Server Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SQL Server Developer. Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo! SQL Server Interview Questions In what sequence SQL statement are processed? The clauses of the select are processed in the following sequence FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY clause TOP clause Can we write a distributed query and get some data which is located on other server and on Oracle Database ? SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group. If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, Views and Stored Procedures ? YES, SQL Server drops all related objects, which exists inside a table like, constraints, indexes, columns, defaults etc. BUT dropping a table will not drop Views and Stored Procedures as they exists outside the table. How would you determine the time zone under which a database was operating? Can we add identity column to decimal datatype? YES, SQL Server support this What is the Difference between LEFT JOIN with WHERE clause & LEFT JOIN with no WHERE clause ? OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically. What are the Multiple ways to execute a dynamic query ? EXEC sp_executesql, EXECUTE() What is the Difference between COALESCE() & ISNULL() ? ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter. COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter, How do you generate file output from SQL? While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution? SET NOCOUNT OFF By Mistake, Duplicate records exists in a table, how can we delete copy of a record ? ;with T as ( select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank from employee ) delete from T where rank > 1 WHAT OPERATOR PERFORMS PATTERN MATCHING? Pattern matching operator is LIKE and it has to used with two attributes 1. %  means matches zero or more characters and  2. _ ( underscore ) means matching exactly one character What’s the logical difference, if any, between the following SQL expressions? -- Statement 1 SELECT COUNT ( * ) FROM Employees -- Statement 2 SELECT SUM ( 1 ) FROM Employees They’re the same unless table Employee table is empty, in which case the first yields a one-column, one-row table containing a zero and the second yields a one-column, one-row table "containing a null." SQL Server Interview Questions And Answers Is it possible to update Views? If yes, How, If Not, Why? Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view). Could you please name different kinds of Joins available in SQL Server ? OUTER JOIN – LEFT, RIGHT, CROSS, FULL ; INNER JOIN How important do you consider cursors or while loops for a transactional database? would like to avoid cursor in OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations. What is a correlated sub query? When a sub query is tied to the outer query. Mostly used in self joins. What is faster, a correlated sub query or an inner join? Correlated sub query. You are supposed to work on SQL optimization and given a choice which one runs faster, a correlated sub query or an exists? Exists Can we call .DLL from SQL server? YES, We can call .Dll from SQL Server.  What are the pros and cons of putting a scalar function in a queries select list or in the where clause? Should be avoided if possible as Scalar functions in these places make the query slow down dramatically. What is the difference between truncate and drop statement? What is the difference between truncate and delete statement? What are user defined data types and when you should go for them? User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined data type calledFlight_num_type of varchar(8) and use it across all your tables. See sp_addtype, sp_droptype in books online. Can You Explain Integration Between SQL Server 2005 And Visual Studio 2005 ? This integration provide wider range of development with the help of CLR for database server because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net. The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors SQL Server Interview Questions And Answers For Experienced You are being assigned to create a complex View and you have completed that task and that view is ready to be get pushed to production server now. you are supposed to fill a deployment form before any change is pushed to production server. One of the Filed in that deployment form asked, “Expected Storage requirement”. What all factors you will consider to calculate storage requirement for that view ? Very tricky, View, doesn’t takes space in Database, Views are virtual tables. Storage is required to store Index, incase you are developing a indexed view. What is Index, cluster index and non cluster index ? Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows. Write down the general syntax for a SELECT statements covering all the options. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax). SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by__expression] [HAVING search_condition] [ORDER BY order__expression [ASC | DESC] ] What is a join and explain different types of joins? Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. For more information see pages from books online titled: "Join Fundamentals" and "Using Joins". What is OSQL utility ? OSQL is command line tool which is used execute query and display the result same a query analyzer but everything is in command prompt. What Is Difference Between OSQL And Query Analyzer ? OSQL is command line tool which executes query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL is quite useful for batch processing or executing remote queries. What Is Cascade delete / update  ? CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. SQL Server Interview Questions For 2-5 Years Experienced What are some of the join algorithms used when SQL Server joins tables. Loop Join (indexed keys unordered) Merge Join (indexed keys ordered) Hash Join (non-indexed keys)   What is maximum number of tables that can joins in a single query ? 256, check SQL Server Limits What is Magic Tables in SQL Server ? The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED These are mantained by SQL server for there Internal processing. When we use update insert or delete on tables these magic tables are used.These are not physical tables but are Internal tables.When ever we use insert statement is fired the Inserted table is populated with newly inserted Row and when ever delete statement is fired the Deleted table is populated with the delete d row.But in case of update statement is fired both Inserted and Deleted table used for records the Original row before updation get store in Deleted table and new row Updated get store in Inserted table. Can we disable a triger?, 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” Why you need indexing? where that is Stored and what you mean by schema object? For what purpose we are using view? We can’t create an Index on Index.. Index is stoed in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view. Indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It’s customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data. What the difference between UNION and UNIONALL? Union will remove the duplicate rows from the result set while Union all does’nt. Which system table contains information on constraints on all the tables created ? USER_CONSTRAINTS, system table contains information on constraints on all the tables created SQL Server Joins Interview Questions Frequently Asked SQL Server Interview Questions & Answers What are different Types of Join? Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price. Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer. Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included: Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear. Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included. Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not. Self Join This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. What is Data-Warehousing? Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time; Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting. Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent. 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 keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. How SQL Server executes a statement with nested subqueries? When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set. How do you add a column to an existing table? ALTER TABLE Department ADD (AGE, NUMBER); Can one drop a column from a table? YES, to delete a column in a table, use  ALTER TABLE table_name DROP COLUMN column_name Which statement do you use to eliminate padded spaces between the month and day values in a function TO_CHAR(SYSDATE,’Month, DD, YYYY’) ? To remove padded spaces, you use the "fm" prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,’fmMonth DD, YYYY’) Which operator do you use to return all of the rows from one query except rows are returned in a second query? You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries.  How will you create a column alias? The AS keyword is optional when specifying a column alias. In what sequence SQL statement are processed? The clauses of the subselect are processed in the following sequence (DB2): 1. FROM clause 2. WHERE clause 3. GROUP BY clause 4. HAVING clause 5. SELECT clause 6. ORDER BY clause 7. FETCH FIRST clause How can we determine what objects a user-defined function depends upon? sp_depends system stored procedure or query the sysdepends system table to return a list of objects that a user-defined function depends upon SELECT DISTINCT so1.name, so2.name FROM sysobjects so1 INNER JOIN sysdepends sd ON so1.id = sd.id INNER JOIN sysobjects so2 ON so2.id = sd.depid WHERE so1.name = '<>' What is lock escalation ? A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages  are locked, it may escalate to a table lock. What are the main differences between #temp tables and @table variables and which one is preferred ? SQL Server can create column statistics on #temp tables Indexes can be created on #temp tables @table variables are stored in memory up to a certain threshold. What are Checkpoint In SQL Server ? When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired. Related Page: SQL Interview Questions For 5+ Years Experienced  Why we use OPENXML clause? OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the DB. Can we store we store PDF files inside SQL Server table ? YES, we can store this sort of data using a blob datatype. Can we store Videos inside SQL Server table ? YES, we can store Videos inside SQL Server by using FILESTREAM datatype, which was introduced in SQL Server 2008. Can we hide the definition of a stored procedure from a user ? YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format. What are included columns when we talk about SQL Server indexing? Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that have the following benefits: Columns defined in the include statement, called non-key columns, are not counted in the number of columns by the Database Engine. Columns that previously could not be used in queries, like nvarchar(max), can be included as a non-key column. A maximum of 1023 additional columns can be used as non-key columns. What is an execution plan? How would you view the execution plan? An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since  the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query  drop-down menu). If this option is turned on it will display query execution plan in separate window  when query is ran again. Explain UNION, MINUS, UNION ALL, INTERSECT ? INTERSECT returns all distinct rows selected by both queries. MINUS – returns all distinct rows selected by the first query but not by the second. UNION – returns all distinct rows selected by either query UNION  ALL  -  returns  all  rows  selected  by  either query, including all duplicates Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!   SQL SERVER Query Interview Questions with Answers SQL Server DATEADD() Function Q) Write a Query to display the date after 15 days? SELECT DATEADD(dd, 15,getdate()) Q) Write a Query to display date after 12 months? SELECT DATEADD(mm, 2, getdate()) Q) Write a Query to display date before 15 days? SELECT DATEADD(dd, -15, getdate()) SQL Server DATEDIFF() Function Q) Write a Query to display employee details along with exp? SELECT * DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee Q) Write a Query to display employee details who is working in ECE department & who his having more than 3 years of exp? SELECT * DATEDIFF(yy, doj, getdate()) AS ‘Exp’  FROM employee  WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’ Q) Write a Query to display employee details along with age? SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee Q) Write a Query to display employee details whose age >18? SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee WHERE DATEDIFF(yy, dob, getdate())>18 SQL Server Multi Row Functions Q) Write a Query to display minimum salary of an employee? SELECT MIN (salary) FROM employee Q) Write a Query to display maximum salary of an employee? SELECT MAX(salary) FROM employee Q) Write a Query to display total salary of all employees? SELECT SUM(salary) FROM employee Q) Write a Query to display average salary of an employee? SELECT AVG(salary) FROM employee Q) Write a Query to count the number of employees working in the company? SELECT COUNT(*) FROM employee Q) Write a Query to display minimum & maximum salary of employee? SELECT MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee Q) Write a Query to count the number of employee working in ECE department? SELECT COUNT(*) FROM employee WHERE dept_name=’ECE’ Q) Write a Query to display second max salary of an employee? SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM emp) Q) Write a Query to display third max salary of an employe? SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM emp where salary < (SELECT MAX(salary) FROM emp)) SQL SERVER: GROUP BY Clause Q) Write a Query to display total salary of employee based on city? SELECT city, SUM(salary)  FROM employee  GROUP BY city; Q) Write a Query to display number of employee based on city? SELECT city, COUNT(emp_no)  FROM employee GROUP BY city; (OR) SELECT city, COUNT(emp_no) AS ‘no.of employees’  FROM employee  GROUP BY city; Q) Write a Query to display total salary of employee based on region? SELECT region, SUM(salary) AS ‘total_salary’  FROM employee GROUP BY region; Q) Write a Query to display the number of employees working in each region? SELECT region, COUNT(gender)  FROM employee GROUP BY region; (OR)  SELECT region, COUNT(gender) AS ‘no.of males’ FROM employee GROUP BY region; Q) Write a Query to display minimum salary & maximum salary based on dept_name? SELECT dept_name, MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’  FROM employee GROUP BY dept_name Q) Write a Query to display the total salary of employee based on dept_name? SELECT dept_name, SUM(salary) AS ‘total_sal’  FROM employee GROUP BY dept_name Q) Write a Query to display no.of males in each department? SELECT dept_name, COUNT(gender)  FROM employee GROUP BY dept_name  WHERE gender=’male’ (OR) SELECT dept_name, COUNT(gender) AS ‘no.of males’  FROM employee WHERE gender=’male’  GROUP BY dept_name; Note:: We cannot apply where condition in GROUP BY CLAUSE if we want apply use having clause. We have to use WHERE condition before GROUP BY but cannot apply where condition after GROUP BY. SQL SERVER: Having Clause Q) Write a Query to display total salary of employee based on whose total salary > 12000? SELECT city, SUM(salary) AS ‘total_salary’  FROM employee GROUP BY city HAVING SUM(salary)>12000; Q) Write a Query to display the total salary of all employees based on city whose average salary >= 23000? SELECT city, SUM(salary) AS ‘total_salary’  FROM employee GROUP BY city HAVING AVG(salary)  >= 23000; SQL SERVER: SUB QUERIES Q) Write a Query to display employee details whose employee numbers are 101, 102? SELECT * FROM employee WHERE Emp_No in (101, 102) (OR) SELECT * FROM employee WHERE Emp_No in (select emp_no from emp) Q) Write a Query to display employee details belongs to ECE department? SELECT Emp_No, Emp_Name, Salary FROM employee WHERE dept_no in (select dept_no from dept where dept_name = ‘ECE’) SQL SERVER TOP Clause Q) Write a Query to display first record from the table? SELECT TOP 1 *  FROM employee Q) Write a Query to display top 3 records from the table? SELECT TOP 3 * FROM employee Q) Write a Query to display last record from the table? SELECT TOP 1 * FROM employee  ORDER BY emp_no descending SQL SERVER: Ranking Functions Student Details Table: Student_No Student_Name Percentage Row_ID Rank_ID DenseRank_ID 105 James 87 1 1 1 106 John 83 2 2 2 101 Anil 83 3 2 2 104 Vijay 83 4 2 2 108 Rakesh 76 5 5 3 102 Sunil 76 6 5 3 103 Ajay 76 7 5 3 107 Ram 75 8 8 4 Q) Write a Query to display student details along with the row_no order by student name? SELECT *, ROW_NUMBER() OVER (ORDER BYstudent_name) AS ‘Row_ID’ FROM employee Q) Write a Query to display even records from the table? SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS ‘ Row_ID’ FROM student) WHERE row_id %2=0 Q) Write a Query to display odd records from student table? SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS Row_ID FROM student) WHERE row_id %2!=0 List of Related Microsoft Certification Courses:  SSIS  Power BI  SSRS  SharePoint  SSAS  SQL Server DBA  SCCM  BizTalk Server  Team Foundation Server  BizTalk Server Administrator  

Last Updated: August 13th, 2018 If you're looking for SQL Server Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, the average salary for SQL Server ranges from approximately $69,674 pa. So, You still have the opportunity to move ahead in your career in SQL Server certification guide. Mindmajix offers Advanced SQL Server Interview Questions & Answers 2018 that helps you in cracking your interview & acquire dream career as SQL Server Developer. SQL Server Interview Questions & Answers Q1) What is the Difference between MySql Vs SQL Server Performance? MySQl Server Vs SQL Server Function MS SQL Server MySql Developer Microsoft Oracle License  Commercial OpenSource Cloud-based No No Implementation Language C++ C & C++ XML Support  Yes Yes Supported Programming Lang It supports C#, PHP, Python, Ruby, R, Visual Basic, Java etc It supports c++, C#, Java, PHP, Perl, Python, Ruby, Tcl, Delphi, D etc Server-Side Scripting. Net & TransaScripting. Yes, it supports Concurreny It supports ,yes It supports ,yes Consistency Concept's It enables immediate consistency It enables immediate consistency Transaction concepts It supports ACID It supports ACID Q2) What is normalization? Explain different levels of normalization? It is the way to eliminate redundant data Reduces null value Enables efficient indexing 1NF – Removes duplicated attributes, Attribute data should be atomic, and attribute should be same kind. 2NF – Should be in 1NF and each non-key is fully dependent on the primary key. 3NF – Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed. Normalization is done in OLTP. Q3) What is denormalization and when would you go for it? It is the reverse process of normalization. It increases the query performance by reducing the joins. It is used for OLAP applications. Q4) How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? One to One –It can be implemented as a single table. Rarely it is implemented in two tables. For each instance in the first entity there is one and only one in the second entity and vice versa. One to Many –For each instance in the first entity there can be one or more in the second entity. For each instance in the second entity there can be one and only one instance in the first entity. Many to Many –For each instance in the first entity there can be one or more instance in the second entity and moreover, for each instance in the second entity there can be one or more instance in the first entity. Q5) Difference between Primary key and Unique key. Primary Key 1.Enforces uniqueness of the column in a table 2.Default clustered index 3.Does not Allow nulls Unique Key   1.Enforces uniqueness of the column in a table 2.Default non-clustered index. 3.Allows one null value Q6) Define following keys: Candidate key, Alternate key, Composite key. 1.Candidate key –Key which can uniquely identify a row in table. 2.Alternate key –If the table has more than one candidate keys and when one becomes a primary key the rest becomes alternate keys. 3.Composite key –More than one key uniquely identify a row in a table. Q7) What are defaults? Is there a column to which a default can’t be bound? 1.It is a value that will be used by a column if no value is supplied to that column while inserting data. 2.I can’t be assigned for identity and timestamp values. Q8) What are user defined data types and when you should go for them? Lets you extend the base SQL server data types by providing a descriptive name and format to the database. E.g. Flight_num appears in many tables and all these tables have varchar(8) Create a user defined data-type Q9) What is a transaction and what are ACID properties? A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the properties of a transaction. Q10) What part does database design have to play in the performance of a SQL Server-based application? It plays a very major part. When building a new system, or adding to an existing system, it is crucial that the design is correct. Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed. It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient.. Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SQL Server Training Demo! Q11) What can a developer do during the logical and physical design of a database in order to help ensure that their database and SQL Server-based application will perform well? A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design. A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler. Q12) What are the main steps in Data Modeling? 1.Logical – Planning, Analysis and Design 2.Physical – Design, Implementation and Maintenance DATABASE DEVELOPMENT / PROGRAMMING Q13) What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? Cursors allow row-by-row processing of the result sets. Types of cursors: Static – Makes a temporary copy of the data and stores in tempdb and any modifications on the base table does not reflected in data returned by fetches made by the cursor. Dynamic – Reflects all changes in the base table. Forward-only – specifies that cursor can only fetch sequentially from first to last. Keyset-driven – Keyset is the set of keys that uniquely identifies a row is built in a tempdb. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, whereas a normal SELECT query makes only one roundtrip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times set-based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 — 5000 hike Salary between 40000 and 55000 — 7000 hike Salary between 55000 and 65000 — 9000 hike In this situation, many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below: UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. Q14) Write down the general syntax for a SELECT statement covering all the options. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax). SELECT select_list [HDEV:INTO new_table_] FROM table_source [HDEV:WHERE search_condition] [HDEV:GROUP BY group_by_expression] [HDEV:HAVING search_condition] [ORDER BY order_expression [ASC | HDEV:DESC] ] Q15) What is a Join and explain different types of Joins? Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. Q16) Can you have a nested transaction? Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT Q17) What is an extended stored procedure? Can you instantiate a COM object by using T-SQL? An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. Q18) What is the system function to get the current user’s userid? USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME(). Q19) What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand? Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder. Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster. Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also – INSTEAD OF triggers. Virtual tables – Inserted and Deleted form the basis of trigger architecture. Q20) What is a self join? Explain it with an example. Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join. CREATE TABLE emp ( empid int, mgrid int, empname char(10) ) INSERT emp SELECT 1,2,’Vyas’ INSERT emp SELECT 2,3,’Mohan’ INSERT emp SELECT 3,NULL,’Shobha’ INSERT emp SELECT 4,2,’Shridhar’ INSERT emp SELECT 5,2,’Sourabh’ SELECT t1.empname [HDEV:Employee], t2.empname [HDEV:Manager] FROM emp t1, emp t2 WHERE t1.mgrid = t2.empid Here’s an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses) SELECT t1.empname [HDEV:Employee], COALESCE(t2.empname, ‘No manager’) [HDEV:Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now! Q21) Write a SQL Query to find first Week Day of month? SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay Q22) How to find 6th highest salary from Employee table? SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary Q23) How can I enforce to use particular index? You can use index hint (index=index_name) after the table name. SELECT au_lname FROM authors (index=aunmind) Q24) What is ORDER BY and how is it different than clustered index? The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes will physically sort data, while inserting/updating the table. Q25) What is the difference between a UNION and a JOIN? A JOIN selects columns from 2 or more tables. A UNION selects rows. Q26) What is the Referential Integrity? Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value Q27) What is the purpose of UPDATE STATISTICS? It updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view. Q28) What is the use of SCOPE_IDENTITY() function? It returns the most recently created identity value for the tables in the current execution scope. Q29) What do you consider are the best reasons to use stored procedures in your application instead of passing Transact-SQL code directly to SQL Server? First and foremost, a stored procedure is a compiled set of code, where passing T-SQL through languages such as VB, Visual FoxPro, etc., means that the set of code needs to be compiled first. Although T-SQL within VB, etc., can be prepared before running, this is still slower than using a stored procedure. Then, of course, there is the security aspect, where, by building a stored procedure, you can place a great deal of security around it. When dealing with sensitive data, you can use an encrypted stored procedure to hide sensitive columns, calculations, and so on. Finally, by using a stored procedure, I feel that transactional processing becomes a great deal easier and, in fact, using nested transactions become more insular and secure. Having to deal with transactions within code that may have front end code, will slow up a transaction and therefore a lock will be held for longer than necessary. Q30) What are some techniques for writing fast performing stored procedures? Fast performing stored procedures are like several other areas within T-SQL. Revisiting stored procedures every six months or so, to ensure that they are still running at their optimum performance is essential. However, actual techniques themselves include working with as short a transaction area as possible, as lock contention will certainly impact performance. Recompiling your stored procedures after index additions if you are unable or not wishing to restart SQL Server, will also ensure that a procedure is using the correct index, if that stored procedure is accessing the table which has received the new index. If you have a T-SQL command that joins several tables, and it takes a long time to return a value, first of all check out the indexes. But what you may find tends to help, is to break down the code and try to determine which join it is that is causing the performance problem. Then analyze this specific join and see why it is a problem. Always check out a stored procedure’s performance as you build it up by using the SHOWPLAN commands. Also, try to use EXISTS, rather than a JOIN statement. An EXISTS statement will only join on a table until one record is found, rather than joining all the records . Also, try to look at using sub queries when you are trying to find a handful of values in the sub query statement, and there is no key on the column you are looking up on. Q31) When should SQL Server-based cursors be used, and not be used? SQL Server cursors are perfect when you want to work one record at a time, rather than taking all the data from a table as a single bulk. However, they should be used with care as they can affect performance, especially when the volume of data increases. From a beginner’s viewpoint, I really do feel that cursors should be avoided every time because if they are badly written, or deal with too much data, they really will impact a system’s performance. There will be times when it is not possible to avoid cursors, and I doubt if many systems exist without them. If you do find you need to use them, try to reduce the number of records to process by using a temporary table first, and then building the cursor from this. The lower the number of records to process, the faster the cursor will finish. Always try to think “out of the envelope”. Q32) What alternatives do developers have over using SQL Server-based cursors? In other words, how can developers perform the same function as a cursor without using a cursor? Perhaps one of the performance gains least utilized by developers starting out in SQL Server are temporary tables. For example, using one or more temporary tables to break down a problem in to several areas could allow blocks of data to be processed in their own individual way, and then at the end of the process, the information within the temporary tables merged and applied to the underlying data. The main area of your focus should be, is there an alternative way of doing things? Even if I have to break this down into several chunks of work, can I do this work without using cursors, and so result in faster performance. Another area that you can look at is the use of CASE statements within your query. By using a CASE statement, you can check the value within a column and make decisions and operations based on what you have found. Although you will still be working on a whole set of data, rather than a subset found in a cursor, you can use CASE to leave values, or records as they are, if they do not meet the right criteria. Care should be taken here though, to make sure that by looking at all the data, you will not be creating a large performance impact. Again, look at using a subset of the data by building a temporary table first, and then merging the results in afterwards. However, don’t get caught out with these recommendations and do any of them in every case. Cursors can be faster if you are dealing with small amounts of data. However, what I have found, to be rule number one, is get as little data in to your cursor as is needed. Q33) If you have no choice but to use a SQL Server-based cursor, what tips do you have in order to optimize them? Perhaps the best performance gain is when you can create a cursor asynchronously rather than needing the whole population operation to be completed before further processing can continue. Then, by checking specific global variables settings, you can tell when there is no further processing to take place. However, even here, care has to be taken. Asynchronous population should only occur on large record sets rather than those that only deal with a small number of rows. Use the smallest set of data possible. Break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit. If you are using the same cursor more than once in a batch of work, and this could mean within more than one stored procedure, then define the cursor as a global cursor by using the GLOBAL keyword, and not closing or deallocating the cursor until the whole process is finished. A fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use. DATABASE PERFORMANCE OPTIMIZATION / TUNING Q34) What are the steps you will take to improve performance of a poor performing query? This is a very open ended question and there could be lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes No Table scans Missing or out of date statistics Blocking Excess recompilations of stored procedures Q35) What is an ER Diagram? An ER diagram or Entity-Relationship diagram is a special picture used to represent the requirements and assumptions in a system from a top down perspective. It shows the relations between entities (tables) in a database. Q36) What is a prime attribute? A prime attribute is an attribute that is part of a candidate key. Q37) What are the properties of a transaction? The ACID properties. Atomicity, Consistency, Isolation, and Durability. Q38) What is a non-prime attribute? A non-prime attribute is an attribute that is not a part of a candidate key. Q39) What is Atomicity? This means the transaction finish completely, or it will not occur at all. Q40) What is Consistency? Consistency means that the transaction will repeat in a predictable way each time it is performed. Q41) What is Isolation? The data the transactions are independent of each other. The success of one transaction doesn’t depend on the success of another. Q42) What is Durability? Guarantees that the database will keep track of pending changes so that the server will be able to recover if an error occurs. Q43) What is a DBMS? A DBMS is a set of software programs used to manage and interact with databases. Q44) What is a RDBMS? It is a set of software programs used to interact with and manage relational databases. Relational databases are databases that contain tables. Q45) What is business intelligence? Refers to computer-based techniques used in identifying, extracting, and analyzing business data, such as sales revenue by products and/or departments, or by associated costs and incomes. Q46) What is normalization? Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Q47) What is a relationship? The way in which two or more concepts/entities are connected, or the state of being connected. Q48) What are the different types of relationships? One to one, one to many, many to many, many to fixed cardinality. Q49) What is the difference between a OLTP and database? An OLTP is the process of gathering the data from the users, and a database is the initial information. Q50) What are the different kinds of relationships? Identifying and non-identifying. Q51) What is an entity? Something that exists by itself, although it need not be of material existence. Q52) What is a conjunction table? A table that is composed of foreign keys that points to other tables. Q53) What is a relational attribute? An attribute that would not exist if it were not for the existence of a relation. Q54) What are associative entities? An associative entity is a conceptual concept. An associative entity can be thought of as both an entity and a relationship since it encapsulates properties from both. It is a relationship since it is serving to join two or more entities together, but it is also an entity since it may have its own properties. Q55) What is the difference between a derived attribute, derivedpersistent attribute, and computed column? A derived attribute is a attribute that is obtained from the values of other existing columns and does not exist on it’s own. A derived persistent attribute is a derived attribute that is stored. A computed attribute is a attribute that is computed from internal system values. Q56) What are the types of attributes? Simple, composite (split into columns), multi-valued (becomes a separate table), derived, computed, derived persistent. Q57) Is the relationship between a strong and weak entity always identifying? Yes, this is the requirement. Q58) Do stand alone tables have cardinality? No. Q59) What is a simple key? It is a key that in composed of one attribute. Give/ recite the types of UDF functions. Scalar, In-line, Multi Q60) Describe what you know about PK, FK, and UK. Primary keys – Unique clustered index by default, doesn’t accept null values, only one primary key per table. Foreign Key – References a primary key column. Can have null values. Enforces referential integrity. Unique key – Can have more than one per table. Can have null values. Cannot have repeating values. Maximum of 999 clustered indexes per table. Q61) What do you mean by CTEs? How will you use it? CTEs also known as common table expressions are used to create a temporary table that will only exist for the duration of a query. They are used to create a temporary table whose content you can reference in order to simplify a queries structure. Q62) What is a sparse column? It is a column that is optimized for holding null values. Q63) What would the command: DENY CREATE TABLE TO Peter do? It wouldn’t allow the user Peter to perform the operation CREATE TABLE regardless of his role. Q64) What does the command: GRANT SELECT ON project TO Peter do? It will allow the SELECT operation on the table ‘project’ by Peter. Q65) What does the command: REVOKE GRANT SELECT ON project TO Peter do? It will revoke the permission granted on that table to Peter. Q66) New commands in SQL 2008? Database encryption, CDCs tables – For on the fly auditing of tables, Merge operation, INSERT INTO – To bulk insert into a table from another table, Hierarchy attributes, Filter indexes, C like operations for numbers, resource management, Intellisense – For making programming easier in SSMS, Execution Plan Freezing – To freeze in place how a query is executed. What is new in SQL 2008 R2? PowerPivot, maps, sparklines, data bars, and indicators to depict data. Q67) What is faster? A table variable or temporary table? A table variable is faster in most cases since it is held in memory while a temporary table is stored on disk. However, when the table variable’s size exceeds memory size the two table types tend to perform similarly. Q68) How big is a tinyint, smallint, int, and bigint? 1 byte, 2 bytes, 4 bytes, and 8 bytes. Q69) What does @@trancount do? It will give you the number of active transactions for the current user. Q70) What are the drawbacks of CTEs? It is query bound. Q71) What is the transaction log? It keeps a record of all activities that occur during a transaction and is used to roll back changes. Q72) What are before images, after images, undo activities and redo activities in relation to transactions? Before images refers to the changes that are rolled back on if a transaction is rolled back. After images are used to roll forward and enforce a transaction. Using the before images is called the undo activity. Using after images is called the redo activity. Q73) What are shared, exclusive and update locks? A shared lock, locks a row so that it can only be read. An exclusive lock locks a row so that only one operation can be performed on it at a time. An update lock basically has the ability to convert a shared lock into an exclusive lock. Q74) What does WITH TIES do? If you use TOP 3 WITH TIES *, it will return the rows, that have a similarity in each of their columns with any of the column values from the returned result set. Q75) How can you get a deadlock in SQL? By concurrently running the same resources that access the same information in a transaction. Q76) What is LOCK_TIMEOUT used for? It is used for determining the amount of time that the system will wait for a lock to be released. Q77) What is the ANY predicate used for? SELECT * FROM emp_table WHERE enter_date > ANY (SELECT enter_date FROM works_on) Q78) What is the ALL predicate used for? SELECT * FROM emp_table WHERE enter_date > ALL (SELECT enter_date FROM works_on) Q79) What are some control flow statements in SQL? while, if, case, for each etc.. Q80) What is the EXISTS function used for? It is used to determine whether a query returns one or more rows. If it does, the EXIST function returns TRUE, otherwise, it will return FALSE. SQL Query Interview Questions with Answers Inner Join: It is used to retrieve matching records from both the tables Department: Department_No Department_Name 10 ECE 20 ECE 30 CSE 40 IT Employee Details: Employee_No Emp_Name Address Age Department_No Salary 1 Anil Hyderabad 23 10 20000 2 Sunil Hyderabad 22 10 21000 3 Ajay Chennai 24 20 23000 4 Vijay Chennai 25 30 22000 5 James Hyderabad 24 50 230000 Q1) Write a Query to display employee details who are working in ECE department? SELECT employee.employee_name, employee.address, employee.salary, employee.age,  FROM Department D INNER JOIN Employees E ON department.D_no=employee.D_no WHERE department.D_name= ‘ECE’ Q2) Write a Query to display employee details? SELECT * FROM employee; Q3)  Write a Query to display employee details along with department_name? SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age, department.department_name  FROM department D INNER JOIN employee E ON department.D_no=employee.D_no Q4) Write a Query to display employee details whose sal>20000 and who is working in ECE department? SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age  FROM department D INNER JOIN employee E   ON dept.D_no=emp.D_no  WHERE dept.D_name=’ECE’ and E.salary>20000 5) Write a Query to display employee details along with department_name and who is working in ECE department, whose name starts with a? SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.dname  FROM department D INNER JOIN employee E ON dept.D_no=emp.D_no  WHERE dept.D_name=’ECE’ and emp.E_name like ‘a%’ Q6) Write a Query to display employee details along with department_name and whose age between 20 and 24? SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name  FROM department D INNER JOIN employee E  ON dept.D_no=emp.D_no  WHERE E.age between 20 and 24 Q7) Write a Query to display employee details along with department_name and who are staying in hyderabad? SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name  FROM department D INNER JOIN employee E  ON dept.D_no=emp.D_no  WHERE E.address=’hyd’ Q8)  Write a Query to display employee details whose salary>20000 and whose age>20 & who is working in ECE department? SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name  FROM department D INNER JOIN employee E ON dept.D_no=emp.D_no  WHERE E.age>20 and E.salary>20000 and dept.D_name=’ECE’ State Table: State ID State Name S1 Telangana S2 AP S3 Tamil Nadu S4 Karnataka S5 Kerala City City ID City Name State ID 1 Hyderabad S1 2 Vizag S2 3 Vijayawada S2 4 Chennai S3 5 Madhurai S3 6 Bangalore S4 Blood Group Details Blood Group ID Blood Group B1 A+ve B2 B+ve B3 AB +ve B4 A -ve B5 O +ve Donor Details Donor ID Donor Name Phone Number  City ID Blood Group ID D1 Anil 9999 1 B1 D2 Sunil 8888 1 B1 D3 Ajay 7777 2 B1 D4 John 6666 4 B3 D5 James 5555 4 B5 Q9) Write a Query to display city names belongs to AP? SELECT C.City_Name FROM State S INNER JOIN City C ON S.State_ID WHERE S.State_Name ‘AP’ Q10) Write a Query to display Donor_ID, Donor_Name, Phone No, City? SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name  FROM Donor D INNER JOIN City C ON D.City_ID=C.City_ID Q11) Write a Query to display Donor_ID, Donor_Name, Phone No, Blood Group? SELECT D.Donor_ID, D_Name, D_Phone No, B.Blood_Group FROM Donor D INNER JOIN Blood B ON D.Blood_ID=B.Blood_ID; Q12) Write a Query to display Donor_ID, Donor_Name, Phone No and who are staying in hyderabad? SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name  FROM Donor D INNER JOIN City C ON C.City_ID=D.City_ID WHERE C.City_Name=’hyderabad’ Q13) Write a Query to display donor details whose blood group is A +ve? SELECT D.Donor_ID, D_Name, D_Phone No FROM Donor D INNER JOIN Blood B ON D.Donor_ID=B.Blood_ID  WHERE B.Blood_Group=’A+ve’ Q14) Write a Query to display Donor_ID, Donor_Name, Phone No, City, Blood Group? SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name B.Blood_Group FROM Blood B INNER JOIN Donor D ON D.Blood_ID=B.Donor_Name INNER JOIN City C ON D.City_ID=C.City_ID Q15) Write a Query to display Donor_Name, Phone No, Blood Group of the donors who is staying in hyderabad and whose blood group is A+ve? SELECT D.Donor_Name, D. Phone_Number, B.Blood_Group FROM Donor D INNER JOIN Blood B ON D.Blood_ID=B.Blood_ID INNER JOIN City C ON D.City_ID=C.City_ID  WHERE C.City_Name=’hyderabad’ and B.Blood_Group=’A+ve’ Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join.. You can create three different outer join to specify the unmatched rows to be included: Left Outer Join Right Outer Join Full Outer Join Employee Details Table Employee_No Employee_Name Dept_No 101 Anil 10 102 Sunil 20 103 Ajay 30 104 Vijay 40 105 Null Null Department Details Table Dept_No Depat_Name 10 EEE 20 EEE 30 CSE Null Null 50 IT Q16) Write a Query to display only left records? SELECT e.* FROM Employee E LEFT OUTER JOIN Department D ON E.D_no WHERE D.D_No IS NULL Q17) Write a Query to display employee details where employee no is 101? SELECT * FROM Employee E WHERE E_No=101 Q18) Write a Query to display employee details where employee number is null? SELECT * FROM Employee E WHERE E_No IS NULL Q19) Write a Query to display only right records? SELECT D.* FROM Employee E RIGHT OUTER JOIN Department D ON E.D.No=D.D_No WHERE E.D_No IS NULL Q20) Write a Query to display all the records from the table except matching records? SELECT E.*, D.* FROM Employee E FULL JOIN Department D ON E.D_No=D.D_No  WHERE E.D_No IS NULL or D.D_No IS NULL Department Details Table Dept_No Dept_Name 1 ECE 2 CSE 3 EEE Course Details Table Course_ID Course_Name Cr 1 EDC 4 2 PDC 4 3 SS 4 4 DAA 4 5 OS 4 Student Details Table Student_No Student_Name 101 Anil 102 Sunil 103 Ajay 104 Vijay 105 John Enroll Details Table Enroll_Date Student_No Dpet_No S_ID 1/2/2014 101 10 S1 3/2/2016 102 10 S1 3/2/2016 103 10` S1 3/2/2016 104 20 S2 3/2/2016 105 20 S2 Address Table Emp_No Address E1 Hyderabad E2 Vizag E3 Hyderabad E4 Bangalore E5 Hyderabad Employee Details Table Emp_No Emp_Name E1 Arun E2 Kiran E3 Kumar E4 Anus E5 James Semester Details Table Semester Sn S1 1 S2 2-1 S3 2-2 S4 3-1 S5 3-2 S6 4-1 S7 4-2 Course Department Details Dept_No Course_ID 10 1 10 2 10 3 20 4 20 5 Syllabus Table Dept_No Course_ID S_ID 10 1 S1 10 2 S1 10 3 S1 20 4 S2 20 5 S2 Instructor Details Table Emp_No Dept_No E1 10 E2 10 E3 10 E4 20 E5 30 Course Instructor Table Course_ID Emp_No S_ID Dept_No 1 E1 S1 10 1 E1 S1 20 1 E2 S1 30 2 E3 S1 10 4 E4 S2 20 5 E4 S2 20 5 E5 S1 10 Q) Write a query to display Student No, Student Name, Enroll Date, Department Name? SELECT S.Student_No,   S.Student_Name, S.Enroll_Date, D.Dept_Name  FROM Student S INNER JOIN Enroll E ON S.Student_No=E.Student_No INNER JOIN Department D ON D.Dept_No=E.Dept_No Q) Write a query to display Employee Number, Employee Name and address, department name? SELECT E.Emp_No, E.Emp_Name, A.Address, D.Dept_Name FROM Employee E INNER JOIN Address A ON E.Emp_No=A.Emp_No INNER JOIN Instructor I ON A.Emp_No=I.Emp_No INNER JOIN Department D ON I.Dept_No=D.Dept_No Q) Write a query to display course name belongs to ECE department? SELECT C.Course_Name FROM Department D INNER JOIN Course Department CD ON D.Dept_NO=CD.Dept_NO INNER JOIN Course C ON CD.CourseDept_ID=C.Course_ID WHERE D.Dept_Name=’ECE’ Q) ) Write a query to display student number, student name, enroll date, dept name,  semester name? SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dpet_Name, Sem.Student_Name FROM Enroll E INNER JOIN Student S ON S.Student_No=E.Student_No INNER JOIN Deprtment D ON E.Dept_No=D.Dept_No  INNER JOIN Semester SE ON E.Student_ID=Sem.Student_ID Q) Write a query to display the syllabus of ECE department 1st year? SELECT C.Course_Name FROM Department D INNER JOIN Syllabus Sy ON D.Dept_No=Sy.Dept_No INNER JOIN Course C ON Sy.Course_ID=C.Course_ID INNER JOIN Semester Se ON Syllabus_Sy_ID=Se_Sy_ID WHERE D.Dept_Name=’ECE’ and Se.Semester=’1’ Q)  Write a query to display the employee names and faculty names of ECE dept 1st year? SELECT E.Emp_Name FROM Employee E INNER JOIN Course Instructor Ci ON E.Emp_No=Ci.Emp_No INNER JOIN Semester Se ON Se.Student_ID=Ci.Student_ID INNER JOIN Dept D ON Ci.Dept_No=D.Dept_No WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’ Q) ) Write a query to display student details who enrolled for ECE department? SELECT S.Student_NO, S.Student_Name, S.Enroll_Date FROM Student S INNER JOIN Enroll E ON S.Student_No=E.Student_No INNER JOIN Department D ON E.Dept_No=D.Dept_No WHERE D.Dept_Name=’ECE’ Q) ) Write a query to display student details along with dept name who are enrolled in ECE department first year? SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dept_Name FROM Student S INNER JOIN Enrollment E ON S.Student_No=E.Student_No INNER JOIN Department D ON D.Dept_No=E.Dept_No INNER JOIN Semester Se ON E.Student_ID=Se.Student_ID WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’ Q) ) Write a query to display employee name who is teaching EDC? SELECT E.Emp_Name FROM Employee E INNER JOIN Course Instructor Ci ON E.Emp_No=Ci.Emp_No INNER JOIN Course C ON Ci.Course_ID=C.Course_ID WHERE C.Course_Name=’EDC’ Q) ) Write a query to display employee details along with dept name who are staying in Hyderabad? SELECT E.Emp_No, Emp_Name, D.Dept_Name FROM Employee E INNER JOIN Address A ON E.Emp_No=A.Emp_No INNER JOIN Instructor I ON A.Emp_No=I.Emp_No INNER JOIN Department D ON I.Dept_No=D.Dept_No WHERE A.Address=’hyderabad’   Emp_No Emp_Name Salary Age Dept_Name 101 Anil 20,000 22 ECE 102 Sunil 23000 23 EEE 103 Vijay 32000 24 CSE Using Range Operator:: BETWEEN, NOT BETWEEN Q) Write a Query to display employee details whose salary > 20000 and whose age >23? SELECT * FROM Employee  WHERE Salary>20000 AND Age>23; Q) Write a Query to display employee details whose salary >20000 and who is working in ECE department? SELECT * FROM Employee WHERE Salary>20000 AND Dept_Name=’ECE’ Q) Write a Query to display employee details whose age is BETWEEN 18 and 22? SELECT * FROM Employee Details  WHERE Age BETWEEN 18 AND 22; Q) Write a Query to display employee details whose salary range BETWEEN 20000 and 23000? SELECT * FROM Employee  WHERE Salary BETWEEN 20000 AND 23000; Q) Write a Query to display employee details whose age is NOT BETWEEN 18 & 22? SELECT * FROM Employee WHERE Age NOT BETWEEN 18 AND 22; Using String Operators:: LIKE, NOT LIKE Q) Write a Query to display employee details whose name starts with a? SELECT * FROM Employee WHERE Emp_Name LIKE ‘a%’ a%  ----> starts with a %a  ----> ends with a Q) Write a Query to display employee details and whose age>20 & whose name starts with a? SELECT * FROM Employee WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’ Q) Write a Query to display employee details whose name not starts with a? SELECT * FROM employee WHERE Emp_Name NOT LIKE ‘a%’ List of Related Microsoft Certification Courses:  SSIS  Power BI  SSRS  SharePoint  SSAS  SQL Server DBA  SCCM  BizTalk Server  Team Foundation Server  BizTalk Server Administrator  

Introduction Spring is an application framework highly popular for the development of enterprise applications. It is built for Java and allows software developers to implement the enterprise systems of various sizes including POS, e-commerce, ERP, banking, and so on. The Spring Framework offers a comprehensive configuration and programming model for the latest enterprise applications based on Java on any type of deployment platform.  Spring 5 is the latest version of Spring framework and it has the support for reactive web applications. Being reactive implies that a system is elastic, message-driven, responsive, and resilient. Spring Framework’s two of the most important features are inversion of control (IOC) and dependency injection (DI). This is because, we can build loosely coupled applications when DI and IOC are used properly. What is Spring MVC? One of the modules of the Web layer of the Spring Framework is the Web-Servlet module. This module consists of Spring Framework’s MVC (model-view-controller) implementation for web applications. It provides a model-view-controller architecture and ready elements that can be utilized to build loosely coupled and flexible web applications.  The model-view-controller pattern results in dissociating the various aspects of application such as UI logic, business logic, input logic while offering a loose coupling between these components. Spring MVC is designed around a DispatcherServlet that deals with the HTTP requests and responses.   What is Spring Boot? Spring Boot is a way to ease the creation of stand-alone application with zero or minimal configurations. It is basically an approach to build applications based on Spring framework with minimal configuration. Spring Boot makes it easy for the creation of production-grade, spring powered services and applications with minimum confusion.  It can be used for traditional WAR deployments or for the creation of stand-alone Java applications. Spring Boot provides a widely accessible and a radically faster ‘getting started’ experience for the entire Spring development.  Wish to Learn Spring Boot? Learn it from industry experts.! Comparing Spring Boot and Spring MVC In the above paragraphs, we have learnt what is Spring Framework, Spring Boot, and Spring MVC. Now, let’s compare Spring MVC and Spring Boot. One of the most common dilemmas for the newbie developers who want to use Spring Framework is which one they should go for, either for Spring MVC or Spring Boot.  Well, if you are a new developer, you may think you should go for Spring Boot as it is the latest one. So, let’s dig deeper into the comparison part and understand which one is more recommended than the other.  Spring MVC Spring MVC is a HTTP oriented web application development framework and is a module of Spring framework.  It provides a decoupled way of building web applications. It will let you implement your web application in accordance with the model-view-controller design pattern.  pring MVC is equivalent to JSF(Java Server Faces) in the JavaEE stack. Classes annotated with @Controller are the most popular elements in Spring MVC.  For the implementation of REST based APIs, Spring MVC has an equivalent @RestController. Spring Boot Spring Boot enables you to rapidly build and create Spring applications utilizing the Spring framework.  You need more configuration on your part if you choose to avoid Spring Boot.  The features that only Spring Boot can offer are the capability to create standalone web applications and auto-configuration.  You can do things such as incorporate H2 on the build path with the help of auto-configuration. For an in-memory database, Spring will auto-configure the connection details without any further configuration.  The starter projects provided by Spring Boot will increase productivity by providing defaults and configurations to minimize the effort of a developer avoiding writing a lot of XML configuration, annotations, and boilerplate code. Spring Boot also offers embedded http servers such as Jetty, Tomcat, etc. which you can package together with your applications for creating executable war files.  With the help of Spring Boot, you can tell Spring how many of its modules(spring-web, spring-date, spring-core, and so on) to use and you will also get a quick setup for them.  You can use Spring MVC if you want to develop a web application with Spring. However, for the development of general Spring applications or beginning to learn Spring, it is recommended that you use Spring Boot as it is production ready, mitigates the job, and is being quickly adopted. Also, Spring Boot can use Spring MVC and autoconfigure it. In this case, the choice won’t be between MVC and Boot but between Boot or Bootless. This depends on how open to associated risks and innovation your organization is.  Today, most of the Spring projects are integrated completely with Boot and even the Spring community began to develop various applications based on Spring Boot(monitoring and managing for example). You can benefit from very nice and useful features like remote shell and actuator for monitoring and managing with Boot. This enhances your application with production-ready features. Spring Boot has very powerful and nice configuration controls. Because of these, you can extend the Boot in a very simple and impressive way. Boot is one of the first microservice ready platforms.  Your project configuration will be very simple with the help of Boot and you need not maintain an XML file as well. Boot provides a lot of default implementation and JPA integration and Spring Hibernate will be pretty simple. Conclusion So, if you look at the comparison part, Spring Boot is more useful when compared with Spring MVC as it has many inbuilt features and benefits which make it more reliable to use than MVC. Most of the things are auto configured using Boot. Just like Tomcat, Boot bundles a war file with server runtime. This enables for easy deployment and distribution of web applications.  Spring Boot is quite useful for container based deployments as industry is moving towards them. Though Spring MVC is the oldest and widely used JVM web framework and it has a large community of followers which are quite helpful and have provided numerous answers and tutorials, the popularity of Spring Boot is growing at a rapid pace and the reasons are mentioned clearly in the above section.    Related Articles: Java Spring MVC Vs Node JS

If you're looking for SIEBEL Business Analyst Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, Siebel Business Analyst has a market share of 2.5%.... So, You still have opportunity to move ahead in your career in SIEBEL Business Analyst. Mindmajix offers Advanced SIEBEL Business Analyst Interview Questions 2018 that helps you in cracking your interview & acquire dream career as Business Data Reporting Analyst. Interested in mastering Siebel Business Analyst Training? Enroll now for a FREE demo on Siebel Business Analyst Training. Q1)  What are the differences of VBC and EBC in Siebel? VBC EBC VBC stands for Virtual Business Component EBC stands for external business components Virtual Business Component allows the data to provide an external system which can be viewed using the Siebel application without replicating the fields and the dataset that I have given already. External business components are used to provide a way to add the resources that are accessed by the data and This data is not shown from our end                               VBC provides the detailed description of the account that is stored in the external database EBC doesn’t provide We use the Siebel tools like MQSeries, insert and update on the data to configure VBC It allows data transfer from one place to other and supports the relationship Q2) What is Siebel Gateway? Siebel Gateway is the name of the server that controls the Enterprise Server. Q3) What do you mean by Seibel Admin Mode View and How to create it? If Admin Mode Property set to true then it is called as Admin mode view. It is used to override all the visibility rules Q4) Differentiate between the MVG applet and a pick Applet? MVG applet is used to display the child records for the parent table whereas pick applet is used to update join fields Q5) Describe what do you mean by flowchart and why it is important? A Flowchart is used to show the complete flow of the system through diagrams and symbols. A flowchart is important as it makes developers and nontechnical stakeholders to understand about the system easily. Q6) Describe what do you mean by link specification? Link specification is described as a field object type property. The link specification can be retrieved in the child business component at the time of setting its value to true for a certain field. Q7) Differentiate between Inbound and Outbound in Picklist? Inbounded picklist has defined the process in which users cannot enter the values, other than specifying in drop down. On the other hand out bounded picklist can be defined as the process in which values are entered by the users outside the drop down. Q8) What does UML stand for? UML stands for Unified Modeling Language. Q9) Tell any two types of diagrams that are heavily used in your field? The two diagrams that are heavily used are 1. Use Case Diagram 2. Collaboration Diagram Q10) As a business analyst, what are all the tools that are more helpful? There are many tools that are more helpful out of those the tools that I mostly use are Rational Tools, MS Visio, MS Word, MS Excel, PowerPoint, and MS Project. Check Out SAS Tutorials Q11) What are all the documents that are prepared by a Business Analyst? Business Analyst prepares so many documents and out of those some of the documents prepared by the business analyst are RACI, Gap Analysis Document, RTM, BRD, SRS, FRS, Use Cases, BPM. Q12) In a view what is the maximum number of applets that can be there? A maximum number of applets in a view Depends on the web template that we use, but in general, eight ( 8 ) is the maximum. Q13) Describe how to do the data cleansing in EIM? In EIM, data cleansing is not supported. Q14) What are the steps that are required to develop a product from an idea? The steps that we have to perform are Market Analysis, SWOT Analysis, Competitor Analysis, Personas, Feature set and Strategic Vision, Prioritize Features, Use Cases, Scalability, Monitoring. Q15) What BPMN stand for? BPMN stands for Business Process Model and Notation and described as a graphical representation of business processes. Q16) Can you Name the five basic elements' categories in BPMN? The five basic elements' categories in BPMN are: 1. Flow Objects 2. Data 3. Connecting Objects 4. Swimlanes 5. Artifacts. Q17) Define KANO analysis and have you ever used it? Kano Analysis is a process in which we analyze a system in terms of its requirements to identify its impact on customers' satisfaction. Q18) Name the three key areas in a Kano Analysis? The 3 key areas in KANO analysis are 1. Performance Attributes 2. Unexpected Delighters 3. Must Have Attributes.  Q19) What is the difference between Fish Model and V Model? Fish model is used when there are no ambiguities in the customers' requirements and it is very costly and time-consuming comparatively, on the other hand, the V model requires costly and requires less time it is used when there are ambiguities in the customers' requirements Q20) What does PEST stand for? PEST stands for P: POLITICAL E: ECONOMIC S: SOCIAL T: TECHNOLOGICAL PEST is used to analyze business environment, in which it has to be operated Explore Siebel Business Analyst Sample Resumes! Download & Edit for Free..!Download Now!

If you're looking for Service Desk Analyst Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, Service Desk Analyst has a market share of about 11.0%... So, You still have opportunity to move ahead in your career in Service Desk Analyst. Mindmajix offers Advanced Service Desk Analyst Interview Questions 2018 that helps you in cracking your interview & acquire dream career as IT Data Analyst. Interested in mastering Service Desk Analyst Training? Enroll now for a FREE demo on Service Desk Analyst Training. Q1) What is a Service Desk? A service desk is a place where we provide technical support to the users who require it. Q2) What are the different types of service desk support? There are four types of service desk support, they are 1. Phone 2. Face to face 3. Email 4. Web Q3) Can you name few skills that are required by a service desk analyst? Yes sir, few management skills required by service desk analysts are 1. Stress-management skills 2. Time-management Skills 3. Organizational Skills Q4) Do you think Service desk analysts need IT skills? Definitely service desk analysts require IT skills because service desk analysts provide technical support so we need to make sure to have IT skills so that we can provide the technical support who are in need and even we should update our IT skills with the requirement. Q5) What is the difference between the service desk and help desk? At the service desk, we provide the technical support to the company users whereas in help desk they provide customer support. Q6) As a service desk analyst, what is the more important qualification you need to have? Effective communication and It skills. Effective communication enables me to listen to what users need and I can even communicate effectively which means that I won't lose any information while listening or communicating which helps in fast resolutions. Q7) What is the difference between RAM and ROM? RAM stands for random access memory whereas ROM stands for read-only memory. RAM is used to store temporary data whereas ROM is used to store permanent data. Q8) Can you describe the latest Computer Processors (CPU)?  Some of the lasts computer processors are I3 processor I5 processor I7 processor Q9) Which operating system you are familiar with? As I am using systems from my childhood days I am familiar with almost each and every operating system. The latest operating system that I have been using is windows 10.   Q10) What do you use to view information about system events and application errors on Windows? To view information about system events and application errors on Windows, we can use Event Viewer Check Out SAS Tutorials Q11) Which network protocol you have used? I have used TCP/IP – Transmission Control Protocol/Internet Protocol Q12) As a Service Desk Analyst, what steps will you take for removing malware? As a Service Desk Analyst, I will use anti-spyware, anti-virus and recovery console Q13) What can you do to optimize hard drive performance on Windows? To optimize hard drive performance on Windows, I will use Defrag to speed up the hard drive and computer’s overall performance by optimizing file system. Q14) What do you know about Blue Screen of Death? The Blue Screen of Death can be described as the blue screen which displays a stoop or fatal error message, indicating that Windows has a become unstable and stop. Q15) What motivates you for the job of service desk assistant ? In the service desk assistant job we need to interact continuously with users and help them to solve their problems and as I am good at technical skills, it would make my job easier to help them to resolve their queries Q16) What are the abilities of an ideal service desk analyst? As a service desk analyst, I should have the following abilities: The first ability is to listen to others I need the Ability to communicate my thoughts clearly The third ability is patience, I need to be patient especially in a tense situation Q17) How you deal with an irate user or customer who refuses to calm down? Answer this question in such a way that the interviewer believes in you. I try to know the reason behind his intense and calm down him by giving the proper answer to his questions and will solve his issue as soon as possible so that he gets cool down.   Q18) Can you describe the role of service desk analyst? Service Desk Analysts act as the bridge between end users and technical teams. They are generally involved in identification, initial assessment and assignment of incidents to appropriate technical teams. Q19) Are you ready to work on weekends and  night shifts? Yes sir, I am wide open for any shift on any day Q20) If we ask you to learn any new technology that is required by our clients or workers, will you learn? As a service desk analyst, I will try to keep myself update with the new technologies in the market so definitely I would love to learn the new technologies Q21) Do you know how to troubleshoot DNS problems? We can troubleshoot DNS problems Using nslookp-query internet name servers interactively. Explore Service Desk Analyst Sample Resumes! Download & Edit for Free..!Download Now!

If you're looking for Service Desk Manager Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, Service Desk Manager has a market share of about 11.0%... So, You still have opportunity to move ahead in your career in Service Desk Manager. Mindmajix offers Advanced Service Desk Manager Interview Questions 2018 that helps you in cracking your interview & acquire dream career as Database Developer. Enhance your IT skills and proficiency by taking up the Service Desk Manager Training. Q1) How important is customer service  for you and Why? Complete business processes depends on the customer service, and if you are holding the post of help desk manager you are holding an important position to help the customer in order to provide them best experience. Q2) What is the difference between helpdesk and service desk? A helpdesk  employee makes sure that the customer’s problems are resolved  as soon as possible while the service desk is a only place of interaction between customer and company, where all the information regarding the company’s service are delivered. Q3) What is importance of IT skills in Service desk service and how you  can keep yourself updated with those skills? Computer skills knowledge is a must in these days, in order to process your works faster and they not only make your work easy but also save your time and energy. One can make use of online resources as well as e-books to update their knowledge about the latest trends. Q4) How will you deal with any frustrated customer? The most important thing for any Service desk manager is to  avoid such situations and conditions which can result in any type of conflicts. If things are managed properly conflicts will not arise, but if they do, you need to be patient enough to listen to the customer’s issues and then solve them through proper discussion. Q5) What are the additional positives of an good Service desk person? Ability to listen to customers. Good presentation skills. To be patient and act maturely in a tense situation Q6) Your Source of motivation for the Service desk assistant job? I like to use my communication skills to interact with people and this job opportunity will provide me ample of scope to use that ability of mine to help customers and generate greater profits.   Q7) IF there is language barrier between you and a customer, what will you to communicate with that customer? According to my personal view, language is never a barrier in communicating with someone. Anyhow if you cannot help out the customer then the best thing would be to make him understand with the sign language. In case of a call, we can make use of technology such as google translate or similar tools to communicate with customer. Q8) Tell me about your worst experience so far as a Service desk Manager? Give answer with an example of the situation and include situation with minimum conflicts.   Q9) Are you flexible with the weekend’s job? You can explain them about your own terms regarding the working days. Q10) How do you respond when you do not know the answer? When you are unaware of the answer, try to tell the customer straightway that you don’t know the answer instead of making things up.  And ask them to wait until further assistance is provided by your superior’s. Check Out SAS Tutorials Q11) What is the best thing you like about your job? Customer satisfaction is of utmost importance to me and when i solve issues of any customer and they are happy with my assistance, that gives me satisfaction an happiness in my job.   Q12) How would you rate your problem solving skills? This is a common question asked for Service desk jobs, so again it is a personal question, and you can rate yourself on the scale of 1 to 10. Q13) How you face the criticism in the job? This question is mostly asked to check your level of patience and how you take things at the work place, whether you handle them  with maturity or not. You can give this answer by giving an example also.   Q14) How good are you at solving problem on phone? Solving problem face to face is very different and sometimes difficult than handling them on phone. You can use an example to explain the interviewer more about your problem solving skills as well as it will give them a rough idea about your skills. Q15) Are you ready to work with a team? This question is usually asked to check whether candidate is capable of working with different people and in different circumstances. Q16) How you deal with a customer who is on the phone and refuses to calm down? These kind of situations are very common in a service desk jobs. This question is mostly put to check your patience level and to check how you respond to stressful situations. Q17) Tell me one thing that you don’t like about your job? You can answer these questions according your experience, avoid using issues related to customers and try to give short but brief explanation. Q18) How you can organize your work schedule? I prioritize my work and will schedule my work and assignment accordingly. Q19) If you fail to give caller the proper information, what will you do? I will try to clear all the doubts and covey the information taking proper pauses, but if still the customer does not understand then I will repeat it, or else I will pass the call to my superiors.   Q20) Please tell me some of the task performed by you in your previous company? Explain about the detailed work description in the previous company along with examples.   I hope this page helps you provide with all the important questions needed to crack the interview, if you like the post share it with your friends and keep visiting us. Explore Service Desk Manager Sample Resumes! Download & Edit for Free..!Download Now!

If you're looking for SAS CDM Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, SAS CDM has a market share of about 0.6%... So, You still have opportunity to move ahead in your career in SAS Programmer Analyst. Mindmajix offers Advanced SAS Clinical Data Management Interview Questions 2018 that helps you in cracking your interview & acquire dream career as Clinical SAS Programmer. Interested in mastering SAS CDM Training? Enroll now for a FREE demo on SAS CDM Training. Q1. What are the phases of clinical trials? The following are some of the phases that contain in the clinical trials of SAS. In the first phase, it can test the new treatment or drug that belong to the small group of people. The second phase in the clinical trials can be stated as the experimental treatment or drug that completely belong to the large group of people. The phase 3 clinical trials are mainly used to monitor the side effects by comparing it to the commonly used treatments. Finally, phase 4 can be used to study all the marketing studios that include the benefits and drug's risk. Q2) What is validation procedure and how could you perform the validation using the data set? Validation process in the SAS program mainly verifies the specific output of the program, which is generated by the source programmer. In this programming process, the validator will write the program in order to generate the output which is considered to be valid. You can also activate the validation process by checking the output manually that analyze the data set using the PROC COMPARE. Q3) How to perform the validation for listing if it is 400 pages? However, it is impossible to validate the long-term listing which contains 400 pages, but we can translate the data into respective data set with the help of PROC report and later we can use the PROC COMPARE to compare the data. Q4) How and why to use the PROC COMPARE to validate the listings? Yeah for sure, we can definitely make use of the PROC COMPARE in order to validate the data listing that is entered into the listings manually with the help of this condition. Q5) How to generate listings, graphs, and tables in SAS CDM? With the help of PROC REPORT, we can easily generate the listings, PROC FREQ, PROC TRANSPOSE And PROC MEANS to create the tables, whereas to create the graph we can use the PROC Gplot option. Q6) How many tables can you create in a single day by using the CDM in SAS? Basically, the creation of tables is based on the complexity. For example, If the fore creating tables belong to the same type, then you can create 1-3 table per day. Q7) Explain the Data Sets that are known by you? The following are some of the data sets which we are generally used for the data validation in CDM like Laboratory, analysis, adverse events and demographics. Q8) What is meant by PROCS? The PROCS in the CDM is mainly used to generate the report list according to the display variables that are mentioned in the data validation process. Q9) How to submit the documents to FDA and who is responsible to do that? In order to submit the documents to FDA, we need to use the .pdf or can define the.XML formats. In this process, there is a chance to know more about the macros as well as the programs and records too. Q10) What is SAS documentation? Generally, the SAS documentation will include all the comments, titles, programmer header and footnotes in order to read and understand the program easily. Q11) Explain Oracle clinical and Clin-trial database? The oracle clinical in the SAS is defined as the data management system which is specifically designed by the team of data management functionalities that can process the entire trials. Q12) What is SDTM? The SDTM in the SAS CDM is stated as the study Data tabulation model which is generally developed to perfection the submitted FDA. Q13) Explain CRT? CRT means Case Report Tabulation, which is a document that needs to submit an NDA for the company CRT's to respective FDA. Q14) Explain the contents that contains in the AE dataset and what is their purpose? The adverse events that contain the data set are used to summarise the events according to the patient's lists, treatments that our aid to the safety analysis of the specified drug. Check Out SAS Tutorials Q15) Explain the contents that included in the lab data? Basically, the lab data set will contain the category of a lab test, standard units, week number and SUBJID, which is mainly used to retrieve the main difference in between the key variables that contain in the drug administration. Q16) How to clean and change the values in the data on your own? It is recommended to use the PROC UNIVARIATE and the PROC FREQ that are found in the data. Q17) How to create the CRT's? In order to create the profiles of the patients, we definitely need to use the PROC SQL and PROC CONTENTS in order to create a new simple patients listings, which may contain the sex, age, and race of the patients. Q18) Explain the main difference between the validation and verification? However, the validation and verification meaning will sound the same, but the verification process will have a more sense of testing that results from the accurate reports by conducting the experiments. Whereas the validation will also make more meaningful by declaring the particular statement whether it is true or false. Q19) Explain the SAS features and how do you use it for data validation and trapping? In order to validate and trap the date the following are some of the conditions that we have to use for it like Debug option and put statement. Q20) Explain the PROC CDISC in SAS CDM The PROC CDISC in the SAS CDM is one of the new procedure, which is currently available in the hotfix with the latest version. Basically, it is described as the process which may allow the users to import the data from the latest versions. Explore SAS CDM Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

If you're looking for SAS Administration Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, SAS Administration has a market share of about 21.3%.. So, You still have opportunity to move ahead in your career in SAS Administrator. Mindmajix offers Advanced SAS Administration Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SAS Admin. Learn more about our SAS Administration Training Videos to get ahead in your career. Q1) What is SAS and what are the functions? The full form of SAS is Statistical Analysis System, which is described as an integrated set of software products and services that are used to retrieve the information by managing the data. It is also used to write the graphics as well as the reports with great forecasting support. The SAS analysis is proved for its app development, data warehousing, data mining and operation research. Q2) What is the basic structure SAS administrator? The below-described format is stated as the basic structure of SAS: PROC step in SAA is used to interpret all the saved data whereas the DATA step will manipulate and recovers the data. Q3) How the SAS basic syntax style described? In order to run the whole program successfully, it is very necessary to follow the basic syntax style of SAS. It is must and should to use the semicolon when ending the line. The particular data statement in SAS will elaborate the specific data set. Have to be one space b/w the each and every statement or word It must have input statement as well as the run statement to run the program without any problem. Q4) What is Data Step? The Data Step in the SAS is used to create a new data set, which is able to store the long data with the help of Data Dictionary. It is mainly used to include all the information about all the properties and the variable too. Q5) What is Program Data Vector? Basically, the logical area that contains in the memory is stated as PDV, whereas the SAS can create the new database within the observation in just a particular time. It can also hold the compilation time that holds the record from the external file. Q6) How many data types in SAS? SAS contains two types of data types. They are: Character Data Types Numeric Data Types Q7) How to debug and test the SAS program? In order to debug and test our own SAS program, we need to use the Obs=0 along with the system options to trace the executed program. Q8) Explain the main difference between the NODUP and NODUPKEY options? The NODUP in the SAS is used to compare the entire variables that contain in our dataset, whereas the NODUPKEY is used to differentiate the BY variables. Q9) What are validation tools that are used in SAS? The following are some of the validation tools that we are mainly used to run the programs in SAS. Dataset: Dataset name/ debug data set: name/vsgdvchsj For Macros: Options: mprint mlogic symbolgen Q10) What is the use of PROC Contents and PROC print in SAS? The PROC Contents in SAS is used to display all the information that contains the data set, whereas the PROC print is used to enable to read the data. Q11) What are informats in SAS? Generally, the informats in SAS are used to input the data from the external files and to read, as the format can clearly explain the users on how to read the entire data into SAS variables. Q12) What are the categories that SAS informats are used to the place the data? The following 3 categories where the SAS informats can be placed: Date/Time Informats: INFORMAT Q. Character Informats: $INFORMATq Numeric Informats: INFORMAT q.m Q13) What is the function of CATX syntax? The CATX syntax in the SAS is used to concatenate the strings, fill the blanks, remover trailing and to insert the separators. Check Out SAS Tutorials Q14) Explain the PROC in SAS? PROC in SAS is mainly used to process and analyze data in the form of SAS data set. Generally, it can also control the library routines which performs all the tasks in the SAS data set that summarize, sort and listing the data. Q15) Explain what is data set in SAS? The dataset in SAS is considered as a file which consists of two parts: one is data portion and the second one is descriptor portion. Q16) Explain the key concept of SAS? The following are some of the keys that are included in the SAS: Data step logic Missing values SORT procedure FORMAT procedure to create the value in formats Q17) What is the difference between INFILE and INPUT? The INFILE statements are mainly used to identify the external files, whereas the INPUT statement is used to explain all the variables. Q18) Explain what is factor analysis? The factor analysis in SAS is used for the particular statistical methods that are basically associated with the elimination of variables, which are in terms of the factors and numbers. The main purpose of this factor analysis is to summarize and reduce the data. Q19) How to read the variables in SAS? In order to read the variables in SAS the line/column pointers, length specifiers and informats can be used. Q20) What are the best practices to process the large data sets in SAS programming? Firstobs= and obs= is stated as the best practices to sort the large data sets in the SAS program. Q21) Explain the function of SUBSTR in SAS? Basically, the SUBSTR in the SAS is used to abstract the data that contains in the character variable. Q22) Which features do you use to check the data validations and errors? In order to check the errors in SAS programming, we generally used the Log option where are for the data validation, we can use the Proc Means on how the data looks. Q23) What are 5 ways to perform a table lookup in SAS? The following described 5 ways that are generally used to perform the table lookup in SAS programming. Match merging, Arrays, Direct Access, Format Tables and PROC SQL. Q24) How to generate the test data in SAS without input data? By using the PUT and DATA Null statement you can generate the test data in SAS. Q25) What is the difference between FLOOR and CEIL functions in SAS? The Floor function in the SAS will be used to return the largest integer, which is less than or equal to the specified argument, while the CEIL function will revert the smallest integer. Q26) Explain the main difference between the SAS procedures and functions? The procedure in the SAS will results in one variable value according to the observation, whereas the Functions in SAS will result in the multiple sets of variable values. Q27) Explain the use of % includes a statement in SAS? The %Include a statement in the SAS can be used to read all the files that contain in the current program, which you are running currently and publishing the file successfully into the SAS system at the same time. Explore SAS Administration Sample Resumes! Download & Edit for Free...!Download Now!

If you're looking for SAS DI Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, SAS DI has a market share of about 2.9%... So, You still have opportunity to move ahead in your career in SAS Data Integration Developer. Mindmajix offers Advanced SAS Data Integration Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SAS Data Integration Programmer. Enhance your IT skills and proficiency by taking up the  SAS Data Integration Training. Q1) Explain Data Dimension? Data Dimension in the SAS DI is defined as the data in between the customers, products, and organization that can be accessed with the governance of data. Generally, we need to acquire the best support of all the business data views with great self-service reporting. With the help of this data dimension, you can completely view the strength and capability of the clients, products as well as the organization. Q2) Explain Data Access? The Data Access in SAS Data Integration is defined as an approval that was selected by the particular business users for the sake of large data loads. Q3) Define Data Governance? Data Governance in the Data Integration is a repeatable, affordable and robust process, which downstream all the down checks. This process will able to manage all the updates that can maintain all the consistency of the data. Q4) What is Exception reporting in DI? The exception reporting in the DI is mainly used to report the data which include more data issues. Q5) Explain multi-dimensional reporting? The multidimensional reporting in the DI will allow the users to make the aggregate analysis for the business metrics across the wide range of business dimensions. Q6) What is meant by dimension tables in DI? The dimension table in SAS Data Integration is defined as the integral component for the fact table, as it contains the more textual explanations regarding the business. These dimension tables in the DI is a well-designed model which may contain more attributes and columns, as they will describe the dimension of the row table and serves as the main source of reporting labels. Q7) Explain the data staging area in DI? The staging area in the DI is defined as the data warehouse which can store the data and process it normally in order to extract more transformation. This data staging will always happen in between the data presentation area as well as the operational source systems. Q8) Describe star schema and snowflake schema? The star schema in the DI is described as the set of the database where the single fact table is subjected to the multiple dimension tables, whereas the snowflake schema is stated as the single fact table is interlinked to the multiple sets of dimension tables in order to reduce all the themes. Q9) Explain the main difference between the primary key and unique key? Unique key in the DI is nothing but a key which contains one, two or more columns, which can be usually used to identify the particular row in a table. The value of the unique key is always equal to null, whereas the primary key has one values that don't have a null value. Q10) What is meant by SAS metadata server, SAS application server? The SAS metadata server in the data integration will be able to provide all the metadata management services according to the request that will be raised on the client application. While the SAS application will directly provide their immense services to the customers without any hassle. Check Out SAS Tutorials Q11) Explain the terms operational system and operating data? The operational system in the data integration of SAS will have a set of multiple programs which can provide the major source for the particular data warehouse, whereas the operational data can provide the single source for an entire data warehouse. Q12) Describe the importance of SAS Management Console? The SAS management Console in the Data Integration is mainly used to provide the great user interface in order to perform all the tasks that are done by the SAS administration. Q13) Determine the transformation that can be used in the SAS data integration? Metadata Object is the transformation, which is used in the SAS DI in order to evaluate the process of extracting data into respective data stores. Q14) List out few data transformation which is used in the SAS DI? The following are some of the listed data transformations which are mainly used in the SAS DI: Fact table lookup Data transfer Data Validation Surrogate key generator SAS Sort, SAS splitter, SAS rank Q15) What is metadata object? Metadata object is nothing but a group of attributes which describes a server, a user, a table and other resources on a network. Q16)Explain the change analysis in SAS Data Integration? In SAS Data Integration, change analysis is used to identify the differences between two sets of metadata and also compare 1 set to another set of metadata. Q17)Write a brief description about interaction table in SAS Data Integration? In SAS Data Integration, interaction table tells the relationship between 2 or more tables. Ex: consider the intersection table which describes the many - to - many relationships between table of groups and table of users. Q18)Explain about the scheduler and mention name of the scheduler for scheduling the job? CONTROL-m is the scheduler for scheduling the job and also process flow, view, and dependencies for the user. So, they can optimize business processes efficiently, easily and data in a center which includes different platforms like Microsoft, Windows, VMS, and Unix. Q19)what is the differences between foreign key, primary key, surrogate key, retained key, alternate key, generated key and business key? Foreign key: It has 1 or more columns which are related to unique key or primary key in another table and also dependent upon its related keys in the table. We can have more than one foreign key in one table. We cannot apply foreign key in the table without primary key or unique key in another table. Primary key: primary key is defined as that columns contain unique and not null values. Surrogate key: When rows are added and updated then the surrogate key generate unique integer values sequentially. In order to connect to specific dimensions in the fact table, and the surrogate key includes as a foreign key. Retained key: In dimension table, the primary key has numeric column is nothing but a retained key of that table. Alternate key: Unique key in the table is also said to be alternate key. Generated key: It is used to identify the unique rows in the table and contain only one primary key. There are no null values which contain the primary key. The generated key is used to implement the retained keys and surrogate keys. Business key: The dimension table has one or more columns which comprise the primary key in the source table. Q20)Explain the cons and pros of the SAS DI? By enabling the rapid generation of data warehouses, data streams and data marts which reduces the development time of SAS data integration. The SAS DI sometimes can have performance issues and generate very complex code that is very hard to decode. Explore SAS Data Integration Sample Resumes! Download & Edit for Free..!Download Now!

If you're looking for SAS Grid Administration Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research, SAS Grid Administration has a market share of about 1.9%... So, You still have opportunity to move ahead in your career in SAS Grid Administrator. Mindmajix offers Advanced SAS Grid Administration Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SAS Admin. All the below topics will be covered under SAS Grid Administration Training MindMajix Q1) What is SAS? SAS which is often referred to as leader when it comes to Analytics is an innovative software through which it enables and inspires its clients around the globe to transform data into intelligence. As a SAS administrator, you will have three roles to play namely 1. Platform oriented support 2. User-oriented support 3. Data-oriented support Q2) What is the role of SAS GRID ADMINISTRATOR? The SAS Platform Administrator/GRID Administrator fills in as contact between the SAS Developers and enterprise infrastructure support teams to ensure proper delivery of technology solutions as per business requirements and objectives. The SAS Platform Administrator or SAS GRID Administrator makes sure that the platform is positioned for compliance with IT policies and standards and agreed upon service levels Q3) What is Connection profile? Connection profile can be described as a file that is stored on a desktop which contains the necessary information to connect to your metadata profile. Q4) How do you connect the desktop application to Metadata server? A Desktop application is connected to Metadata server with the help of connection profile. So as soon as you answer this question, interviewer next question would be Q5) What are the five ways to do a table lookup in SAS? The five ways to do a table lookup are Match merging Direct access Format tables Arrays PROC SQL Q6) What is the role of Administrative users? Administrative users have access to Metadata layer which a typical end user doesn’t have. Q7) What is the role of unrestrictive users? As the name suggests Unrestrictive users don’t have access to the Metadata layer, they just perform tasks when the Metadata server is paused. Q8) In SAS, what are the areas that you are most interested in? The areas that I am most interested in SAS are BASE STAT GRAPH ETS Q9) What are the different versions of SAS that you have used until now? The different versions of SAS that I have used until now are SAS 9.1.3 in Windows and UNIX SAS 9.0 in Windows and UNIX SAS 8.2 in Windows and UNIX SAS 7 and SAS 6.12 Q10) What are the best SAS programming practices for handling very large datasets? Some of the best SAS programming practices to handle very large data sets are Sampling method using OBS option or sub setting commenting the Lines Use Data Null Q11) How does the internal authentication work in SAS? During the log in time, let us say you have entered your credentials that it requires, it sends the information to Metadata servers which checks whether the credential details are right or wrong with the available credentials in its database and it accepts the client connection after validating the credentials. Metadata server’s uses SAS identity associated with the internal account to accept the connection. Check Out SAS Tutorials Q12) What do you know about SAS and what we do? SAS turns numbers into a beautiful presentation that allows organizations or government entities to make proper decisions based on that data. Q13) What are the different operating system platforms in which we can use SAS?   We can use SAS in two different operating systems, they are 1. UNIX OR LINUX 2. WINDOWS Q14) Name some categories in SAS 9? Some categories in SAS 9 are Array Bitwise logical operations Descriptive statistics Trigonometric Macro Mathematical Financial’ Hyperbolic SAS file I/O Variable control Q15) What are the different servers in SAS? There are 5 different SAS servers, they are 1. SAS metadata server 2. SAS OLAP server 3. SAS/SHARE server 4. SAS table server 5. SAS deployment tester server. Q16) Name any two SAS spawners? Two SAS spawners are SAS object spawner SAS/CONNECT spawner Q17) Explain the use of PROC gplot? With the help of PROC gplot, we can create more fancier and colorful graphics as it is having a number of options to choose from. Q18) How do you define PROC in SAS? In SAS, PROC steps analyzes and process data in a form of SAS data set which controls and performs tasks on SAS data set such as: Sorting Summarizing Listing  Q19) What is the SAS data set? A SAS data set can be described as a file consisting. These are the two parts: Descriptor portion Data portion Q20) Differentiate between FORMAT and INFORMAT? FORMAT: This indicates SAS to print the variables INFORMAT: This indicates SAS where a number should be read in a particular format. Q21) What is the general format of function In SAS? The general function format of function in SAS is myvalue=FUNCTION_NAME(required_argument1, …, required_argumentN,optional_argument1,…,optional_argumentN); Q22) Describe what are the different levels of administrative users in SAS? There are basically two levels of administrative users in SAS, they are Administrative  Unrestrictive users Q23) In SAS admin differentiate between roles and capabilities Roles determine which user interface elements a user sees when interacting with an application. The features in applications that provide role-based management are called capabilities. Explore SAS Grid Administration Sample Resumes! Download & Edit for Free..!Download Now! Q24) For a user to have access to a standard workspace server, Is internal authentication alone is sufficient? No, as for a user to have access to a standard workplace server, internal authentication alone is not sufficient, even a host account is required.