Sql Dba Interview Questions And Answers

Sql Dba Interview Questions list for experienced

  1. Which TCP/IP port does SQL Server run on? How can it be changed?
  2. What are the difference between clustered and a non-clustered index?
  3. What are the different index configurations a table can have?
  4. What are different types of Collation Sensitivity?
  5. What is OLTP (Online Transaction Processing)?
  6. What\'s the difference between a primary key and a unique key?
  7. What is difference between DELETE and TRUNCATE commands?
  8. When is the use of UPDATE_STATISTICS command?
  9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
  10. What are the properties and different Types of Sub-Queries?
  11. What is SQL Profiler?
  12. . What are the authentication modes in SQL Server? How can it be changed?
  13. Which command using Query Analyzer will give you the version of SQL server and operating system?
  14. . What is SQL Server Agent?
  15. Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
  16. What is Log Shipping?
  17. Name 3 ways to get an accurate count of the number of records in a table?
  18. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
  19. What is the difference between a Local and a Global temporary table?
  20. What is the STUFF function and how does it differ from the REPLACE function?
  21. What is PRIMARY KEY?
  22. What is UNIQUE KEY constraint?
  23. What is FOREIGN KEY?
  24. What is CHECK Constraint?
  25. . What is NOT NULL Constraint?
  26. How to get @@ERROR and @@ROWCOUNT at the same time?
  27. What is a Scheduled Jobs or What is a Scheduled Tasks?
  28. What are the advantages of using Stored Procedures?
  29. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
  30. Can SQL Servers linked to other servers like Oracle?
  31. What is BCP? When does it used?
  32. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  33. What is an execution plan? When would you use it? How would you view the execution plan?
  34. What is the difference between inner and outer join? Explain with example.
  35. What is the difference between JOIN and UNION?
  36. What is the difference between UNION and UNION ALL?
  37. What is the difference between WHERE clause and HAVING clause?
  38. What is Self Join and why is it required?
  39. How can we transpose a table using SQL (changing rows to column or vice-versa) ?
  40. How to select first 5 records from a table?
  41. What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?
  42. What are the differences among ROWNUM, RANK and DENSE_RANK?
  43. Why does the selected column have to be in the group by clause or part of an aggregate function?
  44. 4. What are the different type of SQL\'s statements ?
  45. What are the Advantages of SQL ?
  46. what is a field in a database ?
  47. What is a Record in a database ?
  48. What is a Table in a database ?
  49. What is a database transaction?
  50. What are properties of a transaction?
  51. What is a Database Lock ?
  52. . What are the type of locks ?
  53. What are the different type of normalization?
  54. What is a primary key?
  55. What is a Composite Key ?
  56. What is a Composite Primary Key ?
  57. What is a Foreign Key ?
  58. What is a Unique Key ?
  59. . Define SQL Insert Statement ?
  60. Define SQL Update Statement ?
  61. Define SQL Delete Statement ?
  62. What are wild cards used in database for Pattern Matching ?
  63. Define Join and explain different type of joins?
  64. What is Self-Join?
  65. . What is Cross Join?
  66. What is a view?
  67. What is a materialized view?
  68. . What are the advantages and disadvantages of views in a database?
  69. What is a stored procedure?
  70. What are the advantages a stored procedure?
  71. . What is a trigger?
  72. Explain the difference between DELETE , TRUNCATE and DROP commands?
  73. What is the difference between Cluster and Non cluster Index?
  74. What is Union, minus and Interact commands?
  75. ) What is data control language?
  76. What is data manipulation language?
  77. What is the difference between clustered and non clustered index in SQL?
  78. What is the SQL query to display current date?
  79. Which types of join is used in SQL widely?
  80. What is \"TRIGGER\" in SQL?
  81. What is self join and what is the requirement of self join?
  82. What are set operators in SQL?
  83. What is a constraint? Tell me about its various levels.
  84. Write an SQL query to find names of employee start with \'A\'?
  85. Write an SQL query to get third maximum salary of an employee from a table named employee_table.
  86. What is the difference between DELETE and TRUNCATE statement in SQL?
  87. What is ACID property in database?
  88. What is DBMS?
  89. What is RDBMS?
  90. What is SQL?
  91. What is a Database?
  92. What is a join?
  93. What are tables and Fields?
  94. . What are the types of join and explain each?
  95. What is normalization?
  96. What is Denormalization.
  97. What are all the different normalizations?
  98. First Normal Form (1NF)
  99. Second Normal Form (2NF):.
  100. Third Normal Form (3NF):.
  101. Fourth Normal Form (3NF):.
  102. What is an Index?
  103. What are all the different types of indexes?
  104. What is a Cursor?
  105. What is a relationship and what are they?
  106. What is subquery?
  107. What are the types of subquery?
  108. . What is the difference between DELETE and TRUNCATE commands?
  109. What are local and global variables and their differences?
  110. What is a constraint?
  111. What is data Integrity?
  112. What is Auto Increment?
  113. What is Datawarehouse?
  114. . What is user defined functions?
  115. What are all types of user defined functions?
  116. What is collation?
  117. What are all different types of collation sensitivity?
  118. Advantages and Disadvantages of Stored Procedure?
  119. What is Online Transaction Processing (OLTP)?
  120. . What is CLAUSE?
  121. What is recursive stored procedure?
  122. What is an ALIAS command?
  123. What is the difference between TRUNCATE and DROP statements?
  124. What are aggregate and scalar functions?
  125. How can you create an empty table from an existing table?
  126. How to fetch common records from two tables?
  127. . How to fetch alternate records from a table?
  128. How to select unique records from a table?
  129. What is the command used to fetch first 5 characters of the string?
  130. . Which operator is used in query for pattern matching?
  131. SELECT statements:
  132. The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
  133. The INSERT INTO Statements:
  134. The Update Statement:
  135. The Delete Statements:
  136. What Is mSQL?
  137. What Is Table?
  138. What Is Column?
  139. What Is Row?
  140. How many TRIGGERS are allowed in MySql table?
  141. Differentiate between FLOAT and DOUBLE.
  142. Tell us something about Heap tables.
  143. How do you control the max size of a HEAP table?
  144. What are the advantages of MySQL in comparison to Oracle?
  145. What does myisamchk do?
  146. What is BLOB?
  147. What is TEXT?
  148. What is the difference between BLOB and TEXT?
  149. How is MyISAM table stored?
  150. Explain advantages of MyISAM over InnoDB?
  151. How would concatenate strings in MySQL?
  152. How would you get the current date in Mysql?
  153. How would you enter Characters as HEX Numbers?
  154. How are MySQL timestamps seen to a user?
  155. Explain about MySQL and its features.
  156. What are the disadvantages of MySQL?
  157. What are the security recommendations while using MySQL?
  158. What are MyISAM tables?
  159. Explain the difference between MyISAM Static and MyISAM Dynamic.
  160. Explain advantages of InnoDB over MyISAM.
  161. How to use myisamchk to check or repair MyISAM tables?
  162. How to Check MyISAM Tables for Errors?
  163. Explain the options of myisamchk to improve the performance of a table.
  164. Discuss about MyISAM Key Cache.
  165. Discuss about MyISAM Index Statistics Collection.
  166. What is HEAP table?
  167. What is Query Cache in MySQL?
  168. How is Exception Handling handled in MySQL?
  169. What are the Performance and Scalability characteristics of MySQL?
  170. What are the limitations of mysql in Comparison of Oracle? MySQL vs. Oracle.
  171. What is a Trigger in MySQL? Define different types of Trigger
  172. What is the difference between CHAR_LENGTH and LENGTH?
  173. Explain the difference between BOOL, TINYINT and BIT.
  174. What are ENUMs used for in MySQL?
  175. Explain MySQL Aggregate Functions.
  176. Describe Transaction-Safe Table Types in MySQL
  177. Describe MySQL Connection using mysql binary.
  178. MySQL - Stored Procedures and Triggers
  179. Primary Keys and Auto Increment Fields in MySQL
  180. COMMIT and ROLLBACK in MySQL
  181. How would you write a query to select all teams that won either 2, 4, 6 or 8 games?
  182. What Is Index?
  183. What Is View?
  184. What Is Union?
  185. What Is ISAM?
  186. What Is MyISAM?
  187. What Is InnoDB?
  188. What Is BDB (BerkeleyDB)?
  189. What Is CSV?
  190. What Is Transaction?
  191. What Is Commit?
  192. What Is Rollback?
  193. Explain what Is MySQL?
  194. How To Install MySQL?
  195. How To Start MySQL Server?
  196. How Do You Know If Your MySQL Server Is Alive?
  197. How Do You Know the Version of Your MySQL Server?
  198. How To Create a Test Table in Your MySQL Server?
  199. How To Shutdown MySQL Server?
  200. What Tools Available for Managing MySQL Server?
  201. What Is \\\"mysqld\\\"?
  202. What Is \"mysqladmin\" in MySQL?
  203. How To Check Server Status with \"mysqladmin\"?
  204. How To Shut Down the Server with \"mysqladmin\"?
  205. How To Use \"mysql\" to Run SQL Statements?
  206. How To Show All Tables with \"mysql\"?
  207. What Is \"mysqlcheck\"?
  208. How To Analyze Tables with \"mysqlcheck\"?
  209. What Is \"mysqlshow\"?
  210. What Is \"mysqldump\"?
  211. How To Dump a Table to a File with \"mysqldump\"?
  212. What Is \"mysqlimport\"?
  213. How To Load Data Files into Tables with \\\"mysqlimport\\\"?
  214. What Is the Command Line End User Interface - mysql?
  215. What Are the \"mysql\" Command Line Options?
  216. What Are the \"mysql\" Command Line Arguments?
  217. How Many SQL DDL Commands Are Supported by \"mysql\"?
  218. How Many SQL DML Commands Are Supported by \"mysql\"?
  219. What Are the Non-Standard SQL Commands Supported by \"mysql\"?
  220. How To Get Help Information from the Server?
  221. How To Run \"mysql\" Commands from a Batch File?
  222. How To Return Query Output in HTML Format?
  223. What Is SQL in MySQL?
  224. How Many Groups of Data Types?
  225. What Are String Data Types?
  226. What Are the Differences between CHAR and NCHAR?
  227. What Are the Differences between CHAR and VARCHAR?
  228. What Are the Differences between BINARY and VARBINARY?
  229. What Are Numeric Data Types?
  230. What Are Date and Time Data Types?
  231. How To Calculate Expressions with SQL Statements?
  232. How To Include Comments in SQL Statements?
  233. How To Include Character Strings in SQL statements?
  234. How To Escape Special Characters in SQL statements?
  235. How To Concatenate Two Character Strings?
  236. How To Include Numeric Values in SQL statements?
  237. How To Enter Characters as HEX Numbers?
  238. How To Enter Numeric Values as HEX Numbers?
  239. How To Enter Binary Numbers in SQL Statements?
  240. What Are NULL Values?
  241. What Happens If NULL Values Are Involved in Expressions?
  242. How To Convert Numeric Values to Character Strings?
  243. How To Convert Character Strings to Numeric Values?
  244. How To Use IN Conditions?
  245. How To Use LIKE Conditions?
  246. How To Use Regular Expression in Pattern Match Conditions?
  247. How To Use CASE Expression?
  248. What Are Date and Time Data Types in MySQL?
  249. How To Write Date and Time Literals?
  250. How To Convert Dates to Character Strings?
  251. How To Convert Character Strings to Dates?
  252. What Are Date and Time Intervals?
  253. How To Increment Dates by 1 in MySQL?
  254. How To Decrement Dates by 1 in MySQL?
  255. How To Calculate the Difference between Two Dates?
  256. How To Present a Past Time in Hours, Minutes and Seconds?
  257. How To Extract a Unit Value from a Date and Time?
  258. What Are Date and Time Functions in MySQL?
  259. What Is TIMESTAMP in MySQL?
  260. How Many Ways to Get the Current Time?
  261. What Are DDL Statements in MySQL?
  262. How To Create a New Table in MySQL?
  263. What Happens If You No CREATE Privilege in a Database?
  264. How To Get a List of All Tables in a Database?
  265. How To Get a List of Columns in an Existing Table?
  266. How To See the CREATE TABLE Statement of an Existing Table?
  267. How To Create a New Table by Selecting Rows from Another Table in MySQL?
  268. . How To Add a New Column to an Existing Table in MySQL?
  269. How To Delete an Existing Column in a Table?
  270. How To Rename an Existing Column in a Table?
  271. How To Rename an Existing Table in MySQL?
  272. How To Drop an Existing Table in MySQL?
  273. How To Create a Table Index in MySQL?
  274. How To Get a List of Indexes of an Existing Table?
  275. How To Drop an Existing Index in MySQL?
  276. How To Create a New View in MySQL?
  277. How To Drop an Existing View in MySQL?
  278. What are the drivers in MySQL?
  279. How do you login to MySql using Unix shell?
  280. What is the difference between MyISAM Static and MyISAM Dynamic?
  281. How can you see all indexes defined for a table?
  282. Is Mysql query is case sensitive?
  283. What is the difference between mysql_fetch_array and mysql_fetch_object?
  284. What are the different tables present in MySQL?
  285. How MySQL Optimizes DISTINCT?
  286. How many columns can be used for creating Index?
  287. What is the different between NOW() and CURRENT_DATE()?
  288. What are the nonstandard string types?
  289. What are all the Common SQL Function?
  290. Explain Access Control Lists.
  291. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
  292. What are the differences between Get and post methods in form submitting. give the case where we can use get and we can use post methods?
  293. Who is the father of PHP and explain the changes in PHP versions?
  294. How can we submit a form without a submit button?
  295. In how many ways we can retrieve the data in the result set of MySQL using PHP?
  296. What is the difference between mysql_fetch_object and mysql_fetch_array?
  297. What is the difference between $message and $$message?
  298. How can we extract string 'abc.com ' from a string 'http://info@abc.com' using regular expression of PHP?
  299. How can we create a database using PHP and MySQL?
  300. What are the differences between require and include, include_once and require_once?
  301. Can we use include ("abc.PHP") two times in a PHP page "makeit.PHP"?
  302. What are the different tables present in MySQL, which type of table is generated when we are creating a table in the following syntax: create table employee (eno int(2),ename varchar(10)) ?
  303. Suppose your Zend engine supports the mode Then how can u configure your PHP Zend engine to support mode ?
  304. Shopping cart online validation i.e. how can we configure Paypal, etc.?
  305. What is meant by nl2br()?
  306. Draw the architecture of Zend engine?
  307. What are the current versions of apache, PHP, and MySQL?
  308. What are the reasons for selecting lamp (Linux, apache, MySQL, PHP) instead of combination of other software programs, servers and operating systems?
  309. How can we encrypt and decrypt a data present in a MySQL table using MySQL?
  310. What are the features and advantages of object-oriented programming?
  311. What are the differences between procedure-oriented languages and object-oriented languages?
  312. What is the use of friend function?
  313. What are the differences between public, private, protected, static, transient, final and volatile?
  314. How can we convert asp pages to PHP pages?
  315. What is the functionality of the function htmlentities?
  316. How can we convert the time zones using PHP?
  317. What is meant by urlencode and urldocode?
  318. How can we get the properties (size, type, width, height) of an image using PHP image functions?
  319. What is the maximum size of a file that can be uploaded using PHP and how can we change this?
  320. How can we increase the execution time of a PHP script?
  321. How can we take a backup of a MySQL table and how can we restore it. ?
  322. How can we optimize or increase the speed of a MySQL select query?
  323. How can we destroy the session, how can we unset the variable of a session?
  324. How can we destroy the cookie?
  325. What is the difference between ereg_replace() and eregi_replace()?
  326. What are the different functions in sorting an array?
  327. How can we know the count/number of elements of an array?
  328. What is the PHP predefined variable that tells the What types of images that PHP supports?
  329. How can I know that a variable is a number or not using a JavaScript?
  330. List out some tools through which we can draw E-R diagrams for mysql.
  331. How can I retrieve values from one database server and store them in other database server using PHP?
  332. How can I make a script that can be bi-language (supports English, German)?
  333. How can we repair a MySQL table?
  334. What is the maximum length of a table name, database name, and fieldname in MySQL?
  335. How many values can the SET function of MySQL take?
  336. What are the other commands to know the structure of table using MySQL commands except explain command?
  337. How many tables will create when we create table, what are they?
  338. What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?
  339. Give the syntax of Grant and Revoke commands?
  340. Explain Normalization concept?
  341. How can we find the number of rows in a table using MySQL?
  342. How many ways we can we find the current date using MySQL?
  343. What are the advantages and disadvantages of Cascading Style Sheets?
  344. What is the difference between Primary Key and Unique key?
  345. The structure of table view buyers is as follows:
  346. What is the difference between GROUP BY and ORDER BY in Sql?
  347. What is the difference between char and varchar data types?
  348. How can we know the number of days between two given dates using MySQL?
  349. How can we know the number of days between two given dates using PHP?
  350. Does SQL support programming?
  351. What is oracle database ?
  352. What is schema?
  353. What is a Tablespace?
  354. What is a Control File ?
  355. Define data blocks ?
  356. What is an Extent ?
  357. What is the difference between Client side JavaScript and Server side JavaScript.
  358. Where are cookies actually stored on the hard disk?
  359. What is the difference between a web-garden and a web-farm?
  360. How to Accessing Elements using javascript?
  361. What is the difference between undefined value and null value?
  362. How to set the cursor to wait in JavaScript?
  363. What is decodeURI(), encodeURI() in JavaScript?

Sql Dba interview questions and answers on advance and basic Sql Dba with example so this page for both freshers and experienced condidate. Fill the form below we will send the all interview questions on Sql Dba also add your Questions if any you have to ask and for apply in Sql Dba Tutorials and Training course just send a mail on info@pcds.co.in in detail about your self.

Top Sql Dba interview questions and answers for freshers and experienced

What is Sql Dba ?

Answer :

Questions : 1 :: Which TCP/IP port does SQL Server run on? How can it be changed?

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

Questions : 2 :: What are the difference between clustered and a non-clustered index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index...View answers

Questions : 3 :: What are the different index configurations a table can have?


A table can have one of the following index configurations: No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered...View answers

Questions : 4 :: What are different types of Collation Sensitivity?

Case sensitivity - A and a, B and b, etc. Accent sensitivity Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width...View answers

Questions : 5 :: What is OLTP (Online Transaction Processing)?

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute...View answers

Questions : 6 :: What's the difference between a primary key and a unique key?


Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a...View answers

Questions : 7 :: What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table...View answers

Questions : 8 :: When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to...View answers

Questions : 9 :: What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?


They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY...View answers

Questions : 10 :: What are the properties and different Types of Sub-Queries?

Properties of Sub-Query A sub-query must be enclosed in the parenthesis. A sub-query must be put in the right hand of the comparison operator, and A sub-query cannot contain an ORDER-BY...View answers

Questions : 11 :: What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server...View answers

Questions : 12 :: . What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager...View answers

Questions : 13 :: Which command using Query Analyzer will give you the version of SQL server and operating system?

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

Questions : 14 :: . What is SQL Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease...View answers

Questions : 15 :: Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by...View answers

Questions : 16 :: What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports...View answers

Questions : 17 :: Name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Questions : 18 :: What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers...View answers

Questions : 19 :: What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the...View answers

Questions : 20 :: What is the STUFF function and how does it differ from the REPLACE function?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters...View answers

Questions : 21 :: What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint...View answers

Questions : 22 :: What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key...View answers

Questions : 23 :: What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign...View answers

Questions : 24 :: What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

Questions : 25 :: . What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Questions : 26 :: How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking...View answers

Questions : 27 :: What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity....View answers

Questions : 28 :: What are the advantages of using Stored Procedures?

Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server...View answers

Questions : 29 :: What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers....View answers

Questions : 30 :: Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server...View answers

Questions : 31 :: What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a...View answers

Questions : 32 :: How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into...View answers

Questions : 33 :: What is an execution plan? When would you use it? 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...View answers

Questions : 34 :: What is the difference between inner and outer join? Explain with example.

Inner Join Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based...View answers

Questions : 35 :: What is the difference between JOIN and UNION?

SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this...View answers

Questions : 36 :: What is the difference between UNION and UNION ALL?

UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows,...View answers

Questions : 37 :: What is the difference between WHERE clause and HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for...View answers

Questions : 38 :: What is Self Join and why is it required?

Self Join is the act of joining one table with itself. Self Join is often very useful to convert a hierarchical structure into a flat structure In our employee table example above, we have kept the...View answers

Questions : 39 :: How can we transpose a table using SQL (changing rows to column or vice-versa) ?

The usual way to do it in SQL is to use CASE statement or DECODE statement. How to generate row number in SQL Without ROWNUM Generating a row number – that is a running sequence of numbers...View answers

Questions : 40 :: How to select first 5 records from a table?

This question, often asked in many interviews, does not make any sense to me. The problem here is how do you define which record is first and which is second. Which record is retrieved first from the...View answers

Questions : 41 :: What is the difference between ROWNUM pseudo column and ROW_NUMBER() function?

ROWNUM is a pseudo column present in Oracle database returned result set prior to ORDER BY being evaluated. So ORDER BY ROWNUM does not work. ROW_NUMBER() is an analytical function which is used in...View answers

Questions : 42 :: What are the differences among ROWNUM, RANK and DENSE_RANK?

ROW_NUMBER assigns contiguous, unique numbers from 1.. N to a result set. RANK does not assign unique numbers—nor does it assign contiguous numbers. If two records tie for second place, no...View answers

Questions : 43 :: Why does the selected column have to be in the group by clause or part of an aggregate function?

now you understand how to fix the error – but do you understand why it is a problem in the first place? Well, you should – because that is the most important thing to understand! So,...View answers

Questions : 44 :: 4. What are the different type of SQL's statements ?

  This is one of the frequently asked SQL Interview Questions to freshers. SQL statements are broadly classified into three. They are 1. DDL – Data Definition Language DDL is used to...View answers

Questions : 45 :: What are the Advantages of SQL ?

SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like...View answers

Questions : 46 :: what is a field in a database ?

A field is an area within a record reserved for a specific piece of data. Examples: Employee Name, Employee ID etc

Questions : 47 :: What is a Record in a database ?

A record is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.

Questions : 48 :: What is a Table in a database ?

A table is a collection of records of a specific type. For example, employee table, salary table etc.

Questions : 49 :: What is a database transaction?

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system...View answers

Questions : 50 :: What are properties of a transaction?

Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties. 1. Atomicity A transaction...View answers

Questions : 51 :: What is a Database Lock ?

Database lock tells a transaction, if the data item in questions is currently being used by other transactions.

Questions : 52 :: . What are the type of locks ?

1. Shared Lock When a shared lock is applied on data item, other transactions can only read the item, but can't write into it. 2. Exclusive Lock When an exclusive lock is applied on data item,...View answers

Questions : 53 :: What are the different type of normalization?

In database design, we start with one single table, with all possible columns. A lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by...View answers

Questions : 54 :: What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any...View answers

Questions : 55 :: What is a Composite Key ?

A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.For example - if "Employee_ID" and "Employee Name" in a...View answers

Questions : 56 :: What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, a table which contains composite primary key will be indexed based on the...View answers

Questions : 57 :: What is a Foreign Key ?

When a "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table.For...View answers

Questions : 58 :: What is a Unique Key ?

Unique key is same as primary with the difference being the existence of null. Unique key field allows one value as NULL value.

Questions : 59 :: . Define SQL Insert Statement ?

SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with “insert into “ statement followed by table name and values command, followed by the...View answers

Questions : 60 :: Define SQL Update Statement ?

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET...View answers

Questions : 61 :: Define SQL Delete Statement ?

SQL Delete is used to delete a row or set of rows specified in the filter condition.The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter...View answers

Questions : 62 :: What are wild cards used in database for Pattern Matching ?

SQL Like operator is used for pattern matching. SQL 'Like' command takes more time to process. So before using "like" operator, consider suggestions given below on when and where to use wild card...View answers

Questions : 63 :: Define Join and explain different type of joins?

Another frequently asked SQL Interview Questions on Joins. In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. "Join"...View answers

Questions : 64 :: What is Self-Join?

Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.  

Questions : 65 :: . What is Cross Join?

Cross Join will return all records where each row from the first table is combined with each row from the second table.

Questions : 66 :: What is a view?

The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

Questions : 67 :: What is a materialized view?

Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition. We can index materialized...View answers

Questions : 68 :: . What are the advantages and disadvantages of views in a database?

Advantages: 1. Views don't store data in a physical location. 2. The view can be used to hide some of the columns from the table. 3. Views can provide Access Restriction, since data insertion,...View answers

Questions : 69 :: What is a stored procedure?

Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.

Questions : 70 :: What are the advantages a stored procedure?

Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time...View answers

Questions : 71 :: . What is a trigger?

Database triggers are sets of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.

Questions : 72 :: Explain the difference between DELETE , TRUNCATE and DROP commands?

Once delete operation is performed, Commit and Rollback can be performed to retrieve data.Once the truncate statement is executed, Commit and Rollback statement cannot be performed. Where condition...View answers

Questions : 73 :: What is the difference between Cluster and Non cluster Index?

A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.A non clustered index does not alter the...View answers

Questions : 74 :: What is Union, minus and Interact commands?

MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.

Questions : 75 :: ) What is data control language?

Data control language allows you to control access to the database. It includes two commands GRANT and REVOKE. GRANT: to grant specific user to perform specific task. REVOKE: to cancel previously...View answers

Questions : 76 :: What is data manipulation language?

Data manipulation language makes user able to access and manipulate data. It is used to perform following operations. Insert data into database Retrieve data from the database Update data in the...View answers

Questions : 77 :: What is the difference between clustered and non clustered index in SQL?

There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective. 1) One table can...View answers

Questions : 78 :: What is the SQL query to display current date?

There is a built in function in SQL called GetDate() which is used to return current timestamp.

Questions : 79 :: Which types of join is used in SQL widely?

The knowledge of JOIN is very necessary for an interviewee. Mostly used join is INNER JOIN and (left/right) OUTER JOIN.

Questions : 80 :: What is "TRIGGER" in SQL?

Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table. Actually triggers are special type of stored procedures that are...View answers

Questions : 81 :: What is self join and what is the requirement of self join?

Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.

Questions : 82 :: What are set operators in SQL?

Union, intersect or minus operators are called set operators

Questions : 83 :: What is a constraint? Tell me about its various levels.

Constraints are representators of a column to enforce data entity and consistency. There are two levels : column level constraint table level constraint

Questions : 84 :: Write an SQL query to find names of employee start with 'A'?

SELECT * FROM Employees WHERE EmpName like 'A%' ...View answers

Questions : 85 :: Write an SQL query to get third maximum salary of an employee from a table named employee_table.

Questions : 86 :: What is the difference between DELETE and TRUNCATE statement in SQL?

The main differences between SQL DELETE and TRUNCATE statements are given below: No.DELETETRUNCATE 1) DELETE is a DML command. TRUNCATE is a DDL command. 2) We can use WHERE clause in...View answers

Questions : 87 :: What is ACID property in database?

ACID property is used to ensure that the data transactions are processed reliably in a database system. A single logical operation of a data is called transaction. ACID is an acronym for Atomicity,...View answers

Questions : 88 :: What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in...View answers

Questions : 89 :: What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides...View answers

Questions : 90 :: What is SQL?

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of...View answers

Questions : 91 :: What is a Database?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways. Example:...View answers

Questions : 92 :: What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

Questions : 93 :: What are tables and Fields?

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields...View answers

Questions : 94 :: . What are the types of join and explain each?

  There are various types of join which can be used to retrieve data and it depends on the relationship between tables. Inner join. Inner join return rows when there is at least one match of...View answers

Questions : 95 :: What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in...View answers

Questions : 96 :: What is Denormalization.

  DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the...View answers

Questions : 97 :: What are all the different normalizations?

The normal forms can be divided into 5 forms, and they are explained below

Questions : 98 :: First Normal Form (1NF)

This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns

Questions : 99 :: Second Normal Form (2NF):.

Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.

Questions : 100 :: Third Normal Form (3NF):.

This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.

Questions : 101 :: Fourth Normal Form (3NF):.

Meeting all the requirements of third normal form and it should not have multi- valued dependencies.

Questions : 102 :: What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

Questions : 103 :: What are all the different types of indexes?

There are three types of indexes -. Unique Index. This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when...View answers

Questions : 104 :: What is a Cursor?

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing...View answers

Questions : 105 :: What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:. One to One Relationship. One to Many...View answers

Questions : 106 :: What is subquery?

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to...View answers

Questions : 107 :: What are the types of subquery?

There are two types of subquery – Correlated and Non-Correlated. A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the...View answers

Questions : 108 :: . What is the difference between DELETE and TRUNCATE commands?

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement. TRUNCATE removes...View answers

Questions : 109 :: What are local and global variables and their differences?

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created...View answers

Questions : 110 :: What is a constraint?

Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are. NOT...View answers

Questions : 111 :: What is data Integrity?

Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the...View answers

Questions : 112 :: What is Auto Increment?

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be...View answers

Questions : 113 :: What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data...View answers

Questions : 114 :: . What is user defined functions?

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever...View answers

Questions : 115 :: What are all types of user defined functions?

Three types of user defined functions are. Scalar Functions. Inline Table valued functions. Multi statement valued functions.

Questions : 116 :: What is collation?

Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the...View answers

Questions : 117 :: What are all different types of collation sensitivity?

Following are different types of collation sensitivity -. Case Sensitivity – A and a and B and b. Accent Sensitivity. Kana Sensitivity – Japanese Kana characters. Width Sensitivity...View answers

Questions : 118 :: Advantages and Disadvantages of Stored Procedure?

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple...View answers

Questions : 119 :: What is Online Transaction Processing (OLTP)?

Online Transaction Processing or OLTP manages transaction based applications which can be used for data entry and easy retrieval processing of data. This processing makes like easier on simplicity...View answers

Questions : 120 :: . What is CLAUSE?

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

Questions : 121 :: What is recursive stored procedure?

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

Questions : 122 :: What is an ALIAS command?

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column. Example-.   Select st.StudentID, Ex.Result from student st, Exam...View answers

Questions : 123 :: What is the difference between TRUNCATE and DROP statements?

TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.

Questions : 124 :: What are aggregate and scalar functions?

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

Questions : 125 :: How can you create an empty table from an existing table?

Select *into studentcopy from student where1=2   Here, we are copying student table to another table with the same structure with no rows...View answers

Questions : 126 :: How to fetch common records from two tables?

Common records result set can be achieved by -.   Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam 1 Select studentID...View answers

Questions : 127 :: . How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers -. To display even numbers-. MySQL Select studentId from (Select rowno, studentId from student) where...View answers

Questions : 128 :: How to select unique records from a table?

Select unique records from a table by using DISTINCT keyword.   Select DISTINCT StudentID, StudentName from Student. 1 Select DISTINCT StudentID,StudentName from...View answers

Questions : 129 :: What is the command used to fetch first 5 characters of the string?

There are many ways to fetch first 5 characters of the string -. MySQL Select SUBSTRING(StudentName,1,5) as studentname from...View answers

Questions : 130 :: . Which operator is used in query for pattern matching?

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

Questions : 131 :: SELECT statements:

SELECT column_name(s) FROM table_name SELECT DISTINCT column_name(s) FROM table_name SELECT column FROM table WHERE column operator value SELECT column FROM table WHERE column LIKE pattern SELECT...View answers

Questions : 132 :: The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value

Questions : 133 :: The INSERT INTO Statements:

INSERT INTO table_name VALUES (value1, value2,....) INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

Questions : 134 :: The Update Statement:

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

Questions : 135 :: The Delete Statements:

DELETE FROM table_name WHERE column_name = some_value Delete All Rows: DELETE FROM table_name or DELETE * FROM table_name

Questions : 136 :: What Is mSQL?

Mini SQL (mSQL) is a light weight relational database management system capable of providing rapid access to your data with very little overhead. mSQL is developed by Hughes Technologies Pty Ltd....View answers

Questions : 137 :: What Is Table?

A table is a data storage structure with rows and columns.

Questions : 138 :: What Is Column?

A column defines one piece of data stored in all rows of the table.

Questions : 139 :: What Is Row?

A row is a unit of data with related data items stored as one item in one column in a table.

Questions : 140 :: How many TRIGGERS are allowed in MySql table?

MySql table allows following 6 triggers: -BEFORE INSERT -AFTER INSERT -BEFORE UPDATE -AFTER UPDATE -BEFORE DELETE and -AFTER DELETE

Questions : 141 :: Differentiate between FLOAT and DOUBLE.

FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

Questions : 142 :: Tell us something about Heap tables.

- HEAP tables are found in memory. - They are used for high speed storage on temporary basis.   Some of their characteristics are: - They do not allow BLOB or TEXT fields. - Only comparison...View answers

Questions : 143 :: How do you control the max size of a HEAP table?

 Maximum size of Heap table can be controlled using MySQL config variable called max_heap_table_size.

Questions : 144 :: What are the advantages of MySQL in comparison to Oracle?

- MySQL is open source software available at zero cost. - It is portable - GUI with command prompt.- Administration is supported by MySQL Query Browser

Questions : 145 :: What does myisamchk do?

- It compresses the MyISAM tables, which reduces their disk or memory usage. How can we convert between Unix & MySQL timestamps? - MySQL timestamp can be converted into Unix timestamp using the...View answers

Questions : 146 :: What is BLOB?

- BLOB stands for binary large object. - It that can hold a variable amount of data. There are four types of BLOB based on the maximum length of values they can hold:- TINYBLOB- BLOB- MEDIUMBLOB -...View answers

Questions : 147 :: What is TEXT?

TEXT is case-insensitive BLOB. The four types of TEXT are: - TINYTEXT- TEXT- MEDIUMTEXT- LONGTEXT

Questions : 148 :: What is the difference between BLOB and TEXT?

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values - In TEXT types sorting and comparison is performed case-insensitive.

Questions : 149 :: How is MyISAM table stored?

MyISAM table is stored on disk in three formats. - ‘.frm’ file – storing the table definition- ‘.MYD’ (MYData) - data file - ‘.MYI’ (MYIndex) – index...View answers

Questions : 150 :: Explain advantages of MyISAM over InnoDB?

- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required. - InnoDB stores the...View answers

Questions : 151 :: How would concatenate strings in MySQL?

With the use of - CONCAT (string1, string2, string3)

Questions : 152 :: How would you get the current date in Mysql?

By using SELECT CURRENT_DATE();

Questions : 153 :: How would you enter Characters as HEX Numbers?

- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X)- Alternatively, just prefix HEX numbers with (Ox).

Questions : 154 :: How are MySQL timestamps seen to a user?

- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.

Questions : 155 :: Explain about MySQL and its features.

MySQL is a relational database management system which is an open source database. Features: Because of its unique storage engine architecture MySQL performance is very high.  Supports...View answers

Questions : 156 :: What are the disadvantages of MySQL?

MySQL does not support a very large database size as efficiently MySQL does not support ROLE, COMMIT, and Stored procedures in versions less than 5.0 Transactions are not handled very...View answers

Questions : 157 :: What are the security recommendations while using MySQL?

Access to the user table should never be given to avoid SQL injection attacks. Privileges such as GRANT and REVOKE must be made use of. SHOW GRANTS can be used to see the list of users who have...View answers

Questions : 158 :: What are MyISAM tables?

In MySQL MyISAM is the default storage engine. MyISAM tables store data values with the low byte first. Even though MyISAM tables are very reliable, corrupted tables can be expected if there is a...View answers

Questions : 159 :: Explain the difference between MyISAM Static and MyISAM Dynamic.

MyISAM Static tables have fields of fixed width while the MyISAM Dynamic can accommodate variable lengths such as TEXT, BLOB etc. MyISAM allows easier restoration of data as compared to MyISAM...View answers

Questions : 160 :: Explain advantages of InnoDB over MyISAM.

InnoDb supports locking of rows while MyISAM supports only table locking. Data integrity is more in InnoDB. Transactional nature of InnoDB enables easy and online...View answers

Questions : 161 :: How to use myisamchk to check or repair MyISAM tables?

Myisamchk gets is used to fetch information about the database tables. It is also used to check, repair and optimize the tables. From shell, Myisamchk can be invoked by typing: Shell> myisamchk...View answers

Questions : 162 :: How to Check MyISAM Tables for Errors?

Using the –myisamchk table_name command can be used to find all errors. it cannot find corruption that involves only the data file. The different options are: Myisamchk –m table_name...View answers

Questions : 163 :: Explain the options of myisamchk to improve the performance of a table.

Myisamchk has a couple of options to optimize a table. Using myisamchk –r table_name, runs myisamchk in recovery mode. This option combines the fragmented rows and gets rid of wasted...View answers

Questions : 164 :: Discuss about MyISAM Key Cache.

MyISAM keeps a key cache to minimize disk I/O. it keeps the most frequently accessed table blocks in memory. For most frequently accessed index blocks, key cache is used. The key cache is not used...View answers

Questions : 165 :: Discuss about MyISAM Index Statistics Collection.

MyISAM’s Index statistics collection is based on a set of rows with the same key prefix value. Here, these set of rows are called as value group. The statistics about the tables is used by the...View answers

Questions : 166 :: What is HEAP table?

Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory...View answers

Questions : 167 :: What is Query Cache in MySQL?

Query Cache in MySQL is used in scenarios when the same queries need to be executed on the same data set. These queries also return the same result. Query cache is most useful when there are tables...View answers

Questions : 168 :: How is Exception Handling handled in MySQL?

Exception handling means changing the usual expected flow of the code. This needs to be done to avoid errors. The exceptions in MySql can be handled using the following syntax: DECLARE handler_type...View answers

Questions : 169 :: What are the Performance and Scalability characteristics of MySQL?

MySQL has a unique storage engine architecture that makes it adaptable to most servers. MySQL meets the expectations of the most common applications, i.e. to run millions of transactions. Features...View answers

Questions : 170 :: What are the limitations of mysql in Comparison of Oracle? MySQL vs. Oracle.

Transactions are better supported in Oracle as compared to Mysql. ROLE feature is available in Oracle and not in MySQL in versions less than 5.0 Large Database size is supported more in Oracle in...View answers

Questions : 171 :: What is a Trigger in MySQL? Define different types of Trigger

A trigger is a set of code which is executed in response to some event. E.g Update employee_perfomance table when a new task is inserted in task table. Here, the trigger is “update” and...View answers

Questions : 172 :: What is the difference between CHAR_LENGTH and LENGTH?

CHAR_LENGTH includes leading and trailing blanks and the string-termination character LENGTH excludes trailing blanks and the string-termination character. It does not exclude leading blanks....View answers

Questions : 173 :: Explain the difference between BOOL, TINYINT and BIT.

BIT data type can store up to 8 bytes from My SQL version 5.0.3 BIT data type can be used only for binary data

Questions : 174 :: What are ENUMs used for in MySQL?

ENUM is used to limit the values that persist in the table. The following example illustrates the best use of ENUM. CREATE TABLE months (month ENUM ‘January’, ‘February’,...View answers

Questions : 175 :: Explain MySQL Aggregate Functions.

Aggregate functions in MySQL are a group of functions that are used to operate on a set of values. These functions ignore NULL values unless specified. Functions like AVG(), MIN(), MAX(), COUNT() etc...View answers

Questions : 176 :: Describe Transaction-Safe Table Types in MySQL

While using transactions in MySQL a transaction –safe table type must be used. MyISAM is the default table type. Other transaction-safe table types are InnoDB and BerkeleyDB. MyISAM is much...View answers

Questions : 177 :: Describe MySQL Connection using mysql binary.

Establishing connection to MySQL database using Mysql binary can be done at command prompt. Mysql –u user_name –p On entering the user name and password the mysql command prompt will be...View answers

Questions : 178 :: MySQL - Stored Procedures and Triggers

When multiple applications need to perform common database operations, stored procedures can be used. It is a set of sql statements stored on the server. It has a name, parameters and some sql...View answers

Questions : 179 :: Primary Keys and Auto Increment Fields in MySQL

Primary Key: A primary key is used to uniquely identify a row in a table. A table can have only one primary key. The values in a single column to store primary key are unique. More one column can be...View answers

Questions : 180 :: COMMIT and ROLLBACK in MySQL

COMMIT and ROLLBACK in MySQL COMMIT: Commit statement commits the current transaction, which means making the changes permanent. A transaction may involve update and or delete and or insert...View answers

Questions : 181 :: How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

Questions : 182 :: What Is Index?

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

Questions : 183 :: What Is View?

A view is a logical table defined by a query statement.

Questions : 184 :: What Is Union?

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

Questions : 185 :: What Is ISAM?

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Questions : 186 :: What Is MyISAM?

MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval....View answers

Questions : 187 :: What Is InnoDB?

InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

Questions : 188 :: What Is BDB (BerkeleyDB)?

BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).

Questions : 189 :: What Is CSV?

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

Questions : 190 :: What Is Transaction?

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements...View answers

Questions : 191 :: What Is Commit?

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Questions : 192 :: What Is Rollback?

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

Questions : 193 :: Explain what Is MySQL?

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. MySQL has the following main features: * Works on many different platforms. * APIs for C, C++, Eiffel,...View answers

Questions : 194 :: How To Install MySQL?

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. You can download a copy and install it on your local computer very easily. Here is how you can do this:...View answers

Questions : 195 :: How To Start MySQL Server?

If you want to start the MySQL server, you can run the "mysqld" program in a command window as shown in the following tutorial: >cd mysqlbin >mysqld "mysqld" will run quietly without...View answers

Questions : 196 :: How Do You Know If Your MySQL Server Is Alive?

If you want to know whether your MySQL server is alive, you can use the "mysqladmin" program in a command window as shown in the following tutorial: >cd mysqlbin >mysqladmin -u root ping...View answers

Questions : 197 :: How Do You Know the Version of Your MySQL Server?

If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial: >cd mysqlbin >mysqladmin -u root...View answers

Questions : 198 :: How To Create a Test Table in Your MySQL Server?

If you want to create a wbtest table in your MySQL server, you can use the "mysql" program in a command window as shown in the following tutorial: >cd mysqlbin >mysql -u root Welcome to the...View answers

Questions : 199 :: How To Shutdown MySQL Server?

If you want to shutdown your MySQL server, you can run the "mysqladmin" program in a command window as shown in the following tutorial: >cd mysqlbin >mysqladmin...View answers

Questions : 200 :: What Tools Available for Managing MySQL Server?

MySQL comes with the following programs as administration tools for you to manage your MySQL server: ► mysqld - MySQL server daemon. You can use "mysqld" to start your MySQL server. ►...View answers

Questions : 201 :: What Is "mysqld"?

"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld"...View answers

Questions : 202 :: What Is "mysqladmin" in MySQL?

"mysqladmin" is a command-line interface for administrators to perform server administration tasks. It support a number of commonly used commands like: ► "mysqladmin shutdown" - Shuts down...View answers

Questions : 203 :: How To Check Server Status with "mysqladmin"?

If you want to check the server status by with "mysqladmin", you can following this tutorial example: >cd mysqlbin >mysqladmin -u root status Uptime: 223 Threads: 1 Questions: 1 Slow...View answers

Questions : 204 :: How To Shut Down the Server with "mysqladmin"?

If you want to shut down the server with "mysqladmin", you can use the command "mysqladmin shutdown" as shown in the following tutorial example: >cd mysqlbin >mysqladmin -u root shutdown...View answers

Questions : 205 :: How To Use "mysql" to Run SQL Statements?

If you want to run SQL statement to your server with "mysql", you need to start "mysql" and enter your SQL statement at the "mysql" prompt. Here is a good tutorial exercise that shows you how...View answers

Questions : 206 :: How To Show All Tables with "mysql"?

If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example: >cd mysqlbin >mysql -u...View answers

Questions : 207 :: What Is "mysqlcheck"?

"mysqlcheck" is a command-line interface for administrators to check and repair tables. Here are some sample commands supported by "mysqlcheck": ► "mysqlcheck databaseName tableName" -...View answers

Questions : 208 :: How To Analyze Tables with "mysqlcheck"?

If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database: >cd mysqlbin...View answers

Questions : 209 :: What Is "mysqlshow"?

"mysqlshow" is a command-line interface for end users to see information on tables and columns. Here are some sample commands supported by "mysqlshow": ► "mysqlshow" - Shows all the...View answers

Questions : 210 :: What Is "mysqldump"?

"mysqldump" - A command-line interface for administrators or end users to export data from the server to files. Here are some sample commands supported by "mysqldump": ► "mysqldump...View answers

Questions : 211 :: How To Dump a Table to a File with "mysqldump"?

If you want to dump all rows in a table from the server to a file, you can use "mysqldump" with the "-f fileName" option as show in the following tutorial exercise: >cd mysqlbin >mysqldump...View answers

Questions : 212 :: What Is "mysqlimport"?

"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by...View answers

Questions : 213 :: How To Load Data Files into Tables with "mysqlimport"?

If you want to load a data file directly into a table, you need to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the...View answers

Questions : 214 :: What Is the Command Line End User Interface - mysql?

"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features: * "mysql" is command line...View answers

Questions : 215 :: What Are the "mysql" Command Line Options?

"mysql" offers a big list of command line options. Here are some commonly used options: ► "-?" - Displays a help message on how to use "mysql" and terminates the program. ► "-u...View answers

Questions : 216 :: What Are the "mysql" Command Line Arguments?

"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good...View answers

Questions : 217 :: How Many SQL DDL Commands Are Supported by "mysql"?

There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions: ► "CREATE dataObjectType dataObjectName" - Creates new...View answers

Questions : 218 :: How Many SQL DML Commands Are Supported by "mysql"?

There are 4 SQL Data Manipulation Language (DML) commands that are supported by "mysql". They are listed below with short descriptions: * "INSERT INTO tableName ..." - Inserts new data rows into...View answers

Questions : 219 :: What Are the Non-Standard SQL Commands Supported by "mysql"?

There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands: ► "SHOW infoName" - Shows basic information of based on the...View answers

Questions : 220 :: How To Get Help Information from the Server?

While you are at the "mysql>" prompt, you can get help information from the server by using the "HELP" command. The tutorial exercise below shows several examples: >cd mysqlbin >mysql...View answers

Questions : 221 :: How To Run "mysql" Commands from a Batch File?

If you have group of "mysql" commands that need to be executed repeatedly, you can put them into a file, and run them from the file in "mysql" batch mode. Here is a batch file, templinks.sql,...View answers

Questions : 222 :: How To Return Query Output in HTML Format?

By default, "mysql" returns query output in text table format. If you want to receive query output in HTML format, you need to use the "-H" command option. Here is a good tutorial...View answers

Questions : 223 :: What Is SQL in MySQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

Questions : 224 :: How Many Groups of Data Types?

MySQL support 3 groups of data types as listed below: ► String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB,...View answers

Questions : 225 :: What Are String Data Types?

MySQL supports the following string data types: ► CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings. Default character set is ASCII. ► NCHAR(n) same as...View answers

Questions : 226 :: What Are the Differences between CHAR and NCHAR?

Both CHAR and NCHAR are fixed length string data types. But they have the following differences: ► CHAR's full name is CHARACTER. ► NCHAR's full name is NATIONAL CHARACTER. ► By...View answers

Questions : 227 :: What Are the Differences between CHAR and VARCHAR?

CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences: ► CHAR stores values in fixed lengths. Values are padded with space characters...View answers

Questions : 228 :: What Are the Differences between BINARY and VARBINARY?

. Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences: ► BINARY stores values in fixed lengths. Values are padded with 0x00. ►...View answers

Questions : 229 :: What Are Numeric Data Types?

MySQL supports the following numeric data types: ► BIT(n) - An integer with n bits. ► BOOL same as BOOLEAN - Boolean values stored in 1 bit. ► TINYINT - A small integer stored in 1...View answers

Questions : 230 :: What Are Date and Time Data Types?

MySQL supports the following date and time data types: ► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD". ► DATETIME - A date with...View answers

Questions : 231 :: How To Calculate Expressions with SQL Statements?

There is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in...View answers

Questions : 232 :: How To Include Comments in SQL Statements?

If you want to include comments in a SQL statement, you can first enter "--", then enter your comment until the end of the line. The tutorial exercise below shows you some good examples: SELECT...View answers

Questions : 233 :: How To Include Character Strings in SQL statements?

If you want to include character strings in your SQL statements, you need to quote them in one of the following formats: ► Using single quotes. For example 'GlobalGuideLine.com'. ►...View answers

Questions : 234 :: How To Escape Special Characters in SQL statements?

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules: ► The escape...View answers

Questions : 235 :: How To Concatenate Two Character Strings?

If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples: SELECT CONCAT('Welcome',' to') FROM DUAL; Welcome to SELECT...View answers

Questions : 236 :: How To Include Numeric Values in SQL statements?

If you want to include a numeric value in your SQL statement, you can enter it directly as shown in the following examples: SELECT 255 FROM DUAL; -- An integer 255 SELECT -6.34 FROM DUAL; -- A...View answers

Questions : 237 :: How To Enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically...View answers

Questions : 238 :: How To Enter Numeric Values as HEX Numbers?

If you want to enter numeric values as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically...View answers

Questions : 239 :: How To Enter Binary Numbers in SQL Statements?

If you want to enter character strings or numeric values as binary numbers, you can quote binary numbers with single quotes and a prefix of (B), or just prefix binary numbers with (0b). Binary...View answers

Questions : 240 :: What Are NULL Values?

NULL is a special value that represents no value. Here are basic rules about NULL values: ► NULL presents no value. ► NULL is not the same as an empty string ''. ► NULL is not...View answers

Questions : 241 :: What Happens If NULL Values Are Involved in Expressions?

If NULL values are used in expressions, the resulting values will be NULL values. In other words: ► Arithmetic expressions with NULL values result NULL values. ► Comparison expressions...View answers

Questions : 242 :: How To Convert Numeric Values to Character Strings?

You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples: SELECT CAST(4123.45700 AS CHAR) FROM DUAL; 4123.45700 -- How to get...View answers

Questions : 243 :: How To Convert Character Strings to Numeric Values?

You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples: SELECT CAST('4123.45700' AS...View answers

Questions : 244 :: How To Use IN Conditions?

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are given in the tutorial exercise below:...View answers

Questions : 245 :: How To Use LIKE Conditions?

A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition: * '_' is used in the pattern to match any one character. * '%' is used in the pattern to...View answers

Questions : 246 :: How To Use Regular Expression in Pattern Match Conditions?

If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples: SELECT...View answers

Questions : 247 :: How To Use CASE Expression?

There are 2 ways to use the CASE expression. The first way is to return one of the predefined values based on the comparison of a given value to a list of target values. The second way is to return...View answers

Questions : 248 :: What Are Date and Time Data Types in MySQL?

MySQL supports the following date and time data types: ► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD". ► DATETIME - A date with...View answers

Questions : 249 :: How To Write Date and Time Literals?

MySQL offers a number of formats for you to use to enter date and time literals: ► ANSI standard format: "YYYY-MM-DD HH:MM:SS". ► Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS"...View answers

Questions : 250 :: How To Convert Dates to Character Strings?

You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes: ► %a Abbreviated weekday name (Sun..Sat) ►...View answers

Questions : 251 :: How To Convert Character Strings to Dates?

. If you have a character string that represents a date, and you want to convert it into a date value, you can use the STR_TO_DATE(string, format) function. STR_TO_DATE() shares the same formatting...View answers

Questions : 252 :: What Are Date and Time Intervals?

A date and time interval is special value to be used to increment or decrement a date or a time at a given date or time unit. A data and time interval should be expression in the format of "INTERVAL...View answers

Questions : 253 :: How To Increment Dates by 1 in MySQL?

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as "date + INTERVAL 1 DAY". The...View answers

Questions : 254 :: How To Decrement Dates by 1 in MySQL?

If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1...View answers

Questions : 255 :: How To Calculate the Difference between Two Dates?

If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below: SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))...View answers

Questions : 256 :: How To Present a Past Time in Hours, Minutes and Seconds?

If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise: SELECT TIMEDIFF(NOW(),...View answers

Questions : 257 :: How To Extract a Unit Value from a Date and Time?

If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:...View answers

Questions : 258 :: What Are Date and Time Functions in MySQL?

MySQL offers a number of functions for date and time values: ► ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD(). ► ADDTIME(time1, time2) - Adding two time...View answers

Questions : 259 :: What Is TIMESTAMP in MySQL?

A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column: ► The first TIMESTAMP column in a table...View answers

Questions : 260 :: How Many Ways to Get the Current Time?

There are 8 ways to get the current time: SELECT NOW() FROM DUAL; 2006-07-01 10:02:41 SELECT CURRENT_TIME() FROM DUAL; 10:02:58 SELECT SYSDATE() FROM DUAL; 2006-07-01 10:03:21 mysql> SELECT...View answers

Questions : 261 :: What Are DDL Statements in MySQL?

DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements: ► CREATE - Creating a new database object. ►...View answers

Questions : 262 :: How To Create a New Table in MySQL?

If you want to create a new table, you can use the "CREATE TABLE" statement. The following tutorial script shows you how to create a table called "tip": mysql> CREATE TABLE tip (id INTEGER...View answers

Questions : 263 :: What Happens If You No CREATE Privilege in a Database?

In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise: >cd...View answers

Questions : 264 :: How To Get a List of All Tables in a Database?

If you want to see the table you have just created, you can use the "SHOW TABLES" command to get a list of all tables in database. The tutorial script gives you a good example: mysql> SHOW...View answers

Questions : 265 :: How To Get a List of Columns in an Existing Table?

mysql> SHOW COLUMNS FROM test;

Questions : 266 :: How To See the CREATE TABLE Statement of an Existing Table?

SHOW CREATE TABLE test;

Questions : 267 :: How To Create a New Table by Selecting Rows from Another Table in MySQL?

Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The...View answers

Questions : 268 :: . How To Add a New Column to an Existing Table in MySQL?

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good...View answers

Questions : 269 :: How To Delete an Existing Column in a Table?

If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing...View answers

Questions : 270 :: How To Rename an Existing Column in a Table?

If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and...View answers

Questions : 271 :: How To Rename an Existing Table in MySQL?

If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example: mysql> ALTER TABLE test RENAME TO wb; Query...View answers

Questions : 272 :: How To Drop an Existing Table in MySQL?

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below: mysql> DROP TABLE tipBackup; Query OK, 0 rows affected...View answers

Questions : 273 :: How To Create a Table Index in MySQL?

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add...View answers

Questions : 274 :: How To Get a List of Indexes of an Existing Table?

If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script...View answers

Questions : 275 :: How To Drop an Existing Index in MySQL?

If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script: mysql> DROP INDEX wb_subject ON test;...View answers

Questions : 276 :: How To Create a New View in MySQL?

You can create a new view based on one or more existing tables by using the "CREATE VIEW viewName AS selectStatement" statement as shown in the following script: mysql> CREATE TABLE comment...View answers

Questions : 277 :: How To Drop an Existing View in MySQL?

If you have an existing view, and you don't want it anymore, you can delete it by using the "DROP VIEW viewName" statement as shown in the following script: mysql> DROP VIEW faqComment; Query...View answers

Questions : 278 :: What are the drivers in MySQL?

Following are the drivers available in MySQL: PHP Driver JDBC Driver ODBC Driver C WRAPPER PYTHON Driver PERL Driver RUBY Driver CAP11PHP...View answers

Questions : 279 :: How do you login to MySql using Unix shell?

We can login through this command: # [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

Questions : 280 :: What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

Questions : 281 :: How can you see all indexes defined for a table?

Indexes are defined for the table by: SHOW INDEX FROM <tablename>;

Questions : 282 :: Is Mysql query is case sensitive?

No.

Questions : 283 :: What is the difference between mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object: mysql_fetch_array() -Returns a result row as an associated array or a regular array from...View answers

Questions : 284 :: What are the different tables present in MySQL?

Total 5 types of tables are present: MyISAM Heap Merge INNO DB ISAM MyISAM is the default storage engine as of MySQL .

Questions : 285 :: How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause. SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a; 1 SELECT DISTINCT t1.aFROM t1,t2...View answers

Questions : 286 :: How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

Questions : 287 :: What is the different between NOW() and CURRENT_DATE()?

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

Questions : 288 :: What are the nonstandard string types?

Following are Non-Standard string types: TINYTEXT TEXT MEDIUMTEXT LONGTEXT

Questions : 289 :: What are all the Common SQL Function?

CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field. FORMAT(X, D) – Formats the number X to D...View answers

Questions : 290 :: Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL server’s security model and it helps in troubleshooting problems like...View answers

Questions : 291 :: What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?

It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

Questions : 292 :: What are the differences between Get and post methods in form submitting. give the case where we can use get and we can use post methods?

  When to use GET or POST The HTML 2.0 specification says, in section Form Submission (and the HTML 4.0 specification repeats this with minor stylistic changes): –>If the...View answers

Questions : 293 :: Who is the father of PHP and explain the changes in PHP versions?

Rasmus Lerdorf is known as the father of PHP.PHP/FI 2.0 is an early and no longer supported version of PHP. PHP 3 is the successor to PHP/FI 2.0 and is a lot nicer. PHP 4 is the current generation of...View answers

Questions : 294 :: How can we submit a form without a submit button?

The main idea behind this is to use Java script submit() function in order to submit the form without explicitly clicking any submit button. You can attach the document.formname.submit() method to...View answers

Questions : 295 :: In how many ways we can retrieve the data in the result set of MySQL using PHP?

You can do it by 4 Ways1. mysql_fetch_row. 2. mysql_fetch_array 3. mysql_fetch_object 4. mysql_fetch_assoc

Questions : 296 :: What is the difference between mysql_fetch_object and mysql_fetch_array?

mysql_fetch_object() is similar tomysql_fetch_array(), with one difference – an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field...View answers

Questions : 297 :: What is the difference between $message and $$message?

It is a classic example of PHP’s variable variables. take the following example.$message = “Mizan”;$$message = “is a moderator of PHPXperts.”;$message is a simple PHP...View answers

Questions : 298 :: How can we extract string 'abc.com ' from a string 'http://info@abc.com' using regular expression of PHP?

preg_match(“/^http://.+@(.+)$/”,’http://info@abc.com&#8217;,$found); echo $found[1];

Questions : 299 :: How can we create a database using PHP and MySQL?

We can create MySQL database with the use of mysql_create_db(“Database Name”)

Questions : 300 :: What are the differences between require and include, include_once and require_once?

The include() statement includes and evaluates the specified file.The documentation below also applies to require(). The two constructs are identical in every way except how they handle failure....View answers

Questions : 301 :: Can we use include ("abc.PHP") two times in a PHP page "makeit.PHP"?

Yes we can use include() more than one time in any page though it is not a very good practice.

Questions : 302 :: What are the different tables present in MySQL, which type of table is generated when we are creating a table in the following syntax: create table employee (eno int(2),ename varchar(10)) ?

Total 5 types of tables we can create 1. MyISAM 2. Heap 3. Merge 4. INNO DB 5. ISAM MyISAM is the default storage engine as of MySQL 3.23 and as a result if we do not specify the table name...View answers

Questions : 303 :: Suppose your Zend engine supports the mode Then how can u configure your PHP Zend engine to support mode ?

In php.ini file: set short_open_tag=on to make PHP support

Questions : 304 :: Shopping cart online validation i.e. how can we configure Paypal, etc.?

We can find the detail documentation about different paypal integration process at the following site PayPal PHP SDK :...View answers

Questions : 305 :: What is meant by nl2br()?

Inserts HTML line breaks (<BR />) before all newlines in a string string nl2br (string); Returns string with ” inserted before all newlines. For example: echo nl2br(“god blessn...View answers

Questions : 306 :: Draw the architecture of Zend engine?

The Zend Engine is the internal compiler and runtime engine used by PHP4. Developed by Zeev Suraski and Andi Gutmans, the Zend Engine is an abbreviation of their names. In the early days of PHP4, it...View answers

Questions : 307 :: What are the current versions of apache, PHP, and MySQL?

As of February, 2007 the current versions arePHP: php5.2.1 MySQL: MySQL 5.2 Apache: Apache 2.2.4Note: visit www.php.net, http://dev.mysql.com/downloads/mysql/, www.apache.org to get current...View answers

Questions : 308 :: What are the reasons for selecting lamp (Linux, apache, MySQL, PHP) instead of combination of other software programs, servers and operating systems?

All of those are open source resource. Security of Linux is very very more than windows. Apache is a better server that IIS both in functionality and security. MySQL is world most popular open source...View answers

Questions : 309 :: How can we encrypt and decrypt a data present in a MySQL table using MySQL?

AES_ENCRYPT () and AES_DECRYPT ()

Questions : 310 :: What are the features and advantages of object-oriented programming?

One of the main advantages of OO programming is its ease of modification; objects can easily be modified and added to a system there by reducing maintenance costs. OO programming is also considered...View answers

Questions : 311 :: What are the differences between procedure-oriented languages and object-oriented languages?

Traditional programming has the following characteristics:Functions are written sequentially, so that a change in programming can affect any code that follows it. If a function is used multiple times...View answers

Questions : 312 :: What is the use of friend function?

Sometimes a function is best shared among a number of different classes. Such functions can be declared either as member functions of one class or as global functions. In either case they can be set...View answers

Questions : 313 :: What are the differences between public, private, protected, static, transient, final and volatile?

Public: Public declared items can be accessed everywhere. Protected: Protected limits access to inherited and parent classes (and to the class that defines the item). Private: Private limits...View answers

Questions : 314 :: How can we convert asp pages to PHP pages?

there are lots of tools available for asp to PHP conversion. you can search Google for that. the best one is available...View answers

Questions : 315 :: What is the functionality of the function htmlentities?

Convert all applicable characters to HTML entities This function is identical to htmlspecialchars() in all ways, except with htmlentities(), all characters which have HTML character entity...View answers

Questions : 316 :: How can we convert the time zones using PHP?

By using date_default_timezone_get and date_default_timezone_set function on PHP 5.1.0 <?php // Discover what 8am in Tokyo relates to on the East Coast of the US // Set the default...View answers

Questions : 317 :: What is meant by urlencode and urldocode?

URLencode returns a string in which all non-alphanumeric characters except -_. have been replaced with a percent (%) sign followed by two hex digits and spaces encoded as plus (+) signs. It is...View answers

Questions : 318 :: How can we get the properties (size, type, width, height) of an image using PHP image functions?

To know the Image type use exif_imagetype () function To know the Image size use getimagesize () function To know the image width use imagesx () function To know the image height use imagesy()...View answers

Questions : 319 :: What is the maximum size of a file that can be uploaded using PHP and how can we change this?

By default the maximum size is 2MB. and we can change the following setup at php.iniupload_max_filesize = 2M

Questions : 320 :: How can we increase the execution time of a PHP script?

by changing the following setup at php.inimax_execution_time = 30 ; Maximum execution time of each script, in seconds

Questions : 321 :: How can we take a backup of a MySQL table and how can we restore it. ?

To backup: BACKUP TABLE tbl_name[,tbl_name…] TO ‘/path/to/backup/directory’ RESTORE TABLE tbl_name[,tbl_name…] FROM ‘/path/to/backup/directory’mysqldump: Dumping...View answers

Questions : 322 :: How can we optimize or increase the speed of a MySQL select query?

first of all instead of using select * from table1, use select column1, column2, column3.. from table1 Look for the opportunity to introduce index in the table you are querying. use limit keyword...View answers

Questions : 323 :: How can we destroy the session, how can we unset the variable of a session?

session_unregister — Unregister a global variable from the current session session_unset — Free all session variables

Questions : 324 :: How can we destroy the cookie?

Set the cookie in past.

Questions : 325 :: What is the difference between ereg_replace() and eregi_replace()?

eregi_replace() function is identical to ereg_replace() except that this ignores case distinction when matching alphabetic characters.eregi_replace() function is identical to ereg_replace() except...View answers

Questions : 326 :: What are the different functions in sorting an array?

  Sort(), arsort(), asort(), ksort(), natsort(), natcasesort(), rsort(), usort(), array_multisort(), and...View answers

Questions : 327 :: How can we know the count/number of elements of an array?

2 ways a) sizeof($urarray) This function is an alias of count() b) count($urarray)

Questions : 328 :: What is the PHP predefined variable that tells the What types of images that PHP supports?

Though i am not sure if this is wrong or not, With the exif extension you are able to work with image meta data.

Questions : 329 :: How can I know that a variable is a number or not using a JavaScript?

bool is_numeric ( mixed var) Returns TRUE if var is a number or a numeric string, FALSE otherwise.or use isNaN(mixed var)The isNaN() function is used to check if a value is not a...View answers

Questions : 330 :: List out some tools through which we can draw E-R diagrams for mysql.

Case Studio Smart Draw

Questions : 331 :: How can I retrieve values from one database server and store them in other database server using PHP?

we can always fetch from one database and rewrite to another. here is a nice solution of it.$db1 = mysql_connect(“host”,”user”,”pwd”)...View answers

Questions : 332 :: How can I make a script that can be bi-language (supports English, German)?

You can maintain two separate language file for each of the language. all the labels are putted in both language files as variables and assign those variables in the PHP source. on runtime choose the...View answers

Questions : 333 :: How can we repair a MySQL table?

The syntex for repairing a MySQL table is REPAIR TABLENAME, [TABLENAME, ], [Quick],[Extended] This command will repair the table specified if the quick is given the MySQL will do a repair of only the...View answers

Questions : 334 :: What is the maximum length of a table name, database name, and fieldname in MySQL?

he following table describes the maximum length for each type of identifier.   Identifier Maximum Length...View answers

Questions : 335 :: How many values can the SET function of MySQL take?

MySQL set can take zero or more values but at the maximum it can take 64 values

Questions : 336 :: What are the other commands to know the structure of table using MySQL commands except explain command?

describe Table-Name;

Questions : 337 :: How many tables will create when we create table, what are they?

The ‘.frm’ file stores the table definition. The data file has a ‘.MYD’ (MYData) extension. The index file has a ‘.MYI’ (MYIndex) extension,

Questions : 338 :: What is the purpose of the following files having extensions 1) .frm 2) .myd 3) .myi? What do these files contain?

In MySql, the default table type is MyISAM. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type....View answers

Questions : 339 :: Give the syntax of Grant and Revoke commands?

The generic syntax for grant is as following > GRANT [rights] on [database/s] TO [username@hostname] IDENTIFIED BY [password] now rights can be a) All privileges b) combination of create, drop,...View answers

Questions : 340 :: Explain Normalization concept?

The normalization process involves getting our data to conform to three progressive normal forms, and a higher level of normalization cannot be achieved until the previous levels have been achieved...View answers

Questions : 341 :: How can we find the number of rows in a table using MySQL?

Use this for mysql >SELECT COUNT(*) FROM table_name;

Questions : 342 :: How many ways we can we find the current date using MySQL?

SELECT CURDATE(); CURRENT_DATE() = CURDATE() for time use SELECT CURTIME(); CURRENT_TIME() = CURTIME()

Questions : 343 :: What are the advantages and disadvantages of Cascading Style Sheets?

External Style SheetsAdvantagesCan control styles for multiple documents at once. Classes can be created for use on multiple HTML element types in many documents. Selector and grouping methods can be...View answers

Questions : 344 :: What is the difference between Primary Key and Unique key?

Primary Key: A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL. Unique Key: Unique Keys are used to uniquely identify each row in the...View answers

Questions : 345 :: The structure of table view buyers is as follows:

Field Type Null Key Default Extra user_pri_id int(15)   PRI null auto_increment userid varchar(10) YES   null   the value of user_pri_id the last...View answers

Questions : 346 :: What is the difference between GROUP BY and ORDER BY in Sql?

  ORDER BY [col1],[col2],…,[coln]; Tels DBMS according to what columns it should sort the result. If two rows will hawe the same value in col1 it will try to sort them according to...View answers

Questions : 347 :: What is the difference between char and varchar data types?

Set char to occupy n bytes and it will take n bytes even if u r storing a value of n-m bytes Set varchar to occupy n bytes and it will take only the required space and will not use the n bytes eg....View answers

Questions : 348 :: How can we know the number of days between two given dates using MySQL?

SELECT DATEDIFF(‘2007-03-07′,’2005-01-01′);

Questions : 349 :: How can we know the number of days between two given dates using PHP?

$date1 = date(‘Y-m-d’); $date2 = ‘2006-08-15′; $days = (strtotime($date1) – strtotime($date2)) / (60 * 60 * 24);

Questions : 350 :: Does SQL support programming?

No, SQL doesn't have loop or Conditional statement. It is used like commanding language to access databases.

Questions : 351 :: What is oracle database ?

Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical...View answers

Questions : 352 :: What is schema?

A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc...View answers

Questions : 353 :: What is a Tablespace?

Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can...View answers

Questions : 354 :: What is a Control File ?

Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can...View answers

Questions : 355 :: Define data blocks ?

Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk

Questions : 356 :: What is an Extent ?

Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

Questions : 357 :: What is the difference between Client side JavaScript and Server side JavaScript.

Client side java script comprises the basic language and predefined objects which are relevant to running java script in a browser. The client side java script is embedded directly by in the HTML...View answers

Questions : 358 :: Where are cookies actually stored on the hard disk?

The storage of cookies on the hard disk depends on OS and the browser. The Netscape navigator on Windows, the file cookies.txt contains all the cookies. The path is : c:Program...View answers

Questions : 359 :: What is the difference between a web-garden and a web-farm?

Web garden is a web hosting system. It is a setup of multi processors in single server. Web farm is a web hosting system. It is a multi-server scenario.

Questions : 360 :: How to Accessing Elements using javascript?

The elements of JavaScript are accessed by their names. By default the browser is accessed by the element ‘windows’ and the page by ‘document’. The corresponding element has...View answers

Questions : 361 :: What is the difference between undefined value and null value?

Undefined value: A value that is not defined and has no keyword is known as undefined value. For example in the declaration, int number; the number has undefined value. Null value: A value that is...View answers

Questions : 362 :: How to set the cursor to wait in JavaScript?

The cursor can set to wait in JavaScript by using the property ‘cursor’ property. The following example illustrates the usage. window.document.body.style.cursor = "wait"; // sets the...View answers

Questions : 363 :: What is decodeURI(), encodeURI() in JavaScript?

To send the characters that can not be specified in a URL should be converted into their equivalent hex encoding. To perform this task the methods encodeURI() and decodeURI() are used. For example,...View answers
More Question

Ask your interview questions on Sql Dba

Write Your comment or Questions if you want the answers on Sql Dba from Sql Dba Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---