• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 61 : - Command Interpreter of mysql ?

Answer - 61 : - The MySQL command interpreter is commonly used to create databases and tables in web database applications and to test queries. Throughout the remainder of this chapter we discuss the SQL statements for managing a database. All these statements can be directly entered into the command interpreter and executed. The statements can also be included in server-side PHP scripts, as discussed in later chapters. Once the MySQL DBMS server is running, the command interpreter can be used. The command interpreter can be run using the following command from the shell, assuming you've created a user hugh with a password shhh: % /usr/local/bin/mysql -uhugh -pshhh The shell prompt is represented here as a percentage character, %. Running the command interpreter displays the output: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 to server version: 3.22.38 Type 'help' for help. mysql> The command interpreter displays a mysql> prompt and, after executing any command or statement, it redisplays the prompt. For example, you might issue the statement: mysql> SELECT NOW( ); This statement reports the time and date by producing the following output: +---------------------+ | NOW( ) | +---------------------+ | 2002-01-01 13:48:07 | +---------------------+ 1 row in set (0.00 sec) mysql> After running a statement, the interpreter redisplays the mysql> prompt. We discuss the SELECT statement later in this chapter. As with all other SQL statements, the SELECT statement ends in a semicolon. Almost all SQL command interpreters permit any amount of whitespace—spaces, tabs, or carriage returns—in SQL statements, and they check syntax and execute statements only after encountering a semicolon that is followed by a press of the Enter key. We have used uppercase for the SQL statements throughout this book. However, any mix of upper- and lowercase is equivalent. On startup, the command interpreter encourages the use of the help command. Typing help produces a list of com

Question - 62 : - When and how much can MySQL replication improve the performance of my system?

Answer - 62 : - MySQL replication is most beneficial for a system with frequent reads and not so frequent writes. In theory, by using a one master/many slaves setup you can scale by adding more slaves until you either run out of network bandwidth, or your update load grows to the point that the master cannot handle it. In order to determine how many slaves you can get before the added benefits begin to level out, and how much you can improve performance of your site, you need to know your query patterns, and empirically (by benchmarking) determine the relationship between the throughput on reads (reads per second, or max_reads) and on writes max_writes) on a typical master and a typical slave. The example below will show you a rather simplified calculation of what you can get with replication for our imagined system. Let's say our system load consists of 10% writes and 90% reads, and we have determined that max_reads = 1200 - 2 * max_writes, or in other words, our system can do 1200 reads per second with no writes, our average write is twice as slow as average read, and the relationship is linear. Let us suppose that our master and slave are of the same capacity, and we have N slaves and 1 master. Then we have for each server (master or slave): reads = 1200 - 2 * writes (from bencmarks) reads = 9* writes / (N + 1) (reads split, but writes go to all servers) 9*writes/(N+1) + 2 * writes = 1200 writes = 1200/(2 + 9/(N+1) So if N = 0, which means we have no replication, our system can handle 1200/11, about 109 writes per second (which means we will have 9 times as many reads due to the nature of our application). If N = 1, we can get up to 184 writes per second. If N = 8, we get up to 400. If N = 17, 480 writes. Eventually as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only 8 servers, we increased it almost 4 times already. Note that our computations assumed infinite network bandwidth, and neglected several other factors that could tu

Question - 63 : - Adding New User Privileges to MySQL?

Answer - 63 : - you can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. The examples below show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the insert privilege for the mysql database and the reload administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands below. You can add new users by issuing GRANT statements: shell> mysql --user=root mysql mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysqlgt; GRANT RELOAD,PROCESS ON *.* TO admin@localhost; mysqlgt; GRANT USAGE ON *.* TO dummy@localhost; These GRANT statements set up three new users: monty A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. admin A user who can connect from localhost without a password and who is granted the reload and process administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin process list . No database-related privileges are granted. (They can be granted later by issuing additional GRANT statements.) dummy A user who can connect without a password, but only from the local host. The global privileges are all set to 'N' -- the USAGE privilege type allows you to create a

Question - 64 : - MySQL Extensions to ANSI SQL92?

Answer - 64 : - MySQL includes some extensions that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the form /*! ... */. In this case, MySQL will parse and execute the code within the comment as it would any other MySQL statement, but other SQL servers will ignore the extensions. For example: SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ... If you add a version number after the '!', the syntax will only be executed if the MySQL version is equal to or newer than the used version number: CREATE /*!32302 TEMPORARY */ TABLE (a int); The above means that if you have Version 3.23.02 or newer, then MySQL will use the TEMPORARY keyword. MySQL extensions are listed below: The field types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types. The field attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL. All string comparisons are case insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host. MySQL maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory. This has a few implications: Database names and table names are case sensitive in MySQL on operating systems that have case-sensitive filenames (like most Unix systems). Database, table, index, column, or alias names may begin with a digit (but may not consist solely of digits). You can use standard system commands to backup, rename, move, delete, and copy tables. For example, to rename a table, rename the `.MYD', `.MYI', and `.frm' files to which the table corresponds. In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL doesn't support tablespaces as in: create table ral

Question - 65 : - Replication in MySQL?

Answer - 65 : - One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case. Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates. Note that if you are replicating a database, all updates to this database should be done through the master! On older servers one can use the update log to do simple replication. Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master.

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

Answer - 66 : - SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

Question - 67 : - What does + mean in REGEXP?

Answer - 67 : - At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

Question - 68 : - How do you return the a hundred books starting from 25th?

Answer - 68 : - SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

Question - 69 : - When would you use ORDER BY in DELETE statement?

Answer - 69 : - When you’re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techpreparation_com_questions.

Question - 70 : - What is SERIAL data type in MySQL?


Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners