• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

MS SQL Interview Questions Answers

Question - 21 : - How do you convert between Unix timestamps and MySQL timestamps?

Answer - 21 : - UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

Question - 22 : - How many Service Packs will be released for SQL Server 2005 in 2007?

Answer - 22 : -  The answer is up in the air and this is more of a poll than a real QOD. Based on the ways things are going, the staff here sees just 1, though our hope would be that 3 or 4 would be released. You setup a linked server from a SQL Server 2000 server to your new SQL Server 2005 server (with defaults), however you cannot execute

Question - 23 : - How can I find the last day of the month?

Answer - 23 : - You can combine T-SQL's DATEADD() and DATEDIFF() functions to calculate different date and time values. Subtract 5ms from the first day of the next month to find the last day of the current month: SELECT DATEADD(ms,-5,DATEADD(mm, DATEDIFF(m,0,GETDATE()  )+1, 0))

Question - 24 : - How would you delete a column?

Answer - 24 : - ALTER TABLE techpreparation_answers DROP answer_user_id.

Question - 25 : - Quries Other Optimization Tips

Answer - 25 : - Use persistent connections to the database to avoid the connection overhead. If you can't use persistent connections and you are doing a lot of new connections to the database, you may want to change the value of the thread_cache_size variable. Always check that all your queries really use the indexes you have created in the tables. In MySQL you can do this with the EXPLAIN command. Try to avoid complex SELECT queries on tables that are updated a lot. This is to avoid problems with table locking. The new MyISAM tables can insert rows in a table without deleted rows at the same time another table is reading from it. If this is important for you, you should consider methods where you don't have to delete rows or run OPTIMIZE TABLE after you have deleted a lot of rows. Use ALTER TABLE ... ORDER BY expr1,expr2... if you mostly retrieve rows in expr1,expr2.. order. By using this option after big changes to the table, you may be able to get higher performance. In some cases it may make sense to introduce a column that is 'hashed' based on information from other columns. If this column is short and reasonably unique it may be much faster than a big index on many columns. In MySQL it's very easy to use this extra column: SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant' For tables that change a lot you should try to avoid all VARCHAR or BLOB columns. You will get dynamic row length as soon as you are using a single VARCHAR or BLOB column. It's not normally useful to split a table into different tables just because the rows gets 'big'. To access a row, the biggest performance hit is the disk seek to find the first byte of the row. After finding the data most new disks can read the whole row fast enough for most applications. The only cases where it really matters to split up a table is if it's a dynamic row size table (see above) that you can change to a fixed row size, or if you very often need to scan the table and don't need most of the columns. If you very often need to calculate things based on information from a lot of rows (like counts of things), it's probably much better to introduce a new table and update the counter in real time. An update of type UPDATE table set count=count+1 where index_column=constant is very fast! This is really important when you use databases like MySQL that only have table locking (multiple readers /

Question - 26 : - What the heck does ATN2 do?

Answer - 26 : - The angle between the x-axis and a ray. This is a mathematical function that returns the angle between the positive x-axis and the ray that passes through the two coordinates passed in. The angle is in radians.

Question - 27 : - When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables?

Answer - 27 : - 1. VARCHARs with length less than 4 become CHARs 2. CHARs with length more than 3 become VARCHARs. 3. NOT NULL gets added to the columns declared as PRIMARY KEYs 4. Default values such as NULL are specified for each column

Question - 28 : - What does tee command do in MySQL?

Answer - 28 : - tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command note.

Question - 29 : - Access Control, Stage 1: Connection Verification?

Answer - 29 : - When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether or not you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests. Your identity is based on two pieces of information: The host from which you connect Your MySQL user name Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password. Values in the user table scope fields may be specified as follows: A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host. You can use the wild-card characters `%' and `_' in the Host field. A Host value of '%' matches any hostname. A blank Host value is equivalent to '%'. Note that these values match any host that can create a connection to your server! As of MySQL Version 3.23, for Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example: GRANT ALL PRIVILEGES on db.* to david@'192.58.197.0/255.255.255.0'; This will allow everyone to connect from an IP where the following is true: user_ip & netmask = host_ip. In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server. Wild-card characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table entry that matches an incoming connection has a blank user name, the user is considered to be the anonymous user (the user with no name), rather than the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2). The Password field can be blank. This does not mean that any password matches, it means the user must connect without specifying a password. Non-blank Password values represent encrypted passwords. MySQL does not

Question - 30 : - If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

Answer - 30 : - 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

Computer Contributors

krishan
Rajeev Katiyar
Yes Baroda

Share your email for latest updates

Name:
Email:

Our partners