Interview Questions.

Question 1. Which Tcp/ip Port Does Sql Server Run On? How Can It Be Changed?

Answer :

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

Question 2. What Are The Difference Between Clustered And A Non-clustered Index?

Answer :

A clustered index is a special type of index that reorders the way statistics in the table are bodily saved. Therefore desk will have most effective one clustered index. The leaf nodes of a clustered index incorporate the facts pages.
A non clustered index is a special sort of index wherein the logical order of the index does now not match the physical saved order of the rows on disk. The leaf node of a non clustered index does no longer consist of the records pages. Instead, the leaf nodes incorporate index rows.

Data Mining Interview Questions
Question three. What Are The Different Index Configurations A Table Can Have?

Answer :

A desk can have one of the following index configurations:

No indexes
A clustered index
A clustered index and lots of nonclustered indexes
A nonclustered index
Many nonclustered indexes
Question four. What Are Different Types Of Collation Sensitivity?

Answer :

Case sensitivity - A and a, B and b, and many others.
Accent sensitivity
Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are handled otherwise, it's miles referred to as Kana sensitive.
Width sensitivity - A single-byte individual (half of-width) and the equal individual represented as a double-byte person (full-width) are handled otherwise than it is width sensitive.
Data Mining Tutorial
Question 5. What Is Oltp (on-line Transaction Processing)?

Answer :

In OLTP - on-line transaction processing systems relational database design use the discipline of information modeling and usually follow the Codd rules of statistics normalization with a view to make sure absolute data integrity. Using these regulations complicated statistics is damaged down into its most easy structures (a table) where all the person atomic level elements relate to each different and satisfy the normalization regulations.

SAS Programming Interview Questions
Question 6. What's The Difference Between A Primary Key And A Unique Key?

Answer :

Both number one key and unique key enforces uniqueness of the column on which they may be described. But by way of default primary key creates a clustered index at the column, in which are particular creates a nonclustered index by way of default. Another important distinction is that, primary key does not permit NULLs, however particular key permits one NULL only.

Question 7. What Is Difference Between Delete And Truncate Commands?

Answer :

Delete command eliminates the rows from a table based on the circumstance that we provide with a WHERE clause. Truncate will really cast off all the rows from a table and there may be no data inside the table after we run the truncate command.
TRUNCATE:

TRUNCATE is faster and uses fewer device and transaction log resources than DELETE.
TRUNCATE removes the statistics by using deallocating the records pages used to keep the table's statistics, and only the web page deallocations are recorded in the transaction log.
TRUNCATE eliminates all rows from a table, but the desk structure, its columns, constraints, indexes and so forth, stays. The counter used by an identification for brand new rows is reset to the seed for the column.
You can not use TRUNCATE TABLE on a table referenced by way of a FOREIGN KEY constraint. Because TRUNCATE TABLE isn't always logged, it can not set off a cause.
TRUNCATE can't be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identification of the table
DELETE:

DELETE eliminates rows one by one and facts an access within the transaction log for every deleted row.
If you need to preserve the identification counter, use DELETE alternatively. If you want to eliminate desk definition and its facts, use the DROP TABLE statement.
DELETE Can be used without or with a WHERE clause
DELETE Activates Triggers.
DELETE may be rolled back.
DELETE is DML Command.
DELETE does now not reset identification of the desk.
Note: DELETE and TRUNCATE both can be rolled again while surrounded through TRANSACTION if the present day session isn't always closed. If TRUNCATE is written in Query Editor surrounded via TRANSACTION and if consultation is closed, it can't be rolled again but DELETE may be rolled lower back.

 

SAS Programming Tutorial Clinical SAS Interview Questions
Question eight. When Is The Use Of Update_statistics Command?

Answer :

This command is largely used while a massive processing of information has came about. If a huge amount of deletions any amendment or Bulk Copy into the tables has passed off, it has to update the indexes to take these adjustments under consideration. UPDATE_STATISTICS updates the indexes on these tables therefore.

Question nine. What Is The Difference Between A Having Clause And A Where Clause?

Answer :

They specify a search circumstance for a group or an mixture. But the distinction is that HAVING may be used handiest with the SELECT announcement. HAVING is commonly used in a GROUP BY clause. When GROUP BY isn't always used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY feature in a query whereas WHERE Clause is implemented to every row before they may be a part of the GROUP BY function in a query.

SASS (Syntactically Awesome Style sheets) Interview Questions
Question 10. What Are The Properties And Different Types Of Sub-queries?

Answer :

Properties of Sub-Query

A sub-query should be enclosed in the parenthesis.
A sub-question must be put in the right hand of the contrast operator, and
A sub-question can not include an ORDER-BY clause.
A question can include more than one sub-question.
Types of Sub-Query

Single-row sub-question, where the sub-query returns only one row.
Multiple-row sub-query, in which the sub-query returns a couple of rows,. And
Multiple column sub-question, wherein the sub-question returns more than one columns
SASS (Syntactically Awesome Style sheets) Tutorial
Question 11. What Is Sql Profiler?

Answer :

SQL Profiler is a graphical device that allows system administrators to screen events in an instance of Microsoft SQL Server. You can seize and save facts about each occasion to a file or SQL Server desk to investigate later. For instance, you could reveal a manufacturing surroundings to look which saved methods are hampering performances by executing too slowly.
Use SQL Profiler to reveal best the occasions in that you are involved. If traces are getting too large, you may filter them primarily based at the records you want, so that handiest a subset of the occasion information is accrued. Monitoring too many events adds overhead to the server and the monitoring method and can reason the hint file or trace table to develop very massive, specially while the tracking method takes place over a long time frame.

Data analyst Interview Questions
Question 12. What Are The Authentication Modes In Sql Server? How Can It Be Changed?

Answer :

Windows mode and Mixed Mode - SQL and Windows. To trade authentication mode in SQL Server click on Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server application institution. Select the server then from the Tools menu choose SQL Server Configuration Properties, and pick the Security web page.

Data Mining Interview Questions
Question thirteen. Which Command Using Query Analyzer Will Give You The Version Of Sql Server And Operating System?

Answer :

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('version').

Question 14. What Is Sql Server Agent?

Answer :

SQL Server agent plays an crucial function inside the everyday duties of a database administrator (DBA). It is frequently left out as one of the major equipment for SQL Server management. Its cause is to ease the implementation of tasks for the DBA, with its complete- characteristic scheduling engine, which lets in you to agenda your own jobs and scripts.

Question 15. Can A Stored Procedure Call Itself Or Recursive Stored Procedure? How Much Level Sp Nesting Is Possible?

Answer :

Yes. Because Transact-SQL supports recursion, you can write stored techniques that name themselves. Recursion may be defined as a way of trouble fixing in which the solution is arrived at through repetitively making use of it to subsets of the hassle. A commonplace software of recursive common sense is to perform numeric computations that lend themselves to repetitive evaluation through the identical processing steps. Stored processes are nested when one saved system calls every other or executes controlled code through referencing a CLR recurring, type, or combination. You can nest stored strategies and controlled code references up to 32 degrees.

SAS DI Interview Questions
Question 16. What Is Log Shipping?

Answer :

Log transport is the method of automating the backup of database and transaction log files on a manufacturing SQL server, and then restoring them onto a standby server. Enterprise Editions most effective helps log shipping. In log delivery the transactional log record from one server is routinely up to date into the backup database on the alternative server. If one server fails, the alternative server can have the identical db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it's going to automatically backup transaction logs at some point of the day and mechanically restore them on the standby server at described c program languageperiod.

Question 17. Name 3 Ways To Get An Accurate Count Of The Number Of Records In A Table?

Answer :

SELECT * FROM table1 
SELECT COUNT(*) FROM table1 
SELECT rows FROM sysindexes WHERE identity = OBJECT_ID(table1) AND indid < 2
Advanced SAS Interview Questions
Question 18. What Does It Mean To Have Quoted_identifier On? What Are The Implications Of Having It Off?

Answer :

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by using double quotation marks, and literals ought to be delimited with the aid of unmarried quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers can not be quoted and have to observe all Transact-SQL guidelines for identifiers.

SAS Programming Interview Questions
Question 19. What Is The Difference Between A Local And A Global Temporary Table?

Answer :

A local transient desk exists most effective at some stage in a connection or, if described interior a compound declaration, at some stage in the compound announcement.
A international temporary table remains within the database permanently, but the rows exist simplest within a given connection. When connection is closed, the records within the international transient table disappears. However, the table definition remains with the database for get right of entry to when database is opened subsequent time.

Question 20. What Is The Stuff Function And How Does It Differ From The Replace Function?

Answer :

STUFF feature is used to overwrite current characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string on the way to have characters substituted, start is the beginning role, period is the wide variety of characters within the string which can be substituted, and replacement_characters are the new characters interjected into the string. REPLACE characteristic to update current characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every prevalence of search_string found within the string_expression will be replaced with replacement_string.

Oracle Data Guard Interview Questions
Question 21. What Is Primary Key?

Answer :

A PRIMARY KEY constraint is a completely unique identifier for a row inside a database table. Every table have to have a number one key constraint to uniquely perceive every row and simplest one number one key constraint may be created for every table. The number one key constraints are used to put in force entity integrity.

Question 22. What Is Unique Key Constraint?

Answer :

A UNIQUE constraint enforces the individuality of the values in a set of columns, so no duplicate values are entered. The specific key constraints are used to put into effect entity integrity because the primary key constraints.

Question 23. What Is Foreign Key?

Answer :

A FOREIGN KEY constraint prevents any moves that would break hyperlinks between tables with the corresponding facts values. A foreign key in a single table factors to a primary key in some other table. Foreign keys save you actions that could depart rows with overseas key values whilst there aren't any number one keys with that cost. The foreign key constraints are used to implement referential integrity.

Base Sas Interview Questions
Question 24. What Is Check Constraint?

Answer :

A CHECK constraint is used to restrict the values that can be placed in a column. The take a look at constraints are used to put in force domain integrity.

Clinical SAS Interview Questions
Question 25. What Is Not Null Constraint?

Answer :

A NOT NULL constraint enforces that the column will not be given null values. The now not null constraints are used to enforce area integrity, as the take a look at constraints.

Question 26. How To Get @@errors And @@rowcount At The Same Time?

Answer :

If @@Rowcount is checked after Error checking assertion then it'll have zero as the value of @@Recordcount as it would were reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the equal time do each in equal declaration and store them in nearby variable.
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Data Visualization Interview Questions
Question 27. What Is A Scheduled Jobs Or What Is A Scheduled Tasks?

Answer :

Scheduled tasks let consumer automate tactics that run on everyday or predictable cycles. User can schedule administrative tasks, along with dice processing, to run in the course of times of sluggish enterprise pastime. User also can determine the order in which responsibilities run by using creating process steps within a SQL Server Agent task. E.G. Lower back up database, Update Stats of Tables. Job steps provide consumer manipulate over go with the flow of execution. If one job fails, user can configure SQL Server Agent to retain to run the ultimate tasks or to stop execution.

SASS (Syntactically Awesome Style sheets) Interview Questions
Question 28. What Are The Advantages Of Using Stored Procedures?

Answer :

Stored system can decreased network traffic and latency, boosting software performance.
Stored manner execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
Stored methods help promote code reuse.
Stored tactics can encapsulate good judgment. You can change stored system code with out affecting customers.
Stored procedures offer better safety for your information.
Question 29. What Is A Table Called, If It Has Neither Cluster Nor Non-cluster Index? What Is It Used For?

Answer :

Unindexed desk or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a desk that doesn't have a clustered index and, therefore, the pages are not linked by tips. The IAM pages are the most effective systems that hyperlink the pages in a table together. Unindexed tables are top for immediate storing of records. Many times it is better to drop all indexes from desk after which do bulk of inserts and to repair those indexes after that.

SAS Macro Interview Questions
Question 30. Can Sql Servers Linked To Other Servers Like Oracle?

Answer :

SQL Server can be linked to any server provided it has OLE-DB company from Microsoft to allow a hyperlink. E.G. Oracle has an OLE-DB company for oracle that Microsoft gives to add it as related server to SQL Server organization.

Question 31. What Is Bcp? When Does It Used?

Answer :

BulkCopy is a tool used to duplicate huge quantity of statistics from tables and perspectives. BCP does now not copy the structures same as supply to destination. BULK INSERT command allows to import a records report right into a database table or view in a user-distinctive format.

Question 32. How To Implement One-to-one, One-to-many And Many-to-many Relationships While Designing Tables?

Answer :

One-to-One dating can be carried out as a unmarried desk and infrequently as  tables with number one and foreign key relationships. One-to-Many relationships are carried out by means of splitting the statistics into two tables with primary key and overseas key relationships. Many-to-Many relationships are applied the usage of a junction desk with the keys from both the tables forming the composite primary key of the junction desk.

SAS BI Interview Questions
Question 33. What Is An Execution Plan? When Would You Use It? How Would You View The Execution Plan?

Answer :

An execution plan is essentially a avenue map that graphically or textually indicates the statistics retrieval techniques chosen by using the SQL Server query optimizer for a stored manner or advert- hoc question and is a completely beneficial tool for a developer to understand the performance traits of a question or stored process since the plan is the one that SQL Server will area in its cache and use to execute the saved system or question. From within Query Analyzer is an choice called "Show Execution Plan" (located at the Query drop-down menu). If this feature is grew to become on it will display query execution plan in separate window whilst question is ran again.

Data analyst Interview Questions
Question 34. What Sas Statements Would You Code To Read An External Raw Data File To A Data Step?

Answer :

INFILE assertion.

Question 35. How Do You Read In The Variables That You Need?

Answer :

Using Input announcement with the column guidelines like @five/12-17 etc.

Question 36. Are You Familiar With Special Input Delimiters? How Are They Used?

Answer :

DLM and DSD are the delimiters that I’ve used. They should be included inside the infile assertion. Comma separated values documents or CSV files are a not unusual type of record that may be used to read with the DSD option. DSD choice treats  delimiters in a row as MISSING fee.
DSD additionally ignores the delimiters enclosed in citation marks.

SAS DI Interview Questions
Question 37. If Reading A Variable Length File With Fixed Input, How Would You Prevent Sas From Reading The Next Record If The Last Variable Didn't Have A Value?

Answer :

By the usage of the choice MISSOVER in the infile declaration.If the enter of some records lines are shorter than others then we use TRUNCOVER choice within the infile statement.

Question 38. What Do The Put And Input Functions Do?

Answer :

INPUT feature converts character facts values to numeric values.
PUT feature converts numeric values to character values.EX: for INPUT: INPUT (source, informat)
For PUT: PUT (supply, format)
Note that INPUT characteristic calls for INFORMAT and PUT function calls for FORMAT.
If we omit the INPUT or the PUT characteristic for the duration of the statistics conversion, SAS will discover the mismatched variables and could try an automatic individual-to-numeric or numeric-to-individual conversion. But occasionally this doesn’t work because $ sign prevents such conversion. Therefore it's miles usually really helpful to encompass INPUT and PUT capabilities on your applications while conversions occur.
Question 39. What Is The Significance Of The 'of' In X=sum (of A1-a4, A6, A9);

Answer :

If don’t use the OF characteristic it won't be interpreted as we anticipate. For example the characteristic above calculates the sum of a1 minus a4 plus a6 and a9 and not the whole sum of a1 to a4 & a6 and a9. It is real for imply option additionally.

 

Question 40. What Other Sas Products Have You Used And Consider Yourself Proficient In Using?

Answer :

Data _NULL_ statement, Proc Means, Proc Report, Proc tabulate, Proc freq and Proc print, Proc Univariate and many others.

 

Advanced SAS Interview Questions
Question forty one. Name Several Ways To Achieve Efficiency In Your Program.

Answer :

Efficiency and performance strategies can be classified into five one of a kind regions.
·CPU time
·Data Storage
· Elapsed time
· Input/Output
· Memory CPU Time and Elapsed Time- Base line measurements

Question 42. What Has Been Your Most Common Programming Mistake?

Answer :

Missing semicolon and now not checking log after submitting software, Not the use of debugging strategies and no longer the usage of Fsview alternative vigorously.

Oracle Data Guard Interview Questions
Question forty three. Explain What Is Sas? What Are The Functions Does It Performs?

Answer :

SAS means Statistical Analysis System, which is an incorporated set of software merchandise.
• Information retrieval and facts control
• Writing reports and pix
• Statistical evaluation, econometrics and statistics mining
• Business making plans, forecasting and selection help
• Operation studies and Project management
• Quality Improvement
• Data Warehousing
• Application Development

Question forty four. Explain What Is The Basic Structure Of Sas Programming?

Answer :

The primary shape of SAS are
• Program Editor
• Explorer Window
• Log Window

Question 45. What Is The Basic Syntax Style In Sas?

Answer :

To run program effectively, and you've following basic elements:
• There should be a semi-colon at the stop of every line
• A data announcement that defines your information set
• Input assertion
• There should be at least one area between every phrase or statement
• A run announcement

For example: Infile ‘H: StatHWyourfilename.Dat’;

Question forty six. Explain What Is Data Step?

Answer :

The Data step creates an SAS dataset which includes the data together with a “records dictionary.” The facts dictionary holds the data about the variables and their residences.

Question forty seven. Explain What Is Pdv?

Answer :

The logical region in the reminiscence is represented by PDV or Program Data Vector. At the time, SAS creates a database of one statement at a time. An enter buffer is created at the time of compilation which holds a document from an external document. The PDV is created following the enter buffer creation 

 

Question forty eight. Mention What Are The Data Types Does Sas Contain?

Answer :

The records kinds in SAS are Numeric and Character.

Question 49. In Sas Explain Which Statement Does Not Perform Automatic Conversions In Comparisons?

Answer :

In SAS, the “in which” announcement does no longer perform automatic conversions in comparisons.

 

Question 50. Explain How You Can Debug And Test Your Sas Program?

Answer :

You can debug and take a look at your SAS software by using Obs=zero and structures alternatives to hint this system execution in log

Question 51. Mention What Is The Difference Between Nodupkey And Nodup Options?

Answer :

The difference between the NODUP and NODUPKEY is that, NODUP compares all the variables in our dataset while NODUPKEY compares just the BY variables

Question fifty two. Mention The Validation Tools Used In Sas?

Answer :

For DataSet : Data set call/ debug Data set: Name/stmtchk
For Macros: Options: mprint mlogic symbolgen

Question 53. Explain What Does Proc Print, And Proc Contents Are Used For?

Answer :

To show the contents of the SAS dataset PROC print is used and additionally to assure that the records were study into SAS successfully. While, PROC CONTENTS show statistics about an SAS dataset.

Question 54. Explain What Is The Use Of Function Proc Summary?

Answer :

The syntax of proc summary is same as that of proc manner, it computes descriptive information on numeric variables in the SAS dataset.

 

Question 55. Explain What Proc Glm Does?

Answer :

Proc glm performs easy and multiple regression, evaluation of variance (ANOVAL), analysis of covariance, multivariate analysis of variance and repeated degree analysis of variance.

Question fifty six. Explain What Is Sas Informats?

Answer :

SAS INFORMATS are used to read, or enter records from outside files referred to as Flat Files ASCII files, textual content documents or sequential files). The informat will inform SAS on the way to read statistics into SAS variables.

Question fifty seven. Mention The Category In Which Sas Informats Are Placed?

Answer :

SAS informats are placed in three classes,
• Character Informats : $INFORMATw
• Numeric Informats : INFORMAT w.D
• Date/Time Informats: INFORMAT w.

Question fifty eight. What Function Catx Syntax Does?

Answer :

CATX syntax concatenate person strings dispose of trailing and leading blanks and inserts separators.

Question fifty nine. Explain What Is The Use Of Proc Gplot?

Answer :

PROC gplot has more alternatives and may create more colorful and fancier graphics.

Question 60. What Are The Scrubbing Procedures In Sas?

Answer :

Proc Sort with nodupkey option, because it will eliminate the duplicate values.