MYSQL,Postgres

Q.What's MySQL ?

Ans: MySQL (pronounced "my ess cue el") is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility. ...

Q. What is DDL, DML and DCL ?

Ans: If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Q.How do you get the number of rows affected by query?

ANS :SELECT COUNT (user_id) FROM users would only return the number of user_id’s. 

Q.If the value in the column is repeatable, how do you find out the unique values?

Ans: Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users; 

Q. How do you return the a hundred books starting from 25th?

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

Q. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) ?

Ans: It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id 

Q.How do you find out which auto increment was assigned on the last insert?

Ans:SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

Q.What does –i-am-a-dummy flag to do when starting MySQL?

Ans: Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

Q.How would you delete a column?

Ans:ALTER TABLE techpreparation_answers DROP answer_user_id.

Q. What is difference between mysql_connect and mysql_pconnect?

Ans:Mysql_connect() opens a new connection to the database while mysql_pconnect() opens a persistent connection to the database. This means that each time the page is loaded mysql_pconnect() does not open the database. Mysql_close() cannot be used to close the persistent connection. Though it can be used to close mysql_connect().

Q.What is MySQL data directory? How to determine the location of the data directory?

Ans: MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data..

Q.Explain advantages of MyISAM over InnoDB?

Ans:Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

Q.What are HEAP tables in MySQL?

Ans: HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

Q.How do you control the max size of a HEAP table?

Ans:MySQL config variable max_heap_table_size.

Q.Database Basics 

Ans: Databases are managed by a relational database management system (RDBMS). An RDBMS supports a database language to create and delete databases and to manage and search data. The database language used in almost all DBMSs is SQL, a set of statements that define and manipulate data. After creating a database, the most common SQL statements used are INSERT, UPDATE, DELETE, and SELECT, which add, change, remove, and search data in a database, respectively.

Database
A repository to store data.

Table
The part of a database that stores the data. A table has columns or attributes, and the data stored in rows.

Attributes
The columns in a table. All rows in table entities have the same attributes. For example, a customer table might have the attributes name, address, and city. Each attribute has a data type such as string, integer, or date.

Rows
The data entries in a table. Rows contain values for each attribute. For example, a row in a customer table might contain the values "Matthew Richardson," "Punt Road," and "Richmond." Rows are also known as records.

Relational model
A model that uses tables to store data and manage the relationship between tables.

Relational database management system
A software system that manages data in a database and is based on the relational model. DBMSs have several components described in detail in Chapter 1.

SQL
A query language that interacts with a DBMS. SQL is a set of statements to manage databases, tables, and data.

Constraints
Restrictions or limitations on tables and attributes. For example, a wine can be produced only by one winery, an order for wine can't exist if it isn't associated with a customer, having a name attribute could be mandatory for a customer.

Primary key
One or more attributes that contain values that uniquely identify each row. For example, a customer table might have the primary key of cust ID. The cust ID attribute is then assigned a unique value for each customer. A primary key is a constraint of most tables.

Index
A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries.

Entity-relationship modeling
A technique used to describe the real-world data in terms of entities, attributes, and relationships.

Normalized database
A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database.

MySQL Command Interpreter
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.

Q. How to take the Dump of a MySQL Database ?

Ans : Mysqldump databasename > dumpname